[SQLite] Built executables fail on SQLite connection

[TristanPouliquen]

Hello,

I am trying to build a Tauri application with a SQLite database.

My folder structure is:

src/
    src-tauri/
        db/
            mydb.sqlite3
        migrations/
            ...
        src/
            main.rs
            models/
                mod.rs

In my mod.rs file, I have the following code to connect to my DB:

pub fn establish_connection() -> SqliteConnection {
    SqliteConnection::establish("../../db/selection-poste-enm.sqlite3")
        .unwrap_or_else(|_| panic!("Error connecting to database"))
}

This works well when developing, but I get an error when trying to execute the built files.

What is the recommended way to have a Tauri app working with SQLite? Should I put my db/ folder in src, or add the sqlite3 file as a resource in my tauri.conf.json?

I also have a question on this topic: If I enable the Updater, and a new update is installed, will the database be replaced by the one from the updated version? How could I manage to update only the code & not the data?

I run my migrations at runtime with the diesel macro embed_migrations!.

Thanks in advance!

[FabianLars]

Adding it as a resource, or completely creating it at runtime (without bundling it) would indeed be the solution here.

For the updater thingy, if you create the database in a user data dir (like app_data_dir for example) this would not be a concern, and it would add cross-platform compatibility too.

[TristanPouliquen]

Thank you for your detailed answer, no worries, it is already awesome to have you reply this quickly 😅 .

I'll update my code with the tauri handle option i think, as I haven't been able to reuse a connection with SQLite as I have seen with Postres connections so I call my establish_connection each time I need a request.

[TristanPouliquen]

I'll stop for tonight, but I'm still having issues.

My problem:

• I try to establish a SqliteConnection in my main function to run the pending migrations;
• I establish a SqliteConnection in all my models functions (I haven't been able to reuse a SqliteConnection)

I've tried following your advice:

fn main() {
    tauri::Builder::default()
        .setup(|app| {
            let mut dirPath = app.path_resolver().app_data_dir().unwrap();
            dirPath.push("selection-poste-enm.sqlite3");
            path = dirPath.display().to_string();
            let connection = &mut establish_connection(path);
            connection
                .run_pending_migrations(MIGRATIONS)
        })
        .invoke_handler(tauri::generate_handler![
            ...
        ])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

#models/mod.rs
pub fn establish_connection(&path: String) -> SqliteConnection {
    SqliteConnection::establish(&path)
        .unwrap_or_else(|_| panic!("Error connecting to database"))
}

I get an error stating that .setup needs to return a certain type:
expected Result<(), Box>, found diesel::migration::Result<Vec>

From what I have tried to understand, I should return something like:

match <....> {
     Ok(_) => Ok(...),
     Err(e) => Err(e)
}

But I am not sure if this is correct nor how to really implement it 😅 .

I'll continue digging tomorrow with a fresh mind :)

[FabianLars]

Maybe like this? (didn't test it)

fn main() {
    tauri::Builder::default()
        .setup(|app| {
            let mut dirPath = app.path_resolver().app_data_dir().unwrap();
            let _ = std::fs::create_dir_all(&dirPath); // I FORGOT THIS THING HERE!!!
            dirPath.push("selection-poste-enm.sqlite3");
            path = dirPath.display().to_string();
            let connection = &mut establish_connection(path);
            connection
                .run_pending_migrations(MIGRATIONS)?; // This way we don't return the Vec but still return early on error
            Ok(()) // return an "empty" Ok
        })
        .invoke_handler(tauri::generate_handler![
            ...
        ])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

[TristanPouliquen]

Hi @FabianLars!

I took some time to go through your last message and try different things to make all of this work! 😅

It finally works, I can launch my app in dev mode, and build it with the updater working. I'll try and see if an update does not mess with my data as intended.

I've set up my main.rs like this:

pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("./migrations");
pub const DATABASE_NAME: &str = "selection-poste-enm.sqlite3";

fn get_db_path(app_handle: tauri::AppHandle) -> String {
    let mut app_dir = app_handle.path_resolver().app_data_dir().unwrap();
    app_dir.push(DATABASE_NAME);
    app_dir.display().to_string()
}

#[tauri::command]
fn get_app_state(app_handle: tauri::AppHandle) -> AppState {
    let db_path = get_db_path(app_handle);
    app_state_get(db_path)
}

fn main() {
    tauri::Builder::default()
        .setup(|app| {
            let mut dir_path = app.path_resolver().app_data_dir().unwrap();
            let _ = std::fs::create_dir_all(&dir_path);
            dir_path.push(DATABASE_NAME);
            let path = dir_path.display().to_string();
            let connection = &mut establish_connection(&path);
            connection.run_pending_migrations(MIGRATIONS)
                 .expect("Error loading migrations");
            Ok(())
        })
        .invoke_handler(tauri::generate_handler![
            get_app_state
        ])
        .run(tauri::generate_context!())

I removed your ? operator as I got an error stating that it did not have a size at compile-time.

I still get an error in my IDE, stating that there is an argument missing with tauri::AppHandle. I guess it comes from the type definition where there is a R: Runtime = Wry (https://docs.rs/tauri/latest/tauri/struct.AppHandle.html) as if I click on "fill in the default arguments" in my IDE, it adds a <R> behind.

But this does not impair my app, so maybe it is an error within the Rust plugin for IntelliJ.

Thanks again for all your help!

[FabianLars]

Yeah that's really just the intellij plugin not being able to handle the default value for the generic, no idea why. rust-analyzer in vscode for example can handle it fine.

If it gets too annoying you can just fill add to to your code, it will still compile/work fine, just being more explicit 🤷