Database Change Management

Question

What are ways in which teams handle database change management? I have a team of 8 developers making database changes concurrently.

Each developer is responsible for updating their changes in what we consider a "poor man's database change log" which is essentially a shared file referencing a script. Sometimes the scripts are database alters. Other times the script re-creates a view due to a change (which may have also been changed by another developer later on).

It always happens that scripts are placed out-of-order, listed in duplicate, etc.

My gut tells me we are doing this wrong. How can we do this better?

If it matters, we are using Postgres + git.

Answer 1

The big tools in this domain are

• flywaydb
• mybatis
• liquibase

Both work with Pg. I've never used either. I prefer to manually version the db with sql scripts. That has always worked well enough.

Answer 2

to me the most important thing in this context is to use transactions. PostgreSQL supports transactional DDLs and people should make heavy use of it.

the rule of thumb is: COMMIT stuff to the real system as late in the process as possible. this avoids the need to rollback stuff and to produce conflicts of all kinds. so transactions are really the key to making things work in a reasonable way. deployment should always happen in a transactional way as well.

one thing is also to use includes:

BEGIN;
\i module_1.sql
\i module_2.sql ...

it allows people to work on various files concurrently. it makes using git and so on a lot easier.

also, consider looking at CREATE EXTENSION. it allows a pretty nice way to handle versions and all that. i strongly encourage to use that one as well. git is also a good thing to have in general.