pgroll 0.14 - New commands and more control over version schemas

We just released v0.14.0 of pgroll, our open-source schema migration tool for Postgres.

Previous releases also have their own release blogs too; some past entries in the series can be found here:

• pgroll v0.13 release blog
• pgroll v0.12 release blog
• pgroll v0.11 release blog

As always, the release is available on the Github v0.14.0 release page.

What is pgroll?

pgroll is a schema migration tool for Postgres. Designed for application developers working on applications that require frequent schema changes but also need to maintain zero downtime around those schema changes, pgroll takes a different approach compared to most other migration tools on the market. There are two aspects that characterize pgroll's approach to migrations:

• Multi-version migrations: Making a schema change with pgroll results in two versions of the schema; the one before the change and the one after the change. This allows applications to select which version of the schema they want to work with and allows side-by-side rollout of applications that require the new schema changes with old applications that may be incompatible with it.
• Lock-safe migrations: Migrations using pgroll are expressed declaratively, rather than using SQL directly. This allows pgroll to implement the steps required to perform the schema change in a safe manner, ensuring that any locks required on the affected objects are held for the shortest possible time.

Full documentation for pgroll is on the documentation section of this site.

Let's take a look at the features and fixes in the latest pgroll release.

Migration file validation

The new pgroll validate command validates the contents of a migration file before execution. Validating a migration not only checks that the migration is syntactically valid, but also checks that the objects referenced by a migration exist in the target database. For example this migration attempts to modify a reviews table to add a NOT NULL constraint to its review field:

The migration can be validated before use by running pgroll validate which checks for the existence of the reviews table (and ensures that the review field is not already NOT NULL):

Validation is performed implicitly before migration start, so if you're running pgroll start or pgroll migrate you still benefit from the 'pre-flight checks' that validation provides, but pgroll validate lets you validate a migration file as a separate step, independent of migration execution.

Automated update of migration files

The new pgroll update command automatically updates local migration files across breaking changes to the migration file format.

Consider this migration file which uses an outdated form of the create_index operation:

This form of the create_index operation was made obsolete in the pgroll 0.10 release. The updated form uses a map for the columns field rather than a list. Running pgroll upgrade migrations/ (assuming your migrations are stored in a migrations/ directory) will update the migration to use the new form of the create_index operation:

As we move towards a pgroll v1.0 release there will likely be breaking changes to migration file formats. pgroll update will help users to keep their migrations up-to-date without manual editing of migration files. This feature was implemented in response to user feedback in issue #765.

Generate Postgres connection strings

The ability to create two versions of a database schema for each migration is at the heart of what makes pgroll unique among migration tools. The mechanism by which applications select which version of the database schema they want to work with relies on Postgres' search_path setting. More information on how pgroll uses the search_path is in the documentation.

pgroll 0.14 adds a new pgroll latest url command to get a connection string for the most recent version schema.

Suppose that we have just started a migration called 02_modify_column. To work with this most recent version schema, new versions of applications should connect to Postgres with the search_path set to 02_modify_column. pgroll latest url gets the right connection string for us:

The pgroll latest url command could be used in a CD pipeline to construct the connection string for an application to use in one simple step.

Specify the version schema for a migration

A migration will take the name of the version schema that it creates from the migration filename. For example, suppose we have this migration saved as 02_create_table.yaml:

When pgroll start 02_create_table is run against the public schema, the target database will contain a new version schema called public_02_create_table:

With pgroll 0.14 it is now possible to specify the name of the version schema that a migration should create instead of having to accept the default:

Running this migration creates the following version schema:

The version schema name (public_my_custom_name) was taken from the migration file rather than defaulting to the migration filename.

Run pgroll without creating version schema

In some scenarios it may be desirable to run a pgroll migration without creating a version schema at all. pgroll 0.14 supports this use-case with the --use-version-schema flag:

The migration is started as usual but there is no version schema created for the migration:

By not creating version schema, we lose the ability for applications to connect to the old or new version of the schema by setting the search_path . This makes side-by-side rollouts of different application versions more challenging. However we still retain pgroll's ability to apply backfills and avoid taking long-lived locks during DDL operations.

Support for altering primary key columns

This release of pgroll fixes a long-standing issue that prevented modification of primary key columns by alter_column operations. Suppose we have a table defined like this:

And we want to change the type of the id field with this migration:

This migration is now possible and runs as expected where previously it would have failed due to the modified id column being a primary key.

Conclusion

These features continue our journey towards making pgroll a production ready migration tool for all application developers working with Postgres.

pgroll is open source software; the code and discussions are open to everyone on our Github repository.

Features are driven in large part by input from the community so please get involved via issues, discussions and pull requests!