New! – Spin up instant copies of your dev databases in the cloud for free Learn more

Flyway Documentation

Poll

Existing Database Setup

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.

Prerequisites

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.

Extract the DDL and reference data from production

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 V1__baseline_migration.sql.

Example

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

Clean all databases containing data you don’t mind losing

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.

Example

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)

Align the databases not cleaned with production

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.

Example

We only have the Production database that isn’t being cleaned and therefore have nothing to align.

Give these databases a baseline version

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 (V1__baseline_migration.sql).

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.

Example

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

Done!

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.

Example

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.

Next steps

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.

Environment Variables