Working with Postgres

Recipe	Crates	Categories
Create tables in a Postgres database
Insert and query data
Aggregate data
`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(())
}

`tokio-postgres`

Postgres-specific library. Performs better than SQLx.

[postgres: cornucopia (P2)](https://github.com/john-cd/rust_howto/issues/286)

`cornucopia` for postgres

Generate 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.

// use cornucopia_async::GenericClient;
// use tokio_postgres::{NoTls, Error};

// // Cornucopia is a Rust library for managing database connections and
// executing queries. It provides type-safe SQL in Rust. // https://cornucopia-rs.netlify.app/

// // tokio = { version = "1", features = ["full"] }
// // cornucopia = "0.3.0"
// // cornucopia_async = "0.3.0"
// // postgres = { version = "0.19", features = ["tokio-runtime"] }

// // CREATE TABLE greetings (
// //     id SERIAL PRIMARY KEY,
// //     message TEXT NOT NULL
// // );
// // INSERT INTO greetings (message) VALUES ('Hello, Cornucopia!');

// #[tokio::main]
// async fn main() -> Result<(), Error> {
//     let (client, connection) =
//         tokio_postgres::connect("host=localhost user=your_user
// dbname=your_db", NoTls).await?;

//     // The connection object performs the actual communication with the
// database,     // so spawn it off to run on its own.
//     tokio::spawn(async move {
//         if let Err(e) = connection.await {
//             eprintln!("connection error: {}", e);
//         }
//     });

//     // Select and print the greetings
//     let rows = client
//         .query("SELECT message FROM greetings", &[])
//         .await?;

//     for row in rows {
//         let message: &str = row.get(0);
//         println!("{}", message);
//     }

//     Ok(())
// }

Rust How-to