[−][src]Crate postgres_query

Helper macros and traits built around tokio-postgres to define queries with human readable parameters and return values.

Example

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

// Construct the query
let query = query!(
    "SELECT age, name 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?;

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

Queries

The preferred way of constructing a new Query is through the query! macro. It uses a syntax similar to the format!(...) family of macros from the standard library. The first parameter is the SQL query and is always given as a string literal (this might be relaxed in the future). This string literal may contain parameter bindings on the form $ident where ident is any valid Rust identifier ($abc, $value_123, etc.).

let age = 42;
let insert_person = query!(
    "INSERT INTO people VALUES ($age, $name)",
    name = "John Wick", // Binds "$name" to "John Wick"
    age,                // Binds "$age" to the value of `age`
);

During compilation the query is converted into the format expected by PostgreSQL: parameter bindings are converted to using numbers ($1, $2, etc.) and the actual parameter values are put into a 1-indexed array. The code snippet above would be expanded into the following:

let age = 42;
let insert_person = Query::new_static(
    "INSERT INTO people VALUES ($1, $2)",
    vec![&age, &"John Wick"],
);

Dynamic Queries

If necessary, queries may be constructed from &strs at runtime instead of the usual compile-time string literals expected by the query! macro. This is achieved by using the query_dyn! macro instead. In addition to dynamic queries, parameter bindings may also be dynamically:

let mut sql = "SELECT * FROM people WHERE name = $name".to_string();
let mut bindings = Vec::new();

// Add a filter at runtime
sql += " AND age > $min_age";
bindings.push(("min_age", &42 as Parameter));

let query: Result<Query> = query_dyn!(
    &sql,
    name = "John",
    ..bindings,
);

Using dynamic queries does introduce some errors that cannot be caught at runtime: such as some parameters in the query not having a matching binding. Because of this the value returned by the query_dyn! macro is not a Query but a Result<Query> which carries an error you must handle:

let mut sql = "SELECT * FROM people".to_string();
sql += " WHERE age <= $max_age AND name = $name";

let query: Result<Query> = query_dyn!(
    &sql,
    name = "John",
    // Forgot to bind the parameter `max_age`. 
    // Will result in an error.
);

assert!(query.is_err());

Data Extraction

In addition to helping you define new queries this crate provides the FromSqlRow trait which makes it easy to extract typed values from the resulting rows. The easiest way to implement this trait for new structs is to use the included derive(FromSqlRow) macro.

If used on a tuple struct, values will be extracted from the corresponding columns based on their position in the tuple.
If used on a stuct with named fields, values will be extracted from the column with the same name as the field.

#[derive(FromSqlRow)]
struct TupleData(i32, String);

#[derive(FromSqlRow)]
struct NamedData {
    age: i32,
    name: String,
};

Multi-mapping

If you query the same table multiple times it gets tedious to have to redefine structs with the same fields over and over. Preferably we would like to reuse the same definition multiple times. We can do this be utilizing "multi-mapping".

Partitions

Multi-mapping works by splitting the columns of rows returned by a query into multiple partitions (or slices). For example, if we had the query SELECT books.*, authors.* FROM ..., we would like to extract the data into two structs: Book and Author. We accomplish this by looking at the columns returned by the database and splitting them into partitions:

Columns:    id, title, release_date, genre, id, name, birthyear
Partitions: +------------Book-------------+ +------Author-----+

Partitioning schemes

There are two supported ways to partition a row: either we specify the number of columns required to populate each struct (in the example above: 4 columns for Book and 3 for author), or we split on the name of a column. The former should generally only be used when you know the number of columns isn't going to change. The latter is less prone to break provided you choose an appropriate column to split on (a good candidate is usually id as almost all tables have this as their first column).

You choose which partitioning scheme you want to use by using the provided attributes. In order to accomplish the partitioning in the example above we could split on the column name id:

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

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

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

Alternatively, we can make Author a part of the Book struct:

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

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

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

Many-to-one 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,
    release_date: 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?;

See the section on attributes for a more advanced in-depth explanation of multi-mapping.

Caching queries

From time to time you probably want to execute the same query multiple times, but with different parameters. In times like these we can decrease the load on the database by preparing our queries before executing them. By wrapping a client in a Caching struct this behaviour is automatically provided for all queries that originate from this crate:

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

// Wrap the client in a query cache
let cached_client = Caching::new(client);

for age in 0..100i32 {
    let query = query!("SELECT name, weight FROM people WHERE age = $age", age);

    // The query is prepared and cached the first time it's executed.
    // All subsequent fetches will use the cached Statement.
    let people: Vec<(String, i32)> = query.fetch(&cached_client).await?;
     
    /* Do something with people */
}

Re-exports

pub use crate::extract::FromSqlRow;

Modules

client	Abstractions over client-like types.
execute	Executing queries through a client.
extract	Extract typed values from rows.

Macros

query	Constructs a new query at compile-time. See also `query_dyn!`.
query_dyn	Constructs a new query dynamically at runtime. See also `query!`.

Structs

Caching	A client wrapper which caches prepared queries.
Query	A static query with dynamic parameters.

Enums

Error

Any error that this crate may produce.

Type Definitions

Parameter	A shorthand for types that can be treated as SQL parameters.
Result

Derive Macros

FromSqlRow

Extract values from a row.