DEV Community

Cover image for SQL-First PostgreSQL Migrations Without the Magic
alexey.zh
alexey.zh

Posted on

SQL-First PostgreSQL Migrations Without the Magic

#go #database #postgres

If you work with PostgreSQL long enough, you start noticing a pattern: migration tools often become more complicated than the schema changes they are supposed to manage.

Some tools invent their own DSL.
Some hide behavior in config files.
Some couple migrations to an ORM.
Some force a directory layout that looks neat in a demo but awkward in a real project.

And then there is the simpler question:

Why can’t PostgreSQL migrations just stay plain SQL?

That is the idea behind gopgmigrate.

It is a SQL-first migration tool for PostgreSQL that keeps the core workflow boring in the best possible way:

  • write normal .sql files
  • organize them however you want
  • run them in order
  • track what was applied
  • support rollbacks
  • support repeatable migrations
  • make non-transactional migrations explicit

No YAML. No hidden DSL. No ORM lock-in. No magic comments.

Just SQL files and a clear naming convention.

Why this approach matters

A migration file should be easy to:

  • read in a code review
  • open in your editor
  • run directly with psql
  • troubleshoot at 2 AM
  • keep using even if you stop using the tool

That last point matters more than many teams realize.

A good migration format should outlive the tool that executes it. Your schema history is long-term infrastructure. It should not depend on a framework-specific abstraction that becomes painful to migrate away from later.

With gopgmigrate, the migration files remain usable as ordinary SQL. The tool adds safety and structure on top, but it does not take ownership of your database change process.

What gopgmigrate does

At a high level, the workflow is simple:

  1. Scan a directory tree recursively for SQL migration files
  2. Sort them globally by revision
  3. Compare them with the migration history stored in PostgreSQL
  4. Apply only what is pending
  5. Record hashes and metadata for auditability
  6. Support rolling back the last applied migrations
  7. Re-run repeatable scripts only when their content changes

That gives you a clean PostgreSQL migration workflow with a small mental model.

The naming convention is the API

One of the nicest design choices in gopgmigrate is that the file name itself declares the migration behavior.

Example:

0000001-create-users-table.up.sql
0000001-create-users-table.down.sql
0000003-fn-get-users.r.up.sql
0000004-vacuum-users.notx.up.sql
0000005-refresh-stats.rnotx.up.sql
Enter fullscreen mode Exit fullscreen mode

This is refreshingly explicit.

Versioned migrations

These run once in order:

0000002-add-roles-table.up.sql
Enter fullscreen mode Exit fullscreen mode

Rollbacks

Rollback files are separate and predictable:

0000002-add-roles-table.down.sql
Enter fullscreen mode Exit fullscreen mode

Repeatable migrations

Useful for functions, views, triggers, or other SQL objects you may want to refresh when the file changes:

0000003-fn-get-users.r.up.sql
Enter fullscreen mode Exit fullscreen mode

Non-transactional migrations

Some PostgreSQL operations cannot run inside a transaction, for example:

  • VACUUM
  • CREATE INDEX CONCURRENTLY
  • DROP INDEX CONCURRENTLY
  • some forms of REINDEX
  • ALTER SYSTEM

Those are made explicit in the file name:

0000004-vacuum-users.notx.up.sql
Enter fullscreen mode Exit fullscreen mode

And if a migration is both repeatable and non-transactional:

0000005-refresh-stats.rnotx.up.sql
Enter fullscreen mode Exit fullscreen mode

This is a small detail, but it solves a real operational problem: the migration behavior is visible before you open the file.

Real projects are not flat folders

A lot of migration tools quietly assume every team wants the same directory structure.

Reality is messier.

Some teams want to split:

  • schema
  • data
  • functions
  • maintenance
  • environment-specific files
  • release-based groups

That is why I like that gopgmigrate does not force a rigid directory layout.

You can organize migrations by concern:

migrations/
  schema/
  data/
  functions/
  no-transaction/
  down/
Enter fullscreen mode Exit fullscreen mode

Or by release:

migrations/
  v1.0.0/
  v1.1.0/
  down/
Enter fullscreen mode Exit fullscreen mode

Or however your team naturally thinks about database changes.

The only rule is that version ordering remains global.

That is a practical compromise: freedom in layout, predictability in execution.

Why SQL-first migrations are still the best default

There is a reason SQL-first tools keep appealing to engineers who work close to PostgreSQL.

PostgreSQL already has a powerful language for schema and data changes. It is called SQL.

When a tool stays out of the way, you get a few concrete advantages:

Better reviewability

A migration diff is just SQL. Reviewers do not have to mentally decode a framework abstraction.

Better portability

You can run the file with psql, a database IDE, automation scripts, or CI jobs.

Better debugging

When something fails, you are looking at the actual statement PostgreSQL rejected.

Better longevity

Your migration history remains useful years later, even if your application stack changes.

That makes SQL-first migration tooling especially attractive for:

  • platform teams
  • backend teams with multiple services
  • teams that avoid ORM-heavy workflows
  • projects with long-lived PostgreSQL databases
  • teams that want plain operational ownership

Safety features that matter in practice

Simple does not mean naive.

For a migration tool to be usable in production, it needs a few guardrails. gopgmigrate includes some of the right ones:

Advisory locking

This helps prevent concurrent migration runs from stepping on each other.

Transactional safety by default

Most PostgreSQL DDL can run inside a transaction, and that is the safe default.

Explicit non-transactional mode

Instead of hiding exceptions, the tool makes them obvious in the filename.

Hash-based change detection

This is particularly useful for repeatable migrations. If the content changes, the tool knows it should re-apply the script.

History tracking

Applied migrations are recorded in a history table, along with metadata such as hash and timing-related details.

That is the kind of boring reliability you want from migration tooling.

Example CLI workflow

The CLI is intentionally straightforward.

Apply pending migrations:

gopgmigrate migrate \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb
Enter fullscreen mode Exit fullscreen mode

Preview without applying:

gopgmigrate migrate \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb \
  --dry-run
Enter fullscreen mode Exit fullscreen mode

Rollback the last migration count:

gopgmigrate rollback-count 2 \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb
Enter fullscreen mode Exit fullscreen mode

Use environment variables in CI:

export PGMIGRATE_DIRNAME=./migrations
export PGMIGRATE_CONNSTR=postgres://user:pass@localhost:5432/mydb

gopgmigrate migrate
Enter fullscreen mode Exit fullscreen mode

That is the kind of interface that works well in local development, CI pipelines, containerized jobs, and release automation.

Where this fits especially well

I think gopgmigrate is especially appealing in a few scenarios.

1. PostgreSQL-first teams

If your team understands PostgreSQL and prefers direct SQL over framework migration layers, this fits naturally.

2. Teams with mixed migration types

Schema changes, data fixes, repeatable view/function refreshes, and non-transactional maintenance are all first-class cases here.

3. Repos with real structure

If your migration directory stopped being a cute flat demo folder a long time ago, recursive scanning and flexible layouts are genuinely useful.

4. CI/CD and automation

The CLI is simple enough to drop into pipelines without teaching your delivery system a new configuration language.

5. Engineers who dislike lock-in

Your migration files stay plain SQL. That is a strong long-term property.

What I like most about this design

The best tools often win not because they do more, but because they make fewer damaging decisions for you.

gopgmigrate seems built around a healthy principle:

the tool should manage execution, not redefine how SQL migrations ought to exist.

That means:

  • your files remain readable
  • your shell workflows still work
  • your database knowledge stays relevant
  • your migration history does not become framework glue

In database tooling, that is a strong design choice.

Final thoughts

There are plenty of PostgreSQL migration tools out there. Many are good. But a lot of them drift toward abstraction for its own sake.

If what you want is:

  • PostgreSQL migrations
  • plain SQL files
  • explicit rollbacks
  • repeatable migrations
  • non-transaction support
  • advisory locking
  • transactional safety
  • hash-based change detection
  • flexible directory layouts
  • clean CLI usage
  • minimal ceremony

then gopgmigrate is worth a look.

It takes a very practical path: keep migrations human-readable, keep behavior explicit, and keep the tool small enough that you can trust what it is doing.

That is a solid direction for database change management.

If you find gopgmigrate useful, consider giving the repo a star on GitHub. It helps more people discover the project.

Repository: https://github.com/hashmap-kz/gopgmigrate

Top comments (0)