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
-
Install from PyPi, e.g. with
pip:pip install pg-schema-version
-
Write a sequence of incremental postgres SQL data definition scripts. The
-- psv:comment header is mandatory to declare the application name, version and optional description.-
initial schema creation
create_000.sql-- psv: acme +1 Acme Schema v1.0 CREATE TABLE AcmeData(aid SERIAL PRIMARY KEY, data TEXT UNIQUE NOT NULL);
-
first schema upgrade
create_001.sql-- psv: acme +2 Acme Schema v1.1 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-- psv: acme +3 Acme Schema v2.0 INSERT INTO AcmeType(atype) VALUES ('wow'), ('incredible');
-
-
Generate a
psql-script from these for the target application:pg-schema-version -a acme create_*.sql > acme.sql
-
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 for application acme # psv dry create for acme on acme, enable with -v psv=create:latest:wet # psv will create infra, register acme and execute all steps psql -v psv=create:wet acme < acme.sql # psv for application acme # psv wet create for acme on acme # psv creating infra # psv registering acme # psv considering applying steps # psv acme version: 0 # psv applying acme 1 # psv applying acme 2 # psv applying acme 3 # psv acme version: 3 # psv wet create for acme done # on rerun, do nothing psql -v psv=wet acme < acme.sql # psv for application acme # psv wet apply for acme on acme # psv skipping acme registration # psv considering applying steps # psv acme version: 3 # psv skipping acme 1 # psv skipping acme 2 # psv skipping acme 3 # psv acme version: 3 # psv wet apply for acme done # show current status psql -v psv=status acme < acme.sql # …
app version description acme 3 Acme Schema v2.0 psv 0 •
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
apply):initjust initialize an empty psv infrastructure.registeradd new application to psv versioning.applyexecute required steps on an already registered application.createdo the 3 phases above: init, register and apply.unregisterremove application from psv versioning.removedrop psv infrastructure.helpshow some help.statusshow version status of applications.catchupupdate 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):drymeaning that no changes are applied.wetto 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.
Each provided script must contain a special -- psv: name +5432 description
header with:
namethe application name, which must be consistent accross all scripts.+5432the version number to apply, which will be checked for inconsistencies such as repetition or missing numbers.descriptionan optional description of the resulting application 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.
Beware that psql can execute arbitrary shell commands in your name with
\!.
Versions
TODO
- check provided strings, eg app name and others? escaping?
- default phase? status? run? help?
- reverse?
- each file contains a mandatory declaration
-- psv: … foo +nfoo -n: app foo schema n, reverse n.-a foois used to check the application name- must check that a continuous path exists before applying anything!
- each file contains a mandatory declaration
- check?
foo =n …? - write a tutorial
- write recipes
0.4 on 2024-10-20
- make psv comment header (
-- psv: foo +1 …) mandatory, including many sanity checks about names, versions… - rename
runtoapply - show status only when asked
- add
--partialoption to allow partial scripts (i.e. missing versions) - use
--appto check script consistency - check current status strictly before applying a step
- improve documentation, esp. the example
- improve tests about descriptions
- refactor script sources
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.
See online documentation. Sources and issues are on GitHub. Packages are distributed from PyPi.
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.4
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| pg_schema_version-0.4.tar.gz | 21.7 kB | Without provenance | Details for pg_schema_version-0.4.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| pg_schema_version-0.4-py3-none-any.whl | Python 3 | none | any | Details for pg_schema_version-0.4-py3-none-any.whl |
Total release size: 34.1 kB
Release files / pg_schema_version-0.4.tar.gz
| Download URL | pg_schema_version-0.4.tar.gz |
|---|---|
| Size | 21.7 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
2fdcc0ed6791b08cd3b367538a863359f35050d6b41691bcfa9653f92ac924f7
|
|
BLAKE2b-256 checksum How to use checksums |
6d1f6d43b020ef01200cce461fe3fcd0bf003319e667b6261d1117672f6134ae
|
| 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.4-py3-none-any.whl
| Download URL | pg_schema_version-0.4-py3-none-any.whl |
|---|---|
| Size | 12.3 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
034f9b3fc37ad51b759c55bdf79ae4fe15846f3848a87875eac4e16e7d7f6711
|
|
BLAKE2b-256 checksum How to use checksums |
d0bb2a1b76256974f9388530cb559cfa07d70c3806cade3f3d6a542d9898ed8d
|
| 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.4
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.