Working with Postgres

Add this crate to your own project:

cargo add postgres

Installing PostgreSQL

postgres-badge cat-database-badge

Before running these examples that access a live Postgresql database, you will need to:

  1. Install the Postgresql database server.
  2. Start the server if that was not done automatically.
  3. Configure the database to accept client connections at address 127.0.0.1 (localhost) on port number 5432.
  4. Set a username and password to access the database.

The following examples expect a database user with full permissions to exist as:

  • Username: postgres
  • Password: ChangeMe99

Modify the examples with the actual username and password.

Obviously, such a test database should not be accessible from the Internet, or even from outside the computer you are working on.

Arch Linux

https://wiki.archlinux.org/title/PostgreSQL

macOS

https://www.postgresql.org/download/macosx/

Windows

https://www.postgresql.org/download/windows/

Ubuntu

Ubuntu server guide instructions can be followed almost exactly for an Ubuntu server or desktop. The instructions to enable PostgreSQL to listen to all network interfaces may be skipped if running these examples on the same system.

This is the actual SQL statement to set the postgres database user password:

ALTER USER postgres with encrypted password 'ChangeMe99';

Note that does not change the postgres system account password.

Be sure to change the password for both the database and system postgres accounts before putting a database server into production.

Create the cats test database

Connect to the database server using the psql command-line client, using the password that was set above:

psql -h 127.0.0.1 -U postgres -W

When connected, create the cats database with this SQL statement:

CREATE DATABASE cats;

Create tables in a PostgreSQL database

postgres-badge cat-database-badge

Use the postgres crate to open a connection to a local PostgreSQL database.

Client::connect will connect to an existing cats database using the provided username postgres and password ChangeMe99. This will fail if the database has not already been created.

Then the two tables are created if they don't already exist. The color of a specific cat must be defined in the cat_colors table before a cat row is inserted which uses that color.

use postgres::{Client, NoTls, Error};

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://postgres:ChangeMe99@localhost/cats", NoTls)?;

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

    client.batch_execute("
        CREATE TABLE IF NOT EXISTS cats  (
            id              SERIAL PRIMARY KEY,
            name            VARCHAR NOT NULL,
            color_id        INTEGER NOT NULL REFERENCES cat_colors
            )
    ")?;

    Ok(())
}

Insert and Select data

postgres-badge cat-database-badge

The example will connect to the database, insert some cat data, and then print the result.

use std::collections::HashMap;
use postgres::{Client, NoTls, Error};

#[derive(Debug)]
struct Cat {
    name: String,
    color: String,
}

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://postgres:ChangeMe99@localhost/cats", NoTls)?;

    client.batch_execute("DELETE FROM cats")?;
    client.batch_execute("DELETE FROM cat_colors")?;

    let mut cat_data = HashMap::new();
    cat_data.insert(String::from("Blue"), vec!["Tigger", "Sammy"]);
    cat_data.insert(String::from("Black"), vec!["Oreo", "Biscuit"]);

    for (color, catnames) in &cat_data {
        client.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&color])?;

        let current_color_row = client.query_one(
            "SELECT id FROM cat_colors WHERE name = $1", &[&color])?;
        let color_row_id: i32 = current_color_row.get(0);

        for cat in catnames {
            client.execute("INSERT INTO cats (name, color_id) VALUES ($1, $2)",
            &[&cat.to_string(),
            &color_row_id])?;
        }
    }

    println!("Cat Name   Color");
    let prepared_stmt = client.prepare("SELECT c.name, cc.name from cats c INNER JOIN cat_colors cc ON cc.id = c.color_id;")?;
    for row in client.query(&prepared_stmt, &[])? {
        let cat = Cat {
            name: row.get(0),
            color: row.get(1),
        };
        println!("{:10} {}", cat.name, cat.color);
    }
    Ok(())
}

Client::connect will open the local database cats, to access the two tables created in the earlier recipe.

First, all data is cleared out of the cats and cat_colors tables using batch_execute. Data must be deleted from cats first, as there is a foreign key dependency upon the cat_colors table.

Then the sample data is inserted into cat_colors and cats tables using the execute method of Client.

Arguments passed to the SQL statement are $1, $2, $3, etc. which correspond to the items in the slice reference. Note that the parenthesis () are part of the SQL INSERT statement syntax and not required for the argument substitution.

A new color is inserted into the cat_colors table. After a record for a color is inserted, a query is performed to get the row id of that color using query_one. Then this color_row_id is used to reference the new color while inserting data into the cats table.

Finally, a prepared statement query is created using the prepare method which gives a statement struct. The query is then executed using query method of statement to print the result. A slice reference containing the arguments to be passed to the SQL is required for the call to query, but since no arguments were needed for that query, the empty slice reference &[] is used.

Using transactions

postgres-badge cat-database-badge

Client::connect will open the local database cats, to access the two tables created in the earlier recipe. The cat_colors table must exist.

Begin a transaction with Client::transaction. Transactions will roll back unless committed explicitly with Transaction::commit.

In the following example, are colors added to a table having a unique constraint on the color name. When an attempt to insert a duplicate color is made, the transaction rolls back.

At the end of successful_tx() the cat_colors table should have exactly two rows, with the colors 'lavender' and 'blue'.

At the end of rolled_back_tx(), the cat_colors table should still have just 'lavender' and 'blue', since the the DELETE and the INSERTs will not have been committed.

use postgres::{Client, NoTls, Error};

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://postgres:ChangeMe99@localhost/cats", NoTls)?;

    successful_tx(&mut client)?;
    println!("successful done");

    let res = rolled_back_tx(&mut client);
    assert!(res.is_err());

    // Query data and print.
    println!("Color");
    for row in client.query( "SELECT name from cat_colors", &[])? {
        let name: &str = row.get(0);
        println!("{name}");
    }
    Ok(())
}

fn successful_tx(client: &mut Client) -> Result<(), Error> {
    let mut tx = client.transaction()?;

    tx.batch_execute("DELETE FROM cats")?;
    tx.batch_execute("DELETE FROM cat_colors")?;
    tx.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&"lavender"])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&"blue"])?;
    tx.commit()
}

fn rolled_back_tx(client: &mut Client) -> Result<(), Error> {
    let mut tx = client.transaction()?;

    tx.batch_execute("DELETE FROM cat_colors")?;
    tx.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&"grey"])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&"black"])?;

    // Since this table has a UNIQUE constaint on color names, this will fail.
    tx.execute("INSERT INTO cat_colors (name) VALUES ($1)", &[&"grey"])?;

    tx.commit()
}