pacenotes_server/
db.rs

1//! The libSQL auth database: credentials and their grants, never packages.
2//! Queries stay plain SQL so that moving to remote Turso is only a `--db-url` change.
3
4/// Idempotent (`IF NOT EXISTS`), so it runs on every start.
5const MIGRATION_0001: &str = include_str!("../migrations/0001_init.sql");
6
7/// Opens `db_url` and applies the migrations.
8///
9/// - `file:./data/auth.db` or a bare path: a local file, parent folders created
10/// - `:memory:`: ephemeral
11/// - `libsql://…`, `http://…`, `https://…`: remote Turso, authenticated by
12///   `PACENOTES_DB_AUTH_TOKEN`, an environment variable so it stays out of the process list
13pub async fn open(db_url: &str) -> anyhow::Result<libsql::Connection> {
14    let is_remote = ["libsql://", "http://", "https://"]
15        .iter()
16        .any(|scheme| db_url.starts_with(scheme));
17    let database = if is_remote {
18        let token = std::env::var("PACENOTES_DB_AUTH_TOKEN").unwrap_or_default();
19        libsql::Builder::new_remote(db_url.to_string(), token)
20            .build()
21            .await
22            .map_err(|e| anyhow::anyhow!("connecting to {db_url}: {e}"))?
23    } else {
24        let path = local_path(db_url);
25        if path != std::path::Path::new(":memory:") {
26            if let Some(parent) = path
27                .parent()
28                .filter(|parent| !parent.as_os_str().is_empty())
29            {
30                std::fs::create_dir_all(parent)
31                    .map_err(|e| anyhow::anyhow!("creating {}: {e}", parent.display()))?;
32            }
33        }
34        libsql::Builder::new_local(&path)
35            .build()
36            .await
37            .map_err(|e| anyhow::anyhow!("opening {}: {e}", path.display()))?
38    };
39
40    let connection = database
41        .connect()
42        .map_err(|e| anyhow::anyhow!("connecting to {db_url}: {e}"))?;
43    if !is_remote {
44        tune_local(
45            &connection,
46            local_path(db_url) == std::path::Path::new(":memory:"),
47        )
48        .await?;
49    }
50    connection
51        .execute_batch(MIGRATION_0001)
52        .await
53        .map_err(|e| anyhow::anyhow!("applying migrations/0001_init.sql: {e}"))?;
54    Ok(connection)
55}
56
57/// Waits up to 5 s for a lock and, outside `:memory:`, switches to WAL, since
58/// an admin `token mint` beside live traffic otherwise fails SQLITE_BUSY as a 500.
59/// Remote libSQL manages its own storage and is never tuned.
60async fn tune_local(connection: &libsql::Connection, in_memory: bool) -> anyhow::Result<()> {
61    // Both PRAGMAs answer with a row, so they go through `query`.
62    let pragma = |sql: &'static str| async move {
63        let mut rows = connection
64            .query(sql, ())
65            .await
66            .map_err(|e| anyhow::anyhow!("{sql}: {e}"))?;
67        while rows
68            .next()
69            .await
70            .map_err(|e| anyhow::anyhow!("{sql}: {e}"))?
71            .is_some()
72        {}
73        anyhow::Ok(())
74    };
75    pragma("PRAGMA busy_timeout = 5000").await?;
76    if !in_memory {
77        pragma("PRAGMA journal_mode = WAL").await?;
78    }
79    Ok(())
80}
81
82/// `file:./data/auth.db` → `./data/auth.db`; a query string is left in place, not stripped.
83fn local_path(db_url: &str) -> std::path::PathBuf {
84    std::path::PathBuf::from(db_url.strip_prefix("file:").unwrap_or(db_url))
85}
86
87#[cfg(test)]
88mod tests {
89    #[test]
90    fn file_urls_become_paths() {
91        for (url, path) in [
92            ("file:./data/auth.db", "./data/auth.db"),
93            ("/var/lib/pacenotes/auth.db", "/var/lib/pacenotes/auth.db"),
94            (":memory:", ":memory:"),
95        ] {
96            assert_eq!(super::local_path(url), std::path::Path::new(path));
97        }
98    }
99
100    /// Reads a single-value PRAGMA back.
101    async fn pragma(connection: &libsql::Connection, sql: &str) -> libsql::Value {
102        let mut rows = connection.query(sql, ()).await.unwrap();
103        let row = rows.next().await.unwrap().unwrap();
104        row.get_value(0).unwrap()
105    }
106
107    #[tokio::test]
108    async fn a_local_file_db_uses_wal_and_waits_for_locks() {
109        let dir = tempfile::tempdir().unwrap();
110        let path = dir.path().join("auth.db");
111        let connection = super::open(&format!("file:{}", path.display()))
112            .await
113            .unwrap();
114        assert_eq!(
115            pragma(&connection, "PRAGMA journal_mode").await,
116            libsql::Value::Text("wal".into())
117        );
118        assert_eq!(
119            pragma(&connection, "PRAGMA busy_timeout").await,
120            libsql::Value::Integer(5000)
121        );
122    }
123
124    #[tokio::test]
125    async fn migrations_are_idempotent() {
126        let connection = super::open(":memory:").await.unwrap();
127        connection
128            .execute_batch(super::MIGRATION_0001)
129            .await
130            .unwrap();
131        connection
132            .execute_batch(
133                "INSERT INTO tokens (token_hash, created_at) VALUES ('h', 1);
134                 INSERT INTO grants (token_hash, directory) VALUES ('h', 'beta');",
135            )
136            .await
137            .unwrap();
138    }
139}
pacenotes_server/db.rs

pacenotes_server/
db.rs
