pgroll v0.12 includes multiple usability improvements

We've just released v0.12 of pgroll, our open-source schema migration tool for Postgres. As we've done for previous releases, we'll take a look at some of the headline features that made it into the release and look ahead to what's coming next. Past entries in the release blog series can be read here:

For the complete notes for this release see the Github v0.12.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 here.

pgroll v0.12 includes verbose mode for migrations, usability improvements and one new constraint type. Most of the features were suggested by you, the community. We appreciate your participation and insightful suggestions coming from you. Let's take a look at what went into the v0.12 release in more detail:

Create migrations interactively

If you are tired of writing migration files in your editor, you can try the new `create` command. It helps you geneate migration files interactively from the command line.

pgroll create subcommand example

Alternatively, if you want to generate new migration files from scripts, you can initialize new migrations by setting --name and --empty flag in the command.

Verbose mode

Ever wondered exactly what pgroll was doing behind the scenes during a migration? Now you can find out! We have added a new --verbose flag to migrator commands: start, rollback, complete and migrate. When you set --verbose, pgroll will log every step of the process from start, rollback, complete and backfilling phases. This is invaluable for debugging, following complex migrations, and ensuring everything is happening as expected. This feature was directly requested on GitHub.

The output of start is short and concise:

However, the start command can tell you a lot more if you set --verbose:

Now let's run rollback with the new flag --verbose:

You can get this level of detail in all migrator commands including `start`, `rollback`, `complete` and `migrate`.

New constraint type: primary_key in create_constraint operation

Previously, the only way to add primary key constraint to tables was in create_table operations: either by setting pk in a column definition or adding a constraint to the list of constraints. But what if you need to quickly add a primary key constraint to an existing table? You can now do it directly with the create_constraint operation! This simplifies the process and makes it easier to ensure data integrity with the help of pgroll.

The new constraint type is called primary_key.

Example

Add a composite primary key to the existing tickets table.

pull command stores files in YAML format by default

In the last release, we added support for YAML migration files. However, it had one limitation. When you ran pull to fetch migrations from your PostgreSQL instance, pgroll persisted the files to disk in JSON format. From now on the migrations files are stored as YAML, unless you set the new flag --json.

migrate command is idempotent

You might try to run migrate from several CI jobs in parallel. To prevent pgroll from throwing an error and potentially halting your workflow, we have made the migrate command idempotent. If a migration is currently running, pgroll will now simply report that a migration is in progress and will not return an error.

This significantly improves reliability and reduces the chance of unexpected interruptions during automated workflows. This issue was raised on Discord.

Conclusion

pgroll is open-source; check out the repository to get involved.

Many of the features that have gone into this and previous releases have been directly influenced by how users tell us they are using the tool and where it's currently lacking. We welcome any feedback in issues, or contributions via pull requests!

pgroll v0.12.0 update