SQLx SQLite Todo

# sqlx-sqlite-todo

## Overview

Rust CLI todo manager backed by SQLite using `sqlx` and `clap`.
Using subcommands and environment-based database configuration.

## What This Example Demonstrates

- Subcommand-based CLI (`add`, `done`, and default list).
- Automatic table initialization at startup.
- Insert, update, and query patterns with SQLx.
- Reading required configuration from environment variables.

## Run

- `DATABASE_FILE=todos.db cargo run`
  - Lists all todos.
- `DATABASE_FILE=todos.db cargo run -- add "buy milk"`
  - Adds a new todo item.
- `DATABASE_FILE=todos.db cargo run -- done 1`
  - Marks todo `1` as done.

## Configuration

- Required env var: `DATABASE_FILE`
- Example value: `todos.db` or `/tmp/my-todos.db`

## Copilot Usage Hint

Keep the command behavior centered in the `Command` enum and `match args.cmd` so new subcommands remain discoverable and testable.

Source code:

use anyhow::Context;
use clap::{Parser, Subcommand};
use sqlx::{
    Row,
    sqlite::{SqliteConnectOptions, SqlitePool},
};
use std::env;

#[derive(Parser)]
struct Args {
    #[command(subcommand)]
    cmd: Option<Command>,
}

#[derive(Subcommand)]
enum Command {
    Add { description: String },
    Done { id: i64 },
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
    let args = Args::parse();
    let database_file = env::var("DATABASE_FILE")
        .context("DATABASE_FILE is not set. Set it to the SQLite database file path, for example: DATABASE_FILE=todos.db")?;
    run(args, database_file).await
}

async fn run(args: Args, database_file: String) -> anyhow::Result<()> {
    let options = SqliteConnectOptions::new()
        .filename(database_file)
        .create_if_missing(true);
    let pool = SqlitePool::connect_with(options).await?;
    initialize_database(&pool).await?;

    match args.cmd {
        Some(Command::Add { description }) => {
            println!("Adding new todo with description '{description}'");
            let todo_id = add_todo(&pool, description).await?;
            println!("Added new todo with id {todo_id}");
        }
        Some(Command::Done { id }) => {
            println!("Marking todo {id} as done");
            if complete_todo(&pool, id).await? {
                println!("Todo {id} is marked as done");
            } else {
                println!("Invalid id {id}");
            }
        }
        None => {
            println!("Printing list of all todos");
            list_todos(&pool).await?;
        }
    }

    Ok(())
}

async fn initialize_database(pool: &SqlitePool) -> anyhow::Result<()> {
    sqlx::query(
        r#"
CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    description TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT FALSE
)
        "#,
    )
    .execute(pool)
    .await?;

    Ok(())
}

async fn add_todo(pool: &SqlitePool, description: String) -> anyhow::Result<i64> {
    let mut conn = pool.acquire().await?;

    // Insert the task, then obtain the ID of this row
    let id = sqlx::query(
        r#"
INSERT INTO todos ( description )
VALUES ( ?1 )
        "#,
    )
    .bind(description)
    .execute(&mut *conn)
    .await?
    .last_insert_rowid();

    Ok(id)
}

async fn complete_todo(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
    let rows_affected = sqlx::query(
        r#"
UPDATE todos
SET done = TRUE
WHERE id = ?1
        "#,
    )
    .bind(id)
    .execute(pool)
    .await?
    .rows_affected();

    Ok(rows_affected > 0)
}

async fn list_todos(pool: &SqlitePool) -> anyhow::Result<()> {
    let recs = sqlx::query(
        r#"
SELECT id, description, done
FROM todos
ORDER BY id
        "#,
    )
    .fetch_all(pool)
    .await?;

    for rec in recs {
        let id: i64 = rec.try_get("id")?;
        let description: String = rec.try_get("description")?;
        let done: bool = rec.try_get("done")?;

        println!(
            "- [{}] {}: {}",
            if done { "x" } else { " " },
            id,
            description,
        );
    }

    Ok(())
}

#[cfg(test)]
mod tests;

This example uses sqlx and SQLite to store a simple todo list.

Run these commands from examples/sqlx-sqlite-todo:

DATABASE_FILE=todos.db cargo run
DATABASE_FILE=todos.db cargo run -- add "buy milk"
DATABASE_FILE=todos.db cargo run -- add "walk the dog"
DATABASE_FILE=todos.db cargo run -- done 1
DATABASE_FILE=todos.db cargo run

Expected output:

Printing list of all todos
- [ ] 1: buy milk
- [x] 2: walk the dog

The DATABASE_FILE environment variable is required and specifies where the SQLite database file is stored. The application automatically creates the database and tables on first run.