Kushal Das: Alembic migration errors on SQLite

We use SQLite3 as the database in SecureDrop. We use SQLAlchemy to talk the database and Alembic for migrations. Some of those migrations are written by hand.

Most of my work time in the last month went to getting things ready for Ubuntu Focal 20.04. We currently use Ubuntu Xenial 16.04. During this, I noticed 17 test failures related to the Alembic on Focal but works fine on Xenial. After digging a bit more, these are due to the missing reference to temporary tables we used during migrations. With some more digging, I found this entry on the SQLite website:

Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table. This is considered an improvement. Applications that depend on the older (and arguably buggy) behavior can use the PRAGMA legacy_alter_table=ON statement or the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE configuration parameter on sqlite3_db_config() interface to make ALTER TABLE RENAME behave as it did prior to version 3.25.0.

This is what causing the test failures as SQLite upgraded to 3.31.1 on Focal from 3.11.0 on Xenial.

According to the docs, we can fix the error by adding the following in the env.py.

diff --git a/securedrop/alembic/env.py b/securedrop/alembic/env.py
index c16d34a5a..d6bce65b5 100644
--- a/securedrop/alembic/env.py
+++ b/securedrop/alembic/env.py
@@ -5,6 +5,8 @@ import sys
 
 from alembic import context
 from sqlalchemy import engine_from_config, pool
+from sqlalchemy.engine import Engine
+from sqlalchemy import event
 from logging.config import fileConfig
 from os import path
 
@@ -16,6 +18,12 @@ fileConfig(config.config_file_name)
 sys.path.insert(0, path.realpath(path.join(path.dirname(__file__), '..')))
 from db import db  # noqa
 
+@event.listens_for(Engine, "connect")
+def set_sqlite_pragma(dbapi_connection, connection_record):
+    cursor = dbapi_connection.cursor()
+    cursor.execute("PRAGMA legacy_alter_table=ON")
+    cursor.close()
+
 try:
     # These imports are only needed for offline generation of automigrations.
     # Importing them in a prod-like environment breaks things.

Later, John found an even simpler way to do the same for only the migrations impacted.

from Planet Python
via read more