Skip to main content

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.

Status Tests Coverage Python Version License Badges

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.

Metadata

Release files for pg-schema-version 0.3

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pg-schema-version 0.3
File Size Uploaded Provenance Actions
pg_schema_version-0.3.tar.gz 18.3 kB Without provenance Details for pg_schema_version-0.3.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for pg-schema-version 0.3
File Interpreter ABI Platform Actions
pg_schema_version-0.3-py3-none-any.whl Python 3 none any Details for pg_schema_version-0.3-py3-none-any.whl

Total release size: 28.7 kB

Release files / pg_schema_version-0.3.tar.gz

Download URL pg_schema_version-0.3.tar.gz
Size 18.3 kB
Tags Source
SHA-256 checksum
How to use checksums
f35c308ebce6331fddbd9a7330f4d12c546b6cafd1f1e4eb7ab8054002ae7b55
BLAKE2b-256 checksum
How to use checksums
5ba2379be28278dcab0c51266da944299e61ec371f0cf0e71152da045d35a260
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/5.1.1 CPython/3.12.3

Release files / pg_schema_version-0.3-py3-none-any.whl

Download URL pg_schema_version-0.3-py3-none-any.whl
Size 10.4 kB
Tags Python 3
SHA-256 checksum
How to use checksums
ef23a1da0d7c0df0b9c151f7fd2e52e87fecb1a5e4c31c2544dd6493f7942610
BLAKE2b-256 checksum
How to use checksums
fe60ee0d2c105bcc9ac33669cbd3c59c3554241f85d48eb658743f4dbcd24c8c
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/5.1.1 CPython/3.12.3

Security for pg-schema-version 0.3

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page