Rust-Query

My main concern with application-defined schemas is that this schema is validated by the wrong system. The database is the authority on what the schema is; all other layers in your application make assumptions based on effectively hearsay.

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

To summarize, I think application-defined schemas provide a false sense of security and add another layer of work for the engineer.

>My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR.

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.

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.

I am really happy to see some more exploration in the typesafe-db-access-in-Rust space.

> 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 :)

When this was posted on reddit, I saw someone [0] ask how it was different from diesel-rs (and diesel-async, the officially supported add-on crate, lest someone says diesel doesn't have async).

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

This is awesome, but also kind of hurts since I was working on something similar here: https://github.com/andrewbaxter/good-ormning

They're very similar: Both define schema version "snapshots" not "diffs", with diffing happening automatically, and check queries based on the latest defined version, don't require a running database, etc.

The main difference seems to be about queries. My approach was to try to replicate sql query structure directly with the query builders, rather than abstract away from sql, whereas this appears to aim for something that's less similar to the original sql. I don't consider myself an sql expert and I've seen sql abstractions that aren't able to replicate all functionality, but seeing the reddit debate with the diesel author this seems fairly thorough.

FWIW in `good-ormning` I was thinking of getting rid of the query builders/my own query ast and having a macro that parses with `datafusion-sqlparser-rs` and checks that ast instead, but it's a significant change.

FWIW I think SQL is awful too, so I'm really happy to see the support here and I hope rust-query takes off.

This looks interesting as an approach to relating schema to data types. One aspect that feels very counter-intuitive/unidiomatic is that, if I understand correctly, in your example there is no Schema enum. Usually this sort of macro does not erase the type defined, and I would find it extremely confusing that I cannot reference, say, Schema::User{..}. It would be clearer to me if this were defined inside of a macro_rules macro, more like lazy_static e.g.

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

Good luck with this, Lucas! We need all of the innovation in the Rust DB space we can get.

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.

This is close to something I've wanted to see for a long time: a language where relational models are first class citizens and the engine for data storage is abstracted away.

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.

It seems that migrations with individual row level manipulation would be tragically slow to execute to me? Like, I have a table with a billion rows, and any normal update statement takes up to an hour. I don’t want to think what an update per row would take.

Diesel basically being the same as Postgres SQL is an awesome feature. I'd much rather than then an abstracted query language which is difficult to understand.

> The actual row numbers are never exposed from the library API. Application logic should not need to know about them.

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.

Looks nice! I’ve been using SeaQuery for a while now but documentation is not that great if you’re looking to create some more advanced queries.

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.

Thanks for this! I am looking forward to putting this together with the Zola CMS for some hobby applications.

>My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR.

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.

Thanks for releasing this! More ideas are great and welcome, and even I don’t use this directly, maybe the ORM I use will like some of the features and add them.

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.

For folks reading who don't have experience in this space: this kind of thing is usually a bad idea. On the surface it looks like a neat thing, and the people who write these components clearly think they're a good thing. But not so much.

Nit: a DB query can never be made type safe (avoiding runtime failure) because its correctness depends on the state of an external service, which may change at any point in time.

It looks like you just got this blog set up. Congratulations!

everytime I see something like this I question myself why I would want or need this over postgREST.

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

https://postgrest.com