Schema management and syncing - dev recipes

Schema syncing is handy for speeding up database-related development and testing tasks.

Auto-syncing dev database to application code

When developing applications that work with a database, you'll inevitably have some kind of "target" schema in mind that you want the database to have.

This is often defined with database model classes (such as used by Django/Rails), or perhaps with a setup script written in raw SQL.

When developing your application, you'll generally have a local version of this database running for use while developing, tweaking and debugging your application.

The challenge is to keep this database in sync with your "target" schema as you tinker and continually modify the target to suit your app.

These sample scripts for databaseci and migra shows how you might use syncing to sync up your dev database almost automatically.

With databaseci:

import databaseci 

DB_URL = 'postgresql:///name_of_local_dev_database'

db = databaseci.db(DB_URL)

DEFINITION = "create table ...; create view ...;"


The equivalent with migra directly:

def sync():
    from sqlbag import S, temporary_database as temporary_db

    DB_URL = 'postgresql:///name_of_local_dev_database'

    with temporary_db() as TEMP_DB_URL:

        with S(DB_URL) as s_current, S(TEMP_DB_URL) as s_target:
            m = Migration(s_current, s_target)

            if m.statements:
                print('THE FOLLOWING CHANGES ARE PENDING:', end='\n\n')
                if input('Apply these changes?') == 'yes':
                    print('Not applying.')
                print('Already synced.')

Creating customized tasks and scripts

Every project is slightly different, so schema diffing doesn't try and solve every migration workflow problem you could possibly have. Instead, schema diffing makes it easier to write scripts to solve your own migration problems.

Setting up tests

You can use schema diffing to test the correctness of three things:

Testing before and after database versions

With the right testing framework and fixture setup code, you can configure your tests to run twice, against both the pre and post migration versions of your database.

Here's how to do that with python's pytest. Suppose you are using migra to generate a file called pending.sql that contains any pending migrations. You could then add something like this to your file:

import os.path
import databaseci

def load_pre_migration(db_url):

def load_post_migration(dburl):


pending = os.path.exists('MIGRATIONS/pending.sql')

if pending:

def db(request):
    with databaseci.temporary_db() as test_db_url:
        setup_method = request.param
        yield test_db_url

Testing against real data

You can make your testing more comprehensive by testing against not just the empty schema structure of your production database, but a copy populated with real application data.

If your production database is large, you really should be testing your migration against a copy of similar size, in order to detect any performance problems that could result from slow-running migration scripts.