Open sqlite connection from &[u8]

[mattrighetti]

I have a use case where I'd need to read an encrypted sqlite file from the filesystem and I ideally I would not want to store the de-crypted database on the filesystem.

The de-crypt function would return the sqlite bytes as &[u8] and I'd need to have some sort of sqlite database :memory: to work with. As soon as I'm done with my queries I would then need to take those bytes, encrypt them and finally writing the filesystem.

Is this something that's possible with sqlx? If not, is there a workaround to achieve something similar? Maybe storing the database in a similar format that I can then easily migrate into sqlite::memory:?

The current POC that I've created basically does the following:

• read encrypted sqlite from filesystem
• write de-crypted sqlite to filesystem
• connect to de-crypted sqlite and do operations on it
• write new encrypted database to filesystem and delete de-crypted database

Thanks!

[mattrighetti]

Just realized that a potential solution would be to store the encrypted sqlite database dump instead of the sqlite file itself, open a connection to sqlite::memory: and run the migration to load the data, but either way I'd be interested to know if what I first asked in the chat would be possible with sqlx (I think it would require less implementation logic).

[abonander]

You could probably implement something like this using sqlite3_deserialize() and sqlite3_serialize(), but that has some potential pitfalls:

• It's not currently exposed in the SQLx API: [SQLite] Expose sqlite3_serialize and sqlite3_deserialize #192
• If you want the database to be growable (SQLITE_DESERIALIZE_RESIZEABLE), the pointer needs to be allocated through sqlite3_alloc().
• The database may grow to the point where copying it back and forth in memory is extremely inefficient.

However, if you don't need a specific encrypted format and just need the database file to be encrypted, might I suggest using the existing support for SQLCipher?

It's not documented at all... and I just realized it's not actually exposed as a feature by sqlx, but you can enable the bundled-sqlcipher feature of libsqlite3-sys (or just sqlcipher if you're using the sqlite-unbundled feature of SQLx) and then pass the encryption passphrase/key using SqliteConnectOptions::pragma() (there's special logic to ensure this is the first PRAGMA executed, if set).

[mattrighetti]

Thank you for your response @abonander, how difficult would it be to submit a PR for #192? I have never looked into the sqlx internals but maybe I can try and work on that with little guidance.

[abonander]

Wrote up a rundown of what the API should look like: #192 (comment)