alembic with sqlite memory autogenerate does not work

[jmgurney]

I'm using alembic to manage revisions of an SQLite3 file format. As such, there isn't a "database" that is a reference database that I keep around for the project. I am using SQLAlchemy's ORM, so I've declared the full format.

I use:

sqlalchemy.url = sqlite+pysqlite:///:memory:

and that worked for the first time to autogenerate the initial db w/o issues. The problem is that when I update the ORM, and then try to autogenerate a new revision, I get:

$ alembic revision --autogenerate -m 'another'
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [alembic.util.messaging] Target database is not up to date.
  FAILED: Target database is not up to date.

and this is because the database in memory wasn't updated/created as it was new for the revision command while it's clear the alembic expects a database to already be in some sort of state that I'm not sure what it should be.

I was able to get a revision working not using autogenerate, but I'd like to simply be able to update the ORM, run --autogenerate to add the new columns/tables (so I don't have to figure out that magic syntax and copy names around every time), and then only need to add the necessary functions to update as needed.

Is this possible?

Thanks.

[CaselIT]

Hi,

You replied to your question there. Using memory every time the database is up to date so there is nothing to do from alembic autogenerate point of view.
You should use a sqlite file instead so that alembic can compare the schema on the file with the one defined by sqlalchemy and detect the changes

[CaselIT]

But why can't alembic generate the DB as needed from that past/existing db conversions, and then use those?

it's not a given that the migration start from an empty db. Using sqlite memory is a very particular use case for alembic, since by definition there is no "previous state" to compare the current model to

using an external file that is unsure of what the state of the schema is

You are describing basically how all database work with alembic.

Also I've just noticed that this is in the sqlalchemy repository. will move to alembic

[jmgurney]

To address the no "previous state", the revision command could be given the state to start from.

I tried to call command.upgrade like I did in my other code from env.py:run_migrations_online after creating the engine, but apparently the tear down code that's part of upgrade destroys enough of the environment that the revision command cannot continue. Even with a guard around creating a second engine, or using a named in memory sqlite db.

I did script around it, but not a fan of creating temporary files when not needed.

[CaselIT]

To address the no "previous state", the revision command could be given the state to start from.

That would not work, since autgenerate would detect everything that's not covered by the missing migrations.
Also it's very likely that migrations would not run on an empty database, since all modification of existing objects would fail

but not a fan of creating temporary files when not needed.

I don't understand what you mean here.
Usually an application that uses alembic has a persistent database that's the final destination of the migrations, and while developing it uses a similar database with demo or dummy data but that has the same schema as the real one.
The autogenerate workflow assumes that when a schema change is done in the code an existing not updated database is available and is used to compare against the schema described in the code. Usually the database used is the development one.

If your application has no persistent database or the workflow you want to implement is not similar to the above, then autogenerate is probably not the right fix in your case.

[jmgurney]

To address the no "previous state", the revision command could be given the state to start from.

That would not work, since autgenerate would detect everything that's not covered by the missing migrations. Also it's very likely that migrations would not run on an empty database, since all modification of existing objects would fail

Well, w/ autogenerate, you can run it on an empty database, and it creates a migration from nothing to whatever your ORM is currently. I know, that's how I got my first migration created (and kinda assumed this is how people started out, but clearly that isn't the case), see: https://www.funkthat.com/gitea/jmg/medashare/src/branch/main/ui/medashare/alembic/versions/afad01589b76_initial_db_schema.py for an example. This was generated when I used :memory: as the db url, not realizing that things wouldn't work the next time.

And w/ an autogen from empty database in place, you can just init a DB from nothing to the current head, which I implemented in:
https://www.funkthat.com/gitea/jmg/medashare/src/commit/c2ac1018e586174b5353531aa4cb917b2d5b7306/ui/medashare/cli.py#L301-L309 (I need to change this code to be explicit in the update instead of automatic like this is.)

This means that when you pass a new sqlite db in, it'll get upgraded to what the code currently expects.

but not a fan of creating temporary files when not needed.

I don't understand what you mean here. Usually an application that uses alembic has a persistent database that's the final destination of the migrations, and while developing it uses a similar database with demo or dummy data but that has the same schema as the real one. The autogenerate workflow assumes that when a schema change is done in the code an existing not updated database is available and is used to compare against the schema described in the code. Usually the database used is the development one.

Yeah, I understand that and why I stated this at the very beginning of my question:

As such, there isn't a "database" that is a reference database that I keep around for the project.

If your application has no persistent database or the workflow you want to implement is not similar to the above, then autogenerate is probably not the right fix in your case.

autogenerate is perfectly fine for what I want, as you can see w/ this script (https://www.funkthat.com/gitea/jmg/medashare/src/commit/c2ac1018e586174b5353531aa4cb917b2d5b7306/ui/medashare/autogen.sh)

# Make sure we start clean
rm -f autogen.db.sqlite3

# Upgrade the database to head
alembic upgrade head

# Autogenerate the migrations
alembic revision --autogenerate -m "$*"

# Clean up
rm -f autogen.db.sqlite3

I tried to use :memory: instead of autogen.db.sqlite3, but that's when I ran into the issue stated previously.

[peterjc]

The documentation edit proposed on #1570 might help others.

I also started with using sqlalchemy.url = sqlite///:memory: and then switched to trying a freshly generated new empty database, before realizing what was expected was a database using the previous schema.

Like @jmgurney I was expecting sqlalchemy.url = sqlite///:memory: to just work (with alembic populating the new empty in-memory database with all the previous update scripts).