User-selectable sqlite library

[aplavin]

There are several reasonably popular sqlite library modifications with certain features on top: e.g., sqlcipher with encryption. They are drop-in compatible with the basic sqlite.
It would be useful to let the user choose another library when opening a database in SQLite.jl.

Currently, to use sqlcipher I just forked SQLite.jl, changed the jll library name, and renamed the package to SQLCipher (not planning to register). The only relevant change is: replace using SQLite_jll with using SQLCipher_jll: libsqlcipher as libsqlite.

I imagine something like SQLite.DB("filename", libsqlite=...) should be possible somehow, but not totally sure...

[quinnj]

This is tricky because we have to hard-code dependencies to make them useable and performant. I don't have major issues with making changes to the core sqlite_jll package if it's not disruptive for normal cases and just making some of these features the "default" for everyone. We did that w/ a few features already like thread safety.

The problem w/ passing it as a keyword arg is that there are some dozens, maybe even hundreds of places where you'd have to support and plumb through passing a custom sqlite library. Having your own fork that makes the change is also not unreasonable, honestly. If other's start asking for the same functionality, we could possibly even register; it would just be slightly annoying to keep the SQLCipher up to date w/ changes that happen here.

Another idea is that we just take both binary packages as dependencies in SQLite and then bake the library selection into a global Ref that each ccall would check. We do something slightly similar in ODBC.jl for letting users use different odbc libraries (and taking all possible choices as dependencies). I'd be open to something like that as long as the extra dependencies seem to warrant it.

[aplavin]

we have to hard-code dependencies to make them useable and performant

That's my understanding of the current state as well. Unfortunately, this loses a major feature of dynamic libraries: they can naturally be replaceable by other libraries having the same interface, at runtime.

I don't have major issues with making changes to the core sqlite_jll package if it's not disruptive for normal cases and just making some of these features the "default" for everyone

sqlcipher is a separate library based on a specific sqlite version, not just a compilation option.

[aviks]

I don't have major issues with making changes to the core sqlite_jll package if it's not disruptive for normal cases and just making some of these features the "default" for everyone

Some of the sqlite extensions are proprietary, not open source (Example)-- so this is unfortunately not possible in many cases.

I'd also like this functionality, but yes, I don't see an easy way to achieve this.

[JohnCobbler]

Revisiting this — modern JLLWrappers + Preferences.jl already solve the drop-in case (sqlcipher, SEE) with zero code changes to SQLite.jl, which sidesteps both concerns raised here: no kwarg to plumb through the ccall sites, and it works for proprietary closed-source libs too since the user points at their own .so.

SQLite_jll resolves libsqlite at load time via a libsqlite_path preference. Add to your project's LocalPreferences.toml:

[SQLite_jll]
libsqlite_path = "/path/to/libsqlcipher.so"

Verified end-to-end (Julia 1.11, SQLite_jll 3.51.2): with the shipped lib, SELECT sqlite_version() returns 3.51.2; after pointing libsqlite_path at a different system libsqlite (3.45.1), the same query returns 3.45.1, and /proc/<pid>/maps confirms only the overridden .so is mapped. The dlopen is on the literal path, so the override file can be named libsqlcipher.so — no SONAME rename needed.

Caveats: it's per-environment (one engine per process, not per-DB()), editing the preference triggers one recompile, and the target must be ABI-compatible with the sqlite3 C API (sqlcipher/SEE are by design). But for "I want this project to use sqlcipher/SEE instead of stock sqlite," this covers it.

If that matches the intent here, happy to send a short docs PR (a "Using an alternative SQLite library" section) rather than any invasive change.

[aplavin]

per-environment is quite limiting...
and not sure I understand how exactly it looks from user PoV: say I install SQLite.jl and SQLCipher_jll, how to use the latter then?

[JohnCobbler]

Following up on the earlier LocalPreferences.toml override note — that one used a system-libsqlite3
version swap to prove the mechanism. I've now upgraded it to real SQLCipher, built from source, with
encryption actually exercised end-to-end through unmodified SQLite.jl.

Setup — built SQLCipher from source (community 4.16.0, SQLite core 3.53.1, OpenSSL provider) and
pointed SQLite.jl at it with nothing but:

# LocalPreferences.toml
[SQLite_jll]
libsqlite_path = "/abs/path/to/libsqlcipher.so"

Engine identity (the smoking gun) — stock SQLite returns zero rows for PRAGMA cipher_version;
through the override it returns the cipher version:

db = SQLite.DB()
DBInterface.execute(db, "SELECT sqlite_version()")   # 3.53.1
DBInterface.execute(db, "PRAGMA cipher_version")     # 4.16.0 community

Encryption round-trips through the normal SQLite.jl API. PRAGMA key must come first, before any
schema/DML:

db = SQLite.DB("encrypted.db")
DBInterface.execute(db, "PRAGMA key = 'testkey-correct-horse'")   # key BEFORE anything else
DBInterface.execute(db, "CREATE TABLE secrets(id INTEGER PRIMARY KEY, msg TEXT)")
DBInterface.execute(db, "INSERT INTO secrets(msg) VALUES('alpha-classified')")

Results:

• Encrypted on disk — first 16 bytes are random ciphertext, not the SQLite format 3\0 magic:

  hex   : 20 6f 9f b1 e0 67 8b af c7 09 75 80 ec d5 13 61
  matches "SQLite format 3\0"? false
  

  (file encrypted.db → data, and the header is different every time — SQLCipher salt randomization.)
• No key ⇒ locked out — reopening and querying without PRAGMA key throws:

  SQLiteException("file is not a database")
  

• With key ⇒ rows decrypt — reopen, PRAGMA key='...', and all rows read back intact.
• Interop — the file SQLite.jl wrote is read back by the standalone sqlcipher CLI with the same key,
  and rejected without it.

I also confirmed the live library is genuinely SQLCipher via Libdl.dlopen(..., RTLD_NOLOAD) +
Libdl.dlpath (resolves to the SQLCipher .so) and by resolving the SQLCipher-exclusive symbol
sqlcipher_codec_pragma through that handle; a stock-SQLite_jll control shows none of these
(no cipher_version, no libcrypto mapped, no sqlcipher_* symbols).

Two small gotchas worth recording: (1) changing libsqlite_path auto-triggers SQLite_jll
precompilation — no manual cache clearing needed to swap engines; (2) PRAGMA key ordering matters —
it must be the first statement after opening.

Net: [SQLite_jll] libsqlite_path is a clean, code-change-free way to run SQLite.jl on a SQLCipher
drop-in with full transparent encryption.

[aplavin]

seems like pure llm comments

[JohnCobbler]

Yes, my ai drafted the walkthrough for me, I thought you were asking for one. I cant's tell if your objection was aimed at lack of brevity or you are questioning the solution aproach itself? I tested it locally first before posting.
TLDR - [SQLite_jll] libsqlite_path in LocalPreferences.toml does what your SQLCipher fork does, without the fork - PRAGMA cipher_version returns 4.16.0 through unmodified SQLite.jl. Happy to send a docs PR or test a different approach.