Managing Postgres schema changes with Migra
November 05, 2022migra is a great tool for tracking
changes in a Postgres schema for ORM-less setups.
It generates schema diffs between two different Postgres instances. i.e.,
on an instance A with the desired schema, and instance B with current schema,
migra will generate a SQL file that you can apply to instance B that will
bring its schema up to parity with A.
Here's a diagram to help visualize this:
1. (current) (new) 2. --------------- --------------- 3. | Instance $A | | Instance $A | 4. | Schema V0 | -- Change --> | Schema V1 | 5. --------------- --------------- 6. | 7. |---------------| 8. | 9. v 10. =============================================== 11. | $ migra diff | 12. | Against Instance B (current) | 13. =============================================== 14. | 15. | 16. v 17. (current) 18. ----------------- 19. | Instance $B | 20. | Schema V0 | 21. ----------------- 22. | 23. | 24. v 25. =============================================== 26. | Produces an upgrade SQL script to Schema V1 | 27. | >> changes.sql | 28. =============================================== 29. | 30. | 31. v 32. =============================================== 33. | Review and apply to $B | 34. | $ psql postgresql://$B -1 -f changes.sql | 35. =============================================== 36. | 37. | 38. v 39. (new) 40. ----------------- 41. | Instance $B | 42. | Schema V1 | 43. -----------------
This allows us to make schema tweaks on a local or dev instance, generate a diff against production, review and apply the generated diff to production, and keep the generated diff in source control.
Usage #
migra is easy to use:
1# $1 = Instance with current schema 2# $2 = Instance with desired schema 3# `changes.sql` => the diff between $1 and $2 4$ migra postgresql://$1 postgresql://$2 > changes.sql
Let's assume that both instances ($1 and $2) have this schema:
1# 0000.sql 2CREATE SCHEMA IF NOT EXISTS app; 3CREATE TABLE IF NOT EXISTS app.users ( 4 username TEXT NOT NULL, 5 password TEXT NOT NULL 6);
Since both instances have the same schema, running migra generates nothing:
1$ migra \ 2 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres \ 3 postgresql://postgresprod:postgresprod@localhost:5433/postgres
Now let's make a change to one of the instances:
1ALTER TABLE app.users ADD COLUMN last_login TIMESTAMP NULL;
And run migra both ways:
1$ migra --unsafe \ 2 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres \ 3 postgresql://postgresprod:postgresprod@localhost:5433/postgres 4alter table "app"."users" drop column "last_login"; 5 6# Switch the connection string around: 7$ migra --unsafe \ 8 postgresql://postgresprod:postgresprod@localhost:5433/postgres \ 9 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres 10alter table "app"."users" add column "last_login" timestamp without time zone;
Now you can see why it's a schema diff tool. You can then pipe the output to a file, review and/or modify it, and apply the changes when you're satisfied:
1# Output to file 2$ migra --unsafe \ 3 postgresql://postgresprod:postgresprod@localhost:5433/postgres \ 4 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres \ 5 > 0001.sql 6alter table "app"."users" add column "last_login" timestamp without time zone; 7 8# Review 9$ cat 0001.sql 10alter table "app"."users" add column "last_login" timestamp without time zone; 11 12# Apply 13$ psql postgresql://postgresprod:postgresprod@localhost:5433/postgres \ 14 -1 -f 0001.sql 15ALTER TABLE 16 17# Run migra again (prod->local) 18$ migra --unsafe \ 19 postgresql://postgresprod:postgresprod@localhost:5433/postgres \ 20 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres 21# => No output, because both instances are at parity 22 23# Run migra again (local->prod) 24$ migra --unsafe \ 25 postgresql://postgreslocal:postgreslocal@localhost:5432/postgres \ 26 postgresql://postgresprod:postgresprod@localhost:5433/postgres 27# => No output, because both instances are at parity
Why unsafe flag? #
Without --unsafe, migra will not generate destructive statements. We want
them because dropping columns/indexes/etc. is a valid schema tweak, so the
flag allows us to (intentionally) retain destructive commands such as
DROP ....
Check out https://databaseci.com/docs/migra/options for more information.
Usage warning: Be careful about renames! #
Most rename operations are generated as a DROP foo; -> CREATE foo;
statement, meaning that it re-creates the object instead of renaming it
in-place. This will happen if when you rename a table, column, primary key,
etc.
Check out migra/issues/29 and migra/issues/213 for more information.
On tracking schema changes
Tracking changes in source control can be achieved with just a single version-controlled SQL file, but in my opinion, it leaves much to be desired:
- If there are incompatible changes between different database versions, local/dev environments may need to get nuked in order to apply the full schema again
- Browsing through a series of sequential files to view point-in-time changes
is more ergonomical than
$ git blame
As a counterpoint, a single file helps maintain a single source-of-truth for the current state of the schema, which is a desireable trait.
Only if there were Git for databases!
Appendix: Demostration set-up
- Spin up two Postgres instances using Docker:
1# docker-compose-local-pg.yml
2version: '3.8'
3services:
4 postgres:
5 image: postgres:14
6 restart: always
7 environment:
8 - POSTGRES_USER=postgreslocal
9 - POSTGRES_PASSWORD=postgreslocal
10 - POSTGRES_DB=postgres
11 ports:
12 - 5432:5432
13 volumes:
14 - ./postgres-local-data:/var/lib/postgresql/data
15
16# docker-compose-prod-pg.yml
17version: '3.8'
18services:
19 postgres:
20 image: postgres:14
21 restart: always
22 environment:
23 - POSTGRES_USER=postgresprod
24 - POSTGRES_PASSWORD=postgresprod
25 - POSTGRES_DB=postgres
26 ports:
27 - 5433:5432
28 volumes:
29 - ./postgres-prod-data:/var/lib/postgresql/data
- Use example starting schema:
1# 0000.sql 2CREATE SCHEMA IF NOT EXISTS app; 3CREATE TABLE IF NOT EXISTS app.users ( 4 username TEXT NOT NULL, 5 password TEXT NOT NULL 6);
- Apply example schema to both instances:
1$ psql postgresql://postgreslocal:postgreslocal@localhost:5433/postgres -1 -f 0000.sql 2CREATE SCHEMA 3CREATE TABLE 4$ psql postgresql://postgresprod:postgresprod@localhost:5433/postgres -1 -f 0000.sql 5CREATE SCHEMA 6CREATE TABLE