Syeed Talha

Posted on May 3

CRUD API in Rust Using Axum, SQLite, and sqlx

#axum #rust #webapi #sqlite

In this article, we will build a beginner-friendly CRUD API using Rust, Axum, SQLite, and sqlx.

We will build a simple users API where we can:

Create a user
Get all users
Get one user by ID
Update a user
Delete a user

This article uses a clean project structure instead of putting everything inside main.rs.

What We Are Using

We will use:

axum for building the web API
tokio for async runtime
sqlx for async database queries
sqlite as the database
serde for JSON serialization and deserialization

SQLite is a good database for learning because it does not need a separate database server. It stores data inside a local file.

Project Structure

Create a new Rust project:

cargo new axum_sqlite_crud
cd axum_sqlite_crud

Our final project structure will look like this:

axum_sqlite_crud/
├── Cargo.toml
└── src/
    ├── main.rs
    ├── db.rs
    ├── state.rs
    ├── models.rs
    └── handlers.rs

Here is what each file will do:

main.rs      -> starts the server and connects routes
db.rs        -> database connection and table creation
state.rs     -> shared application state
models.rs    -> request and response structs
handlers.rs  -> CRUD handler functions

Step 1: Add Dependencies

Open Cargo.toml and add:

[dependencies]
axum = "0.8"
tokio = { version = "1", features = ["full"] }

sqlx = {
    version = "0.8",
    features = [
        "sqlite",
        "runtime-tokio-rustls"
    ]
}

serde = { version = "1", features = ["derive"] }

Step 2: Create `state.rs`

Create this file:

src/state.rs

Add this code:

use sqlx::SqlitePool;

#[derive(Clone)]
pub struct AppState {
    pub db: SqlitePool,
}

Explanation

AppState is used to share application data across routes.

In our case, we want every handler to access the SQLite database connection pool.

The SqlitePool is a database connection pool. Instead of creating a new database connection for every request, the pool manages and reuses connections.

Step 3: Create `models.rs`

Create this file:

src/models.rs

Add this code:

use serde::{Deserialize, Serialize};
use sqlx::FromRow;

#[derive(Debug, Serialize, FromRow)]
pub struct User {
    pub id: i64,
    pub name: String,
    pub email: String,
}

#[derive(Debug, Deserialize)]
pub struct CreateUser {
    pub name: String,
    pub email: String,
}

#[derive(Debug, Deserialize)]
pub struct UpdateUser {
    pub name: String,
    pub email: String,
}

Explanation

We created three structs.

User represents the data we return from the database.

pub struct User {
    pub id: i64,
    pub name: String,
    pub email: String,
}

CreateUser represents the JSON body when creating a new user.

pub struct CreateUser {
    pub name: String,
    pub email: String,
}

UpdateUser represents the JSON body when updating an existing user.

pub struct UpdateUser {
    pub name: String,
    pub email: String,
}

Why `Serialize`?

Serialize allows Rust structs to be converted into JSON responses.

Why `Deserialize`?

Deserialize allows JSON request bodies to be converted into Rust structs.

Why `FromRow`?

FromRow allows sqlx to convert database rows into our User struct.

Step 4: Create `db.rs`

Create this file:

src/db.rs

Add this code:

use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::SqlitePool;
use std::str::FromStr;

pub async fn init_db() -> SqlitePool {
    let options = SqliteConnectOptions::from_str("sqlite:test.db")
        .unwrap()
        .create_if_missing(true);

    SqlitePoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await
        .unwrap()
}

pub async fn create_table(pool: &SqlitePool) {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL
        )",
    )
    .execute(pool)
    .await
    .unwrap();
}

Explanation

This file handles database setup.

SqliteConnectOptions::from_str("sqlite:test.db")

This tells SQLite to use a database file named:

test.db

If the file does not exist, this line creates it:

.create_if_missing(true)

Without this, you may get an error like:

unable to open database file

Then we create a connection pool:

SqlitePoolOptions::new()
    .max_connections(5)

This means the pool can manage up to 5 database connections.

Then we create a table:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL
)

IF NOT EXISTS is important because the table creation code runs every time the app starts. If the table already exists, SQLite will not create it again.

Step 5: Create `handlers.rs`

Create this file:

src/handlers.rs

Add this code:

use axum::{
    extract::{Path, State},
    http::StatusCode,
    Json,
};

use crate::{
    models::{CreateUser, UpdateUser, User},
    state::AppState,
};

pub async fn create_user(
    State(state): State<AppState>,
    Json(payload): Json<CreateUser>,
) -> (StatusCode, Json<User>) {
    let user = sqlx::query_as::<_, User>(
        "INSERT INTO users (name, email)
         VALUES (?, ?)
         RETURNING id, name, email",
    )
    .bind(payload.name)
    .bind(payload.email)
    .fetch_one(&state.db)
    .await
    .unwrap();

    (StatusCode::CREATED, Json(user))
}

pub async fn get_users(
    State(state): State<AppState>,
) -> Json<Vec<User>> {
    let users = sqlx::query_as::<_, User>(
        "SELECT id, name, email FROM users",
    )
    .fetch_all(&state.db)
    .await
    .unwrap();

    Json(users)
}

pub async fn get_user_by_id(
    Path(id): Path<i64>,
    State(state): State<AppState>,
) -> Result<Json<User>, StatusCode> {
    let user = sqlx::query_as::<_, User>(
        "SELECT id, name, email FROM users WHERE id = ?",
    )
    .bind(id)
    .fetch_optional(&state.db)
    .await
    .unwrap();

    match user {
        Some(user) => Ok(Json(user)),
        None => Err(StatusCode::NOT_FOUND),
    }
}

pub async fn update_user(
    Path(id): Path<i64>,
    State(state): State<AppState>,
    Json(payload): Json<UpdateUser>,
) -> Result<Json<User>, StatusCode> {
    let user = sqlx::query_as::<_, User>(
        "UPDATE users
         SET name = ?, email = ?
         WHERE id = ?
         RETURNING id, name, email",
    )
    .bind(payload.name)
    .bind(payload.email)
    .bind(id)
    .fetch_optional(&state.db)
    .await
    .unwrap();

    match user {
        Some(user) => Ok(Json(user)),
        None => Err(StatusCode::NOT_FOUND),
    }
}

pub async fn delete_user(
    Path(id): Path<i64>,
    State(state): State<AppState>,
) -> StatusCode {
    let result = sqlx::query(
        "DELETE FROM users WHERE id = ?",
    )
    .bind(id)
    .execute(&state.db)
    .await
    .unwrap();

    if result.rows_affected() == 0 {
        StatusCode::NOT_FOUND
    } else {
        StatusCode::NO_CONTENT
    }
}

Understanding the Handlers

Now let’s understand the important parts.

`State(state): State<AppState>`

This gives the handler access to shared application state.

Because our AppState contains the database pool, every handler can use:

state.db

to run database queries.

`Json(payload): Json<CreateUser>`

This extracts JSON from the request body.

For example, if the client sends:

{
  "name": "Talha",
  "email": "talha@example.com"
}

Axum converts it into:

CreateUser {
    name: "Talha".to_string(),
    email: "talha@example.com".to_string(),
}

`Path(id): Path<i64>`

This extracts the ID from the URL.

For example:

GET /users/1

Axum takes 1 from the URL and gives it to the handler as:

id = 1

In Axum 0.8, path parameters use curly braces in routes:

"/users/{id}"

not:

"/users/:id"

`.bind()`

This is very important.

Instead of writing SQL like this:

format!("SELECT * FROM users WHERE id = {}", id)

we write:

sqlx::query("SELECT * FROM users WHERE id = ?")
    .bind(id)

This is safer because it helps prevent SQL injection.

`fetch_one`

This is used when we expect one row.

Example:

.fetch_one(&state.db)

We use it after inserting a user because we return the newly created user.

`fetch_all`

This is used when we expect many rows.

Example:

.fetch_all(&state.db)

We use it when getting all users.

`fetch_optional`

This is used when the data may or may not exist.

Example:

.fetch_optional(&state.db)

If the user exists, it returns:

Some(user)

If the user does not exist, it returns:

None

That is why we can return 404 NOT FOUND.

Step 6: Create `main.rs`

Now open:

src/main.rs

Replace everything with:

mod db;
mod handlers;
mod models;
mod state;

use axum::{
    routing::{get, post},
    Router,
};

use db::{create_table, init_db};
use handlers::{
    create_user,
    delete_user,
    get_user_by_id,
    get_users,
    update_user,
};
use state::AppState;

#[tokio::main]
async fn main() {
    let pool = init_db().await;

    create_table(&pool).await;

    let state = AppState { db: pool };

    let app = Router::new()
        .route("/users", post(create_user).get(get_users))
        .route(
            "/users/{id}",
            get(get_user_by_id)
                .put(update_user)
                .delete(delete_user),
        )
        .with_state(state);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000")
        .await
        .unwrap();

    println!("Server running on http://localhost:3000");

    axum::serve(listener, app).await.unwrap();
}

Understanding `main.rs`

These lines import our other files:

mod db;
mod handlers;
mod models;
mod state;

This means Rust can use code from:

db.rs
handlers.rs
models.rs
state.rs

Then we initialize the database:

let pool = init_db().await;

Then we create the table:

create_table(&pool).await;

Then we create shared state:

let state = AppState { db: pool };

Then we define routes:

.route("/users", post(create_user).get(get_users))

This means:

POST /users -> create user
GET  /users -> get all users

Then:

.route(
    "/users/{id}",
    get(get_user_by_id)
        .put(update_user)
        .delete(delete_user),
)

This means:

GET    /users/{id} -> get one user
PUT    /users/{id} -> update one user
DELETE /users/{id} -> delete one user

Finally, we attach the shared state:

.with_state(state)

Now all handlers can access the database pool.

Step 7: Run the Server

Run:

cargo run

You should see:

Server running on http://localhost:3000

Also, after running the app, you should see a new file in your project:

test.db

That is your SQLite database file.

Step 8: Test the API

You can test using Postman, Thunder Client, or curl.

Create User

Request:

POST http://localhost:3000/users

Body:

{
  "name": "Talha",
  "email": "talha@example.com"
}

curl:

curl -X POST http://localhost:3000/users \
  -H "Content-Type: application/json" \
  -d '{"name":"Talha","email":"talha@example.com"}'

On Windows CMD, use:

curl -X POST http://localhost:3000/users ^
  -H "Content-Type: application/json" ^
  -d "{\"name\":\"Talha\",\"email\":\"talha@example.com\"}"

Expected response:

{
  "id": 1,
  "name": "Talha",
  "email": "talha@example.com"
}

Get All Users

Request:

GET http://localhost:3000/users

curl:

curl http://localhost:3000/users

Expected response:

[
  {
    "id": 1,
    "name": "Talha",
    "email": "talha@example.com"
  }
]

Get One User

Request:

GET http://localhost:3000/users/1

curl:

curl http://localhost:3000/users/1

Expected response:

{
  "id": 1,
  "name": "Talha",
  "email": "talha@example.com"
}

If the user does not exist, the API returns:

404 NOT FOUND

Update User

Request:

PUT http://localhost:3000/users/1

Body:

{
  "name": "Updated Talha",
  "email": "updated@example.com"
}

curl:

curl -X PUT http://localhost:3000/users/1 \
  -H "Content-Type: application/json" \
  -d '{"name":"Updated Talha","email":"updated@example.com"}'

Windows CMD:

curl -X PUT http://localhost:3000/users/1 ^
  -H "Content-Type: application/json" ^
  -d "{\"name\":\"Updated Talha\",\"email\":\"updated@example.com\"}"

Expected response:

{
  "id": 1,
  "name": "Updated Talha",
  "email": "updated@example.com"
}

Delete User

Request:

DELETE http://localhost:3000/users/1

curl:

curl -X DELETE http://localhost:3000/users/1

If deleted successfully, the API returns:

204 NO CONTENT

If the user does not exist, the API returns:

404 NOT FOUND

Why We Did Not Put Everything in `main.rs`

For very small examples, putting all code in main.rs is okay.

But as your project grows, main.rs becomes hard to read.

This structure is better:

main.rs      -> application startup
db.rs        -> database setup
state.rs     -> shared state
models.rs    -> data types
handlers.rs  -> request handlers

This makes the project easier to understand, maintain, and extend.

Current Limitations

This project is beginner-friendly, but it is not fully production-ready yet.

For production, we should improve:

Error handling instead of using .unwrap()
Database migrations instead of creating tables in code
Validation for user input
Separate service/repository layers for larger apps
Environment variables for database URL

But this version is a strong starting point.

Final Thoughts

In this article, we built a CRUD API using Axum, SQLite, and sqlx.

We learned:

How to connect Axum with SQLite
How to use a database connection pool
How to share database access using state
How to create clean file structure
How to write Create, Read, Update, and Delete handlers
How to test the API using curl or Postman

SQLite is a great choice when you are learning database integration because it is simple, local, and does not require a separate database server.

Once you understand this, switching to PostgreSQL later will be much easier because sqlx supports both SQLite and PostgreSQL.

DEV Community

CRUD API in Rust Using Axum, SQLite, and sqlx

What We Are Using

Project Structure

Step 1: Add Dependencies

Step 2: Create `state.rs`

Explanation

Step 3: Create `models.rs`

Explanation

Why `Serialize`?

Why `Deserialize`?

Why `FromRow`?

Step 4: Create `db.rs`

Explanation

Step 5: Create `handlers.rs`

Understanding the Handlers

`State(state): State<AppState>`

`Json(payload): Json<CreateUser>`

`Path(id): Path<i64>`

`.bind()`

`fetch_one`

`fetch_all`

`fetch_optional`

Step 6: Create `main.rs`

Understanding `main.rs`

Step 7: Run the Server

Step 8: Test the API

Create User

Get All Users

Get One User

Update User

Delete User

Why We Did Not Put Everything in `main.rs`

Current Limitations

Final Thoughts

Top comments (0)

What We Are Using

Project Structure

Step 1: Add Dependencies

Step 2: Create state.rs

Explanation

Step 3: Create models.rs

Explanation

Why Serialize?

Why Deserialize?

Why FromRow?

Step 4: Create db.rs

Explanation

Step 5: Create handlers.rs

Understanding the Handlers

State(state): State<AppState>

Json(payload): Json<CreateUser>

Path(id): Path<i64>

.bind()

fetch_one

fetch_all

fetch_optional

Step 6: Create main.rs

Understanding main.rs

Step 7: Run the Server

Step 8: Test the API

Create User

Get All Users

Get One User

Update User

Delete User

Why We Did Not Put Everything in main.rs

Current Limitations

Final Thoughts

Step 2: Create `state.rs`

Step 3: Create `models.rs`

Why `Serialize`?

Why `Deserialize`?

Why `FromRow`?

Step 4: Create `db.rs`

Step 5: Create `handlers.rs`

`State(state): State<AppState>`

`Json(payload): Json<CreateUser>`

`Path(id): Path<i64>`

`.bind()`

`fetch_one`

`fetch_all`

`fetch_optional`

Step 6: Create `main.rs`

Understanding `main.rs`

Why We Did Not Put Everything in `main.rs`