db-migrator 1.0.2

Python package to migrate postgresql database

Settings

dbmigrator requires a few settings to work:

• --migrations-directory: the directory that contains all the migrations

• --context: name of the python package containing an entry point to the migrations directory

• --db-connection-string: database host, port, name, user, password etc for connecting to postgres

• --config: a config file that contains the above settings

See dbmigrator -h.

To set the migrations directory using an entry point, in mymodule setup.py:

setup(
    ...
    entry_points={
        'dbmigrator': [
            'migrations_directory = mymodule.main:migrations_directory',
            ],
        },
    )

Important note: For the settings from setup.py to be picked up, before running dbmigrator, first run python setup.py develop or python setup.py install.

Then in mymodule/main.py:

import os

migrations_directory = '{}/sql/migrations'.format(
    os.path.abspath(os.path.dirname(__file__)))

or:

import os

def migrations_directory():
    return '{}/sql/migrations'.format(
        os.path.abspath(os.path.dirname(__file__)))

or with a config file, development.ini, that looks like this:

[app:main]
db-connection-string = postgres://dbuser@localhost/dbname

generate

Generate a migration script in the migrations directory.

Example usage:

dbmigrator generate add_id_to_users

generates a file called migrations/20151217170514_add_id_to_users.py with content:

# Uncomment should_run if this is a repeat migration
# def should_run(cursor):
#     # TODO return True if migration should run


def up(cursor):
    # TODO migration code
    pass


def down(cursor):
    # TODO rollback code
    pass

init

Initialize schema migrations table. By default, all the migrations are assumed to have been applied to the database.

Example usage:

dbmigrator --db-connection-string='postgres://dbuser@localhost/dbname' init

There is an option to manually set the version of the database. For example, if none of the migrations have been applied to the database, you can:

dbmigrator --config=development.ini init --version=0

list

List migration versions, names, whether it has been applied and the date applied.

Example usage:

$ dbmigrator --config=development.ini list
version        | name            | is applied | date applied
----------------------------------------------------------------------
20151217170514   add_id_to_users   True         2016-01-31 00:15:01.692570+01:00
20151218145832   add_karen_to_us   False*
20160107200351   blah              deferred

A * in the “is applied” column means that the migration is a repeated migration.

To see the full migration name, use --wide:

$ dbmigrator --config=development.ini list --wide
version        | name               | is applied | date applied
----------------------------------------------------------------------
20151217170514   add_id_to_users      True         2016-01-31 00:15:01.692570+01:00
20151218145832   add_karen_to_users   False*
20160107200351   blah                 deferred

migrate

Run pending migrations.

For example, with two migrations in the migrations directory,

migrations/20151217170514_add_id_to_users.py:

from dbmigrator import super_user

def up(cursor):
    # TODO migration code
    pass

    # if a super user database connection is needed
    with super_user() as super_cursor:
        pass

def down(cursor):
    # TODO rollback code
    pass

and

migrations/20151218145832_add_karen_to_users.py:

def up(cursor):
    cursor.execute('ALTER TABLE users ADD COLUMN karen TEXT')

def down(cursor):
    cursor.execute('ALTER TABLE users DROP COLUMN karen')

To run the migrations:

$ dbmigrator migrate
Running migration 20151217170514 add_id_to_users

Running migration 20151218145832 add_karen_to_users
---
+++
@@ -4005,21 +4005,22 @@
     first_name text,
     firstname text,
     last_name text,
     surname text,
     full_name text,
     fullname text,
     suffix text,
     title text,
     email text,
     website text,
-    is_moderated boolean
+    is_moderated boolean,
+    karen text
 );

 ALTER TABLE public.users OWNER TO rhaptos;

 --
 -- Name: abstractid; Type: DEFAULT; Schema: public; Owner: rhaptos
 --

 ALTER TABLE ONLY abstracts ALTER COLUMN abstractid SET DEFAULT nextval('abstracts_abstractid_seq'::regclass);

or to run migrations up to a specific version:

$ dbmigrator migrate version=20151217170514
Running migration 20151217170514 add_id_to_users

if all migrations have already been run:

$ dbmigrator migrate
No pending migrations.  Database is up to date.

To write a repeat migration, make sure your migration has should_run defined:

def should_run(cursor):
    return os.path.exists('data.txt')


def up(cursor):
    with open('data.txt') as f:
        data = f.read()
    cursor.execute('INSERT INTO table VALUES (%s)', (data,))


def down(cursor):
    pass

The above migration will run every time migrate is called, except if it is marked as “deferred”. up is run if should_run returns True.

To write a deferred migration, add @deferred to the up function:

from dbmigrator import deferred


@deferred
def up(cursor):
    # this migration is automatically deferred

The above migration will not run unless you use migrate --run-deferred.

rollback

Rollback a migration.

For example, with two migrations in the migrations directory,

migrations/20151217170514_add_id_to_users.py:

def up(cursor):
    # TODO migration code
    pass

def down(cursor):
    # TODO rollback code
    pass

and

migrations/20151218145832_add_karen_to_users.py:

def up(cursor):
    cursor.execute('ALTER TABLE users ADD COLUMN karen TEXT')

def down(cursor):
    cursor.execute('ALTER TABLE users DROP COLUMN karen')

Make sure the database is up to date:

$ dbmigrator migrate
No pending migrations.  Database is up to date.

Now rollback the last migration:

$ dbmigrator rollback
Rolling back migration 20151218145832 add_karen_to_users
---
+++
@@ -4005,22 +4005,21 @@
     first_name text,
     firstname text,
     last_name text,
     surname text,
     full_name text,
     fullname text,
     suffix text,
     title text,
     email text,
     website text,
-    is_moderated boolean,
-    karen text
+    is_moderated boolean
 );

 ALTER TABLE public.users OWNER TO rhaptos;

 --
 -- Name: abstractid; Type: DEFAULT; Schema: public; Owner: rhaptos
 --

 ALTER TABLE ONLY abstracts ALTER COLUMN abstractid SET DEFAULT nextval('abstracts_abstractid_seq'::regclass);

To rollback the last 2 migrations:

$ dbmigrator rollback --steps=2
Rolling back migration 20151218145832 add_karen_to_users
---
+++
@@ -4005,22 +4005,21 @@
     first_name text,
     firstname text,
     last_name text,
     surname text,
     full_name text,
     fullname text,
     suffix text,
     title text,
     email text,
     website text,
-    is_moderated boolean,
-    karen text
+    is_moderated boolean
 );

 ALTER TABLE public.users OWNER TO rhaptos;

 --
 -- Name: abstractid; Type: DEFAULT; Schema: public; Owner: rhaptos
 --

 ALTER TABLE ONLY abstracts ALTER COLUMN abstractid SET DEFAULT nextval('abstracts_abstractid_seq'::regclass);

Rolling back migration 20151217170514 add_id_to_users

mark

Mark a migration as completed, not completed or deferred.

Example usage:

$ dbmigrator --config=development.ini --migrations-directory=migrations/ list
name                      | is applied | date applied
----------------------------------------------------------------------
20151217170514_add_id_to_   True         2016-01-31 00:15:01.692570+01:00
20151218145832_add_karen_   False
20160107200351_blah         False

To mark a migration as not completed:

$ dbmigrator --config=development.ini --migrations-directory=migrations/ mark -f 20151217170514
Migration 20151217170514 marked as not been run

$ dbmigrator --config=development.ini --migrations-directory=migrations/ list
name                      | is applied | date applied
----------------------------------------------------------------------
20151217170514_add_id_to_   False
20151218145832_add_karen_   False
20160107200351_blah         False

To mark a migration as completed:

$ dbmigrator --config=development.ini --migrations-directory=migrations/ mark -f 20151217170514
Migration 20151217170514 marked as completed

$ dbmigrator --config=development.ini --migrations-directory=migrations/ list
name                      | is applied | date applied
----------------------------------------------------------------------
20151217170514_add_id_to_   True         2016-06-13 16:39:58.777893+01:00
20151218145832_add_karen_   False
20160107200351_blah         False

To mark a migration as deferred means to ignore a migration when running migrate or rollback:

$ dbmigrator --config=development.ini --migrations-directory=migrations/ mark -d 20151217170514
Migration 20151217170514 marked as deferred

$ dbmigrator --config=development.ini --migrations-directory=migrations/ list
name                      | is applied | date applied
----------------------------------------------------------------------
20151217170514_add_id_to_   deferred     None
20151218145832_add_karen_   False
20160107200351_blah         False

---

CHANGELOG

1.0.2 (2017-09-25)

• create a savepoint around should_run - rollback on false, to restore db state

• Add __version__ to dbmigrator

1.0.1 (2017-09-14)

• Update minimum version of psycopg2 required

1.0.0 (2017-08-24)

• Add “*” to indicate repeated migrations in list

• Raise SystemExit when mark migration not found

• Add deferred migrations

• Add repeat conditional migrations

• Change print statement to logger.debug

• Reduce noise in tests when installing test packages

• Add CLI test for rollback

• Add CLI test for migrate

• Add super user database connections

• Move logger from __init__.py to utils.py

• Change python 3.4 to 3.5 in .travis.yml

0.2.0 (2016-06-24)

• Add psycopg2 wait callback so ctrl-c stops a migration

• Implement mark a migration as deferred: mark -d

• Add –wide option for list to display the full migration name

• Update command usage in README

• Add help message to dbmigrator commands

• Warn user with dbmigrator init --help if schema migrations doesn’t exist

• Add CLI test for generating migrations

0.1.4 (2016-05-12)

• Change dbmigrator list to list version and migration name separately

0.1.3 (2016-04-19)

• Separate cli tests into different test cases

• Change test config to use the travis database

• Change logger.warn to logger.warning

• Add test case for cli verbose option

• Add tests for dbmigrator list

• Add CLI init test case for multiple contexts

• Add travis and coveralls badges to README

• Move cli.main import to base test case

• Refactor code for marking a migration as completed or not

• Add mark command for marking a migration as completed or not

• Add tests for the mark command

• Update README with example usage for the mark command

0.1.2 (2016-03-18)

• bug:

  Fix list to not explode when no migrations directories are given

• Log warning message for list if schema_migrations table doesn’t exist

• Change --verbose to set the logger level to debug

• Add test for utils.timestamp

• Add test for utils.rollback_migration

• Add test for utils.run_migration

• Add test for utils.get_pending_migrations

• Add test for utils.get_migrations

• Add test for utils.import_migration

• Make dbmigrator generate generate pep8 compliant code

• Fix dbmigrator generate migrations directory lookup

• Add test for utils.with_cursor

• Add test for utils.get_settings_from_config

• Add integration tests with test packages

• Add pep8 to travis

• Add a logger for dbmigrator that writes to stdout

• Change version information option to -V

• Sort migrations by their filename, not the full path

0.1.1 (2016-02-24)

• Stop changing schema_migrations data if the table already exists

• Rewrite --version to use argparse version action

• Add unit test for --version

• Add travis CI configuration file

• Fix default context (working directory) being a string instead of a list

0.1.0 (2016-02-12)

• Allow multiple migrations directories / context to be specified

• Add –verbose which prints the configuration used by dbmigrator

• Use datetime utcnow instead of now for timestamps

• Add --version to show the version of db-migrator installed

0.0.7 (2016-02-10)

• Add option --context to dbmigrator in order to load entry points

• Raise error if config file is specified but not found

0.0.6 (2016-02-08)

• Fix missing migrations directory the “init” command

0.0.5 (2016-02-08)

• Include CHANGELOG in distribution’s manifest

0.0.4 (2016-02-08)

• Show warning message instead of error if migrations directory is undefined

• Add CHANGELOG

0.0.3 (2016-02-08)

• Return error if migrations directory is undefined

0.0.2 (2016-02-03)

• Fix invalid rst in README

• Update setup.py description and long_description

• Update setup.py to include README as the description and fix url

• Update README and cli after removing default value for config file

• Remove default config path (development.ini)

• Add dbmigrator list command

• Fix dbmigrator rollback to stop if there are no migrations to rollback

• Print message after initializing schema migrations

• Add note to run python setup.py install if using entry points

• Add migrations directory setting from setup.py entry point in README

• Update command names for init and generate in README

• Get settings from setup.py entry points

• Remove __init__.py generation in migrations directory

• Add option version to dbmigrator init for setting the initial version

• Rename “generate_migration” command to “generate”

• Rename “init_schema_migrations” command to “init”

• Change the way migrations are imported so it works in python2

• Add “applied” timestamp to schema migrations table

• Add # -*- coding: utf-8 -*- to the top of generated migration files

• Add README

• Add command “rollback” to rollback migrations

• Add command “migrate” to run pending migrations

• Add migrations to table when running init_schema_migrations

• Add command for creating the schema migrations table

• Create dbmigrator cli and “generate_migration” command

• Create dbmigrator python package