Introduction

Welcome back to our Rust journey! Today, we're going to connect SQLite to a Rust program. This program is a CLI-based password vault! I wrote a detailed article about how to build it here. In this article, I'm going to show you what you should change/add to connect it with SQLite. You can also use this guide to connect pretty much any Rust program to SQLite.

The program we'll build below provides a basic command-line interface (CLI) for managing a password vault, allowing users to add, list, search, and quit operations.

Setup

Our project consists of two main files: main.rs and db.rs. The main.rs file is the heart of our application, handling user input and displaying information. On the other hand, db.rs takes care of the password entry data structure and file operations, it has all the logic for the database.

We also have the Cargo.toml where we included the dependencies:

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

"Rusqlite is an ergonomic wrapper for using SQLite from Rust." - Crates.io

Step-by-step Guide

The main.rs File

Main.rs is very similar to this (from our previous program), only this time you don't need this part:

mod pentry;

use crate::pentry::prompt; // print out stuff and accept values from the user
use crate::pentry::read_passwords_from_file; // print out everything that the file has
use crate::pentry::ServiceInfo;

As we are going to take care of these actions in db.rs.

Let's add/make the connection to the database:

mod db;
use db::*;

fn clr() {
    print!("{}[2J", 27 as char);
}

// it imports the db.rs file,
fn main() {
    // Making a connection to the database
    let conn = init_database().expect("Failed to initialize the database");
    clr();
    .
    .
    .
}

After that, we enter a loop presenting the user with a menu to add entries (1), list entries (2), search for entries (3), or quit the application (4).

On action 'quit the application' (4), we have no changes!

"4" => {
                clr();
                println!("Goodbye!");
                break;
            }
            _ => println!("Invalid choice.")

On action 'add entries' (1), we're going to have a new function write_password_to_db() . We'll write this function to a new file (db.rs), we're going to talk about it a little bit later.

"1" => {
                clr();
                let entry = ServiceInfo::new(
                    prompt("Service :"),
                    prompt("Username :"),
                    prompt("Password :"),
                );
                write_password_to_db(
                    &conn,
                    &entry.service,
                    &entry.username,
                    &entry.password,
                )
                .expect("Failed to write to the database");
                println!("Entry added successfully.");

            }

So, in the write_password_to_db we are going to pass connection (conn) where we created in the main.rs (let conn = init_database().expect("Failed to initialize the database");) and then &entry.service, &entry.username, &entry.password, you access them with entry, as this is where you store them.

On action 'list entries' (2), we're going to have a new function read_passwords_from_db. We'll write this function to our new file (db.rs).

"2" => {
                clr();
                let services = read_passwords_from_db(&conn).unwrap_or_else(|err| {
                    eprintln!("Error reading passwords: {}", err);    
                    Vec::new()
                });
                for item in &services {
                    println!(
                        "Service = {}
                        - Username : {} 
                        - Password : {}",
                        item.service, item.username, item.password
                    );
                }
            }

Everything else remains the same!

On action 'search for entries' (3), we're going to have a new function search_service_by_name(&conn, &search). Yes, you guessed it right, we'll write this function to our new file as well (db.rs).

"3" =>{
                clr();
                let search = prompt("Search by service name:");
                match search_service_by_name(&conn, &search) {
                    Ok(Some(entry)) => {
                        println!(
                            "Service = {}
                - Username : {} 
                - Password : {:?}",
                            entry.service, entry.username, entry.password
                        );
                    }
                    Ok(None) => {
                        println!("Service not found.");
                    }
                    Err(err) => {
                        eprintln!("Error searching for service: {}", err);
                    }
                }
            }

This time, in match search_service_by_name(&conn, &search) we'll pass the connection and search, and basically, we'll search for that entry (db will handle this) and show it to the user (entry.service, entry.username, entry.password).

If we find nothing we'll print println!("Service not found."); and if there is an error eprintln!("Error searching for service: {}", err);.

That's it with main.rs, time to move on to the db.rs where the magic happens!

The db.rs File

First things first, we need to import a few stuff:

use std::io;
use std::io::Write;
extern crate rusqlite;
use rusqlite::{Connection, Error};
use serde::{Deserialize, Serialize};

• std::io and std::io::Write are imported for input/output operations, particularly for prompting the user and flushing outputs.

• rusqlite is the external crate used for SQLite database operations.

• serde::{Deserialize, Serialize} is used for serializing and deserializing the ServiceInfo struct, which helps read from or write to the database.

Defining the Data Structure (ServiceInfo):

• A ServiceInfo struct is defined with id, service, username, and password fields.

• It implements a new function for easy instantiation.

#[derive(Debug, Serialize, Deserialize)]
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,
        }
    }
}

Prompt Function: A utility function to display a prompt message, take input from the user, and return the input as a trimmed String.

(We're calling this function in action 1, 'entry')

pub fn prompt(prompt: &str) -> String {
    print!("{}", prompt);
    io::stdout().flush().unwrap();

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

    input.trim().to_string()
}

Initializing the Database (init_database):

• Establishes a connection to the SQLite database file (passwords.db).

• Creates a passwords table if it doesn't exist, with columns matching the ServiceInfo struct (id, service, username, password).

(We're calling this function as we start on the main.rs)

pub fn init_database() -> Result<Connection, Error> {
    let conn = Connection::open("passwords.db")?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS passwords (
            id INTEGER PRIMARY KEY,
            service TEXT,
            username TEXT,
            password TEXT
        )",
        [],
    )?;

    Ok(conn)
}

Writing to the Database (write_password_to_db):

• Takes in a database connection and the details (service, username, password) to be inserted.

• Executes an INSERT statement to add the new entry to the passwords table.

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

Reading from the Database (read_passwords_from_db):

• Queries all entries from the passwords table.

• Maps each row to a ServiceInfo instance.

• Returns a vector of ServiceInfo instances representing all entries in the database.

(We call this function in action 2)

pub fn read_passwords_from_db(conn: &Connection) -> Result<Vec<ServiceInfo>, Error> {
    let mut stmt = conn.prepare("SELECT service, username, password FROM passwords")?;
    let entries = stmt
        .query_map([], |row| {
            Ok(ServiceInfo::new(
                row.get(0)?,
                row.get(1)?,
                row.get(2)?,
            ))
        })?
        .collect::<Result<Vec<_>, _>>()?;
    Ok(entries)
}

Searching the Database by Service Name (search_service_by_name):

• Takes in a service name and searches for a matching entry in the passwords table.

• Uses a SELECT statement with a condition to find a specific service by name.

• Returns an Option<ServiceInfo>, which is Some(ServiceInfo) if a matching entry is found, or None if no entry matches.

(We call this function in action 3)

pub fn search_service_by_name(conn: &Connection, name: &str) -> Result<Option<ServiceInfo>, Error> {
    let mut stmt = conn.prepare("SELECT id, service, username, password FROM passwords WHERE service = ?")?;
    let result = stmt.query_row(&[name], |row| {
        Ok(ServiceInfo {
            id: Some(row.get(0)?),
            service: row.get(1)?,
            username: row.get(2)?,
            password: row.get(3)?,
        })
    });

    match result {
        Ok(entry) => Ok(Some(entry)),
        Err(Error::QueryReturnedNoRows) => Ok(None),
        Err(err) => Err(err),
    }
}

Woohoo, are you still here? Congrats 🥳

You just finished building a CLI password vault in Rust + SQLite.

To run it: cargo run

Check the full code on GitHub.

Conclusion

Congratulations! You've built your password vault in Rust + SQLite. Each of these components (actions 1 to 4) plays a crucial role in managing the data flow between your Rust application and the SQLite database, enabling functionalities like adding new passwords, listing all passwords, and searching for a password by service name. As you continue to explore Rust, consider adding new features to your vault, such as password encryption, to further enhance your learning and the application's utility.

Happy coding. 🦀

---

👋 Hello, I'm Eleftheria, Community Manager, developer, public speaker, and content creator.

🥰 If you liked this article, consider sharing it.

🔗 All links | X | LinkedIn