Database Versioning: Migrations, Forward-Only vs Down, and Running Them Safely

Database versioning means tracking schema changes as an ordered sequence of migrations, each one a script that alters the schema and gets applied exactly once, recorded in a table the migration tool itself maintains. It is not the same as putting a schema dump in git: a git diff shows what changed in a file, but a migration is what actually ran against a live database, in order, which is the part version control alone cannot express. Tools like Prisma Migrate, Knex, Alembic and Flyway all implement this pattern with different syntax over the same idea.

The explanations that rank tend to be tool-specific, a Flyway guide or an Alembic guide, each good at their own tool and silent on the general pattern underneath. The Stack Overflow and Reddit threads that ask the broader question get decent answers about why migrations beat a version-controlled schema dump, but stop short of forward-only versus reversible migrations, or how any of this fits into an actual deploy pipeline. Here is the whole shape.

Table of contents

• Why a schema dump in git is not enough
• How the tools implement the same pattern
• Forward-only vs down migrations
• Backups before migrate, every time
• Running migrations in a deploy pipeline
• Where this actually runs
• How this fits the rest of the stack
• FAQ

Why a schema dump in git is not enough

Committing schema.sql to git tracks what the schema should look like at a given commit, but it does not tell you what has already been run against the production database, which might be three migrations behind main because a deploy failed partway through last week. Two databases can end up with different actual schemas even while their git history looks identical.

A migration tool solves this by keeping its own record, usually a table named something like schema_migrations inside the database itself, listing every migration that has successfully run. Checking that table, not the git log, tells you the database’s true current state.

How the tools implement the same pattern

Despite different ecosystems, the concept is identical everywhere: an ordered, timestamped or numbered set of scripts, each one runs once, and the tool tracks which have run.

• Prisma Migrate (Node/TypeScript) generates SQL migration files from schema changes you define in schema.prisma, applied with prisma migrate deploy.
• Knex (Node) uses hand-written or generated JS migration files with up and down functions, applied with knex migrate:latest.
• Alembic (Python, usually with SQLAlchemy) autogenerates migration scripts from model changes, applied with alembic upgrade head.
• Flyway (JVM ecosystem, but usable from any stack via CLI) uses versioned SQL files named like V1__create_users.sql, applied with flyway migrate.

Pick the one that matches your application’s language, not the one with the most features; the migration pattern itself is the same regardless, and switching later is painful mainly because of naming and versioning conventions, not the underlying concept.

Forward-only vs down migrations

A reversible migration pairs an up script with a down script that undoes it, letting you roll the schema back a version if something goes wrong. A forward-only approach never rolls back; if a migration causes a problem, you write and apply a new migration that fixes it, rather than reversing the old one.

Forward-only is the more common recommendation for production systems today, because a down migration that drops a column or table is often destructive in ways that are worse than the problem it was meant to fix, silently losing data written after the up migration ran. Keep down scripts for local development convenience, where resetting a dev database is low-stakes, and treat production rollback as deploy a fix, not run the down script.

Backups before migrate, every time

A migration is a schema change running against real data, and any tool can have a bug, hit an unexpected edge case in existing data, or simply be the wrong migration if a deploy is misconfigured. A backup taken immediately before the migration step runs is the one safety net that covers all of those cases at once, cheaper than trying to anticipate every specific failure mode.

This should be a pipeline step, not a manual habit someone remembers to do before big migrations. Every migration is a candidate for something going wrong; treat the backup as mandatory infrastructure, not a judgment call.

Running migrations in a deploy pipeline

The order matters: take the backup, run the migration, then deploy the application code that depends on the new schema, not the other way around. Deploying new code against an old schema, or an old schema against new code mid-migration, is where most migration-related outages actually come from, not the migration script itself.

- name: Backup database
  run: ./scripts/backup-db.sh
- name: Run migrations
  run: npx prisma migrate deploy
- name: Deploy application
  run: ./scripts/deploy.sh

For zero-downtime deploys, migrations should also be backward-compatible with the currently running code for at least one deploy cycle: add a column before the code that uses it ships, and drop an old column only after the code that used it is fully retired. This expand and contract pattern avoids a window where running code expects a schema that does not exist yet.

Where this actually runs

All of this assumes a database that supports transactional DDL and a straightforward backup step, which is exactly the baseline a managed database should provide rather than something you build yourself. On RunxBuild, a managed Postgres or MySQL instance has backups and connection limits already in place, so the migration pipeline step above is a restore point away from a bad migration rather than a hope that a manual dump from last week is still around.

The migration tooling itself, Prisma, Knex, Alembic, Flyway, runs the same way against a managed database as any other; nothing about this pattern is platform-specific, which is exactly why it is worth getting the general shape right once.

How this fits the rest of the stack

A database that migrates safely is one where the backup, the migration, and the deploy are three separate, ordered steps, and where the plan underneath has room for connection limits and storage as the schema and the data both grow. Model that alongside the service that runs the migrations in the RunxBuild hosting calculator. Database backups on RunxBuild covers the restore-point side of this pipeline, and the dashboard is where the managed instance and its connection string live. See Django’s makemigrations for how one specific framework’s migration tool implements the pattern above.

Useful related references:

• Redis vs DynamoDB: Cache, Database, or Both
• MySQL to MySQL: Migrating a Database Between Servers
• Database Pricing: What You Are Actually Paying For, Line by Line
• Databases on RunxBuild

FAQ

What is the difference between database versioning and migrations?

Database versioning is the goal, tracking the schema’s state over time. Migrations are the mechanism: ordered scripts that each run once against the database, recorded in a table the migration tool maintains, which is what actually achieves versioning in practice.

Should database migrations be reversible?

Reversible down migrations are useful in local development, but most production systems today prefer forward-only: fixing a bad migration with a new migration rather than reversing the old one, since a down migration that drops data can be more destructive than the original problem.

When should migrations run in a deployment pipeline?

After a backup and before the application code that depends on the new schema deploys. Running migrations after the new code is live risks the app querying a schema that does not exist yet, which is a common source of migration-related outages.

Why not just version control a schema.sql file?

A file in git tracks what the schema should be at a commit, not what has actually run against a live database, which can drift from git history if a deploy fails partway. Migration tools keep their own record inside the database of exactly what has been applied.

What is the expand and contract pattern in migrations?

Adding new schema elements before the code that uses them deploys, and removing old ones only after the code that used them is fully retired. It keeps the schema backward-compatible with the currently running code during a rolling deploy, avoiding downtime from a schema-code mismatch.