Using SQLite Database in Rust with Sqlx

SQLite is self-contained, file based relational database. If you have a simple use case and want to avoid complex databases then SQLite is one of the best choice.

We will use sqlx crate to interact with SQLite database.

Why Sqlx?

Async support
Supports migrations
Compile time type safety
Supports SQLite, Postgres and MySQL

Setup

cargo init sqlite-demo

Cargo.toml

Add the following dependencies to your Cargo.toml file. We are using tokio runtime and sqlite features of sqlx.

[package]
name = "sqlite-demo"
version = "0.1.0"
edition = "2024"

[dependencies]
sqlx = { version = "0.8", features = [
    "runtime-tokio",
    "sqlite",
    "migrate",
] }
tokio = { version = "1.44.2", features = ["full"] }

Connect to SQLite Database

Here we are using SqliteConnectOptions to connect to SQLite database. We are also creating the database file if it doesn’t exist. We are using SqliteJournalMode::Delete to delete the journal file after commit so that we will only have one file in the database directory.

Since we want file based database, we are using sqlite://main.db as connection string. For memory based database, we can use sqlite::memory: as connection string.

use sqlx::{ConnectOptions, Connection, sqlite::SqliteConnectOptions};
use std::str::FromStr;

#[tokio::main]
async fn main() {
    let mut conn = SqliteConnectOptions::from_str("sqlite://main.db")
        .unwrap()
        .create_if_missing(true)
        .journal_mode(sqlx::sqlite::SqliteJournalMode::Delete)
        .connect()
        .await
        .expect("Failed to connect to database");

    // Create the users table if it doesn't exist
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER
        )",
    )
    .execute(&mut conn)
    .await
    .unwrap();

    let _ = conn.close().await;
}

Creating Connection Pool

We will use SqlitePoolOptions to create a connection pool.

We will create a helper function db_conn to create a connection pool. This function will return a connection pool that can be used to interact with the database.

use sqlx::{
    Pool, Sqlite,
    sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::{str::FromStr, time::Duration};

#[tokio::main]
async fn main() {
    let conn = db_conn().await;

    // Create the users table if it doesn't exist
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER
        )",
    )
    .execute(&conn)
    .await
    .unwrap();

    let _ = conn.close().await;
}

async fn db_conn() -> Pool<Sqlite> {
    let db_url = "sqlite://main.db";

    SqlitePoolOptions::new()
        .max_connections(20)
        .idle_timeout(Duration::from_secs(60))
        .acquire_timeout(Duration::from_secs(5))
        .connect_with(
            SqliteConnectOptions::from_str(db_url)
                .unwrap()
                .create_if_missing(true)
                .journal_mode(sqlx::sqlite::SqliteJournalMode::Delete),
        )
        .await
        .unwrap()
}

Using OnceCell to create a connection pool

We can use OnceCell to create a connection pool. This will ensure that the connection pool is created only once and can be shared across multiple threads. So that we won’t to pass connection pool to every function, we can just use this helper function to get the connection pool and it will re-use the same one.

use sqlx::{
    Pool, Sqlite,
    sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::{str::FromStr, time::Duration};
use tokio::sync::OnceCell;

static DB_CONN: OnceCell<Pool<Sqlite>> = OnceCell::const_new();

async fn db_conn() -> Pool<Sqlite> {
    let db_url = "sqlite://main.db";

    DB_CONN
        .get_or_init(|| async {
            SqlitePoolOptions::new()
                .max_connections(20)
                .idle_timeout(Duration::from_secs(60))
                .acquire_timeout(Duration::from_secs(5))
                .connect_with(
                    SqliteConnectOptions::from_str(db_url)
                        .unwrap()
                        .create_if_missing(true)
                        .journal_mode(sqlx::sqlite::SqliteJournalMode::Delete),
                )
                .await
                .unwrap()
        })
        .await
        .clone()
}

Handling Database Migrations

We will use sqlx-cli to handle migrations.

cargo install sqlx-cli

Create migration file for users table.

sqlx migrate add users

This will create a migration file in migrations directory with empty migration file for users table. Add the following content to the newly created migration file.

-- Add migration script here
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);

Embed the migration file in the executable.

use sqlx::{
    Pool, Sqlite,
    sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::{str::FromStr, time::Duration};
use tokio::sync::OnceCell;

#[tokio::main]
async fn main() {
    // this will make sure migrations are added to the executable
    sqlx::migrate!()
        .run(&db_conn().await)
        .await
        .expect("Migrations went wrong :(");
}

static DB_CONN: OnceCell<Pool<Sqlite>> = OnceCell::const_new();

async fn db_conn() -> Pool<Sqlite> {
    let db_url = "sqlite://main.db";

    DB_CONN
        .get_or_init(|| async {
            SqlitePoolOptions::new()
                .max_connections(20)
                .idle_timeout(Duration::from_secs(60))
                .acquire_timeout(Duration::from_secs(5))
                .connect_with(
                    SqliteConnectOptions::from_str(db_url)
                        .unwrap()
                        .create_if_missing(true)
                        .journal_mode(sqlx::sqlite::SqliteJournalMode::Delete),
                )
                .await
                .unwrap()
        })
        .await
        .clone()
}

Full Code with Example Queries

main.rs

use sqlx::{
    Pool, Row, Sqlite,
    prelude::FromRow,
    sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::{str::FromStr, time::Duration};
use tokio::sync::OnceCell;

#[tokio::main]
async fn main() {
    sqlx::migrate!()
        .run(&db_conn().await)
        .await
        .expect("❌ Migrations went wrong :(");

    sqlx::query("INSERT INTO users (name, age) VALUES (?, ?)") // Use ? for placeholders
        .bind("Alice")
        .bind(30)
        .execute(&db_conn().await)
        .await
        .unwrap();

    let query = sqlx::query("SELECT count(*) AS count FROM users")
        .fetch_one(&db_conn().await)
        .await
        .unwrap();

    println!("Count: {:?}", query.get::<i64, &str>("count"));

    let users = sqlx::query_as::<_, User>("SELECT id, name, age FROM users")
        .fetch_all(&db_conn().await)
        .await
        .unwrap();

    for user in users {
        println!("Found user: {:?}", user);
    }
}

static DB_CONN: OnceCell<Pool<Sqlite>> = OnceCell::const_new();

async fn db_conn() -> Pool<Sqlite> {
    let db_url = "sqlite://main.db";

    DB_CONN
        .get_or_init(|| async {
            SqlitePoolOptions::new()
                .max_connections(20)
                .idle_timeout(Duration::from_secs(60))
                .acquire_timeout(Duration::from_secs(5))
                .connect_with(
                    SqliteConnectOptions::from_str(db_url)
                        .unwrap()
                        .create_if_missing(true)
                        .journal_mode(sqlx::sqlite::SqliteJournalMode::Delete),
                )
                .await
                .unwrap()
        })
        .await
        .clone()
}

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    age: Option<i32>,
}