I am upgrading an application from Django 3.2 to 4.2, which uses postgis in production, and django.contrib.gis.db.backends.spatialite when running unit tests.

Running the tests on this application, I’m getting this error:

django.db.utils.OperationalError: error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid
make: *** [django.mk:49: test] Error 1
This problem is documented in this ticket, and seems to be an issue with using Django 4.2 and Spatialite 5: #32935 (Test suite fails with sqlite 3.36 and spatialite 5.) – Django
One of the workarounds looks like executing this command in the spatialite database:
./manage.py shell -c "import django;django.db.connection.cursor().execute('SELECT InitSpatialMetaData(1);')";
However, after I run this command, I am still getting the no such column: rowid error. Has anyone else run into this? I am using Django 4.2.11, sqlite 3.42.0, libspatialite 5.0.1, python 3.12.2 on Fedora Linux.
              
I think the easiest option available is in ⬆ django 4.2 by nikolas · Pull Request #2976 · ccnmtl/footprints · GitHub .github/workflows/build-and-test.yml, do not upgrade from ubuntu-20.04 to ubuntu-22.04.

Ubuntu 20.04 is using spatialite 4.3 when installing libsqlite3-mod-spatialite.
More fiddly options include:
in the action, upgrade to spatialite 5.1
in the action, recompile SQLite with -DSQLITE_ALLOW_ROWID_IN_VIEW
When Ubuntu 24.04 is released April 25th, libsqlite3-mod-spatialite will install spatialite 5.1 libsqlite3-mod-spatialite : Noble (24.04) : Ubuntu (launchpad.net)
          
        'django.contrib.messages.middleware.MessageMiddleware',
          
        'django.contrib.sessions.middleware.SessionMiddleware',
          
    SECRET_KEY="not-secret",
          
    # django.VERSION >= (3, 2)
          
    DEFAULT_AUTO_FIELD="django.db.models.BigAutoField",
          
django.setup()
          
# Workaround for incompatibility between SQLite 3.36+ and SpatiaLite 5,
          
# as used on GitHub Actions. This change monkey patches
          
# prepare_database() to avoid a call to InitSpatialMetaDataFull(). See:
          
# https://code.djangoproject.com/ticket/32935
          
# https://groups.google.com/g/spatialite-users/c/SnNZt4AGm_o
          
from django.contrib.gis.db.backends.spatialite.base import DatabaseWrapper
          
def prepare_database(self):
          
    super(DatabaseWrapper, self).prepare_database()
          
    with self.cursor() as cursor:
          
        cursor.execute("PRAGMA table_info(geometry_columns);")
              
Thank you for these options - Our production systems are running Spatialite 5.0.1 so I would prefer a solution that works with that. If I can’t get that working I suppose I will figure out how to upgrade everything to Spatialite 5.1.
It would be much simpler to just accept the Django patch I submitted, though (Fixed #32935 -- Call InitSpatialMetaData(1) on spatialite 5.0.1. by nikolas · Pull Request #18083 · django/django · GitHub). I think I will most likely just create a custom database wrapper based on that patch.