Manage multiple database environments
Learn how to manage schemas for multiple database environments with Flyway
With Flyway, you can manage and track changes to your database schema, ensuring that the database evolves consistently across different environments.
When automating releases, there are often multiple environments or a chain of environments that you must deliver changes to in a particular order. Such environments might include development, staging, and production.
In this guide, we'll show you how to use Neon's branching feature to spin up a branch for each environment and how to configure Flyway to manage schema changes across those environments.
- A flyway installation. See Get started with Flyway and Neon for installation instructions.
- A Neon account and project. See Sign up.
- A database. This guide uses the ready-to-use
neondbdatabase on the
mainbranch of your Neon project. You can create your own database if you like. See Create a database for instructions.
Add a table to your database
Set up a database to work with by adding a table to your
neondb database on the
main branch of your Neon project. If you completed Get started with Flyway and Neon, you might already have this
person table created. We'll consider this your production environment database.
If you still need to create the
person table, open the Neon SQL Editor, and run the following statement:
Create databases for development and staging
Using Neon's branching feature, create your development and staging databases. When you create a branch in Neon, you are creating a copy-on-write clone of the parent branch that incudes all databases and roles that exist on the parent, and each branch is an isolated Postgres instance with its own compute resources.
Perform these steps twice, once for your development branch and once for your staging branch.
When you are finished, you should have a development branch and a staging branch.
Retrieve your Neon database connection strings
From the Neon Dashboard, retrieve the connection string for each branch (
staging) from the Connection Details widget. Use the Branch drop-down menu to select each branch before copying the connection string.
Your connection strings should look something like the ones shown below. Note that the hostname differs for each (the part starting with
ep- and ending with
aws.neon.tech). That's because each branch is hosted on its own compute instance.
Configure flyway to connect each environment
To enable Flyway to connect to multiple environments, we'll create a configuration file for each environment and add the environment-specific connection details. When running Flyway, you'll specify the configuration file to be used.
By default, Flyway loads its configuration from the default
conf/flyway.conf file. This is true even if you specify another configuration file when running Flyway. You can take advantage of this behavior by defining non-environment specific configuration settings in the default
conf/flyway.conf file, and placing your environment-specific settings in separate configuration files, as we'll do here.
Switch to your Flyway
/confdirectory and create the following configuration files, one for each environment, by copying the default configuration file. For example:
In each configuration file, update the following items with the correct connection details for that database environment. The
urlsetting will differ for each environment (in
urlwill point to
main). In this example, where you are the only user, the
passwordsettings should be the same for each of your three database environments.
flyway.locationssetting tells Flyway where to look for your migration files. We'll create them in the
/sqldirectory in a later step.
flyway.baselineOnMigrate=truesetting tells Flyway to perform a baseline action when you run the
migratecommand on a non-empty schema with no Flyway schema history table. The schema will then be initialized with the
baselineVersionbefore executing migrations. Only migrations above the
baselineVersionwill then be applied. This is useful for initial Flyway deployments on projects with an existing database. You can disable this setting by commenting it out again or setting it to false after applying your first migration on the database.
Create a migration
Create a migration file called
V2__Add_people.sql, add it to your Flyway
/sql directory, and add the following statements to the file:
Run the migration on each environment
Run the migration on each environment in order by specifying the environment's configuration file in the
flyway migrate command. You'll start with your
development environment, then
staging, and then finally,
A successful migration command returns output similar to the following:
Database: jdbc:postgresql://ep-nameless-unit-49929920.us-east-2.aws.neon.tech/neondb (PostgreSQL 15.4)
Schema history table "public"."flyway_schema_history" does not exist yet
Successfully validated 1 migration (execution time 00:00.199s)
Creating Schema History table "public"."flyway_schema_history" with baseline ...
Successfully baselined schema with version: 1
Current version of schema "public": 1
Migrating schema "public" to version "2 - Add people"
Successfully applied 1 migration to schema "public", now at version v2 (execution time 00:00.410s)
A Flyway report has been generated here: /home/alex/flyway-x.y.z/report.html
After you run the migration commands, your database should be consistent across all three environments. You can verify that the data was added to each database by viewing the branch and table on the Tables page in the Neon console. Select Tables from the sidebar and select your database.
You've seen how you can instantly create new database environment with Neon's branching feature and how to keep schemas consistent across different environments using Flyway. The steps in this guide were performed manually from the command line but could be easily integrated into your release management pipeline. Neon provides a CLI and API for automating various tasks in Neon, such as branch creation, which you can also integrate into your release automation.
Last updated on