Manage multiple database environments

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.

Prerequisites

• 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 neondb database on the main branch 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 table person (
    ID int not null,
    NAME varchar(100) not null
);

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 (main, development, and 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.

• main

  jdbc:postgresql://ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb?user=alex&password=AbC123dEf

• development

  jdbc:postgresql://ep-mute-night-47642501.us-east-2.aws.neon.tech/neondb?user=alex&password=AbC123dEf

• staging

  jdbc:postgresql://ep-shrill-shape-27763949.us-east-2.aws.neon.tech/neondb?user=alex&password=AbC123dEf

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.

1. Switch to your Flyway /conf directory and create the following configuration files, one for each environment, by copying the default configuration file. For example:

   cd ~/flyway-x.y.z/conf
   cp flyway.conf env_dev.conf
   cp flyway.conf env_staging.conf
   cp flyway.conf env_prod.conf

2. In each configuration file, update the following items with the correct connection details for that database environment. The url setting will differ for each environment (in env_prod.conf, the url will point to main). In this example, where you are the only user, the user and password settings should be the same for each of your three database environments.

   flyway.url=jdbc:postgresql://ep-cool-darkness-123456.us-east-2.aws.neon.tech:5432/neondb
   
   flyway.user=alex
   
   flyway.password=AbC123dEf
   
   flyway.locations=filesystem:/home/alex/flyway-x.y.z/sql
   
   flyway.baselineOnMigrate=true

   • The flyway.locations setting tells Flyway where to look for your migration files. We'll create them in the /sql directory in a later step.
   • The flyway.baselineOnMigrate=true setting tells Flyway to perform a baseline action when you run the migrate command on a non-empty schema with no Flyway schema history table. The schema will then be initialized with the baselineVersion before executing migrations. Only migrations above the baselineVersion will 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:

insert into person (ID, NAME) values (1, 'Alex');
insert into person (ID, NAME) values (2, 'Mr. Lopez');
insert into person (ID, NAME) values (3, 'Ms. Smith');

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, production.

flyway migrate -configFiles="conf/env_dev.conf"

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.

Conclusion

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.

References

• Flyway documentation
• Flyway command-line tool
• Flyway command-line quickstart
• A simple way to manage multi-environment deployments