SQLx integration in Axum

SQLx is an awesome crate for interacting with databases in Rust with compile time checks. In this blog post, we will learn how to use it in an Axum backend to store and retrieve data from a database.

The example will be storing submitted contact forms from the previous post about Axum.

During the SQLx integration, we will learn about states and returning JSON in Axum.

Landscape mode recommended on mobile devices

Disclaimer

This post is not about SQL. Therefore, the details of SQL statements will not be explained.

The post uses unwrap and expect to focus on the main concepts. In a real project, you should do proper error handling!

I will assume that you are familiar with the first few chapters of the Rust book and know the basics of async Rust with tokio.

Base project

The example extends the small Axum backend built in the blog post about Axum. Use the code of that post as a starting point.

You can clone the whole repository and start extending in the getting-started-with-rust-backends directory.

SQLx preparation

The steps in this section are analogous to the blog post about SQLx where they are explained properly.

First, run a PostgreSQL database postgres at the host 127.0.0.1 (localhost) on port 5432 with the username postgres and the password CHANGE_ME.

Now, tell SQLx how to connect to the database by storing the following environment variable in the file .env:

DATABASE_URL=postgresql://postgres:CHANGE_ME@127.0.0.1:5432/postgres

Now, let's make sure that we can connect to the database and that it is created using the SQLx-CLI:

sqlx database create

If the previous command returns any error, check your database connection before continuing!

We want to create a table in the database that contains the content of our contact form:

• Non-optional name
• Non-optional email
• Non-optional message

The fields being non-optional means that they can't be NULL in the database.

Let's create such a table named submissions.

First, create the first migration:

sqlx migrate add create_submissions_table

The previous command creates the new file TIMESTAMP_create_submissions_table.sql where TIMESTAMP is the Unix timestamp of the migration. Place the following SQL statement in the created SQL file:

CREATE TABLE IF NOT EXISTS submissions (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    message TEXT NOT NULL
);

The id having the type BIGSERIAL means that it will be automatically incremented so we don't have to specify it when inserting to the table.

Run the migration using the following command:

sqlx migrate run

Finally let's run the following command to trigger recompilation after adding possible later migrations:

sqlx migrate build-script

The SQLx preparation is done 🎉 We can start coding now 🦀

Dependencies

To follow the example, add the following additional dependencies to Cargo.toml of the base project:

[dependencies]
dotenvy = "0.15.7"
sqlx = { version = "0.6.3", features = ["postgres", "macros", "runtime-tokio-rustls"] }

Imports

All additional imports used in the example are gathered below to keep later code snippets slim:

use axum::{extract::State, Json};
use serde::Serialize;
use sqlx::{postgres::PgPoolOptions, PgPool};
use std::{env, sync::Arc};

States

How do we want to integrate SQLx into our base project?

The naive idea would be to create a new database connection inside each handler. But this would be very inefficient since creating a database connection is expensive!

Instead, we should create a connection pool and share it with all handlers! But how can we make such a pool accessible by all handlers? Do we need something like global variables? Fortunately, no!

Axum provides states for sharing across handlers.

A state is a struct that implements Clone. It has to implement Clone because Axum clones it for every handler call.

The state that we want to create is a struct containing one field which is the connection pool:

struct AppStateInner {
    pool: PgPool,
}

Let's define a constructor for our struct:

impl AppStateInner {
    async fn new() -> Self {
        dotenvy::dotenv().expect("Could not load the .env file!");
        let database_url =
            env::var("DATABASE_URL").expect("The environment variable DATABASE_URL is missing!");

        let pool = PgPoolOptions::new()
            .connect(&database_url)
            .await
            .expect("Failed to connect to the database!");

        Self { pool }
    }
}

The constructor adds the variable DATABASE_URL from the .env file to the environment variables and creates the pool as described in the post about SQLx.

Our state is almost ready...

Did you ask yourself why the struct is called AppStateInner? 🤔

We have said that a state has to implement Clone, but our struct does not implement it yet!

In general, we have to wrap our struct with an Arc for atomic reference counting. Cloning an Arc of a struct is like cloning a pointer to the struct and incrementing the atomic reference counter by one.

To wrap our struct with an Arc, we define the type alias AppState:

type AppState = Arc<AppStateInner>;

Now, in our main function, we initialize the state and provide it for the handlers using the method with_state:

let state = Arc::new(AppStateInner::new().await);

let router = Router::new()
    .route("/", get(index))
    .route("/submit", post(submit))
    .with_state(state);

To receive the state in the submit handler, we change its signature to the following by adding the state as State<AppState>:

async fn submit(state: State<AppState>, fields: Form<FormFields>) -> Response

The type State is an Axum extractor that extracts a state if it was provided to the router of this handler (checked at compile time).

Inserting into the database

We want to insert a form submission into the database in the submit handler. In this handler, we receive the submission as a Form with fields in the following struct:

struct FormFields {
    name: String,
    email: String,
    message: String,
}

We want to implement a function for this struct that takes a reference to the connection pool and inserts the fields as a row in the submissions table:

impl FormFields {
    async fn insert_into_db(&self, pool: &PgPool) {
        sqlx::query!(
            "INSERT INTO submissions(name, email, message) VALUES ($1, $2, $3)",
            &self.name,
            &self.email,
            &self.message
        )
        .execute(pool)
        .await
        .expect("Failed to insert a submission into the database!");

        println!("Inserted a submission into the database!");
    }
}

If the insertion was successful, a message is printed out.

Now, let's use this function in the submit handler! Replace the pritnln expression that prints out the form fields in the handler with the following:

fields.insert_into_db(&state.pool).await;

Run the program, visit 127.0.0.1:8080 and submit a form. If you see "Inserted a submission into the database!" printed out in the terminal, then congratulations, you did successfully run your first SQLx query in Axum 🎉

Reading from the database

How can we see the submissions in the database?

Let's add a handler that returns the list of all submissions as JSON (in an API style).

First, we need a function that returns the list of submissions from the database. Add the following function to the implementation of FormFields:

async fn get_all_submissions(pool: &PgPool) -> Vec<Self> {
    sqlx::query_as!(Self, "SELECT name, email, message FROM submissions")
        .fetch_all(pool)
        .await
        .expect("Failed to get submissions from the database!")
}

It takes a reference to a connection pool and runs a query. The output is a vector of FormFields.

To use this function in a handler, add the route "/all-submissions" first:

let router = Router::new()
    .route("/", get(index))
    .route("/submit", post(submit))
    .route("/all-submissions", get(all_submissions))
    .with_state(state);

Now, implement the handler all_submissions:

async fn all_submissions(state: State<AppState>) -> Json<Vec<FormFields>> {
    Json(FormFields::get_all_submissions(&state.pool).await)
}

But for returning a struct as JSON, the struct has to implement Serialize.

Vec<T> implements Serialize if T implements Serialize. Therefore, we have to derive Serialize for our struct (in addition to Deserialize which is needed for the Form extractor):

#[derive(Deserialize, Serialize)]
struct FormFields {
    ...
}

Run the program and visit the URL 127.0.0.1:8080/all-submissions to see the JSON list of your submissions 🤩

The output will look like the following:

[
  {
    "name": "Ferris",
    "email": "ferris@rust-lang.org",
    "message": "Are we web yet?"
  },
  {
    "name": "Mo",
    "email": "mo8it@proton.me",
    "message": "Any feedback for this blog post? :D"
  }
]

Summary

Axum + SQLx = 🚀

Let's dominate web backends with Rust, what are you waiting for? 🤓

Full code

The full code used in this post can be found here.