Skip to main content

Python package to migrate postgresql database

Project description

https://travis-ci.org/karenc/db-migrator.svg?branch=master https://coveralls.io/repos/github/karenc/db-migrator/badge.svg?branch=master

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.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

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

db-migrator-1.0.1.tar.gz (30.1 kB view details)

Uploaded Source

Built Distributions

db_migrator-1.0.1-py2.7.egg (47.1 kB view details)

Uploaded Egg

db_migrator-1.0.1-py2-none-any.whl (27.9 kB view details)

Uploaded Python 2

File details

Details for the file db-migrator-1.0.1.tar.gz.

File metadata

  • Download URL: db-migrator-1.0.1.tar.gz
  • Upload date:
  • Size: 30.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for db-migrator-1.0.1.tar.gz
Algorithm Hash digest
SHA256 b7ed1700b9aa51dd304f24ae6a24717464ad172939503ae07f0c64a01100f0da
MD5 c225df3ca5d9b5213174478815833443
BLAKE2b-256 d5ad6bfd6bc6f95b8aea6ebb7ea89cd479deb35d69566797a8e05a3b2a3056f8

See more details on using hashes here.

File details

Details for the file db_migrator-1.0.1-py2.7.egg.

File metadata

File hashes

Hashes for db_migrator-1.0.1-py2.7.egg
Algorithm Hash digest
SHA256 f9b56dd73c713f36dbc04ea2665d9a6dfd743e651b92d7d334b3352d4764ceea
MD5 c7f20acbac9cd718bb38d76e69e0272a
BLAKE2b-256 ff5ecc39f0734514bd3ad509ed6811d0a93aa2ae669a34a456bafd890808b017

See more details on using hashes here.

File details

Details for the file db_migrator-1.0.1-py2-none-any.whl.

File metadata

File hashes

Hashes for db_migrator-1.0.1-py2-none-any.whl
Algorithm Hash digest
SHA256 322eb70f261167146807d78c2cf52ca68c2a1f5b804746cb07caf0ab0a293e52
MD5 d812a324424afe39f6d3e2bb3897f909
BLAKE2b-256 b20428eb8287f7dcbd4bddf3f8ad8c8909a529821dc1167839210f6ebe1bad76

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page