In this guide we’ll be looking at working with PostgreSQL using Rust. By the end of this guide you’ll have more of an idea of how to use both and when each one would be better for your use case.

It will be assumed you already have a project initialised - if not, you can always initialise a new project with shuttle init.

​
SQL

Relational databases are the classical way to store data in the backend of web applications when it comes to storing records and persisted data. Shuttle currently offers free provisioned SQL instances for your applications and currently provides it through an SQLx connection pool. It should be noted that although we’re using Postgres for this guide, the same concepts works equally well with both MySQL and MariaDB.

To get started with SQLx (and using our new database), we’ll want to add the sqlx crate to an initialised Rust program. We will also want to add the shuttle-shared-db crate which allows us to use the macro that provisions the database instance to us.

cargo add sqlx shuttle-shared-db --features sqlx/macros,shuttle-shared-db/postgres,shuttle-shared-db/sqlx
cargo install sqlx-cli

We can then run sqlx migrate add <name> in our project to add a migrations folder that will contain an empty SQL file, with the naming convention <timestamp>_<name>.sql. We can then create our tables using the power of raw SQL:

CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
password VARCHAR NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

When we run our migrations, this folder will be the default that will be used - when we’re ready to run our migrations, we can run sqlx migrate run --database-url <database-url>. We can also just run the migrate macro programmatically after connecting to the database:

sqlx::migrate!().run(&pool).await.expect("Migrations failed :(");

Be aware however that this will run every time the program starts, so if you only want your migrations to run once or you’re not using CREATE TABLE IF NOT EXISTS, you will probably want to make sure you either comment this statement out or just delete it once the migrations have run.

To get started with querying using SQLx, we’ll need to connect to our database. Normally you’d have to set up your connection pool manually - however, with Shuttle you can just set the macro up and you’ll receive the connection pool immediately:

#[shuttle_runtime::main]
async fn axum(
    #[shuttle_shared_db::Postgres] pool: PgPool
) -> shuttle_axum::ShuttleAxum {
... some code
}

Now when you need to make a query, you can use &pool in your queries as the database connection you want to execute your query against. The query uses the referenced version of the variable to be able to keep the connection alive; if it doesn’t, then the variable will get consumed and you’d lose your connection pool.

At a basic level, we can use sqlx::query to query something and then chain the .bind() method to insert our own variables into the query:

// fetch one row
let query = sqlx::query("SELECT * FROM users WHERE id = $1")
    .bind(id)
    .fetch_one(&pool)
    .await;

let number: i32 = query.get("id");

While this is pretty cool, what we probably want to do if we know what we want to extract from the database is to use SQLx’s sqlx::FromRow derive macro on a struct with the data type we want from the database. Let’s have a look at what that might look like below:

#[derive(sqlx::FromRow)]
struct Message {
    id: i32,
    message: String
}

let query = sqlx::query::_as<_, Message>("SELECT id, message FROM messages")
    .fetch_all(&pool)
    .await;

This is much more convenient for us for gathering information since we already know what we want the outputted data type from the query to be, so when we carry out the query it’ll now automatically be converted into a vector of structs.

One of SQLx’s main strengths in addition to the above is being able to create data for compile-time queries. To do this, we’ll want to enable the macros flag for SQLx by adding the macros feature flag to SQLx in our Cargo.toml file, though if following this guide from the start you’ll probably have it already. If not, you can run this command:

cargo add sqlx --features macros

This will allow us to be able to use the query!() and query_as!() macros, which like the above allows us to execute general queries as well as queries that can directly return structs. The main difference however is that these macros do compile-time syntactic and semantic verification of the SQL, meaning you’ll get errors at compile time if the queries are invalid. Let’s have a look at how we can use them below:

// as you can see this is pretty much the same
let messages = sqlx::query!("SELECT * FROM messages")
    .fetch_all(&pool)
    .await;

#[derive(sqlx::FromRow)]
struct Message {
    id: i32,
    message: String
}

// as you can see we input the struct type in-macro, then the query & bound variables
let messages_to_struct = sqlx::query_as!(Message, "SELECT id, message FROM messages
 WHERE id = (1)", 1i32)
    .fetch_one(&pool)
    .await;

Once we’ve built all of our queries that we want to use, all we need to do is run the following command which will create query metadata at the current working directory:

cargo sqlx prepare --database-url <database-url>

// use the command below if you're using a workspace
cargo sqlx prepare --workspace

Now we can build and run our program as usual, and if there’s no errors then it should work!

If you’re using SQLx in conjunction with a backend web development framework like Axum, bear in mind that you will want to use Serde to serialize your data to JSON - this can be enabled by installing Serde with the “derive” feature:

cargo add serde --features derive

Then you need to add the Serialize derive macro to your structs as required, and then when you return the JSON-serialized data it’ll automatically be accepted as a HTTP-compatible response.