Skip to main content

How to use local SQLite database with Tauri and Rust

· 5 min read
Luis Güette

Digital assistants are becoming increasingly popular as people seek more efficient ways to manage their tasks and streamline their daily routines. Orion is a desktop app that offers users the ability to create multiple assistants with specific goals, powered by chatGPT technology.

ChatGPT is a natural language processing model that enables computers to understand and respond to human language. With Orion, you can create assistants that are capable of helping you with anything you need, based on the goals you've defined for them.

One of Orion's key features is its local SQLite database, which stores the chat history for each assistant and allows you to easily organize your conversations by assistant. This database offers faster access to data and better performance, making it an ideal choice for Orion.

In this article, we will explain how to integrate a SQLite database into Tauri, the framework that powers Orion. We will walk you through the steps involved in setting up the database and provide tips for overcoming any potential challenges.

The first step is to define where you want to store your database file. In our case, we've chosen to store it in the ~/.config/orion/ directory.

To handle the creation and management of the database, we'll create a db.rs file with the following functions:

use std::fs;
use std::path::Path;

// Check if a database file exists, and create one if it does not.
pub fn init() {
if !db_file_exists() {
create_db_file();
}
}

// Create the database file.
fn create_db_file() {
let db_path = get_db_path();
let db_dir = Path::new(&db_path).parent().unwrap();

// If the parent directory does not exist, create it.
if !db_dir.exists() {
fs::create_dir_all(db_dir).unwrap();
}

// Create the database file.
fs::File::create(db_path).unwrap();
}

// Check whether the database file exists.
fn db_file_exists() -> bool {
let db_path = get_db_path();
Path::new(&db_path).exists()
}

// Get the path where the database file should be located.
fn get_db_path() -> String {
let home_dir = dirs::home_dir().unwrap();
home_dir.to_str().unwrap().to_string() + "/.config/orion/database.sqlite"
}

The init() function checks if a database file exists and creates one if it does not. It does this by calling the db_file_exists()function and if it returns false, it calls create_db_file().

The create_db_file() function is responsible for creating the database file. It starts by getting the path where the database file should be located (by calling get_db_path()). Then, it retrieves the parent directory of the database file path (using the parent() method of the Path struct). If the parent directory doesn't exist, it creates it using the create_dir_all() function from the fsmodule. Finally, it creates the database file using the File::create() function from the fs module.

The db_file_exists() function checks whether the database file exists or not. It does this by calling get_db_path() to get the path of the database file and checking if the file exists using the exists() method of the Path struct.

The get_db_path() function returns the path where the database file should be located. It starts by getting the home directory of the user (using the home_dir() function from the dirs module). Then, it converts the path to a string using to_str() and concatenates the string with "/.config/orion/database.sqlite" to get the full path of the database file.

To integrate the SQLite database into Tauri, we need to call the init() function in our main function. Here's how to do it:

//...

mod db;

async fn main() {
// Create a new Tauri application builder with default settings.
tauri::Builder::default()
.setup(|_app| {
// Initialize the database.
db::init();

Ok(())
})
.run(tauri::generate_context!())
.expect("error while running tauri application");
}

In the mainfunction, a new tauri::Builder instance is created using the default() method. This creates a new Tauri application builder with default settings.

The setup() method is called on the builder, which takes a closure that sets up the application. In this case, it initializes the database by calling the db::init() function.

The init() function checks if the database file exists and creates one if it does not. Once the database is initialized, it can be used to store and retrieve data using SQL queries. This makes it possible to create assistants with persistent memory that can remember previous conversations and provide personalized recommendations.

We can use Diesel to communicate with the SQLite database and run pending migrations automatically every time the desktop app initializes. Here's the updated db.rs code:

use std::fs;
use std::path::Path;

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};

const MIGRATIONS: EmbeddedMigrations = embed_migrations!();

pub fn init() {
if !db_file_exists() {
create_db_file();
}

run_migrations();
}

pub fn establish_db_connection() -> SqliteConnection {
let db_path = get_db_path().clone();

SqliteConnection::establish(db_path.as_str())
.unwrap_or_else(|_| panic!("Error connecting to {}", db_path))
}

fn run_migrations() {
let mut connection = establish_connection();
connection.run_pending_migrations(MIGRATIONS).unwrap();
}

fn establish_connection() -> SqliteConnection {
let db_path = "sqlite://".to_string() + get_db_path().as_str();

SqliteConnection::establish(&db_path)
.unwrap_or_else(|_| panic!("Error connecting to {}", db_path))
}

fn create_db_file() {
let db_path = get_db_path();
let db_dir = Path::new(&db_path).parent().unwrap();

if !db_dir.exists() {
fs::create_dir_all(db_dir).unwrap();
}

fs::File::create(db_path).unwrap();
}

fn db_file_exists() -> bool {
let db_path = get_db_path();
Path::new(&db_path).exists()
}

fn get_db_path() -> String {
let home_dir = dirs::home_dir().unwrap();
home_dir.to_str().unwrap().to_string() + "/.config/orion/database.sqlite"
}

Now you are ready to interact with your local SQLite database!

If you want to know more about how to keep a persistent state in your Tauri apps, check What you need to know about persistent state in Tauri apps from Aptabase where they explain multiple other options and the pros. and cons. for each one.

Are you curious about Orion? Our powerful code open source and the desktop app is available for download on Github - and we'd love to hear your thoughts! Connect with us on Twitter and let us know what you think. Don't miss out on this opportunity to take your work to the next level with Orion.

Discover the secrets behind our successful software with our book "MoonGuard: The Software Creator’s Journey" to learn how to create successful Laravel package from scratch! In addition to our website, we also maintain an active presence on Twitter (@moonguard_dev). By following us on Twitter, you'll be the first to know about any breaking news or important announcements regarding MoonGuard. So be sure to check us out and stay connected!