The closest we came so far to bridging this gap in strictly typed language like Rust is SQLx, which creates a struct based on the database types returned by a query. This is validated at compile time against a database, which is good, but of course there is no guarantee that the production database will have the same types. Easiest mistake to make is to design a query against your local Postgres v15 and hit a runtime error in production running Postgres v12, e.g. a function like gen_ramdom_uuid() doesn't exist. Another is to assume a migration in production was actually executed.

In duck-typed languages like Ruby, the application objects are directly created from the database at runtime. They are as accurate as possible, since the schema is directly read at application startup. Then of course you see developers do something like:

    if respond_to?(:column_x)
        # do something with column_x
    end

- 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.

This is nuts.

SQL is a high level language. It's higher level than python or rust. It's basically a declarative statement that's almost english-like and it's specifically DESIGNED to be more readable and easier to use by humans. It compiles down into many procedures that don't easily map to the SQL statement itself. You can't get any higher level than SQL.

The issue here is that the database exists at the bottleneck of web dev. It's where state mutation happens and it's essentially usually the slowest part of the pipeline in a computer. Yet instead of having fine grained low level control over this part of the pipeline, we have a high level language on top of it. So if we want to optimize this part of the pipeline we have to HACK the query. We have to make the abstraction layer leaky in the API itself with EXPLAIN. We can't do direct optimizations because SQL is so high level. It is a bit of a problem but the industry is so entrenched in SQL that it's actually 10x more efficient to just use it then to develop an API that's more appropriate for this level. SQL is tech debt we are sort of stuck with. The ideal API would be one that is both high level but allows fine grained control... but we don't have one yet.

To use machine learning analogies. SQL is a local optima. There's a much more optimal language somewhere in this space but we are stuck in the local optima and it's likely we will never end up finding the actual optimal api.

In short SQL is the furthest thing from LLVM IR. It's crazy. You cannot treat it as the same thing. If you do there are huge problems.

The problem with rust query and the problem with ORMs in general is that the API for these libraries are in itself high level. They are HIGH level abstractions ON TOP of high level abstractions. You want to optimize a query now? Well you need to hack the first high level abstraction in such a way that it hacks the second high level abstraction such that it produces optimized compiled procedures. That's the problem here.

All this ORM stuff is just programmer OCD. We don't want to do meta programming where we have another language living as a string in our web app. We want everything fully integrated so we create an abstraction in attempt to get rid of an abstraction that was intended to be an abstraction in itself. It's aesthetics and the aesthetics actually makes life harder.

> The existing libraries don't provide the compile time guarantees that I want and are verbose or awkward like SQL.

Worth noting: diesel definitely fulfills the "providing compile time guarantees" criteria.

Here's where I stand on the inevitable ORM-vs-no-ORM debate that's about to erupt: I like typesafe query builders that don't abstract over sql ( I'd put diesel in this category, and i would not put activerecord or djangos orm or sealORM in this category).

It looks like rust-query will lean towards the full-ORM side of that spectrum. Not my cup of tea, but the tea industry can accommodate many flavors :)

I saw some replies by the diesel maintainer about how the creator of this Rust-Query might not have really understood in-depth how diesel worked and wanted to write their own solution, and there's nothing wrong with that, of course, but this thread might be some good context for others asking themselves similar questions.

[0] https://www.reddit.com/r/rust/s/6midd79iDo

``` schema!{ User { name: String, }, Story { author: User, title: String, content: String, }, } ```

This point confuses me. If we're thinking about a web server, you'll pass your data to the frontend with a row ID, so that they can refer to and modify that data in another request? How would it work otherwise?

Am I missing something? I can think of some use cases, but this limitation cuts down on the uses I'd have for this massively.

Although I partially agree with the high level idea of this statement (let's accept the reality; no mature database is going to support your new shiny query language anytime soon), SQL is not the most convenient language to be written by code generator. Actually this is a sort of nightmare; a seemingly simple plan optimization would completely change the layout of the query so you're going to have a decent chance to write a full query rewrite stage just for the sake of workaround for a specific query engine. Google's SQL pipe proposal seems to make it a bit better but it has the same problem of new query languages unless it's broadly adopted.

While I liked the idea of having strongly typed queries I’ve lately found out that sometimes they unnecessarily slow me down during the development process, I’m considering going back to the good old prep statements and binding values manually.

But I have to admit, this one bit almost made me stop reading:

> For those who don't know, SQL is the standard when it comes to interacting with databases.

I can scarcely imagine anyone who both 1. would want to use this and 2. doesn’t know what SQL is. So if I’m reading this, and you’re telling me what a database is, I assume you assume I’m an idiot. This makes me wonder if it has design choices to protect hypothetical Rust-using, DB-using idiots from themselves at the expense of making it hard to handle edge cases.

I’m sure you didn’t mean it that way, but that’s how it rubbed me. Maybe consider leaving out the extremely basic introductory bits.

I can't use yours in production yet as it only supports SQLite, but I'll keep my eyes peeled for updates.

We're using Sqlx and Diesel in production currently.

Sqlx is great because it's not an ORM and lets you write "type checked" raw SQL. It frustrates us in the fact that you can't write dynamic queries and have them be type checked, though. No `IN` clauses or conditional predicates, only static queries with simple bindings get type checked.

Love to see more ORMs like yours! Diesel isn't our favorite.

Tables, joins, etc. should all be representable within the language's type system and standard libraries. Connectivity to a database should be handled by plugins/implementations of storage engines. If no storage engine is used, the relational data just lives in RAM like any other variable, collection, etc.

It's kind of the opposite of an ORM. An ORM tries to bolt a relational database into an OOP or other language model. This wraps the language around relational database concepts, fixing the impedance mismatch in the language rather than with a translation layer.

It also means one does not have to reinvent relational concepts (badly) in every application with a complex data model even if that application is not backed by a database.

I mean why reinvent the wheel? we live in a time where the DB itself is a REST API with postgREST.

https://postgrest.com