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;

pub fn main() -> anyhow::Result<()> {
    // 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,
    )?;

    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS author (
            id              SERIAL PRIMARY KEY,
            name            VARCHAR NOT NULL,
            country         VARCHAR NOT NULL
            )
    ",
    )?;

    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS book  (
            id              SERIAL PRIMARY KEY,
            title           VARCHAR NOT NULL,
            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;

struct Author {
    _id: i32,
    name: String,
    country: String,
}

pub fn main() -> Result<(), Error> {
    // 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,
    )?;

    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");

    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],
        )?;
    }

    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;

struct Nation {
    nationality: String,
    count: i64,
}

// 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