Alembic migrate with existing SQLAlchemy engine

Question

I have a certain SQLAlchemy declarative Base that I create on a sqlite memory DB:

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

I'm using this for unit testing logic.

With this I have my tables in the DB. But now I wish to migrate certain things using alembic.

AFAIK alembic migrations use the env.py run_migrations_online and there uses a SQLAlchemy function called engine_from_config creating a new engine here.

The problem I wish to solve is to have a way to use the previously created connection, which holds the recently created tables, for the alembic migrations.

I used this on my test scripts: Using Alembic API from inside application code, so that my script does the following after the previous create_all call:

import alembic.config
alembicArgs = ['--raiseerr', '-x', 'dbPath=sqlite:///:memory:', 'upgrade', 'head']
alembic.config.main(argv=alembicArgs

[Please mind, I would just create my schemas with the Base.metadata.create_all(engine) call but my alembic versions not only hold schema changes, they also have some filling of catalog tables data, that's why I intend to use alembic here. In fact, if my alembic migrations hold some "create tables" logic, these two would conflict. So I can safely remove the create_all call and depend on alembic alone to create my schemas here.]

Having already modified my alembic's env.py:

def run_migrations_online():
    ini_section = config.get_section(config.config_ini_section)

    db_path = context.get_x_argument(as_dictionary=True).get('dbPath')

    if db_path:
        ini_section['sqlalchemy.url'] = db_path

    connectable = engine_from_config(
        ini_section,
        prefix ... # everything from here the same as default env.py

As far as I can tell the connectable=engine_from_config creates a connection to a new engine on a new sqlite:///:memory: database, and that's why I can't upgrade via alembic the previously created DB on my script with create_all(engine).

So... TLDR; is there a way to pass my previously existing engine connection (with my created tables) to alembic so that it can migrate it? (I'm pretty sure that the dbPath arg I created is useless here, in fact, I'm just copying what the other post I referenced uses).

what `run_migrations_online` need inside is a `connection` object, you could create one from existing engine: `connection = db.engine.connect()`, it works for me. — georgexsh, Jan 22 '18 at 21:15
Yes but how can I pass that existing connection when invoking alembic programmatically? — Javier Novoa C., Jan 23 '18 at 02:47
could you like show more pieces of your code? like how you init engine and invoke alembic. — georgexsh, Jan 23 '18 at 03:49
So this db lives as long as the app lives right? Do you intend to run migrations live? How does that work? How are you "hot-updating" your models? — AArias, Jan 23 '18 at 11:49
@georgexsh I edited to clarify where do I want to use all this: on unit test scripts. So the first piece of code is actually where I init my engine and the second is where I invoke alembic. The piece of code on env.py is just the alembic env configuration script — Javier Novoa C., Jan 23 '18 at 21:53
@AArias yes, well, I edited to tell more about my use case: I want to use this on unit test scripts. I'd like to have my schemas created and filled (via alembic versions which I use not only to build my schemas, I also fill some data on catalog tables, etc.) before testing anything. — Javier Novoa C., Jan 23 '18 at 21:55

Hetzroni · Accepted Answer · 2018-01-29T22:22:38.560

4

You can create an alembic config instance and do operations on it:

def migrate_in_memory(migrations_path, alembic_ini_path=None, connection=None, revision="head"):
    config = alembic.config.Config(alembic_ini_path)
    config.set_main_option('script_location', migrations_path)
    config.set_main_option('sqlalchemy.url', 'sqlite:///:memory:')
    if connection is not None:
        config.attributes['connection'] = connection
    alembic.command.upgrade(config, revision)

It might take some fine tuning, but that's the general gist of things.

edited Jan 29 '18 at 22:22

answered Jan 29 '18 at 12:35

Hetzroni

2,109
1
14
29

Have to test this, but how does this solution ensures I'm using the same connection engine as the one on my main script? I mean, declaring a memory database to be used has the problem that, if you declare two independent connections to it, then you'll have two independent memory databases, so you can't work on the same one and then the upgrade won't happen on the same one I previously declared – Javier Novoa C. Jan 29 '18 at 12:40
1

In that case I assume you have a `connection` object? If so, try calling: `config.attributes['connection'] = connection` before calling `upgrade`. – Hetzroni Jan 29 '18 at 12:54
Yes I have it, the post says that. Let me try it and I'll come back to you – Javier Novoa C. Jan 29 '18 at 13:20
mmm seems to work, but can't say for sure, alembic throws the following error while doing alembic.command.upgrade(config, "head") : unknown encoding: utf8mb4 – Javier Novoa C. Jan 29 '18 at 18:12
As far as I know sqlite doesn't support utf8mb4. This is used for storing code points greater than 65535. This probably has something to do with one of your migrations. Does this still happen if you use a different in-memory database? – Hetzroni Jan 29 '18 at 22:07
sorry, fixed it. your solution has a typo: you set the sqlite.url option which doesn't makes sense, must be the sqlalchemy.url option. – Javier Novoa C. Jan 29 '18 at 22:12
It fixed the utf8mb4 problem, because it was still trying to use a MySQL connection I had, but still it seems to create the tables and do migrations on some DB but when using the same connection on my script, seems like no tables nor migrations were created – Javier Novoa C. Jan 29 '18 at 22:25
However it seems as a promising solution, after reading more on alembic docs and learning about the Config object and attributes. something may be missing here – Javier Novoa C. Jan 29 '18 at 22:27
Changing the memory SQLite dB with a file DB works fine, as expected, but using a memory DB is what keeps failing, as if the connections were referring to different memory DBS... – Javier Novoa C. Jan 29 '18 at 22:29
further reading alembic's API documentation, it seems that the attributes on Config object receive a connection, which lives while alembic upgrades and does its things. However then it ends, and further on my script I'm using a sessionmaker session to query the DB, maybe that's where the issue lives? – Javier Novoa C. Jan 29 '18 at 22:56
2

ah!! here is it what I needed to get it working. THANK YOU SO MUCH! http://alembic.zzzcomputing.com/en/latest/cookbook.html#connection-sharing – Javier Novoa C. Jan 30 '18 at 00:37

Alembic migrate with existing SQLAlchemy engine

1 Answers1