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

Same plugin as the contacts manager, longer-form content, full CRUD with edit-in-place. The pattern that scales from notes to a real CMS.

The previous lesson built a contacts manager — short text fields, simple list. Today: an article manager. Title, body (multi-line markdown), tags. The same plugin, but a richer schema and a slightly more polished UI.

What we are building

A two-pane article manager:

• Left: a list of articles with titles and a "New" button.
• Right: an editor for the selected article — title input, body textarea, tags input, Save and Delete buttons.

Articles persist to SQLite via tauri-plugin-sql.

Schema

let migrations = vec![
  Migration {
    version: 1,
    description: "create articles table",
    sql: "CREATE TABLE IF NOT EXISTS articles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      body TEXT NOT NULL DEFAULT '',
      tags TEXT NOT NULL DEFAULT '',
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    );",
    kind: MigrationKind::Up,
  },
];

Five columns. tags is a comma-separated string for simplicity. For richer tag handling (joins, autocomplete) you would split into a tags table and a articles_tags join table — common but more code.

TypeScript model

interface Article {
  id: number;
  title: string;
  body: string;
  tags: string;
  created_at: string;
  updated_at: string;
}

Mirror the schema. Use this everywhere — reads from SQL, function signatures, React props.

Loading the list

const [articles, setArticles] = useState<Article[]>([]);
const [selectedId, setSelectedId] = useState<number | null>(null);

async function refresh() {
  const rows = await db!.select<Article[]>(
    "SELECT * FROM articles ORDER BY updated_at DESC"
  );
  setArticles(rows);
}

useEffect(() => {
  if (db) refresh();
}, [db]);

Sort by updated_at DESC so the most recently edited article is at the top — a small but valuable UX touch.

Selecting an article

const selected = articles.find(a => a.id === selectedId) ?? null;

The right pane reads selected. Click an article in the left pane → set selectedId.

We don't re-fetch from the DB on selection. The full article is already in articles. Re-fetching would round-trip unnecessarily.

Editor

function ArticleEditor({ article, onSave, onDelete }: {
  article: Article;
  onSave: (id: number, title: string, body: string, tags: string) => void;
  onDelete: (id: number) => void;
}) {
  const [title, setTitle] = useState(article.title);
  const [body, setBody] = useState(article.body);
  const [tags, setTags] = useState(article.tags);

  useEffect(() => {
    setTitle(article.title);
    setBody(article.body);
    setTags(article.tags);
  }, [article.id]);

  return (
    <div className="flex flex-col h-full">
      <input value={title} onChange={(e) => setTitle(e.target.value)} className="text-2xl p-2 border-b" />
      <input value={tags} onChange={(e) => setTags(e.target.value)} placeholder="Tags (comma-separated)" className="p-2 border-b text-sm" />
      <textarea value={body} onChange={(e) => setBody(e.target.value)} className="flex-1 p-2 font-mono" />
      <footer className="flex gap-2 p-2 border-t">
        <button onClick={() => onSave(article.id, title, body, tags)}>Save</button>
        <button onClick={() => onDelete(article.id)}>Delete</button>
      </footer>
    </div>
  );
}

useEffect resets local state when the selected article changes (article.id is the dependency). Otherwise the editor would show the previous article's content while you click around the list.

Save

async function saveArticle(id: number, title: string, body: string, tags: string) {
  await db!.execute(
    "UPDATE articles SET title = $1, body = $2, tags = $3, updated_at = CURRENT_TIMESTAMP WHERE id = $4",
    [title, body, tags, id]
  );
  refresh();
}

Update the row, refresh the list. The article jumps to the top because of the ORDER BY updated_at DESC.

New article

async function newArticle() {
  const result = await db!.execute(
    "INSERT INTO articles (title) VALUES ($1)",
    ["Untitled"]
  );
  setSelectedId(result.lastInsertId);
  refresh();
}

Insert with just a title. lastInsertId gives us the new ID; select it so the editor opens in the right state.

Delete

async function deleteArticle(id: number) {
  if (!(await confirm("Delete this article?"))) return;
  await db!.execute("DELETE FROM articles WHERE id = $1", [id]);
  if (selectedId === id) setSelectedId(null);
  refresh();
}

Confirm first. After delete, clear the selection if we just deleted the selected article.

Putting it together

return (
  <div className="flex h-screen">
    <aside className="w-64 border-r overflow-y-auto">
      <button onClick={newArticle}>+ New Article</button>
      <ul>
        {articles.map(a => (
          <li
            key={a.id}
            onClick={() => setSelectedId(a.id)}
            className={a.id === selectedId ? "bg-blue-100" : ""}
          >
            <h3>{a.title}</h3>
            <p>{a.tags}</p>
          </li>
        ))}
      </ul>
    </aside>
    <main className="flex-1">
      {selected ? (
        <ArticleEditor article={selected} onSave={saveArticle} onDelete={deleteArticle} />
      ) : (
        <div>Select or create an article.</div>
      )}
    </main>
  </div>
);

A two-pane layout. The list is the navigation; the editor is the content. Same shape as countless production apps.

Auto-save

For a more polished feel, auto-save after a debounce:

const debounced = useDebounce({ title, body, tags }, 1000);

useEffect(() => {
  if (selected && (debounced.title !== selected.title || debounced.body !== selected.body || debounced.tags !== selected.tags)) {
    saveArticle(selected.id, debounced.title, debounced.body, debounced.tags);
  }
}, [debounced]);

useDebounce (your own hook or from a library like usehooks-ts) waits 1 second after the user stops typing, then fires. The user never has to think about saving.

Search

const [query, setQuery] = useState("");
const filtered = articles.filter(a =>
  a.title.toLowerCase().includes(query.toLowerCase()) ||
  a.tags.toLowerCase().includes(query.toLowerCase())
);

For more sophisticated search, push it into SQL with FTS5:

CREATE VIRTUAL TABLE articles_fts USING fts5(title, body, tags);

Then maintain it via triggers and query with MATCH. More setup, but vastly faster on large corpora.

Common mistakes

Reading the row again after every state change. Stale-while-revalidate is fine, but unnecessary fetches slow the UI. Use the in-memory list for navigation; query the DB only when something changed.

Not resetting editor state on selection change. The editor shows the previous article's content for one render cycle. Use a useEffect keyed on article.id.

Saving on every keystroke. Hits the DB every character — slow, lossy. Debounce.

Not handling concurrent edits in a multi-window app. If two windows edit the same article, the last save wins. For a multi-window scenario, broadcast change events from Rust and refresh other windows.

What's next

Next lesson: debugging SQLite in Tauri — how to verify your queries actually do what you think, using println! and the SQLite CLI.

Recap

Same plugin, richer schema. CRUD pattern: refresh the list after every mutation. Use lastInsertId to select newly-created rows. Reset editor local state when selection changes. Debounce auto-save. Use MATCH and FTS5 for fast search.

Next: SQLite debugging.