Websites · Glossary
What is a database migration?
A database migration is a script, usually SQL, that changes a database's structure in one step: creating a table, adding a column, writing an access policy. Migrations are numbered or timestamped, stored with the application's code and applied in order, so any copy of the database can be brought to the same shape and every change has a history.
Migrations turn database changes into code. Instead of clicking changes into a live database and hoping to remember them, each change is a file that can be reviewed, repeated and rolled forward.
Indunil Asanka · Co-founder
4 min read · Published
| Approach | History kept | Repeatable on a new copy | Risk |
|---|---|---|---|
| Migration files in the repo | Yes, one file per change | Yes, apply the files in order | Low, if each file is reviewed |
| Editing the live database by hand | No | No | High; changes are forgotten or differ between copies |
| Restoring a schema dump | Only the latest state | Yes, but loses the step by step history | Medium; data changes are not captured |
What it is
A migration describes one change to a database schema. Tools differ, but the pattern is the same: a folder of files named with a timestamp or sequence number, each containing SQL such as create table, alter table add column or create policy. Supabase's command line tool, for instance, creates a file in a supabase/migrations folder named with a timestamp and a description. The database records which migrations it has applied, so running the tool again applies only the new ones.
How it works
A developer, or an agent, writes the change as a new migration file, applies it to a development database, tests the application against it, and commits it with the code that uses it. In production the same file is applied in order. Many teams also keep a schema file that shows the current shape of the whole database, regenerated after each migration, because reading fifty migration files to understand one table is slow. Migrations that change data as well as structure, such as filling a new column, need extra care, because they run against real rows.
When it matters
As soon as a database outlives a single afternoon. A site's database changes every time a feature is added: a bookings table, then a deposit column, then a policy so members see only their own bookings. Without migrations, nobody can rebuild the database from scratch, a test copy drifts away from the live one, and a broken change cannot be traced. With them, the repository holds the full history, and a new environment can be created by applying every file.
Common mistakes
Editing an old migration after it has been applied, so environments disagree about what it did. Making changes in the dashboard and never writing them down. Writing migrations that lock a large table for minutes during busy hours. Dropping a column the running code still reads. Forgetting the access policies, so a new table is created without row level security. Mixing a schema change and a large data backfill in one file. And never testing a migration on a copy before running it live.
Where it shows up in OneCraft
When the OneCraft agent builds a capability that needs data, it creates the tables, policies and functions itself, and the migrations and a supabase/schema.sql file are written into the site's own repository alongside its code. Every table must have row level security, and the agent checks each new table as an anonymous visitor and as a signed in person before a generation step can finish. Every change to the site is a commit, and the newest 50 commits appear in Versions.
Questions people ask
Can a database migration be undone?
Some tools support down migrations that reverse a change, but many teams roll forward instead: they write a new migration that fixes or reverses the last one. Dropping a column or table cannot be undone without a backup, so destructive changes deserve a backup and a second look before they run.
What is the difference between a migration and a seed?
A migration changes the structure of the database: tables, columns, indexes, policies. A seed inserts rows, usually sample or reference data, into that structure. Migrations run in every environment including production; seeds usually run only in development and test databases, apart from fixed reference data.
Do I need migrations for a small website?
If the site has a database that changes over time, yes, even a small one. A handful of migration files costs nothing and means you can recreate the database, move to a new host or see exactly when a table changed. Sites without a database have nothing to migrate.
What does migration mean when moving a whole database?
The same word is used for moving a database from one server or provider to another, such as from MySQL to Postgres or from one host to another. That is a data migration project. Schema migrations, the subject of this page, are the small versioned changes made during normal development.
Why did my migration fail in production but not locally?
Usually because production has data the development database does not: duplicate values that break a new unique constraint, nulls in a column made required, or a large table that times out. Test migrations against a recent copy of production data, and split risky changes into smaller steps.
Make one with websites
The button opens the generator with this use case already described. Change the wording to match your own.
Create a website with OneCraftRelated questions
- What is seed data?What is seed data? Rows inserted into a new database so it has something to show and test: seed data compared with fixtures, reference and production data.
- What is a Postgres schema?What is a Postgres schema? A named namespace of tables, views and functions inside one database, how search_path finds them, and how schemas separate tenants.
- What is a connection pooler?What is a connection pooler? A proxy that lets many clients share a few Postgres connections. Session and transaction pooling compared, with examples.
- What is a Stripe webhook?What is a Stripe webhook? Stripe's signed message to your site when a payment event happens, the events a small shop needs and how to handle them safely.
Written and checked by the OneCraft team. Last checked .