Query Validation in readonly mode possible?

[djmarcin]

I'm trying to resolve an issue where my build system is generating the schema for query validation as a readonly sqlite file. Is it possible for query validation to operate on sqlite in readonly mode?

If not, perhaps an option to copy the database schema to an in-memory database for validation would be a possibility?

[abonander]

If you add ?mode=ro to your DATABASE_URL it should open the database in read-only mode. We can perhaps enable this by default for SQLite in the macros. @mehcode?

[djmarcin]

Hmm, that's weird. It's definitely not working for me with ?mode=ro, so it must be something subtle.

error: error returned from database: attempt to write a readonly database
  --> src/role.rs:42:18
   |
42 |           let id = sqlx::query!(
   |  __________________^
43 | |             "INSERT INTO roles (name, user_id, team_id) VALUES (?1, ?2, ?3)",
44 | |             self.name,
45 | |             self.user_id,
46 | |             self.team_id
47 | |         )
   | |_________^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: error returned from database: attempt to write a readonly database
  --> src/user.rs:42:9
   |
42 | /         sqlx::query_as!(
43 | |             User,
44 | |             "SELECT id as \"id!\", uid, name FROM users WHERE uid = ?1 LIMIT 1",
45 | |             uid,
46 | |         )
   | |_________^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

The directory itself is also not writable, so perhaps this is a misleading error and what's actually failing is some kind of locking or the WAL. For example, even accessing it with mode=ro from the command line fails, but setting immutable=true works:

$ echo "EXPLAIN SELECT * FROM sessions;" | sqlite3 "file:bazel-out/k8-fastbuild/bin/db/build_script.out_dir/query_checker.db?mode=ro"
Error: near line 1: attempt to write a readonly database
$ echo "EXPLAIN SELECT * FROM sessions;" | sqlite3 "file:bazel-out/k8-fastbuild/bin/db/build_script.out_dir/query_checker.db?immutable=true"
<explain output omitted>

The documentation says that this flag is for when the database file exists on read-only media. Still trying to dig into the code and see if supporting that flag would help here, but wanted to give an update.

[djmarcin]

I put a patchset that seems to solve my problem in master...djmarcin:immutable

It appears to me that the "immutable" parameter only works when sent on the connection URI and there's no flag to enable it. I don't know how switching back to the URI affects any of the other connection options, so I'm not sure if this is mergeable or not. Let me know if you'd like me to send it as a PR, though.

[abonander]

@djmarcin if you want to add it as a flag to SqliteOptions and add support for parsing it, we'll gladly welcome a PR.

[mehcode]

I can confirm that yes, immutable=true is impossible to pass to sqlite3_open and you must use the URI connection format. That feels crazy.

I agree with @abonander. We would accept a PR for that patch, yes.