Using Alembic API from inside application code

Question

I am using SQLite as an application file format (see here for why you would want to do this) for my PySide-based desktop application. That is, when a user uses my app, their data is saved in a single database file on their machine. I am using the SQLAlchemy ORM to communicate with the databases.

As I release new versions of the application, I may modify the database schema. I don't want users to have to throw away their data every time I change the schema, so I need to migrate their databases to the newest format. Also, I create temporary databases a lot to save subsets of the data for use with some external processes. I want to create these databases with alembic so they are tagged with the proper version.

I have a few questions:

• Is there a way to call alembic from inside my Python code? I think it's weird to have to use Popen to a pure Python module, but the docs just use alembic from the command line. Mainly, I need to change the database location to wherever the user's database is located.

• If that's not possible, can I specify a new database location from the command line without editing the .ini file? This would make calling alembic through Popen not a big deal.

• I see that alembic keeps its version information under a simple table called alembic_version, with one column called version_num and a single row specifying the version. Can I add an alembic_version table to my schema and populate it with the latest version when I create new databases so there is no overhead? Is that even a good idea; should I just use alembic to create all databases?

I have alembic working great for the single database I use to develop with in my project's directory. I want to use alembic to conveniently migrate and create databases in arbitrary locations, preferably through some sort of Python API, and not the command line. This application is also frozen with cx_Freeze, in case that makes a difference.

Thanks!

Answer 1

Here's what I've learned after hooking up my software to alembic:

Is there a way to call alembic from inside my Python code?

Yes. As of this writing the main entry point for alembic is alembic.config.main, so you can import it and call it yourself, for example:

import alembic.config
alembicArgs = [
    '--raiseerr',
    'upgrade', 'head',
]
alembic.config.main(argv=alembicArgs)

Note that alembic looks for migrations in the current directory (i.e., os.getcwd()). I've handled this by using os.chdir(migration_directory) before calling alembic, but there may be a better solution.

---

Can I specify a new database location from the command line without editing the .ini file?

Yes. The key lies in the -x command line argument. From alembic -h (surprisingly, I wasn't able to find a command line argument reference in the docs):

optional arguments:
 -x X                  Additional arguments consumed by custom env.py
                       scripts, e.g. -x setting1=somesetting -x
                       setting2=somesetting

So you can create your own parameter, e.g. dbPath, and then intercept it in env.py:

alembic -x dbPath=/path/to/sqlite.db upgrade head

then for example in env.py:

def run_migrations_online():   
    # get the alembic section of the config file
    ini_section = config.get_section(config.config_ini_section)

    # if a database path was provided, override the one in alembic.ini
    db_path = context.get_x_argument(as_dictionary=True).get('dbPath')
    if db_path:
        ini_section['sqlalchemy.url'] = db_path

    # establish a connectable object as normal
    connectable = engine_from_config(
        ini_section,
        prefix='sqlalchemy.',
        poolclass=pool.NullPool)

    # etc

Of course, you can supply the -x parameter using argv in alembic.config.main, too.

I agree with @davidism about using migrations vs metadata.create_all() :)