Setting SQLite3 PRAGMA's automatically with crystal-sqlite3

For a project in Crystal using SQLite3 (crystal-sqlite3), I wanted to automate setting PRAGMAs on the database connection without having to leverage on DB#setup_connection and hardcoding values in my code.

So, instead of doing this:

require "sqlite3"

DB.open "sqlite3:file.db" do |db|
  db.setup_connection do |cnn|
    cnn.exec "PRAGMA busy_timeout=1000"
  end

  # ... your code ...
end

I could do something like this:

DB.open "sqlite3:file.db?_busy_timeout=1000" do |db|
  # ... your code ...
end

Even better, I can move the entire DB connection string to an ENV variable!

DB.open ENV["DATABASE_URL"] do |db|
  # success!
end

▶️ Similar to previous TIL, I made a recording of this initial prototype here.

👨‍💻 Also submitted a PR #85 to crystal-sqlite3 for discussion! 😊

---

1. Write some specs/examples of the desired functionality

I planned to introduce multiple PRAGMAs, so wrote a helper method:

private def it_sets_pragma_on_connection(pragma : String, option : String, value : String, expected, file = __FILE__, line = __LINE__)
  it "sets pragma '#{pragma}' to #{expected} using '#{option}'", file, line do
    with_db("#{DB_FILENAME}?#{option}=#{value}") do |db|
      db.scalar("PRAGMA #{pragma}").should eq(expected)
    end
  end
end

This allow to write the specific PRAGMA specs as one-liner:

it_sets_pragma_on_connection "busy_timeout", "_busy_timeout", "1000", 1000
it_sets_pragma_on_connection "cache_size", "_cache_size", "-4000", -4000
it_sets_pragma_on_connection "foreign_keys", "_foreign_keys", "1", 1

2. Ensure each connection understand those PRAGMAs

When doing DB.connect, a new SQLite3 connection will be instantiated and following SQLite3 rules about PRAGMAs, some are per-connection, meaning that once the connection is created, those PRAGMAs must be set.

We could process the options given in the URI in SQLite3::Connection:

class SQLite3::Connection < DB::Connection
  def initialize(database)
    super
    filename = self.class.filename(database.uri)
    check LibSQLite3.open_v2(filename, out @db, (Flag::READWRITE | Flag::CREATE), nil)
    # ...
  end
  # ...
end

From there we know that the URI is available to us via database.uri, so we can process these PRAGMA options! 😊

private def process_query_params(uri : URI)
  pragmas = Hash(String, String).new

  URI::Params.parse(query) do |key, value|
    case key
    when "_busy_timeout"
      pragmas["busy_timeout"] = value
    when "_cache_size"
      pragmas["cache_size"] = value
    when "_foreign_keys"
      pragmas["foreign_keys"] = value
    when "_journal_mode"
      pragmas["journal_mode"] = value
    when "_synchronous"
      pragmas["synchronous"] = value
    when "_wal_autocheckpoint"
      pragmas["wal_autocheckpoint"] = value
    end
  end
end

Notice there is no sanitization to the input value, as SQLite3 will ignore any invalid value, example:

sqlite> PRAGMA busy_timeout;
0

sqlite> PRAGMA busy_timeout=something;
0

sqlite> PRAGMA busy_timeout;
0

The same for other PRAGMAs that expect boolean (yes, no) or specific options.

Also decided to use a Hash to collect all the set pragmas to avoid doing multiple queries but to honor last parameter set (as override).

Setting _busy_timeout=100&_busy_timeout=5000 will result in only one call to set busy_timeout to 5000.

3. Use SQLite3 C API 😬

Now that we have all the collected pragmas, is time to pass those to SQLite3.

We will use SQLite3's exec, which combines all the stages needed to process SQL statements in a single call. Plus, it allow us to combine multiple SQL in a single call (I'm lazy, OK?).

# src/sqlite3/lib_sqlite3.cr
# ...
  fun prepare_v2 = sqlite3_prepare_v2(db : SQLite3, zSql : UInt8*, nByte : Int32, ppStmt : Statement*, pzTail : UInt8**) : Int32
+ fun exec = sqlite3_exec(db : SQLite3, zSql : UInt8*, pCallback : Callback, pCallbackArgs : Void*, pzErrMsg : UInt8**) : Int32

And we complete the changes in process_query_params, after collecting all PRAGMAs:

# concatenate all into a single SQL string
sql = String.build do |str|
  pragmas.each do |key, value|
    str << "PRAGMA #{key}=#{value};"
  end
end

check LibSQLite3.exec(@db, sql, nil, nil, nil)

4. Test it, and ship it!

With the changes in place, make sure specs are passing (crystal spec) and send that over for review! 🎉