sqddl

README

sqddl

one-page documentation

sqddl is a zero-configuration database migration tool for Go. It can generate migrations based on a declarative schema (defined as Go structs).

Notable features:

• Works on SQLite, Postgres, MySQL and SQL Server.
• No up and down migrations. No migration versions.
  • Migrations that have not been run will be run, and migrations that have already been run will be skipped.
  • That's basically it.
  • How do you roll back to a previous state? Update your schema definition, generate a new migration from it.
• Embed migrations into your application binary (via //go:embed) and run them automatically on startup.
• Supports transactional migrations.
  • Multiple migrations can run in a single transaction.
  • Or a migration can be run in its own transaction.
  • Or a migration can explicitly opt out of transactions.
• Supports undo migrations.
  • If a non-transactional migration fails, a corresponding undo migration can optionally be defined to clean up the effects of the failed migration.
• Supports repeatable migrations (concept taken from Flyway).
  • Repeatable migrations are migrations that are re-run whenever the contents of their file changes. Useful for views, stored procedures.
• Generates safe migrations by default.
• Dump a database and restore it later as easy test fixtures.

Installation

$ go install -tags=fts5 github.com/bokwoon95/sqddl@latest

Example migrations.

To view what a sample migration directory would look like, click the following links:

• sqlite_migrations
• postgres_migrations
• mysql_migrations
• sqlserver_migrations

Subcommands

sqddl has 12 subcommands. Click on each of them to find out more.

• migrate - Run pending migrations and add them to the history table.
• ls - Show pending migrations.
• touch - Upsert migrations into to the history table. Does not run them.
• rm - Remove migrations from the history table.
• mv - Rename migrations in the history table.
• tables - Generate table structs from database.
• views - Generate view structs from database.
• generate - Generate migrations from a declarative schema (defined as table structs).
• wipe - Wipe a database of all views, tables, routines, enums, domains and extensions.
• dump - Dump the database schema as SQL scripts and data as CSV files.
• load - Load SQL scripts and CSV files into a database.
• automigrate - Automatically migrate a database based on a declarative schema (defined as table structs).

History table

The history table stores the history of the applied migrations. The default name of the table is "sqddl_history". You can override it with the -history-table flag, but try not to do so unless you really have a table name that conflicts with the default.

This is the schema for the history table.

CREATE TABLE sqddl_history (
    filename VARCHAR(255) PRIMARY KEY NOT NULL,
    checksum VARCHAR(64),
    started_at DATETIME, -- postgres uses TIMESTAMPTZ, sqlserver uses DATETIMEOFFSET
    time_taken_ns BIGINT,
    success BOOLEAN -- sqlserver uses BIT
);

migrate

Docs: https://bokwoon.neocities.org/sqddl.html#migrate.

The migrate subcommand runs pending migrations in some directory (specified with -dir). No output means no pending migrations. Once a migration has been run, it will be recorded in a history table so that it doesn't get run again.

Any top-level *.sql file in the migration directory is considered a migration. You are free to use any naming convention for your migrations, but keep in mind that they will be run in alphabetical order.

Check out https://bokwoon.neocities.org/sqddl.html#db-flag for more -db flag examples.

# sqddl migrate -db <DATABASE_URL> -dir <MIGRATION_DIR> [FLAGS] [FILENAMES...]
$ sqddl migrate -db 'postgres://user:pass@localhost:5432/sakila' -dir ./migrations
BEGIN
[OK] 01_extensions_types.sql (22.4397ms)
[OK] 02_sakila.sql (194.1385ms)
[OK] 03_webpage.sql (29.5218ms)
[OK] 04_extras.sql (20.1678ms)
COMMIT

ls

Docs: https://bokwoon.neocities.org/sqddl.html#ls.

The ls subcommand shows the pending migrations to be run. No output means no pending migrations.

# sqddl ls -db <DATABASE_URL> -dir <MIGRATION_DIR> [FLAGS]
$ sqddl ls -db 'postgres://user:pass@localhost:5432/sakila' -dir ./migrations
[pending] 01_extensions_types.sql
[pending] 02_sakila.sql
[pending] 03_webpage.sql
[pending] 04_extras.sql

touch

Docs: https://bokwoon.neocities.org/sqddl.html#touch.

The touch subcommand upserts migrations into the history table without running them.

• The SHA256 checksum for repeatable migrations will be updated.
• started_at will be set to the current time.
• time_taken_ns will be set to 0.

# sqddl touch -db <DATABASE_URL> -dir <MIGRATION_DIR> [FILENAMES...]
$ sqddl touch \
    -db 'postgres://user:pass@localhost:5432/sakila' \
    -dir ./migrations \
    02_sakila.sql 04_extras.sql # add 02_sakila.sql and 04_extras.sql to the history table without running them
2 rows affected

rm

Docs: https://bokwoon.neocities.org/sqddl.html#rm.

The rm subcommand removes migrations from the history table (it does not remove the actual migration files from the directory). This is useful if you accidentally added migrations to the history table using touch, or if you want to deregister the migration from the history table so that migrate will run it again.

# sqddl rm -db <DATABASE_URL> [FILENAMES...]
$ sqddl rm \
    -db 'postgres://user:pass@localhost:5432/sakila' \
    -dir ./migrations \
    02_sakila.sql 04_extras.sql # remove 02_sakila.sql and 04_extras.sql from the history table
2 rows affected

mv

Docs: https://bokwoon.neocities.org/sqddl.html#mv.

The mv subcommand renames migrations in the history table. This is useful if you manually renamed the filename of a migration that was already run (for example a repeatable migration) and you want to update its entry in the history table.

# sqddl mv -db <DATABASE_URL> <OLD_FILENAME> <NEW_FILENAME>
$ sqddl mv \
    -db 'postgres://user:pass@localhost:5432/sakila' \
    -dir ./migrations \
    old_name.sql new_name.sql # renames old_name.sql to new_name.sql in the history table
1 row affected

tables

Docs: https://bokwoon.neocities.org/sqddl.html#tables.

The tables subcommand generates table structs from the database.

# sqddl tables -db <DATABASE_URL> [FLAGS]
$ sqddl tables -db 'postgres://user:pass@localhost:5432/sakila' -pkg tables -file tables/tables.go

views

Docs: https://bokwoon.neocities.org/sqddl.html#views.

The views subcommand generates view structs from the database.

# sqddl views -db <DATABASE_URL> [FLAGS]
$ sqddl views -db 'postgres://user:pass@localhost:5432/sakila' -pkg tables -file tables/views.go

generate

Docs: https://bokwoon.neocities.org/sqddl.html#generate.

The generate subcommand generates migrations needed to get from a source schema to a destination schema. The source is typically a database URL/DSN (same as the -db flag), while the destination is typically a Go source file containing table structs. No output means no migrations were generated.

# sqddl generate -src <SRC_SCHEMA> -dest <DEST_SCHEMA> [FLAGS]
$ sqddl generate \
    -src 'postgres://user:pass@localhost:5432/mydatabase' \
    -dest tables/tables.go \
    -output-dir ./migrations
./migrations/20060102150405_01_schemas.sql
./migrations/20060102150405_02_tables.sql
./migrations/20060102150405_03_add_person_country_fkeys.tx.sql

$ sqddl generate -dialect sqlite -dest tables/tables.go -dry-run
-- 20060102150405.sql
CREATE TABLE actor (
    actor_id INTEGER PRIMARY KEY AUTOINCREMENT
    ,first_name TEXT NOT NULL
    ,last_name TEXT NOT NULL
    ,last_update DATETIME NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX actor_last_name_idx ON actor (last_name);

CREATE TABLE category (
    category_id INTEGER PRIMARY KEY
    ,name TEXT NOT NULL
    ,last_update DATETIME NOT NULL DEFAULT (unixepoch())
);

wipe

Docs: https://bokwoon.neocities.org/sqddl.html#wipe.

The wipe subcommand wipes a database of all views, tables, routines, enums, domains and extensions.

# sqddl wipe -db <DATABASE_URL> [FLAGS]
$ sqddl wipe -db 'postgres://user:pass@localhost:5432/sakila'

dump

Docs: https://bokwoon.neocities.org/sqddl.html#dump.

The dump subcommand can dump a database's schema and data.

• The schema is dumped as 4 files:
  • schema.json
  • schema.sql
  • indexes.sql
  • constraints.sql
• The data is dumped as a CSV file per table.
  • e.g. if the table is called actor, the CSV file will be called actor.csv.

# sqddl dump -db <DATABASE_URL> [FLAGS]
$ sqddl dump -db 'postgres://user:pass@localhost:5432/sakila' -output-dir ./db
./db/schema.json
./db/schema.sql
./db/indexes.sql
./db/constraints.sql
./db/actor.csv
./db/address.csv
./db/category.csv
./db/city.csv
./db/country.csv
./db/customer.csv
./db/data.csv
./db/film.csv
./db/film_actor.csv
./db/film_category.csv
./db/inventory.csv
./db/language.csv
./db/payment.csv
./db/rental.csv
./db/staff.csv
./db/store.csv

load

Docs: https://bokwoon.neocities.org/sqddl.html#load.

The load subcommand loads SQL scripts and CSV files into a database. It can also load directories and zip/tar gzip archives created by the dump subcommand.

# sqddl load -db <DATABASE_URL> [FLAGS] [FILENAMES...]
$ sqddl load \
    -db 'postgres://user:pass@localhost:5432/sakila' \
    ./db/schema.sql ./db/actor.csv ./db/language.csv ./db/indexes.sql ./db/constraints.sql

$ sqddl load -db 'postgres://user:pass@localhost:5432/sakila' ./db

$ sqddl load -db 'postgres://user:pass@localhost:5432/sakila' ./db/sakila.zip

$ sqddl load -db 'postgres://user:pass@localhost:5432/sakila' ./db/sakila.tgz

automigrate

Docs: https://bokwoon.neocities.org/sqddl.html#automigrate.

The automigrate subcommand automatically migrates a database based on a declarative schema (defined as table structs). It is equivalent to running generate followed by migrate, except the generated migrations are created in-memory and will not be added to the history table.

# sqddl automigrate -db <DATABASE_URL> -dest <DEST_SCHEMA> [FLAGS]
$ sqddl automigrate -db 'postgres://user:pass@localhost:5432/sakila' -dest tables/tables.go
BEGIN
[OK] automigrate_01_schemas.sql (604.834µs)
[OK] automigrate_02_tables.sql (6.896833ms)
COMMIT
BEGIN
[OK] automigrate_03_add_person_country_fkeys.tx.sql (1.40075ms)
COMMIT

Contributing

See START_HERE.md.