26 ms·

Safe relational database queries using the Rust type system

270 points by lukastyrychtr 2y ago

tyleo 2y ago
It looks like you just got this blog set up. Congratulations!
echelon 2y ago
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.
- Simpliplant 2y ago
  After being frustrated with Diesel, I gave a try to SeaORM and I couldn’t be happier (have been using it for more than a year). It is not perfect but certainly the best ORM I have ever used myself (vs Diesel, ActiveRecord, SQLAlchemy)
  - kstrauser 2y ago
    How come? I’m new to the space and picked Diesel a couple weeks ago for a new project because it seemed the most recommended. What do you prefer about SeaORM?
    - Simpliplant 2y ago
      To me, SeaORM feels like a well-designed library while many things in Diesel feel like an afterthought or a hack. Which mostly manifests itself when doing something complex and debugging it. Also recommend reading https://www.sea-ql.org/SeaORM/docs/internal-design/diesel/ https://www.sea-ql.org/SeaORM/docs/internal-design/diesel/
      - kstrauser 2y ago
        I’ll check it out. Thanks!
      - echelon 2y ago
        sea-query looks a lot like jOOQ!
      - weiznich 2y ago
        Please note that this comparison is outdated since at least 2 years, given that diesel-async exists for more than 2 years now and this page completely forgets to mention it.
  - satvikpendem 2y ago
    SeaORM is not compile time safe by their own admission so it's automatically out of the running, personally speaking.
    - kelnos 2y ago
      That feels like a weird requirement to me; an ORM is by its very nature not going to be able to validate queries at compile-time. And even if you're writing queries directly, any time you need to write something dynamic, you're not going to be able to verify those at compile-time either.
      - satvikpendem 2y ago
        > an ORM is by its very nature not going to be able to validate queries at compile-time. I don't see why this is by its "very nature" when libraries like diesel or Prisma already do this. > any time you need to write something dynamic, you're not going to be able to verify those at compile-time either. Diesel can validate dynamic queries at compile time too, sqlx too to some extent.
- k972 2y ago
  Depending on what database you're using, you might be able to restructure your dynamic queries to fit a static form... WHERE ... AND CASE WHEN ? IS NULL THEN TRUE ELSE col ILIKE ? || '%' END AND CASE WHEN ? IS NULL THEN TRUE ELSE col2 IN (?) END Each "dynamic" parameter is then bound twice. (My personal preference is to use a CTE to bind and name my parameter values in the query instead of binding multiple times whenever possible).
  - kelnos 2y ago
    That only works when the dynamic portion is in the WHERE clause, though. I often need to dynamically generate UPDATEs and even sometimes INSERTs.
    - weiznich 2y ago
      It doesn't even work for all dynamic where clauses, but only for such that can lead to a fixed number of combinations. This approach doesn't work for dynamic API's that let users combination column + filters as required, which is rather common in complex web applications. (Think of the GitHub issue search box for example)
- written-beyond 2y ago
  My first Rust PR was for a Diesel Plugin crate, their macros weren't updated with Rusts newer spec. I honestly really enjoyed Diesel but it's extremely slow migration to async really made it lose traction. I've used SQLx extensively for projects after that and it was pretty decent.
  - weiznich 2y ago
    Please note that up until today an async rust database library does not give you any measurable performance advantage compared to a sync database library. In addition to that it won't even matter for most applications as they don't reach the required scale to even hit that bottleneck. As a matter of facts crates.io just run well on sync diesel up until maybe a month ago. They have now switched to diesel-async, for certain specific not-performance related reasons. The lead developer there told me specifically that from a performance point of view the service would have been fine with sync diesel for quite a while without problems, and that's with somewhat exponential growth of requests. Other than that the async rust ecosystem is still in a place that makes it literally impossible to provide strong guarantees around handling transactions and make sure that they ended, which is a main reason why diesel-async is not considered stable from my side yet. This problem exists in all other async rust database libraries as well, as it's an language level problem. They just do not document this correctly.
    - written-beyond 2y ago
      WEIZNICH! I am your biggest fan. Thank you so much for the amazing crates you've made and for Diesel. When I started my rust journey Diesel and Rocket were the first few crates I worked with and it really opened up my mind and I haven't turned back since. As for async, my primary concern for that was to make sure you minimise blocking code calls in your futures. Even if there weren't many performance gains to be made from Diesel itself being async but having database calls marked as Futures could theoretically help the runtime schedule and manage other threads. I'd like to thank you for you amazing work once more!
      - weiznich 2y ago
typicalset 2y ago
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, }, } ```
- pwdisswordfishz 2y ago
  I was just about to ask WTF is a Schema-typed value supposed to represent.
Diggsey 2y ago
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.
- jeltz 2y ago
  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.
  - Diggsey 2y ago
    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.
    - ris 2y ago
      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.
davidatbu 2y ago
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 :)
- pdimitar 2y ago
  > Worth noting: diesel definitely fulfills the "providing compile time guarantees" criteria. Also sqlx. I liked the article and I bookmarked the library but this statement from the author confused me a bit. At least two libraries give you pretty good compile-time guarantees. sqlx even gives you the option to specify a DB where it can check whether your queries will succeed at runtime.
  - weiznich 2y ago
    > Also sqlx. The guarantees provides by sqlx are less strong than what's provided by diesel due to the fact that sqlx needs to know the complete query statically at compile time. This excludes dynamic constructs like `IN` expressions or dynamic where clauses from the set of checked queries. Diesel also verifies that these queries are correct at compile time.
- Kinrany 2y ago
  Agree. SQL libraries should aim to make any valid query possible to express (though semantic equivalents are fine) while integrating query building into the rest of the language as seamlessly as possible.
kstrauser 2y ago
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.
- davidatbu 2y ago
  A more charitable take is that OP tried to make his blogpost more accessible to newbies at a very small verbosity cost for non-newbies. Fwiw: i'm totally fine with that. > This makes me wonder if it has design choices to ... I personally felt that the relevant design choices were explicitly enumerated, and so one needs not to deduce them from the rest of the text.
  - kstrauser 2y ago
    That’s fair, and I offered that mainly in the spirit of feedback. To me, and perhaps to no one else, but just to me, it was a little off putting. Like, “yeah, yeah, I know what SQL is. Do you think I don’t? Or did you just learn about it last month and you’re eager to share?” I’m certainly not gonna lose sleep over it. Someone came on HN to show us their neat new project. I wanted to give them my first impression to help them craft their future messaging. If they disagree, fine with me! They don’t owe me anything.
mattrighetti 2y ago
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.
- giovannibonetti 2y ago
  You might be interested in SQLx, since you write the SQL but the library generates the tedious bindings for you.
  - mattrighetti 2y ago
    That's my go-to! Maybe it was not super clear from my comment but I'm not using sea-query-orm but just the query builder, once the query is build you still have to feed it to sqlx (e.g [0]) :) [0]: https://github.com/SeaQL/sea-query/blob/master/examples/sqlx_postgres/src/main.rs#L29-L51 https://github.com/SeaQL/sea-query/blob/master/examples/sqlx...
ocschwar 2y ago
Thanks for this! I am looking forward to putting this together with the Zola CMS for some hobby applications.
ninetyninenine 2y ago
>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.
- vlovich123 2y ago
  > You can't get any higher level than SQL. Can’t you? Wouldn’t higher level be a goal oriented natural language like “what’s the most popular music file in the database” and an AI agent just figured out how to give you the answer?
Aeolun 2y ago
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.
- Hytak 2y ago
  Hi, migrations are 1 select statement + `n` insert statement for `n` rows right now. This might be improved to insert in batches in the future without changing the API.
  - Aeolun 2y ago
    I imagine a select on 1B rows will be equally problematic :) But you are right, it’s not something you necessarily have to worry about now. It just means it doesn’t work for our use case. Just figured I should point it out.
levkk 2y ago
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.
- IshKebab 2y ago
  This doesn't seem fundamentally different from any schema/API mismatch issue. For example using the wrong header for a C library, or the wrong Protobuf schema. I guess it would be good if it verified it at runtime somehow though. E.g. when you first connect to the database it checks Postgresql is the minimum required version, and the tables match what was used at compile time.
  - dietr1ch 2y ago
    It could be verified at runtime, but I haven't seen anyone trying to version/hash schemas and include that in the request. The workaround in practice seems to be to keep the DB behind a server that always™ uses a compatible schema and exposes an API that's either properly versioned or at least safe for slightly older clients. To be fair it's hard to get rid of the middleman and serve straight from the DB, it's always deemed too scary for many reasons, so it's not that bad.
  - Someone 2y ago
api 2y ago
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.
summerlight 2y ago
>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.
omani 2y ago
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 https://postgrest.com
- satvikpendem 2y ago
  Why would I want to interact with my database over the network when I can just...do it locally? It's orders of magnitudes slower and more wasteful and it doesn't even provide all the guarantees that using actual SQL or an ORM does.
  - kccqzy 2y ago
    If you are using PostgREST as intended, you don't have an application layer any more. It's not something extra you put in between the application and the database, but it replaces the application.
    - satvikpendem 2y ago
      Ah so it's like Hasura, it's a BaaS platform. Unfortunately I don't like putting all my logic in the client only.
      - kccqzy 2y ago
        No the logic is in the database. They encourage you to write functions to achieve custom logic in the database. Search for `create or replace function` in their tutorial to see an example.
        satvikpendem 2y ago
        Yeah that is also something I don't want to do, after having used Hasura at a previous place. After a while, the logic gets convoluted when it's all in the database only.
satvikpendem 2y ago
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 https://www.reddit.com/r/rust/s/6midd79iDo
- pstoll 2y ago
  When a sample showing a new SQL thing has eg a) no id types eg for User, just a string b) uses i64 for “stars” C) any other of a million things… I quickly conclude it’s likely a cute toy by someone who wanted to write a thing, not ever likely to be a battle-hardened effort brought forth by a high performance / scale database tortured soul. And I move on.
- rendaw 2y ago
  I'm (was? not sure after seeing this) working on a similar thing to this, and the most significant difference between this and diesel is that this has stronger safety guarantees by controlling migrations as well, whereas diesel just trusts the database is in the state you claim it is.
  - weiznich 2y ago
    You get the same safety guarantees with diesel by using the [`embed_migration!`](https://docs.diesel.rs/2.2.x/diesel_migrations/macro.embed_migrations.html https://docs.diesel.rs/2.2.x/diesel_migrations/macro.embed_m...) macro and running your applications on startup. Diesel decouples that as there are situations where you might want to work with a slightly different database as defined, for example if you don't control the database.
    - rendaw 2y ago
      I don't get the impression that there's any interaction or data sharing between the embedded migrations and the query generation part, that would validate the queries against the schema as defined by the migration -- is that wrong? The documentation doesn't seem to mention it at least.
mijoharas 2y ago
> 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.
- twiss 2y ago
  I'm guessing they're talking about https://www.complexsql.com/rowid-rownum/ https://www.complexsql.com/rowid-rownum/, i.e. the row numbers defined by the database, rather than the value of the ID column you typically create in each table. I.e., it should still be possible to create a column with a public identifier by which you can refer to the row, I assume.
- Hytak 2y ago
  Sending row IDs to your frontend has two potential problems: - Row IDs can be reused when the row is deleted. So when the frontend sends a message to the backend to modify that data, it might accidentally modify a different row that was created after the original was deleted. - You may be leaking the total number of rows (this allows detecting when new rows are created etc. which can be problematic). If you have nothing else to uniquely identify a row, then you can always create a random unique identifier and put a unique constraint on it.
skeptrune 2y ago
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.
runeks 2y ago
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.
- LudwigNagasena 2y ago
  It can be type safe in relation to the established contract between services.
rendaw 2y ago
This is awesome, but also kind of hurts since I was working on something similar here: https://github.com/andrewbaxter/good-ormning 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.
dboreham 2y ago
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.
Namm22 2y ago
[flagged]

270 points by lukastyrychtr 2y ago

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

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.

Simpliplant 2y ago
After being frustrated with Diesel, I gave a try to SeaORM and I couldn’t be happier (have been using it for more than a year). It is not perfect but certainly the best ORM I have ever used myself (vs Diesel, ActiveRecord, SQLAlchemy)
- kstrauser 2y ago
  How come? I’m new to the space and picked Diesel a couple weeks ago for a new project because it seemed the most recommended. What do you prefer about SeaORM?
  - Simpliplant 2y ago
    To me, SeaORM feels like a well-designed library while many things in Diesel feel like an afterthought or a hack. Which mostly manifests itself when doing something complex and debugging it. Also recommend reading https://www.sea-ql.org/SeaORM/docs/internal-design/diesel/ https://www.sea-ql.org/SeaORM/docs/internal-design/diesel/
    - kstrauser 2y ago
      I’ll check it out. Thanks!
    - echelon 2y ago
      sea-query looks a lot like jOOQ!
    - weiznich 2y ago
      Please note that this comparison is outdated since at least 2 years, given that diesel-async exists for more than 2 years now and this page completely forgets to mention it.
- satvikpendem 2y ago
  SeaORM is not compile time safe by their own admission so it's automatically out of the running, personally speaking.
  - kelnos 2y ago
    That feels like a weird requirement to me; an ORM is by its very nature not going to be able to validate queries at compile-time. And even if you're writing queries directly, any time you need to write something dynamic, you're not going to be able to verify those at compile-time either.
    - satvikpendem 2y ago
      > an ORM is by its very nature not going to be able to validate queries at compile-time. I don't see why this is by its "very nature" when libraries like diesel or Prisma already do this. > any time you need to write something dynamic, you're not going to be able to verify those at compile-time either. Diesel can validate dynamic queries at compile time too, sqlx too to some extent.
k972 2y ago
Depending on what database you're using, you might be able to restructure your dynamic queries to fit a static form... WHERE ... AND CASE WHEN ? IS NULL THEN TRUE ELSE col ILIKE ? || '%' END AND CASE WHEN ? IS NULL THEN TRUE ELSE col2 IN (?) END Each "dynamic" parameter is then bound twice. (My personal preference is to use a CTE to bind and name my parameter values in the query instead of binding multiple times whenever possible).
- kelnos 2y ago
  That only works when the dynamic portion is in the WHERE clause, though. I often need to dynamically generate UPDATEs and even sometimes INSERTs.
  - weiznich 2y ago
    It doesn't even work for all dynamic where clauses, but only for such that can lead to a fixed number of combinations. This approach doesn't work for dynamic API's that let users combination column + filters as required, which is rather common in complex web applications. (Think of the GitHub issue search box for example)
written-beyond 2y ago
My first Rust PR was for a Diesel Plugin crate, their macros weren't updated with Rusts newer spec. I honestly really enjoyed Diesel but it's extremely slow migration to async really made it lose traction. I've used SQLx extensively for projects after that and it was pretty decent.
- weiznich 2y ago
  Please note that up until today an async rust database library does not give you any measurable performance advantage compared to a sync database library. In addition to that it won't even matter for most applications as they don't reach the required scale to even hit that bottleneck. As a matter of facts crates.io just run well on sync diesel up until maybe a month ago. They have now switched to diesel-async, for certain specific not-performance related reasons. The lead developer there told me specifically that from a performance point of view the service would have been fine with sync diesel for quite a while without problems, and that's with somewhat exponential growth of requests. Other than that the async rust ecosystem is still in a place that makes it literally impossible to provide strong guarantees around handling transactions and make sure that they ended, which is a main reason why diesel-async is not considered stable from my side yet. This problem exists in all other async rust database libraries as well, as it's an language level problem. They just do not document this correctly.
  - written-beyond 2y ago
    WEIZNICH! I am your biggest fan. Thank you so much for the amazing crates you've made and for Diesel. When I started my rust journey Diesel and Rocket were the first few crates I worked with and it really opened up my mind and I haven't turned back since. As for async, my primary concern for that was to make sure you minimise blocking code calls in your futures. Even if there weren't many performance gains to be made from Diesel itself being async but having database calls marked as Futures could theoretically help the runtime schedule and manage other threads. I'd like to thank you for you amazing work once more!
    - weiznich 2y ago

typicalset 2y ago

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, }, } ```

pwdisswordfishz 2y ago
I was just about to ask WTF is a Schema-typed value supposed to represent.

Diggsey 2y ago

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.

jeltz 2y ago
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.
- Diggsey 2y ago
  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.
  - ris 2y ago
    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.

davidatbu 2y ago

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

pdimitar 2y ago
> Worth noting: diesel definitely fulfills the "providing compile time guarantees" criteria. Also sqlx. I liked the article and I bookmarked the library but this statement from the author confused me a bit. At least two libraries give you pretty good compile-time guarantees. sqlx even gives you the option to specify a DB where it can check whether your queries will succeed at runtime.
- weiznich 2y ago
  > Also sqlx. The guarantees provides by sqlx are less strong than what's provided by diesel due to the fact that sqlx needs to know the complete query statically at compile time. This excludes dynamic constructs like `IN` expressions or dynamic where clauses from the set of checked queries. Diesel also verifies that these queries are correct at compile time.
Kinrany 2y ago
Agree. SQL libraries should aim to make any valid query possible to express (though semantic equivalents are fine) while integrating query building into the rest of the language as seamlessly as possible.

kstrauser 2y ago

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.

davidatbu 2y ago
A more charitable take is that OP tried to make his blogpost more accessible to newbies at a very small verbosity cost for non-newbies. Fwiw: i'm totally fine with that. > This makes me wonder if it has design choices to ... I personally felt that the relevant design choices were explicitly enumerated, and so one needs not to deduce them from the rest of the text.
- kstrauser 2y ago
  That’s fair, and I offered that mainly in the spirit of feedback. To me, and perhaps to no one else, but just to me, it was a little off putting. Like, “yeah, yeah, I know what SQL is. Do you think I don’t? Or did you just learn about it last month and you’re eager to share?” I’m certainly not gonna lose sleep over it. Someone came on HN to show us their neat new project. I wanted to give them my first impression to help them craft their future messaging. If they disagree, fine with me! They don’t owe me anything.

mattrighetti 2y ago

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.

giovannibonetti 2y ago
You might be interested in SQLx, since you write the SQL but the library generates the tedious bindings for you.
- mattrighetti 2y ago
  That's my go-to! Maybe it was not super clear from my comment but I'm not using sea-query-orm but just the query builder, once the query is build you still have to feed it to sqlx (e.g [0]) :) [0]: https://github.com/SeaQL/sea-query/blob/master/examples/sqlx_postgres/src/main.rs#L29-L51 https://github.com/SeaQL/sea-query/blob/master/examples/sqlx...

ocschwar 2y ago

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

ninetyninenine 2y ago

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

vlovich123 2y ago
> You can't get any higher level than SQL. Can’t you? Wouldn’t higher level be a goal oriented natural language like “what’s the most popular music file in the database” and an AI agent just figured out how to give you the answer?

Aeolun 2y ago

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.

Hytak 2y ago
Hi, migrations are 1 select statement + `n` insert statement for `n` rows right now. This might be improved to insert in batches in the future without changing the API.
- Aeolun 2y ago
  I imagine a select on 1B rows will be equally problematic :) But you are right, it’s not something you necessarily have to worry about now. It just means it doesn’t work for our use case. Just figured I should point it out.

levkk 2y ago

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.

IshKebab 2y ago
This doesn't seem fundamentally different from any schema/API mismatch issue. For example using the wrong header for a C library, or the wrong Protobuf schema. I guess it would be good if it verified it at runtime somehow though. E.g. when you first connect to the database it checks Postgresql is the minimum required version, and the tables match what was used at compile time.
- dietr1ch 2y ago
  It could be verified at runtime, but I haven't seen anyone trying to version/hash schemas and include that in the request. The workaround in practice seems to be to keep the DB behind a server that always™ uses a compatible schema and exposes an API that's either properly versioned or at least safe for slightly older clients. To be fair it's hard to get rid of the middleman and serve straight from the DB, it's always deemed too scary for many reasons, so it's not that bad.
- Someone 2y ago

api 2y ago

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.

summerlight 2y ago

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

omani 2y ago

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 https://postgrest.com

satvikpendem 2y ago
Why would I want to interact with my database over the network when I can just...do it locally? It's orders of magnitudes slower and more wasteful and it doesn't even provide all the guarantees that using actual SQL or an ORM does.
- kccqzy 2y ago
  If you are using PostgREST as intended, you don't have an application layer any more. It's not something extra you put in between the application and the database, but it replaces the application.
  - satvikpendem 2y ago
    Ah so it's like Hasura, it's a BaaS platform. Unfortunately I don't like putting all my logic in the client only.
    - kccqzy 2y ago
      No the logic is in the database. They encourage you to write functions to achieve custom logic in the database. Search for `create or replace function` in their tutorial to see an example.
      - satvikpendem 2y ago
        Yeah that is also something I don't want to do, after having used Hasura at a previous place. After a while, the logic gets convoluted when it's all in the database only.

satvikpendem 2y ago

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 https://www.reddit.com/r/rust/s/6midd79iDo

pstoll 2y ago
When a sample showing a new SQL thing has eg a) no id types eg for User, just a string b) uses i64 for “stars” C) any other of a million things… I quickly conclude it’s likely a cute toy by someone who wanted to write a thing, not ever likely to be a battle-hardened effort brought forth by a high performance / scale database tortured soul. And I move on.
rendaw 2y ago
I'm (was? not sure after seeing this) working on a similar thing to this, and the most significant difference between this and diesel is that this has stronger safety guarantees by controlling migrations as well, whereas diesel just trusts the database is in the state you claim it is.
- weiznich 2y ago
  You get the same safety guarantees with diesel by using the [`embed_migration!`](https://docs.diesel.rs/2.2.x/diesel_migrations/macro.embed_migrations.html https://docs.diesel.rs/2.2.x/diesel_migrations/macro.embed_m...) macro and running your applications on startup. Diesel decouples that as there are situations where you might want to work with a slightly different database as defined, for example if you don't control the database.
  - rendaw 2y ago
    I don't get the impression that there's any interaction or data sharing between the embedded migrations and the query generation part, that would validate the queries against the schema as defined by the migration -- is that wrong? The documentation doesn't seem to mention it at least.

mijoharas 2y ago

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

twiss 2y ago
I'm guessing they're talking about https://www.complexsql.com/rowid-rownum/ https://www.complexsql.com/rowid-rownum/, i.e. the row numbers defined by the database, rather than the value of the ID column you typically create in each table. I.e., it should still be possible to create a column with a public identifier by which you can refer to the row, I assume.
Hytak 2y ago
Sending row IDs to your frontend has two potential problems: - Row IDs can be reused when the row is deleted. So when the frontend sends a message to the backend to modify that data, it might accidentally modify a different row that was created after the original was deleted. - You may be leaking the total number of rows (this allows detecting when new rows are created etc. which can be problematic). If you have nothing else to uniquely identify a row, then you can always create a random unique identifier and put a unique constraint on it.

skeptrune 2y ago

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.

runeks 2y ago

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.

LudwigNagasena 2y ago
It can be type safe in relation to the established contract between services.

rendaw 2y ago

This is awesome, but also kind of hurts since I was working on something similar here: https://github.com/andrewbaxter/good-ormning 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.

dboreham 2y ago

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.

Namm22 2y ago

[flagged]