Schema version control with Liquibase - Part 3: multiple databases project

Introduction

In part 1 of this series on Liquibase, I introduced the main concepts we need to know to start working with Liquibase. In part 2, I explained how to manage changes on a single database schema. In development, we more often have to deal with multiple environments: development, staging, production, etc… Each environment may not need to have the same schema. We may need some extra tables in the staging environment that we don't want in the production one. We also need to have a reference schema with all the objects that are required for our app to work. In this article, I will explain how to manage a database across multiple environments, and how to compare schemas and apply changes in a specific environment.

Set up the properties files

The properties files store the defaults values of the options we need when we run a Liquibase command. Among other things, it stores the database connection information. We are going to create one properties file per environment. Depending on which environment we want to apply the changes to, we will use the appropriate properties files. As an example, I am going to create one file for a staging environment and one file for a production environment.

For the staging environment, I create a liquibase-stag.properties file

classPath: /liquibase/changelog
changelogFile: changelog-master.xml

# Database connection 
driver: org.postgresql.Driver
url: jdbc:postgresql://172.17.0.3:5432/mydb
username: postgres
password: postgres


# Database tables names
databaseChangeLogTableName: database_changelog
databaseChangeLogLockTableName: database_changelog_lock

I create another file for the production environment. I call it liquibase-prod.properties.

classPath: /liquibase/changelog
changelogFile: changelog-master.xml


# Database connection 
driver: org.postgresql.Driver
url: jdbc:postgresql://172.17.0.2:5432/mydb
username: postgres
password: postgres

# Database tables names
databaseChangeLogTableName: database_changelog
databaseChangeLogLockTableName: database_changelog_lock

Both files point to the same changelog. Next, we'll look at how to select the changesets we want to execute in a given environment. For now, the only difference is the database connection information. We will add more lines as we need them.

Using contexts

Because we are working with multiple environments, we need to be able to choose what changesets we want to execute when working in a given environment. To do so, we are going to use contexts. When we write a changeset, we add a context that will tell Liquibase when it should execute it. Here is an example of a changelog containing several changesets:

-- liquibase formatted sql


-- changeset guillaume:001 
-- comment create global_table in all environments
CREATE TABLE global_table (
    id integer
);
-- rollback DROP TABLE global_table;

-- changeset guillaume:002 context:staging
-- comment create stating_table in staging environment
CREATE TABLE staging_table (
    id integer
);
-- rollback DROP TABLE staging_table;


-- changeset guillaume:003 context:production
-- comment create production_table in production environment
CREATE TABLE  production_table(
    id integer
)
-- rollback DROP TABLE production_table;


-- changeset guillaume:004 context:"production or staging"
-- comment create stag_prod_table in both staging and production environment
CREATE TABLE stag_prod_table(
    id integer
);
-- rollback DROP TABLE stag_prod_table;

There are four changesets: - 001: global_table should be created in all environments - 002: staging_table should be created in the staging environment only - 003: production_table should be created in the production environment only - 004: stag_prod_table should be created in the staging and production environments but not in other ones.

When we run Liquibase, we need to specify in which context(s) we are executing the changesets. To do so, we add the --contexts option to the command, like this:

liquibase --defaults-file=/path/to/master-changelog.xml --contexts=production

We can also specify the contexts in our properties files. As we already created properties files for each of our environments, we add the context option:

contexts: production

We can now look how Liquibase would execute the changesets using the status command.

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties status --verbose
Liquibase Community 4.8.0 by Liquibase
3 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.2:5432/mydb
     changelogs/001-use-contexts.sql::001::guillaume
     changelogs/001-use-contexts.sql::003::guillaume
     changelogs/001-use-contexts.sql::004::guillaume

Liquibase will only execute the changesets 001, 003, and 004. Those changesets either don't have a context (001) or have production in their contexts (003 and 004).

Let's check the staging environment:

liquibase --defaults-file=/liquibase/changelog/liquibase-stag.properties status --verbose
3 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.3:5432/mydb
     changelogs/001-use-contexts.sql::001::guillaume
     changelogs/001-use-contexts.sql::002::guillaume
     changelogs/001-use-contexts.sql::004::guillaume

Liquibase will only execute the changesets 001, 002, and 004. Those changesets either don't have a context (001) or have staging in their contexts.

Let's run update to apply those changes.

liquibase --defaults-file=/liquibase/changelog/liquibase-stag.properties update
[...]
Running Changeset: changelogs/001-use-contexts.sql::001::guillaume
Running Changeset: changelogs/001-use-contexts.sql::002::guillaume
Running Changeset: changelogs/001-use-contexts.sql::004::guillaume
Liquibase command 'update' was executed successfully.

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties update
Running Changeset: changelogs/001-use-contexts.sql::001::guillaume
Running Changeset: changelogs/001-use-contexts.sql::003::guillaume
Running Changeset: changelogs/001-use-contexts.sql::004::guillaume
Liquibase command 'update' was executed successfully.

We can look at the tables that Liquibase created in our databases: - In the production environment: 

\d
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | database_changelog      | table | postgres
 public | database_changelog_lock | table | postgres
 public | global_table            | table | postgres
 public | production_table        | table | postgres
 public | stag_prod_table         | table | postgres
  • In the staging environment:
\d
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | database_changelog      | table | postgres
 public | database_changelog_lock | table | postgres
 public | global_table            | table | postgres
 public | stag_prod_table         | table | postgres
 public | staging_table           | table | postgres

Everything seems to be in order.

Comparing databases

One thing that we will have to do when working in multiple environments is to compare the schemas. The Liquibase Diff command is an easy way to do it. Before we keep going, I want to make a change to the global table. First, I create a new changelog 002-add-column.sql.

-- liquibase formatted sql

-- changeset guillaume:001 context:staging
-- comment add new_column to global_table
ALTER TABLE glogbal_table
ADD COLUMN new_column varchar
-- rollback ALTER TABLE DROP COLUMN

Then I execute the changeset:

liquibase --defaults-file=/liquibase/changelog/liquibase-stag.properties update

Liquibase created a new column in the staging environment:

mydb=# \d global_table
                   Table "public.global_table"
   Column   |       Type        | Collation | Nullable | Default
------------+-------------------+-----------+----------+---------
 id         | integer           |           |          |
 new_column | character varying |           |          |

Liquibase didn't create the column in production:

mydb=# \d global_table
            Table "public.global_table"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |

To compare the two databases, we need to connect to two of them. The problem is that for now, each of our properties files contains only the credentials for one database. We need to add the connection information to the reference database. To do so, we add the following lines to liquibase-prod.properties:

# Reference database settings
referenceDriver: org.postgresql.Driver
referenceUrl: jdbc:postgresql://172.17.0.3:5432/mydb
referenceUsername: postgres
referencePassword: postgres

Generate a Diff file

The Diff command generates a text file that lists the differences between two schemas.

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties diff > output/diff.txt

The saved output looks like this:

Reference Database: postgres @ jdbc:postgresql://172.17.0.3:5432/mydb (Default Schema: public)
Comparison Database: postgres @ jdbc:postgresql://172.17.0.2:5432/mydb (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s):
     public.staging_table.id
     public.global_table.new_column
Unexpected Column(s):
     public.production_table.id
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s):
     staging_table
Unexpected Table(s):
     production_table
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE

We can see that there is a missing table (staging_table). It means that the table exists in the staging environment (reference database) but not in the production environment. There is also an unexpected table (production_table). Unexpected items don't exist in the reference database. Under missing columns, we can also find the column we added to global_table in the staging environment but not in the production one. We can decide what changes we need to make and write the changesets that will make those changes. Another option is to have Liquibase generate those changesets automatically using the diff-changelog command.

diff-changelog

The diff-changelog command compares two schemas and generates a changelog file containing deployable changesets.

liquibase \
    --defaults-file=/liquibase/changelog/liquibase-prod.properties \
    --changelog-file=/liquibase/changelog/changelogs/003 -diff-changelog.postgresql.sql \
    diff-changelog

The command will create a file (003-diff-changelog.postgresql.sql) that contains the changesets we need to execute to have both schemas synchronized:

-- liquibase formatted sql

-- changeset ?:1646872728968-1
CREATE TABLE "public"."staging_table" ("id" INTEGER);

-- changeset ?:1646872728968-2
ALTER TABLE "public"."global_table" ADD "new_column" VARCHAR;

-- changeset ?:1646872728968-3
DROP TABLE "public"."production_table";

It is important to review the generated changelog before executing it. In the above example, we can see that one of the changesets deletes the production_table table because it does not exist in the reference database. There is another flaw with automatically generated changelogs. Liquibase does not understand the semantics of the change. For example, if we change the name of a column, Liquibase will see that a column has been deleted and another one added. It will generate a changeset to drop the old column and a changeset to create a new one, which can lead to data loss.

We can check what the update command would do.

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties status --verbose
[...]
3 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.2:5432/mydb
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-1::?
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-2::?
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-3::?
Liquibase command 'status' was executed successfully.

liquibase --defaults-file=/liquibase/changelog/liquibase-stag.properties status --verbose
[...]
3 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.3:5432/mydb
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-1::?
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-2::?
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-3::?
Liquibase command 'status' was executed successfully.

The three changesets generated by Liquibase will be executed in both the production and staging environments. It is not really what we want. Liquibase is going to create the stag_table in production and create the global_table.new_column in the staging environment (where it already exists). It is why reviewing the changelogs that Liquibase automatically generated is important. In our example, we need to add a context to changeset 2 and remove the two other ones. I would also add a rollback statement to the changeset we kept.

-- liquibase formatted sql

-- changeset ?:1646872728968-2 context:production
ALTER TABLE "public"."global_table" ADD "new_column" VARCHAR;
-- rollback ALTER TABLE global_table DROP COLUMN IF EXISTS new_column;

Only one changeset will now apply to our production database:

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties status --verbose
[...]
1 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.2:5432/mydb
     changelogs/003-diff-changelog.postgresql.sql::1646872728968-2::?
Liquibase command 'status' was executed successfully.

No change will be made to the staging database:

liquibase --defaults-file=/liquibase/changelog/liquibase-stag.properties status --verbose
[...]
postgres@jdbc:postgresql://172.17.0.3:5432/mydb is up to date
Liquibase command 'status' was executed successfully.

We can now run the update on the production database:

liquibase --defaults-file=/liquibase/changelog/liquibase-prod.properties update
[...]
Running Changeset: changelogs/003-diff-changelog.postgresql.sql::1646872728968-2::?
Liquibase command 'update' was executed successfully.

We can check that Liquibase added a new column to global_table in production:

mydb=# \d global_table 
                   Table "public.global_table"
   Column   |       Type        | Collation | Nullable | Default 
------------+-------------------+-----------+----------+---------
 id         | integer           |           |          | 
 new_column | character varying |           |          |

Conclusion

That's it! We can now manage our database schema changes in a centralized way. We have a single authoritative source for our schema that can easily be shared with people who need it. We can also easily use this source to restore the schema to its current state or roll back to a previous one. Finally, we are also able to synchronize our schema across multiple environments. If you need more information on how to use Liquibase, you can find the documentation on all the commands here.