Small work report about sqlite in sqlx.

1 files changed, 52 insertions, 0 deletions

diff --git a/content/code/sqlx-sqlite-automatic-creation.md b/content/code/sqlx-sqlite-automatic-creation.md
new file mode 100644
index 0000000..37ec5e8
--- /dev/null
+++ b/content/code/sqlx-sqlite-automatic-creation.md
@@ -0,0 +1,52 @@
+---
+title: Sqlite in Sqlx, Automatic File Creation, and Error 14
+date: 2024-08-30T20:44:13-04:00
+---
+Sometimes the defaults get in your way.
+<!--more-->
+Recently, when working with [Sqlite] in a Rust program, I encountered this frustrating behaviour:
+
+[Sqlite]: https://www.sqlite.org
+
+```
+% cargo run --
+   Compiling hi v0.1.0 (/Users/owen/Projects/grimoire.ca/hi)
+	Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.61s
+	 Running `target/debug/hi`
+Error: Database(SqliteError { code: 14, message: "unable to open database file" })
+```
+
+The program created its connection using the following:
+
+```rust
+async fn pool(&self) -> sqlx::Result<SqlitePool> {
+	let pool = SqlitePoolOptions::new().connect(&self.database_url).await?;
+	Ok(pool)
+}
+```
+
+This code is drawn, with minimal modification, from [sqlx's README](https://github.com/launchbadge/sqlx?tab=readme-ov-file#connecting).
+
+No amount of tinkering with the database URL (here `.hi`, but variously also `sqlite:.hi`, `sqlite://.hi`, and numerous others) changed the output.
+
+The root cause, after about an hour of digging, is [this default]:
+
+[this default]: https://docs.rs/sqlx/latest/sqlx/sqlite/struct.SqliteConnectOptions.html#method.create_if_missing
+
+> By default, a new file **will not be created** if one is not found.
+
+**Emphasis** thus.
+
+I replaced my connection pool factory with the following:
+
+```rust
+async fn pool(&self) -> sqlx::Result<SqlitePool> {
+	let options = SqliteConnectOptions::from_str(&self.database_url)?
+		.create_if_missing(true);
+
+	let pool = SqlitePoolOptions::new().connect_with(options).await?;
+	Ok(pool)
+}
+```
+
+And now it works the way I intended: the database is created automatically on startup if it doesn't exist, or reused if it does.