$ dbdiff diff current.sqlite desired.sqlite
ALTER TABLE "users" ADD COLUMN "created_at" TEXT;
CREATE INDEX "users_email" ON "users" ("email");
DROP TABLE "audit";
CREATE VIEW active_users AS SELECT id, email FROM users;

dbdiff supports SQLite, PostgreSQL, MySQL, and MariaDB. Each side is a database, a .sql file, or a directory of .sql files.

Download a binary from the releases page, or install with Go:

go install github.com/quantumsheep/dbdiff/cmd/dbdiff@latest

dbdiff needs no C compiler. The SQLite driver supports the virtual table modules fts5, rtree, and geopoly. It does not support fts3 and fts4.

dbdiff diff changes no database. To apply its output, send it to the client of the engine:

dbdiff diff current.sqlite schema.sql | sqlite3 -bail current.sqlite
dbdiff diff postgres://localhost:5432/current postgres://localhost:5432/desired

For SQLite, use -bail. If a statement of a table recreation fails, -bail stops the script, and SQLite rolls back the recreation. Without -bail, the script continues after the error.

dbdiff completion <shell> prints the completion script for bash, zsh, fish, or pwsh.

source <(dbdiff completion bash)                         # bash
source <(dbdiff completion zsh)                          # zsh
dbdiff completion fish > ~/.config/fish/completions/dbdiff.fish   # fish

dbdiff runs a .sql file, or the .sql files of a directory in name order, in a temporary database. For PostgreSQL and MySQL, it downloads a temporary server on the first run, with the version of the other side. The --server-version flag sets another version.

Each file runs in one transaction. If a file holds a statement that cannot run in a transaction, for example CREATE INDEX CONCURRENTLY, add the line -- dbdiff:no-transaction to the file.

To use an existing server instead of a temporary server, give its connection URL with --scratch-database, or with the key scratch-database. dbdiff then downloads nothing and starts no server. For each SQL side, dbdiff creates a database with a name that starts with dbdiff_. At the end, dbdiff drops the database, also when the diff fails. The user of the URL must have the privilege to create and drop a database. If both sides are SQL files and --driver is not set, the scheme of the URL selects the driver. Do not use --scratch-database together with --server-version.

dbdiff diff --scratch-database postgres://user:password@localhost:5432/postgres current.sql desired.sql

A scratch database needs none of the server requirements below. It also works for MariaDB on Linux with arm64.

A user or a role belongs to the server, not to a database. If a SQL file creates a user or a role on the scratch server, dbdiff drops the temporary database but the user or the role stays. The next run of the same file then fails. Write CREATE USER IF NOT EXISTS in MySQL. In PostgreSQL, create the role in a DO block that checks pg_roles first.

ln -s /usr/lib/$(dpkg-architecture -qDEB_HOST_MULTIARCH)/libaio.so.1t64 \
      /usr/lib/$(dpkg-architecture -qDEB_HOST_MULTIARCH)/libaio.so.1

dbdiff does not support MariaDB on Linux with arm64.

--data compares the rows by primary key and prints INSERT, UPDATE, and DELETE statements. dbdiff skips a table with no primary key.

dbdiff reads every row of the two sides into memory. Use --data for small tables, for example reference data and seed data.

dbdiff migrate reads dbdiff.yaml from the working directory. Each key has the name of its long flag. A flag overrides its key. The keys are driver, migrations, desired, database, schema, server-version, scratch-database, and ignore-table. --config, --to, and --run have no key.

dbdiff diff also reads dbdiff.yaml. It uses only the keys driver, schema, server-version, scratch-database, and ignore-table, and it ignores the other keys. Give the two schemas of diff as arguments.

driver: postgres
migrations: ./migrations      # migration files
desired: ./schema.sql         # desired schema, for `migrate generate`
database: ${DATABASE_URL}     # database, for the other commands
schema: app
server-version: 17.11.0       # temporary server version
ignore-table:
  - example

dbdiff replaces each ${NAME} in a value of the file with the environment variable NAME. If the variable is not set, dbdiff stops with an error. dbdiff replaces only the form ${NAME}. It does not replace $NAME, and it does not replace a value of a flag. A value of the file cannot hold the literal text ${NAME}.

migrate generate writes the difference between the migration files and the desired schema as a new file:

$ dbdiff migrate generate add_created_at
migrations/20260822143000_add_created_at.sql

For a data change, add the --empty flag. generate then writes a file with no statement, and it does not read the desired schema. Write the statements in the file.

$ dbdiff migrate generate --empty backfill_users
migrations/20260822143100_backfill_users.sql

The other commands act on the database that --database or the database key names:

export DATABASE_URL=postgres://user:password@localhost:5432/production
dbdiff migrate up

• migrate up applies each file in its own transaction and records it in dbdiff_migrations. --to <version> stops after that version.
• On MySQL, and in a file with -- dbdiff:no-transaction, a file that stops before its end can leave part of its changes. If this occurs, repair the database. Then run migrate repair.
• migrate baseline records the files as applied, and runs no file. If the database already has the schema, use this command.

Run a diff:

import "github.com/quantumsheep/dbdiff/drivers"

driver, err := drivers.NewDriver(drivers.SQLiteDriverName)

statements, err := driver.Diff(ctx,
	drivers.NewConnectionStringDataSource("app.sqlite"),
	drivers.NewFileDataSource("schema.sql"),
)

fmt.Println(statements.String())

Apply the migrations, for example at server start:

import (
	"github.com/quantumsheep/dbdiff/drivers"
	"github.com/quantumsheep/dbdiff/migrations"
)

driver, err := drivers.NewDriver(drivers.PostgresDriverName)

migrator := driver.Migrator(
	migrations.WithDatabase(
		drivers.NewConnectionStringDataSource(os.Getenv("DATABASE_URL")),
	),
	migrations.WithMigrations("migrations"),
)

err = migrator.Up(ctx)

✅: dbdiff supports the object. ➖: the engine has no such object.

• PostgreSQL: dbdiff compares one schema per run. It does not print CREATE SCHEMA. If a table of the schema has a parent table in another schema, dbdiff cannot create that table or change its parents.
• SQLite: SQLite cannot change a virtual table. If the module or its arguments change, dbdiff drops the virtual table and creates it again, and the new table holds no row.
• MySQL: dbdiff compares one database per run. --privileges compares only the grants of the database and of the tables.
• The mysql client splits the bodies of triggers and routines at each semicolon. To apply such a diff, use dbdiff migrate up.
• dbdiff has no down migrations. This is a design choice. A rollback is a new up migration that reverses the change. To roll back, change the desired schema back, run dbdiff migrate generate, then run dbdiff migrate up.

docker compose up -d     # Start PostgreSQL, MySQL, and MariaDB
go test ./...

# Without the containers
DBDIFF_TEST_SKIP_POSTGRES=1 DBDIFF_TEST_SKIP_MYSQL=1 go test -short ./...

MIT