SQLx - Interacting with databases in Rust

SQLx is an async Rust crate that let's us interact with a database with compile-time checked queries 🧰

This post will be a short introduction to SQLx with PostgreSQL, but SQLx also supports MySQL, SQLite, and MSSQL.

The example will be a todo list in a database. A classical programming example, I know 😅

We will also see if SQLx protects us against SQL injections 💉

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.

Database preparation

Before starting with SQLx, run PostgreSQL in the background to be able to connect to it.

The post "Containerized PostgreSQL with rootless Podman" can help with setting up PostgreSQL. But you don't have to use a container!

Database URL

Now that our database is running in the background, we want to provide information to SQLx to be able to connect to the database.

This information is the DATABASE_URL environment variable with the following general form for PostgreSQL:

postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]

You can check possible parameters (param) in this documentation page.

The easiest way to provide this environment variable during development is to store it in a .env file in the project's root directory (where Cargo.toml is):

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

I am using the connection details from the previous post. We are connecting to the database postgres at the host 127.0.0.1 (localhost) on port 5432 with the username postgres and the password CHANGE_ME.

Make sure you enter your connection details.

SQLx automatically extracts the environment variable from this file for the CLI (see below) and the compile-time query checks.

SQLx CLI

SQLx provides a CLI that helps during development. You can install it with cargo:

cargo install sqlx-cli --no-default-features --features rustls,postgres

Make sure that ~/.cargo/bin is in your PATH to be able to call the binary sqlx.

The CLI has three subcommands which can be seen by running sqlx --help:

• The database subcommand allows you to create, drop and reset the database in DATABASE_URL.
• The prepare subcommand allows support for compile-time query checks in offline mode which is useful if you don't want to keep the database running in the background.
• The migrate subcommand allows you to add and run database migrations which we will use in the next section.

Migrations

Since our database is still empty, we want to create a table to interact with in our code.

We could create our tables manually by connecting to the database with psql and running some SQL statements. But to automate the migrations process, we should use SQLx migrations.

First, make sure that the database exists with the following command:

sqlx database create

Now, let's create our first migration with the following command:

sqlx migrate add create_todos_table

create_todos_table is the migration description. The output of this command is very helpful:

Creating migrations/20230409131413_create_todos_table.sql

Congratulations on creating your first migration!

Did you know you can embed your migrations in your application binary?
On startup, after creating your database connection or pool, add:

sqlx::migrate!().run(<&your_pool OR &mut your_connection>).await?;

Note that the compiler won't pick up new migrations if no Rust source files have changed.
You can create a Cargo build script to work around this with `sqlx migrate build-script`.

See: https://docs.rs/sqlx/0.6.3/sqlx/macro.migrate.html

The command did create the directory migrations in the project's root directory with the file <timestamp>_<description>.sql.

It does also tell us about embedding and running the migrations in our code which we will do later.

To trigger recompilation for the compile-time query checks after a new migration, we will follow the tip in the command output above and run the following command:

sqlx migrate build-script

This will create a build.rs file that triggers recompilation after changes in the migrations directory.

Now, let's write our first migration in the file created with sqlx migrate add:

CREATE TABLE IF NOT EXISTS todos (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false
);

This SQL statement creates a table called todos with a self incrementing id as the primary key, a text title and a boolean done that defaults to false.

To run this migration, run the following command:

sqlx migrate run

You have applied your first migration 🎉

You can create and run future migrations the same way. SQLx will then only run migrations that are not applied yet. Applied migrations are tracked in a special table in the database called _sqlx_migrations.

Now that our database is prepared, we can finally start interacting with it through code 🦀

Dependencies

The example uses the following crates:

• sqlx: Async SQL toolkit
• tokio: Asynchronous runtime
• futures: Needed to handle async streams
• dotenvy: Loads environment variables from the .env file

To follow the example, add the following dependencies to Cargo.toml:

[dependencies]
dotenvy = "0.15.7"
futures = "0.3.28"
sqlx = { version = "0.6.3", features = ["postgres", "macros", "runtime-tokio-rustls"] }
tokio = { version = "1.27.0", features = ["macros", "rt-multi-thread"] }

Imports

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

use futures::TryStreamExt;
use sqlx::postgres::PgPoolOptions;
use std::env;

Connection pool

Since SQLx is async, we need tokio to make the main function async.

We will use dotenvy to add the content of the .env file. Then, we will use the environment variable DATABASE_URL to create a connection pool to the database:

#[tokio::main]
async fn main() {
    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!");
}

A connection pool manages opening and re-using multiple database connections. The maximum number of connections can be provided with max_connections before connect.

Although SQLx provides a way to open a single database connection, a connection pool should always be used instead of expensively opening a connection for every operation.

Now that we have a connection pool, we can try some queries!

Unchecked queries

First, we will see how to run a query that is not checked at compile-time:

let first_todo_title = "Learn SQLx";

sqlx::query("INSERT INTO todos (title) VALUES ($1)")
    .bind(first_todo_title)
    .execute(&pool)
    .await
    .unwrap();

"Wait a minute", you might think, "this is just SQL! It is not what I did expect from a database library 🤨"

If you had this though, then you were expecting an ORM. SQLx is not an ORM. This means that you will just use normal SQL statements to interact with the database. We will discuss how the SQLx approach compares to an ORM at the end of the post.

Now, let's discuss the snippet above. The function query takes a SQL query as input with $1 .. $N as parameters.

The values of the parameters are provided by calling bind N times with respect to the order of the parameters. Since we only have one parameter ($1), we call bind once with the value as the title of our first todo.

Congratulations, you made your first query with SQLx 🎉

Now, how can we retrieve something from the database?

We can use query_as to run a query and interpret the database output as an instance of a struct. Let's create a struct that represents a row in the todos table:

#[derive(sqlx::FromRow)]
struct Todo {
    id: i32,
    title: String,
    done: bool,
}

Notice the need to derive sqlx::FromRow for using the struct with query_as.

Now, we can run the following to get the inserted first todo from the database:

let first_todo = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE title=$1")
    .bind(first_todo_title)
    .fetch_one(&pool)
    .await
    .unwrap();

We use fetch_one because we are expecting a single row as a result. If you want to retrieve multiple rows, you can use fetch or fetch_all that we will cover in a later section.

We can use first_todo just as any instance of Todo:

println!(
    "query_as: id={}, title={}, done={:?}",
    first_todo.id, first_todo.title, first_todo.done
);

The output should be the following:

query_as: id=1, title=Learn SQLx, done=false

You can run any other SQL statement using query and query_as. Your only limits are the features that the database system supports! As a demonstration, we will add the following statement before the first INSERT statement to start with a clean table and avoid confusion when running the program multiple times:

sqlx::query("TRUNCATE TABLE todos RESTART IDENTITY")
    .execute(&pool)
    .await
    .expect("Failed to truncate table!");

Compile-time checked queries

Now, let's try queries that are checked at compile-time. Such queries require the development database to be reachable during compilation. An alternative would be the offline mode that can be prepared with the SQLx CLI.

An example for such a query is the following:

let second_todo_title = "Give feedback to this blog post";

sqlx::query!("INSERT INTO todos (title) VALUES ($1)", second_todo_title)
    .execute(&pool)
    .await
    .unwrap();

The syntax difference to query is that the macro query! takes the values of the parameters as arguments after the query.

The advantage of the macro is that SQLx connects to the development database and lets the database itself verify the query with the value types provided.

If, for example, the table todos doesn't exist, the program will not compile! You will get a compilation error with the error message from the database. In this case, you might have forgotten to run the migrations.

The macro checks the type of the values provided. If we replace the provided value second_todo_title with some number like 42, SQLx will tell us that it was expecting &str because the title has the type TEXT in the database!

We will see the real advantage of the compile-time checks in the next section. But first, let's see the equivalence of the function query_as as a macro:

let second_todo = sqlx::query_as!(
    Todo,
    "SELECT * FROM todos WHERE title=$1",
    second_todo_title
)
.fetch_one(&pool)
.await
.unwrap();

The syntax differences are the following:

• The struct is provided as the first argument.
• The parameter values are provided as arguments after the query instead of using bind.

Adding/dropping a column

Now, let's add a column to practice migrations and experience the major advantage of the compile-time checked queries. We will add a column for an optional description.

First, we will create a new migration:

sqlx migrate add add_description_column

Now, enter the following in the new migration file:

ALTER TABLE todos
ADD COLUMN IF NOT EXISTS description TEXT;

Let's apply this migration with the following command:

sqlx migrate run

The changes are applied to the database 🎉

But what about our code? Let's try to compile it as is! We will become the following error that points to the SELECT statement with the macro query_as!:

struct `Todo` has no field named `description`

This feels like magic 🪄

Remember, we did not change our code, we did not even run our program to find the bug at runtime. No, this error is shifted to compile-time!

Let's fix the error by adding the following field to our Todo struct:

struct Todo {
    // ...
    description: Option<String>,
}

We use Option because the description is nullable in the database.

Now, our code compiles again 🎉

Let's add two more todos with a description, one with the function query and one with the macro query!:

sqlx::query("INSERT INTO todos (title, description) VALUES ($1, $2)")
    .bind("Learn SQL")
    .bind("To understand the statements in this blog post")
    .execute(&pool)
    .await
    .unwrap();

sqlx::query!(
    "INSERT INTO todos (title, description) VALUES ($1, $2)",
    "Learn Axum",
    "To write a Rust backend"
)
.execute(&pool)
.await
.unwrap();

Both work! But let's say that at a later point in the future we don't like descriptions anymore. We want to drop the column 🗑️

Apply the following migration:

ALTER TABLE todos
DROP COLUMN IF EXISTS description;

Now, compile again. You will get two errors. This first one is solved by removing the field description from the Todo struct. The second error is the following:

error returned from database: column "description" of relation "todos" does not exist

This error is from the macro query! with the todo "Learn Axum". The query references description, but the column doesn't exist anymore and the database complains.

Why am I stressing this point? I am sure you did get that the checks at compile-time are very useful.

What I want to show is the alternative to compile-time checks. To see it, let's fix the compiler error by removing the description from the macro query! and run the program:

sqlx::query!("INSERT INTO todos (title) VALUES ($1)", "Learn Axum")
    .execute(&pool)
    .await
    .unwrap();

Our program runs, but then... PAM 💥 A runtime error that leads to a panic because of the function query with a description. We forgot to adopt the change from the migration here.

Of course, it is our fault that the program panics because we did use unwrap which you should not use in a real project. But the point is that the error occurs anyway, but at runtime! The users of your program will probably discover the error after your product is published, not you... ☠️

Or you have to write unit tests for every query and hope that such errors get caught... 🥲

Fetching multiple rows

We did see that we can use fetch_one to retrieve a single row. To retrieve multiple rows, we can either use fetch or fetch_all.

fetch_all returns a simple vector of rows. Here is how to use it:

let todos = sqlx::query_as!(Todo, "SELECT * FROM todos")
    .fetch_all(&pool)
    .await
    .unwrap();

for todo in todos {
    println!("title={}, done={:?}", todo.title, todo.done);
}

On the other hand, fetch returns an async stream from the database. This means that the results are not collected into a vector first which prevents unneeded allocations:

let mut todos_stream = sqlx::query_as!(Todo, "SELECT * FROM todos").fetch(&pool);

while let Some(todo) = todos_stream.try_next().await.unwrap() {
    println!("title={}, done={:?}", todo.title, todo.done);
}

Make sure to import the trait futures::TryStreamExt to be able to run try_next on the stream.

SQL injections

There is one big misconception I had about SQLx that prevented me from using it for some time because I thought it would be vulnerable to SQL injections.

I thought that query and query! work by replacing the parameters $1 .. $N with the provided values like how format! works. This is not true!

SQLx sends the queries unmodified (with the parameters $1 .. $N) to the database and the parameter values are sent in addition. There is no formatting like with format!. The database itself takes care of preventing SQL injections in these parameterized queries.

An ORM is not safer than parameterized queries!

A parameterized query is not only safe, but also more efficient than a normal query because it can be prepared in isolation from the parameter values.

Embedded migrations

During development, you need to use the SQLx CLI to run migrations. But you can embed migration in the binary and run them automatically when deploying the binary.

To do so, add the following snippet just after creating the connection pool:

sqlx::migrate!()
    .run(&pool)
    .await
    .expect("Failed to run migrations!");

Compile-time optimization

Because of the checks that SQLx does at compile-time, it is recommended to add the following to the end of your Cargo.toml file to optimize these checks and speed up compilation:

[profile.dev.package.sqlx-macros]
opt-level = 3

Conclusion

SQLx is a very flexible and powerful tool. It allows you to use any feature your database provides by just using SQL statements. But at the same time, its macros automagically validate your queries at compile-time 🪄

Personally, I don't see myself going back to ORMs after trying SQLx. ORMs are just another layer of abstraction without many advantages in comparison to SQLx. With ORMs, you need to learn the specific restricting API of every new ORM you use and forget your SQL skills that you need at some point anyway.

SQLx is simple, safe, flexible and powerful 🚀

Finally, we can check our todo "Learn SQLx" 😉

sqlx::query!("UPDATE todos SET done=true WHERE title='Learn SQLx'")
    .execute(&pool)
    .await
    .unwrap();

Recommendations

• SQL Injection in Rust; still possible? - Security Research: A video about how SQLx prevents SQL injections.

Code snippets

Almost all code snippets used in this post can be found here.