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

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"`
}

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 sample.go sample.sql
INFO: 2023/11/16 16:10:39 ddlctl.go:44: source: sample.go
INFO: 2023/11/16 16:10:39 ddlctl.go:73: destination: sample.sql

3. Check generated DDL file

Please check the contents of the outputted DDL:

cat sample.sql

content:

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

-- source: docs/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: docs/sample.go:7
-- pgddl:index      "index_users_username" ON public.users ("username")
CREATE INDEX "index_users_username" ON public.users ("username");

-- source: docs/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: docs/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 sample.sql

content:

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

-- source: docs/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: docs/sample.go:7
-- pgddl:index      "index_users_username" ON public.users ("username")
CREATE INDEX "index_users_username" ON public.users ("username");

-- source: docs/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: docs/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" 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" 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" 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 -ISs https://github.com/kunitsucom/ddlctl/releases/latest | tr -d '\r' | awk -F/ '/location:/{print $NF}')
OS=$(uname | tr '[:upper:]' '[:lower:]')
ARCH=$(uname -m)
URL="https://github.com/kunitsucom/ddlctl/releases/download/${LATEST_VERSION}/ddlctl_${LATEST_VERSION}_${OS}_${ARCH}.zip"

# Check URL
echo "${URL}"

# Download
curl -fLROSs "${URL}"

# Unzip
unzip -j ddlctl_${LATEST_VERSION}_${OS}_${ARCH}.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> <source> <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_GO_COLUMN_TAG, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_GO_DDL_TAG, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_GO_PK_TAG, default: pk)
        primary key annotation key for Go struct tag
    --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_GO_COLUMN_TAG, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_GO_DDL_TAG, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_GO_PK_TAG, 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_GO_COLUMN_TAG, default: db)
        column annotation key for Go struct tag
    --go-ddl-tag (env: DDLCTL_GO_DDL_TAG, default: ddlctl)
        DDL annotation key for Go struct tag
    --go-pk-tag (env: DDLCTL_GO_PK_TAG, 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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

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`

Files

README.md

Latest commit

History

README.md

File metadata and controls

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

Example: `ddlctl generate`

Example: `ddlctl diff` and `ddlctl apply`

`ddlctl`

`ddlctl generate`

`ddlctl show`

`ddlctl diff`

`ddlctl apply`