How to migrate Postgres schemas as your project evolves

(written by lawrence krubner, however indented passages are often quotes). You can contact lawrence at: lawrence@krubner.com, or follow me on Twitter.

This seems like a brilliant solution to a tough problem: how to handle database migrations in a polyglot environment? Once you go polyglot you can no longer rely on your framework to manage database migrations. So here is an idea: create “diffs” of the changing schema, and automate the execution of those diffs. This library is specific to Postgres:

https://michaelsogos.github.io/pg-diff/

“If you adopt any DevOps best practice during software development, and if you work in a team, of course you found yourself fighting on how better care about database objects changes. Generally speaking you can work in 2 different situation:

  1. you use a kind of ORM that will integrate probably a migration strategy, this approch is called code-first; in this case this library is not for you :smiley:.
  2. you handle database objects directly usually with a GUI tool like pgAdmin or DBeaver, this approch is called database-first; if this is your case then this library is absolutely a good companion for you.

In our long experience, passing thru many way to handle database objects changes (not just on PostgreSQL :smiley:), we understood that for both code versioning as also for software releases, code changes should stay close to database objects changes.
In this way it will be easy to apply database objects changes when software will be updated.”

Post external references

  1. 1
    https://michaelsogos.github.io/pg-diff/
Source