Redgate · Database DevOps
Database version control: how to put a schema under version control and keep it there
Database version control means the repository, not a running server, is the authority for your schema, reference data and permissions, and every change reaches each environment through the same recorded path. Most teams version their application code and leave the database as it was. This page explains the problem, the two ways to version a database and how to start from a production system that already exists, then shows where Redgate's Flyway fits.
Published 5 min read
In short
- What it is: schema, reference data and permissions kept in a repository, with each change applied to every environment by a pipeline and recorded.
- What it replaces: a running server as the only authority, and changes made by hand through a console.
- Two methods: ordered, versioned migration scripts, or a schema model that holds each object as it should be. Flyway supports both for some platforms.
- Who it is for: teams that cannot say with certainty what is in production, or whose schema changes wait for a maintenance window.
- Where Interkey helps: baseline, pipeline, checks and rehearsed rollback, in Saudi Arabia, in Arabic and English, using Redgate's tooling where it fits.
Why a database needs version control
Application code has had version control for decades. The database often has a running server, a few scripts in a shared folder and one person who knows what is really in production. The states teams describe are familiar: nobody is certain what is in production, a schema change needs a window and a DBA at midnight, environments drift from each other, and a rollback means restoring a backup.
Read more
The arithmetic is simple. If applications deploy weekly and the database deploys quarterly, the system deploys quarterly. Version control is the first step in removing that limit, because nothing downstream can be automated while the current state of the target is a matter of opinion. The full sequence is on the database DevOps page.
Relateddatabase DevOps
Two ways to version a database
| Method | What you store | Strength | Cost |
|---|---|---|---|
| Versioned migrations | An ordered series of scripts, each with a version number, applied once and recorded in a history table | What ran in production is exactly what was reviewed, in the order it ran | The history of one object is spread across many scripts |
| Schema model | One definition per object, with reference data, committed as files | A diff shows how an object changed over time | The script that changes production still has to be generated and reviewed |
Redgate's documentation describes Flyway as working with migrations, and says that for platforms including MySQL, PostgreSQL and SQL Server it can also capture objects and reference data as a schema model committed to version control. The two can be used together. Which one leads depends on the team, and the pipeline is the same either way.
What belongs in the repository
A database under version control has all of these in the repository:
- Schema objects: tables, views, procedures and the rest of the structure.
- Reference data: the lookup values the application depends on.
- Permissions: who may read and change what.
- Migrations: every change, with its reverse where one can be written safely.
- Pipeline definitions: how a change is checked, promoted and recorded.
Drift: the difference between the repository and the server
Drift is any difference between what the repository says and what an environment holds. It appears when someone fixes a problem directly on a server, or when an environment is built by a script nobody has run end to end recently. A drift check reports it; someone then has to rule on whether the difference was deliberate. The tool reports drift, but it cannot decide which side is right.
Redgate lists drift detection and reporting as a Flyway Enterprise capability. Whatever the tool, the rule is the same: no change reaches production by a console, and a drift report says which environment disagrees today.
How to start from a running production database
-
Capture the baseline.
Record the production schema as the origin and compare every other environment to it. There is no reconstructing history that was never recorded; version one is today.
-
Rule on the drift.
Decide which differences were deliberate. Keep those, correct the rest.
-
Move changes into migrations.
From now on every change is a versioned migration, reviewed before it merges.
-
Apply them through a pipeline.
The pipeline applies migrations in order, records what ran and refuses to run out of sequence or against a target whose history does not match.
-
Prove the rollback.
Run each reverse in a lower environment before the forward change goes to production.
Where Flyway fits, and what it cannot do
Flyway is the tool Interkey implements for versioned migrations across the mainstream relational platforms. Redgate states support for more than 60 platforms. Flyway applies migrations in version order and records each one in a history table, and it can be run from the pipeline the application already uses. Editions, licensing and support are on the Flyway page; the wider implementation is described under Redgate implementation.
Flyway applies whatever migration it is given, safe or not. Version control makes changes visible and repeatable; it does not make a destructive change safe. That comes from change patterns that are additive and reversible by default, and the same work applies whichever migration tool applies the result.
How to tell it is working
Checks someone outside the project can make
- The repository, not a server, is the authority for the schema.
- Every migration is versioned, applied by the pipeline in order and recorded.
- A lower environment can be rebuilt from the repository by someone who has not done it before.
- Every migration ships with its reverse, and the reverse has been run.
Questions buyers ask
Is database version control the same as keeping scripts in Git?
No. Scripts in Git are a start, but version control for a database also needs the repository to be the authority, every environment compared with it, and changes applied by a pipeline that records what ran.
Should we version the data as well as the schema?
Version the schema, the permissions and the reference data the application depends on. Do not put customer data in the repository; non-production environments should hold masked or generated data.
Migrations or a schema model: which should we choose?
Migrations keep the exact order in which changes ran. A schema model shows how each object changed over time. Flyway supports migrations, and for platforms including MySQL, PostgreSQL and SQL Server it can also hold a schema model. We choose with your team.
How do we start when production is already running?
Capture the production schema as the baseline, compare every environment to it, rule on the drift and apply every later change as a versioned migration through a pipeline.
Does it remove the maintenance window?
Not by itself. The window usually comes from changes that cannot be applied online. Version control makes the changes visible and repeatable; reworking the change patterns is what removes the window.
Next
Related pages
Next step
Not sure what is in your production schema?
Tell us the platforms and how a schema change reaches production today. We will propose a baseline and a drift check as the first step.
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
or How to start