Schema migration tools exist for a good reason: applying a sequence of database changes deterministically across environments, without forgetting one or applying them out of order, is a hard problem at scale. Tools like Alembic, Flyway, and Liquibase are well-engineered solutions to that problem.
They are also overkill for a small API.
If you have one database, three environments at most, and a team measured in single digits, you can replace these tools with about thirty lines of Python or Go and a folder of .sql files. We have run four production APIs this way for months, with about forty migrations applied across them, and nothing has gone wrong. Here is the pattern.
The minimum viable migration system
You need three things: a way to know which migrations have run, a way to apply new ones, and a way to fail loudly if anything goes wrong. That is it.
The "which have run" part is one table:
CREATE TABLE IF NOT EXISTS schema_migrations (
version TEXT PRIMARY KEY,
applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Every migration is a file like migrations/0007_add_idempotency_keys.sql. The leading zero-padded number is the version. The script reads the folder in order, checks each filename against the table, and applies any that are missing.
The runner, in full
This is the entire migration runner for a SQLite-backed Python service. It is forty lines:
import os, sqlite3, sys, glob
def run_migrations(db_path, migrations_dir):
conn = sqlite3.connect(db_path)
conn.execute("""
CREATE TABLE IF NOT EXISTS schema_migrations (
version TEXT PRIMARY KEY,
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
applied = {row[0] for row in conn.execute("SELECT version FROM schema_migrations")}
files = sorted(glob.glob(os.path.join(migrations_dir, "*.sql")))
for path in files:
version = os.path.basename(path).replace(".sql", "")
if version in applied:
continue
with open(path) as f:
sql = f.read()
print(f"Applying {version}...")
try:
conn.executescript(sql)
conn.execute("INSERT INTO schema_migrations(version) VALUES (?)", (version,))
conn.commit()
except Exception as e:
conn.rollback()
print(f"FAILED: {version}: {e}", file=sys.stderr)
sys.exit(1)
conn.close()
Run this at container startup, before the API starts accepting traffic. If a migration fails, the container exits non-zero and the orchestrator (Docker Compose, Kubernetes, whatever) refuses to mark it as ready. The old container keeps serving. You get paged. You fix the migration. You redeploy.
What you give up
This is the honest part. The simple pattern lacks several things that real migration tools provide:
No down migrations. Every migration is forward-only. If you need to roll back a schema change, you write a new migration that reverses it. This sounds inconvenient but is actually a feature: it forces you to think of every schema change as a one-way commit, the same way you should think about deployments.
No transactional DDL guarantees. SQLite happens to wrap executescript in a transaction; PostgreSQL does too if you keep your DDL in a single statement. But cross-statement transactional rollback for failed migrations is something you do yourself, not something the tool guarantees.
No multi-database support. Tools like Flyway can target ten different database engines from one schema. The simple runner targets one. For a small team this is almost always what you want.
No checksum tracking. Real tools store a hash of each migration file and refuse to run if the file has changed since application. The simple runner does not — but you can add this in five lines if you find yourself accidentally editing applied migrations.
The patterns that prevent pain
Forward-only migrations work, but only with discipline. The patterns we follow:
Expand-contract for column changes. Renaming a column is two migrations, not one. The first migration adds the new column and copies data over. The application is updated to read from both, prefer new. The second migration, in a later release, drops the old column. This works for any column change: rename, type change, NOT NULL addition. Always two migrations, always with an application release in between.
Backfills outside migrations. Long-running data backfills do not belong in a migration. They block startup, they cannot be safely retried, and they fail in ways that are hard to recover from. Make backfills a separate one-shot script you run manually, with explicit batching and progress logging.
NOT NULL is hard. Adding a NOT NULL constraint to an existing table requires the column to be backfilled and the constraint applied while no concurrent writes are happening. For a small service with a brief deploy window, you can sometimes do this in a single migration. For anything bigger, do it in stages: nullable column first, then backfill, then add constraint.
Indexes need CONCURRENTLY in PostgreSQL. SQLite does not block on index creation. PostgreSQL does, unless you use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction — meaning it cannot be in a wrapped migration. We put concurrent index creations in a separate migrations folder that runs without transaction wrapping.
Numbering, not timestamps
Some teams use timestamps in migration filenames (20260426143000_add_orders.sql) to avoid merge conflicts when two developers add migrations independently. We use sequential numbers (0007_add_orders.sql) and accept the occasional merge conflict.
The sequential numbers are a feature: you can see at a glance how many migrations a service has had, and you can refer to "migration 14" in conversation without anyone having to look it up. Timestamps make migrations into noise.
If two developers add migration 0008 simultaneously, the merge conflict is trivial: rename one to 0009 and we move on. We have had this happen exactly twice.
The "do not edit applied migrations" rule
This is the one inviolable rule. Once a migration has been applied to any environment past development, it does not change. If you find a bug in migration 0007 after it has been applied to staging, you write migration 0008 to fix it. You do not edit 0007.
The consequences of breaking this rule are nasty: production has the buggy version, staging has the fixed version, and the migration table on both says the same thing. You will spend a weekend reconciling.
The rule is enforced by code review and by the file modification timestamp. We have not yet added an explicit checksum check. We probably should.
What we run in production
All four products — DocuMint, CronPing, FlagBit, WebhookVault — use this pattern. Migrations live in app/migrations/ as numbered SQL files. The runner is the forty-line snippet above. Total: about ten migrations per service, all forward-only, all under twenty lines of SQL.
When we eventually need PostgreSQL, the runner needs minor changes (psycopg2 instead of sqlite3, parameter binding) but the philosophy stays. When we eventually need cross-region replication or zero-downtime DDL, we will switch to a real migration tool. Until then, the file system and a tiny script have been entirely sufficient.
The smaller the better
The migration tool you do not have is the migration tool that does not have bugs, does not have a CLI to learn, does not require a config file, and does not break when you upgrade it. For a small service, the cost-benefit of a tool is a losing trade. The cost-benefit of a forty-line runner is a clear win.
This is the same shape as the rest of the early-stage SaaS toolbox: prefer the simplest thing that works. Reach for the tool when the simple thing breaks. Most of the time, it does not break.