While SQLite is fine for prototyping and simpler use cases, a real relational database management system can make the life of a developer much easier. One such sophisticated database system is PostgreSQL. To integrate postgres in Rust, we have the postgres crate on crates.io. It's a native Rust client, meaning that it does not ride on a C library but implements the whole protocol in Rust. If the API looks familiar to the rusqlite crate, this is deliberate; the SQLite client's API is actually based on the postgres client.The postgres crate supports some of PostgreSQL's unique features, such as bit vectors, time fields, JSON support, and UUIDs.

In this section, we'll explore interacting with postgres by creating a sample program that initializes the postgres database and does a few inserts and queries on the database. We assume that you have already set up the database on your system. The PostgreSQL version used for the example is 9.5.

Postgres comes with its command line tool called psql which can be used to run queries, examine tables, manage roles, view system information and much more. You can view the postgres version running on your system by running the following commands inside the psql prompt. First we'll launch psql by running:

$ sudo -u postgres psql

Once we are within the psql, we run the following command at the prompt:

postgres=# SELECT version();

Running the command above, gives the following output:

PostgreSQL 9.5.14 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

To make this example simpler, we'll reuse the same books data that we used in our rusqlite demo. We'll use the default "postgres" user in the following examples, with the password as "postgres". You'll need to adapt the following examples to match your new user. Let's fire up a new project by running cargo new postgres_demo. Here's our dependencies in Cargo.toml:

# postgres_demo/Cargo.toml

[dependencies]
postgres = "0.15.2"
serde = { version = "1.0.82"}
serde_derive = "1.0.82"
serde_json = "1.0.33"

Let's run through the code in main.rs:

// postgres_demo/src/main.rs

use postgres::{Connection, TlsMode};

const DROP_TABLE: &str = "DROP TABLE IF EXISTS books";
const CONNECTION: &str = "postgres://postgres:postgres@localhost:5432";
const CREATE_TABLE: &str = "CREATE TABLE IF NOT EXISTS books 
                            (id SERIAL PRIMARY KEY,
                            title VARCHAR NOT NULL,
                            author VARCHAR NOT NULL,
                            year SERIAL)";

#[derive(Debug)]
struct Book {
    id: i32,
    title: String,
    author: String,
    year: i32
}

fn reset_db(conn: &Connection) {
    let _ = conn.execute(DROP_TABLE, &[]).unwrap();
    let _ = conn.execute(CREATE_TABLE, &[]).unwrap();
}

We have a bunch of string constants for connecting to the database and creating and deleting the books table. Next is our function main:

// postgres_demo/src/main.rs

fn main() {
    let conn = Connection::connect(CONNECTION, TlsMode::None).unwrap();
    reset_db(&conn);

    let book = Book {
        id: 3,
        title: "A programmers introduction to mathematics".to_string(),
        author: "Dr. Jeremy Kun".to_string(),
        year: 2018
    };

    conn.execute("INSERT INTO books (id, title, author, year) VALUES ($1, $2, $3, $4)",
                 &[&book.id, &book.title, &book.author, &book.year]).unwrap();

    for row in &conn.query("SELECT id, title, author, year FROM books", &[]).unwrap() {
        let book = Book {
            id: row.get(0),
            title: row.get(1),
            author: row.get(2),
            year: row.get(3)
        };
        println!("{:?}", book);
    }
}

Because we are not using an ORM here, and just a low-level interface, we'll need to unpack the values into the database query manually. Let's run this program:

Here's an output of the program, along with a psql query of the table to show the contents afterwards:

First, we list our database with the dt command at the psql prompt. Following that, we use the query, that is, "select * from books".

That's the basics o interacting with PostgreSQL with Rust. Next, let's explore how we can make our database queries efficient by using the concept of connection pooling.