Day 11 - postgres

Relevancy: 1.9 stable (macros only on nightly)

Yes, I'm biased. PostgreSQL is my favorite SQL database. There is already a pure Rust driver for PostgreSQL - the postgres crate which will be the subject of today's article.

Connecting

In this and the following examples we will assume a Postgres user rust with password rust and an existing database named... well, rust.

day11.rs

extern crate time;

day11.rs

day11.rs

fn sql_macro() {
    let query = sql!("select '{4, 5, 6}'::int[]");
    println!("{:?}", query);
}

#[cfg(not(target_family="unix"))]
fn sql_macro() {
    println!("TODO");

The Connection type has a few methods related to making queries; perhaps the simplest one is execute() which immediately executes the query and returns the number of modified rows (or an error). This method can be used for example for insert/update queries but also DDL as shown below.

day11.rs

}

fn main() {
    println!("24 days of Rust - postgres (day 11)");
    let dsn = "postgresql://rust:rust@localhost/rust";
    let conn = match Connection::connect(dsn, TlsMode::None) {
        Ok(conn) => conn,

The second argument looks slightly awkward, but that's the way of telling execute() that the query takes no parameters. Later on we will see a few examples that use query parameters.

Prepared queries and statements

Let's add a few rows to our table. We will use the prepare() method.

day11.rs

Err(e) => {
            println!("Connection error: {:?}", e);
            return;
        }
    };
    conn.execute("create table if not exists blog (
        id serial primary key,
        title \
                  varchar(255),
        body text)",
                 &[])
        .expect("Table creation failed");

The query was prepared only once and what we got (after some crude error handling) is a Statement value. We can use its execute() method to actually run the query with the supplied parameters (note the borrowing).

To read the data from the database we will use the same prepare() method in conjunction with the query() method of a Statement. There's a significant difference between execute() and query(): the former returns just the number of affected rows, while the latter returns a collection of Row values.

day11.rs

let stmt = match conn.prepare("insert into blog (title, body) values ($1, $2)") {
        Ok(stmt) => stmt,
        Err(e) => {
            println!("Preparing query failed: {:?}", e);
            return;
        }
    };
    for i in 1..5 {
        let title = format!("Blogpost number {}", i);
        let text = format!("Content of the blogpost #{}", i);
        stmt.execute(&[&title, &text]).expect("Inserting blogposts failed");
    }
    let stmt = match conn.prepare("select id, title, body from blog where id < $1") {
        Ok(stmt) => stmt,

Keep in mind that the get() method will panic if it encounters incompatible types, for example if we changed String to i32 above. There's also a safer get_opt() method returning a Result instead of panicking.

Advanced PostgreSQL types

All this is a bit boring so far. One of the reasons developers love PostgreSQL is its selection of interesting data types. Let's see how to use them in Rust (hint: it's kinda cool). We'll start from writing a generic helper function to read a single value from the first column of the first row.

day11.rs

use postgres::{Connection, TlsMode};
use postgres::types::FromSql;

day11.rs

fn get_single_value<T>(conn: &Connection, query: &str) -> PgResult<T>
    where T: FromSql
{
    println!("Executing query: {}", query);
    let stmt = conn.prepare(query)?;
    let rows = stmt.query(&[])?;
    let row = rows.iter().next().unwrap();
    row.get_opt(0).unwrap()

We use the try! macro to minimize the noise from error handling. Now let's see it in action. The more interesting types like arrays, ranges etc. come from a few additional crates: postgres_array and postgres_range.

day11.rs

extern crate rustc_serialize;
extern crate time;
extern crate postgres;
extern crate postgres_array;

day11.rs

use postgres::Result as PgResult;
use postgres_array::Array;
// use postgres_range::Range;

use rustc_serialize::json::Json;

day11.rs

Err(e) => {
            println!("Preparing query failed: {:?}", e);
            return;
        }
    };
    let max_id: i32 = 3;
    let rows = stmt.query(&[&max_id]).expect("Selecting blogposts failed");
    for row in rows.iter() {
        let id: i32 = row.get("id");
        let title: String = row.get("title");
        println!("ID={}, title={}", id, title);
    }
    println!("{:?}", get_single_value::<bool>(&conn, "select 1=1"));
    println!("{:?}", get_single_value::<i32>(&conn, "select 1=1"));

    type IntArray = Array<Option<i32>>;
    let arr = get_single_value::<IntArray>(&conn, "select '{4, 5, 6}'::int[]");
    println!("{:?}",
             arr.map(|arr| {
                 arr.iter()
                     .filter_map(|x| *x)

$ cargo run
Executing query: select 1=1
Ok(true)
Executing query: select 1=1
Err(WrongType(Bool))
Executing query: select '{4, 5, 6}'::int[]
Ok([4, 5, 6])
Executing query: select '{"foo": "bar", "answer": 42}'::json
Ok(Object({"answer": U64(42), "foo": String("bar")}))
Executing query: select '[10, 20)'::int4range
Ok([10,20))
Executing query: select '[2015-01-01, 2015-12-31]'::tsrange
Ok([Timespec { sec: 1420070400, nsec: 0 },Timespec { sec: 1451520000, nsec: 0 }])

Fantastic! The error handling is still there when we need it and we get values of reasonable Rust types.

Compile-time SQL checking

There is another crate worth mentioning here - postgres_macros. It provides the sql! macro that validates correctness of the SQL query given as argument at compile time.

let query = sql!("select '{4, 5, 6}'::int[]");
let this_wont_compile = sql!("eslect '{4, 5, 6}'::int[]");

See also