sqldef

sqldef is the easiest idempotent schema management tool for MySQL, PostgreSQL, SQLite3, and SQL Server that uses plain SQL DDLs. Define your desired schema in SQL, and sqldef generates and applies the migrations to update your database.

With sqldef, you maintain a single SQL file with your complete schema. To modify your schema - add columns, change constraints, or create indexes - simply edit this file. sqldef compares desired against current schema and generates the appropriate DDLs, ensuring your database reaches the desired state from any starting point.

Each database gets its own command (mysqldef, psqldef, sqlite3def, mssqldef) that mimics the connection options of the native database client, making it familiar and easy to integrate into existing workflows. The tool comes as a single binary with no dependencies, and provides idempotent operations that are safe to run multiple times.

This is inspired by Ridgepole, which uses Ruby DSL. However, sqldef uses plain SQL, so all you need to remember is SQL.

Supported Databases

• mysqldef - MySQL, MariaDB, and TiDB
• psqldef - PostgreSQL
• sqlite3def - SQLite3
• mssqldef - SQL Server

See CI workflow for tested versions.

Usage

Basic Workflow

This is the basic workflow, which is identical across all databases - only the connection options differ between commands.

Note: Replace $sqldef with the appropriate command for your database:

• mysqldef for MySQL
• psqldef for PostgreSQL
• sqlite3def for SQLite
• mssqldef for SQL Server

1. Export Current Schema

$sqldef [connection-options] --export > schema.sql

Export the existing database schema to review your starting point.

2. Modify the Schema

Edit schema.sql to add, remove, or change columns/tables/indexes:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  age INTEGER,  -- Added new column
  created_at TIMESTAMP
);

3. Preview Changes

$sqldef [connection-options] --dry-run < schema.sql

Show the migrations that will be applied without executing them (e.g., ALTER TABLE users ADD COLUMN age INTEGER).

4. Apply Changes

$sqldef [connection-options] --apply < schema.sql

Apply the necessary DDLs to transform current schema to desired state.

Running again shows no changes needed - operations are idempotent.

Offline Mode

sqldef can compare two SQL files without connecting to a database. This is useful for CI/CD pipelines, schema validation, and generating migration scripts.

To use offline mode, specify a .sql file as the database argument:

# Compare current.sql with desired.sql
$sqldef current.sql < desired.sql

See the command documentation for more details on offline mode features.

Renaming Tables, Columns, and Indexes

sqldef supports renaming tables, columns, and indexes using the -- @renamed from=old_name annotation.

Given the current schema:

CREATE TABLE user_accounts (
  id INTEGER PRIMARY KEY,
  username TEXT,
  age INTEGER
);

And the desired schema with @renamed annotation:

CREATE TABLE users ( -- @renamed from=user_accounts
  id INTEGER PRIMARY KEY,
  username TEXT,
  age INTEGER
);

This generates the following migration:

ALTER TABLE user_accounts RENAME TO users;

Also @renamed works for columns, indexes, and ENUM values. See command documentation for more details.

Command Documentation

• mysqldef
• psqldef
• sqlite3def
• mssqldef

Examples

See practical examples in the example directory:

# Database mode - apply schema changes to a running database
./example/run.sh psqldef      # PostgreSQL
./example/run.sh mysqldef     # MySQL/MariaDB
./example/run.sh sqlite3def   # SQLite3
./example/run.sh mssqldef     # SQL Server

# Offline mode - compare schema files without database connection
./example/run-offline.sh psqldef      # PostgreSQL
./example/run-offline.sh mysqldef     # MySQL/MariaDB
./example/run-offline.sh sqlite3def   # SQLite3
./example/run-offline.sh mssqldef     # SQL Server

Installation

Pre-built binaries

Download the single-binary executable for your favorite database from:

https://github.com/sqldef/sqldef/releases

Docker images

Docker images are available on Docker Hub:

https://hub.docker.com/u/sqldef

Linux

Debian packages are not currently available. Use the pre-built binaries or Docker images instead.

# mysqldef
wget -O - https://github.com/sqldef/sqldef/releases/latest/download/mysqldef_linux_amd64.tar.gz \
  | tar xvz

# psqldef
wget -O - https://github.com/sqldef/sqldef/releases/latest/download/psqldef_linux_amd64.tar.gz \
  | tar xvz

# sqlite3def
wget -O - https://github.com/sqldef/sqldef/releases/latest/download/sqlite3def_linux_amd64.tar.gz \
  | tar xvz

# mssqldef
wget -O - https://github.com/sqldef/sqldef/releases/latest/download/mssqldef_linux_amd64.tar.gz \
  | tar xvz

macOS

Homebrew tap is available.

# mysqldef
brew install sqldef/sqldef/mysqldef

# psqldef
brew install sqldef/sqldef/psqldef

# sqlite3def
brew install sqldef/sqldef/sqlite3def

# mssqldef
brew install sqldef/sqldef/mssqldef

Preview Changes with GitHub Actions

There's a GitHub Action that can preview changes to your database schema:

https://github.com/sqldef/sqldef-preview-action

Development

If you update parser/parser.y, run:

$ make parser

Use the following commands to prepare command line tools and DB servers for running tests.

This is the beginning of the README. Read the rest at the original. Read the full README ↗