Build a Single-File Rust Web API with SQLite

In this post we'll continue our series of building web APIs with Rust. In the last post we built a single-file web API with Rust and Axum using in-memory storage.

Now we're going to add a database as you'll commonly want data to persist beyond server restarts. In this post we'll add a data layer that persists to SQLite using SQLx.

What we're building

We're building on our existing single-file todo list CRUD web API.

• Create todos
• List todos
• Get a todo by id
• Delete a todo by id
• Mark a todo complete by id

For more on this core functionality and how it's built, checkout the previous post in this series.

Tech Stack:

• Rust - My best candidate for the missing programming language - expressive types, fast, good community and ecosystem

• Axum - Popular web framework built by creators of tokio

• SQLite - Simple and lightweight, though you can use a similar approach for any mainstream db like Postgres or MySQL

• Sqlx - Async database driver with query checking

How it's built

Data Models

Our data models remain largely the same as they did in the in-memory todo list:

• Todo - Modeling a todo with id, title, and whether it's completed
• CreateTodo - a command for when we create a todo

#[derive(Clone, Serialize, LightClone)]
struct Todo {
    id: Uuid,
    title: Arc<str>,
    completed: bool,
}

#[derive(Deserialize)]
struct CreateTodo {
    title: String,
}

What we've added is a new model that represents the data layer's representation of a todo.

• derive FromRow - Tells sqlx to map these fields directly to / from the database columns
• into_todo implementation - easy mapper from a TodoRow to a Todo

#[derive(FromRow)]
struct TodoRow {
    id: Uuid,
    title: String,
    completed: bool,
}

impl TodoRow {
    fn into_todo(self) -> Todo {
        Todo {
            id: self.id,
            title: self.title.into(),
            completed: self.completed,
        }
    }
}

This is common in many systems that use layered architecture. I'm personally a fan of vertical slices and layer boundaries as I find it typically helps make more composable systems - the data layer is contained in a solid, consistent boundary and now anyone that needs that data can reuse that code.

Error Handling

Error handling looks largely the same except now we've added an Internal error to help us represent issues that may be caused by e.g. not being able to connect to the db or similar. We could've also just allowed these to throw but I thought it was a good opportunity to showcase how these error enumerations can grow to more comprehensively model your system's failure cases.

Note that we've added a From implementation on TodoError that says if we get a sqlx::Error, this is how to convert it to a TodoError::Internal. This is nice as now Rust can infer this conversion in our service function implementations later.

#[derive(Debug)]
enum TodoError {
    NotFound(Uuid),
    Internal(sqlx::Error),
}

impl From<sqlx::Error> for TodoError {
    fn from(e: sqlx::Error) -> Self {
        TodoError::Internal(e)
    }
}

impl IntoResponse for TodoError {
    fn into_response(self) -> axum::response::Response {
        match self {
            TodoError::NotFound(id) => (
                StatusCode::NOT_FOUND,
                Json(serde_json::json!({ "error": format!("Todo {id} not found") })),
            )
                .into_response(),
            TodoError::Internal(e) => (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": format!("Internal error: {e}") })),
            )
                .into_response(),
        }
    }
}

Async Service Trait

Our TodoService looks largely the same but now everything is an async fn and returns a Result as we'll be making IO calls as we query the database and we know those can fail internally. This requires us to have a #[async_trait] on the trait and impl because while async fn is supported in Rust stable, it is not yet supported with dynamic dispatch which is how we're building interface-like dependency injection into our system.

#[async_trait]
trait TodoService: Send + Sync {
    async fn list(&self) -> Result<Vec<Todo>, TodoError>;
    async fn get(&self, id: Uuid) -> Result<Todo, TodoError>;
    async fn create(&self, input: CreateTodo) -> Result<Todo, TodoError>;
    async fn complete(&self, id: Uuid) -> Result<Todo, TodoError>;
    async fn delete(&self, id: Uuid) -> Result<Todo, TodoError>;
}

This is another case where Rust's low-level memory/call functions leak into app code and where it may feel a bit overwhelming when coming from a high-level language. But again if you just kind of think of it as boilerplate - when doing traits with async functions, need to add async_trait it remains manageable. Plus there's several proposals for fixing this so hopefully will be resolved at some point.

SqliteTodoService

Now that we're using Sqlite instead of an in-memory store, we need a new struct to model it.

Here we replace the Mutex<Vec<Todo>> with a SqlitePool and create the underlying todo table if it doesn't exist on service creation. Typically in an app you'd actually use migrations to spin up the table but for this example wanted to keep things simple and in a single-file so we're doing it at service creation time.

struct SqliteTodoService {
    pool: SqlitePool,
}

impl SqliteTodoService {
    async fn new(pool: SqlitePool) -> Self {
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS todos (
                id TEXT PRIMARY KEY NOT NULL,
                title TEXT NOT NULL,
                completed BOOLEAN NOT NULL DEFAULT FALSE
            )",
        )
        .execute(&pool)
        .await
        .unwrap();
        Self { pool }
    }
}

With the service defined and a way to create the table our logic depends on, we can now implement the service functions.

Note that we also need async_trait here to play nice with dynamic dispatch - the same reason we need it on the TodoService trait.

For each of the implementations, we use sqlx to run our query, convert to our data layer dto, and then map to Ok | Err.

#[async_trait]
impl TodoService for SqliteTodoService {
    async fn list(&self) -> Result<Vec<Todo>, TodoError> {
        let todos = sqlx::query_as::<_, TodoRow>("SELECT id, title, completed FROM todos")
            .fetch_all(&self.pool)
            .await?;
        Ok(todos.into_iter().map(TodoRow::into_todo).collect())
    }

    async fn get(&self, id: Uuid) -> Result<Todo, TodoError> {
        sqlx::query_as::<_, TodoRow>("SELECT id, title, completed FROM todos WHERE id = ?")
            .bind(id)
            .fetch_optional(&self.pool)
            .await?
            .map(TodoRow::into_todo)
            .ok_or(TodoError::NotFound(id))
    }

    async fn create(&self, input: CreateTodo) -> Result<Todo, TodoError> {
        let id = Uuid::now_v7();
        sqlx::query("INSERT INTO todos (id, title, completed) VALUES (?, ?, FALSE)")
            .bind(id)
            .bind(&input.title)
            .execute(&self.pool)
            .await?;
        Ok(Todo {
            id,
            title: input.title.into(),
            completed: false,
        })
    }

    async fn complete(&self, id: Uuid) -> Result<Todo, TodoError> {
        sqlx::query_as::<_, TodoRow>(
            "UPDATE todos SET completed = TRUE WHERE id = ? RETURNING id, title, completed",
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?
        .map(TodoRow::into_todo)
        .ok_or(TodoError::NotFound(id))
    }

    async fn delete(&self, id: Uuid) -> Result<Todo, TodoError> {
        sqlx::query_as::<_, TodoRow>(
            "DELETE FROM todos WHERE id = ? RETURNING id, title, completed",
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?
        .map(TodoRow::into_todo)
        .ok_or(TodoError::NotFound(id))
    }
}

Startup and Routing

The routing and handlers have minimal changes except to await the new async functions and create the SqlitePool that our TodoService now needs to function.

async fn hello() -> &'static str {
    "Hello, World!"
}

async fn list_todos(
    State(service): State<Arc<dyn TodoService>>,
) -> Result<Json<Vec<Todo>>, TodoError> {
    service.list().await.map(Json)
}

async fn get_todo(
    State(service): State<Arc<dyn TodoService>>,
    Path(id): Path<Uuid>,
) -> Result<Json<Todo>, TodoError> {
    service.get(id).await.map(Json)
}

async fn create_todo(
    State(service): State<Arc<dyn TodoService>>,
    Json(input): Json<CreateTodo>,
) -> Result<(StatusCode, Json<Todo>), TodoError> {
    let todo = service.create(input).await?;
    Ok((StatusCode::CREATED, Json(todo)))
}

async fn complete_todo(
    State(service): State<Arc<dyn TodoService>>,
    Path(id): Path<Uuid>,
) -> Result<Json<Todo>, TodoError> {
    service.complete(id).await.map(Json)
}

async fn delete_todo(
    State(service): State<Arc<dyn TodoService>>,
    Path(id): Path<Uuid>,
) -> Result<Json<Todo>, TodoError> {
    service.delete(id).await.map(Json)
}

#[tokio::main]
async fn main() {
    let pool = SqlitePool::connect("sqlite:todos.db?mode=rwc")
        .await
        .unwrap();

    let service: Arc<dyn TodoService> = Arc::new(SqliteTodoService::new(pool).await);

    let app = Router::new()
        .route("/", get(hello))
        .route("/todos", get(list_todos).post(create_todo))
        .route("/todos/{id}", get(get_todo).delete(delete_todo))
        .route("/todos/{id}/complete", post(complete_todo))
        .with_state(service);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    println!("Listening on http://localhost:3000");
    axum::serve(listener, app).await.unwrap();
}

That's it! Spin this up and get a single-file web api on localhost:3000 backed by a sqlite db!

Next

So now you know how to spin up a web api backed by a sqlite db in Rust.

If you want access to the full project source code, you can check out the HAMY LABS Example Code repo on GitHub. This is available to HAMINIONs Members so join to get access and support me making more tutorials like this one.

If you want to see how I spin up my fullstack Rust webapps, take a look at CloudSeed Rust.

If you liked this post you might also like:

• Build a Simple Single-File Rust Web API
• Why I'm moving from C# to Rust for High-level Apps
• High-Level Rust: Getting 80% of the Benefits with 20% of the Pain