Database DevOps · Schema migration
Database schema migration: how to change a live schema safely, and how to get back
A database schema migration is a controlled change to the structure of a database: a new column, a changed index, a rewritten view. It is not the same as moving data to a new platform. This guide sets out the two approaches, how migration scripts should be written, the ways back when a change fails, and where Redgate's Flyway fits for teams in Saudi Arabia.
Published 9 min read
Orientation
At a glance
The whole page in one table, before the detail.
- What it is
- A versioned, reviewed change to tables, indexes, views, procedures or permissions
- What it is not
- Moving data between databases or platforms; that is a data migration
- Two approaches
- Migration-based (ordered scripts) or state-based (a model of the target schema)
- Common risks
- Drift between environments, changes that lock tables, no tested way back
- Tool Interkey implements
- Redgate Flyway: versioned, repeatable, undo and baseline migrations
- Platforms
- SQL Server, Oracle, MySQL, PostgreSQL and more, by Redgate's list
The problem in plain words
- The usual situation: schema changes are written by hand, applied at night by one person, and nobody is sure every environment received the same ones.
- What goes wrong: a change that worked in test locks a large table in production, or fails halfway, and the only way back is a restore.
- What a migration discipline changes: every change is a numbered script, reviewed, applied once in order by a tool, and recorded in the database.
- The decision for leaders: treat the way back as part of every change, planned before it ships, not after it fails.
What a database schema migration is, and what it is not
A schema migration changes the shape of a database while its data stays in place: add a column, split a table, change a constraint. Each change is a script with a version. A tool applies pending scripts in order and records each one, so any database can say which version it is at.
A data migration is a different job: moving records from one database or platform to another, often while both run. That work, its downtime and its validation are covered under data and database migration and handled with iblync, Interkey's migration and replication platform. The two meet once: the new target should be under schema migration from its first day.
Migration-based vs state-based schema changes
Both approaches keep the schema in version control. They differ in what you write and what the tool works out for you.
| Migration-based | State-based | |
|---|---|---|
| What you write | Each change as a numbered script | The desired definition of each object |
| How the deployment script appears | It is the reviewed script itself | A comparison tool generates it from the difference |
| Strength | What runs in production is exactly what was reviewed, in that order | An object's full definition sits in one file, so its history is easy to read |
| Weak point | An object's history is spread across many scripts | A rename or a column split can be read as drop and create, so the generated script must be checked |
| Fits well when | Changes involve data, renames or large tables | Many people edit procedures and views |
| Common in practice | Versioned scripts for tables and data | Repeatable scripts or a schema model for code objects |
Most teams use both: migration scripts for tables and data, and a state-based view of code objects. Redgate's documentation says Flyway works with migrations and, for platforms including MySQL, PostgreSQL and SQL Server, can also capture a schema model. The basics of keeping a schema in a repository are on our database version control page.
Flyway migrations: versioned, repeatable, undo and baseline
Flyway picks up scripts by their file names. The prefix tells it what kind of migration each one is.
| Type | Example file name | When it runs | Use it for |
|---|---|---|---|
| Versioned | V001.002__NewTwitterColumn.sql | Once per database, in version order | Tables, columns, constraints, data fixes |
| Repeatable | R__SetDefaults.sql | After pending versioned migrations, whenever its checksum changes | Views, procedures, functions, packages, reference data |
| Undo | U001.002__UndoNewTwitterColumn.sql | Only when the undo command is run | Reversing the versioned migration with the same version |
| Baseline | B5__my_database.sql | On new environments only | Building a fresh database without replaying old history |
Example names are from Redgate's documentation on versioned, repeatable, undo and baseline migrations, read on 10 November 2026. Flyway stores each script's checksum in flyway_schema_history, so an applied versioned script that is later edited is detected. Redgate marks undo migrations for its paid editions, not Community; editions are compared on the Flyway editions page.
Writing database migration scripts that survive production
The tool applies whatever it is given. These habits decide whether a change is safe:
- One purpose per script. A small change is easier to review, test and reverse.
- Never edit an applied migration. Redgate's advice is to roll forward with a new script once a migration has reached a permanent environment.
- Expand, then contract. Add the new column or table, move readers and writers to it, and remove the old one in a later release, so each application version still works.
- Make repeatable scripts safe to re-run. Use
CREATE OR REPLACEor its equivalent on your platform. - Move large data in batches. A single update on a large table can lock it and fill the log.
- Know your platform's DDL behaviour. Some engines run structure changes inside a transaction; others commit each one, so a failed script can stop halfway. Check your engine's documentation.
- Write the reverse at the same time. Run it in a lower environment before the forward change goes to production.
Schema drift: when a database stops matching its scripts
Where drift comes from, and what to do about it
Drift is any difference between what the migrations say and what a database holds. It appears when someone fixes production by hand, or when a test environment is built from an old backup. Drift breaks migrations in surprising ways: a script that adds a column fails because the column is already there.
Check for drift before each deployment, not after an incident. Redgate lists drift detection and change reports for Flyway Enterprise, and our database CI/CD page shows how those reports sit in a pipeline. A person still decides whether a difference was deliberate.
Relateddatabase CI/CD
Rollback scripts and other ways back: a decision table
An undo script is one way back, not the only one. Choose per change, before it ships.
| Way back | Works well when | Limits |
|---|---|---|
| Undo script | The change was structural and completed fully | Cannot bring back dropped or overwritten data; assumes the whole migration succeeded |
| Roll forward | The fault is small and a corrective script can be reviewed quickly | Needs a fast, trusted pipeline to apply the fix |
| Backward-compatible change | Expand and contract was used, so the old application version still works | Takes two or more releases to finish a change |
| Restore a backup | Data was damaged and nothing else will recover it | Loses changes made since the backup; must be rehearsed |
| Feature flag in the application | The schema is fine and new behaviour is the problem | Does not reverse the schema change itself |
Redgate's own undo documentation gives the same caution: undo scripts suit structure better than data, and it recommends keeping the database compatible with every application version in use, backed by a tested backup and restore plan.
Schema migration in Saudi Arabia: test data, records and support
Three things shape this work for organisations in the Kingdom. First, migrations are tested against copies of the database, and those copies often hold personal data. Saudi Arabia's Personal Data Protection Law, overseen by the Saudi Data and Artificial Intelligence Authority (SDAIA), applies to those copies as it does to production, so build and test databases should hold masked or generated data. Your compliance owner confirms what applies from SDAIA's own texts.
Read more
Second, internal audit asks who changed a database, when and with whose approval. Numbered migrations, the history table and pipeline approvals answer that as a by-product of the change itself.
Third, support. Interkey's engineers work from Riyadh, in Arabic and English, alongside your DBAs. Flyway runs on your own servers and pipelines, so your databases and their data stay where they are today; nothing about data residency changes because the change process does.
RelatedSaudi Data and Artificial Intelligence Authority (SDAIA)
Where Flyway fits, and where it does not
Flyway is the schema migration tool Interkey implements. Redgate lists Oracle, SQL Server, MySQL and PostgreSQL among the databases it supports, with version ranges on its supported databases page, and states more than 60 platforms in all. One tool and one naming scheme can therefore cover a mixed estate. Flyway runs from the command line or Docker, so it fits the pipeline you already have; the practice around it is described under database DevOps.
Read more
Where it does not fit: Flyway does not move data between platforms, so a move to a new engine needs a data migration tool as well. It does not tune slow queries. And it applies any migration it is given, safe or not; the script habits above are what make a change safe. If a team has one database, few changes and no audit questions, a lighter process may be enough, and we will say so.
How Interkey helps
Redgate makes Flyway; Interkey does the work around it. Redgate licensing is quoted at Redgate's terms beside our work, in one proposal. As an estimate, a first schema takes weeks; the full estate depends on the number of databases, their drift and how many changes still need a maintenance window.
-
Capture the baseline.
Record production as the starting version and compare each environment to it.
-
Choose the approach.
Agree which objects are migration-based and which are state-based, and the naming rules.
-
Pilot one schema.
Put Flyway on one busy schema in a lower environment, through your pipeline.
-
Rehearse the way back.
Pick the way back for each change type and run it before production.
-
Hand over or operate.
Write the standards, train the team, or run the process for you from Riyadh.
Questions buyers ask about schema migration
What is the difference between a schema migration and a data migration?
A schema migration changes the structure of a database while the data stays in place. A data migration moves records from one database or platform to another. A platform move usually needs both: the data moved, and the new schema under migration control from day one.
Should our team use migration-based or state-based schema changes?
Most teams use both. Numbered scripts suit tables, renames and data changes, because what runs is what was reviewed. A state-based model suits views and procedures that many people edit. We agree the split per object type with your team.
How do we roll back a schema migration that failed in production?
Choose the way back before the change ships: an undo script for a structural change that completed, a roll-forward fix for a small fault, or a backup restore when data was damaged. Changes written as expand and contract usually need no rollback at all.
Can one tool manage schema migrations on Oracle, SQL Server and PostgreSQL together?
Yes. Redgate lists Oracle, SQL Server, MySQL and PostgreSQL among the databases Flyway supports, so one naming scheme and one history table pattern cover a mixed estate. Supported versions differ per engine; we confirm yours during scoping.
What does schema migration tooling cost a company in Saudi Arabia, and how long does it take?
Two parts: the Redgate licence, on Redgate's terms, and Interkey's work, scoped per estate. As an estimate, a first schema takes weeks; the full estate depends on the number of databases, their drift and the edition you need.
Does our data leave the Kingdom if Interkey sets up schema migrations for us?
No. Flyway runs on your servers and pipelines, so databases and data stay where they are. Interkey's engineers support the process from Riyadh, in Arabic and English, and test copies should hold masked or generated data.
Next
Related pages
Next step
Review your schema migration process
Tell us your database platforms, how a schema change reaches production today and what happened the last time one failed. We will suggest an approach, a way back and a first schema to pilot.
Or directly
+966-11-2180999 [email protected]Tawuniya Towers, North Tower, 7th Floor, King Fahad Highway, Olaya, P.O. Box 56835, Riyadh 11564, Saudi Arabia