Working with Postgres

Recipe	Crates	Categories
Create Tables in a Postgres Database
Insert and Query Data
Aggregate Data
Connect to and Query Postgres Asynchronously with `tokio-postgres`
`cornucopia` for Postgres	cornucopia-rs⮳

Create Tables in a Postgres Database

Use the postgres⮳ crate to create tables in a Postgres database.

postgres::Client::connect⮳ helps in connecting to an existing database. The recipe uses a URL string format with Client::connect⮳. It assumes an existing database named library, the username is postgres and the password is postgres.

use postgres::Client;
use postgres::NoTls;

/// This function creates two tables in a PostgreSQL database: `author` and
/// `book`.
///
/// The `author` table has columns for `id`, `name`, and `country`.
/// The `book` table has columns for `id`, `title`, and `author_id` (a foreign
/// key referencing the `author` table).
pub fn main() -> anyhow::Result<()> {
    // Establish a connection to the PostgreSQL database.
    // The connection URL format is: postgresql://<user>:<password>@<host>/<db>
    let mut client = Client::connect(
        // Example connection URL:
        // postgresql://postgres:postgres@localhost/library
        "postgresql://postgres:mysecretpassword@rust_howto_dev-postgres-1/library",
        NoTls,
    )?;

    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS author (
            -- Primary key for the author table.
            id              SERIAL PRIMARY KEY,
            name            VARCHAR NOT NULL,
            country         VARCHAR NOT NULL
            )
    ",
    )?;

    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS book  (
            -- Primary key for the book table.
            id              SERIAL PRIMARY KEY,
            title           VARCHAR NOT NULL,
            -- Foreign key referencing the author table.
            author_id       INTEGER NOT NULL REFERENCES author
            )
    ",
    )?;
    println!("Tables created!");
    Ok(())
}

Insert and Query Data

The recipe inserts data into the author table using postgres::Client::execute⮳ method of postgres::Client⮳. Then, displays the data from the author table using postgres::Client::query⮳ method of postgres::Client⮳.

use std::collections::HashMap;

use postgres::Client;
use postgres::Error;
use postgres::NoTls;

/// Represents an author with an ID, name, and country.
struct Author {
    _id: i32,
    name: String,
    country: String,
}

pub fn main() -> Result<(), Error> {
    // Connect to the PostgreSQL database using the provided connection string.
    // The connection URL is formatted as
    // postgresql://<user>:<password>@<host>/<db>,
    // for example postgresql://postgres:postgres@localhost/library
    let mut client = Client::connect(
        "postgresql://postgres:mysecretpassword@rust_howto_dev-postgres-1/library",
        NoTls,
    )?;

    // Create a HashMap to store author names and their respective countries.
    let mut authors = HashMap::new();
    authors.insert(String::from("Chinua Achebe"), "Nigeria");
    authors.insert(String::from("Rabindranath Tagore"), "India");
    authors.insert(String::from("Anita Nair"), "India");

    // Iterate over the authors HashMap and insert each author into the
    // database. For each author, create an Author struct and execute an SQL
    // INSERT query. The query uses parameterized values ($1, $2) to prevent
    // SQL injection.
    for (key, value) in &authors {
        let author = Author {
            _id: 0,
            name: key.to_string(),
            country: value.to_string(),
        };

        client.execute(
            "INSERT INTO author (name, country) VALUES ($1, $2)",
            &[&author.name, &author.country],
        )?;
    }

    // Query the database to retrieve all authors and print their details.
    // The query returns rows, and we iterate over them to extract author
    // information.
    for row in client.query("SELECT id, name, country FROM author", &[])? {
        let author = Author {
            _id: row.get(0),
            name: row.get(1),
            country: row.get(2),
        };
        println!("Author {} is from {}", author.name, author.country);
    }

    Ok(())
}

Aggregate Data

This recipe lists the nationalities of the first 7999 artists in the database of the Museum of Modern Art⮳ in descending order.

use postgres::Client;
use postgres::Error;
use postgres::NoTls;

/// Represents a nation with its nationality and the count of artists from that
/// nation.
struct Nation {
    nationality: String,
    count: i64,
}

/// Connects to a PostgreSQL database, queries artist nationalities and their
/// counts, and prints the results. Data from
// https://github.com/MuseumofModernArt/collection/tree/main
pub fn main() -> Result<(), Error> {
    // The connection URL is formatted as
    // postgresql://<user>:<password>@<host>/<db>, for example
    // postgresql://postgres:postgres@127.0.0.1/moma
    let mut client = Client::connect(
        "postgresql://postgres:mysecretpassword@rust_howto_dev-postgres-1/moma",
        NoTls,
    )?;

    for row in client.query(
        "SELECT nationality, COUNT(nationality) AS count
 FROM artists GROUP BY nationality ORDER BY count DESC",
        &[],
    )? {
        let (nationality, count): (Option<String>, Option<i64>) =
            (row.get(0), row.get(1));

        if nationality.is_some() && count.is_some() {
            let nation = Nation {
                nationality: nationality.unwrap(),
                count: count.unwrap(),
            };
            println!("{} {}", nation.nationality, nation.count);
        }
    }

    Ok(())
}

Connect to and Query Postgres Asynchronously with `tokio-postgres`

tokio-postgres⮳ provides an asynchronous PostgreSQL client. It is built on top of the tokio⮳ runtime and thus supports non-blocking interactions with PostgreSQL databases. This crate offers connection pooling, prepared statements, transactions, and support for various PostgreSQL data types. It performs better than SQLx⮳.

/// In this example, we:
///
/// - Connect to a PostgreSQL database using `tokio_postgres::connect`.
/// - Spawn the connection on a separate task using `tokio::spawn`.
/// - Create a table if it does not already exist.
/// - Insert a new row into the table.
/// - Query the table for rows and print the results.
/// - Update a row in the table.
/// - Delete a row from the table.
#[tokio::main]
async fn main() -> Result<(), tokio_postgres::Error> {
    // Connect to the database.
    //  The libpq-style connection strings consist of space-separated
    // key-value pairs: https://docs.rs/tokio-postgres/latest/tokio_postgres/config/struct.Config.html
    let (client, connection) = tokio_postgres::connect(
        "host=rust_howto_dev-postgres-1 user=postgres password=mysecretpassword dbname=library",
        tokio_postgres::NoTls,
    )
    .await?;

    // Spawn the connection on a separate task.
    // The connection object performs the actual communication with the
    // database.
    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("Connection error: {}", e);
        }
    });

    // Create a table.
    client
        .execute(
            "CREATE TABLE IF NOT EXISTS person (
                id SERIAL PRIMARY KEY,
                name VARCHAR NOT NULL,
                data BYTEA
            )",
            &[],
        )
        .await?;

    // Insert a new row.
    client
        .execute(
            "INSERT INTO person (name, data) VALUES ($1, $2)",
            &[&"John Doe", &Some(b"example data".to_vec())],
        )
        .await?;

    // Query the rows.
    let mut id: i32 = 0;
    for row in client
        .query("SELECT id, name, data FROM person", &[])
        .await?
    {
        id = row.get(0);
        let name: &str = row.get(1);
        let data: Option<Vec<u8>> = row.get(2);

        println!("Found person: {} with id: {}", name, id);
        if let Some(data) = data {
            println!("Data: {:?}", String::from_utf8(data));
        }
    }
    // Note: calling a method like `Client::query` on its own does nothing.
    // The associated request is not sent to the database until the future
    // returned by the method is first polled (awaited). Requests are
    // executed in the order that they are first polled, not in the order that
    // their futures are created.

    // Update a row.
    client
        .execute(
            "UPDATE person SET name = $1 WHERE id = $2",
            &[&"Jane Doe", &id],
        )
        .await?;

    // Delete a row.
    client
        .execute("DELETE FROM person WHERE id = $1", &[&id])
        .await?;

    Ok(())
}

`cornucopia` for Postgres

cornucopia⮳ generates type-checked Rust from your PostgreSQL: cornucopia-rs⮳.

cornucopia⮳ is a tool powered by rust-postgres designed to generate type-checked Rust interfaces from your PostgreSQL queries. It works by preparing your queries against an actual database and then running an extensive validation suite on them. Once the queries are prepared and validated, Rust code is generated into a module, which can be imported and used in your project.

The basic premise is thus to:

Write PostgreSQL queries.
Use Cornucopia to generate Rust code.
Use the generated code in your project.

// // COMING SOON

The Rust How-to Book