SQLite CRUD in Tauri: Build an Article Manager with Rust & React

SQLite CRUD in Tauri: Build an Article Manager with Rust & React

This tutorial demonstrates how to create a full CRUD application using SQLite in a Tauri desktop app with Rust and a React frontend. You'll learn to set up a thread-safe SQLite database with rusqlite, implement create, read, update, and delete operations, and connect them to a React UI.

Code

use serde::{Deserialize, Serialize};
use rusqlite::{params, Connection};
use std::sync::Mutex;
use tauri::State;

// Define the Article model with serialization for frontend communication
#[derive(Debug, Serialize, Deserialize, Clone)]
struct Article {
  id: Option,        // None for new articles, Some(id) for existing
  title: String,
  content: String,
  created_at: Option, // Set by SQLite automatically
}

// Thread-safe wrapper around rusqlite Connection
struct Database(Mutex<Connection>);

impl Database {
  // Initialize database and create articles table if not exists
  fn init() -> Self {
    let conn = Connection::open("articles.db").expect("Failed to open DB");
    conn.execute(
      "CREATE TABLE IF NOT EXISTS articles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )",
      [],
    ).unwrap();
    Database(Mutex::new(conn))
  }
}

// Create (Insert) a new article
#[tauri::command]
fn add_article(db: State<Database>, article: Article) -> Result<Article, String> {
  let conn = db.0.lock().unwrap();
  conn.execute(
    "INSERT INTO articles (title, content) VALUES (?, ?)",
    params![article.title, article.content],
  ).map_err(|e| e.to_string())?;
  let id = conn.last_insert_rowid();
  Ok(Article {
    id: Some(id),
    created_at: None,
    ..article
  })
}

// Read (Select) all articles ordered by newest
#[tauri::command]
fn get_articles(db: State<Database>) -> Result<Vec<Article>, String> {
  let conn = db.0.lock().unwrap();
  let mut stmt = conn.prepare("SELECT id, title, content, created_at FROM articles ORDER BY created_at DESC").map_err(|e| e.to_string())?;
  let articles_iter = stmt.query_map([], |row| {
    Ok(Article {
      id: row.get(0)?,
      title: row.get(1)?,
      content: row.get(2)?,
      created_at: row.get(3)?,
    })
  }).map_err(|e| e.to_string())?;
  let mut articles = Vec::new();
  for article in articles_iter {
    articles.push(article.map_err(|e| e.to_string())?);
  }
  Ok(articles)
}

// Update an existing article partially
#[tauri::command]
fn update_article(db: State<Database>, updated: Article) -> Result<Article, String> {
  let conn = db.0.lock().unwrap();
  // Fetch existing article
  let mut stmt = conn.prepare("SELECT id, title, content FROM articles WHERE id = ?").map_err(|e| e.to_string())?;
  let existing = stmt.query_row(params![updated.id], |row| {
    Ok(Article {
      id: row.get(0)?,
      title: row.get(1)?,
      content: row.get(2)?,
      created_at: None,
    })
  }).map_err(|_| "Article not found".to_string())?;
  // Use updated fields or keep existing
  let new_title = if updated.title.is_empty() { existing.title } else { updated.title };
  let new_content = if updated.content.is_empty() { existing.content } else { updated.content };
  // Execute update query
  conn.execute(
    "UPDATE articles SET title = ?, content = ?, created_at = CURRENT_TIMESTAMP WHERE id = ?",
    params![new_title, new_content, updated.id],
  ).map_err(|e| e.to_string())?;
  Ok(Article {
    id: updated.id,
    title: new_title,
    content: new_content,
    created_at: None,
  })
}

// Delete an article by id
#[tauri::command]
fn delete_article(db: State<Database>, id: i64) -> Result<bool, String> {
  let conn = db.0.lock().unwrap();
  let affected = conn.execute("DELETE FROM articles WHERE id = ?", params![id]).map_err(|e| e.to_string())?;
  Ok(affected > 0)
}

// In lib.rs main setup:
// fn main() {
//   let db = Database::init();
//   tauri::Builder::default()
//     .manage(db)
//     .invoke_handler(tauri::generate_handler![add_article, get_articles, update_article, delete_article])
//     .run(tauri::generate_context!())
//     .expect("error while running tauri application");
// }

Key Points

• Use rusqlite with the "bundled" feature to embed SQLite without external dependencies.
• Wrap the SQLite connection in a Mutex to ensure thread-safe access in Tauri commands.
• Use parameterized queries with question marks to prevent SQL injection vulnerabilities.
• Define Rust structs with Serde traits for seamless JSON serialization between backend and React frontend.
• Register CRUD commands in Tauri’s invoke handler and share the database state via Tauri’s manage method.