Yes, plain sql is indeed the bees knees but there are good ORMs like django/ecto etc. that let you consider N+1 query issues ahead of time. Most ORMs these days have escape hatches anyway. Patience might be needed to keep it all tidy but they don't necessarily have to be a mess.
I don't get why to use an ORM in the first place. Just define a bunch of structs, run a query, map results to structs. It's a few lines of simple code. You're in control of everything (the SQL, the running, the mapping). It's transparent. With any ORM, you give away control and make everything more complex, only to make it slightly easier to run a query and map some results.
Op is never implying they intend to maintain one to one correspondence between the DB and objects and do that through manipulating objects only. Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM.
Not in most modern web application servers. ORMs seem to solve the problem of synchronizing some persistent application state (like a desktop GUI app) to your database state, but web application servers are usually relatively stateless. It's better to think of the application's job as taking a request, parsing it, compiling it to SQL, handing that to a database, and serializing the results.
Through that lens, the parts where you load and save object state are redundant. You're going to throw those objects away after the request anyway. Just take your request and build an UPDATE, etc. Use record types merely as a way to define your schema.
No type safety & writing manual SQL is slower. I get your point but the bottleneck is often developement speed, not query efficiency. I know and hate how stupid the ORM is underneath but I have to admit it's a blessing that I dont have to think about SQL at all (until I do).
As said, they have cost me too much time and money already, moreso as other devs on the team(s) lent heavily into certain features and I had to rewrite a lot of code.
Why are you rewritting? 80%[1] of queries most users do can be efficiently handled by ORM. I might need to use hand-written query a few times either because this particular query is faster to write by hand or because ORM builds a a bad query. That is it, no need to throw away entire ORM because of that.
When I was in RoR world, pretty much every N+1 query I saw was due to lack of RTFM.
I wish the following three paragraphs were widely read and understood by all software developers, especially web developers:
> The common wisdom is to maximize productivity when performance is less critical. I agree with this position. When building a web application, performance is a secondary concern to productivity. So why are teams adopting Rust more often where performance is less critical? It is because once you learn Rust, you can be very productive.
> Productivity is complex and multifaceted. We can all agree that Rust's edit-compile-test cycle could be quicker. This friction is countered by fewer bugs, production issues, and a robust long-term maintenance story (Rust's borrow checker tends to incentivize more maintainable code). Additionally, because Rust can work well for many use cases, whether infrastructure-level server cases, higher-level web applications, or even in the client (browser via WASM and iOS, MacOS, Windows, etc. natively), Rust has an excellent code-reuse story. Internal libraries can be written once and reused in all of these contexts.
> So, while Rust might not be the most productive programming language for prototyping, it is very competitive for projects that will be around for years.
I'd add that a lot of the described advantages come from culture. For web applications manual memory management is 100% a friction instead of a relief. But the culture in Rust community in general, at least for the past ten years or so, is to encourage a coding style with inherently fewer bugs and more reusable, maintainable code, to the point of consistently want something to not happen if they weren't sure they got it right (one may argue that this is counter-production short-term).
It is this culture thing makes adopting Rust for web apps worthwhile - it counters the drawback of manual memory management.
If you hire an engineer already familiar with Rust you are sure you get someone who is sane. If you onboard someone with no Rust background you can be pretty sure that they are going to learn the right way (tm) to do everything, or fail to make any meaningful contribution, instead of becoming a -10x engineer.
If you work in a place with a healthy engineering culture, trains people well, with good infra, it doesn't really matter, you may as well use C++. But for us not so lucky, Rust helps a lot, and it is not about memory safety, at all.
Very interested in exploring how this will compare to Diesel [1] and SeaORM [2], the other two options in this space today. Joshua Mo at Shuttle did a comparison between Diesel and SeaORM in January of this year that was really interesting [3].
My first reaction is this feels like a nice middleground between Diesel and SeaORM.
The codegen part makes all columns and tables and stuff checked at compile-time (name and type) like Diesel, with a query builder that's more natural like SeaORM. I hope the query builder does not end up too magical like SQLAlchemy with its load of footguns, and stay close in spirit to Diesel that's "write sql in rust syntax".
I think time will tell, and for now I'm keeping my Diesel in production :D
Sea ORM is too opinionated in my experience. Even making migration is not trivial with their own DSL.
Diesel was ok, but I never use it anymore since rocket moved to async.
I'm mainly use sqlx, it's simple to use, there's query! and query_as! macro which is good enough for most of the case.
I use SQLx, but I'm not totally convinced it's better than writing raw SQL with the underlying postgres/sqlite3/mysql driver. The macros and typing fall apart as soon as you need anything more complicated than basic a SELECT with one to one relationships, much less one/many to many.
I remember fighting with handling enums in relations for a while, and now just default to manually mapping everything.
SQLx can handle complicated queries as long as they're completely static strings. We've got SELECT FOR UPDATE, upserts, and some crazy hundred-line queries that are fine with their macros.
SQLx sucks at dynamic queries. Dynamic predicates, WHERE IN clauses, etc.
For SQLx to be much more useful, their static type checker needs to figure out how to work against these. And it needs a better query builder DSL.
Right, it's not bad if you stick with what the type checker can handle, but I usually end up falling back on manual building with the majority of queries in any semi-complex app.
It doesn't end up being too bad though, except for the loss of compile time syntax checking. Manually handling joins can be kind of nice, it's easier to see optimizations when everything is explicit.
It's nice seeing more Django/Prisma style ORMs where the non-SQL source code is the source of truth for the schema and migrations are automatically generated.
As time passes, the more I feel a minority in adoring rust, while detesting Async. I have attempted it a number of times, but it seems incompatible with my brain's idea of structure. Not asynchronous or concurrent programming, but Async/Await in rust. It appears that most of the networking libraries have committed to this path, and embedded it moving in its direction.
I bring this up because a main reason for my distaste is Async's incompatibility with non-Async. I also bring this up because lack of a Django or SQLAlchemy-style ORM is one reason I continue to write web applications in Python.
> I bring this up because a main reason for my distaste is Async's incompatibility with non-Async. I also bring this up because lack of a Django or SQLAlchemy-style ORM is one reason I continue to write web applications in Python.
Async code is not incompatible with blocking one, in Rust it's quite straightforward to make the two interoperate: calling a blocking code from async is donne with spawn_blocking and the reverse (async from blocking code) is done with block_on.
Non-async functions are absolutely blocking. The question is if they’re expected to block for a meaningful amount of time, which is generally suggested by your async runtime.
It’s really not that bad, you might just need a better mental model of what’s actually happening.
If it's neither blocking nor async then it's a completely regular function and you don't even have to call it with spawn blocking, there's nothing that prevent calling a normal function from an async one.
And in the opposite situation, if you call an async function then you are doing IO so your function must be either async or blocking, there's no third way in this direction, so when you're doing IO you have to make a choice: you either make it explicit (and thus declare the function async) or you hide it (by making a blocking call).
A blocking function is just a function doing IO that hides it from the type system and pretend to be a regular function.
A blocking function is one that blocks the event loop from switching to another task. It doesnt matter what it is doing only that it is doing something and not hitting another await to release the loop to work on another task. A simple function with while loop can block the event loop if it doesnt contain any awaits in it.
This is an implementation detail that can leak from single-threaded event loops (JavaScript typically) but this isn't true of multithreaded event loops, which can even have a preemption mechanism for long running tasks (for instance in Rust async-std has one IIRC).
There's a fundamental difference between CPU heavy workload that keep a thread busy and a blocking syscall: if you have as many CPU heavy tasks as CPU cores then there's fundamentally not much to do about it and it means your server is under-dimensioned for your workload, whereas a blocking syscall is purely virtual blocking that can be side-stepped.
Rust executors don't have real preemption, sadly. I'd love to have in Rust what the BEAM has for Erlang, block all you want, the rest of the processes (tasks) still run in time.
Also, the IO and the execution being completely tied (the executor provides the IO) is a wrong choice in my opinion. Hopefully in the future there is a way to implement async IO via Futures without relying on the executor, maybe by std providing more than just a waker in the passed-in context.
> Also, the IO and the execution being completely tied (the executor provides the IO) is a wrong choice in my opinion.
It's more a consequence of having let tokio becoming the default runtime instead of having the foundational building blocks in the standard library than a language issue. But yes, the end result is unfortunate.
Looks similar to Prisma Client Rust but because Prisma and its file format are already established unlike toasty files, might be easier to use that. However, this is by Tokio and PCR is relatively unknown with development being not too fast, so your mileage may vary. I've been using diesel (with diesel_async) so far.
It is nice to see more ORMs, but inventing a new file format and language `toasty` isn't my cup of tea. I'd rather define the models in Rust and let the generator emit more Rust files.
Creating your own file format is always difficult. Now, you have to come up with syntax highlighting, refactoring support, go to definition, etc. When I prototype, I tend to rename a lot of my columns and move them around. That is when robust refactoring support, which the language's own LSP already provides, is beneficial, and this approach throws them all away.
My experience with Prisma, which has a very similar DSL for defining schemas, has changed my mind on this. Makes me much more productive when maintaining large schemas. I can make a one line change in the schema file and instantly have types, models, and up/down migrations generated and applied, and can be guaranteed correct. No issues with schema drift between different environments or type differences in my code vs db.
Prisma is popular enough it also has LSP and syntax highlighting widely available. For simple DSL this is actually very easy build. Excited to have something similar in Rust ecosystem.
I mostly agree with this, but the trouble is (probably) that proc-macros are heavy-handed, inflexible, and not great for compile times.
In this case, for example, it looks like the generated code needs global knowledge of related ORM types in the data model, and that just isn't supported by proc-macros. You could push some of that into the trait system, but it would be complex to the point where a custom DSL starts to look appealing.
Proc-macros also cannot be run "offline", i.e. you can't commit their output to version control. They run every time the compiler runs, slowing down `cargo check` and rust-analyzer.
You can absolutely do global knowledge in proc macros via the filesystem and commit their output to version control: https://github.com/trevyn/turbosql
I find the syntax confusing. Setting properties and even creating associated model instances is done with opaque method names like `.name()` and `.todo()`. I'm not always a fan of using set/get prefixes, but I think there should be some differentiation for an ORM which is inherently involved in property access. I'm particular it is strange and surprising to me that `.todo()` would associate another model. Why not "add_todo" or "create_todo"? What if the association is not one to many but one to one? The method `.todos()` retrieves a list of Todos, but what if we're talking about a 1:1 Profile model? How would a user differentiate between setting and getting a `.profile()`?
I'm not a rust person so I might just be exposing my ignorance here, just wanted to provide feedback since it's on early development.
In my experience, Dynamo and other NoSQL systems are really expressive and powerful when you take the plunge and make your own ORM. That’s because the model of nosql can often play much nicer with somewhat unique structures like
- single table patterns
- fully denormalized or graph style structures
- compound sort keys (e.g. category prefixed)
Because of that, I would personally recommend developing your own ORM layer, despite the initial cost
For me diesel hits right balance since it is more a query builder and it is close to the SQL syntax. But sometimes it doesn't work because it is very strongly typed, right now I use sea-query for those scenarios and I built the bridge between the two.
The second that you would benefit from using a DBMS specific feature, the ORM begins getting in the way. It is highly unlikely that an ORM provides support, much less a good abstraction, over features that only 1/N supported DBMS have.
Your code ends up using the driver raw in these cases, so why not just use the driver for everything? Your codebase would be consistent at that point
>The second that you would benefit from using a DBMS specific feature, the ORM begins getting in the way.
You can extend diesel (and probably many other orms, Diesel is just particularly easy here) to support any db feature you want.
> It is highly unlikely that an ORM provides support, much less a good abstraction, over features that only 1/N supported DBMS have.
That depends on orm flexibility and popularity. It may not provide support OOTB, but can make it easy to add it.
> Your code ends up using the driver raw in these cases, so why not just use the driver for everything? Your codebase would be consistent at that point
Main point of using orm for me is that I have type verification, raw (as in text) breaks too easily.
You can extend diesel in theory, but can you really in practice? In my experience, it's very hard to work with once you get into the weeds. It's a big mess of very complicated generic signatures.
Might have improved since last I checked, but I was pretty confused.
I have found that ORM arguments in context don’t stick very well to Django’s ORM, but see the argument applying well to most all the others.
Case in point Django is really good about DB-specific functionality and letting you easily add in extension-specific stuff. They treat “you can only do this with raw” more or less as an ORM design API issue.
My biggest critique of Django’s ORM is its grouping and select clause behavior can be pretty magical, but I’ve never been able to find a good API improvement to tackle that.
Django's ORM is the worst for object-relational impedance mismatch, though. Django is great if you're happy with thinly-veiled database tables. But it absolutely sucks if what you want is real objects representing business entities.
The simplest example is you can't build a Django object with a collection on it. Take the simplest toy example: a todo list. The natural model is simple: a todo list has a name and a list of items. You can't do that in Django. Instead you have to do exactly what you would do in SQL: two tables with item having a foreign key. There's no way to just construct a list with items in it. You can't test any business rules on the list without creating persistent objects in a db. It's crazy.
So yeah, Django lets you do loads with the relational side, but that's because it's doing a half-arsed job of mapping these to objects.
I mean first of all you could "just" use an array field for your list of items. Single model.
But then you have actual properties on your todo list. So even in your object model you already have two classes, and your todo list has a name and a list of items.
So there's not one class, there's two classes already.
As to "having a list", Django gives you reverse relations so you can do `my_list.items.all()`. Beyond the fact that your persistence layer being a database meaning that you need to do _something_, you're really not far off.
One could complain that `my_list.save()` doesn't magically know to save all of your items in your one-to-many. But I think your complaint is less about the relational model and much more about the "data persistence" question. And Django gives you plenty of tools to choose how to resolve the data persistence question very easily (including overriding `save` to save some list of objects you have on your main object! It's just a for loop!)
Using an array is just giving up on a relational database. In fact what you'd do is use a JSON field, but at that point you don't need an ORM, just use an object database.
You can only do `my_list.items.all()` if you've already saved the related records in the db. And if you do something like `my_list.items.filter(...)` well that's another db query. A proper ORM should be able to map relationships to objects, not these thinly veiled db records. See how SQLAlchemy does it to see what I mean. In SQLAlchemy you can fully construct objects with multiple layers of composition and it will only map this to the db when you need it to. That means you can test your models without any kind of db interaction. It's the whole point of using an ORM really.
I mean if you think SQLAlchemy does the job for you that's great! My general contention is more "there are good ORMs". I believe Django is the good one, but if you think SQLAlchemy works well for you, go for it!
Because you only need the specific features in a tiny amount of cases, while 99% is some flavour of SELECT * ... LEFT JOIN ... (If it's not, then sure, ORM would be annoying)
Making that 99% smaller, simpler and automatically mapping to common types makes development a lot easier/faster. This applies to pretty much any higher level language. It's why you can write in C, but embed an ASM fragment for that one very specific thing instead of going 100% with either one.
You’re probably making so much money that don’t care about your Database bill or query performance.
ORM is basically a no-code tool for databases, if that solves your problem great, but that’s not something that would scale beyond basic use.
Has it benefited you? Have you moved to a different underlying SQL software without having to make any changes to your codebase? Or some other benefit?
For me it’s speed of development. I’m frankly not very good at SQL, but an ORM in a familiar syntax to the language I use most (Typescript) increases my dev speed tremendously.
I also have a relatively successful saas that uses Prisma and it’s been phenomenal. Queries are more than fast enough for my use case and it allows me to just focus on writing more difficult business logic than dealing with complex joins
Looks well thought out i like that for the most part this seems faster/easier than rolling your own sql query mapping etc compared to the other solutions I've come across in rust
Because ORM attribute access usually trigger requests, and you must design the API so that those requests, which trigger called to the network, don't block.
I see, the ORM backed objects are lazy and trigger I/O when the data is accessed. On first blush I'm surprised that Rust culture would go for this as it spread network i/o (incl network errors), async waiting and memory allocation widely in your code. This would seemto hamper eg the common "functional core, imperative shell" architecture which gets you many of the classic FP virtues. I wonder if I'm missing something that makes these less of a problem?
I think you are talking past each others. Preventing N+1 by doing lazy fetching and having synchronous / asynchronous API are orthogonal issues. Async API must not block the thread/event loop when the data loading is being done.
Is it just me or why does Rust have all these confusing names that seemingly have nothing to do with the functionality of the module/crate? Or maybe I’m just used to the names in Python and C++ packages for performing common tasks. It just seems to make it harder for a newcomer to locate what packages they should be using when they want to perform some common function.
I expect the parent meant to write R instead of O. It is misleading as nobody ever maps relational databases. As the parent points out, usually they reach for tablational databases. This project also supports a key/value database. But it does not even support relational databases.
Further, the project is focused on implementing the active record pattern, so it would be more appropriately called an async active record than an "async ORM".
You seem to be thinking of query builders. I would posit that they are still useful in many real-world scenarios because SQL still stupidly does not support composition. Maybe some day it will catch up with the 1950s, but until that day...
ORM has nothing to do with queries. It is about the data. You will still want to map your database structures to your application structures (and vice-versa), else you'll end up with a weird and ugly chain of dependencies that will forever ruin your codebase.
What do you mean here? I'm my experience, SQL is one of the most compositional languages there is. You've got relations and a bunch of operations to combine and transform relations to form new relations.
2. I may be misinterpreting you, but you seem to be talking about composition of data, while the rest of us are talking about the composition of the language.
But, hopefully I've misinterpreted you. Perhaps you could demonstrate how query builder composition is best replaced in pure SQL?
I'm not sure what distinction you're making besides allowing duplicate rows (which don't affect composition and you can remove with `distinct `).
I'm also not sure how to answer your question. Obviously a query builder is just spitting out queries that you can just write yourself. In the best case, they're a thin wrapper to give different names to SQL (e.g. where vs filter, select vs map, join vs flatMap). Perhaps an example would be how frequently, ORMs encourage you to do a load-modify-save pattern that turns into multiple statements to execute. This is usually more code, more error-prone, and worse performing than just doing an UPDATE WHERE. If you need complex reporting with a dozen joins and several window functions, you'll also see how difficult it is to write similar code in an application.
I'm not sure what you mean with composition of the language. The language consists of operators which you can chain together, and you can substitute expressions into each other to form larger expressions. E.g. you can join to a (select from where group by) expression in place of a table, and you can factor such things out into CTEs. What's not composable?
> I'm not sure what distinction you're making besides allowing duplicate rows
Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education!
> I'm also not sure how to answer your question.
You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.
So to be clear, my choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables", but you can of course use these things in SQL expressions. So tables are relations (not in the mathematical sense, but in the sense that e.g. postgresql documentation uses), but not all relations are tables. In that sense, the things that compose are relations and their operations.
I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?
> choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables"
The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing?
> postgresql documentation uses
In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation.
> and have a method chaining syntax.
And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider:
And now imagine having tens, maybe even hundreds, of slight variations on the same query in the same vein. Each used in different parts of the application, which is quite common in any kind of line of business application. I'll save your bandwidth and not spell them all out as this is just a contrived example anyway, and I'm sure your imagination can fill in the blanks.
Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used.
But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.
If I'm understanding you correctly, then you can do something like
create view admin_users as select * from users where role='admin';
create view recent_admin_users as select * from admin_users where created > LAST_WEEK();
etc. You can also give different roles different permissions to access views without access to the underlying tables as a way to define a stable, high performance API, for example.
I wouldn't use views for something so small, but I probably wouldn't use a query builder either. If you want a stable API, make a view to indirect access to the table(s). Don't break your view API. If you change the underlying table, update the view to keep it as a stable interface.
Query builders can be nice for generic code. E.g. you have a variable length list of predicates (e.g. from http query strings) and want to do `predicates.fold(_ => true)(_ and _)`. In that case you're basically using it as a macro system, which works because sql fragments compose. In fact IMO the most pleasant way to use a query builder is usually exactly as string interpolation macros.
ORMs, the original topic at hand, are an entirely different beast. I think generally people who bash ORMs don't have much issue with query builders.
AI can give better responses to a lot of requests when it has a well designed high level API available. And many ORMs don’t produce well designed APIs but it seems this one will.
I think that for some things user intent would be better expressed as customized ORM than SQL queries. If the ORM isn’t customized and is just generated from the tables then, yeah, not much of a help.
I never understood why people are so stubborn about hating on orm.
For example I'm familiar with https://gorm.io and it does save me a lot of time and useless boilerplate.
And guess what, if I ever need to make a complex query, I also happen to know SQL, and I'm just going to make a "raw" query https://gorm.io/docs/sql_builder.html#Raw-SQL and be done with it.
It's not all that hard.
edit:
the other common complaint usually is: "but I don't know what query the orm is going to make..."
> I never understood why people are so stubborn about hating on orm.
Objects are just bad abstractions for representing database interactions. They never map cleanly and any attempt to cover up the incoherence introduces further problems.
Avoiding boilerplate is understandable, but typed schemas and queries exist without hauling in a full ORM.
Of course you can pump out a lot of SQL very quickly with ORMs. There's a lot positive to say about this approach! But you don't tend to end up with code where you can easily tell what's going on.
> Objects are just bad abstractions for representing database interactions.
But they're excellent abstractions for business entities.
> Of course you can pump out a lot of SQL very quickly with ORMs. There's a lot positive to say about this approach! But you don't tend to end up with code where you can easily tell what's going on.
15 years building up massive apps with Django and SQLAlchemy and this has never been a problem.
If your job literally entails using a database abstraction and you can't arse yourself into learning said abstraction then it's a skills issue, just as much as people who want to use ORMs without learning SQL.
I can't speak to SQLAlchemy itself, but ActiveRecord (which I'm sure has both shared and unshared issues) still imposes a high maintenance burden with a small team of highly-skilled developers, both on the performance front and the validating-changes front. I, personally, find it very annoying to read, having to hop around an object hierarchy just to figure out what kind of query is generated and what kind of logic is "silently" added to the query somewhere in said object hierarchy (or, god forbid, mixins/monkey-patching).
You definitely make good points, and all my issues are over-comable with time and effort and knowing which tricks to use, so this truly is a matter of opinion and taste. I'm just pointing out that the idea that this produces more readable code seems far from obvious.
I think it's worth giving SQLAlchemy a try if you're writing a Python app. I've worked with most of the popular ones in Python/JS/Rust, and it's the only one I haven't had to fight with.
I'm back to using bespoke query builders and raw SQL since I don't use Python much anymore, but sometimes miss the syntax and migration system.
IMHO your critiques have more to do with Ruby's promotion of class hierarchies and the fact that ActiveRecord requires inspecting a DB to infer what's going on and Rail's abuse of monkeypatching.
I concur. I’ve used ORMs in certain projects, but iterating and optimizing them later on became untenable. I’ve learned my lesson — all my new projects forgo ORMs by default.
How? ORM logic is as simple or as complicated as the underlying queries and results. In 15 years of building web apps with Python ORMs this has never been a problem across many, many different teams.
A lot of people think SQL is antiquated, and therefore not worth learning. However, SQL is based upon Relation Algebra, which is the foundation of database optimalisation. Any data querying language/library that is not based on relational algebra will lack the performance. In particular ORMs fall under this category. Having said that, there are other languages/libraries out there that do use the relational algebra constructs.
You still need to know SQL when you're using ORM. You also need to know ORM when you're using one. It's really not that hard to make ORM perform well if you spend some time RTFMing.
When was the last time your RDBMS was handling business logic and wasn't just a persistence layer for your application? SQL (the language) isn't composable so you often have a choice of building strings or having many slightly different queries.
Anyway, pros of using an ORM outweight it cons in my opinion.
I respect that some prefer just to use SQL, but that isn't where most stand.
Also, instead of a reactionary "all ORMs are trash," where ORM probably means different things to different people, maybe you could provide some value to the conversation by providing specific points and/or arguments supporting your feelings about ORMs. At the very least, you could provide some citation to an article that does the summarization.
Moving fast is often crucial, but you typically also get more readable code, which is also worth it.
A query/DML that is badly optimized is usually very complex, which means that the ORM's syntax isn't a good fit. No problem, since they typically support raw SQL calls, so nothing is really lost.
You just have to know when to use the right tool for the job, as always.
> Moving fast is often crucial, but you typically also get more readable code, which is also worth it.
I think the exact opposite is true, actually—because of the introduction of stuff like lifecycle hooks it becomes very difficult to figure out how the domain semantics translate to query/execution semantics. Of course some are lighter than that and just e.g. map a table to a domain type (which is much more readable), but that's not typically what catches criticism.
Nonsense. People use ORMs because the vast majority of queries are trivial. If you need something the ORM doesn't provide sufficient control over, only then you move to raw queries for these cases.
It’s has nothing to do with being simple and everything to do with wha the database looks like at the end of the day.
Some ORMs are better than others but if if you have ever looked at a database created by a ORM it always has weird lookup tables, funny names and even with simple objects completely unusable without the ORM.
We live in a multi language world. There is a high chance you are going to want to access this data with a different language. This langue is not going to have the same ORM as such you will have a horrid time access the data.
ORMs often lay their data out in a way that is highly language dependent.
The fact of the matter is SQL is not hard and bypassing the main interface to the database and hand wave it away will always bring you regret when writing any sooty of software other than a toy project.
Your best case is you become an expert in the ORM. Resulting in a skill set that does not transfer easy, language locked and the worst of all bottle necks any changes to the data layer to your ORM expert who at first will be proud and happy and end smug and bitchy as all the data layers request changes will simply be redirected to them.
When people like me say ORMs are trash it’s much more than any of the surface level rebuttals listed here. It’s about the whole life cycle is your project. Adding up all the places ORMs can fuck you just makes it a bad proposition.
God forbid you need to upgrade your database or the ORM version.
> ORMs often lay their data out in a way that is highly language dependent.
Which ORMs did you use? This doesn't sound normal at all. Never saw this with Rails, Ecto, EF, Sybase, or even the legacy project I once worked on that had 4 different ORMs for different parts of the same backend process, using the same database (some were very old and behind slowly phased out over time). Maybe you have ORM confused with CMS (content management system). A CMS can do those things, but that is not an ORM.
> There is a high chance you are going to want to access this data with a different language.
There are tools for that, such as ETL, read replicas, data warehouses, code generators, raw sql, stored procedures, views, just off the top of my head.
Sorry but anyone who needs to build queries dynamically e.g. for extended/advanced search or ACL is going to significantly benefit from being able to seamlessly mix regular code and query logic. Compare that to the old way of concatenating SQL fragments. Shudder.
Its better than sitting silent and doing nothing. What it does is validate other peoples feelings who feel the same way. And just maybe, that validation will lead to them saying "fuck ORMs" when some junior dev comes in and tries to use one.
Fair, but it also reinforces the stereotype that the pro-SQL types are obstinate relics yelling at the youngsters to "Get off my lawn!"
There's a pattern of "We can't change X, so we'll write Y that transpiles down to X". It happens often with closed source tools and others that can't or won't implement new languages. Verilog, SQL, Javascript, all fit that bill.
E.g. Why is Javascript the only first class language for the browser? For the longest time JS was the only game in town.
Yeah, let's trash the 50 year old industry standard and let's obfuscate the interface to one of the most performance sensitive part of an application. Hell, lets build multiple obfuscators for each language, each with it's own astonishing behavior that turns pathological once you actually get usage on the app.
ORM has never worked for me in any language.
Sooner or later we always hit the n+1 query problem which could only be resolved by a query builder or just plain old sql.
It always was a mess and these days I can't be bothered to try it even anymore because it has cost me a lot of hours and money.
Yes, plain sql is indeed the bees knees but there are good ORMs like django/ecto etc. that let you consider N+1 query issues ahead of time. Most ORMs these days have escape hatches anyway. Patience might be needed to keep it all tidy but they don't necessarily have to be a mess.
I don't get why to use an ORM in the first place. Just define a bunch of structs, run a query, map results to structs. It's a few lines of simple code. You're in control of everything (the SQL, the running, the mapping). It's transparent. With any ORM, you give away control and make everything more complex, only to make it slightly easier to run a query and map some results.
> Just define a bunch of structs, run a query, map results to structs
Congrats, you now have your own little ORM.
No, absolutely not.
Op is never implying they intend to maintain one to one correspondence between the DB and objects and do that through manipulating objects only. Mapping hand written queries results to structs and updating the DB yourself on the basis of what is in structs is not at all an ORM.
Not in most modern web application servers. ORMs seem to solve the problem of synchronizing some persistent application state (like a desktop GUI app) to your database state, but web application servers are usually relatively stateless. It's better to think of the application's job as taking a request, parsing it, compiling it to SQL, handing that to a database, and serializing the results.
Through that lens, the parts where you load and save object state are redundant. You're going to throw those objects away after the request anyway. Just take your request and build an UPDATE, etc. Use record types merely as a way to define your schema.
No type safety & writing manual SQL is slower. I get your point but the bottleneck is often developement speed, not query efficiency. I know and hate how stupid the ORM is underneath but I have to admit it's a blessing that I dont have to think about SQL at all (until I do).
This is pretty much where I landed as well, I also love being able to quickly copy and run SQL queries to test and modify them somewhere else.
It's not a black or white thing. Good ORMs let you use plain old SQL when needed.
As said, they have cost me too much time and money already, moreso as other devs on the team(s) lent heavily into certain features and I had to rewrite a lot of code.
Why are you rewritting? 80%[1] of queries most users do can be efficiently handled by ORM. I might need to use hand-written query a few times either because this particular query is faster to write by hand or because ORM builds a a bad query. That is it, no need to throw away entire ORM because of that.
When I was in RoR world, pretty much every N+1 query I saw was due to lack of RTFM.
[1]: I made this up
What do you recommend? Do the mapping manually? Tbh I tried that while learning rust and it was awful.
On the other hand an async orm sounds like (n+1)(n+2)+...+(n+m) Problem
I wish the following three paragraphs were widely read and understood by all software developers, especially web developers:
> The common wisdom is to maximize productivity when performance is less critical. I agree with this position. When building a web application, performance is a secondary concern to productivity. So why are teams adopting Rust more often where performance is less critical? It is because once you learn Rust, you can be very productive.
> Productivity is complex and multifaceted. We can all agree that Rust's edit-compile-test cycle could be quicker. This friction is countered by fewer bugs, production issues, and a robust long-term maintenance story (Rust's borrow checker tends to incentivize more maintainable code). Additionally, because Rust can work well for many use cases, whether infrastructure-level server cases, higher-level web applications, or even in the client (browser via WASM and iOS, MacOS, Windows, etc. natively), Rust has an excellent code-reuse story. Internal libraries can be written once and reused in all of these contexts.
> So, while Rust might not be the most productive programming language for prototyping, it is very competitive for projects that will be around for years.
I'd add that a lot of the described advantages come from culture. For web applications manual memory management is 100% a friction instead of a relief. But the culture in Rust community in general, at least for the past ten years or so, is to encourage a coding style with inherently fewer bugs and more reusable, maintainable code, to the point of consistently want something to not happen if they weren't sure they got it right (one may argue that this is counter-production short-term).
It is this culture thing makes adopting Rust for web apps worthwhile - it counters the drawback of manual memory management.
If you hire an engineer already familiar with Rust you are sure you get someone who is sane. If you onboard someone with no Rust background you can be pretty sure that they are going to learn the right way (tm) to do everything, or fail to make any meaningful contribution, instead of becoming a -10x engineer.
If you work in a place with a healthy engineering culture, trains people well, with good infra, it doesn't really matter, you may as well use C++. But for us not so lucky, Rust helps a lot, and it is not about memory safety, at all.
Very interested in exploring how this will compare to Diesel [1] and SeaORM [2], the other two options in this space today. Joshua Mo at Shuttle did a comparison between Diesel and SeaORM in January of this year that was really interesting [3].
[1]: https://diesel.rs/
[2]: https://www.sea-ql.org/SeaORM/
[3]: https://www.shuttle.dev/blog/2024/01/16/best-orm-rust
My first reaction is this feels like a nice middleground between Diesel and SeaORM.
The codegen part makes all columns and tables and stuff checked at compile-time (name and type) like Diesel, with a query builder that's more natural like SeaORM. I hope the query builder does not end up too magical like SQLAlchemy with its load of footguns, and stay close in spirit to Diesel that's "write sql in rust syntax".
I think time will tell, and for now I'm keeping my Diesel in production :D
Sea ORM is too opinionated in my experience. Even making migration is not trivial with their own DSL. Diesel was ok, but I never use it anymore since rocket moved to async.
I'm mainly use sqlx, it's simple to use, there's query! and query_as! macro which is good enough for most of the case.
I use SQLx, but I'm not totally convinced it's better than writing raw SQL with the underlying postgres/sqlite3/mysql driver. The macros and typing fall apart as soon as you need anything more complicated than basic a SELECT with one to one relationships, much less one/many to many.
I remember fighting with handling enums in relations for a while, and now just default to manually mapping everything.
SQLx can handle complicated queries as long as they're completely static strings. We've got SELECT FOR UPDATE, upserts, and some crazy hundred-line queries that are fine with their macros.
SQLx sucks at dynamic queries. Dynamic predicates, WHERE IN clauses, etc.
For SQLx to be much more useful, their static type checker needs to figure out how to work against these. And it needs a better query builder DSL.
Right, it's not bad if you stick with what the type checker can handle, but I usually end up falling back on manual building with the majority of queries in any semi-complex app.
It doesn't end up being too bad though, except for the loss of compile time syntax checking. Manually handling joins can be kind of nice, it's easier to see optimizations when everything is explicit.
I like sqlx, but have been eyeing diesel for some time. Any reasons you don't use diesel_async?
With Diesel async integrating everything with the pooling is a bit hairy. With sqlx everything just works.
It's nice seeing more Django/Prisma style ORMs where the non-SQL source code is the source of truth for the schema and migrations are automatically generated.
Et tu, toasty?
As time passes, the more I feel a minority in adoring rust, while detesting Async. I have attempted it a number of times, but it seems incompatible with my brain's idea of structure. Not asynchronous or concurrent programming, but Async/Await in rust. It appears that most of the networking libraries have committed to this path, and embedded it moving in its direction.
I bring this up because a main reason for my distaste is Async's incompatibility with non-Async. I also bring this up because lack of a Django or SQLAlchemy-style ORM is one reason I continue to write web applications in Python.
> I bring this up because a main reason for my distaste is Async's incompatibility with non-Async. I also bring this up because lack of a Django or SQLAlchemy-style ORM is one reason I continue to write web applications in Python.
So you use gevent/greenlet?
Async code is not incompatible with blocking one, in Rust it's quite straightforward to make the two interoperate: calling a blocking code from async is donne with spawn_blocking and the reverse (async from blocking code) is done with block_on.
I think this is core to the disconnect: Non Async/await does not imply blocking.
Non-async functions are absolutely blocking. The question is if they’re expected to block for a meaningful amount of time, which is generally suggested by your async runtime.
It’s really not that bad, you might just need a better mental model of what’s actually happening.
Depends, on Linux you can call set_nonblocking on a TcpListener and get a WouldBlock error whenever a read would block. That's called non-blocking.
Doesn't this miss the forest for the trees? The entire point is to drive with epoll.
Well, yes. But it means you can do sync non-blocking IO by hand.
If it's neither blocking nor async then it's a completely regular function and you don't even have to call it with spawn blocking, there's nothing that prevent calling a normal function from an async one.
And in the opposite situation, if you call an async function then you are doing IO so your function must be either async or blocking, there's no third way in this direction, so when you're doing IO you have to make a choice: you either make it explicit (and thus declare the function async) or you hide it (by making a blocking call).
A blocking function is just a function doing IO that hides it from the type system and pretend to be a regular function.
A blocking function is one that blocks the event loop from switching to another task. It doesnt matter what it is doing only that it is doing something and not hitting another await to release the loop to work on another task. A simple function with while loop can block the event loop if it doesnt contain any awaits in it.
This is an implementation detail that can leak from single-threaded event loops (JavaScript typically) but this isn't true of multithreaded event loops, which can even have a preemption mechanism for long running tasks (for instance in Rust async-std has one IIRC).
There's a fundamental difference between CPU heavy workload that keep a thread busy and a blocking syscall: if you have as many CPU heavy tasks as CPU cores then there's fundamentally not much to do about it and it means your server is under-dimensioned for your workload, whereas a blocking syscall is purely virtual blocking that can be side-stepped.
Rust executors don't have real preemption, sadly. I'd love to have in Rust what the BEAM has for Erlang, block all you want, the rest of the processes (tasks) still run in time.
Also, the IO and the execution being completely tied (the executor provides the IO) is a wrong choice in my opinion. Hopefully in the future there is a way to implement async IO via Futures without relying on the executor, maybe by std providing more than just a waker in the passed-in context.
> Also, the IO and the execution being completely tied (the executor provides the IO) is a wrong choice in my opinion.
It's more a consequence of having let tokio becoming the default runtime instead of having the foundational building blocks in the standard library than a language issue. But yes, the end result is unfortunate.
I think the custom schema definition file is not needed. Just define it in plain Rust. Not sure what the win is for this tool.
Looks similar to Prisma Client Rust but because Prisma and its file format are already established unlike toasty files, might be easier to use that. However, this is by Tokio and PCR is relatively unknown with development being not too fast, so your mileage may vary. I've been using diesel (with diesel_async) so far.
It is nice to see more ORMs, but inventing a new file format and language `toasty` isn't my cup of tea. I'd rather define the models in Rust and let the generator emit more Rust files.
Creating your own file format is always difficult. Now, you have to come up with syntax highlighting, refactoring support, go to definition, etc. When I prototype, I tend to rename a lot of my columns and move them around. That is when robust refactoring support, which the language's own LSP already provides, is beneficial, and this approach throws them all away.
My experience with Prisma, which has a very similar DSL for defining schemas, has changed my mind on this. Makes me much more productive when maintaining large schemas. I can make a one line change in the schema file and instantly have types, models, and up/down migrations generated and applied, and can be guaranteed correct. No issues with schema drift between different environments or type differences in my code vs db.
Prisma is popular enough it also has LSP and syntax highlighting widely available. For simple DSL this is actually very easy build. Excited to have something similar in Rust ecosystem.
I mostly agree with this, but the trouble is (probably) that proc-macros are heavy-handed, inflexible, and not great for compile times.
In this case, for example, it looks like the generated code needs global knowledge of related ORM types in the data model, and that just isn't supported by proc-macros. You could push some of that into the trait system, but it would be complex to the point where a custom DSL starts to look appealing.
Proc-macros also cannot be run "offline", i.e. you can't commit their output to version control. They run every time the compiler runs, slowing down `cargo check` and rust-analyzer.
You can absolutely do global knowledge in proc macros via the filesystem and commit their output to version control: https://github.com/trevyn/turbosql
I find the syntax confusing. Setting properties and even creating associated model instances is done with opaque method names like `.name()` and `.todo()`. I'm not always a fan of using set/get prefixes, but I think there should be some differentiation for an ORM which is inherently involved in property access. I'm particular it is strange and surprising to me that `.todo()` would associate another model. Why not "add_todo" or "create_todo"? What if the association is not one to many but one to one? The method `.todos()` retrieves a list of Todos, but what if we're talking about a 1:1 Profile model? How would a user differentiate between setting and getting a `.profile()`?
I'm not a rust person so I might just be exposing my ignorance here, just wanted to provide feedback since it's on early development.
Interesting take!
In my experience, Dynamo and other NoSQL systems are really expressive and powerful when you take the plunge and make your own ORM. That’s because the model of nosql can often play much nicer with somewhat unique structures like
- single table patterns - fully denormalized or graph style structures - compound sort keys (e.g. category prefixed)
Because of that, I would personally recommend developing your own ORM layer, despite the initial cost
Do you find that you value the relational model that a ORM constructs on top a non-relational DB? Or do you use it more like a "OM" without the R?
Why does a NoSQL or denormalized database need an ORM?
Developing your own ORM is almost always a waste of time and a bad idea.
For me diesel hits right balance since it is more a query builder and it is close to the SQL syntax. But sometimes it doesn't work because it is very strongly typed, right now I use sea-query for those scenarios and I built the bridge between the two.
Ideally I would use something akin to Go Jet.
I don't get the pent up anger with ORMs, I used it for my SaaS on Flask that I run and own for 4 years bringing in over $2M+ ARR with no issues.
Great to see some development in this for Rust, perhaps after it becomes stable I may even switch my SaaS to it.
The second that you would benefit from using a DBMS specific feature, the ORM begins getting in the way. It is highly unlikely that an ORM provides support, much less a good abstraction, over features that only 1/N supported DBMS have.
Your code ends up using the driver raw in these cases, so why not just use the driver for everything? Your codebase would be consistent at that point
>The second that you would benefit from using a DBMS specific feature, the ORM begins getting in the way.
You can extend diesel (and probably many other orms, Diesel is just particularly easy here) to support any db feature you want.
> It is highly unlikely that an ORM provides support, much less a good abstraction, over features that only 1/N supported DBMS have.
That depends on orm flexibility and popularity. It may not provide support OOTB, but can make it easy to add it.
> Your code ends up using the driver raw in these cases, so why not just use the driver for everything? Your codebase would be consistent at that point
Main point of using orm for me is that I have type verification, raw (as in text) breaks too easily.
You can extend diesel in theory, but can you really in practice? In my experience, it's very hard to work with once you get into the weeds. It's a big mess of very complicated generic signatures.
Might have improved since last I checked, but I was pretty confused.
I've added some sql functions, and support for decimal type for mysql (It didn't have it at some point). Wasn't complicated.
I have found that ORM arguments in context don’t stick very well to Django’s ORM, but see the argument applying well to most all the others.
Case in point Django is really good about DB-specific functionality and letting you easily add in extension-specific stuff. They treat “you can only do this with raw” more or less as an ORM design API issue.
My biggest critique of Django’s ORM is its grouping and select clause behavior can be pretty magical, but I’ve never been able to find a good API improvement to tackle that.
Here's one: https://stackoverflow.com/questions/65596920/use-django-subq...
Django's ORM is the worst for object-relational impedance mismatch, though. Django is great if you're happy with thinly-veiled database tables. But it absolutely sucks if what you want is real objects representing business entities.
The simplest example is you can't build a Django object with a collection on it. Take the simplest toy example: a todo list. The natural model is simple: a todo list has a name and a list of items. You can't do that in Django. Instead you have to do exactly what you would do in SQL: two tables with item having a foreign key. There's no way to just construct a list with items in it. You can't test any business rules on the list without creating persistent objects in a db. It's crazy.
So yeah, Django lets you do loads with the relational side, but that's because it's doing a half-arsed job of mapping these to objects.
I mean first of all you could "just" use an array field for your list of items. Single model.
But then you have actual properties on your todo list. So even in your object model you already have two classes, and your todo list has a name and a list of items.
So there's not one class, there's two classes already.
As to "having a list", Django gives you reverse relations so you can do `my_list.items.all()`. Beyond the fact that your persistence layer being a database meaning that you need to do _something_, you're really not far off.
One could complain that `my_list.save()` doesn't magically know to save all of your items in your one-to-many. But I think your complaint is less about the relational model and much more about the "data persistence" question. And Django gives you plenty of tools to choose how to resolve the data persistence question very easily (including overriding `save` to save some list of objects you have on your main object! It's just a for loop!)
Using an array is just giving up on a relational database. In fact what you'd do is use a JSON field, but at that point you don't need an ORM, just use an object database.
You can only do `my_list.items.all()` if you've already saved the related records in the db. And if you do something like `my_list.items.filter(...)` well that's another db query. A proper ORM should be able to map relationships to objects, not these thinly veiled db records. See how SQLAlchemy does it to see what I mean. In SQLAlchemy you can fully construct objects with multiple layers of composition and it will only map this to the db when you need it to. That means you can test your models without any kind of db interaction. It's the whole point of using an ORM really.
I mean if you think SQLAlchemy does the job for you that's great! My general contention is more "there are good ORMs". I believe Django is the good one, but if you think SQLAlchemy works well for you, go for it!
Because you only need the specific features in a tiny amount of cases, while 99% is some flavour of SELECT * ... LEFT JOIN ... (If it's not, then sure, ORM would be annoying)
Making that 99% smaller, simpler and automatically mapping to common types makes development a lot easier/faster. This applies to pretty much any higher level language. It's why you can write in C, but embed an ASM fragment for that one very specific thing instead of going 100% with either one.
You’re probably making so much money that don’t care about your Database bill or query performance. ORM is basically a no-code tool for databases, if that solves your problem great, but that’s not something that would scale beyond basic use.
Has it benefited you? Have you moved to a different underlying SQL software without having to make any changes to your codebase? Or some other benefit?
For me it’s speed of development. I’m frankly not very good at SQL, but an ORM in a familiar syntax to the language I use most (Typescript) increases my dev speed tremendously.
I also have a relatively successful saas that uses Prisma and it’s been phenomenal. Queries are more than fast enough for my use case and it allows me to just focus on writing more difficult business logic than dealing with complex joins
Nice, I love it!
It reminds me of Prisma and yet, it's all Rust. Also good to see that async is the focus point of the API so the usage feels ergonomic.
Looks well thought out i like that for the most part this seems faster/easier than rolling your own sql query mapping etc compared to the other solutions I've come across in rust
Toasty was the focus of Carl Lerche's P99 CONF keynote on Wednesday. It provoked some interesting discussion in the chat.
Why is asynchronity (sp?) a concern of the ORM in this case?
Because ORM attribute access usually trigger requests, and you must design the API so that those requests, which trigger called to the network, don't block.
I see, the ORM backed objects are lazy and trigger I/O when the data is accessed. On first blush I'm surprised that Rust culture would go for this as it spread network i/o (incl network errors), async waiting and memory allocation widely in your code. This would seemto hamper eg the common "functional core, imperative shell" architecture which gets you many of the classic FP virtues. I wonder if I'm missing something that makes these less of a problem?
this has been a solved problem for... a long time, can't remember how long even.
https://docs.sqlalchemy.org/en/20/orm/queryguide/relationshi...
the older I get the more I'm convinced this should be the default behavior.
I think you are talking past each others. Preventing N+1 by doing lazy fetching and having synchronous / asynchronous API are orthogonal issues. Async API must not block the thread/event loop when the data loading is being done.
Diesel hasn't been providing an async API for reason told in this thread: https://github.com/diesel-rs/diesel/issues/399
The situation might change some day though, once async support in the core language and surrounding ecosystem gets stronger.
diesel_async exists and is also maintained by the same creator.
[dead]
Is it just me or why does Rust have all these confusing names that seemingly have nothing to do with the functionality of the module/crate? Or maybe I’m just used to the names in Python and C++ packages for performing common tasks. It just seems to make it harder for a newcomer to locate what packages they should be using when they want to perform some common function.
Yep, for example hearing Jinja tells you right away what the package does.
Looks awesome. Would love to see the table definitions that are generated from the schema as well.
O in orm is misleading term. To me orm is about table.
O refers to the OOP object, it's the R that's the (relational) database.
I expect the parent meant to write R instead of O. It is misleading as nobody ever maps relational databases. As the parent points out, usually they reach for tablational databases. This project also supports a key/value database. But it does not even support relational databases.
Further, the project is focused on implementing the active record pattern, so it would be more appropriately called an async active record than an "async ORM".
but active record is just one of patterns that can be used to implement an ORM?
Meh, the article makes it sound as if there were nothing and we have already solid options like SeaORM and Diesel.
Still to me they all suck and nothing beats SQLx
The days of the ORM have passed.
AI writes amazing SQL, modern SQL databases are incredible and the best way to get the most out of your DB is write SQL.
Invest your learning budget in SQL, not in some random developers abstraction.
A good ORM is just a pre-built and externally tested validator of common DB work.
There's plenty of value in knowing both.
"AI writes amazing SQL" and "AI writes amazing DB to Application Translation Layer Code" just means "AI can write your company's bespoke ORM".
You seem to be thinking of query builders. I would posit that they are still useful in many real-world scenarios because SQL still stupidly does not support composition. Maybe some day it will catch up with the 1950s, but until that day...
ORM has nothing to do with queries. It is about the data. You will still want to map your database structures to your application structures (and vice-versa), else you'll end up with a weird and ugly chain of dependencies that will forever ruin your codebase.
What do you mean here? I'm my experience, SQL is one of the most compositional languages there is. You've got relations and a bunch of operations to combine and transform relations to form new relations.
1. SQL has tables, not relations...
2. I may be misinterpreting you, but you seem to be talking about composition of data, while the rest of us are talking about the composition of the language.
But, hopefully I've misinterpreted you. Perhaps you could demonstrate how query builder composition is best replaced in pure SQL?
I'm not sure what distinction you're making besides allowing duplicate rows (which don't affect composition and you can remove with `distinct `).
I'm also not sure how to answer your question. Obviously a query builder is just spitting out queries that you can just write yourself. In the best case, they're a thin wrapper to give different names to SQL (e.g. where vs filter, select vs map, join vs flatMap). Perhaps an example would be how frequently, ORMs encourage you to do a load-modify-save pattern that turns into multiple statements to execute. This is usually more code, more error-prone, and worse performing than just doing an UPDATE WHERE. If you need complex reporting with a dozen joins and several window functions, you'll also see how difficult it is to write similar code in an application.
I'm not sure what you mean with composition of the language. The language consists of operators which you can chain together, and you can substitute expressions into each other to form larger expressions. E.g. you can join to a (select from where group by) expression in place of a table, and you can factor such things out into CTEs. What's not composable?
> I'm not sure what distinction you're making besides allowing duplicate rows
Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education!
> I'm also not sure how to answer your question.
You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.
So to be clear, my choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables", but you can of course use these things in SQL expressions. So tables are relations (not in the mathematical sense, but in the sense that e.g. postgresql documentation uses), but not all relations are tables. In that sense, the things that compose are relations and their operations.
I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?
> choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables"
The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing?
> postgresql documentation uses
In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation.
> and have a method chaining syntax.
And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider:
And now imagine having tens, maybe even hundreds, of slight variations on the same query in the same vein. Each used in different parts of the application, which is quite common in any kind of line of business application. I'll save your bandwidth and not spell them all out as this is just a contrived example anyway, and I'm sure your imagination can fill in the blanks.Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used.
But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.
If I'm understanding you correctly, then you can do something like
etc. You can also give different roles different permissions to access views without access to the underlying tables as a way to define a stable, high performance API, for example.I wouldn't use views for something so small, but I probably wouldn't use a query builder either. If you want a stable API, make a view to indirect access to the table(s). Don't break your view API. If you change the underlying table, update the view to keep it as a stable interface.
Query builders can be nice for generic code. E.g. you have a variable length list of predicates (e.g. from http query strings) and want to do `predicates.fold(_ => true)(_ and _)`. In that case you're basically using it as a macro system, which works because sql fragments compose. In fact IMO the most pleasant way to use a query builder is usually exactly as string interpolation macros.
ORMs, the original topic at hand, are an entirely different beast. I think generally people who bash ORMs don't have much issue with query builders.
AI can give better responses to a lot of requests when it has a well designed high level API available. And many ORMs don’t produce well designed APIs but it seems this one will.
Ai is as good as its training data and there’s vast sql documentation and source code that has been ingested by the AI engines.
I think that for some things user intent would be better expressed as customized ORM than SQL queries. If the ORM isn’t customized and is just generated from the tables then, yeah, not much of a help.
[flagged]
I never understood why people are so stubborn about hating on orm.
For example I'm familiar with https://gorm.io and it does save me a lot of time and useless boilerplate.
And guess what, if I ever need to make a complex query, I also happen to know SQL, and I'm just going to make a "raw" query https://gorm.io/docs/sql_builder.html#Raw-SQL and be done with it.
It's not all that hard.
edit:
the other common complaint usually is: "but I don't know what query the orm is going to make..."
Use "Debug" https://gorm.io/docs/session.html#Debug and it will print out exactly what query it's making. Not happy with it? Make a raw query.
> I never understood why people are so stubborn about hating on orm.
Objects are just bad abstractions for representing database interactions. They never map cleanly and any attempt to cover up the incoherence introduces further problems.
Avoiding boilerplate is understandable, but typed schemas and queries exist without hauling in a full ORM.
Of course you can pump out a lot of SQL very quickly with ORMs. There's a lot positive to say about this approach! But you don't tend to end up with code where you can easily tell what's going on.
> Objects are just bad abstractions for representing database interactions.
But they're excellent abstractions for business entities.
> Of course you can pump out a lot of SQL very quickly with ORMs. There's a lot positive to say about this approach! But you don't tend to end up with code where you can easily tell what's going on.
15 years building up massive apps with Django and SQLAlchemy and this has never been a problem.
> 15 years building up massive apps with Django and SQLAlchemy and this has never been a problem.
I guarantee you it is if the reader isn't already intimately familiar with SQLAlchemy.
If your job literally entails using a database abstraction and you can't arse yourself into learning said abstraction then it's a skills issue, just as much as people who want to use ORMs without learning SQL.
I can't speak to SQLAlchemy itself, but ActiveRecord (which I'm sure has both shared and unshared issues) still imposes a high maintenance burden with a small team of highly-skilled developers, both on the performance front and the validating-changes front. I, personally, find it very annoying to read, having to hop around an object hierarchy just to figure out what kind of query is generated and what kind of logic is "silently" added to the query somewhere in said object hierarchy (or, god forbid, mixins/monkey-patching).
You definitely make good points, and all my issues are over-comable with time and effort and knowing which tricks to use, so this truly is a matter of opinion and taste. I'm just pointing out that the idea that this produces more readable code seems far from obvious.
I think it's worth giving SQLAlchemy a try if you're writing a Python app. I've worked with most of the popular ones in Python/JS/Rust, and it's the only one I haven't had to fight with.
I'm back to using bespoke query builders and raw SQL since I don't use Python much anymore, but sometimes miss the syntax and migration system.
IMHO your critiques have more to do with Ruby's promotion of class hierarchies and the fact that ActiveRecord requires inspecting a DB to infer what's going on and Rail's abuse of monkeypatching.
I concur. I’ve used ORMs in certain projects, but iterating and optimizing them later on became untenable. I’ve learned my lesson — all my new projects forgo ORMs by default.
How? ORM logic is as simple or as complicated as the underlying queries and results. In 15 years of building web apps with Python ORMs this has never been a problem across many, many different teams.
A lot of people think SQL is antiquated, and therefore not worth learning. However, SQL is based upon Relation Algebra, which is the foundation of database optimalisation. Any data querying language/library that is not based on relational algebra will lack the performance. In particular ORMs fall under this category. Having said that, there are other languages/libraries out there that do use the relational algebra constructs.
You still need to know SQL when you're using ORM. You also need to know ORM when you're using one. It's really not that hard to make ORM perform well if you spend some time RTFMing.
When was the last time your RDBMS was handling business logic and wasn't just a persistence layer for your application? SQL (the language) isn't composable so you often have a choice of building strings or having many slightly different queries.
Anyway, pros of using an ORM outweight it cons in my opinion.
I respect that some prefer just to use SQL, but that isn't where most stand.
Also, instead of a reactionary "all ORMs are trash," where ORM probably means different things to different people, maybe you could provide some value to the conversation by providing specific points and/or arguments supporting your feelings about ORMs. At the very least, you could provide some citation to an article that does the summarization.
Moving fast is often crucial, but you typically also get more readable code, which is also worth it.
A query/DML that is badly optimized is usually very complex, which means that the ORM's syntax isn't a good fit. No problem, since they typically support raw SQL calls, so nothing is really lost.
You just have to know when to use the right tool for the job, as always.
> Moving fast is often crucial, but you typically also get more readable code, which is also worth it.
I think the exact opposite is true, actually—because of the introduction of stuff like lifecycle hooks it becomes very difficult to figure out how the domain semantics translate to query/execution semantics. Of course some are lighter than that and just e.g. map a table to a domain type (which is much more readable), but that's not typically what catches criticism.
Nonsense. People use ORMs because the vast majority of queries are trivial. If you need something the ORM doesn't provide sufficient control over, only then you move to raw queries for these cases.
It’s has nothing to do with being simple and everything to do with wha the database looks like at the end of the day.
Some ORMs are better than others but if if you have ever looked at a database created by a ORM it always has weird lookup tables, funny names and even with simple objects completely unusable without the ORM.
We live in a multi language world. There is a high chance you are going to want to access this data with a different language. This langue is not going to have the same ORM as such you will have a horrid time access the data.
ORMs often lay their data out in a way that is highly language dependent.
The fact of the matter is SQL is not hard and bypassing the main interface to the database and hand wave it away will always bring you regret when writing any sooty of software other than a toy project.
Your best case is you become an expert in the ORM. Resulting in a skill set that does not transfer easy, language locked and the worst of all bottle necks any changes to the data layer to your ORM expert who at first will be proud and happy and end smug and bitchy as all the data layers request changes will simply be redirected to them.
When people like me say ORMs are trash it’s much more than any of the surface level rebuttals listed here. It’s about the whole life cycle is your project. Adding up all the places ORMs can fuck you just makes it a bad proposition.
God forbid you need to upgrade your database or the ORM version.
> ORMs often lay their data out in a way that is highly language dependent.
Which ORMs did you use? This doesn't sound normal at all. Never saw this with Rails, Ecto, EF, Sybase, or even the legacy project I once worked on that had 4 different ORMs for different parts of the same backend process, using the same database (some were very old and behind slowly phased out over time). Maybe you have ORM confused with CMS (content management system). A CMS can do those things, but that is not an ORM.
> There is a high chance you are going to want to access this data with a different language.
There are tools for that, such as ETL, read replicas, data warehouses, code generators, raw sql, stored procedures, views, just off the top of my head.
Well, different people, different experiences.
Same experience just different time lines.
`if you have ever looked at a database created by a ORM`
You do realize that you can make your own migrations using raw SQL, and still use the ORM with your tables?
Yep, plus learn SQL and you can use it everywhere.
Every language has it's own (multiple) language specific ORMs.
Sorry but anyone who needs to build queries dynamically e.g. for extended/advanced search or ACL is going to significantly benefit from being able to seamlessly mix regular code and query logic. Compare that to the old way of concatenating SQL fragments. Shudder.
And yet, the Vietnam of Computer Science paper didn't stop the creation of ORM's.
There's something fundamentally broken with SQL syntax, and yelling at people to "Just Use SQL" doesn't really help.
Its better than sitting silent and doing nothing. What it does is validate other peoples feelings who feel the same way. And just maybe, that validation will lead to them saying "fuck ORMs" when some junior dev comes in and tries to use one.
Fair, but it also reinforces the stereotype that the pro-SQL types are obstinate relics yelling at the youngsters to "Get off my lawn!"
There's a pattern of "We can't change X, so we'll write Y that transpiles down to X". It happens often with closed source tools and others that can't or won't implement new languages. Verilog, SQL, Javascript, all fit that bill.
E.g. Why is Javascript the only first class language for the browser? For the longest time JS was the only game in town.
> There's something fundamentally broken with SQL syntax
? Are you referring to something specific?
excuse me, what is Vietnam of Computer Science paper? what’s vietnam has to do with it?
I am not anti-ORM, but I do know that reference: https://www.odbms.org/wp-content/uploads/2013/11/031.01-Newa...
Yeah, let's trash the 50 year old industry standard and let's obfuscate the interface to one of the most performance sensitive part of an application. Hell, lets build multiple obfuscators for each language, each with it's own astonishing behavior that turns pathological once you actually get usage on the app.
You know whats also a 50 year old industry Standard? Assembler. Yet, Nobody writes it any more.