migra: PostgreSQL migrations made almost painless
=================================================
Migrations are without doubt the most cumbersome and annoying part of working with SQL databases. So much so that some people think that schemas themselves are bad!
But schemas are actually good. Enforcing data consistency and structure is a good thing. It’s the migration tooling that is bad, because it’s harder to use than it should be. `migra` is an attempt to change that, and make migrations easy, safe, and reliable instead of something to dread.
How it Works
------------
Think of migra as a diff tool for schemas. Say database A and database B have a similar but slightly different schemas. `migra` will detect the differences and output the SQL needed to transform A to B.
Being a python library, you can use it programmatically and use it to build your own migration scripts, tools, etc. Installing `migra` also installs the migra command, so you can use it as follows:
.. code-block:: shell
$ migra postgresql:///a postgresql:///b
alter table "public"."products" add column newcolumn text;
alter table "public"."products" add constraint "x" CHECK ((price > (0)::numeric));
If *b* is the target schema, then a new column and constraint needs to be applied to *a* to make it match *b*'s schema. Once we've reviewed the autogenerated SQL and we're happy with it, we can apply these changes as easily as:
.. code-block:: shell
$ migra --unsafe postgresql:///a postgresql:///b > migration_script.sql
# Then after careful review (obviously)...
$ psql a --single-transaction -f migration_script.sql
Migration complete!
IMPORTANT: Practice safe migrations
-----------------------------------
**Migrations can never be fully automatic**. As noted above **ALWAYS REVIEW MIGRATION SCRIPTS CAREFULLY, ESPECIALLY WHEN DROPPING TABLES IS INVOLVED**.
`migra` is in the alpha stage. Even more reason not trust it too much, and review and test your migrations thoroughly.
`migra` will deliberately throw an error if any generated statements feature the word "drop". This safety feature is by no means bulletproof either, but might prevent a few obvious blunders.
If you want to generate "drop ..." statements, you need to use the --unsafe flag if using the command, or if using the python package directly, `set_safety(` to false on your `Migration` object.
Python Code
-----------
Here's how the migra command is implemented under the hood (with a few irrelevant lines removed).
As you can see, it's pretty simple (`S` here is a context manager that creates a database session from a database URL).
.. code-block:: python
from migra import Migration
from sqlbag import S
with S(args.dburl_from) as s0, S(args.dburl_target) as s1:
m = Migration(s0, s1)
m.set_safety(False)
m.add_all_changes()
print(m.sql)
Here the code just opens connections to both databases for the Migration object to analyse. `m.add_all_changes()` generates the SQL statements for the changes required, and adds to the migration object's list of pending changes. The necessary SQL is now available as a property.
Documentation
-------------
`migra` is in early alpha and documentation is scarce so far. We are working on remedying this. Watch this space.
Features and Limitations
-----------
Migra will detect changes to tables, views, materialized views, indexes, constraints, sequences, and extensions.
In terms of specific PostgreSQL feature limitations, `migra` is only confirmed to work with SQL/PLPGSQL functions so far. Doesn't track changes to function modifiers (IMMUTABLE/STABLE/VOLATILE, STRICT, RETURNS NULL ON NULL INPUT, etc).
Installation
------------
Assuming you have `pip <https://pip.pypa.io>`_ installed, all you need to do is install as follows:
.. code-block:: shell
$ pip install migra
If you don't have psycopg2 (the PostgreSQL driver) installed yet, you can install this at the same time with:
.. code-block:: shell
$ pip install migra[pg]
=================================================
Migrations are without doubt the most cumbersome and annoying part of working with SQL databases. So much so that some people think that schemas themselves are bad!
But schemas are actually good. Enforcing data consistency and structure is a good thing. It’s the migration tooling that is bad, because it’s harder to use than it should be. `migra` is an attempt to change that, and make migrations easy, safe, and reliable instead of something to dread.
How it Works
------------
Think of migra as a diff tool for schemas. Say database A and database B have a similar but slightly different schemas. `migra` will detect the differences and output the SQL needed to transform A to B.
Being a python library, you can use it programmatically and use it to build your own migration scripts, tools, etc. Installing `migra` also installs the migra command, so you can use it as follows:
.. code-block:: shell
$ migra postgresql:///a postgresql:///b
alter table "public"."products" add column newcolumn text;
alter table "public"."products" add constraint "x" CHECK ((price > (0)::numeric));
If *b* is the target schema, then a new column and constraint needs to be applied to *a* to make it match *b*'s schema. Once we've reviewed the autogenerated SQL and we're happy with it, we can apply these changes as easily as:
.. code-block:: shell
$ migra --unsafe postgresql:///a postgresql:///b > migration_script.sql
# Then after careful review (obviously)...
$ psql a --single-transaction -f migration_script.sql
Migration complete!
IMPORTANT: Practice safe migrations
-----------------------------------
**Migrations can never be fully automatic**. As noted above **ALWAYS REVIEW MIGRATION SCRIPTS CAREFULLY, ESPECIALLY WHEN DROPPING TABLES IS INVOLVED**.
`migra` is in the alpha stage. Even more reason not trust it too much, and review and test your migrations thoroughly.
`migra` will deliberately throw an error if any generated statements feature the word "drop". This safety feature is by no means bulletproof either, but might prevent a few obvious blunders.
If you want to generate "drop ..." statements, you need to use the --unsafe flag if using the command, or if using the python package directly, `set_safety(` to false on your `Migration` object.
Python Code
-----------
Here's how the migra command is implemented under the hood (with a few irrelevant lines removed).
As you can see, it's pretty simple (`S` here is a context manager that creates a database session from a database URL).
.. code-block:: python
from migra import Migration
from sqlbag import S
with S(args.dburl_from) as s0, S(args.dburl_target) as s1:
m = Migration(s0, s1)
m.set_safety(False)
m.add_all_changes()
print(m.sql)
Here the code just opens connections to both databases for the Migration object to analyse. `m.add_all_changes()` generates the SQL statements for the changes required, and adds to the migration object's list of pending changes. The necessary SQL is now available as a property.
Documentation
-------------
`migra` is in early alpha and documentation is scarce so far. We are working on remedying this. Watch this space.
Features and Limitations
-----------
Migra will detect changes to tables, views, materialized views, indexes, constraints, sequences, and extensions.
In terms of specific PostgreSQL feature limitations, `migra` is only confirmed to work with SQL/PLPGSQL functions so far. Doesn't track changes to function modifiers (IMMUTABLE/STABLE/VOLATILE, STRICT, RETURNS NULL ON NULL INPUT, etc).
Installation
------------
Assuming you have `pip <https://pip.pypa.io>`_ installed, all you need to do is install as follows:
.. code-block:: shell
$ pip install migra
If you don't have psycopg2 (the PostgreSQL driver) installed yet, you can install this at the same time with:
.. code-block:: shell
$ pip install migra[pg]
1 maintainer
Metadata
1 maintainer
Release files for migra 0.1.1469411508
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| migra-0.1.1469411508.tar.gz | 5.4 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| migra-0.1.1469411508-py2.py3-none-any.whl | Python 2, Python 3 | none | any | Details |
Total release size: 14.9 kB
Release files / migra-0.1.1469411508.tar.gz
| Download URL | migra-0.1.1469411508.tar.gz |
|---|---|
| Size | 5.4 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
f5c809b5c4f66c3151306fd9f2d4647aad75e2d0ec9bf124aeab5145fd6d9963
|
|
BLAKE2b-256 checksum How to use checksums |
8b41a82b49cafb990aca3f90b7fa86eb003cabc769b8f4297be0ebfd8b0b9a6d
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
Release files / migra-0.1.1469411508-py2.py3-none-any.whl
| Download URL | migra-0.1.1469411508-py2.py3-none-any.whl |
|---|---|
| Size | 9.5 kB |
| Tags | Python 2 Python 3 |
|
SHA-256 checksum How to use checksums |
3135715b8ca7e80f6bbb8da0ec040a0f42cf8b359c4a257841886cf645273f73
|
|
BLAKE2b-256 checksum How to use checksums |
edc66569e6e708d61faa34404ea8e626efed17f98720eb69e13d3de1d8112f55
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
