sqldef: Idempotent Database Schema Management

Managing database schemas across environments is a constant source of friction for SRE and DevOps teams. Traditional migration tools require you to write incremental scripts, track migration history, and hope nothing breaks when you apply them in production. sqldef takes a different approach: define your desired state, and let the tool figure out how to get there.

What is sqldef?

sqldef is an open-source, idempotent schema management tool that works with MySQL, PostgreSQL, SQLite, and SQL Server. Instead of writing migration files, you maintain a single SQL file containing your complete schema definition. When you run sqldef, it compares your desired schema against the current database state and generates only the necessary ALTER statements.

The tool ships as a single binary with no dependencies. Each database has its own command (mysqldef, psqldef, sqlite3def, mssqldef) that mimics the connection options of native database clients, making integration straightforward.

Key Features

• Idempotent operations: Run sqldef multiple times safely. If the schema already matches, nothing happens.
• Plain SQL definitions: No DSL to learn. Your schema file is standard SQL DDL.
• Dry-run mode: Preview generated migrations before applying them with --dry-run.
• Offline mode: Compare two SQL files without a database connection, perfect for CI validation.
• Rename support: Use @renamed annotations to handle table, column, and index renames cleanly.

Installation

On macOS with Homebrew:

brew install sqldef/sqldef/psqldef

On Linux, download the binary directly:

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

Docker images are also available on Docker Hub under the sqldef organization.

Usage

Export your current schema as a starting point:

psqldef -U postgres -h localhost mydb --export > schema.sql

Edit schema.sql to add a new column, then preview the changes:

psqldef -U postgres -h localhost mydb --dry-run < schema.sql

When satisfied, apply:

psqldef -U postgres -h localhost mydb --apply < schema.sql

Operational Tips

Integrate sqldef into your CI/CD pipeline using offline mode to validate schema changes before they reach production. The project also provides a GitHub Action that posts schema diffs as PR comments, giving your team visibility into database changes during code review.

Conclusion

sqldef removes the complexity of incremental migrations by treating your database schema as declarative infrastructure. For teams already using Infrastructure as Code practices, this approach feels natural and reduces the cognitive load of managing database changes.

Looking for a platform that brings the same declarative, automated approach to your entire infrastructure? Akmatori helps SRE teams manage incidents and automate runbooks, while Gcore provides the global edge infrastructure to keep your applications fast and reliable.