Alembic with SQLite FOREIGN_KEYS pragma

[Lele Gaifax]

Hi all,

this is loosely related to my other post: modern SA/Alembic transaction
handling changed (unsurprisingly! :-) somewhat, and old migration trick
does not work anymore. I found a way out, but maybe there is a better
approach, who knows!

My app, being SQLite based, installs an event handler to force SQLite
FKs check[0]:

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()

As explained in the related Alembic doc[1], I used to disable that check
in some migrations (for example this[2]), something that surely worked
when I applied them in production, nearly two years ago.

Since my last migration something has changed though, and that trick
doesn't work anymore, apparently because for some reason nowadays
Alembic issues a BEGIN well before running the migration script and,
accordingly to SQLite doc[3], that instruction does not work within a
transaction: a new migration like the following, that change an existing
column to be NOT NULL

def upgrade():
op.execute("PRAGMA foreign_keys=OFF")
op.execute("UPDATE clubs SET nationality='wrl' WHERE nationality IS NULL")
with op.batch_alter_table('clubs') as batch:
batch.alter_column('nationality',
existing_type=sa.CHAR(length=3),
nullable=False)

now fails, even running it on a "production equivalent context" that is
still based on SA 1.3 and Alembic 1.7.6: as soon as it "drops" the old table
and is going to rename the new temporary table built by
"batch_alter_table()" the FK check interrupts the process with a
"sqlite3.IntegrityError: FOREIGN KEY constraint failed" error.

I found a possible, less-than-ideal workaround moving that PRAGMA
instruction into the Alembic new, modernized env.py preamble I'm trying
out while upgrading to SA 2:

def run_migrations_online():
"""Run migrations in 'online' mode.

In this scenario we need to create an Engine
and associate a connection with the context.
"""

connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)

with connectable.connect() as connection:
# Disable FK validation, turned on by models/__init__.py::set_sqlite_pragma()
connection.exec_driver_sql("PRAGMA foreign_keys=OFF")

context.configure(
connection=connection, target_metadata=target_metadata
)

with context.begin_transaction():
context.run_migrations()

As this unconditionally affects all migrations, it obviously may ruins
further ones, where the FK check would be desiderable.

Can you suggest some better approach?

Thanks again,
bye, lele.

[0] https://gitlab.com/metapensiero/SoL/-/blob/master/src/sol/models/__init__.py#L215
[1] https://alembic.sqlalchemy.org/en/latest/batch.html#dealing-with-referencing-foreign-keys
[2] https://gitlab.com/metapensiero/SoL/-/blob/master/alembic/versions/d311277a4da7_widen_urls_size_to_128_characters.py#L17
[3] https://www.sqlite.org/pragma.html#pragma_foreign_keys
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Mike Bayer]

On Sun, Jan 15, 2023, at 6:51 AM, Lele Gaifax wrote:

Hi all,

this is loosely related to my other post: modern SA/Alembic transaction

handling changed (unsurprisingly! :-) somewhat, and old migration trick

does not work anymore. I found a way out, but maybe there is a better

approach, who knows!

My app, being SQLite based, installs an event handler to force SQLite

FKs check[0]:

  @event.listens_for(Engine, "connect")

  def set_sqlite_pragma(dbapi_connection, connection_record):

      cursor = dbapi_connection.cursor()

      cursor.execute("PRAGMA foreign_keys=ON")

      cursor.close()

As explained in the related Alembic doc[1], I used to disable that check

in some migrations (for example this[2]), something that surely worked

when I applied them in production, nearly two years ago.

Since my last migration something has changed though, and that trick

doesn't work anymore, apparently because for some reason nowadays

Alembic issues a BEGIN well before running the migration script and,

accordingly to SQLite doc[3], that instruction does not work within a

transaction: a new migration like the following, that change an existing

column to be NOT NULL

OK, alembic nor sqlalchemy do not emit BEGIN nor they even know how to do this.  what will make a sqlite-level BEGIN occur is if some kind of SQL commands are running on the connection, like an INSERT or something.    This goes with your other email that I dont see what this mystery command is, ahead of the migration running.   

you might want to try calling op.get_bind().commit() maybe, or try this feature called autocommit_block: https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.migration.MigrationContext.autocommit_block

however, overall, I dont know what's going on here, every migration script should be starting with a clean transaction for sqlite assuming transaction_per_migration is ON.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87cz7gdnip.fsf%40metapensiero.it.