postgres-query

This crate provides convenience macros and traits which help with writing SQL queries and gathering their results into statically typed structures.

Documentation

Example

// Connect to the database
let client: Client = connect(/* ... */);

// Construct the query
let query = query!(
    "SELECT name, age FROM people WHERE age >= $min_age",
    min_age = 18
);

// Define the structure of the data returned from the query
#[derive(FromSqlRow)]
struct Person {
    age: i32,
    name: String,
}

// Execute the query
let people: Vec<Person> = query.fetch(&client).await?;

// Use the results
for person in people {
    println!("{} is {} years young", person.name, person.age);
}

Features

Extractors

This crate allows you to extract the result of queries simply by tagging a struct with the #[derive(FromSqlRow)] attribute:

#[derive(FromSqlRow)]
struct Book {
    id: i32,
    title: String,
    genre: String,
}

let books: Vec<Book> = query!("SELECT * FROM books")
    .fetch(&client)
    .await?;

Multi-mapping

You may also extract multiple structures from a single row. This can be useful when you are joining two tables. As a motivating example, we can store an Author instance inside a Book instance, which can be easier to work with:

#[derive(FromSqlRow)]
#[row(split)]
struct Book {
    #[row(split = "id")]
    id: i32,
    title: String,
    genre: String,

    #[row(flatten, split = "id")]
    author: Author,
}

#[derive(FromSqlRow)]
struct Author {
    id: i32,
    name: String,
    birthyear: i32,
}

let books: Vec<Book> = query!(
        "SELECT books.*, authors.* 
        FROM books
        INNER JOIN authors ON authors.id = books.id"
    )
    .fetch(&client)
    .await?;

Notice the #[row(split = "id")] attributes on the fields in Book. In order to extract values correctly we have to first split the row into smaller segments. We do this by by specifying that the first occurrence of id is part of the book and the second id part of the author. The rest is done for you.

The splits/segments would look like this:

Splits:   id                id
Columns:  id, title, genre, id, name, birthyear
Segments: +-----Book-----+  +-----Author------+

If we wanted to reuse an already existing Book we could just as easily do the following:

#[derive(FromSqlRow)]
#[row(split)]
struct Listings {
    #[row(flatten, split = "id")]
    book: Book
    #[row(flatten, split = "id")]
    author: Author,
}

One-to-many Relationships

In the previous examples we had a Book that contained an Author. This is what is called a many-to-one relationship, since one book only has one author, but many books may share the same author (or so we assume anyway). What if you instead had Author an author that contained many Books? We know that one author may write many books, so that is a one-to-many relationship. We can write an extractor for that case as well:

#[derive(FromSqlRow)]
#[row(split, group)]
struct Author {
    #[row(split = "id", key)]
    id: i32,
    name: String,
    birthyear: i32,

    #[row(split = "id", merge)]
    books: Vec<Book>,
}

#[derive(FromSqlRow)]
struct Book {
    id: i32,
    title: String,
    genre: String,
}

let authors: Vec<Author> = query!(
        "SELECT authors.*, books.*
         INNER JOIN books ON books.author = authors.id
         GROUP BY authors.id"
    )
    .fetch(&client)
    .await?;

Dynamic queries

Queries may be constructed from either a string literal, in which case parameter bindings are computed at compile time, or any other String dynamically at runtime. The same is true for parameter bindings, which in the latter case can be added dynamically.

Let's say that we wanted to dynamically add filters to our query:

// We have the query we want to execute
let mut sql = "SELECT * FROM people".to_string();

// and some filters we got from the user.
let age_filter: Option<i32> = Some(32);
let name_filter: Option<&str> = None;

// Then we dynamically build a list of filters and bindings to use:
let mut filters = Vec::new();
let mut bindings = Vec::new();

// We add the filters as needed.
if let Some(age) = age_filter.as_ref() {
    filters.push("age > $min_age");
    bindings.push(("min_age", age as Parameter));
}

if let Some(name) = name_filter.as_ref() {
    filters.push("name LIKE $name");
    bindings.push(("name", name as Parameter));
}

// And append them to the query.
if filters.len() > 0 {
    sql += &format!(" WHERE {}", filters.join(" AND "));
}

// Then we can use it as normal.
let query: Query = query_dyn!(&sql, ..bindings)?;

License

Licensed under either of Apache License, Version 2.0 or MIT license at your option.

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in rust-postgres-query by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.