diff options
| -rw-r--r-- | content/code/sqlx-sqlite-automatic-creation.md | 52 |
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. |