SQLx

sqlx is an async SQL toolkit for Rust. It supports connection pools, prepared queries, transactions, and compile-time checked queries for supported databases. To use sqlx in your application, add the following crates to your project:

cargo add tokio --features full
cargo add sqlx --features sqlite,postgres,runtime-tokio

Connect to SQLite and query data

Use SqlitePoolOptions::connect to open an in-memory SQLite database backed by a connection pool that only allows a maximum of 1 concurrent connections. Create a table and insert rows with sqlx::query and Executor::execute, then load the results with Query::fetch_all. Each row is read by calling Row::try_get for the selected columns.

use sqlx::{Error, Row, sqlite::SqlitePoolOptions};

#[tokio::main]
async fn main() -> Result<(), Error> {
    let pool = SqlitePoolOptions::new()
        .max_connections(1)
        .connect("sqlite::memory:")
        .await?;

    sqlx::query(
        "CREATE TABLE IF NOT EXISTS location (
            id                  INTEGER PRIMARY KEY,
            name                TEXT NOT NULL,
            latitude            REAL NOT NULL,
            longitude           REAL NOT NULL
            )
        ",
    )
    .execute(&pool)
    .await?;

    sqlx::query("INSERT INTO location (name, latitude, longitude) VALUES (?1, ?2, ?3)")
        .bind("Null Island")
        .bind(0.00)
        .bind(0.00)
        .execute(&pool)
        .await?;

    sqlx::query("INSERT INTO location (name, latitude, longitude) VALUES (?1, ?2, ?3)")
        .bind("Titanic")
        .bind(41.726931)
        .bind(-49.948253)
        .execute(&pool)
        .await?;

    let locations = sqlx::query("SELECT name, latitude, longitude FROM location")
        .fetch_all(&pool)
        .await?;

    for row in locations {
        let name: String = row.try_get("name")?;
        let lat: f64 = row.try_get("latitude")?;
        let longitude: f64 = row.try_get("longitude")?;
        println!("{name} is at coordinates latitude: {lat} and longitude: {longitude}");
    }
    Ok(())
}

Connect to Postgres and query typed rows

Use PgPool::connect to open a connection pool for an existing locations Postgres database. Query values in the database and map query results into a typed Location struct with sqlx::query_as and FromRow, then load all rows with QueryAs::fetch_all.

use sqlx::{Error, postgres::PgPool};

#[derive(sqlx::FromRow)]
struct Location {
    name: String,
    latitude: f64,
    longitude: f64,
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let pool =
        PgPool::connect("postgresql://postgres:postgres@localhost/locations").await?;

    let locations = sqlx::query_as::<_, Location>("SELECT name, latitude, longitude FROM location")
        .fetch_all(&pool)
        .await?;

    for location in locations {
        println!(
            "{} is at coordinates latitude: {} and longitude: {}",
            location.name, location.latitude, location.longitude
        );
    }

    Ok(())
}

Compile-time checked queries

Use Connection::connect to open a Postgres connection and sqlx::query_as! to validate the SQL query against the database schema at compile time. The macro maps the selected columns into a Location struct and fetches a single row using Query::fetch_one.

use sqlx::{Connection, Error, postgres::PgConnection};

struct Location {
    name: String,
    latitude: f64,
    longitude: f64,
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let mut conn =
        PgConnection::connect("postgresql://postgres:postgres@localhost/locations").await?;

    let location = sqlx::query_as!(
        Location,
        "SELECT  name, latitude, longitude FROM location where id = $1",
        1i32
    )
    .fetch_one(&mut conn)
    .await?;

    println!(
        "{} is at coordinates latitude: {} and longitude: {}",
        location.name, location.latitude, location.longitude
    );

    Ok(())
}

Set DATABASE_URL to a Postgres database that already contains the location table before building. This may be achieved by either adding a DATABASE_URL variable to a .env file or running:

export DATABASE_URL=<your_database_url>

Transactions with SQLx

Use PgPoolOptions::connect to open a connection pool for an existing locations Postgres database. Start a transaction with Pool::begin, run two INSERT statements with sqlx::query, and save both rows together with Transaction::commit.

use sqlx::{Error, postgres::PgPoolOptions};

#[tokio::main]
async fn main() -> Result<(), Error> {
    let pool = PgPoolOptions::new()
        .max_connections(3)
        .connect("postgresql://postgres:postgres@localhost/locations")
        .await?;

    let mut tx = pool.begin().await?;

    sqlx::query("INSERT INTO location (name, latitude, longitude) VALUES ($1, $2, $3)")
        .bind("Area 51")
        .bind(37.2350)
        .bind(-115.8111)
        .execute(&mut *tx)
        .await?;

    sqlx::query("INSERT INTO location (name, latitude, longitude) VALUES ($1, $2, $3)")
        .bind("Point Nemo")
        .bind(-48.8767)
        .bind(-123.3933)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}