Connecting to SQLite in Rust is ridiculously easy (and you get “real database” power)

SQLite is not a separate server you install and manage—it's an in-process, zero-configuration, transactional SQL engine that lives inside your application. That’s why it’s perfect for desktop apps, CLI tools, embedded utilities, caches, local storage, and even many backend workloads.

In Rust, you typically pick one of two excellent paths:

• rusqlite: straightforward, synchronous, ergonomic wrapper—great for CLI/desktop/tools.

• sqlx: async-first toolkit with compile-time checked SQL (when using its macros), supports SQLite too—great for async services.

Below is an article-style guide with extensive, copy/paste-ready examples.

Option A: rusqlite (sync) — fastest path to “it works”

1) Add dependency

Cargo.toml

[dependencies]
rusqlite = { version = "0.38", features = ["bundled"] }
anyhow = "1"

Why bundled? On many setups (especially Windows), this makes life easier by building/using a bundled SQLite instead of relying on a system install—ideal for shipping a single binary. (rusqlite documents many feature flags and common usage via docs.rs.)

2) Create a DB, a table, and insert data

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    // Creates file if it doesn't exist.
    let conn = Connection::open("app.db")?;

    conn.execute_batch(
        r#"
        PRAGMA foreign_keys = ON;

        CREATE TABLE IF NOT EXISTS users (
            id      INTEGER PRIMARY KEY,
            name    TEXT NOT NULL,
            email   TEXT NOT NULL UNIQUE,
            active  INTEGER NOT NULL DEFAULT 1
        );
        "#,
    )?;

    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["Ayman", "ayman@example.com"],
    )?;

    println!("Inserted user.");
    Ok(())
}

What you just got:

• a persistent database file (app.db)

• schema creation

• parameter binding (safe against SQL injection for values)

• a real transactional DB engine under the hood

3) Query rows into Rust structs

use rusqlite::{params, Connection, Result};

#[derive(Debug)]
struct User {
    id: i64,
    name: String,
    email: String,
    active: bool,
}

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;

    let mut stmt = conn.prepare(
        "SELECT id, name, email, active FROM users WHERE active = ?1 ORDER BY id DESC"
    )?;

    let users_iter = stmt.query_map(params![1], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            active: row.get::<_, i64>(3)? != 0, // SQLite stores booleans commonly as integers
        })
    })?;

    for u in users_iter {
        println!("{:?}", u?);
    }

    Ok(())
}

4) Updates, deletes, and “rows affected”

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;

    let changed = conn.execute(
        "UPDATE users SET active = 0 WHERE email = ?1",
        params!["ayman@example.com"],
    )?;
    println!("Deactivated {changed} user(s).");

    let deleted = conn.execute(
        "DELETE FROM users WHERE active = 0",
        [],
    )?;
    println!("Deleted {deleted} user(s).");

    Ok(())
}

5) Transactions (the “professional” part that stays simple)

If you have multiple operations that must succeed together:

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;
    let tx = conn.transaction()?;

    tx.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["User1", "u1@example.com"],
    )?;

    tx.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["User2", "u2@example.com"],
    )?;

    // If any statement fails, you can simply not commit and it rolls back on drop.
    tx.commit()?;
    println!("Transaction committed.");
    Ok(())
}

6) Returning IDs (last_insert_rowid)

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;

    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["NewUser", "new@example.com"],
    )?;

    let id = conn.last_insert_rowid();
    println!("Inserted id = {id}");
    Ok(())
}

Option B: sqlx (async) — when you want async + compile-time checked SQL

sqlx is an async SQL toolkit that supports SQLite and is known for its compile-time checked queries (using macros) without forcing an ORM/DSL.

1) Add dependencies

Cargo.toml

[dependencies]
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }
sqlx = { version = "0.8", features = ["sqlite", "runtime-tokio"] }
anyhow = "1"

2) Async connect + schema + CRUD (runtime queries)

use anyhow::Result;
use sqlx::{sqlite::SqlitePoolOptions, Row};

#[tokio::main]
async fn main() -> Result<()> {
    let db_url = "sqlite://app_async.db";
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect(db_url)
        .await?;

    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        )
        "#
    )
    .execute(&pool)
    .await?;

    sqlx::query("INSERT INTO users (name, email) VALUES (?1, ?2)")
        .bind("Ayman")
        .bind("ayman@example.com")
        .execute(&pool)
        .await?;

    let rows = sqlx::query("SELECT id, name, email FROM users ORDER BY id DESC")
        .fetch_all(&pool)
        .await?;

    for r in rows {
        let id: i64 = r.get("id");
        let name: String = r.get("name");
        let email: String = r.get("email");
        println!("{id} | {name} | {email}");
    }

    Ok(())
}

3) Stronger typing with a struct (recommended)

use anyhow::Result;
use sqlx::{FromRow, sqlite::SqlitePoolOptions};

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    email: String,
}

#[tokio::main]
async fn main() -> Result<()> {
    let pool = SqlitePoolOptions::new()
        .connect("sqlite://app_async.db")
        .await?;

    let users: Vec<User> = sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
        .fetch_all(&pool)
        .await?;

    for u in users {
        println!("{u:?}");
    }
    Ok(())
}

4) Transactions (async)

use anyhow::Result;
use sqlx::{sqlite::SqlitePoolOptions};

#[tokio::main]
async fn main() -> Result<()> {
    let pool = SqlitePoolOptions::new()
        .connect("sqlite://app_async.db")
        .await?;

    let mut tx = pool.begin().await?;

    sqlx::query("INSERT INTO users (name, email) VALUES (?1, ?2)")
        .bind("U1")
        .bind("u1@example.com")
        .execute(&mut *tx)
        .await?;

    sqlx::query("INSERT INTO users (name, email) VALUES (?1, ?2)")
        .bind("U2")
        .bind("u2@example.com")
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

Which one should you choose?

Choose rusqlite when:

• You’re writing a CLI tool, desktop app, automation utility, or anything mostly single-threaded/synchronous.

• You want the simplest mental model and minimal dependencies.

Choose sqlx when:

• You already run an async runtime (Tokio/Actix/etc.).

• You want async pooling and the option of compile-time SQL checks.

• Your project may later switch from SQLite to Postgres/MySQL with fewer conceptual changes.

Practical tips that save real time

• Use parameters for values (?1, ?2, .bind(...)) to avoid SQL injection for data values.

• Keep schema creation in execute_batch (rusqlite) or a startup migration step (sqlx).

• SQLite concurrency reality: it’s amazing, but it’s still a single-file DB; design your write patterns thoughtfully (transactions, batching). SQLite is built as an in-process engine and is meant to be embedded.