How to Set Up a PostgreSQL Database with SQLx in Rust

Amza

Amza

— 4 min read

Setting up a database in your application can seem daunting, but with the right tools and a step-by-step approach, it's a manageable task. In this article, we'll walk through setting up a PostgreSQL database using SQLx, an asynchronous SQL toolkit for Rust. SQLx helps us interact with databases safely and efficiently, and it works well with PostgreSQL, MySQL, and SQLite.

By the end of this guide, you’ll have a basic understanding of how to set up a PostgreSQL database, connect to it using SQLx, and perform basic queries. Let’s get started!

Step 1: Install Dependencies

Before writing any code, we need to install the necessary tools and libraries. First, ensure you have Rust installed on your system. You can check this by running:

$ rust --version

Next, we'll need to add a few dependencies to our Cargo.toml file. These include sqlx for interacting with the database, and tokio for asynchronous programming.

  1. Open your project’s Cargo.toml file.
  2. Add the following dependencies:
[dependencies]
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.6", features = ["postgres", "runtime-tokio-native-tls"] }
dotenv = "0.15"
  • tokio: This is an asynchronous runtime for Rust, and SQLx requires it.
  • sqlx: This is the library that enables us to interact with the database.
  • dotenv: We'll use this to load our database URL from an environment file, keeping sensitive information secure.

Run the following command to fetch the dependencies:

$ cargo build

Step 2: Set Up the Database

You’ll need a PostgreSQL database. If you haven’t set one up yet, you can run it using Docker:

$ docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

This will create a PostgreSQL container running on port 5432 with the password mysecretpassword.

Alternatively, if you already have PostgreSQL installed locally or remotely, you can skip this step and just use your own credentials.

Step 3: Create the .env File

To keep our database connection string safe, we’ll store it in a .env file. This prevents exposing sensitive credentials directly in our code. Create a file named .env in the root of your project and add the following line:

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

In this URL:

  • postgres is the username.
  • mysecretpassword is the password we set in the Docker container.
  • localhost:5432 is the address of the running PostgreSQL instance.
  • mydatabase is the name of the database we want to connect to (you can create it in your PostgreSQL client).

We’ll use this URL in our code to connect to the database.

Step 4: Write the Code

Now, let’s start writing some code! In the main.rs file, we’ll set up a simple connection to the PostgreSQL database and perform a basic query.

use sqlx::{PgPool, Error};
use dotenv::dotenv;
use std::env;

#[tokio::main]
async fn main() -> Result<(), Error> {
    dotenv().ok(); // Load .env file

    let database_url = env::var("DATABASE_URL")
        .expect("DATABASE_URL environment variable is not set"); // Get DATABASE_URL from .env
    let pool = PgPool::connect(&database_url).await?; // Connect to the PostgreSQL database

    println!("Successfully connected to the database!");

    // Run a basic query
    let row: (i32,) = sqlx::query_as("SELECT 1")
        .fetch_one(&pool)
        .await?;

    println!("Query result: {}", row.0);

    Ok(())
}

Explanation:

  • dotenv().ok(): This loads the .env file and makes the database URL available to our code.
  • PgPool::connect(): This establishes a connection pool with the PostgreSQL database. SQLx uses connection pooling for efficiency.
  • sqlx::query_as(): This is the method we use to execute queries. We’re using a simple SQL query that returns 1 from the database to test the connection.
  • fetch_one(): This method fetches a single result row, in this case, the 1 we queried.

Step 5: Run the Code

With everything set up, let’s run our application:

$ cargo run

You should see output like this:

Successfully connected to the database!
Query result: 1

This confirms that we successfully connected to the database and executed a query.

Step 6: Adding a Real Query

Let’s make our code a little more interesting. We’ll add a real table to the database and fetch some data. First, create a table in your PostgreSQL database:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Insert some data into the table:

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

Now, modify your Rust code to query this table:

#[tokio::main]
async fn main() -> Result<(), Error> {
    dotenv().ok(); // Load .env file

    let database_url = env::var("DATABASE_URL")
        .expect("DATABASE_URL environment variable is not set");
    let pool = PgPool::connect(&database_url).await?;

    println!("Successfully connected to the database!");

    // Query users
    let rows: Vec<(i32, String, String)> = sqlx::query_as("SELECT id, name, email FROM users")
        .fetch_all(&pool)
        .await?;

    for row in rows {
        println!("ID: {}, Name: {}, Email: {}", row.0, row.1, row.2);
    }

    Ok(())
}

This code fetches all users from the users table and prints their information.

Step 7: Run the New Code

Run the code again:

$ cargo run

This time, you should see output like:

Successfully connected to the database!
ID: 1, Name: Alice, Email: alice@example.com
ID: 2, Name: Bob, Email: bob@example.com

Now you’re not only connecting to the database but also retrieving and displaying data!

Challenges or Questions

  1. Try to handle errors: Modify the code to gracefully handle scenarios where the database connection fails or no users exist in the database.
  2. Experiment with INSERT or UPDATE queries: Modify the code to insert a new user into the users table or update existing user information.

These exercises will deepen your understanding and improve your coding skills.

Recap and Conclusion

In this guide, we’ve:

  1. Set up PostgreSQL with Docker and connected to it using SQLx in Rust.
  2. Written simple queries to test the connection and retrieve data.
  3. Explored how to use SQLx to interact with databases asynchronously.

This is just the beginning! You can now expand on this by exploring more complex queries, working with migrations, and integrating your database with a full-fledged web application.

Happy coding!

Read more