feat(sql) Adding support for Sqlite database encryption + Other pragma (journal, read only...) + In memory database

[eleroy]

Hi everyone,

Here is a pull request referring to issues #7 #875 and PR #877 (in-memory database).

This is my first pull request, and I'm very new to rust so if the code and PR are not well written/formatted please feel free to edit it.

I propose a way to specify SqlLite options similarly to Migrations. For this I had to review also how non-existing database are created. Here is how it works:

Sqlite options

Adding Sqlite options

Similarly as adding migrations, it is possible to add Sqlite options such as database encryption or regular Sqlite options.

use tauri_plugin_sql::{Builder, Migration, MigrationKind, SqliteConfig};

fn main() {
    let migrations = vec![
        // Define your migrations here
        Migration {
            version: 1,
            description: "create_initial_tables",
            sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
            kind: MigrationKind::Up,
        }
    ];

    tauri::Builder::default()
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations("sqlite:mydatabase.db", migrations)
                .add_sqlite_options("sqlite:mydatabase.db", SqliteConfig{key:"my_database_key", journal_mode:"OFF", foreign_keys:true, read_only:false,..Default::default()})
                .build(),
        )
        ...
}

All the options are specified in the struct definition

pub struct SqliteConfig {
    pub key: &'static str, // Database key
    pub cipher_page_size: i32, // Page size of encrypted database. Default for SQLCipher v4 is 4096.
    pub cipher_plaintext_header_size: i32,
    pub kdf_iter: i32, // Number of iterations used in PBKDF2 key derivation. Default for SQLCipher v4 is 256000
    pub cipher_kdf_algorithm: &'static str,  // Define KDF algorithm to be used. Default for SQLCipher v4 is PBKDF2_HMAC_SHA512.
    pub cipher_hmac_algorithm: &'static str, // Choose algorithm used for HMAC. Default for SQLCipher v4 is HMAC_SHA512.                                        
    pub cipher_salt: Option<&'static str>, // Allows to provide salt manually. By default SQLCipher sets salt automatically, use only in conjunction with 'cipher_plaintext_header_size' pragma
    pub cipher_compatibility: Option<i32>, // 1, 2, 3, 4
    pub journal_mode: &'static str,        // DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
    pub foreign_keys: bool,
    pub synchronous: &'static str,  // EXTRA | FULL | NORMAL |  OFF
    pub locking_mode: &'static str, // NORMAL | EXCLUSIVE
    pub read_only: bool, // set read only mode
}

In memory

A database name containing :memory will be loaded as an in-memory database.

SqlCipher

To make it work with SqlCipher, you'll need to make additionnal changes to you cargo.toml:

[dependencies]
...
libsqlite3-sys = { version = "*", features = ["bundled-sqlcipher"] }

You'll also need openssl to build. Please refer to libsqlite3-sys documentation to understand how to build.

On windows, I recommend building and installing openssl using vcpkg https://github.com/Microsoft/vcpkg

• Clone vcpkg to C:\src\vcpkg for example
• Bootstrap vcpkg C:\src\vcpkg\bootstrap-vcpkg.bat
• C:\src\vcpkg\vcpkg.exe integrate install
• C:\src\vcpkg\vcpkg.exe install openssl:x64-windows
• Add env variable OPENSSL_DIR = C:\src\vcpkg\installed\x64-windows

[FabianLars]

Thanks for contributing and sorry for the long delay in your first PR!

To make it work with SqlCipher, you'll need to make additionnal changes to you cargo.toml:

We could expose this as a feature flag in the plugin (similar to the sqlite/mysql/psql flags).

You'll also need openssl to build. Please refer to libsqlite3-sys documentation to understand how to build.

Same for this with the bundled-sqlcipher-vendored-openssl flag.

---

About the options:

• For the rust side i think it makes more sense to re-export the sqlx SqliteConnectOptions struct directly and keep the custom struct wrapper for the js bindings only (remove the pub from the struct and the )
• Not really sure but i think we should keep the read_only option on the path like we've had it so far. If not, then it looks like you're not actually using this option.
• I'd prefer if we could get rid of the custom is_in_memory stuff and simply require paths that sqlx can work with

[eleroy]

Hello @FabianLars,

I did change the commit to use directly the SqliteConnectOptions struct from sqlx, it does make sense to use it directly.

I'm not really sure how you want to make the connect options work with the JS backend. Would be nice to load dynamically any database with the connect options but I'm not really sure how to do that.

I've tried to implement it so that it is possible to specify an in-memory database in the SQLite options. It could eventually be possible to use arbitrary file paths, but i still use the path_mapper to build the path in this pull request.

I also added the feature flags for sqlcipher.

Sqlite options

Adding Sqlite options

Similarly as adding migrations, it is possible to add Sqlite options such as database encryption or regular Sqlite options.

use tauri_plugin_sql::{Builder, Migration, MigrationKind, SqliteConnectOptions, SqliteJournalMode};

fn main() {
    let migrations = vec![
        // Define your migrations here
        Migration {
            version: 1,
            description: "create_initial_tables",
            sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
            kind: MigrationKind::Up,
        }
    ];

    tauri::Builder::default()
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations("sqlite:mydatabase.db", migrations)
                .add_sqlite_options("sqlite:mydatabase.db", SqliteConnectOptions::new().pragma("key", "my_database_key").journal_mode(SqliteJournalMode::Off))
                .build(),
        )
        ...
}

Refer to the SqliteConnectOptions doc of the sqlx crate to view all possible options.

In memory

It is possible to use an in-memory database using SqliteConnectOptions::from_url with sqlite::memory: as url.

use tauri_plugin_sql::{Builder, Migration, MigrationKind, SqliteConnectOptions, SqliteJournalMode};

fn main() {
    let migrations = vec![
        // Define your migrations here
        Migration {
            version: 1,
            description: "create_initial_tables",
            sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
            kind: MigrationKind::Up,
        }
    ];

    tauri::Builder::default()
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations("sqlite:mydatabase.db", migrations)
                .add_sqlite_options("sqlite:mydatabase.db", SqliteConnectOptions::from_url("sqlite::memory:"))
                .build(),
        )
        ...
}

SqlCipher

To make it work with SqlCipher, you'll need to use the bundled-sqlcipher or bundled-sqlcipher-vendored-openssl instead of the sqlite feature depending on your system.

[dependencies.tauri-plugin-sql]
features = ["bundled-sqlcipher"]

You'll also need openssl to build. Please refer to libsqlite3-sys documentation to understand how to install openssl.

On windows, I recommend building and installing openssl using vcpkg https://github.com/Microsoft/vcpkg

• Clone vcpkg to C:\src\vcpkg for example
• Bootstrap vcpkg C:\src\vcpkg\bootstrap-vcpkg.bat
• C:\src\vcpkg\vcpkg.exe integrate install
• C:\src\vcpkg\vcpkg.exe install openssl:x64-windows
• Add env variable OPENSSL_DIR = C:\src\vcpkg\installed\x64-windows

[FabianLars]

Oh man, i really didn't think this through at first. I totally forgot that you can open multiple databases at the same time...

I'll try allocate a bit of time this week to think about this plugin. There are a few things i'd like to change and it probably makes sense to combine some of them. Would it be okay for you if i combine your changes into another PR and add you as a co-author (if it comes to that)?

[eleroy]

Hello @FabianLars,

No problem on my side, please feel free to combine whatever part of this contribution which is useful to you. Feel free to also add code from #1458 if you find it useful.

Edouard.

[FabianLars]

Thanks! I think we can merge 1458 seperately before that tbh. It should (hopefully) be unrelated enough 😅

[lozbub]

Hey @eleroy,

i tried to use your plugin to decrypt my sqlite database.

Unfortunately I am not able to get it work.

Here is my cargo.toml

 [build-dependencies]
 tauri-build = { version = "2.0.0-beta", features = [] }
 
 [dependencies]
 tauri = { version = "2.0.0-beta", features = [] }
 tauri-plugin-shell = "2.0.0-beta"
 serde = { version = "1", features = ["derive"] }
 serde_json = "1"
 libsqlite3-sys = { version = "*", features = ["bundled-sqlcipher"] }
 
 [dependencies.tauri-plugin-sql]
 features = ["sqlite"] # or "postgres", or "mysql"
 version = "2.0.0-beta"
 git = "https://github.com/eleroy/plugins-workspace"
 branch = "v2"

Here the main.rs

fn main() {
    tauri::Builder::default()
        .plugin(tauri_plugin_shell::init())
        .plugin(tauri_plugin_sql::Builder::default().build())
        .invoke_handler(tauri::generate_handler![])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

The method .add_sqlite_options() is not available in my project.

fn main() {
    tauri::Builder::default()
        .plugin(tauri_plugin_shell::init())
        .plugin(tauri_plugin_sql::Builder::default()
                     .add_sqlite_options("sqlite:mydatabase.db", SqliteConnectOptions::new().pragma("key", "my_database_key").journal_mode(SqliteJournalMode::Off))
                     .build())
        .invoke_handler(tauri::generate_handler![])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

Am I doing something wrong? I am using MacOS.

Thanks in advance!

[eleroy]

Hello,

Ah yes, I need to update the readme.

I moved the PR to another branch:

[dependencies.tauri-plugin-sql]
features = ["bundled-sqlcipher"] # or "postgres", or "mysql"
version = "^2.0.0-beta"
# alternatively with Git
git = "https://github.com/eleroy/plugins-workspace"
branch = "sql/sqlcipher"

also you can remove libsqlite3-sys = { version = "*", features = ["bundled-sqlcipher"] } because it is now directly a feature of the plugin.

Also on Mac os I might not work out of the box (I have not tested).

I think it is best to use the feature bundled-sqlcipher-vendored-openssl for Mac Os.

Look into the docs here if you have more trouble https://github.com/rusqlite/rusqlite#notes-on-building-rusqlite-and-libsqlite3-sys.

Let me know if it works or not.

[lozbub]

Hey!

Thank you, it works!

I tried the "bundled-sqlcipher-vendored-openssl" - This one didn't work for me on MacOS. The "bundled-sqlcipher" works fine for me on MacOS!

Best wishes

[lozbub]

Hey @eleroy,

how do you handle the database connection when reloading the client?

When Tauri is initializing the plugins, accessing the Database works fine. When I am reloading my frontend application I get an error when using an select statement: "error returned from database: (code: 26) file is not a database"

On reload I first load my Database and then I use my select statements. I still tried to close the current connection when the view gets destroyed but with no success.

Do you know a workaround?

Best wishes!

[eleroy]

Hello,

Yes it is pretty annoying having to reload the whole app. I don't know how to handle this. From my experience, this happens with several tauri plugins. Maybe you could search in tauri plugin issues.

You can maybe try to put all the database logic in a separated file and then import the database and select method to your App. Then when you reload, it won't reload the database logic unless the specific file is modified (I guess...).

[lucanisi]

Thank you so much for this plugin! I successfully used SQLite with encryption across iOS, Android, macOS, and Windows x86_64. The only step required was installing OpenSSL correctly for each platform.

However, I’m encountering an issue on Windows ARM64. The build succeeds, but the app crashes right after executing the first query. Simple queries like SELECT 1 work fine, but when I attempt a CREATE TABLE, the app crashes.

Here’s the error I get right after the crash:

error Command failed with exit code 3221225477.

Has anyone else experienced this?

I'm using the following dependencies:

[dependencies.tauri-plugin-sql]
features = ["bundled-sqlcipher-vendored-openssl"] # or "postgres", or "mysql"
version = "^2.0.0-beta"
# alternatively with Git
git = "https://github.com/eleroy/plugins-workspace"
branch = "sql/sqlcipher"

I'm testing this in a Windows 11 VM (Parallels Desktop) on MacBook Pro M2

Any help or insights would be greatly appreciated!

[petersamokhin]

The only step required was installing OpenSSL correctly for each platform.

@lucanisi hey, thanks for your comment with confirmation that you made it work! For sake of documentation, could you elaborate here? A short guide will be very helpful for the future Tauri users who find this issue/PR and try to make it work. Ideally, with the details how do you ensure openssl is properly installed across systems and is usable by underlying sqlx. Thanks!

[haexhub]

Hi will it be possible to include the pragma statements via the database constructor? Something like this would be nice.

import Database from '@tauri-apps/plugin-sql';
const db = await Database.load('sqlite:mydatabase.db', {
 pragmas: { 
    key: "userpassword", 
    rekey: "new_password", 
    cipher: "aes256",
...});

in my use case, the users can create and open several databases with their own password. Especially the possibility of reencryption would be nice.

I also would love to see if we could use drizzle or other ORM with this.

[MrChaker]

@lozbub hey man . did you solve client reloading problem ? @eleroy

[galbangi]

hi, i am trying to implement this plugin to my tauri app on mac m3 pro.

i am using in "Cargo.toml"

i also tried with "bundled-sqlcipher-vendored-openssl" [dependencies.tauri-plugin-sql] features = ["bundled-sqlcipher"] # or "postgres", or "mysql" version = "^2.0.0-rc.0" /# alternatively with Git git = "https://github.com/eleroy/plugins-workspace" branch = "sql/sqlcipher"

im my main.rs

.plugin( tauri_plugin_sql::Builder::default() .add_migrations("sqlite:mydatabase.db", migrations) .add_sqlite_options("sqlite:mydatabase.db", SqliteConnectOptions::new().pragma("key", "my_database_key").journal_mode(SqliteJournalMode::Off)) .build(), )

this is all working. file is created and encrypted. i can open file with passowrd and see all tables.

but when i am trying to access from front end i am getting error. ERROR"error returned from database: (code: 26) file is not a database"

can someone please help me with this.

[lucanisi]

hi, i am trying to implement this plugin to my tauri app on mac m3 pro.

i am using in "Cargo.toml"

i also tried with "bundled-sqlcipher-vendored-openssl" [dependencies.tauri-plugin-sql] features = ["bundled-sqlcipher"] # or "postgres", or "mysql" version = "^2.0.0-rc.0" /# alternatively with Git git = "https://github.com/eleroy/plugins-workspace" branch = "sql/sqlcipher"

im my main.rs

.plugin( tauri_plugin_sql::Builder::default() .add_migrations("sqlite:mydatabase.db", migrations) .add_sqlite_options("sqlite:mydatabase.db", SqliteConnectOptions::new().pragma("key", "my_database_key").journal_mode(SqliteJournalMode::Off)) .build(), )

this is all working. file is created and encrypted. i can open file with passowrd and see all tables.

but when i am trying to access from front end i am getting error. ERROR"error returned from database: (code: 26) file is not a database"

can someone please help me with this.

As a workaround, can you maybe try creating a custom function that injects the pragma before the query?

Something like

const dbName = "your_db.sql";
const dbKey = "your_key";

let db = await Database.load(`sqlite:${dbName}`);

const injectKey = async () => {
  return db.execute(`PRAGMA key = '${dbKey}';`);
};

const dbSelect = async (query, params) => {
  await injectKey();
  return db.select(query, params);
};

const dbExecute = async (query, params) => {
  await injectKey();
  return db.execute(query, params);
};

I'm not sure since I haven't investigated too much, but I suspect the pragma must be sent before any query.

[Eveeifyeve]

@eleroy any progress on this? else I might consider taking over and crediting for the code you have provided.

[eleroy]

@Eveeifyeve feel free to work on this as you wish. @FabianLars planned on rewriting how the feature was implemented that was why the pr was not merged in the first place.

[Eveeifyeve]

@Eveeifyeve feel free to work on this as you wish. @FabianLars planned on rewriting how the feature was implemented that was why the pr was not merged in the first place.

Well I want to promise encryption with a Minecraft client launcher that I am building with SQLite db to store credentials, so I will work on this since this is a top priority before it does release fully to the public and non beta.