@jpgilldev / steid

steid/src/infrastructure/database.rs
3.5 KBRaw
1use std::str::FromStr;
2
3use sqlx::SqlitePool;
4use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
5
6/// Opens the SQLite pool and brings the schema up to date.
7///
8/// The pool is cheap to clone and shared by reference across every request, so it is
9/// registered once as Topcoat app context rather than rebuilt per handler.
10pub async fn connect(database_url: &str) -> Result<SqlitePool, sqlx::Error> {
11 // SQLite ignores foreign keys unless asked, per connection. Without this the
12 // references in the schema are documentation rather than constraints, and the
13 // ordering our use cases are careful about stops being enforced at all.
14 let options = SqliteConnectOptions::from_str(database_url)?.foreign_keys(true);
15
16 let pool = SqlitePoolOptions::new().connect_with(options).await?;
17
18 migrate(&pool).await?;
19
20 Ok(pool)
21}
22
23/// Applies any migrations the database has not seen.
24///
25/// Embedded at compile time, so the binary carries its own schema and there is no
26/// separate migration step to forget on deploy.
27pub async fn migrate(pool: &SqlitePool) -> Result<(), sqlx::Error> {
28 sqlx::migrate!("./migrations")
29 .run(pool)
30 .await
31 .map_err(|error| sqlx::Error::Migrate(Box::new(error)))
32}
33
34#[cfg(test)]
35pub(crate) mod test_support {
36 use super::*;
37
38 /// A migrated, isolated in-memory database for tests.
39 ///
40 /// `:memory:` is per-connection, so the pool is capped at one connection —
41 /// otherwise each checkout gets its own empty database and writes vanish.
42 pub async fn test_pool() -> SqlitePool {
43 let options = SqliteConnectOptions::from_str("sqlite::memory:")
44 .expect("in-memory url should parse")
45 .foreign_keys(true);
46
47 let pool = SqlitePoolOptions::new()
48 .max_connections(1)
49 .connect_with(options)
50 .await
51 .expect("in-memory database should open");
52
53 migrate(&pool).await.expect("migrations should apply");
54
55 pool
56 }
57}
58
59#[cfg(test)]
60mod tests {
61 use super::*;
62 use sqlx::Row;
63
64 #[tokio::test]
65 async fn migrations_apply_to_a_fresh_database() {
66 let pool = test_support::test_pool().await;
67
68 let tables: Vec<String> =
69 sqlx::query("select name from sqlite_master where type = 'table'")
70 .fetch_all(&pool)
71 .await
72 .expect("query")
73 .into_iter()
74 .map(|row| row.get::<String, _>("name"))
75 .collect();
76
77 for expected in ["orgs", "users", "memberships"] {
78 assert!(
79 tables.iter().any(|name| name == expected),
80 "expected a {expected} table, got {tables:?}"
81 );
82 }
83 }
84
85 #[tokio::test]
86 async fn foreign_keys_are_enforced() {
87 let pool = test_support::test_pool().await;
88
89 // A user pointing at an org that doesn't exist must be refused. If this ever
90 // passes, `foreign_keys(true)` has been lost and the schema's references have
91 // quietly become decorative.
92 let result = sqlx::query(
93 "insert into users (id, email, password_hash, personal_org_id)
94 values ('u1', 'dev@example.com', 'hash', 'org-that-does-not-exist')",
95 )
96 .execute(&pool)
97 .await;
98
99 assert!(result.is_err(), "expected the foreign key to be enforced");
100 }
101
102 #[tokio::test]
103 async fn migrations_are_idempotent() {
104 let pool = test_support::test_pool().await;
105
106 migrate(&pool).await.expect("re-running should be a no-op");
107 }
108}