steid

e51b80cfeat: sqlite migrations for the identity schema1mo	1	use std::str::FromStr;
	2
0d12f7dfeat: config from env and SQLite pool in app context1mo	3	use sqlx::SqlitePool;
e51b80cfeat: sqlite migrations for the identity schema1mo	4	use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
0d12f7dfeat: config from env and SQLite pool in app context1mo	5
e51b80cfeat: sqlite migrations for the identity schema1mo	6	/// Opens the SQLite pool and brings the schema up to date.
0d12f7dfeat: config from env and SQLite pool in app context1mo	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.
	10	pub async fn connect(database_url: &str) -> Result<SqlitePool, sqlx::Error> {
e51b80cfeat: sqlite migrations for the identity schema1mo	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.
	27	pub 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)]
	35	pub(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)]
	60	mod 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	}
0d12f7dfeat: config from env and SQLite pool in app context1mo	108	}