Using Alembic API from inside application code

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() :)

If you look at the commands API page from the alembic docs you see an example of how to run the CLI commands directly from a Python application. Without going through the CLI code.

Running alembic.config.main has the downside that the env.py script is executed which may not be what you want. For example, it will modify your logging config.

Another, very simple way is to use the "command API" linked above. For example, here is a small helper function which I ended up writing:

from alembic.config import Config
from alembic import command

def run_migrations(script_location: str, dsn: str) -> None:
    LOG.info('Running DB migrations in %r on %r', script_location, dsn)
    alembic_cfg = Config()
    alembic_cfg.set_main_option('script_location', script_location)
    alembic_cfg.set_main_option('sqlalchemy.url', dsn)
    command.upgrade(alembic_cfg, 'head')

I am using the set_main_option method here to be able to run the migrations on a different DB if needed. So I can simply call this as follows:

run_migrations('/path/to/migrations', 'postgresql:///my_database')

Where you get those two values (path and DSN) from is up to you. But this seems to be very close to what you want to achieve. The commands API also has the stamp() methods which allows you mark a given DB to be of a specific version. The example above can be easily adapted to call this.

This is a very broad question, and actually implementing your idea will be up to you, but it is possible.

You can call Alembic from your Python code without using the commands, since it's implemented in Python too! You just need to recreate what the commands are doing behind the scenes.

Admittedly, the docs aren't in very good shape since these are still relatively early releases of the library, but with a little digging you will find the following:

1. Create a Config
2. Use the Config to create a ScriptDirectory
3. Use the Config and the ScriptDirectory to create an EnvironmentContext
4. Use the EnvironmentContext to create a MigrationContext
5. Most commands use some combination of methods from Config and MigrationContext

I've written an extension to provide this programmatic Alembic access to a Flask-SQLAlchemy database. The implementation is tied to Flask and Flask-SQLAlchemy, but it should be a good place to start. See Flask-Alembic here.

Regarding your last point about how to create new databases, you can either use Alembic to create the tables, or you can use metadata.create_all() then alembic stamp head (or equivalent python code). I recommend always using the migration path to create the tables, and ignoring the raw metadata.create_all().

I don't have any experience with cx_freeze, but it should be fine as long as the migrations are included in the distribution and the path to that directory in the code is correct.