Hacker News Reader

Comment by Diggsey | Hacker News Reader

SQL, while not great, does have some advantages that are hard to beat:

- Everyone knows at least some basic SQL, even non-technical users have often encountered it in some form.

- The documentation for eg. PostgreSQL is for SQL, so if you write queries in anything else you have to mentally translate back and forth, and so you need to know SQL anyway.

- Any external tools you use to interact with the database will use SQL.

- Changing the queries doesn't require an expensive compilation step - `sqlx` gets the best of both worlds in this respect, in that it is able to type-check the parameters and rely on the database itself to validate the query, so you don't end up with a ton of type-system shenanigans that increase compilation times.

Maybe for a brand new database, a better query language could win out, but having used sqlx I can't imagine going back to a query-builder style interface for existing SQL databases.

View on HN

I am toying with spreadsheet backends these days and it's had me wondering - do spreadsheet query languages offer value that SQL is missing? Beyond "people are used to it" of course.

One thing i am wanting beyond SQL though is a more Object oriented language. Ie i want something SQL-like (or more specifically PRQL, which i find very nice), but without the being so tailored towards a flat data structure. I want to use it on JSON/etc.

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.

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)

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.

BTW, do you suggest a LINQ kind of integration in Rust? I just found the Microsoft has a Rust for C#/.NET developers resource [1][2] which is more about integration I think. It is a little weird that there is not Rust.NET yet. .NET is not a garbage collection only platform. I guess Microsoft is working internally on this.

[1] https://microsoft.github.io/rust-for-dotnet-devs/latest/linq... [2] https://microsoft.github.io/rust-for-dotnet-devs/latest/

I'm impressed by this individual's effort to bring rust to the clr: https://github.com/FractalFir/rustc_codegen_clr

It is already way more complete given it's age than I would have expected.

I agree that the clr is a more natural target than, let's say, the JVM (at least until Project Valhalla comes out).

Reply on news.ycombinator.com