I've noticed a recurring pattern when developers first move from memory-based storage (like a Vec) to persistent storage in Rust. They often believe that to interact with a database, they must use a heavyweight ORM (Object-Relational Mapper) like Diesel or SeaORM to avoid writing "raw" SQL. They assume that without an ORM, they'll be drowning in boilerplate and unsafe string concatenation.

You don't need a heavyweight ORM for local storage

Here is why that's usually a mistake for a tool like a CLI Todo app: ORMs are fantastic for massive enterprise schemas with dozens of joins, but they introduce a steep learning curve and significant compile-time overhead. If you're just tracking a task name and a boolean "completed" status, setting up a full migration system is like using a sledgehammer to crack a nut.

For this project, we're using rusqlite. It's a ergonomic wrapper around the SQLite C API. It doesn't hide SQL from you—it embraces it. I prefer this because it keeps the data layer transparent. You aren't guessing what the ORM is doing under the hood; you're just writing the SQL you already know.

// The "heavy" way: Fighting with macros and schema files.
// The "rusqlite" way: Just open the connection and run the command.
let conn = Connection::open("todos.db")?;
conn.execute(
    "CREATE TABLE IF NOT EXISTS todo (
        id INTEGER PRIMARY KEY,
        task TEXT NOT NULL,
        done BOOLEAN NOT NULL DEFAULT 0
    )",
    [],
)?;

Bridging the gap between SQL rows and Rust structs
The real friction point isn't the SQL—it's getting the data back into a type-safe Rust struct. You might think you have to manually index into a row array for every single field, which feels brittle and error-prone. But rusqlite provides a query_map method that lets you define the transformation once.

Look at how we handle the mapping here. I'm using a closure to turn each row into a TodoItem. This keeps the "database logic" separate from the "business logic" of your app.

struct TodoItem {
    id: i32,
    task: String,
    done: bool,
}

fn get_all_todos(conn: &Connection) -> Result<Vec<TodoItem>> {
    let mut stmt = conn.prepare("SELECT id, task, done FROM todo")?;
    let todo_iter = stmt.query_map([], |row| {
        Ok(TodoItem {
            id: row.get(0)?,
            task: row.get(1)?,
            done: row.get(2)?,
        })
    })?;

    let mut results = Vec::new();
    for todo in todo_iter {
        results.push(todo?);
    }
    Ok(results)
}

Handling the "Database is Locked" headache
If you've used SQLite before, you've probably hit the database is locked error. This usually happens in Rust when you have a statement handle (like the one in the get_all_todos function above) that hasn't been dropped yet while you're trying to write to the database. 

Because Rust's ownership system is so strict, if that stmt variable is still in scope, SQLite might think the table is still being read from. My advice? Keep your database transactions tight. Wrap your query logic in a block or a separate function so the statement is dropped the moment you're done fetching the data. Don't hold onto a Statement object longer than you absolutely have to.

📋 Practical Task

Implementing a 'Toggle Completion' Command and Priority Filter

Now that you have the basic CRUD operations working, you need to add two specific features to your CLI tool to make it actually usable:

• The Toggle: Create a function toggle_todo(conn: &Connection, id: i32). It should first check the current done status of the task with that ID, and then update it to the opposite value (true → false, or vice versa).
• The Filter: Modify your database schema to include a priority column (INTEGER). Then, update your get_all_todos function to accept an optional priority_filter: Option<i32>. If a filter is provided, only return tasks matching that priority; otherwise, return all of them.

Requirement: Ensure you use parameterized queries (using [] or params![]) to prevent SQL injection, even though this is a local CLI app. It's a habit you need to build.