PostgreSQL just won DBMS of the Year 2023 and is enjoying a vibrant ecosystem. On the other hand, database schema migration in Postgres is still PITA. In this post, we are reviewing several open source database schema migration tools for Postgres.
Bytebase
Bytebase is an all-in-one database DevOps and CI/CD solution. It's like GitHub/GitLab that provides a GUI workspace for developers and DBAs to collaborate on database changes. It's written in Go and TypeScript.
It also provides configurable SQL lint rules to detect SQL anti-patterns such as NOT NULL enforcement.
Apart from taking care of database change management, Bytebase also provides a SQL Editor with data access control, dynamic data masking and audit logs to control the query path.
Liquibase
Liquibase is arguably the most well-known product in this segment. It's CLI-based and written in Java.
In Liquibase, the schema migrations are organized as Changeset and Changelog. Probably due to its age and root in Java, the most commonly used form is XML (YAML and JSON support have been added later):
Plain SQL is also supported with proper annotation:
Flyway
Flyway is another open source project with a long history and a large customer base. Its core product includes the CLI and a Java library.
Flyway has been acquired by Redgate, while it still retains a developer-oriented approach. This can be felt from its website.
Liquibase and Flyway are closely matched in their offerings. The key distinction between them is their target audiences: Liquibase is oriented towards serving enterprise clients, whereas Flyway presents itself as more developer-friendly and approachable.
Reshape
Reshape takes a novel approach to achieve zero-downtime schema migrations. It's CLI based and written in Rust. Reshape schema migration consists of 3 phases:
-
Start migration (
reshape migration start): Sets up views and triggers to ensure both the new and old schema are usable at the same time. -
Roll out application: Your application can be gradually rolled out without downtime. The existing deployment will continue using the old schema whilst the new deployment uses the new schema.
-
Complete migration (
reshape migration complete): Removes the old schema and any intermediate data and triggers.
The author is now working on ReshapeDB, a new database built from the ground up to make zero-downtime schema and data migrations as simple and safe as possible.
FWIW, pgroll is another Postgres schema migration tool inspired by Reshape. pgroll is CLI based and written in Go. It's built by Xata, the serverless database platform built on the top of PostgreSQL.
graphile-migrate
graphile-migrate is an opinionated SQL-powered productive roll-forward migration tool for PostgreSQL.
graphile-migrate is also CLI-based, written in TypeScript. It can also work with PostGraphile to generate GraphQL schemas upon database changes.
Another novel idea graphile-migrate introduces is the commit, uncommit commands. It enables a
git-like workflow when developing against the local database.
-
graphile-migrate commitcommits the current migration into thecommitted/folder, resetting the current migration. Resets the shadow database. -
graphile-migrate uncommitMoves the latest commit out of the committed migrations folder and back to the current migration (assuming the current migration is empty-ish). Removes the migration tracking entry from ONLY the local database.
Summary
| Name | Interface | Language | Postgres Only | Highlight |
|---|---|---|---|---|
| Bytebase | GUI | Go + TypeScript | N | All in one for team collaboration. |
| Liquibase | CLI | Java | N | Long-standing. |
| Flyway | CLI | Java | N | Developer-friendly. |
| Reshape | CLI | Rust | Y | Zero-downtime based on view. |
| pgroll | CLI | Go | Y | Inspired by Reshape and more accessible via its database platform. |
| graphile-migrate | CLI | TypeScript | Y | Opinionated with git-like workflow. |
the
