Wednesday, April 27, 2022

ActiveRecord, SQLite, URI Database name tokens

Blogging an answer I posted at Stack Overflow:

I ran into an issue recently with a library I was testing - the in-memory SQLite database wasn't shared between threads in the testing process. This can be accommodated in SQLite with a shared cache, and this should be usable in ActiveRecord by configuring the database connection with a URI filename... but it wasn't working.

If your SQLite build did not set the SQLITE_USE_URI flag to true, then after SQLite v3.38.2 it will default to false. As the folks at S/O observe, you will see files created with the name of the URI token.

You can work around this by passing the appropriate bit switches to SQLite via the flags parameter on your ActiveRecord connection.

In particular, you will want:

  • SQLite3::Constants::Open::READWRITE (0x02)
  • SQLite3::Constants::Open::CREATE (0x04)
  • SQLite3::Constants::Open::URI (0x40)
... which is to say:

ActiveRecord::Base.establish_connection {

adapter: "sqlite3",

database: "file::memory:?cache=shared",

flags: 70 # SQLite3::Constants::Open::READWRITE | CREATE | URI