$ cat /posts/tauri-20-database-integration-sqlite-and-local-storage.md

Tauri 2.0 Database Integration SQLite and Local Storage

drwxr-xr-x2026-01-285 min0 views

Database integration in Tauri 2.0 enables desktop applications to store and manage local data using SQLite—the world's most widely deployed database engine providing zero-configuration, serverless, self-contained SQL database perfect for desktop applications requiring structured data storage, complex queries, transactions, and data persistence without external database servers. SQLite integration leverages Rust's powerful rusqlite crate offering type-safe database operations, prepared statements preventing SQL injection, transaction support ensuring data consistency, and connection pooling for concurrent access creating robust local storage capabilities for user data, application settings, caching, and offline-first architectures. This comprehensive guide covers understanding SQLite benefits and architecture for desktop applications, setting up rusqlite with Tauri configuration, creating database schemas and tables with proper types, performing CRUD operations with INSERT, SELECT, UPDATE, DELETE, using prepared statements and parameter binding for security, implementing transactions for atomic operations, handling migrations and schema evolution, optimizing queries with indexes and query planning, managing database connections with connection pooling, and building real-world applications including note-taking apps, task managers, and data synchronization. Mastering database patterns enables building sophisticated desktop applications with persistent data storage, complex queries, full-text search, data relationships, backup/restore functionality, and offline-first capabilities syncing when online. Before proceeding, understand command creation and state management.

SQLite Setup and Configuration

tomlCargo.toml

# Add rusqlite to Cargo.toml
# src-tauri/Cargo.toml

[dependencies]
tauri = { version = "2.0", features = ["...your features..."] }
rusqlite = { version = "0.30", features = ["bundled"] }
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
tokio = { version = "1", features = ["full"] }

# Optional: For migrations
refinery = { version = "0.8", features = ["rusqlite"] }

# Optional: For connection pooling
r2d2 = "0.8"
r2d2_sqlite = "0.23"

rustdatabase_setup.rs

// Rust: Initialize database
use rusqlite::{Connection, Result as SqlResult};
use std::path::PathBuf;
use tauri::AppHandle;

// Get database path
fn get_db_path(app: &AppHandle) -> Result<PathBuf, String> {
    let app_dir = app.path_resolver()
        .app_data_dir()
        .ok_or("Failed to get app data directory")?;
    
    std::fs::create_dir_all(&app_dir)
        .map_err(|e| format!("Failed to create directory: {}", e))?;
    
    Ok(app_dir.join("database.db"))
}

// Initialize database with schema
fn init_database(db_path: &PathBuf) -> SqlResult<Connection> {
    let conn = Connection::open(db_path)?;
    
    // Create tables
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT NOT NULL
        )",
        [],
    )?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        [],
    )?;
    
    // Create indexes
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id)",
        [],
    )?;
    
    Ok(conn)
}

// Database state management
use std::sync::Mutex;
use tauri::State;

struct DbState {
    conn: Mutex<Connection>,
}

// Initialize in main
fn main() {
    tauri::Builder::default()
        .setup(|app| {
            let handle = app.handle();
            let db_path = get_db_path(&handle)?;
            let conn = init_database(&db_path)
                .map_err(|e| format!("Failed to init database: {}", e))?;
            
            app.manage(DbState {
                conn: Mutex::new(conn),
            });
            
            Ok(())
        })
        .invoke_handler(tauri::generate_handler![
            // Database commands here
        ])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

CRUD Operations

rustcrud_operations.rs

// Rust: CRUD operations
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
use tauri::State;

#[derive(Debug, Serialize, Deserialize)]
struct User {
    id: Option<i64>,
    name: String,
    email: String,
    created_at: String,
}

// CREATE
#[tauri::command]
fn create_user(
    state: State<DbState>,
    name: String,
    email: String
) -> Result<i64, String> {
    let conn = state.conn.lock().unwrap();
    let created_at = chrono::Utc::now().to_rfc3339();
    
    conn.execute(
        "INSERT INTO users (name, email, created_at) VALUES (?1, ?2, ?3)",
        params![name, email, created_at],
    )
    .map_err(|e| e.to_string())?;
    
    Ok(conn.last_insert_rowid())
}

// READ - Get single user
#[tauri::command]
fn get_user(state: State<DbState>, id: i64) -> Result<User, String> {
    let conn = state.conn.lock().unwrap();
    
    let mut stmt = conn
        .prepare("SELECT id, name, email, created_at FROM users WHERE id = ?1")
        .map_err(|e| e.to_string())?;
    
    let user = stmt
        .query_row(params![id], |row| {
            Ok(User {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        })
        .map_err(|e| e.to_string())?;
    
    Ok(user)
}

// READ - Get all users
#[tauri::command]
fn get_all_users(state: State<DbState>) -> Result<Vec<User>, String> {
    let conn = state.conn.lock().unwrap();
    
    let mut stmt = conn
        .prepare("SELECT id, name, email, created_at FROM users ORDER BY created_at DESC")
        .map_err(|e| e.to_string())?;
    
    let users = stmt
        .query_map([], |row| {
            Ok(User {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    Ok(users)
}

// READ - Search users
#[tauri::command]
fn search_users(state: State<DbState>, query: String) -> Result<Vec<User>, String> {
    let conn = state.conn.lock().unwrap();
    let search_pattern = format!("%{}%", query);
    
    let mut stmt = conn
        .prepare(
            "SELECT id, name, email, created_at FROM users 
             WHERE name LIKE ?1 OR email LIKE ?1 
             ORDER BY name"
        )
        .map_err(|e| e.to_string())?;
    
    let users = stmt
        .query_map(params![search_pattern], |row| {
            Ok(User {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    Ok(users)
}

// UPDATE
#[tauri::command]
fn update_user(
    state: State<DbState>,
    id: i64,
    name: String,
    email: String
) -> Result<(), String> {
    let conn = state.conn.lock().unwrap();
    
    let updated = conn
        .execute(
            "UPDATE users SET name = ?1, email = ?2 WHERE id = ?3",
            params![name, email, id],
        )
        .map_err(|e| e.to_string())?;
    
    if updated == 0 {
        return Err("User not found".to_string());
    }
    
    Ok(())
}

// DELETE
#[tauri::command]
fn delete_user(state: State<DbState>, id: i64) -> Result<(), String> {
    let conn = state.conn.lock().unwrap();
    
    let deleted = conn
        .execute("DELETE FROM users WHERE id = ?1", params![id])
        .map_err(|e| e.to_string())?;
    
    if deleted == 0 {
        return Err("User not found".to_string());
    }
    
    Ok(())
}

// Frontend: Using database commands
import { invoke } from "@tauri-apps/api/core";

interface User {
  id?: number;
  name: string;
  email: string;
  created_at: string;
}

// Create user
const userId = await invoke<number>("create_user", {
  name: "John Doe",
  email: "[email protected]",
});

// Get user
const user = await invoke<User>("get_user", { id: userId });

// Get all users
const users = await invoke<User[]>("get_all_users");

// Search users
const results = await invoke<User[]>("search_users", {
  query: "john",
});

// Update user
await invoke("update_user", {
  id: userId,
  name: "John Smith",
  email: "[email protected]",
});

// Delete user
await invoke("delete_user", { id: userId });

Transactions and Data Consistency

rusttransactions.rs

// Rust: Transactions
use rusqlite::{params, Connection, Transaction};

// Atomic operation with transaction
#[tauri::command]
fn create_user_with_posts(
    state: State<DbState>,
    name: String,
    email: String,
    post_titles: Vec<String>
) -> Result<i64, String> {
    let mut conn = state.conn.lock().unwrap();
    let tx = conn.transaction().map_err(|e| e.to_string())?;
    
    // Insert user
    let created_at = chrono::Utc::now().to_rfc3339();
    tx.execute(
        "INSERT INTO users (name, email, created_at) VALUES (?1, ?2, ?3)",
        params![name, email, created_at],
    )
    .map_err(|e| e.to_string())?;
    
    let user_id = tx.last_insert_rowid();
    
    // Insert posts
    for title in post_titles {
        tx.execute(
            "INSERT INTO posts (user_id, title, content, created_at) 
             VALUES (?1, ?2, ?3, ?4)",
            params![user_id, title, "", created_at],
        )
        .map_err(|e| e.to_string())?;
    }
    
    // Commit transaction
    tx.commit().map_err(|e| e.to_string())?;
    
    Ok(user_id)
}

// Transfer data between tables
#[tauri::command]
fn archive_old_posts(state: State<DbState>, days: i64) -> Result<usize, String> {
    let mut conn = state.conn.lock().unwrap();
    let tx = conn.transaction().map_err(|e| e.to_string())?;
    
    // Create archive table if not exists
    tx.execute(
        "CREATE TABLE IF NOT EXISTS archived_posts (
            id INTEGER PRIMARY KEY,
            user_id INTEGER,
            title TEXT,
            content TEXT,
            created_at TEXT,
            archived_at TEXT
        )",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    let cutoff_date = chrono::Utc::now() - chrono::Duration::days(days);
    let cutoff = cutoff_date.to_rfc3339();
    let archived_at = chrono::Utc::now().to_rfc3339();
    
    // Move old posts to archive
    let moved = tx.execute(
        "INSERT INTO archived_posts (id, user_id, title, content, created_at, archived_at)
         SELECT id, user_id, title, content, created_at, ?1
         FROM posts
         WHERE created_at < ?2",
        params![archived_at, cutoff],
    )
    .map_err(|e| e.to_string())?;
    
    // Delete from posts table
    tx.execute(
        "DELETE FROM posts WHERE created_at < ?1",
        params![cutoff],
    )
    .map_err(|e| e.to_string())?;
    
    tx.commit().map_err(|e| e.to_string())?;
    
    Ok(moved)
}

// Batch insert with transaction
#[tauri::command]
fn batch_insert_users(
    state: State<DbState>,
    users: Vec<User>
) -> Result<usize, String> {
    let mut conn = state.conn.lock().unwrap();
    let tx = conn.transaction().map_err(|e| e.to_string())?;
    
    let mut inserted = 0;
    let created_at = chrono::Utc::now().to_rfc3339();
    
    for user in users {
        tx.execute(
            "INSERT INTO users (name, email, created_at) VALUES (?1, ?2, ?3)",
            params![user.name, user.email, created_at],
        )
        .map_err(|e| e.to_string())?;
        inserted += 1;
    }
    
    tx.commit().map_err(|e| e.to_string())?;
    
    Ok(inserted)
}

Query Optimization and Indexing

rustquery_optimization.rs

// Rust: Query optimization

// Create indexes for performance
#[tauri::command]
fn create_indexes(state: State<DbState>) -> Result<(), String> {
    let conn = state.conn.lock().unwrap();
    
    // Index for user email lookups
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    // Index for post searches
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_posts_title ON posts(title)",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    // Composite index for filtering and sorting
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_posts_user_created 
         ON posts(user_id, created_at DESC)",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    Ok(())
}

// Pagination for large result sets
#[tauri::command]
fn get_users_paginated(
    state: State<DbState>,
    page: i64,
    page_size: i64
) -> Result<Vec<User>, String> {
    let conn = state.conn.lock().unwrap();
    let offset = page * page_size;
    
    let mut stmt = conn
        .prepare(
            "SELECT id, name, email, created_at 
             FROM users 
             ORDER BY created_at DESC 
             LIMIT ?1 OFFSET ?2"
        )
        .map_err(|e| e.to_string())?;
    
    let users = stmt
        .query_map(params![page_size, offset], |row| {
            Ok(User {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    Ok(users)
}

// Count total records for pagination
#[tauri::command]
fn count_users(state: State<DbState>) -> Result<i64, String> {
    let conn = state.conn.lock().unwrap();
    
    let count: i64 = conn
        .query_row("SELECT COUNT(*) FROM users", [], |row| row.get(0))
        .map_err(|e| e.to_string())?;
    
    Ok(count)
}

// Full-text search
#[tauri::command]
fn setup_fulltext_search(state: State<DbState>) -> Result<(), String> {
    let conn = state.conn.lock().unwrap();
    
    // Create FTS5 virtual table
    conn.execute(
        "CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts 
         USING fts5(title, content, content=posts, content_rowid=id)",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    // Populate FTS table
    conn.execute(
        "INSERT INTO posts_fts(rowid, title, content) 
         SELECT id, title, content FROM posts",
        [],
    )
    .map_err(|e| e.to_string())?;
    
    Ok(())
}

#[tauri::command]
fn fulltext_search(
    state: State<DbState>,
    query: String
) -> Result<Vec<Post>, String> {
    let conn = state.conn.lock().unwrap();
    
    let mut stmt = conn
        .prepare(
            "SELECT p.id, p.user_id, p.title, p.content, p.created_at
             FROM posts p
             INNER JOIN posts_fts ON posts_fts.rowid = p.id
             WHERE posts_fts MATCH ?1
             ORDER BY rank"
        )
        .map_err(|e| e.to_string())?;
    
    let posts = stmt
        .query_map(params![query], |row| {
            Ok(Post {
                id: Some(row.get(0)?),
                user_id: row.get(1)?,
                title: row.get(2)?,
                content: row.get(3)?,
                created_at: row.get(4)?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    Ok(posts)
}

Database Best Practices

Use Prepared Statements: Always use parameter binding preventing SQL injection attacks
Implement Transactions: Use transactions for multi-step operations ensuring data consistency
Create Indexes: Add indexes on frequently queried columns improving performance
Handle Errors: Properly handle database errors providing meaningful messages
Use Foreign Keys: Enable foreign key constraints maintaining referential integrity
Implement Pagination: Use LIMIT/OFFSET for large result sets preventing memory issues
Regular Backups: Implement database backup functionality preventing data loss
Schema Migrations: Version database schema enabling smooth updates
Connection Pooling: Reuse database connections improving performance
Optimize Queries: Use EXPLAIN QUERY PLAN identifying slow queries

Performance Tip: SQLite performs best with indexes on frequently queried columns, proper use of transactions for batch operations, and avoiding SELECT * in favor of specific column selection. Use EXPLAIN QUERY PLAN to analyze and optimize slow queries.

Real-World Example: Note-Taking App

rustnotes_app.rs

// Complete note-taking application with SQLite
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
use tauri::State;

#[derive(Debug, Serialize, Deserialize)]
struct Note {
    id: Option<i64>,
    title: String,
    content: String,
    tags: Vec<String>,
    created_at: String,
    updated_at: String,
}

// Initialize notes database
fn init_notes_db(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute(
        "CREATE TABLE IF NOT EXISTS notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )",
        [],
    )?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL
        )",
        [],
    )?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS note_tags (
            note_id INTEGER NOT NULL,
            tag_id INTEGER NOT NULL,
            PRIMARY KEY (note_id, tag_id),
            FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
        )",
        [],
    )?;
    
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_notes_updated ON notes(updated_at DESC)",
        [],
    )?;
    
    conn.execute(
        "CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts 
         USING fts5(title, content, content=notes, content_rowid=id)",
        [],
    )?;
    
    Ok(())
}

// Create note with tags
#[tauri::command]
fn create_note(
    state: State<DbState>,
    title: String,
    content: String,
    tags: Vec<String>
) -> Result<i64, String> {
    let mut conn = state.conn.lock().unwrap();
    let tx = conn.transaction().map_err(|e| e.to_string())?;
    
    let now = chrono::Utc::now().to_rfc3339();
    
    // Insert note
    tx.execute(
        "INSERT INTO notes (title, content, created_at, updated_at) 
         VALUES (?1, ?2, ?3, ?3)",
        params![title, content, now],
    )
    .map_err(|e| e.to_string())?;
    
    let note_id = tx.last_insert_rowid();
    
    // Insert into FTS
    tx.execute(
        "INSERT INTO notes_fts(rowid, title, content) VALUES (?1, ?2, ?3)",
        params![note_id, title, content],
    )
    .map_err(|e| e.to_string())?;
    
    // Insert tags
    for tag in tags {
        // Insert tag if doesn't exist
        tx.execute(
            "INSERT OR IGNORE INTO tags (name) VALUES (?1)",
            params![tag],
        )
        .map_err(|e| e.to_string())?;
        
        // Get tag ID
        let tag_id: i64 = tx
            .query_row(
                "SELECT id FROM tags WHERE name = ?1",
                params![tag],
                |row| row.get(0),
            )
            .map_err(|e| e.to_string())?;
        
        // Link note to tag
        tx.execute(
            "INSERT INTO note_tags (note_id, tag_id) VALUES (?1, ?2)",
            params![note_id, tag_id],
        )
        .map_err(|e| e.to_string())?;
    }
    
    tx.commit().map_err(|e| e.to_string())?;
    
    Ok(note_id)
}

// Get note with tags
#[tauri::command]
fn get_note(state: State<DbState>, id: i64) -> Result<Note, String> {
    let conn = state.conn.lock().unwrap();
    
    // Get note
    let mut stmt = conn
        .prepare(
            "SELECT id, title, content, created_at, updated_at 
             FROM notes WHERE id = ?1"
        )
        .map_err(|e| e.to_string())?;
    
    let mut note = stmt
        .query_row(params![id], |row| {
            Ok(Note {
                id: Some(row.get(0)?),
                title: row.get(1)?,
                content: row.get(2)?,
                tags: Vec::new(),
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            })
        })
        .map_err(|e| e.to_string())?;
    
    // Get tags
    let mut stmt = conn
        .prepare(
            "SELECT t.name FROM tags t
             INNER JOIN note_tags nt ON t.id = nt.tag_id
             WHERE nt.note_id = ?1"
        )
        .map_err(|e| e.to_string())?;
    
    let tags = stmt
        .query_map(params![id], |row| row.get::<_, String>(0))
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    note.tags = tags;
    
    Ok(note)
}

// Search notes
#[tauri::command]
fn search_notes(state: State<DbState>, query: String) -> Result<Vec<Note>, String> {
    let conn = state.conn.lock().unwrap();
    
    let mut stmt = conn
        .prepare(
            "SELECT DISTINCT n.id, n.title, n.content, n.created_at, n.updated_at
             FROM notes n
             LEFT JOIN notes_fts ON notes_fts.rowid = n.id
             LEFT JOIN note_tags nt ON n.id = nt.note_id
             LEFT JOIN tags t ON nt.tag_id = t.id
             WHERE notes_fts MATCH ?1 OR t.name LIKE ?2
             ORDER BY n.updated_at DESC"
        )
        .map_err(|e| e.to_string())?;
    
    let search_pattern = format!("%{}%", query);
    
    let mut notes = stmt
        .query_map(params![query, search_pattern], |row| {
            Ok(Note {
                id: Some(row.get(0)?),
                title: row.get(1)?,
                content: row.get(2)?,
                tags: Vec::new(),
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            })
        })
        .map_err(|e| e.to_string())?
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| e.to_string())?;
    
    // Get tags for each note
    for note in &mut notes {
        if let Some(id) = note.id {
            let tags = get_note_tags(&conn, id).map_err(|e| e.to_string())?;
            note.tags = tags;
        }
    }
    
    Ok(notes)
}

fn get_note_tags(conn: &Connection, note_id: i64) -> rusqlite::Result<Vec<String>> {
    let mut stmt = conn.prepare(
        "SELECT t.name FROM tags t
         INNER JOIN note_tags nt ON t.id = nt.tag_id
         WHERE nt.note_id = ?1"
    )?;
    
    stmt.query_map(params![note_id], |row| row.get(0))?
        .collect()
}

Next Steps

File System: Backup database with file operations
HTTP Client: Sync data with API requests
State Management: Cache queries with state management
Commands: Expose database operations via commands
Security: Encrypt sensitive data with security practices

Conclusion

Mastering SQLite integration in Tauri 2.0 enables building sophisticated desktop applications with robust local data storage, complex queries, transactions ensuring consistency, full-text search, and offline-first capabilities syncing with remote servers when available creating professional applications handling structured data reliably throughout operation. SQLite provides zero-configuration serverless database perfect for desktop applications requiring no setup, no external dependencies, self-contained single-file database, ACID transactions, and cross-platform compatibility maintaining data integrity with comprehensive SQL support. Understanding database patterns including prepared statements preventing SQL injection, transaction usage for atomic operations, index creation for query performance, schema evolution with migrations, connection management, and backup strategies establishes foundation for professional desktop application development creating reliable data persistence serving diverse use cases from note-taking apps to task managers to data synchronization systems. Your Tauri applications now possess powerful database capabilities enabling features like local storage, complex queries, full-text search, data relationships, offline operation, and cloud sync delivering professional desktop experiences with reliable data management!