sl_map_web/
db.rs

1//! SQLite pool setup and migration runner.
2
3use sqlx::SqlitePool;
4use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
5use std::str::FromStr as _;
6
7/// Embedded migrations. Resolved at compile time so the binary stays
8/// self-contained and no separate migration command is needed.
9pub static MIGRATIONS: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");
10
11/// Errors that can occur while opening the database.
12#[derive(Debug, thiserror::Error)]
13#[expect(
14    clippy::module_name_repetitions,
15    reason = "`DbError` is the conventional name and `Error` would clash with the crate's other top-level error type"
16)]
17pub enum DbError {
18    /// the configured `database_url` could not be parsed as
19    /// [`SqliteConnectOptions`].
20    #[error("invalid database URL: {0}")]
21    BadUrl(#[source] sqlx::Error),
22    /// the pool could not be opened (file permissions, locked, etc.).
23    #[error("failed to open SQLite pool: {0}")]
24    Open(#[source] sqlx::Error),
25    /// applying the embedded migrations failed.
26    #[error("failed to apply migrations: {0}")]
27    Migrate(#[source] sqlx::migrate::MigrateError),
28}
29
30/// Open the SQLite pool from a `sqlx` connection URL and apply any
31/// pending migrations. Uses WAL journaling so reads do not block writes.
32///
33/// Migrations are run against a separate, single-connection pool with
34/// `PRAGMA foreign_keys = OFF`. This is the recipe SQLite documents for
35/// the ALTER TABLE pattern that rebuilds a parent table referenced by
36/// other tables: `defer_foreign_keys` is not sufficient on its own
37/// because the per-row deferred-check queue retains entries from the
38/// moment a parent row "disappeared" (between the DROP and the RENAME)
39/// and rejects them at COMMIT even when the new schema would have
40/// accepted them. Since `PRAGMA foreign_keys` is a no-op inside a
41/// transaction, the pragma has to be set on a connection that is not
42/// already in a transaction — easiest to achieve by using a dedicated
43/// pool just for migrations.
44///
45/// After the migrations run, we run `PRAGMA foreign_key_check` against
46/// the same connection to verify the resulting schema does not contain
47/// any orphaned FK references. The application's runtime pool is then
48/// opened with FK enforcement back on so future inserts/updates are
49/// checked normally.
50///
51/// # Errors
52///
53/// Returns a [`DbError`] if the URL is malformed, the file cannot be
54/// opened, migrations fail, or the post-migration FK check finds
55/// orphaned references.
56pub async fn open_and_migrate(url: &str) -> Result<SqlitePool, DbError> {
57    let migration_options = SqliteConnectOptions::from_str(url)
58        .map_err(DbError::BadUrl)?
59        .foreign_keys(false)
60        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
61        .busy_timeout(std::time::Duration::from_secs(5));
62    let migration_pool = SqlitePoolOptions::new()
63        .max_connections(1)
64        .connect_with(migration_options)
65        .await
66        .map_err(DbError::Open)?;
67    MIGRATIONS
68        .run(&migration_pool)
69        .await
70        .map_err(DbError::Migrate)?;
71    // Surface any orphaned FK references the migrations may have created
72    // — a `foreign_key_check` row means a child row points at a parent
73    // that does not exist, which is exactly the kind of inconsistency
74    // running with `foreign_keys = OFF` would otherwise hide.
75    let violations: Vec<(String, i64, String, i64)> = sqlx::query_as("PRAGMA foreign_key_check")
76        .fetch_all(&migration_pool)
77        .await
78        .map_err(|err| DbError::Migrate(sqlx::migrate::MigrateError::Execute(err)))?;
79    if !violations.is_empty() {
80        tracing::error!(
81            "post-migration foreign_key_check found {} violation(s): {:?}",
82            violations.len(),
83            violations
84        );
85        return Err(DbError::Migrate(sqlx::migrate::MigrateError::Execute(
86            sqlx::Error::Protocol(format!(
87                "post-migration foreign_key_check found {} violation(s)",
88                violations.len()
89            )),
90        )));
91    }
92    migration_pool.close().await;
93
94    let options = SqliteConnectOptions::from_str(url)
95        .map_err(DbError::BadUrl)?
96        .foreign_keys(true)
97        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
98        .busy_timeout(std::time::Duration::from_secs(5));
99    let pool = SqlitePoolOptions::new()
100        .max_connections(8)
101        .connect_with(options)
102        .await
103        .map_err(DbError::Open)?;
104    Ok(pool)
105}
sl_map_web/db.rs

sl_map_web/
db.rs
