Here's How to Add SQLite to an Existing Rust Project

Upgrading a flat-file JSON or in-memory application to a persistent relational database is a standard rite of passage for systems developers. This chapter explores how to take an existing command-line password vault written in Rust and integrate rusqlite—a lightweight, ergonomic wrapper for SQLite—to store credentials safely and query them efficiently.

1. Why Do We Need This? (The Limitations of Flat-File Storage)

In the initial implementation of the Rust password vault, entries were typically serialized and stored within a local JSON file or simple data structures. While functional for micro-projects, flat-file storage carries severe limitations:

Concurrency Bottlenecks: Writing whole files concurrently leads to race conditions and file corruption.
Inefficient Searching: Every lookup requires scanning the entire file from disk and deserializing all records into memory (O(N) scan complexity).
Lack of Scalability: As the data grows, memory consumption spikes because the entire data set must be parsed into vectors.
Missing Atomicity: Transactions are non-existent; if a write fails halfway, the state becomes corrupted.

SQLite solves these problems by providing a zero-configuration, self-contained, transactional SQL database engine running locally in a single file (passwords.db).

2. Core Mental Model & Library Architecture

Unlike heavy ORMs (Object-Relational Mappers) in languages like Java or TypeScript that abstract away SQL entirely, rusqlite is a lightweight, low-level binding. It does not generate SQL queries for you; you write raw SQL statements and pass them directly to the database connection.

Real-Life Analogy: The Local Ledger

Think of rusqlite as a pocket notebook and pen handed to you by SQLite. The library doesn't organize your thoughts or manage your filing system; it simply provides the direct conduit to write ink onto paper (passwords.db) and flip through pages when searching for a specific name.

CODE

Rust Application Code
       |
       | passes raw SQL queries via rusqlite
       v
rusqlite C-bindings
       |
       | reads/writes bytes
       v
Local SQLite File (passwords.db)

3. Project Initialization and Dependency Setup

To get started, create a new Cargo package via the terminal and open it in your code editor:

BASH

cargo new rust-sqlite-vault-yt
cd rust-sqlite-vault-yt
code .

Configuring `Cargo.toml`

Add the necessary external crates. Along with serialization dependencies (serde), you must include rusqlite version 0.25 to enable SQLite bindings.

TOML

[package]
name = "rust-sqlite-vault-yt"
version = "0.1.0"
edition = "2021"

[dependencies]
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
rusqlite = { version = "0.25", features = ["bundled"] }

Note: The bundled feature flag on rusqlite ensures that SQLite is compiled directly from source alongside your Rust binary, eliminating external system dependency headaches.

4. Structuring the Database Module (`db.rs`)

To maintain clean separation of concerns, extract all database-related operations into a dedicated module file named db.rs.

Importing Dependencies

In src/db.rs, import standard I/O libraries, SQLite connection structures, and serialization traits:

RUST

use std::io::{self, Write};
use rusqlite::{Connection, Result, OptionalExtension};
use serde::{Serialize, Deserialize};

Defining the `ServiceInfo` Struct

The data model represents an individual password entry, complete with an optional database ID (i64) and string fields for service name, username, and password:

RUST

#[derive(Serialize, Deserialize, Debug)]
pub struct ServiceInfo {
    pub id: Option<i64>,
    pub service: String,
    pub username: String,
    pub password: String,
}

impl ServiceInfo {
    pub fn new(service: String, username: String, password: String) -> Self {
        ServiceInfo {
            id: None,
            service,
            username,
            password,
        }
    }

    pub fn prompt() -> Self {
        print!("Enter service name: ");
        io::stdout().flush().unwrap();
        let mut service = String::new();
        io::stdin().read_line(&mut service).unwrap();

        print!("Enter username: ");
        io::stdout().flush().unwrap();
        let mut username = String::new();
        io::stdin().read_line(&mut username).unwrap();

        print!("Enter password: ");
        io::stdout().flush().unwrap();
        let mut password = String::new();
        io::stdin().read_line(&mut password).unwrap();

        ServiceInfo::new(
            service.trim().to_string(),
            username.trim().to_string(),
            password.trim().to_string(),
        )
    }
}

5. Implementing Database Initialization & Table Creation

The application needs an initialization function that opens a connection to passwords.db (creating the file automatically in your project root if it does not exist) and sets up the relational schema.

RUST

pub fn init_database() -> Result<Connection> {
    let conn = Connection::open("passwords.db")?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS passwords (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            service TEXT NOT NULL,
            username TEXT NOT NULL,
            password TEXT NOT NULL
        )",
        [],
    )?;

    Ok(conn)
}

6. Implementing Core CRUD Operations

With the database and table initialized, implement the functions for writing, reading, and searching records.

Writing a Password Entry (`write_password_to_db`)

This function accepts an active database connection and the credential fields, executing an SQL INSERT statement with parameterized bindings (?1, ?2, ?3) to prevent SQL injection vulnerabilities.

RUST

pub fn write_password_to_db(
    conn: &Connection, 
    service: &str, 
    username: &str, 
    password: &str
) -> Result<()> {
    conn.execute(
        "INSERT INTO passwords (service, username, password) VALUES (?1, ?2, ?3)",
        &[&service, &username, &password],
    )?;
    Ok(())
}

Reading All Passwords (`read_passwords_from_database`)

To list all stored credentials, prepare a SELECT statement, map rows into ServiceInfo instances, and collect them into a vector.

RUST

pub fn read_passwords_from_database(conn: &Connection) -> Result<Vec<ServiceInfo>> {
    let mut stmt = conn.prepare("SELECT id, service, username, password FROM passwords")?;
    
    let password_iter = stmt.query_map([], |row| {
        Ok(ServiceInfo {
            id: Some(row.get(0)?),
            service: row.get(1)?,
            username: row.get(2)?,
            password: row.get(3)?,
        })
    })?;

    let mut entries = Vec::new();
    for entry in password_iter {
        entries.push(entry?);
    }

    Ok(entries)
}

Searching by Service Name (`search_service_by_name`)

Instead of scanning vectors in memory, let SQLite filter records using a WHERE clause:

RUST

pub fn search_service_by_name(conn: &Connection, search_term: &str) -> Result<Option<ServiceInfo>> {
    let mut stmt = conn.prepare(
        "SELECT id, service, username, password FROM passwords WHERE service = ?1"
    )?;

    let mut entry_iter = stmt.query_map(&[&search_term], |row| {
        Ok(ServiceInfo {
            id: Some(row.get(0)?),
            service: row.get(1)?,
            username: row.get(2)?,
            password: row.get(3)?,
        })
    })?;

    if let Some(entry) = entry_iter.next() {
        Ok(Some(entry?))
    } else {
        Ok(None)
    }
}

7. Connecting the UI Loop (`main.rs`)

Now tie the database module together inside main.rs, managing user input choices via a command-line menu loop.

RUST

mod db;

use std::io::{self, Write};
use db::{ServiceInfo, init_database, write_password_to_db, read_passwords_from_database, search_service_by_name};

fn main() {
    let conn = init_database().expect("Failed to initialize SQLite database");

    loop {
        println!("\n=== PASSBOLT (SQLite Edition) ===");
        println!("1. Add Entry");
        println!("2. List Entries");
        println!("3. Search Entry");
        println!("4. Quit");
        print!("Enter your choice: ");
        io::stdout().flush().unwrap();

        let mut choice = String::new();
        io::stdin().read_line(&mut choice).unwrap();
        let choice = choice.trim();

        match choice {
            "1" => {
                let entry = ServiceInfo::prompt();
                if let Err(e) = write_password_to_db(&conn, &entry.service, &entry.username, &entry.password) {
                    println!("Error saving to database: {}", e);
                } else {
                    println!("Password saved successfully!");
                }
            }
            "2" => {
                match read_passwords_from_database(&conn) {
                    Ok(entries) => {
                        println!("\n--- Stored Entries ---");
                        for entry in entries {
                            println!("Service: {} | Username: {} | Password: {}", entry.service, entry.username, entry.password);
                        }
                    }
                    Err(e) => println!("Error reading database: {}", e),
                }
            }
            "3" => {
                print!("Enter service name to search: ");
                io::stdout().flush().unwrap();
                let mut search_term = String::new();
                io::stdin().read_line(&mut search_term).unwrap();
                let search_term = search_term.trim();

                match search_service_by_name(&conn, search_term) {
                    Ok(Some(entry)) => {
                        println!("\n[Found Match]");
                        println!("Service: {}", entry.service);
                        println!("Username: {}", entry.username);
                        println!("Password: {}", entry.password);
                    }
                    Ok(None) => println!("Service not found."),
                    Err(_) => println!("Error searching for services."),
                }
            }
            "4" => {
                println!("Exiting Passbolt. Goodbye!");
                break;
            }
            _ => println!("Invalid choice. Please enter a number between 1 and 4."),
        }
    }
}

8. Summary and Key Takeaways

Database Persistence: Moving from ephemeral files or memory structures to SQLite (passwords.db) ensures records survive application restarts safely.
Lightweight Bindings: rusqlite provides a low-overhead, high-performance C-binding wrapper that requires developers to write explicit SQL queries rather than heavy ORM boilerplate.
Bundled Compilations: Using the bundled feature flag in Cargo.toml embeds SQLite directly into the compiled executable, eliminating external dependency requirements on host machines.
Parameterized Queries: Protecting against SQL injection is achieved by passing parameters as slices (&[&service, &username, &password]) instead of string-formatting raw inputs.
Relational Efficiency: Filtering operations like searches are pushed down into SQLite via WHERE clauses, turning O(N) linear file scans into indexed database lookups.
Modular Code Design: Separating data definitions and database interactions into dedicated modules (db.rs) keeps the main application entry point clean and maintainable.

Here's How to Add SQLite to an existing Rust Project

Here's How to Add SQLite to an Existing Rust Project

1. Why Do We Need This? (The Limitations of Flat-File Storage)

2. Core Mental Model & Library Architecture

Real-Life Analogy: The Local Ledger

3. Project Initialization and Dependency Setup

Configuring Cargo.toml

4. Structuring the Database Module (db.rs)

Importing Dependencies

Defining the ServiceInfo Struct

5. Implementing Database Initialization & Table Creation

6. Implementing Core CRUD Operations

Writing a Password Entry (write_password_to_db)

Reading All Passwords (read_passwords_from_database)

Searching by Service Name (search_service_by_name)

7. Connecting the UI Loop (main.rs)

8. Summary and Key Takeaways

Configuring `Cargo.toml`

4. Structuring the Database Module (`db.rs`)

Defining the `ServiceInfo` Struct

Writing a Password Entry (`write_password_to_db`)

Reading All Passwords (`read_passwords_from_database`)

Searching by Service Name (`search_service_by_name`)

7. Connecting the UI Loop (`main.rs`)