Hacker News Reader

Comment by jeltz | Hacker News Reader

Yeah, I am using sqlx and it is amazing. The only thing it cannot do is help you in the case when you actually need a query builder, e.g. when you need support for queries with dynamic filters and sort orders. But other than that it is much nicer to work with than every other query library I have used.

View on HN

You can add sea-orm on top of sqlx for a query builder. It can also introspect your SQL database and generate entity types.

I built my own back in the days when I worked for Prisma. None of these crates existed. It's not that complex if you limit the builder to queries you need in your application.

But, to be honest, jOOQ or LINQ would be awesome to get for Rust.

FWIW, you very rarely need to make the query itself dynamic. After all, SQL alone is powerful enough to do conditionals.

For example:

    SELECT * FROM example
    WHERE ($1 IS NULL OR field1 = $1)
    AND ($2 IS NULL OR field2 = $2)
    ...

When you supply the parameters to this prepared statement, the constant conditions should be optimized away by the query planner if you're using a decent database.

Those tricks cover a very small subset of dynamic queries, and if you don't give your developers a proper tool to do dynamic queries in, your code will start growing these funny string-formatting-based query-mangling functions that have god-knows-what escaping problems behind them.

That has not been my experience. SQL is a fully fledged programming language so there's very little that you can't express this way.

What are you doing that requires dynamically building a query?

> That has not been my experience

Well, it has been mine. That trick you suggested works for the simple binary "either include this WHERE clause filter or don't" -- which, agreed, is something that comes up often enough -- but it's only in the simplest of CRUD apps where that's all I've needed.

A quick example: I have a REST endpoint that updates a resource. The JSON fields that map to the DB columns can be missing (leave the column value unchanged), null (set the column value to NULL), or have a value (set the new provided value). The list of assignments in the UPDATE needs to be dynamic; sometimes there might be only one column being updated, sometimes two, three, four five...

Isn’t the standard just to set the unchanged column to itself?

In this case you just set the column value to the existing value...

It's a fully fledged, horrible language. Anything beyond basic queries is unreadable, ESPECIALLY when it's done in plain strings in another language. There's not even a way to not have to repeat values EVERY SINGLE TIME (think variables or constants in every other language).

Oh, but what about <feature>? Well, is that SQL, or a frankensteined version of SQL, aka a "dialect"?

SQL is the JavaScript of databases, and we'll be better for it once we admit this.

As this claim comes up quite often I have a set of examples I typically ask these people to provide. So far I've got so response, maybe you can point out how you would implement one or more of the following queries using `sqlx::query!`?

* A batch insert query for mysql

* A conditional where clause for postgresql that allows the user to dynamically specify the column + filter operation + values over e.g. a rest end point. Each column can appear zero, one or multiple times with different or even the operations.

* A IN expression with a list of values provided by a rust `Vec<_>` (so dynamically sized) for sqlite.

Can you show an example where you have 4 possible fields to filter on, and then one more where the sorting field is dynamic? (Not the value, but the column)

Reply on news.ycombinator.com