Local SQLite

The go-to store for local data — no server, no network, one file.

1. Install

pnpm tauri add sql

Auto-registers deps and capabilities.

2. Migrations

use tauri_plugin_sql::{Builder, Migration, MigrationKind};

pub fn run() {
    let migrations = vec![
        Migration {
            version: 1,
            description: "create_foods",
            sql: "CREATE TABLE foods (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                rating TEXT NOT NULL,
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );",
            kind: MigrationKind::Up,
        },
    ];
    tauri::Builder::default()
        .plugin(Builder::default().add_migrations("sqlite:app.db", migrations).build())
        .run(tauri::generate_context!()).unwrap();
}

3. Frontend queries

import Database from "@tauri-apps/plugin-sql";

const db = await Database.load("sqlite:app.db");

await db.execute(
  "INSERT INTO foods (name, rating) VALUES ($1, $2)",
  ["Kimchi stew", "loved"]
);

const rows = await db.select<Food[]>(
  "SELECT * FROM foods WHERE rating = $1 ORDER BY created_at DESC LIMIT $2",
  ["loved", 20]
);

Use $1, $2 placeholders. Never string concatenation.

4. Storage location

• macOS ~/Library/Application Support/<identifier>/app.db
• Windows %APPDATA%\<identifier>\app.db
• Linux ~/.local/share/<identifier>/app.db
• Android app internal storage

5. Wrap SQL

let _db: Database | null = null;
export async function getDb() { if (!_db) _db = await Database.load("sqlite:app.db"); return _db; }

export async function addFood(name: string, rating: string) {
  const db = await getDb();
  await db.execute("INSERT INTO foods (name, rating) VALUES ($1, $2)", [name, rating]);
}

Components call addFood, not raw SQL.

6. Export

import { save } from "@tauri-apps/plugin-dialog";
import { writeTextFile } from "@tauri-apps/plugin-fs";

const rows = await db.select<Food[]>("SELECT * FROM foods");
const path = await save({ filters: [{ name: "JSON", extensions: ["json"] }] });
if (path) await writeTextFile(path, JSON.stringify(rows, null, 2));

Always give users an escape hatch for their data.

7. Gotchas

• Concatenated SQL → injection still applies
• Placeholder style $1, not ?
• Hardcoded paths on Android; keep relative sqlite:app.db
• Skipped migration versions

Closing

Local SQLite alone is enough for apps like Matgilog or ReadingBounce. No server means no long-term server bill.

Next

• 05-android-build