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/sqddl@latest

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).

-db flag

-db is the database url needed to connect to your database. For sqlite this is a file path.

SQLite examples

# <filename>.{sqlite,sqlite3,db,db3}
relative/path/to/file.sqlite
./relative/path/to/file.sqlite3
/absolute/path/to/file.db
file:/absolute/path/to/file.db3

# sqlite:<filepath>
sqlite:relative/path/to/file
sqlite:./relative/path/to/file
sqlite:/absolute/path/to/file
sqlite:file:/absolute/path/to/file

Postgres examples

# postgres://<username>:<password>@<host>:<port>/<database>
postgres://user:pass@localhost:5432/sakila
postgres://admin1:Hunter2!@127.0.0.1:5433/mydatabase

MySQL examples

# <username>:<password>@tcp(<host>:<port>)/<database>
user:pass@tcp(localhost:3306)/sakila
root:Hunter2!@tcp(127.0.0.1:3307)/mydatabase

# mysql://<username>:<password>@<host>:<port>/<database>
mysql://user:pass@localhost:3306/sakila
mysql://root:Hunter2!@127.0.0.1:3307/mydatabase

# mysql://<username>:<password>@tcp(<host>:<port>)/<database>
mysql://user:pass@tcp(localhost:3306)/sakila
mysql://root:Hunter2!@tcp(127.0.0.1:3307)/mydatabase

SQL Server examples

# sqlserver://<username>:<password>@<host>:<port>?database=<database>
sqlserver://user:pass@localhost:1433?database=sakila
sqlserver://sa:Hunter2!@127.0.0.1:1434?database=mydatabase

# sqlserver://<username>:<password>@<host>:<port>/<database>
sqlserver://user:pass@localhost:1433/sakila
sqlserver://sa:Hunter2!@127.0.0.1:1434/mydatabase

Contributing

See START_HERE.md.