DEV Community

Cover image for Using SQLite to Track Sync State in Rust — Simple, Reliable, Zero Dependencies
hiyoyo
hiyoyo

Posted on

Using SQLite to Track Sync State in Rust — Simple, Reliable, Zero Dependencies

#tauri #rust #sql #programming

All tests run on an 8-year-old MacBook Air.

Sync tools need to remember what they've already transferred. Files change. Transfers get interrupted. The app restarts.

The naive approach — scan everything on every run — gets slow fast. The right approach: track state in SQLite.

The schema

One table. One row per file.

CREATE TABLE IF NOT EXISTS sync_state (
    path        TEXT PRIMARY KEY,
    size        INTEGER NOT NULL,
    modified_at INTEGER NOT NULL,  -- Unix timestamp
    hash        TEXT,              -- SHA-256, computed lazily
    synced_at   INTEGER,           -- when we last synced this file
    status      TEXT NOT NULL DEFAULT 'pending'
                CHECK(status IN ('pending', 'synced', 'failed', 'skipped'))
);

CREATE INDEX idx_status ON sync_state(status);
CREATE INDEX idx_modified ON sync_state(modified_at);
Enter fullscreen mode Exit fullscreen mode

Connecting from Rust

Using rusqlite:

[dependencies]
rusqlite = { version = "0.31", features = ["bundled"] }
Enter fullscreen mode Exit fullscreen mode

bundled compiles SQLite into your binary — no system SQLite dependency, consistent version everywhere.

use rusqlite::{Connection, Result};

pub struct SyncDb {
    conn: Connection,
}

impl SyncDb {
    pub fn open(path: &str) -> Result {
        let conn = Connection::open(path)?;
        conn.execute_batch("
            PRAGMA journal_mode=WAL;
            PRAGMA synchronous=NORMAL;
        ")?;
        Ok(Self { conn })
    }
}
Enter fullscreen mode Exit fullscreen mode

WAL mode enables concurrent reads while writes are in progress — important if your UI is reading sync status while a background thread is writing it.

Detecting what needs syncing 

pub fn get_pending_files(&self) -> Result> {
    let mut stmt = self.conn.prepare(
        "SELECT path, size, modified_at FROM sync_state
         WHERE status = 'pending'
         OR (status = 'synced' AND modified_at > synced_at)
         ORDER BY modified_at DESC"
    )?;

    let entries = stmt.query_map([], |row| {
        Ok(SyncEntry {
            path: row.get(0)?,
            size: row.get(1)?,
            modified_at: row.get(2)?,
        })
    })?.collect::>>()?;

    Ok(entries)
}
Enter fullscreen mode Exit fullscreen mode

Files that are pending or modified after their last sync — those need to transfer.

Updating state after transfer 

pub fn mark_synced(&self, path: &str) -> Result<()> {
    let now = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .unwrap()
        .as_secs();

    self.conn.execute(
        "UPDATE sync_state SET status = 'synced', synced_at = ?1 WHERE path = ?2",
        rusqlite::params![now, path],
    )?;
    Ok(())
}

pub fn mark_failed(&self, path: &str, _reason: &str) -> Result<()> {
    self.conn.execute(
        "UPDATE sync_state SET status = 'failed' WHERE path = ?1",
        rusqlite::params![path],
    )?;
    Ok(())
}
Enter fullscreen mode Exit fullscreen mode

Why not a plain file or JSON?

A plain file works until you have concurrent access, interrupted writes, or need to query "show me everything that failed last run." SQLite handles all of these. It's also faster than parsing JSON for 10,000+ file entries.

For local state tracking, SQLite is almost always the right answer.

Hiyoko PDF Vault → https://hiyokoko.gumroad.com/l/HiyokoPDFVault
X → @hiyoyok

Top comments (0)