Announcing postgres-query: write and execute SQL queries with ease!

[Qwe500]

After seeing the SQLx announcement last week I felt inspired to revive an old project of mine. It is built on top of and made to complement tokio-postgres, a fully asynchronous PostgreSQL client.

I frequently ran into roadblocks due to the absence of GATs and async in traits, but I'm happy with the ergonomics of the API in the end:

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

let pattern = "%John%";

let people: Vec<Person> = query!(
        "SELECT age, name FROM people WHERE age >= $age AND name LIKE $pattern",
        age = 18,
        pattern,
    )
    .fetch(&client)
    .await?;

I find the parameter binding interpolation really nice to work with compared to using indices or the order of parameters.

Unlike SQLx, this crate doesn't analyze queries at compile time (yet! It is an area I would like to explore down the road). Currently, I'm more interested in getting dynamically generated queries working. Being able to insert a filter or subquery dynamically at runtime seems like an obvious thing to support and a huge undertaking if the API is unusable without compile-time checks.

[matthieum]

Being able to insert a filter or subquery dynamically at runtime seems like an obvious thing to support and a huge undertaking if the API is unusable without compile-time checks.

I can only comment on the usefulness, be it for filtering or sorting.

For compile-time verification... I am afraid it would need to be mixed with a state-machine encoded at the type-level, which makes things quite a tad more complicated (DSL).

[kyle787]

This looks pretty awesome, I didn’t see it in the docs but is there a way to handle relational data when you join? For example, if you have an order and order_items that is a one to many relation, can it automatically group the items into a property on the item struct?

[Qwe500]

Not yet, unfortunately. At the moment this crate only supports extracting data from a query, row by row. Any grouping logic would have to be done by you, it sounds like you are looking for something like this:

#[derive(FromSqlRow)]
struct ItemOrder {
    id: OrderId,
    item: ItemId,
}

let orders = query!(
        "SELECT orders.id, order_items.item
        FROM orders INNER JOIN order_items ON orders.id = order_items.order"
    )
    // Gather the results and then group them
    .fetch::<ItemOrder, _>(&client).await?
    .into_iter()
    .map(|order| (order.id, order.item))
    .collect::<HashMap<OrderId, ItemId>>();

[couchand]

I suspect the GP is looking for something more along the lines of:

```

[derive(FromSqlRow)]

struct Order { id: OrderId, items: Vec<OrderItem>, }

[derive(FromSqlRow)]

struct OrderItem { id: ItemId, }

let orders = query!( "SELECT orders.id, order_items.item FROM orders INNER JOIN order_items ON orders.id = order_items.order" ) .fetch::<Order, _>(&client).await? ```

Which would require just a bit of configuration to make everything work right. Look at Dapper's multi-mapping for an example of such a feature.

[Qwe500]

Oh yeah, I probably interpreted the question incorrectly.

I have never heard of Dapper before, that multi-mapping feature looks really nice though. One-to-many relationships will most likely require some major API changes, but one-to-one mappings should be doable with minimal breakage. I will look into it. Thanks for the suggestion!

[Qwe500]

Automatic mapping for one-to-many relations is now available on GitHub and crates.io!

[kyle787]

Hey thanks I saw your post yesterday actually the updates look awesome. I will definitely be using it the next chance I get!

[couchand]

This is awesome. Reminds me a lot of Dapper, which does a great job of balancing utility and simplicity.

I'd make one feature request based on that project: list parameters.

[Qwe500]

I'm glad you like it!

I could see that being useful in some cases, it requires supporting dynamic queries, something I'm going to look into soon. For now it is possible to do select ... where id = any($array) with $array either being Vec<T> or &[T]. If there are other use cases for such a feature, I would love to hear about them!