Sqlite in Sqlx, Automatic File Creation, and Error 14

Sometimes the defaults get in your way.

Recently, when working with Sqlite in a Rust program, I encountered this frustrating behaviour:

% 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:

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.

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:

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:

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.