ddlctl

module

v0.0.12 Latest Latest Go to latest Published: Feb 12, 2024 License: Apache-2.0

Details

Valid go.mod file
Redistributable license
Tagged version
Stable version
Learn more about best practices

Repository

github.com/kunitsucom/ddlctl

Links

Open Source Insights

README ¶

ddlctl

ddlctl is a tool to control RDBMS DDLs: output all RDBMS DDLs, generate DDLs from tagged Golang source code, view differences between RDBMS and your DDL, and automate migrations.

[!WARNING] This project is experimental. It is operational in the author's environment, but it is not known if it can be operated in other environments without trouble.

Demo

ddlctl_demo

Overview

ddlctl can do the following:

Output all RDBMS DDLs
Generate DDL from tagged Golang source code
Output differences between the RDBMS and your DDL
Automated Migration

TODO

generate subcommand
- source language
  - Support go (beta)
- dialect
  - Support mysql (alpha)
  - Support postgres (alpha)
  - Support cockroachdb (alpha)
  - Support spanner (alpha)
  - Support sqlite3
show subcommand
- dialect
  - Support mysql (beta)
  - Support postgres (alpha)
  - Support cockroachdb (beta)
  - Support spanner (alpha)
  - Support sqlite3
diff subcommand
- dialect
  - Support mysql (alpha)
  - Support postgres (alpha)
  - Support cockroachdb (alpha)
  - Support spanner (alpha)
  - Support sqlite3
apply subcommand
- dialect
  - Support mysql (alpha)
  - Support postgres (alpha)
  - Support cockroachdb (alpha)
  - Support spanner (alpha)
  - Support sqlite3

Example: `ddlctl generate`

1. Prepare your annotated model source code

For example, prepare the following Go code:

package sample

// User is a user model struct.
//
//pgddl:table public.users
//pgddl:constraint UNIQUE ("username")
//pgddl:index "index_users_username" ON public.users ("username")
type User struct {
    UserID   string `db:"user_id"  pgddl:"TEXT NOT NULL" pk:"true"`
    Username string `db:"username" pgddl:"TEXT NOT NULL"`
    Age      int    `db:"age"      pgddl:"INT  NOT NULL"`
}

// Group is a group model struct.
//
//pgddl:table CREATE TABLE IF NOT EXISTS public.groups
//pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")
type Group struct {
    GroupID     string `db:"group_id"    pgddl:"TEXT NOT NULL" pk:"true"`
    GroupName   string `db:"group_name"  pgddl:"TEXT NOT NULL"`
    Description string `db:"description" pgddl:"TEXT NOT NULL"`
}

Write it to an appropriate file:

cat <<"EOF" > /tmp/sample.go
package sample

// User is a user model struct.
//
//pgddl:table public.users
//pgddl:constraint UNIQUE ("username")
//pgddl:index "index_users_username" ON public.users ("username")
type User struct {
    UserID   string `db:"user_id"  pgddl:"TEXT NOT NULL" pk:"true"`
    Username string `db:"username" pgddl:"TEXT NOT NULL"`
    Age      int    `db:"age"      pgddl:"INT  NOT NULL"`
}

// Group is a group model struct.
//
//pgddl:table CREATE TABLE IF NOT EXISTS public.groups
//pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")
type Group struct {
    GroupID     string `db:"group_id"    pgddl:"TEXT NOT NULL" pk:"true"`
    GroupName   string `db:"group_name"  pgddl:"TEXT NOT NULL"`
    Description string `db:"description" pgddl:"TEXT NOT NULL"`
}
EOF

2. Generate DDL

Please execute the ddlctl command as follows:

$ ddlctl generate --dialect postgres --go-column-tag db --go-ddl-tag pgddl --go-pk-tag pk --src /tmp/sample.go --dst /tmp/sample.sql
INFO: 2023/11/16 16:10:39 ddlctl.go:44: source: /tmp/sample.go
INFO: 2023/11/16 16:10:39 ddlctl.go:73: destination: /tmp/sample.sql

3. Check generated DDL file

Please check the contents of the outputted DDL:

cat /tmp/sample.sql

content:

-- Code generated by ddlctl. DO NOT EDIT.
--

-- source: tmp/sample.go:5
-- User is a user model struct.
--
-- pgddl:table public.users
-- pgddl:constraint UNIQUE ("username")
CREATE TABLE public.users (
    "user_id"  TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "age"      INT  NOT NULL,
    PRIMARY KEY ("user_id"),
    UNIQUE ("username")
);

-- source: tmp/sample.go:7
-- pgddl:index "index_users_username" ON public.users ("username")
CREATE INDEX "index_users_username" ON public.users ("username");

-- source: tmp/sample.go:16
-- Group is a group model struct.
--
-- pgddl:table CREATE TABLE IF NOT EXISTS public.groups
CREATE TABLE IF NOT EXISTS public.groups (
    "group_id"    TEXT NOT NULL,
    "group_name"  TEXT NOT NULL,
    "description" TEXT NOT NULL,
    PRIMARY KEY ("group_id")
);

-- source: tmp/sample.go:17
-- pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")
CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name");

Example: `ddlctl diff` and `ddlctl apply`

1. Prepare your DDL

cat /tmp/sample.sql

content:

-- Code generated by ddlctl. DO NOT EDIT.
--

-- source: tmp/sample.go:5
-- User is a user model struct.
--
-- pgddl:table public.users
-- pgddl:constraint UNIQUE ("username")
CREATE TABLE public.users (
    "user_id"  TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "age"      INT  NOT NULL,
    PRIMARY KEY ("user_id"),
    UNIQUE ("username")
);

-- source: tmp/sample.go:7
-- pgddl:index "index_users_username" ON public.users ("username")
CREATE INDEX "index_users_username" ON public.users ("username");

-- source: tmp/sample.go:16
-- Group is a group model struct.
--
-- pgddl:table CREATE TABLE IF NOT EXISTS public.groups
CREATE TABLE IF NOT EXISTS public.groups (
    "group_id"    TEXT NOT NULL,
    "group_name"  TEXT NOT NULL,
    "description" TEXT NOT NULL,
    PRIMARY KEY ("group_id")
);

-- source: tmp/sample.go:17
-- pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")
CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name");

2. Check diff from local DDL file to DSN

Please check the differences between the local DDL file and the destination database:

$ ddlctl diff --dialect postgres "postgres://postgres:password@localhost/testdb?sslmode=disable" /tmp/sample.sql
CREATE TABLE public.users (
    "user_id" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "age" INT NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY ("user_id"),
    CONSTRAINT users_unique_username UNIQUE ("username")
);
CREATE INDEX "index_users_username" ON public.users ("username");
CREATE TABLE IF NOT EXISTS public.groups (
    "group_id" TEXT NOT NULL,
    "group_name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    CONSTRAINT groups_pkey PRIMARY KEY ("group_id")
);
CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name");

3. Apply DDL

$ ddlctl apply --dialect postgres "postgres://postgres:password@localhost/testdb?sslmode=disable" /tmp/sample.sql --auto-approve

ddlctl will exec the following DDL queries:

-- 8< --

CREATE TABLE public.users (
    "user_id" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "age" INT NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY ("user_id"),
    CONSTRAINT users_unique_username UNIQUE ("username")
);
CREATE INDEX "index_users_username" ON public.users ("username");
CREATE TABLE IF NOT EXISTS public.groups (
    "group_id" TEXT NOT NULL,
    "group_name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    CONSTRAINT groups_pkey PRIMARY KEY ("group_id")
);
CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name");


-- >8 --

Do you want to apply these DDL queries?
  ddlctl will exec the DDL queries described above.
  Only 'yes' will be accepted to approve.

Enter a value: yes (via --auto-approve option)

executing...
done

4. (Optional) Edit DDL and apply

 -- pgddl:table public.users
 -- pgddl:constraint UNIQUE ("username")
 CREATE TABLE public.users (
     "user_id"     TEXT NOT NULL,
     "username"    TEXT NOT NULL,
     "age"         INT  NOT NULL,
+    "description" TEXT NOT NULL,
     PRIMARY KEY ("user_id"),
     UNIQUE ("username")
 );

apply:

$ ddlctl apply --dialect postgres "postgres://postgres:password@localhost/testdb?sslmode=disable" /tmp/sample.sql --auto-approve

ddlctl will exec the following DDL queries:

-- 8< --

-- -
-- +"description" TEXT NOT NULL
ALTER TABLE public.users ADD COLUMN "description" TEXT NOT NULL;


-- >8 --

Do you want to apply these DDL queries?
  ddlctl will exec the DDL queries described above.
  Only 'yes' will be accepted to approve.

Enter a value: yes (via --auto-approve option)

executing...
done

Installation

pre-built binary

LATEST_VERSION=$(curl -ILSs https://github.com/kunitsucom/ddlctl/releases/latest | tr -d '\r' | awk -F'/tag/' '/location/ { print $2 }') && echo "${LATEST_VERSION}"
OS=$(uname | tr '[:upper:]' '[:lower:]') && echo "${OS}"
ARCH=$(uname -m) && echo "${ARCH}"

# download
curl -fLROSs "https://github.com/kunitsucom/ddlctl/releases/download/${LATEST_VERSION}/ddlctl_${LATEST_VERSION}_${OS}_${ARCH}.zip"

# unzip
unzip -j ddlctl_${LATEST_VERSION}_darwin_arm64.zip '*/ddlctl'

go install

go install github.com/kunitsucom/ddlctl/cmd/ddlctl@latest

Usage

`ddlctl`

$ ddlctl --help
Usage:
    ddlctl [options]

Description:
    ddlctl is a tool for control RDBMS DDL.

sub commands:
    version: show version
    generate: generate DDL from source (file or directory) to destination (file or directory).
    show: show DDL from DSN like `SHOW CREATE TABLE`.
    diff: diff DDL from <before DDL source> to <after DDL source>.
    apply: apply DDL from <DDL source> to <DSN to apply>.

options:
    --trace (env: DDLCTL_TRACE, default: false)
        trace mode enabled
    --debug (env: DDLCTL_DEBUG, default: false)
        debug mode
    --help (default: false)
        show usage

`ddlctl generate`

$ ddlctl generate --help
Usage:
    ddlctl generate [options] --dialect <DDL dialect> --src <source> --dst <destination>

Description:
    generate DDL from source (file or directory) to destination (file or directory).

options:
    --lang (env: DDLCTL_LANGUAGE, default: go)
        programming language to generate DDL
    --dialect (env: DDLCTL_DIALECT, default: )
        SQL dialect to generate DDL
    --go-column-tag (env: DDLCTL_COLUMN_TAG_GO, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_DDL_TAG_GO, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_PK_TAG_GO, default: pk)
        primary key annotation key for Go struct tag
    --src (env: DDLCTL_SOURCE, default: /dev/stdin)
        source file or directory
    --dst (env: DDLCTL_DESTINATION, default: /dev/stdout)
        destination file or directory
    --help (default: false)
        show usage

`ddlctl show`

$ ddlctl show --help
Usage:
    ddlctl show --dialect <DDL dialect> <DSN>

Description:
    show DDL from DSN like `SHOW CREATE TABLE`.

options:
    --dialect (env: DDLCTL_DIALECT, default: )
        SQL dialect to generate DDL
    --help (default: false)
        show usage

`ddlctl diff`

$ ddlctl diff --help
Usage:
    ddlctl diff [options] --dialect <DDL dialect> <before DDL source> <after DDL source>

Description:
    diff DDL from <before DDL source> to <after DDL source>.

options:
    --lang (env: DDLCTL_LANGUAGE, default: go)
        programming language to generate DDL
    --dialect (env: DDLCTL_DIALECT, default: )
        SQL dialect to generate DDL
    --go-column-tag (env: DDLCTL_COLUMN_TAG_GO, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_DDL_TAG_GO, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_PK_TAG_GO, default: pk)
        primary key annotation key for Go struct tag
    --help (default: false)
        show usage

`ddlctl apply`

$ ddlctl apply --help
Usage:
    ddlctl apply [options] --dialect <DDL dialect> <DSN to apply> <DDL source>

Description:
    apply DDL from <DDL source> to <DSN to apply>.

options:
    --lang (env: DDLCTL_LANGUAGE, default: go)
        programming language to generate DDL
    --dialect (env: DDLCTL_DIALECT, default: )
        SQL dialect to generate DDL
    --go-column-tag (env: DDLCTL_COLUMN_TAG_GO, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_DDL_TAG_GO, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_PK_TAG_GO, default: pk)
        primary key annotation key for Go struct tag
    --auto-approve (env: DDLCTL_AUTO_APPROVE, default: false)
        auto approve
    --help (default: false)
        show usage

Directories ¶

Path	Synopsis
cmd
ddlctl
pkg
apperr
ddl
ddl/cockroachdb
ddl/internal
ddl/logs
ddl/mysql
ddl/postgres
ddl/spanner
ddlctl
internal/config
internal/consts
internal/contexts
internal/fixture
internal/generator
internal/generator/dialect/mysql
internal/generator/dialect/postgres
internal/generator/dialect/spanner
internal/lang/go
internal/lang/util
internal/logs
internal/util
show/cockroachdb
show/mysql
show/postgres
show/spanner

?	: This menu
/	: Search site
f or F	: Jump to
y or Y	: Canonical URL

README ¶

ddlctl

Demo

Overview

TODO

Example: ddlctl generate

1. Prepare your annotated model source code

2. Generate DDL

3. Check generated DDL file

Example: ddlctl diff and ddlctl apply

1. Prepare your DDL

2. Check diff from local DDL file to DSN

3. Apply DDL

4. (Optional) Edit DDL and apply

Installation

pre-built binary

go install

Usage

ddlctl

ddlctl generate

ddlctl show

ddlctl diff

ddlctl apply

Directories ¶

Example: `ddlctl generate`

Example: `ddlctl diff` and `ddlctl apply`

`ddlctl`

`ddlctl generate`

`ddlctl show`

`ddlctl diff`

`ddlctl apply`