Industry

Top Open Source Postgres Migration Tools in 2024

Tianzhou
Tianzhou6 min read
Top Open Source Postgres Migration Tools in 2024

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.

starhistory

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.

issue-detail

It also provides configurable SQL lint rules to detect SQL anti-patterns such as NOT NULL enforcement.

sql-review

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.

sql-editor

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:

  1. Start migration (reshape migration start): Sets up views and triggers to ensure both the new and old schema are usable at the same time.

  2. 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.

  3. 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 commit commits the current migration into the committed/ folder, resetting the current migration. Resets the shadow database.

  • graphile-migrate uncommit Moves 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

NameInterfaceLanguagePostgres OnlyHighlight
BytebaseGUIGo + TypeScriptNAll in one for team collaboration.
LiquibaseCLIJavaNLong-standing.
FlywayCLIJavaNDeveloper-friendly.
ReshapeCLIRustYZero-downtime based on view.
pgrollCLIGoYInspired by Reshape and more accessible via its database platform.
graphile-migrateCLITypeScriptYOpinionated with git-like workflow.

Further Readings

Jointhe community

At Bytebase, we believe in the power of collaboration and open communication, and we have a number of communities that you can join to connect with other like-minded.

Subscribe to Newsletter

By subscribing, you agree with Bytebase's Terms of Service and Privacy Policy.