Build CRUD REST API with Rust 🦀and MySQL using Axum & SQLx (Rusty API Part 1)

7 min readJan 12, 2024

🦀Rust Language 🦀have been hype in the few last year. So, let’s go straight to create a Rust Project for Simple CRUD REST API with Axum and SQLx.

Kindly remind me on comment if there any newer version of Rust. I’ll try update to the latest.

Project Requirement & Scope

Let create a REST API Services for managing Notes. This service will handle to perform Create, Read, Update and Delete Notes, and also listing all notes with simple paginating.

I write and build this with (updated!):
- Rust 1.80.1

My Recommended IDE & Plugins when building this:
- VSCode
- VSCode Plugins: Rust Analyzer (Rust support language for VSCode)
- VSCode Plugins: REST Client (a Simple sending HTTP Request to testing our REST API)

Our Final project structure will be looks like this:

Set-up Rust Server with Axum

After installing rust (I’m assume you guys already install rust or see the guide here), let’s create & initialize the project and set-up server with axum

# Init Project
cargo init raditzlawliet_rust-note-rest

Command above will create a folder and a new rust project inside. Then, we need add a dependency will be used in this project

# Depedency
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 running command above, the cargo.toml files will looks like this (Updated!)

Then, Let’s write our first API Service. Open main.rs and modify into this

Code above will run a server with single API api/healthcheck and return a JSON response. Let’s try to run it out using

# Build & Run Project
cargo build
cargo run

The result may looks like this

Now you can test to call this API to http://127.0.0.1:8080/api/healthcheck

AND, our Rust Server API Service is complete!

Additionally, we can also re-run automatically every time the code change using cargo-watch

# CLI For Watch source when running & Automatically rebuild the project
cargo install cargo-watch

# Run with watch the src/
cargo watch -q -c -w src/ -x run

Set-up MySQL Server & Connect it with SQLx

Let set-up MySQL, you can use your own MySQL or using Docker instead.

For Docker let create new environment file .env and docker compose file docker-compose.yml

Then run it to start MySQL on root project or in folder where docker-compose.yml file located.

# Run Docker Compose & Detach
docker compose up -d

# (Bonus! for stopping MySQL Docker)
docker compose stop

Ok, Now update main.rs to be able connect the MySQL

Above code should looks like above figure. In this section we have able to connect our Rust Axum Server to MySQL and use that connection to perform our CRUD handler later.

Database Migration

Create a Notes Table, you can use other method, but now let try to using migration with sqlx-cli. Install sqlx-cli and create a migration file

# CLI For migration
cargo install sqlx-cli

# create a migration
sqlx migrate add -r create_notes_table

Command above will create 2 migration file for Up and Down/Revert. Update them to create notes table and drop notes table

Then we can run the migration via sqlx-cli (And if you need to revert, you can also do that). Finally our database is ready to be handle (you can check using Visual Tools like DBeaver or MySQL Workbench or else)

# perform migration up
sqlx migrate run

# (Bonus!, perform migration down/revert)
sqlx migrate revert

Define SQLx Model & Response

Create model.rs and define struct the Notes Model DB and Notes Model Response. Later this model will be use for retrieve data from MySQL and parse to NoteModel, then we can adjust to NoteModelResponse for JSON response.

# Create file model.rs
touch src/model.rs

Notable: for Nullable column, you can use type Option<T> so it value can be Null or T
Notable: BOOLEAN in MySQL is TINYINT(1) so we can use i8 to retrieve the record and later we can parse to Boolean

Define API Request Schema

Create schema.rs and define struct for Schema API Request

# Create schema.rs
touch src/schema.rs

API will be plan like this:

List will have pagination page and limit parameter and use FilterOptions e.g., GET http://127.0.0.1:8080/api/notes?page=0&limit=10
Create and Update will use CreateNoteSchema and UpdateNoteSchema
Read and Delete will have id param and directly put in handler function

Implement CRUD Functionality

Now we can create handler for all CRUD functionality and later we can attach them to our API.

Create handler.rs file

# create handler.rs
touch src/handler.rs

Fill the imports on top before creating each handler (for easiness) and one function to parsing NoteModel to NoteModelResponse

Query with and/or without Macro

Sqlx have query macro and query function (without macro), you can pick one best for your case.

sqlx::query_as!(
    NoteModel,
    r#"SELECT * FROM notes ORDER by id LIMIT ? OFFSET ?"#,
    limit as i32,
    offset as i32
)

Above example is query with macro.

Sqlx will check directly to database schema and compare. If your database is in wrong schema or offline, it can given an intellisense error like below

Another approach is using query without macro like below. It will not check directly to database schema. But use with caution and make sure database schema are already correct.

sqlx::query_as::<_, NoteModel>(
    r#"SELECT * FROM notes ORDER by id LIMIT ? OFFSET ?"#
)

Implement handler to Fetch All Records

Create a function to handle fetching all Notes records

Notable point step by step:

Validate limit& offset from param given.
Query with validated param and fetch all data and put into notes
Convert query result notes:NoteModel to Response Model NoteModelResponse and put into note_responses .
Create json_response and return it.

Implement handler to insert a record (Create/Insert)

Create a function to handle inserting a record

Notable point step by step:

Note is using UUID as ID. Therefore we need to create ID first with this let id = uuid::Uuid::new_v4().to_string(); .
Then, we do Insert Query with payload (JSON Request already transform automatically into CreateNoteScheme).
Then, we check error (e.g., Duplicate Record).
Get Inserted notes by Select data using ID we create first (optional).
Return data with JSON format response using serde_json::json!() macro.

Implement handler to get a record (Read)

Create a function to handle getting a single record by ID

Notable point step by step:

ID will automatically transformed from path route and passed to variable id .
Don’t forget to checking the error. e.g., Database error or No record found. Otherwise, return the Note data with JSON format.

Implement handler to update a record (Update)

create a function to handle updating a record by ID

Notable point step by step:

We do Select Query get single Note by ID.
Parsing data (if needed), in our case we need to parsing is_published from Boolean to i8 and vice versa.
Do Query update and check if there is an error.
Get updated note data and response it as JSON format.

Implement handler to delete a record (Delete)

create a function to handle deleting a record By ID

Quite straightforward, using id from route path, we do delete query by ID.
Then we can check the return, OK as success or NOT_FOUND when ID not found or no data deleted.

Register all handler to Axum Router

Our handler handler.rs should be looks like this, (it including moving the other handlers like health_check_handler)

Now we can create route.rs to implement our handler into route

# Create route.rs
touch src/route.rs

Then, update our main.rs to connect them all (and add CORS layer on it):

Finally! You can try to test them out using REST Client or Postman or else:

Create: POST http://localhost/8080/api/notes it will create a note with JSON Payload
List: GET http://localhost/8080/api/notes listing all notes
Get: GET http://localhost/8080/api/{ID} get a note by ID
Update: PATCH http://localhost/8080/api/notes/{ID} update note by ID with JSON Payload
Delete: DELETE http://localhost/8080/api/notes/{ID} delete a note by ID

Bonus! You can use REST Client, a plugins on VSCode to test HTTP, create test.http and test them out