If you're making a Tauri app and are looking for ways to persist data locally for your application SQLite is a common choice, and the sqlx crate provides an easy way to query and manage a database. So for this post we'll build a todo app saving our todos to an SQLite database from rust, and we'll build the frontend with simple HTML and vanilla JS.
What we'll cover:
- Set up a sqlite database using sqlx including migrations
- Manage global state in tauri
- create basic crud functions and call them from the frontend
Getting started
To get started we'll create a new tauri project.
npm create tauri-app@latestWalk through the prompts, for this demo we're using vanilla JS.
Next we'll install our required crates for the project.
cd src-tauricargo add sqlx --features sqlx/sqlite --features sqlx/runtime-tokiocargo add futuresHere we pull in sqlx for the database and futures which will help us stream data from our queries.
Setting up the DB
Now we'll make a function to set up the sqlite database, we'll need to create a database file if there isn't one and open a connection pool using sqlx.
async fn setup_db(app: &App) -> Db { let mut path = app.path().app_data_dir().expect("failed to get data_dir"); match std::fs::create_dir_all(path.clone()) { Ok(_) => {} Err(err) => { panic!("error creating directory {}", err); } }; path.push("db.sqlite"); Sqlite::create_database( format!( "sqlite:{}", path.to_str().expect("path should be something") ) .as_str(), ) .await .expect("failed to create database"); let db = SqlitePoolOptions::new() .connect(path.to_str().unwrap()) .await .unwrap(); sqlx::migrate!("./migrations").run(&db).await.unwrap(); db}The setup_db function accepts a reference to tauri::App so we can get the proper path where the application data should live on whatever operating system it lives on. After getting the path we try to create it, and panic to crash the app if we can't.
Next we push "db.sqlite" to our path to include the name of our database file, then we attempt to create the database file, in the match statement we print out if a new file was created, we ignore an error if the file already exists, or we panic for any other error.
Lastly, we open the database pool with sqlx and return the pool.
Note we call panic! or unwrap() in this function because the database connection is required for the whole application to work so it's okay if the application crashes if we don't have a database connection.
Migrations with sqlx
We have a connection to our sqlite database, but we don't have any tables set up yet, this is where migrations come in. Thankfully, sqlx makes this very easy. First we need to install the sqlx-cli. We can use cargo to download and build it.
cargo install sqlx-cliNow we can add our migrations.
cd src-taurisqlx migrate add create_todos_tableWe cd into our src-tauri directory because when sqlx::migrate!() macro is called, it looks for files relative to the project's Cargo.toml file.
Now we can open the newly generated file and add our database schema.
CREATE TABLE todos ( id INTEGER PRIMARY KEY, description TEXT, status VARCHAR(30));For our simple todo app, we just need one todos table, with an id description and status columns.
Now that we have a migration file, we'll add a line of code to automatically update our database schema every time the app launches. We'll add this just after creating the sqlite pool in setup_db().
sqlx::migrate!("./migrations").run(&db).await.unwrap();Adding the Db to global state
To use the sqlite pool throughout the application, we'll use Tauri's managed state. First we'll add a struct to hold our application state, and type alias The Pool<Sqlite> type.
type Db = Pool<Sqlite>; struct AppState { db: Db,}Next we'll modify the run function to set up our db and manage it globally.
pub fn run() { tauri::Builder::default() .plugin(tauri_plugin_shell::init()) .invoke_handler(tauri::generate_handler![ add_todo, get_todos, update_todo, delete_todo ]) .setup(|app| { tauri::async_runtime::block_on(async move { let db = setup_db(&app).await; app.manage(AppState { db }); }); Ok(()) }) .run(tauri::generate_context!()) .expect("error building the app");}Here we use the setup() hook and use tauri's async runtime to set up our database, then call app.manage() to manage our global state, finally we call app.run() to run our application.
Todo CRUD
Now that we have our db and app setup we can move on to the main logic of our application. First, let's define a Todo struct and a TodoStatus to hold our todo data.
#[derive(Debug, Serialize, Deserialize, sqlx::Type)]enum TodoStatus { Incomplete, Complete,} #[derive(Debug, Serialize, Deserialize, FromRow)]struct Todo { id: u16, description: String, status: TodoStatus,}For both the Todo and the TodoStatus we derive serde's Serialize, and Deserialize traits, because we will be passing these between the frontend and backend of application as JSON. The TodoStatus derives the sqlx::Type which will convert our the enum into a string in the database and back to an enum when taking it out.
Adding a Todo
Now we'll create a function to save a new todo to our database, and to expose this function to the frontend JavaScript we'll make a tauri::command.
#[tauri::command]async fn add_todo(state: tauri::State<'_, AppState>, description: &str) -> Result<(), String> { let db = &state.db; sqlx::query("INSERT INTO todos (description, status) VALUES (?1, ?2)") .bind(description) .bind(TodoStatus::Incomplete) .execute(db) .await .map_err(|e| format!("Error saving todo: {}", e))?; Ok(())}Let's break down the function. It accepts our global state, and a description of the task. Since it's an async tauri command, it must return a Result type, and both the Ok and Err values must implement the Serialize trait. In our case the () and String will work.
In the function body we get a reference to Db from the state, then write our query to insert a new todo. We bind our values, defaulting status to incomplete. Since sqlx errors don't implement the Serilize trait, we have to map our error to a String before using the try operator (?) to bubble the error up. If everything works we'll return Ok(()).
Adding todos from JavaScript
Before we can call add_todo from JavaScript, we need to add it to the tauri::generate_handler![] macro in the main function to expose it to the frontend.
.invoke_handler(tauri::generate_handler![add_todo])With that taken care of, we can open our main.js file and write a function to add our todos.
const { invoke } = window.__TAURI__.core; async function addTodo(description) { return await invoke("add_todo", { description, });} window.addEventListener("DOMContentLoaded", () => { addTodo("Learn Tauri ✨");});Invoking a tauri command is pretty easy, first we'll get the invoke function from the global window object, and in the addTodo function it accepts a description, and invokes the add_todo command passing along the description to be handled by our rust code. So we can test for now we can add a todo when the page loads for the first time. If you don't have your app running yet run it with npm run tauri dev.
Getting todos
Now that we're saving todos, we'll need to be able to get them, so let's add another command.
// add this to your use statementsuse futures::TryStreamExt; #[tauri::command]async fn get_todos(state: tauri::State<'_, AppState>) -> Result<Vec<Todo>, String> { let db = &state.db; let todos: Vec<Todo> = sqlx::query_as::<_, Todo>("SELECT * FROM todos") .fetch(db) .try_collect() .await .map_err(|e| format!("Failed to get todos {}", e))?; Ok(todos)}Same as before but now we're returning a Vec<Todo> in our Result. This will automatically be serialized into a json array on the frontend.
In the body we'll call sqlx::query_as() and type it to our Todo struct, call fetch, then try_collect() to convert the stream into a Vec<Tool> which we'll return. Like before, add get_todos to the generate_handler![] macro.
Getting todos from JavaScript
Like before let's make a simple function to get our todos.
async function getTodos() { return await invoke("get_todos");} window.addEventListener("DOMContentLoaded", () => { getTodos().then(console.log);});Now we can get the todo we saved earlier and log it to the console.
Update and delete todos
Now that we have the basics down, update and delete should be simple.
#[tauri::command]async fn update_todo(state: tauri::State<'_, AppState>, todo: Todo) -> Result<(), String> { let db = &state.db; sqlx::query("UPDATE todos SET description = ?1, status = ?2 WHERE id = ?3") .bind(todo.description) .bind(todo.status) .bind(todo.id) .execute(db) .await .map_err(|e| format!("could not update todo {}", e))?; Ok(())} #[tauri::command]async fn delete_todo(state: tauri::State<'_, AppState>, id: u16) -> Result<(), String> { let db = &state.db; sqlx::query("DELETE FROM todos WHERE id = ?1") .bind(id) .execute(db) .await .map_err(|e| format!("could not delete todo {}", e))?; Ok(())}The only thing to note here is that we're taking a whole Todo struct in update, so we'll replace each value and update where the id matches the id in the database. Add these to the generate_handler![] and we'll add move to our JavaScript.
Like before, we're just making helper functions for later.
async function updateTodo(todo) { return await invoke("update_todo", { todo });} async function deleteTodo(id) { return await invoke("delete_todo", { id });}Creating the UI
Our UI will be simple, a list of todos, with checkboxes, and delete buttons, and an input to create new tasks.
Here's our basic html, just a form and a container for todos.
<div class="container"> <h1>Todos</h1> <div id="tasks" class="task-list"></div> <form class="row" id="todo-form"> <input id="todo-input" placeholder="Enter task description" /> <button class="btn" type="submit">add todo</button> </form></div>Back to our main.js we'll create a function to build our todo list's html, insert that into the #tasks container and register event listeners for the checkbox and delete button.
async function buildTodoList() { let todos = await getTodos(); let tasksContainer = document.querySelector("#tasks"); tasksContainer.innerHTML = ""; todos.forEach((todo) => { let div = document.createElement("div"); div.classList.add("todo-wrapper"); div.innerHTML = ` <label> <input type="checkbox" class="todo-item" data-id="${todo.id}" data-description="${todo.description}" ${todo.status === "Complete" ? "checked='checked'" : ""}> <span>${todo.description}</span> </label> <button class="delete" data-id="${todo.id}"> delete </button> `; tasksContainer.appendChild(div); }); document.querySelectorAll(".todo-item").forEach((el) => { el.addEventListener("input", (input) => { let data = input.target.dataset; updateTodo({ id: parseInt(data.id), description: data.description, status: input.target.checked ? "Complete" : "Incomplete", }); }); }); document.querySelectorAll(".delete").forEach((el) => { el.addEventListener("click", async (event) => { let id = parseInt(event.target.dataset.id); await deleteTodo(id); await buildTodoList(); }); });} window.addEventListener("DOMContentLoaded", () => { buildTodoList();});Now when our page loads it will load our todos and display them in a list.
Lastly, we'll handle the adding a todo with the form inside the content loaded event listener. When the form is submitted we grab the input value, add the todo, when that finishes the list will be rebuilt and displayed.
window.addEventListener("DOMContentLoaded", () => { buildTodoList(); document.querySelector("#todo-form").addEventListener("submit", (event) => { event.preventDefault(); let input = document.querySelector("#todo-input"); addTodo(input.value).then(() => { buildTodoList(); }); input.value = ""; });});Now we should have a working todo app, we can add todos, check them off or delete them, and if we reload our application it should pick up right where we left it.