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")typeUserstruct {
UserIDstring`db:"user_id" pgddl:"TEXT NOT NULL" pk:"true"`Usernamestring`db:"username" pgddl:"TEXT NOT NULL"`Ageint`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")typeGroupstruct {
GroupIDstring`db:"group_id" pgddl:"TEXT NOT NULL" pk:"true"`GroupNamestring`db:"group_name" pgddl:"TEXT NOT NULL"`Descriptionstring`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.sqlINFO: 2023/11/16 16:10:39 ddlctl.go:44: source: sample.goINFO: 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")CREATETABLEpublic.users (
"user_id"TEXTNOT NULL,
"username"TEXTNOT NULL,
"age"INTNOT NULL,
PRIMARY KEY ("user_id"),
UNIQUE ("username")
);
-- source: docs/sample.go:7-- pgddl:index "index_users_username" ON public.users ("username")CREATEINDEX "index_users_username" ONpublic.users ("username");
-- source: docs/sample.go:16-- Group is a group model struct.---- pgddl:table CREATE TABLE IF NOT EXISTS public.groupsCREATETABLEIF NOT EXISTS public.groups (
"group_id"TEXTNOT NULL,
"group_name"TEXTNOT NULL,
"description"TEXTNOT NULL,
PRIMARY KEY ("group_id")
);
-- source: docs/sample.go:17-- pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")CREATEUNIQUE INDEX "index_groups_group_name" ONpublic.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")CREATETABLEpublic.users (
"user_id"TEXTNOT NULL,
"username"TEXTNOT NULL,
"age"INTNOT NULL,
PRIMARY KEY ("user_id"),
UNIQUE ("username")
);
-- source: docs/sample.go:7-- pgddl:index "index_users_username" ON public.users ("username")CREATEINDEX "index_users_username" ONpublic.users ("username");
-- source: docs/sample.go:16-- Group is a group model struct.---- pgddl:table CREATE TABLE IF NOT EXISTS public.groupsCREATETABLEIF NOT EXISTS public.groups (
"group_id"TEXTNOT NULL,
"group_name"TEXTNOT NULL,
"description"TEXTNOT NULL,
PRIMARY KEY ("group_id")
);
-- source: docs/sample.go:17-- pgddl:index CREATE UNIQUE INDEX "index_groups_group_name" ON public.groups ("group_name")CREATEUNIQUE INDEX "index_groups_group_name" ONpublic.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.sqlCREATE 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-approveddlctl 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-approveddlctl will exec the following DDL queries:-- 8< ---- --- +"description" TEXT NOT NULLALTER 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/hakadoriya/ddlctl/releases/latest | tr -d '\r'| awk -F/ '/location:/{print $NF}')
OS=$(uname | tr '[:upper:]''[:lower:]')
ARCH=$(uname -m)
URL="https://github.com/hakadoriya/ddlctl/releases/download/${LATEST_VERSION}/ddlctl_${LATEST_VERSION}_${OS}_${ARCH}.zip"# Check URLecho"${URL}"# Download
curl -fLROSs "${URL}"# Unzip
unzip -j ddlctl_${LATEST_VERSION}_${OS}_${ARCH}.zip '*/ddlctl'

go install

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

Usage

ddlctl

$ ddlctl --helpUsage: 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 --helpUsage: 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 --helpUsage: 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 --helpUsage: 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 --helpUsage: 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