Integrating Axum with PostgreSQL and SQLx: A Step-by-Step Guide

Amza

Amza

— 3 min read

Building web applications involves connecting your app to a database. In this tutorial, we'll walk through integrating Axum, a web framework for Rust, with a PostgreSQL database using the SQLx library. You'll learn how to set up an Axum server, configure a connection to a PostgreSQL database, and interact with it by querying data and handling responses.

By the end of this article, you’ll understand how to:

  • Set up Axum with SQLx
  • Connect to a PostgreSQL database
  • Perform a basic database query to read data

Let’s get started!

Step 1: Setup Your Environment

Before jumping into the code, ensure you have the following installed:

  • Rust – The programming language we're using for this tutorial.
  • PostgreSQL – The database we'll be connecting to.
  • Cargo – The Rust package manager, used to manage dependencies.

Install Dependencies

In your project, you'll need to add some dependencies. Here's the Cargo.toml configuration to get started:

[dependencies]
axum = "0.6"
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.6", features = ["postgres", "runtime-tokio-native-tls"] }
dotenv = "0.15"

Explanation:

  • Axum: The web framework for building our server.
  • Tokio: The async runtime needed for handling concurrency in Axum.
  • SQLx: A SQL library to interact with the PostgreSQL database asynchronously.
  • dotenv: For loading environment variables (like your database URL).

Step 2: Basic Axum Setup

We’ll start by setting up a basic Axum server that serves a simple "Hello, World!" endpoint. This gives us the foundation on which we’ll later build.

use axum::{routing::get, Router};
use std::net::SocketAddr;

#[tokio::main]
async fn main() {
    // Build our application with a simple GET route
    let app = Router::new().route("/", get(root));

    // Define the address to run the app on
    let addr = SocketAddr::from(([127, 0, 0, 1], 8080));

    // Start the server
    println!("Listening on {}", addr);
    axum::Server::bind(&addr)
        .serve(app.into_make_service())
        .await
        .unwrap();
}

// This is the handler function for our route
async fn root() -> &'static str {
    "Hello, World!"
}

Explanation:

  • Router: This sets up routing for our app. We define a route that listens for GET requests on / and responds with "Hello, World!".
  • SocketAddr: Defines the address (127.0.0.1:8080) for our server.
  • axum::Server::bind: Starts the server on the specified address.

Try it yourself:

Run the code and visit http://127.0.0.1:8080 in your browser. You should see "Hello, World!" displayed.

Step 3: Setting Up PostgreSQL Connection

Now, let’s integrate the database. First, we need to configure the connection to PostgreSQL.

Create a .env file in your project root with the following content (make sure you replace the values with your actual database credentials):

DATABASE_URL=postgres://postgres:mysecretpassword@localhost:5432/mydatabase

Explanation:

  • DATABASE_URL: Contains the connection string to your PostgreSQL database.

Now, update the main function to initialize the database connection using SQLx.

use axum::{routing::get, Router};
use sqlx::PgPool;
use std::net::SocketAddr;
use dotenv::dotenv;
use std::env;

#[tokio::main]
async fn main() {
    // Load environment variables from .env file
    dotenv().ok();
    
    // Get the database URL from the environment variables
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    // Create a connection pool for PostgreSQL
    let pool = PgPool::connect(&database_url).await.unwrap();

    // Build our application with a simple GET route
    let app = Router::new()
        .route("/", get(root))
        .route("/data", get(move || get_data(pool.clone()))); // Using closure to capture pool

    // Define the address to run the app on
    let addr = SocketAddr::from(([127, 0, 0, 1], 8080));

    // Start the server
    println!("Listening on {}", addr);
    axum::Server::bind(&addr)
        .serve(app.into_make_service())
        .await
        .unwrap();
}

async fn root() -> &'static str {
    "Hello, World!"
}

async fn get_data(pool: PgPool) -> String {
    // Query the database for data
    let row: (String,) = sqlx::query_as("SELECT message FROM greetings LIMIT 1")
        .fetch_one(&pool)
        .await
        .unwrap();

    row.0
}

Explanation:

  • PgPool::connect: Establishes the connection to the database using the connection URL stored in the .env file.
  • get_data: A simple function that runs an SQL query to fetch a message from a greetings table.

Challenge:

Before running the code, make sure the table greetings exists in your database. Here’s an example SQL statement to create the table:

CREATE TABLE greetings (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL
);

Insert some data into the table:

INSERT INTO greetings (message) VALUES ('Hello from the database!');

Step 4: Querying the Database

We’ve already added the basic database query inside the get_data function. Let’s break down what’s happening here:

  • sqlx::query_as: Executes a SQL query, and query_as maps the result to the specified type ((String,) in this case).
  • fetch_one: Fetches a single row from the query.

Explanation:

When you visit http://127.0.0.1:8080/data, the server will query the database for the message from the greetings table and return it as the response.

Try it yourself:

Run the server and go to http://127.0.0.1:8080/data. You should see the database message.

Step 5: Conclusion and Next Steps

Congrats! You’ve successfully integrated Axum with a PostgreSQL database using SQLx. Here's a summary of what we covered:

  • Setting up a basic Axum server.
  • Connecting to a PostgreSQL database using SQLx.
  • Running a query to retrieve data from the database.

What's next?

  • Learn how to add more complex CRUD operations (e.g., creating, updating, and deleting rows).
  • Explore error handling in Axum and SQLx to make your application more robust.
  • Scale your application by adding more routes and integrating with other Rust libraries.

For further learning, check out the official Axum and SQLx documentation for deeper dives into their features and capabilities. Happy coding!

Read more