Rust CRUD API Example with Axum Framework and MySQL

In this article, you’ll discover how to use the Axum framework to build a RESTful API in Rust that supports basic CRUD (Create, Read, Update, Delete) operations against a MySQL database. To achieve this, we’ll be leveraging the SQLx toolkit, which provides a simple and efficient way to interact with databases.

By completing this tutorial, you’ll gain a solid understanding of how to build a Rust and Axum API that can handle advanced database operations.

A CRUD API is a web-based application programming interface that follows the CRUD paradigm, which provides a standardized way for developers to interact with data, making it an essential part of many web-based applications. If you’re new to RESTful APIs, they are a way of structuring web services that use HTTP requests to access and manipulate resources.

When it comes to building high-performance APIs in Rust that can handle a large volume of traffic, the Axum framework is a popular choice. When combined with the SQLx toolkit, it becomes even better.

So, without further delay, let’s begin building an API for a note-taking application using the powerful combination of Axum and SQLx.

More practice:

• Implement Google and GitHub OAuth2 in Rust Frontend App
• Rust CRUD API Example with Axum and PostgreSQL
• Create a Simple API in Rust using the Axum Framework
• Build a Frontend Web App in Rust using the Yew.rs Framework
• Frontend App with Rust and Yew.rs: User SignUp and Login
• Rust – How to Generate and Verify (JWTs) JSON Web Tokens
• Rust and Actix Web – JWT Access and Refresh Tokens
• Rust and Yew.rs Frontend: JWT Access and Refresh Tokens
• Rust – JWT Authentication with Actix Web
• Build a Simple API with Rust and Rocket
• Build a CRUD API with Rust and MongoDB

Set up and Run the Axum MySQL CRUD API Project

To run the Axum MySQL CRUD API project on your computer, follow these steps:

1. Clone or download the project repository from https://github.com/wpcodevo/rust-axum-mysql and open it in your favourite code editor.
2. Launch the MySQL server in a Docker container by running docker-compose up -d in your integrated terminal.
3. Install the SQLX-CLI tool by running cargo install sqlx-cli, and apply the migration script to the MySQL database by running sqlx migrate run.
4. Build the project and install all the required crates by running cargo build.
5. Start the Axum HTTP server by running cargo run.
6. To test the CRUD functionality of the Axum API, import the Note App.postman_collection.json Postman collection file into either Postman or Thunder Client VS Code extension. This file includes predefined requests that you can use for testing, so you don’t have to manually type the request URLs, set the HTTP methods, and type the request bodies.
   
   Alternatively, set up a React application that has CRUD functionalities to interact with the API using the instructions below.

Run the Axum CRUD API with a Frontend App

If you want a step-by-step guide on how to build a React app with CRUD functionalities, you can refer to the article titled “Build a React.js CRUD App using a RESTful API“. However, if you’re looking for a quick way to test the CRUD functionalities of the Rust API without writing code, you can follow these simple steps:

1. Ensure that you have Node.js and Yarn installed on your computer.
2. Clone or download the React CRUD project from https://github.com/wpcodevo/reactjs-crud-note-app and open it in your preferred IDE or text editor.
3. In the terminal of the project’s root directory, run yarn or yarn install to install the project’s dependencies.
4. Start the Vite development server by running yarn dev.
5. Navigate to http://localhost:3000/ in your web browser to access the React app and test its CRUD functionality against the Rust API.

Note: To avoid “site can’t be reached” or CORS errors, don’t use http://127.0.0.1:3000.

Set up the Rust Project

By the end of this article, you’ll have a well-organized project structure that looks just like the screenshot below. This will not only make it easier to follow along with the tutorial but also help you stay on track and confidently implement your own version of the project.

Now that you have a clear idea of what the file and folder structure of the final project will be like, it’s time to set up the project. First, choose a directory on your computer where you want to keep the project’s source code. Once you’re there, open a terminal and execute the following commands:

mkdir rust-axum-mysql
cd rust-axum-mysql
cargo init

The commands above create a new folder called rust-axum-mysql and initialize a new Rust project inside it. After the project is initialized, you can proceed to install all the required crates using the following commands.

cargo add axum
cargo add tokio -F full
cargo add tower-http -F "cors"
cargo add serde_json
cargo add serde -F derive
cargo add chrono -F serde
cargo add dotenv
cargo add uuid -F "serde v4"
cargo add sqlx --features "runtime-async-std-native-tls mysql chrono uuid"

After installing all the required crates with the previous commands, you can proceed to open the project in your preferred IDE. If you’re using VS Code, you can open the project by running the command code . in the terminal while inside the project directory.

Once the project is opened in your IDE, you can navigate to the Cargo.toml file to view all the dependencies installed. This file should look similar to the one below.

If you experience compatibility issues with the latest versions of the crates, you can revert to the versions listed in the Cargo.toml file. If you still face any issues, please leave a comment so that I can update both the article and the source code accordingly.

Cargo.toml

[package]
name = "rust-axum-mysql"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
axum = "0.7.2"
chrono = { version = "0.4.24", features = ["serde"] }
dotenv = "0.15.0"
serde = { version = "1.0.160", features = ["derive"] }
serde_json = "1.0.96"
sqlx = { version = "0.7.3", features = ["runtime-async-std-native-tls", "mysql", "chrono", "uuid"] }
tokio = { version = "1.27.0", features = ["full"] }
tower-http = { version = "0.5.0", features = ["cors"] }
uuid = { version = "1.3.1", features = ["serde", "v4"] }

As a warm-up before implementing the CRUD features, let’s start by building a simple Axum server with only one endpoint, a health checker route, that responds with a JSON object containing a message field and status. Open the src/main.rs file and replace its content with the following code:

src/main.rs

use axum::{response::IntoResponse, routing::get, Json, Router};

async fn health_checker_handler() -> impl IntoResponse {
    const MESSAGE: &str = "Rust CRUD API Example with Axum Framework and MySQL";

    let json_response = serde_json::json!({
        "status": "success",
        "message": MESSAGE
    });

    Json(json_response)
}

#[tokio::main]
async fn main() {
    let app = Router::new().route("/api/healthchecker", get(health_checker_handler));

    println!("🚀 Server started successfully");
    let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await.unwrap();
    axum::serve(listener, app).await.unwrap()
}

With the code above, you can compile the crates and build the project by running cargo build. After that, start the Axum HTTP server by executing cargo run.

To avoid manually restarting the server after every change to the source code, you can use Cargo Watch, a tool that watches the source code and automatically rebuilds the project. Install it with cargo install cargo-watch.

Once you have Cargo Watch installed, use the command cargo watch -q -c -w src/ -x run to start the Axum HTTP server. With the server up and running, go to http://localhost:8000/api/healthchecker in your web browser. If everything was done correctly, you should see the JSON object.

Set up the MySQL Server with Docker

Let’s move on to setting up the MySQL server using Docker. Although this step is optional and can be skipped if you already have a running MySQL server, be sure to add the required credentials to the .env file if you choose to skip it.

To set up the MySQL server with Docker, you need to create a docker-compose.yml file in the root directory of your project and include the following Docker Compose configurations:

docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:latest
    container_name: mysql
    env_file:
      - ./.env
    ports:
      - '6500:3306'
    volumes:
      - mysqlDB:/var/lib/mysql
volumes:
  mysqlDB:

In the above configuration code, you may have noticed that the credentials required to build the MySQL image are obtained from a .env file using the env_file flag.

Therefore, to provide the necessary credentials, create a .env file in the project’s root directory and add the following environment variables. These variables will be used by Docker Compose to build the MySQL image and configure the container.

.env

MYSQL_DATABASE=rust_sqlx
MYSQL_USER=admin
MYSQL_PASSWORD=password123
MYSQL_ROOT_PASSWORD=password123

# Don't worry about the placeholders. Rust supports this feature.
DATABASE_URL=mysql://${MYSQL_USER}:${MYSQL_PASSWORD}@localhost:6500/${MYSQL_DATABASE}

Now that we’ve set up the MySQL server configurations, it’s time to bring it to life! To do so, run the command docker-compose up -d. This will start the MySQL server and make it available on port 6500.

And don’t worry, if you ever need to take the server down, simply run docker-compose down and the container will be removed. It’s that easy!

Connect the Axum Server to the MySQL Server

To interact with the MySQL server and perform database queries and mutations, we need to create connection pools using the SQLX toolkit. We can achieve this by replacing the existing code in our src/main.rs file with the following:

src/main.rs

use std::sync::Arc;

use axum::{response::IntoResponse, routing::get, Json, Router};
use dotenv::dotenv;
use sqlx::mysql::{MySqlPool, MySqlPoolOptions};

pub struct AppState {
    db: MySqlPool,
}

#[tokio::main]
async fn main() {
    dotenv().ok();

    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let pool = match MySqlPoolOptions::new()
        .max_connections(10)
        .connect(&database_url)
        .await
    {
        Ok(pool) => {
            println!("✅Connection to the database is successful!");
            pool
        }
        Err(err) => {
            println!("🔥 Failed to connect to the database: {:?}", err);
            std::process::exit(1);
        }
    };

    let app = Router::new()
        .route("/api/healthchecker", get(health_checker_handler))
        .with_state(Arc::new(AppState { db: pool.clone() }));

    println!("🚀 Server started successfully");
    let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await.unwrap();
    axum::serve(listener, app).await.unwrap()
}

async fn health_checker_handler() -> impl IntoResponse {
    const MESSAGE: &str = "Rust CRUD API Example with Axum Framework and MySQL";

    let json_response = serde_json::json!({
        "status": "success",
        "message": MESSAGE
    });

    Json(json_response)
}

In the code above, we first used the dotenv crate to load environment variables into the Rust runtime. Next, we retrieved the MySQL database connection URL using the std::env::var() function and stored it in the database_url variable.

Then, we created 10 connection pools to the MySQL database using SQLX and added the connection pool to the AppState struct. We used Rust’s smart pointer called Arc to store the connection pool and provided it to the with_state() method to attach it to the router. This allows the application to utilize the connection pool for querying or modifying the database.

Perform Database Migrations

To ensure that our database schema is in sync with the SQL queries we’ll be using, we need to run database migrations on the MySQL server. Fortunately, SQLx provides a command-line utility that generates migration files and applies them to the database.

To get started, simply run the command cargo install sqlx-cli to install the SQLx-CLI tool if you haven’t already done so. Then, use the following command to generate reversible migrations with corresponding “up” and “down” scripts in the root directory:

sqlx migrate add -r init

As we’re building an API for a note-taking application, we’ll need to create a ‘notes‘ table in the database. To do this, simply open the ‘up‘ script in the migrations folder and add the following SQL code:

migrations/20230417143142_init.up.sql

-- Add up migration script here

CREATE TABLE
    IF NOT EXISTS notes (
        id CHAR(36) PRIMARY KEY NOT NULL,
        title VARCHAR(255) NOT NULL UNIQUE,
        content TEXT NOT NULL,
        category VARCHAR(100),
        published BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

To ensure that the database schema can be safely rolled back to a previous state, it’s important to create a corresponding ‘down‘ migration script. In the migrations folder, locate the ‘down‘ file that corresponds to the ‘up‘ migration file and add the following SQL code to it. This code will remove the notes table:

migrations/20230417143142_init.down.sql

-- Add down migration script here

DROP TABLE IF EXISTS notes;

After creating the ‘up‘ and ‘down‘ migration scripts, you can now apply the ‘up‘ script to the MySQL database by executing the command sqlx migrate run.

If you log in to the MySQL server using the credentials provided in the .env file and navigate to the tables section of the rust_sqlx database, you will notice the newly added notes table generated by SQLx-CLI. Additionally, you will find a _sqlx_migrations table, which SQLx-CLI uses to keep track of the applied migrations.

If you ever want to undo the changes made by the ‘up‘ script, you can revert the migration by running sqlx migrate revert. This command will apply the ‘down‘ script to remove the ‘notes‘ table.

Define the SQLX Database Model

Now that we have successfully applied the migration scripts to our database, we can now proceed to create a SQLX database model for the ‘notes‘ table. To achieve this, we will use a struct that implements the sqlx::FromRow trait, which allows us to map each column of the table to a corresponding field in the struct.

To ensure seamless mapping, it is crucial to name the fields of the struct after their corresponding columns in the notes table. Additionally, we need to consider that MySQL does not have a built-in boolean type, so we’ll represent the tinyint type of the published field as an i8.

To ensure that the JSON response for the published field has a Boolean value rather than 0 or 1, we’ll create a new struct that has the same fields as the SQLX database model. However, for the published field of this struct, we’ll use a Boolean type instead of an i8. Later on, we’ll write a utility function that will convert the SQLX database model to this new struct.

To define the database model, create a file named model.rs in the src directory and include the following struct definitions.

src/model.rs

use serde::{Deserialize, Serialize};

#[derive(Debug, Deserialize, Serialize, sqlx::FromRow)]
#[allow(non_snake_case)]
pub struct NoteModel {
    pub id: String,
    pub title: String,
    pub content: String,
    pub category: Option<String>,
    pub published: i8,
    pub created_at: Option<chrono::DateTime<chrono::Utc>>,
    pub updated_at: Option<chrono::DateTime<chrono::Utc>>,
}

#[derive(Debug, Deserialize, Serialize)]
#[allow(non_snake_case)]
pub struct NoteModelResponse {
    pub id: String,
    pub title: String,
    pub content: String,
    pub category: String,
    pub published: bool,
    pub createdAt: chrono::DateTime<chrono::Utc>,
    pub updatedAt: chrono::DateTime<chrono::Utc>,
}

Define the API Request Structs

We’re almost ready to implement the CRUD operations for our API. The next step is to create structs that will be used to deserialize the request parameters and bodies in the Axum route functions. These structs not only handle deserialization but also ensure that the required fields are included in the JSON object.

To get started, let’s create a schema.rs file in the ‘src‘ directory and add the following structs:

src/schema.rs

use serde::{Deserialize, Serialize};

#[derive(Deserialize, Debug, Default)]
pub struct FilterOptions {
    pub page: Option<usize>,
    pub limit: Option<usize>,
}

#[derive(Deserialize, Debug)]
pub struct ParamOptions {
    pub id: String,
}

#[derive(Serialize, Deserialize, Debug)]
pub struct CreateNoteSchema {
    pub title: String,
    pub content: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub category: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub published: Option<bool>,
}

#[derive(Serialize, Deserialize, Debug)]
pub struct UpdateNoteSchema {
    pub title: Option<String>,
    pub content: Option<String>,
    pub category: Option<String>,
    pub published: Option<bool>,
}

Implement the CRUD Functionalities

Whew! We’ve written quite a bit of code to get to this point. Now, it’s time to create Axum route functions to handle the CRUD requests. These route functions will act as higher-level CRUD functions, providing a more user-friendly interface for accessing the database.

To get started, let’s create a new file named handler.rs in the ‘src‘ folder and add the following crates and dependencies.

src/handler.rs

use std::sync::Arc;

use axum::{
    extract::{Path, Query, State},
    http::StatusCode,
    response::IntoResponse,
    Json,
};
use serde_json::json;

use crate::{
    model::{NoteModel, NoteModelResponse},
    schema::{CreateNoteSchema, FilterOptions, UpdateNoteSchema},
    AppState,
};

Next, we’ll create a utility function called filter_db_record that will take the NoteModel struct as an argument and convert it to the NoteModelResponse struct. This will allow us to convert the i8 value of the published field to a boolean value, as discussed earlier.

To add this function to our project, simply include the following code in the src/handler.rs file:

src/handler.rs

fn filter_db_record(note: &NoteModel) -> NoteModelResponse {
    NoteModelResponse {
        id: note.id.to_owned(),
        title: note.title.to_owned(),
        content: note.content.to_owned(),
        category: note.category.to_owned().unwrap(),
        published: note.published != 0,
        createdAt: note.created_at.unwrap(),
        updatedAt: note.updated_at.unwrap(),
    }
}

Route Function to Fetch All Records

Let’s implement the first CRUD operation to retrieve a list of notes from the database and return them as a JSON response. We’ll do this by creating an Axum route function that will be triggered when a GET request is made to the /api/notes?page=1&limit=10 endpoint.

In order to avoid excessive data being sent in the JSON response, especially when the database contains a significant number of notes, we’ll be implementing pagination in this route function.

When the function is called, it will first extract the query parameters and use them to calculate the limit and offset values for the SQL query.

Next, it will use the sqlx::query_as! macro to query the database and retrieve a list of notes from the ‘notes‘ table in the database, ordered by ID, and limited by the limit and offset values. The query result will be mapped to Vec<NoteModel>.

If the database query fails, the function will return an error response containing a JSON object with the error message. However, if everything goes as planned, the function will iterate over the notes vector and create a new note_responses vector by mapping each NoteModel object to a NoteModelResponse object using the filter_db_record function. This will convert the published field from an i8 to a boolean type.

Finally, the function will construct a JSON response object containing the notes, the number of notes, and a status, and return it.

To implement this route function, add the following code to src/handler.rs:

src/handler.rs

pub async fn note_list_handler(
    opts: Option<Query<FilterOptions>>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let Query(opts) = opts.unwrap_or_default();

    let limit = opts.limit.unwrap_or(10);
    let offset = (opts.page.unwrap_or(1) - 1) * limit;

    let notes = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes ORDER by id LIMIT ? OFFSET ?"#,
        limit as i32,
        offset as i32
    )
    .fetch_all(&data.db)
    .await
    .map_err(|e| {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Database error: {}", e),
        });
        (StatusCode::INTERNAL_SERVER_ERROR, Json(error_response))
    })?;

    let note_responses = notes
        .iter()
        .map(|note| filter_db_record(&note))
        .collect::<Vec<NoteModelResponse>>();

    let json_response = serde_json::json!({
        "status": "success",
        "results": note_responses.len(),
        "notes": note_responses
    });

    Ok(Json(json_response))
}

Route Function to Insert a Record

The create_note_handler is the next Axum route function to be created, and it is responsible for handling the creation of a new note in the database. This function will be triggered when a POST request is made to the /api/notes/ endpoint.

Upon invocation, the function generates a new UUID to use as the ID of the note to be created. This is important because MySQL does not return the record inserted into the database, and we need to keep track of the ID so that we can fetch the newly-inserted record and return it.

The function then uses the SQL query builder from SQLX to insert the new note into the database with the generated UUID as the primary key.

If an unexpected error or conflict error occurs during the operation, a JSON response with the appropriate error message will be returned.

However, if the insertion is successful, the function performs another query to retrieve the newly created note using the UUID.

Finally, the function calls the filter_db_record function on the note so that the published field can be converted from an i8 to a boolean and returns the result in a JSON response.

To implement this route function, add the code below to the src/handler.rs file:

src/handler.rs

pub async fn create_note_handler(
    State(data): State<Arc<AppState>>,
    Json(body): Json<CreateNoteSchema>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let user_id = uuid::Uuid::new_v4().to_string();
    let query_result =
        sqlx::query(r#"INSERT INTO notes (id,title,content,category) VALUES (?, ?, ?, ?)"#)
            .bind(user_id.clone())
            .bind(body.title.to_string())
            .bind(body.content.to_string())
            .bind(body.category.to_owned().unwrap_or_default())
            .execute(&data.db)
            .await
            .map_err(|err: sqlx::Error| err.to_string());

    if let Err(err) = query_result {
        if err.contains("Duplicate entry") {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": "Note with that title already exists",
            });
            return Err((StatusCode::CONFLICT, Json(error_response)));
        }

        return Err((
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", err)})),
        ));
    }

    let note = sqlx::query_as!(NoteModel, r#"SELECT * FROM notes WHERE id = ?"#, user_id)
        .fetch_one(&data.db)
        .await
        .map_err(|e| {
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            )
        })?;

    let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
        "note": filter_db_record(&note)
    })});

    Ok(Json(note_response))
}

Route Function to Retrieve a Record

To retrieve a specific note from the database based on an ID, we’ll create an Axum route function for handling GET requests to the /api/notes/:id endpoint.

When this function is called, it will extract the ID of the note to be retrieved from the request parameter and use the query_as! macro from the SQLx library to send a SQL query to the database. If the query is successful, it will retrieve the note from the query result and create a JSON response containing the note data.

In case the note is not found, the function will generate a JSON response with a message indicating that the note was not found and return an HTTP 404 status code.

src/handler.rs

pub async fn get_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await;

    match query_result {
        Ok(note) => {
            let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
                "note": filter_db_record(&note)
            })});

            return Ok(Json(note_response));
        }
        Err(sqlx::Error::RowNotFound) => {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": format!("Note with ID: {} not found", id)
            });
            return Err((StatusCode::NOT_FOUND, Json(error_response)));
        }
        Err(e) => {
            return Err((
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            ));
        }
    };
}

Route Function to Edit a Record

Let’s create a route function that will edit a note in the database when a PATCH request is made to the /api/notes/:id endpoint.

When the function is called, it will begin by querying the database for the note with the ID provided in the query parameter using the SQLx’s query_as! macro. If the query is successful, the note will be retrieved and stored in the ‘note‘ variable.

Afterward, the function will send another query to update the note with the new information provided in the request body. If the update is successful and at least one row is affected, the function will retrieve the updated note from the database and create a JSON response containing the updated note.

If the update fails because no row with the given ID is found, the function will create a JSON response indicating the failure and return an HTTP 404 status code.

To implement this route function, you can add the following code to the src/handler.rs file:

src/handler.rs

pub async fn edit_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
    Json(body): Json<UpdateNoteSchema>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await;

    let note = match query_result {
        Ok(note) => note,
        Err(sqlx::Error::RowNotFound) => {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": format!("Note with ID: {} not found", id)
            });
            return Err((StatusCode::NOT_FOUND, Json(error_response)));
        }
        Err(e) => {
            return Err((
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            ));
        }
    };

    let published = body.published.unwrap_or(note.published != 0);
    let i8_publised = published as i8;

    let update_result = sqlx::query(
        r#"UPDATE notes SET title = ?, content = ?, category = ?, published = ? WHERE id = ?"#,
    )
    .bind(body.title.to_owned().unwrap_or_else(|| note.title.clone()))
    .bind(
        body.content
            .to_owned()
            .unwrap_or_else(|| note.content.clone()),
    )
    .bind(
        body.category
            .to_owned()
            .unwrap_or_else(|| note.category.clone().unwrap()),
    )
    .bind(i8_publised)
    .bind(id.to_string())
    .execute(&data.db)
    .await
    .map_err(|e| {
        (
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", e)})),
        )
    })?;

    if update_result.rows_affected() == 0 {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Note with ID: {} not found", id)
        });
        return Err((StatusCode::NOT_FOUND, Json(error_response)));
    }

    let updated_note = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await
    .map_err(|e| {
        (
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", e)})),
        )
    })?;

    let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
        "note": filter_db_record(&updated_note)
    })});

    Ok(Json(note_response))
}

Route Function to Delete a Record

The last route function to be implemented in the src/handler.rs file is responsible for handling DELETE requests to the /api/notes/:id endpoint.

When this function is called, it will extract the ID of the note to be deleted from the request parameter and execute a SQL DELETE query to remove the note with the given ID from the database.

If the deletion is successful, the function will check if any rows were affected by the deletion. If no rows were affected, it will create a JSON response with a “not found” message and return an HTTP 404 status code.

However, if the deletion is successful and at least one row was affected, the function will return an HTTP 204 status code, indicating that the request was successful but there is no content to return in the response body.

src/handler.rs

pub async fn delete_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query!(r#"DELETE FROM notes WHERE id = ?"#, id.to_string())
        .execute(&data.db)
        .await
        .map_err(|e| {
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            )
        })?;

    if query_result.rows_affected() == 0 {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Note with ID: {} not found", id)
        });
        return Err((StatusCode::NOT_FOUND, Json(error_response)));
    }

    Ok(StatusCode::NO_CONTENT)
}

The Complete Code of the Route Functions

src/handler.rs

use std::sync::Arc;

use axum::{
    extract::{Path, Query, State},
    http::StatusCode,
    response::IntoResponse,
    Json,
};
use serde_json::json;

use crate::{
    model::{NoteModel, NoteModelResponse},
    schema::{CreateNoteSchema, FilterOptions, UpdateNoteSchema},
    AppState,
};

pub async fn health_checker_handler() -> impl IntoResponse {
    const MESSAGE: &str = "Rust CRUD API Example with Axum Framework and MySQL";

    let json_response = serde_json::json!({
        "status": "success",
        "message": MESSAGE
    });

    Json(json_response)
}

pub async fn note_list_handler(
    opts: Option<Query<FilterOptions>>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let Query(opts) = opts.unwrap_or_default();

    let limit = opts.limit.unwrap_or(10);
    let offset = (opts.page.unwrap_or(1) - 1) * limit;

    let notes = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes ORDER by id LIMIT ? OFFSET ?"#,
        limit as i32,
        offset as i32
    )
    .fetch_all(&data.db)
    .await
    .map_err(|e| {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Database error: {}", e),
        });
        (StatusCode::INTERNAL_SERVER_ERROR, Json(error_response))
    })?;

    let note_responses = notes
        .iter()
        .map(|note| filter_db_record(&note))
        .collect::<Vec<NoteModelResponse>>();

    let json_response = serde_json::json!({
        "status": "success",
        "results": note_responses.len(),
        "notes": note_responses
    });

    Ok(Json(json_response))
}

pub async fn create_note_handler(
    State(data): State<Arc<AppState>>,
    Json(body): Json<CreateNoteSchema>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let user_id = uuid::Uuid::new_v4().to_string();
    let query_result =
        sqlx::query(r#"INSERT INTO notes (id,title,content,category) VALUES (?, ?, ?, ?)"#)
            .bind(user_id.clone())
            .bind(body.title.to_string())
            .bind(body.content.to_string())
            .bind(body.category.to_owned().unwrap_or_default())
            .execute(&data.db)
            .await
            .map_err(|err: sqlx::Error| err.to_string());

    if let Err(err) = query_result {
        if err.contains("Duplicate entry") {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": "Note with that title already exists",
            });
            return Err((StatusCode::CONFLICT, Json(error_response)));
        }

        return Err((
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", err)})),
        ));
    }

    let note = sqlx::query_as!(NoteModel, r#"SELECT * FROM notes WHERE id = ?"#, user_id)
        .fetch_one(&data.db)
        .await
        .map_err(|e| {
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            )
        })?;

    let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
        "note": filter_db_record(&note)
    })});

    Ok(Json(note_response))
}

pub async fn get_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await;

    match query_result {
        Ok(note) => {
            let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
                "note": filter_db_record(&note)
            })});

            return Ok(Json(note_response));
        }
        Err(sqlx::Error::RowNotFound) => {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": format!("Note with ID: {} not found", id)
            });
            return Err((StatusCode::NOT_FOUND, Json(error_response)));
        }
        Err(e) => {
            return Err((
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            ));
        }
    };
}

pub async fn edit_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
    Json(body): Json<UpdateNoteSchema>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await;

    let note = match query_result {
        Ok(note) => note,
        Err(sqlx::Error::RowNotFound) => {
            let error_response = serde_json::json!({
                "status": "fail",
                "message": format!("Note with ID: {} not found", id)
            });
            return Err((StatusCode::NOT_FOUND, Json(error_response)));
        }
        Err(e) => {
            return Err((
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            ));
        }
    };

    let published = body.published.unwrap_or(note.published != 0);
    let i8_publised = published as i8;

    let update_result = sqlx::query(
        r#"UPDATE notes SET title = ?, content = ?, category = ?, published = ? WHERE id = ?"#,
    )
    .bind(body.title.to_owned().unwrap_or_else(|| note.title.clone()))
    .bind(
        body.content
            .to_owned()
            .unwrap_or_else(|| note.content.clone()),
    )
    .bind(
        body.category
            .to_owned()
            .unwrap_or_else(|| note.category.clone().unwrap()),
    )
    .bind(i8_publised)
    .bind(id.to_string())
    .execute(&data.db)
    .await
    .map_err(|e| {
        (
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", e)})),
        )
    })?;

    if update_result.rows_affected() == 0 {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Note with ID: {} not found", id)
        });
        return Err((StatusCode::NOT_FOUND, Json(error_response)));
    }

    let updated_note = sqlx::query_as!(
        NoteModel,
        r#"SELECT * FROM notes WHERE id = ?"#,
        id.to_string()
    )
    .fetch_one(&data.db)
    .await
    .map_err(|e| {
        (
            StatusCode::INTERNAL_SERVER_ERROR,
            Json(json!({"status": "error","message": format!("{:?}", e)})),
        )
    })?;

    let note_response = serde_json::json!({"status": "success","data": serde_json::json!({
        "note": filter_db_record(&updated_note)
    })});

    Ok(Json(note_response))
}

pub async fn delete_note_handler(
    Path(id): Path<uuid::Uuid>,
    State(data): State<Arc<AppState>>,
) -> Result<impl IntoResponse, (StatusCode, Json<serde_json::Value>)> {
    let query_result = sqlx::query!(r#"DELETE FROM notes WHERE id = ?"#, id.to_string())
        .execute(&data.db)
        .await
        .map_err(|e| {
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(json!({"status": "error","message": format!("{:?}", e)})),
            )
        })?;

    if query_result.rows_affected() == 0 {
        let error_response = serde_json::json!({
            "status": "fail",
            "message": format!("Note with ID: {} not found", id)
        });
        return Err((StatusCode::NOT_FOUND, Json(error_response)));
    }

    Ok(StatusCode::NO_CONTENT)
}

fn filter_db_record(note: &NoteModel) -> NoteModelResponse {
    NoteModelResponse {
        id: note.id.to_owned(),
        title: note.title.to_owned(),
        content: note.content.to_owned(),
        category: note.category.to_owned().unwrap(),
        published: note.published != 0,
        createdAt: note.created_at.unwrap(),
        updatedAt: note.updated_at.unwrap(),
    }
}

Create an Axum Router for the Route Functions

Creating the Axum route functions is one piece of the puzzle. We also need to create a router to connect the routes to the corresponding handler functions. While we could put this logic in the main.rs file, it’s better to keep things organized and create a new file named route.rs for this purpose.

Inside this file, we will define a function that creates a new Axum router and add the CRUD route endpoints to it, which will then invoke the appropriate handler functions. To ensure that the AppState, which contains the MySQL database connection pool, is available to all the router functions, we’ll attach it to the router using the with_state method.

To get started, create a new route.rs file in the ‘src‘ directory and add the following code.

src/route.rs

use std::sync::Arc;

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

use crate::{
    handler::{
        create_note_handler, delete_note_handler, edit_note_handler, get_note_handler,
        health_checker_handler, note_list_handler,
    },
    AppState,
};

pub fn create_router(app_state: Arc<AppState>) -> Router {
    Router::new()
        .route("/api/healthchecker", get(health_checker_handler))
        .route("/api/notes/", post(create_note_handler))
        .route("/api/notes", get(note_list_handler))
        .route(
            "/api/notes/:id",
            get(get_note_handler)
                .patch(edit_note_handler)
                .delete(delete_note_handler),
        )
        .with_state(app_state)
}

Register the Axum Router and Set up CORS

We need to register all the routes by calling the create_router() function in the main() function of the src/main.rs file. However, before we can do that, we need to ensure that all the necessary modules are imported into src/main.rs to avoid any errors.

Once that’s done, we can proceed to register the routes and prepare our server to accept cross-origin requests from specified origins, configure allowed HTTP methods and headers, and determine whether cookies are allowed or not. This is done using CORS middleware, which we’ll apply to the router using the layer() method.

To implement all of these features, simply open the src/main.rs file and replace its current contents with the code provided below.

src/main.rs

mod handler;
mod model;
mod route;
mod schema;

use std::sync::Arc;

use axum::http::{
    header::{ACCEPT, AUTHORIZATION, CONTENT_TYPE},
    HeaderValue, Method,
};
use dotenv::dotenv;
use route::create_router;
use tower_http::cors::CorsLayer;

use sqlx::mysql::{MySqlPool, MySqlPoolOptions};

pub struct AppState {
    db: MySqlPool,
}

#[tokio::main]
async fn main() {
    dotenv().ok();

    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let pool = match MySqlPoolOptions::new()
        .max_connections(10)
        .connect(&database_url)
        .await
    {
        Ok(pool) => {
            println!("✅Connection to the database is successful!");
            pool
        }
        Err(err) => {
            println!("🔥 Failed to connect to the database: {:?}", err);
            std::process::exit(1);
        }
    };

    let cors = CorsLayer::new()
        .allow_origin("http://localhost:3000".parse::<HeaderValue>().unwrap())
        .allow_methods([Method::GET, Method::POST, Method::PATCH, Method::DELETE])
        .allow_credentials(true)
        .allow_headers([AUTHORIZATION, ACCEPT, CONTENT_TYPE]);

    let app = create_router(Arc::new(AppState { db: pool.clone() })).layer(cors);

    println!("🚀 Server started successfully");
    let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await.unwrap();
    axum::serve(listener, app).await.unwrap()
}

Congratulations, you’ve completed the setup of the CRUD API in Rust using the Axum framework with MySQL integration!

Now, to test the functionality of our API, we need to send some HTTP requests to it. To avoid the tedious task of manually typing the request URLs, setting HTTP methods, and inputting the request bodies, I’ve included a Postman collection named Note App.postman_collection.json in the source code of this project.

You can download or clone the source code from https://github.com/wpcodevo/rust-axum-mysql and import the collection into Postman or the Thunder Client VS Code extension to access it.

Once you have the collection, you can easily test the CRUD operations of the API by sending the appropriate requests. If you prefer a visual approach, you can follow the instructions in the “Run the Axum CRUD API with a Frontend App” section to set up a React.js app that supports CRUD operations.

Conclusion

This article has guided you through the process of setting up a MySQL server with Docker, performing database migrations using the SQLx-CLI utility, and executing CRUD operations on a MySQL database using the SQLx toolkit.

I hope you found this tutorial useful and informative. If you have any feedback or questions, feel free to leave them in the comments section, and I’ll be sure to respond promptly.