Rust: sqlx without macros

Author

James Pace

Published

February 15, 2024

sqlx is a Rust crate that allows for interacting with a SQL database. One of the more popular features of sqlx is that it does compile time checking of database queries (giving you some ORM-ish properties) while still allowing you to write straight SQL queries without the abstraction of an ORM though use of their query! macro.

To make this work, sqlx requires:

access to a database at compile time, OR
config files to be generated from a running database everytime the database queries are modified.

I don’t particularly like either of those options, though I admittedly have not tried either of them long term. Requiring a running database at compile time would seem to make compilation a lot slower. It also adds an unexpected step (run a container hosting a development database) before you can compile, which just feels wrong. The configuration files are better in the sense they don’t require a running database at compile time, but it’s an extra thing that has to be kept in sync with the code.

Luckily, sqlx provides functions that can be used to do queries that aren’t checked at compile time for people with the same concerns as I have. Because this isn’t the recommended path though, the number of examples of using the functions online is a little lacking, which I’ll make an effort to improve in this post.

The other thing I’ll show in this post is working with JSON in Postgres. Postgres has native support for working with JSON documents, providing a lot of the benefits of NoSQL databases in a SQL database. sqlx’s API for json documents integrates well with serde, and is reasonably easy to work with.

Pre-reqs

Running the example requires a running Postgres database, which I’m going to run locally via podman using the bash script below.

#!/usr/bin/env bash

DATA_DIR="$PWD/data"

if [ ! -d "$DATA_DIR" ]; then
  echo "Making data directory."
  mkdir $DATA_DIR
fi
podman run --rm --name test-db --network=host \
       -e POSTGRES_USER=postgres \
       -e POSTGRES_PASSWORD=devpassword \
       -e PGDATA=/var/lib/postgresql/data/pgdata \
       -v $DATA_DIR:/var/lib/postgresql/data \
       docker.io/library/postgres:latest

The script:

Defines DATA_DIR which will be a local host directory we will mount in the database container and tell the database to save its files to so they persist beyond container restarts.
Makes that directory if it is missing.
Starts a container running the official postgres image. Environment variables are used to set some settings to values suitable for local testing.

Example

For the rest of this post, I will walk through an example (which can be found here).

use serde::{Deserialize, Serialize};
use sqlx::{types::Json, Row};

We start by “use”-ing some structs and traits we’re going to want later, starting with the standard serde ones. “Use”-ing sqlx::Row is important because it brings the traits we need to work with the results of doing the sqlx queries into scope.

#[derive(Serialize, Deserialize, Debug)]
struct Doc {
    age: u32,
    fav_color: String,
    fav_movie: String,
}

We next define a Doc struct that we will work with like JSON in the database.

Starting with our main function…

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Connect to the database.
    let pool =
        sqlx::postgres::PgPool::connect("postgresql://postgres:devpassword@localhost/postgres")
            .await?;

The first thing we do in main is to connect to the database by making a connection pool. The connect function takes a string argument to control what database by what method we want to connect to. Specifically, in the call above, we’re going to connect as the user postgres using the password devpassword to the server running on localhost with the default port to the database postgres.

    // Make the table.
    let _ = sqlx::query(
        "CREATE TABLE IF NOT EXISTS test (id SERIAL PRIMARY KEY,
                                          name TEXT,
                                          doc  JSONB
                                        );",
    )
    .execute(&pool)
    .await?;

The first thing we’re going to do is generate a table for us to work with in the rest of example. This is the first time we’ve seen the sqlx::query function, so we”ll pause here to talk about it. The input to the query function is a string with, well, a SQL query in it. query returns a Query object which has member functions we call to actually do the database call. The two functions we’ll use in this example are execute and fetch_one. Both functions take the pool as an input and return a future with a result that we need to await. What’s in the non-Err part of the results depends on which function we call. For execute it is (), and thus we call execute when we’re not expecting to be given anything by the database. For fetch_one it is a sqlx::Row, which contains members we can call to query the output of our database query.

    // Insert some entries into db.
    // Add James.
    let james_doc = Doc {
        age: 30,
        fav_color: "blue".to_owned(),
        fav_movie: "Iron Man".to_owned(),
    };
    let james_doc_as_str = serde_json::to_string(&james_doc)?;
    let james_add_query = format!(
        r#"INSERT INTO test (name, doc) VALUES('James', '{}');"#,
        james_doc_as_str
    );
    let _ = sqlx::query(&james_add_query).execute(&pool).await?;

The next thing we do is add rows to table so we have something to query. We start by instantiating an instance of our Doc struct, and then using serde_json to serialize it into a JSON string. We generate and execute the database query as before, with the exception that we make the string we’re going to pass into sqlx::query in its own line. One thing of interest is we define the string using a “raw string literal” (the r# stuff). The official documentation for the “raw string literal”s is here. It’s basically a special syntax that allows us to avoid having to escape characters inside of the string.

The next block of text is basically the same as above, but for our second user, named “Dave”.

    // Add Dave.
    let dave_doc = Doc {
        age: 25,
        fav_color: "yellow".to_owned(),
        fav_movie: "Spiderman".to_owned(),
    };
    let dave_doc_as_str = serde_json::to_string(&dave_doc)?;
    let dave_add_query = format!(
        r#"INSERT INTO test (name, doc) VALUES('Dave', '{}');"#,
        dave_doc_as_str
    );
    let _ = sqlx::query(&dave_add_query).execute(&pool).await?;

We’re now ready to query the database:

    // Get james favorite color.
    let fav_color_query = r#"SELECT doc->>'fav_color' FROM test WHERE name = 'James';"#;
    let fav_color_result = sqlx::query(&fav_color_query).fetch_one(&pool).await?;
    let james_fav_color = fav_color_result.try_get::<String, usize>(0)?;
    println!("James' Fav Color: {}", james_fav_color);

Our first query is to find James’s favorite color, which is saved in the Doc in James’ row. There are three interesting new things in this block of code:

In the SQL query, we’re going to use the special syntax ->> to refer to elements inside the JSON object we added to the database.
Since we’re trying to get one element from the database, instead of calling execute on the query, we use fetch_one.
The result of fetch_one is a sqlx::Row. To get actual data out of the row we call try_get, which takes one parameter, the index of the element of the row to get an element of. There are two type parameters for try_get, the first the type of the thing we’re getting from the row and the second the type of the index.

    // Get name based on movie.
    let spiderman_query = r#"SELECT name FROM test WHERE doc->>'fav_movie' = 'Spiderman';"#;
    let spiderman_result = sqlx::query(&spiderman_query).fetch_one(&pool).await?;
    let spiderman_name = spiderman_result.try_get::<String, usize>(0)?;
    println!("{} likes Spiderman.", spiderman_name);

Our second example is similar to the first, but we use the ->> syntax to pull something out of the JSON doc in the WHERE condition instead of in what we SELECT.

    // Get james's doc.
    let james_query = r#"SELECT doc FROM test where name = 'James'"#;
    let james_result = sqlx::query(&james_query).fetch_one(&pool).await?;
    let james_doc = james_result.try_get::<Json<Doc>, usize>(0)?.0;
    println!("James Doc is: {:?}", james_doc);

    Ok(())
}

In our final example, we pull out the whole Doc for James, using the Json type from sqlx to convert between the JSON data in the database and our Doc struct.