SqlAlchemy Migrations for mysql and sqlite and testing

I really want to be a unit tester, I really do. Unfortunately it's one of those things I can't seem to get going. I start and end up falling short before I get over the initial setup hurdle. Or I get a couple of tests working, but then as I have a hard time trying to test parts of things I fade. So here goes my latest attempt. It's for a web app I'm working on at work and I REALLY want to have this under at least basic unit tests. Since it's a database talking web application my first step is to get a test db up and runnging to run my tests against. At least with that up I can start some actual web tests that add and alter objects via some ajax api calls.

In order to get a test db I first had to figure out how to setup a database for the tests. For speed and ease purposes I'd rather be able to use sqlite. This way I don't need to setup/reset a mysql db on each host I end up trying to run tests on.

Of course this is complicated because I'm using sqllchemy-migrate for my application. This means part of the testing should be to init a new sqlite db and then bring it up to the latest version. In order to do this I had to convert my existing migrations to work in both MySQL and Sqlite.

Step 1: I need a way to tell the migrations code to use the sqlite db vs the live mysql db. I've setup a manage.py script in my project root so I hacked it up to check for a --sqlite flag. Not that great, but it works. [sourcecode lang="python"] """ In order to support unit testing with sqlite we need to add a flag for specifying that db

python manage.py version_control --sqlite python manage.py upgrade --sqlite

Otherwise it will default to using the mysql connection string

""" from migrate.versioning.shell import main

import sys if '--sqlite' in sys.argv: main(url='sqlite:///apptesting.db',repository='app/migrations') else: main(url='mysql://connection_stringl',repository='app/migrations') [/sourcecode]

Step 2: Not all of my existing migrations were sqlite friendly. I had cheated and added some columns by straight sql like

[sourcecode lang="python"] from sqlalchemy import * from migrate import *

def upgrade(): sql = "ALTER TABLE jobs ADD COLUMN created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;" migrate_engine.execute(sql);

def downgrade(): sql = = "ALTER TABLE jobs DROP created;" migrate_engine.execute(sql); [/sourcecode]

This worked great with MySQL, but sqlite didn't like it. In order to get things to work both ways I moved to using the changeset tools to make these more SA happy.

[sourcecode lang="python"] from sqlalchemy import * from migrate import * from migrate.changeset import * from datetime import datetime

meta = MetaData(migrate_engine) jobs_table = Table('jobs', meta)

def upgrade(): col = Column('created', DateTime, default=datetime.now) col.create(jobs_table)

def downgrade(): sql = "ALTER TABLE jobs DROP created;" migrate_engine.execute(sql); [/sourcecode]

A couple of notes. This abstracted the column creation so that sqlite and mysql would take it. Notice I did NOT update the drop command. Sqlite won't drop columns, and I honestly didn't care because the goal is for my unit tests to be able to bring up a database instance for testing, I'm not going to run through the downgrade commands in the sqlite database.

Step 3. So with all of the migrations moved to do everything via SA abstracted code vs SQL strings, I was in business. My final problem was one migration in particular. I had changed a field from a varchar to a int field. Sqlite won't let you do simple 'ALTER TABLE...' and even when I had the command turned into SA based changeset code my db upgrade failed due to sqlite tossing an exception.

What did I do? I cheated. First, I updated the migration with the original column definition to an Integer field. I mean, any new installs could walk that migration up just fine. I happen to know that the two deployments right now have already made the change from varchar to int. So for them, the change won't break anything for upgrade/downgrade.

I then kept the migration with the change, but tossed it in a try/except block so that I could trap it nice and just output a message "If this fails, it's probably sqlite choking.". It's hackish, but works for all my use cases I need.

Now I can create a new test database with the commands [code] python manage.py version_control --sqlite python manage.py upgrade --sqlite [/code]

Now I can start building my test suite to use this as the bootstrap to create a test db. I'll have to them remove the file on teardown so that I don't get any errors, but that'll be part of the testing setup. Not in memory, but oh well...it works.