django-migrate-sql 0.1.1
Migration support for raw SQL in Django
License: BSD

django-migrate-sql
==================

|Build Status| |codecov.io|

Django Migrations support for raw SQL.

About
-----

This tool implements mechanism for managing changes to custom SQL
entities (functions, types, indices, triggers) using built-in migration
mechanism. Technically creates a sophistication layer on top of the
``RunSQL`` Django operation.

What it does
------------

-  Makes maintaining your SQL functions, custom composite types, indices
   and triggers easier.
-  Structures SQL into configuration of **SQL items**, that are
   identified by names and divided among apps, just like models.
-  Automatically gathers and persists changes of your custom SQL into
   migrations using ``makemigrations``.
-  Properly executes backwards/forwards keeping integrity of database.
-  Create -> Drop -> Recreate approach for changes to items that do not
   support altering and require dropping and recreating.
-  Dependencies system for SQL items, which solves the problem of
   updating items, that rely on others (for example custom
   types/functions that use other custom types), and require dropping
   all dependency tree previously with further recreation.

What it does not
----------------

-  Does not parse SQL nor validate queries during ``makemigrations`` or
   ``migrate`` because is database-agnostic. For this same reason
   setting up proper dependencies is user's responsibility.
-  Does not create ``ALTER`` queries for items that support this, for
   example ``ALTER TYPE`` in Postgre SQL, because is database-agnostic.
   In case your tools allow rolling all the changes through ``ALTER``
   queries, you can consider not using this app **or** restructure
   migrations manually after creation by nesting generated operations
   into ```state_operations`` of
   ``RunSQL`` <https://docs.djangoproject.com/en/1.8/ref/migration-operations/#runsql>`__
   that does ``ALTER``.
-  (**TODO**)During ``migrate`` does not restore full state of items for
   analysis, thus does not notify about existing changes to schema that
   are not migrated **nor** does not recognize circular dependencies
   during migration execution.

Installation
------------

Install from PyPi:

::

    $ pip install django-migrate-sql

Add ``migrate_sql`` to ``INSTALLED_APPS``:

.. code:: python

    INSTALLED_APPS = [
        # ...
        'migrate_sql',
    ]

App defines a custom ``makemigrations`` command, that inherits from
Django's core one, so in order ``migrate_sql`` app to kick in put it
after any other apps that redefine ``makemigrations`` command too.

Usage
-----

1) Create ``sql_config.py`` module to root of a target app you want to
   manage custom SQL for.

2) Define SQL items in it (``sql_items``), for example:

.. code:: python

    # PostgreSQL example.
    # Let's define a simple function and let `migrate_sql` manage it's changes.

    from migrate_sql.config import SQLItem

    sql_items = [
        SQLItem(
            'make_sum',   # name of the item
            'create or replace function make_sum(a int, b int) returns int as $$ '
            'begin return a + b; end; '
            '$$ language plpgsql;',  # forward sql
            reverse_sql='drop function make_sum(int, int);',  # sql for removal
        ),
    ]

3) Create migration ``./manage.py makemigrations``:

   ::

       Migrations for 'app_name':
         0002_auto_xxxx.py:
       - Create SQL "make_sum"

You can take a look at content this generated:

.. code:: python

    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    from django.db import migrations, models
    import migrate_sql.operations


    class Migration(migrations.Migration):
        dependencies = [
            ('app_name', '0001_initial'),
        ]
        operations = [
            migrate_sql.operations.CreateSQL(
                name='make_sum',
                sql='create or replace function make_sum(a int, b int) returns int as $$ begin return a + b; end; $$ language plpgsql;',
                reverse_sql='drop function make_sum(int, int);',
            ),
        ]

4) Execute migration ``./manage.py migrate``:

   ::

       Operations to perform:
         Apply all migrations: app_name
       Running migrations:
         Rendering model states... DONE
         Applying app_name.0002_xxxx... OK

Check result in ``./manage.py dbshell``:

::

    db_name=# select make_sum(12, 15);
     make_sum
    ----------
           27
    (1 row)

Now, say, you want to change the function implementation so that it
takes a custom type as argument:

5) Edit your ``sql_config.py``:

.. code:: python

    # PostgreSQL example #2.
    # Function and custom type.

    from migrate_sql.config import SQLItem

    sql_items = [
        SQLItem(
            'make_sum',  # name of the item
            'create or replace function make_sum(a mynum, b mynum) returns mynum as $$ '
            'begin return (a.num + b.num, 'result')::mynum; end; '
            '$$ language plpgsql;',  # forward sql
            reverse_sql='drop function make_sum(mynum, mynum);',  # sql for removal
            # depends on `mynum` since takes it as argument. we won't be able to drop function
            # without dropping `mynum` first.
            dependencies=[('app_name', 'mynum')],
        ),
        SQLItem(
            'mynum'   # name of the item
            'create type mynum as (num int, name varchar(20));',  # forward sql
            reverse_sql='drop type mynum;',  # sql for removal
        ),
    ]

6) Generate migration ``./manage.py makemigrations``:

::

    Migrations for 'app_name':
      0003_xxxx:
        - Reverse alter SQL "make_sum"
        - Create SQL "mynum"
        - Alter SQL "make_sum"
        - Alter SQL state "make_sum"

You can take a look at the content this generated:

.. code:: python

    # -*- coding: utf-8 -*-
    from __future__ import unicode_literals
    from django.db import migrations, models
    import migrate_sql.operations


    class Migration(migrations.Migration):
        dependencies = [
            ('app_name', '0002_xxxx'),
        ]
        operations = [
            migrate_sql.operations.ReverseAlterSQL(
                name='make_sum',
                sql='drop function make_sum(int, int);',
                reverse_sql='create or replace function make_sum(a int, b int) returns int as $$ begin return a + b; end; $$ language plpgsql;',
            ),
            migrate_sql.operations.CreateSQL(
                name='mynum',
                sql='create type mynum as (num int, name varchar(20));',
                reverse_sql='drop type mynum;',
            ),
            migrate_sql.operations.AlterSQL(
                name='make_sum',
                sql='create or replace function make_sum(a mynum, b mynum) returns mynum as $$ begin return (a.num + b.num, \'result\')::mynum; end; $$ language plpgsql;',
                reverse_sql='drop function make_sum(mynum, mynum);',
            ),
            migrate_sql.operations.AlterSQLState(
                name='make_sum',
                add_dependencies=(('app_name', 'mynum'),),
            ),
        ]

***NOTE:** Previous function is completely dropped before creation
because definition of it changed. ``CREATE OR REPLACE`` would create
another version of it, so ``DROP`` makes it clean.*

***If you put ``replace=True`` as kwarg to an ``SQLItem`` definition, it
will NOT drop + create it, but just rerun forward SQL, which is
``CREATE OR REPLACE`` in this example.***

7) Execute migration ``./manage.py migrate``:

::

    Operations to perform:
      Apply all migrations: app_name
    Running migrations:
      Rendering model states... DONE
      Applying brands.0003_xxxx... OK

Check results:

::

    db_name=# select make_sum((5, 'a')::mynum, (3, 'b')::mynum);
      make_sum
    ------------
     (8,result)
    (1 row)

    db_name=# select make_sum(12, 15);
    ERROR:  function make_sum(integer, integer) does not exist
    LINE 1: select make_sum(12, 15);
                   ^
    HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

For more examples see ``tests``.

Feel free to `open new
issues <https://github.com/klichukb/django-migrate-sql/issues>`__.

.. |Build Status| image:: https://travis-ci.org/klichukb/django-migrate-sql.svg?branch=master
   :target: https://travis-ci.org/klichukb/django-migrate-sql
.. |codecov.io| image:: https://img.shields.io/codecov/c/github/klichukb/django-migrate-sql/master.svg
   :target: https://codecov.io/github/klichukb/django-migrate-sql?branch=master