Liquibase developer workflow with Neon

Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes. To learn more about Liquibase, refer to the Liquibase documentation.

This guide shows how to set up a developer workflow using Liquibase with Neon's branching feature. The workflow involves making schema changes to a database on a development branch and applying those changes back to the source database on the main branch of your Neon project.

The instructions in this guide are based on the workflow described in the Liquibase Developer Workflow tutorial.

Prerequisites

• A Neon account. See Sign up.
• A Neon project. See Create your first project.
• Liquibase requires Java. For Liquibase Java requirements, see Requirements. To check if you have Java installed, run java --version, or java -version on macOS`.
• An installation of Liquibase. For instructions, refer to Get started with Liquibase and Neon.

Initialize a new Liquibase project

Run the init project command to initialize a Liquibase project in the specified directory. The project directory is created if it does not exist. Initializing a Liquibase project in this way provides you with a pre-populated Liquibase properties file, which we'll modify in a later step.

Enter Y to accept the defaults.

Prepare a source database

For demonstration purposes, create a blog database in Neon with two tables, posts and authors.

1. Open the Neon Console.

2. Select your project.

3. Select Databases from the sidebar and create a database named blog. For instructions, see Create a database.

4. Using the Neon SQL Editor, add the following tables:

Prepare a development database

Now, let's prepare a development database in Neon by creating a development branch, where you can safely make changes to your database schema without affecting the source database on your main branch. A branch is a copy-on-write clone of the data in your Neon project, so it will include a copy of the blog database with the authors and posts tables that you just created.

To create a branch:

1. In the Neon Console, select Branches. You will see your main branch, where you just created your blog database and tables.
2. Click New Branch to open the branch creation dialog.
3. Enter a name for the branch. Let's call it dev1.
4. Leave main selected as the parent branch. This is where you created the blog database.
5. Leave the remaining default settings. Creating a branch from Head creates a branch with the latest data, and a compute endpoint is required to connect to the database on the branch.
6. Click Create Branch to create your branch.

Retrieve your Neon database connection strings

From the Neon console, select your project and retrieve connection strings for your target and source databases from the Connection Details widget on the Neon Dashboard.

1. Select the dev1 branch, the blog database, and copy the connection string.

2. Select the main branch, the blog database, and copy the connection string.

Be careful not to mix up your connection strings. You'll see that the hostname (the part starting with -ep and ending in neon.tech) differs. This is because the dev1 branch is a separate instance of Postgres, hosted on its own compute.

Update your liquibase.properties file

The liquibase.properties file defines the location of the Liquibase changelog file and your target and source databases.

1. From your Liquibase project directory, open the liquibase.properties file, which comes pre-populated with example settings.

2. Change the changeLogFile setting as shown:

   The changelog file is where you define database schema changes (changesets).

3. Change the target database url, username, and password settings to the correct values for the blog database on your dev1 branch. You can obtain the required details from the connection string you copied previously. You will need to swap out the hostname (ep-silent-hill-85675036.us-east-2.aws.neon.tech), username, and password for your own.

4. Change the source database settings to the correct values for the blog database on your main branch. The username and password will be the same as your dev1 branch, but make sure to use the right hostname. Copy the snippet below and replace the hostname (ep-cool-darkness-123456.us-east-2.aws.neon.tech), username, and password for your own.

Take a snapshot of your target database

Capture the current state of your target database. The following command creates a Liquibase changelog file named mydatabase_changelog.xml.

If the command was successful, you’ll see output similar to the following:

Check for the mydatabase_changelog.xml file in your Liquibase project directory. It should look something like this:

Create a schema change

Now, you can start making database schema changes by creating changesets and adding them to the changelog file you defined in your liquibase.properties file. A changeset is the basic unit of change in Liquibase.

1. Create the changelog file where you will add your schema changes:

2. Add the following changeset to the dbchangelog.xml file, which adds a comments table to your database:

Deploy the schema change

Run the update command to deploy the schema change to your target database (your development database on the dev1 branch).

If the command was successful, you’ll see output similar to the following:

At this point, you can continue to iterate, applying schema changes to your database, until you are satisfied with the modified schema.

Review schema changes

It is a best practice to review schema changes before saving and applying them to your source database.

You can run the status command to see if there are any changesets that haven't been applied to the source database. Notice that the command specifies the hostname of the source database:

Command output

If the command was successful, you’ll see output similar to the following indicating that there is one changeset that has not been applied to the source database. This is your comments table changeset.

Check your SQL

Before applying the update, you can run the updateSQL command to inspect the SQL Liquibase will apply when running the update command:

Command output

If the command was successful, you’ll see output similar to the following, which confirms that the changeset will create a comments table.

Run a diff command

You can also run a diff command to compare your source and target databases.

Command output

If the command was successful, you’ll see output similar to the following:

Save your changelog to source control

When you are satisfied with the changes that will be applied, save your changelog to source control, such as a GitHub repository where you or your team stores you changelog.

Apply the changeset to your source database

Apply the new changesets to the source database on your primary branch:

Command output

If the command was successful, you’ll see output similar to the following:

To ensure that all changes have been applied to the production database, you can rerun the status, updatedSql, and diff commands you ran above. After applying the change, there should be no differences. You can also check your databases in the Tables view in the Neon console to verify that the source database now has a comments table.

References

• Get started with Liquibase
• Setting up your Liquibase Workspace
• Liquibase Developer Workflow