These are the steps to follow to successfully integrate Flyway into a project with existing databases.
To guide you through the process, we’ll show examples using Spawn to quickly provision database instances that will represent our Development and Production environments.
If you are new to Flyway, read through our getting started section first.
To follow along with the examples, install Spawn and run the following commands to create our Development and Production instances:
> spawnctl create data-container \ --image postgres:flyway-existing-database \ --name flyway-container-dev \ --lifetime 24h
> spawnctl create data-container \ --image postgres:flyway-existing-database \ --name flyway-container-prod \ --lifetime 24h
This will return connection string details for each PostgreSQL instance which are used when connecting with Flyway and can be queried with your normal tools:
Data container 'flyway-container-dev' created! -> Host=instances.spawn.cc;Port=<DevPort>;Username=<DevUsername>;Database=pagila;Password=<DevPassword>
Data container 'flyway-container-prod' created! -> Host=instances.spawn.cc;Port=<ProdPort>;Username=<ProdUsername>;Database=pagila;Password=<ProdPassword>
You can retrieve these details at any time by running:
> spawnctl get data-containers -o yaml
We now have two identical but isolated and disposable database instances to use in our examples. Under each section below, you will find an
Example section with steps to follow.
First start by taking a snapshot of your most important database: production. This will be the starting point for migrations.
Generate a SQL script that includes the entire DDL (including indexes, triggers, procedures …) of the production database. To do this you will need to add insert statements for all of the reference data present in the database.
This script will form your baseline migration. Save it in a location specified in the locations property. Give it a relevant version number and description such as
You can download a sample baseline migration for our example database here. Create a new directory
flyway-tutorial, download the migration into the new directory and switch to it:
> cd flyway-tutorial
Now comes the point where we have to make sure that the migrations meant for production will work everywhere with the clean command.
For all databases with unimportant data that you don’t mind losing, execute:
> flyway clean
by altering the url to completely remove their contents.
In this example, we don’t mind losing data in the Development database so we will clean it. Using the connection details from our
flyway-container-dev container we can run:
> flyway clean -url="jdbc:postgresql://instances.spawn.cc:<DevPort>/pagila" \ -user="<DevUsername>" -password="<DevPassword>"
Database: jdbc:postgresql://instances.spawn.cc:<DevPort>/pagila (PostgreSQL 11.0) Successfully dropped pre-schema database level objects (execution time 00:00.001s) Successfully cleaned schema "public" (execution time 00:01.404s) Successfully dropped post-schema database level objects (execution time 00:00.000s)
Now you need to check all remaining databases (e.g. test). You must make sure that their structure (DDL) and reference data matches production exactly. This step is important, as all scripts destined for production will likely be applied to these databases first. For the scripts to succeed, the objects they migrate must be identical to what is present in production.
We only have the Production database that isn’t being cleaned and therefore have nothing to align.
Now comes the time to baseline the databases that contain data (including production) with a baseline version. Use the same version and description you used for the baselined migration above (
You can accomplish it like this:
> flyway -baselineVersion="1" -baselineDescription="baseline_migration" baseline
You must perform this step for each database that hasn’t been cleaned by altering the url again.
The Production database needs to be baselined in our example, for which we can run:
> flyway baseline -baselineVersion="1" -baselineDescription="baseline_migration" \ -url="jdbc:postgresql://instances.spawn.cc:<ProdPort>/pagila" \ -user="<ProdUsername>" -password="<ProdPassword>" -locations="filesystem:."
This should give an output similar to:
Database: jdbc:postgresql://instances.spawn.cc:<ProdPort>/pagila (PostgreSQL 11.0) Creating Schema History table "public"."flyway_schema_history" with baseline ... Successfully baselined schema with version: 1
Congratulations! You are now ready.
When you execute:
> flyway migrate
against the empty databases (by altering the url), they will be migrated to the state of production and the others will be left as is.
As soon as you add a new migration, it can be applied identically to any of your databases.
Looking at our databases, we have Production baselined and in line with our migration scripts. The Development database has been cleaned and so is not in line. If we now run migrate against each database it will align them. Migrate isn’t strictly needed at this point for Production, however it will highlight that there is no work to be done as the baseline has been configured correctly.
First we should migrate our Development database:
> flyway migrate -url="jdbc:postgresql://instances.spawn.cc:<DevPort>/pagila" \ -user="<DevUsername>" -password="<DevPassword>" -locations="filesystem:."
Database: jdbc:postgresql://instances.spawn.cc:<Port>/pagila (PostgreSQL 11.0) Successfully validated 1 migration (execution time 00:00.087s) Creating Schema History table "public"."flyway_schema_history" ... Current version of schema "public": << Empty Schema >> Migrating schema "public" to version "1 - baseline migration" ... Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:04.962s)
As expected, Flyway recognised that the database has an empty schema and therefore created the
flyway_schema_history table and ran our baseline migration.
Now to migrate the Production database:
> flyway migrate -url="jdbc:postgresql://instances.spawn.cc:<ProdPort>/pagila" \ -user="<ProdUsername>" -password="<ProdPassword>" -locations="filesystem:."
Database: jdbc:postgresql://instances.spawn.cc:<Port>/pagila (PostgreSQL 11.0) Successfully validated 1 migration (execution time 00:00.111s) Current version of schema "public": 1 Schema "public" is up to date. No migration necessary.
We can see that our Production database is already at version
1 which matches the
-baselineVersion we specified to the
baseline command. This means it is up to date with our migration scripts and nothing needs to be run.
If you have followed our examples and are ready to integrate Flyway into your own database, don’t forget that Spawn is available for free to create fast, isolated copies for development and testing of your own databases. This enables you to perfect the baselining process on a copy of Production before touching the real database. You can find out how to create a data image of your own database here.