db-migrator 1.1.0

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.1.0 (2018-01-03)

• Add db-config-ini-key to CLI options
• Add --sort (version or applied) to dbmigrator list
• Allow mark to accept multiple timestamps
• Set dbmigrator settings before running utils tests
• Add quiet switch
• Add tests for no migrations to rollback
• Convert most output to logging
• Explicitly close all postgres db connections

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