Working with Postgres

Create tables in a Postgres database

Use thepostgres crate to create tables in a Postgres database.

Client::connect helps in connecting to an existing database. The recipe uses a URL string format withClient::connect. It assumes an existing database namedlibrary, the username ispostgres and the password ispostgres.

use postgres::{Client, NoTls, Error};fn main() -> Result<(), Error> {    let mut client = Client::connect("postgresql://postgres:postgres@localhost/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            )    ")?;    Ok(())}

Insert and Query data

The recipe inserts data into theauthor table usingexecute method ofClient. Then, displays the data from theauthor table usingquery method ofClient.

use postgres::{Client, NoTls, Error};use std::collections::HashMap;struct Author {    _id: i32,    name: String,    country: String}fn main() -> Result<(), Error> {    let mut client = Client::connect("postgresql://postgres:postgres@localhost/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 theMuseum of Modern Art in descending order.

use postgres::{Client, Error, NoTls};struct Nation {    nationality: String,    count: i64,}fn main() -> Result<(), Error> {    let mut client = Client::connect(        "postgresql://postgres:postgres@127.0.0.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(())}