Why Your Migration Files Might Be Working Against You

You've already done the hard part of realizing that untracked schema changes are a liability. You've probably read something like Your Code Is Versioned. Your Database Schema Is Not. and nodded along. Now you're in a different spot: you're weighing tools and approaches, and the question isn't whether to version your schema but how. That's a harder question, and the answer depends more on your team's workflow than on any feature list.

The dominant approach today is migration-file tooling. Flyway, Liquibase, golang-migrate, and the built-in migration runners in frameworks like Rails or Django all follow the same basic idea: you write a numbered SQL file that describes a transition (add this column, drop that index), run it once, and the tool records that it ran. This works well when your team is disciplined, your migrations are linear, and you rarely need to know what the schema actually looks like right now without running the whole history. The weakness shows up when you need to answer a simple question like "does staging have the same schema as production?" You can't easily diff that without running both histories and hoping no one applied a manual fix in between.

State-based tools take a different angle. Instead of recording transitions, they store a snapshot of what the schema is supposed to look like, then compute the necessary SQL to get from the current state to that target. SchemaLens is one example of this approach for PostgreSQL: it keeps declarative SQL files (one per object) in a `schema/` directory, and a `schemalens diff` tells you exactly what's different between two environments or between your files and a live database. The trade-off is real, though. State-based tools are generally better at environment comparison and catching drift, but they require more care when a transition itself matters, like a multi-step data migration where you need precise control over the order of operations. In those cases, a raw migration file is often clearer and safer.

Here's a concrete scenario worth thinking about. Imagine your team has three environments: local dev, staging, and production. A developer adds a new RLS policy on a table in dev, forgets to tell anyone, and staging gets deployed without it. Two weeks later, a bug report surfaces. With a migration-only tool, finding that gap means auditing applied migration IDs across environments, which is tedious and error-prone. With a state-based tool, you run a cross-environment diff and the missing policy shows up immediately. On the flip side, if that same developer needed to backfill a column with computed values before making it NOT NULL, a migration file gives them a precise, ordered script they control step by step. Neither approach is universally better. They solve different failure modes.

The criteria a thoughtful team should use come down to a few honest questions. How often do you need to know what your schema actually is right now, not what it should be after applying N migrations? Do you have multiple long-lived environments that can drift independently? Do you track objects like RLS policies, triggers, and custom functions, or just tables and columns? Migration-only tools tend to treat those object types as afterthoughts. If your schema is mostly tables and indexes, the gap matters less. If you're using PostgreSQL's more advanced features, the gap matters a lot.

It's also worth thinking about team size and discipline. Migration files are append-only and concrete, which makes them easy to audit in a pull request. State-based files are declarative and compact, which makes them easier to read but require the tooling to generate the right diff reliably. Trusting that generation is a fair thing to verify before you adopt any state-based tool. Ask to see how it handles a rename (which is inherently ambiguous in any diffing system) and what happens when a deployment partially fails. Transactional rollback on failure is a reasonable baseline to expect.

Before you make a final call, two questions are worth sitting with. First: does your current tool let you confidently answer "are dev, staging, and prod in sync right now?" without manually comparing migration logs? If not, that gap will keep causing incidents. Second: when something goes wrong in production at 11 PM, does your schema tooling make the situation clearer or more complicated? The answer to that second question probably tells you more about fit than any feature comparison will.