Jan Mikoláš
Glossary
Database migration
A database migration moves an application schema safely between versions. It is more than an SQL file: it must be readable, tested, operationally sound, and compatible with the code being deployed.
Short definition
A history of schema changes instead of manual database edits.
A database migration is a small step that describes the transition from a known schema to its next version: creating a table, adding a column, index, or constraint, or transforming data deliberately. The migration tool records which versions a particular database has already applied and runs the missing steps in the correct order.
A migration is not the same as automatic entity synchronisation or SQL run ad hoc from an administration interface. A schema change is reviewed and tested as version-controlled code. Developers can therefore assess its impact on older and newer application versions, table sizes, indexes, database locks, and the recovery plan if deployment fails.
What it is used for
Evolving a schema without manual drift between environments
Migrations give a database change the same traceability as an application-code change.
- new tables, columns, enum values, indexes, and database constraints
- extending the order process with a status, external identifier, or tracking number
- moving and backfilling data when the model changes, provided the operation is safe and reasonably small
- aligning local development, test databases, staging, and production on the same schema version
- an auditable basis for review, deployment, and resolving an incident caused by a data change
Practical example
Adding a tracking number without interrupting orders
An online store needs to add a carrier tracking_number to orders. A safe first step creates a new nullable column and any supporting index, but does not yet require a value for old orders. The new code can write the value and accepts that an older order may not have it when reading.
Historical data is backfilled in batches by a separate monitored process, not with one long UPDATE in the middle of a critical deployment. Only after the application and data are ready does another separate change follow, such as adding NOT NULL or a unique constraint. This approach divides the risk and allows a return to the previous application version without old code encountering an unknown schema.
Change process
Expand, migrate, contract: the schema and application change gradually
A live application is not a one-time database installation. A safe change leaves room for both old and new code for a period of time.
- Impact analysis Identify the affected tables, data volume, constraints, access patterns, and whether two application versions will see the changed schema at the same time.
- Expand The first migration adds a compatible structure, such as a nullable column or new table. Old reads and writes can continue to work.
- Deploying code The application starts using the new structure while remaining tolerant of earlier data. It writes to both the old and new locations when needed.
- Transforming data A backfill runs in manageable batches outside the critical request path. Progress, errors, and database load are monitored.
- Contract Only after verification can the old column be removed, a constraint tightened, or the code simplified. A destructive step belongs in a separate, deliberate change.
Main components and concepts
A schema change has both technical and operational dimensions.
A tool creates a history of applied steps, but cannot know the operational risk of a particular table and application by itself.
Version and history
Every migration has a unique identifier, and the tool records whether it has run. This is more than file order: the history helps trace the database state from which a change began.
Schema versus data
Adding a column changes the schema. Recalculating millions of rows is a data operation with a different load, duration, and retry behaviour; it often belongs in a separate process.
Up and down
Some tools can describe both forward and reverse steps. Down is not an automatic rollback guarantee: lost data, a completed backfill, or a value already used externally may not be safely reversible.
Automatically generated diff
A diff between the model and database can speed up an initial draft. The generated SQL must always be reviewed because the tool does not know the data’s meaning, the operating window, or the intent of the change.
Transactions, locks, and time
Support for transactional DDL and index behaviour varies by database and operation. Even correct SQL can cause waits on a large table; the plan, tests, and a suitable deployment window decide.
Benefits and limitations
Traceability does not remove the risk of a change.
Benefits
- the same traceable schema across development, testing, and production
- a reviewable record of changes to tables, indexes, and database rules
- the ability to plan a compatible rollout instead of one large switch
- reproducible creation of a new environment for an older application version
Risks and common mistakes
- a destructive change without a backup, verification, and realistic recovery plan
- a long backfill or index build in the critical request path
- relying on an automatically generated migration without reviewing the resulting SQL
- deploying code that gets ahead of the schema or cannot read existing data
When it makes sense
Once a database is shared across environments or people, a manual process is no longer enough.
Migrations are practical for an API, online store, internal system, or small application with staging or long-term development. They bring order when a team adds a constraint, changes an integration, and needs both the development database and production to have an exactly explainable state.
A full migration process may be excessive for a one-off prototype, but manual changes quickly lose their history. With larger or sensitive data, the right question is not merely “can the tool create a column?” but “what happens during concurrency, rollback, an outage, or a slow data transformation?”
What to keep in mind
Every step must be understandable to both the database and operations.
Before production, verify the migration contents, the data shape, and the step’s order relative to the code being deployed.
- run and test migrations on a copy or realistically sized staging schema
- review the resulting SQL, dry run, duration, indexes, constraints, and database locks
- expand the schema compatibly and remove the old structure only in a follow-up step
- perform large data transformations in monitored batches with resumability and an idempotent result
- have a tested backup, recovery procedure, and clear deployment owner for a destructive change
Common questions
Migrations in a long-running application
Is an automatically generated migration enough?
No. A generator can be a useful starting point, but it does not know the data’s meaning, the table load, or old-code compatibility. The resulting SQL and deployment sequence require review.
Can every migration be safely reverted with down?
Not always. Deleted data, a completed transformation, or a change already used by another system may not have a safe technical inverse. A planned application rollback and data recovery matter more.
Should a large backfill be in the same migration as a new column?
Usually not. A long transformation is better run in batches outside the critical path, with monitoring and the ability to retry only the unfinished portion.
Can a migration block production traffic?
Yes, depending on the database, change type, table size, and concurrency. Changes should therefore be tested in a representative environment, planned, and divided into smaller compatible steps.
How I work with databases in practice
I design data-model changes around how the application is deployed and operated.
In e-commerce and integration projects, I design the data model, transactions, imports, and a safe schema-change process without unnecessary operational risk.