pg-schema-version 0.3

Simple Postgres Schema Versioning

Simple Postgres Schema Versioning

There already exists many tools to manage database schema versions, such as sqitch, or alembic. Please consider them first to check whether they fit your needs before considering this one. In contrast to these tools, pg-schema-version emphasizes a simple approach based on a single plain SQL scripts and no configuration, to provide limited but useful features with safety in mind. The application schema status is maintained in one table to detect reruns. Several application can share the same setup.

Usage

1. Write a sequence of incremental postgres SQL data definition scripts

   • initial schema creation create_000.sql

     CREATE TABLE AcmeData(aid SERIAL PRIMARY KEY, data TEXT UNIQUE NOT NULL);
     

   • first schema upgrade create_001.sql

     CREATE TABLE AcmeType(atid SERIAL PRIMARY KEY, atype TEXT UNIQUE NOT NULL);
     INSERT INTO AcmeType(atype) VALUES ('great'), ('super');
     ALTER TABLE AcmeData ADD COLUMN atid INT NOT NULL DEFAULT 1 REFERENCES AcmeType;
     

   • second schema upgrade create_002.sql

     INSERT INTO AcmeType(atype) VALUES ('wow'), ('incredible');
     

2. Generate a psql-script from these for the target application:

   pg-schema-version -a acme create_*.sql > acme.sql
   

3. Execute the script against a database to bring its schema up to date.

   # first time MUST use command create
   psql -v psv=create acme < acme.sql
   # psv command set to create
   # psv dry create for acme, enable with -v psv=create:wet
   # psv script will create infra, register acme and execute all steps
   
   psql -v psv=create:wet < acme.sql
   # psv wet run for acme
   # psv creating psv infra
   # psv registering app acme
   # psv applying acme 1
   # psv applying acme 2
   # psv applying acme 3
   # psv acme version: 3
   # psv wet run for acme done
   
   # on rerun, do nothing
   psql -v psv=wet < acme.sql
   # psv wet run for app acme
   # psv skipping acme 1
   # psv skipping acme 2
   # psv skipping acme 3
   # psv acme version: 3
   # psv wet run for acme done
   

Features

The python script generates a reasonably safe re-entrant idempotent SQL script driven by psql-variable psv with value command:version:moist

• available commands are (default is run):
  • init just initialize an empty psv infrastructure if needed.
  • register add new application to psv versioning if needed.
  • run apply required steps on an already registered application.
  • create do init, register and run.
  • unregister remove application from psv versioning if needed.
  • remove drop psv infrastructure.
  • help show some help.
  • status show version status of applications.
  • catchup update application version status without actually executing steps (imply init and register).
• versions are integers designating the target step, default is latest.
• available moistures are (default is dry):
  • dry meaning that no changes are applied.
  • wet to trigger actual changes.

The only way is forward: there is no provision to go back to a previous state. However, note that schema steps are performed in a transaction, so that it can only fail one full step at a time.

If a script contains a special -- psv: some description comment, the description is recorded and shown on command status.

Caveats

Only dream of running the generated SQL scripts if you have a working (i.e. actually tested) backup of your data.

Always run dry and read the output carefully before running wet.

There is no magic involved, you can still shot yourself in the foot, although with an effort.

For safety, SQL schema creation scripts must NOT:

• include backslash commands which may interfere with the script owns.
• include SQL transaction commands.

Imperfect checks are performed to try to detect the above issues. They can be circumvented with option --trust-scripts.

Always test your scripts with care before applying it to production data.

Versions

TODO

• check provided strings, eg app name and others? escaping?
• default phase? status? run? help?
• rename run phase? apply? exec?
• reverse?
• write a tutorial
• write recipes

0.3 on 2024-10-19

• add unregister and catchup commands
• add setting a version target for a run
• add filename and description fields
• add verbose option
• show description on status
• escape schema and table identifiers
• refactor application registration
• improve documentation

0.2 on 2024-10-15

• activate GitHub pages
• working GitHub CI
• add coverage check
• add markdown check
• use exit code 3 for output file

0.1 on 2024-10-14

• initial beta version for testing

License

This code is Public Domain.

All software has bug, this is software, hence… Beware that you may lose your hairs or your friends because of it. If you like it, feel free to send a postcard to the author.