Database schema migration tool for people who do not afraid SQL
PGmigrate helps you to evolve your database together with your application.
The fundamental unit of PGmigrate is a single SQL snippet called patch.
$ cat 000049_Added_index_on_CategorySlug.sql --- id: 89ccfca6-6851-11e1-99d8-a088b4e3b168 --- author: serg --- memo: Added index on CategorySlug --- date: 2012-03-07 14:32 CREATE UNIQUE INDEX catalog_category_slug_shop_id_slug ON catalog_category_slug USING btree (shop_id, slug);
As you can see patch is a valid SQL file, which even can be executed directly. It also has nice, human readable file name, and some metadata.
$ pgmigrate2 init postgresql://user@password/testdb $
This will create table __applied_patches__ in testdb. This table is used to track which patches are already applied.
$ mkdir patchrepo $ pgmigrate2 newpatch patchrepo ... edit patch in your text editor... Wrote 'patchrepo/000001_creating_table_x.sql' $
This will create empty patch and open it in your text editor. Enter patch SQL, and optional memo, describing what is the function of this patch.
PGmigrate will create a file like patchrepo/000001_creating_table_x.sql where 000001 is a patch serial number, and creating_table_x is a slugified patch memo. PGmigrate will fill rest of patch metadata by itself.
$ pgmigrate2 check patchrepo/ postgresql://user@password/testdb Need to apply: creating table x $
Check takes all patches in patch repo, and print a list of patches which are need to be applied to testd.
$ pgmigrate2 migrate patchrepo/ postgresql://user@password/testdb Need to apply 1 patches: Applying 'creating table x' $
Migrate takes all patches from patch repo, and sequentially applies those of them, whose id are not present in __applied_patches__ tables of testdb.
Here is example how we use PGmigrate in our project:
### Database migration commands @finaloption.command(config_opts) def dbmigrate(config): from shopium.core.config import read_config config = read_config(config) from pgmigrate2 import api return api.migrate('migrations', config.db_uri) @finaloption.command(config_opts) def dbnewpatch(config): from shopium.core.config import read_config config = read_config(config) from pgmigrate2 import api import subprocess path = api.newpatch('migrations') if path: subprocess.check_call('hg add %s' % path, shell=True) # add created patch to Mercurial repo @finaloption.command(config_opts) def dbcheckstatus(config): from shopium.core.config import read_config config = read_config(config) from pgmigrate2 import api api.check_status('migrations', config.db_uri)
That’s abbreviation for PostgreSQL Migrate.
Actually no. Internally we use SQLAlchemy, which is database agnostic, so theoretically it should work with any database. But since it is raw-SQL based, you need to use same DBMS everywhere.
We belive that it if you have a good developers — it makes no sense to hide power to SQL from them. So, with PGmigrate you have a full control on what would be executed. Also since we do not have any fancy stuff, PGmigrate is quite simple, and can be used in almost any development model.
In normal circumstances downgrades are rarely used. And since they are rarely used nobody tests them, and/or sometimes do not write them at all. We belive that having unreliable downgrades are worse than not having them at all.
So, if something goes wrong, just roll forwards instead of rolling back. Or, if you really need to roll back, you can craft downgrade SQL manually.
You can check https://github.com/piranha/nomad/ which has similar design goals, has support for executable patches, but slightly cluttered patches repo structure.
Sure. But we do not have a management commands so far, so, you will need to write them by youself (you can contribute them back to PGmigrate afterwards).
Sergey Kirillov Michał Papierski