NOTE: this is a forked version of https://github.com/depesz/Versioning
This project strives to provide a simple way to manage changes to a Postgres database.
Instead of making changes on the development server, then finding differences between production and development, deciding which ones should be installed on production, and finding a way to install them - you start with writing diffs directly!
More detailed description is (so far) in here: http://www.depesz.com/index.php/2010/08/22/versioning/
Suppose the initial design of the database was badly done. For instance:
DO $$
DECLARE
patch_exists int := _v.register_patch('160822', 'initial database design');
BEGIN
IF patch_exists THEN
RETURN;
END IF;
/* the actual code to change to the database starts here */
CREATE TABLE stuff(id int primary key, email text unique);
END;
$$
Meanwhile we have some data inserted by the application:
INSERT INTO stuff VALUES (10, 'c'), (5, 'b'), (20, 'a');
Sometime in the future we conclude that a new separate users
table is needed. It should contain the data in stuff.email
(plus other new columns). A new patch is created to handle these changes:
DO $$
DECLARE
patch_exists int := _v.register_patch('160823', 'add the "users" table, delete "email" column from "stuff", move data');
BEGIN
IF patch_exists THEN
RETURN;
END IF;
/* the actual code to change to the database starts here */
-- add a new "users" table and fill it with the existing data
CREATE TABLE users(id serial primary key, email text unique);
INSERT INTO users(email)
SELECT email FROM stuff
ORDER BY id;
-- add a new column (fk) in the old "stuff" table and fill with the
-- correct reference
ALTER TABLE stuff
ADD COLUMN user_id int REFERENCES users(id);
UPDATE stuff
SET user_id = u.id
FROM ( SELECT * FROM users ) u
WHERE stuff.email = u.email;
-- delete the "email" column in the old table (not needed anymore)
ALTER TABLE stuff
DROP COLUMN email;
END;
$$
The statements relative to 'second-patch' will be executed only once because we return early if the patch has been already applied.
For simple operations like CREATE TABLE
we could simply use the IF NOT EXISTS
clause. The usefulness of pg-versioning is seen when there are more complex changes involved, in particular when there's data that has to be moved (an operation that should be done only once).
But even for simple operations it might make sense to use pg-versioning instead of relying on IF NOT EXISTS
. For instance, suppose a existing table is not needed anymore. We could use a DROP TABLE IF EXISTS
in the scripts. Without pg-versioning we will be creating and droppping that table everytime the scripts are run, without any purpose.
To install pg-versioning simply run the pg-versioning.sql
script in your
database (all of them: production, stage, test, devel, ...).
To uninstall, just run
DROP SCHEMA _v CASCADE;
The original project was started by depesz and lives here: https://github.com/depesz/Versioning
This fork has been changed a little in the register_patch
function: it returns an INT4
instead of setof INT4
:
- If the patch is already registered, returns 1 and won't register it (return early). Can be used to skip the execution of the patch, as shown in the example above.
- If the patch does not exist:
- If there are conflicting patches already registered, an exception is raised (as in the original version)
- If there are pre-requisites patches not registered, an exception is raised (as in the original version)
- otherwise, the patch is registered and returns 0
This way we can check for the return value of register_patch
and skip the execution of the statements if it returns 1.
The register_patch
function also accepts a new parameter at the end: description (text).
The unregister_patch
function was changed similarly:
- if the patch is required by some other patch, an exception is raised (as in the original version)
- if the patch is not registered, an exception is raised (as in the original version)
- otherwise, the patch is unregistered (deleted) and returns 0