pub async fn open_and_migrate(url: &str) -> Result<SqlitePool, DbError>Expand description
Open the SQLite pool from a sqlx connection URL and apply any
pending migrations. Uses WAL journaling so reads do not block writes.
Migrations are run against a separate, single-connection pool with
PRAGMA foreign_keys = OFF. This is the recipe SQLite documents for
the ALTER TABLE pattern that rebuilds a parent table referenced by
other tables: defer_foreign_keys is not sufficient on its own
because the per-row deferred-check queue retains entries from the
moment a parent row “disappeared” (between the DROP and the RENAME)
and rejects them at COMMIT even when the new schema would have
accepted them. Since PRAGMA foreign_keys is a no-op inside a
transaction, the pragma has to be set on a connection that is not
already in a transaction — easiest to achieve by using a dedicated
pool just for migrations.
After the migrations run, we run PRAGMA foreign_key_check against
the same connection to verify the resulting schema does not contain
any orphaned FK references. The application’s runtime pool is then
opened with FK enforcement back on so future inserts/updates are
checked normally.
§Errors
Returns a DbError if the URL is malformed, the file cannot be
opened, migrations fail, or the post-migration FK check finds
orphaned references.