🦀 Building a REST API with Rust using Actix-web + SQLite (sqlx)

Published on: 2022-07-15

Previously, we built a simple REST API with Actix-web. Now, let’s connect it to a SQLite database using sqlx for async, compile-time safe SQL queries.

---

⚙️ Prerequisites

• Rust version: 1.58+
• Actix-web version: 4.0
• sqlx version: 0.5+

Install dependencies:

cargo add actix-web sqlx tokio --features sqlite

---

🧩 Database Setup

Create a file called todos.db and run:

CREATE TABLE todos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT 0
);

Use your preferred SQLite client or CLI.

---

📂 Project Structure

src/
  main.rs
  handlers.rs
  models.rs
  db.rs

---

✨ db.rs

Initialize the SQLite pool:

use sqlx::sqlite::SqlitePool;

pub async fn init_db(db_url: &str) -> SqlitePool {
  SqlitePool::connect(db_url).await.expect("Failed to connect to DB")
}

---

✨ models.rs

Todo struct remains:

use serde::{Serialize, Deserialize};

#[derive(Serialize, Deserialize, sqlx::FromRow)]
pub struct Todo {
  pub id: i64,
  pub title: String,
  pub completed: bool,
}

---

✨ handlers.rs

Handlers now interact with the database:

use actix_web::{get, post, web, HttpResponse, Responder};
use sqlx::SqlitePool;
use crate::models::Todo;

#[get("/todos")]
pub async fn list_todos(db: web::Data<SqlitePool>) -> impl Responder {
  let todos = sqlx::query_as::<_, Todo>("SELECT * FROM todos")
    .fetch_all(db.get_ref())
    .await
    .unwrap_or_else(|_| vec![]);

  HttpResponse::Ok().json(todos)
}

#[post("/todos")]
pub async fn create_todo(db: web::Data<SqlitePool>, todo: web::Json<Todo>) -> impl Responder {
  let inserted = sqlx::query_as::<_, Todo>(
    "INSERT INTO todos (title, completed) VALUES (?, ?) RETURNING id, title, completed"
  )
    .bind(&todo.title)
    .bind(todo.completed)
    .fetch_one(db.get_ref())
    .await
    .unwrap();

  HttpResponse::Created().json(inserted)
}

---

✨ main.rs

Wire everything with the DB pool:

use actix_web::{App, HttpServer, web};
mod handlers;
mod models;
mod db;

#[actix_web::main]
async fn main() -> std::io::Result<()> {
  let db_pool = db::init_db("sqlite:todos.db").await;

  HttpServer::new(move || {
    App::new()
      .app_data(web::Data::new(db_pool.clone()))
      .service(handlers::list_todos)
      .service(handlers::create_todo)
  })
  .bind(("127.0.0.1", 8080))?
  .run()
  .await
}

---

📊 Project Class Diagram

---

🔥 Running the API

Start your server:

cargo run

Test with curl:

curl http://127.0.0.1:8080/todos

---

💡 Why sqlx?

• Async: Fully asynchronous for scalable APIs.
• Compile-time safety: SQL queries checked at compile time to prevent runtime errors.
• Simple SQLite support: Great for small services or local development.

---

🧠 TL;DR

• Integrating Actix-web + sqlx + SQLite creates a lightweight, performant backend.
• Compile-time query checking boosts confidence in deployment.
• Scale to PostgreSQL or MySQL with minimal changes.

---