class Account < ApplicationRecord
scope :active, -> { where.not(active_at: nil) }
belongs_to :user
end
class User < ApplicationRecord
scope :born_before, ->(born_on) { where(birthdate: born_on) }
end
active_users_with_birthdays_today = Account.active.joins(:user).merge(User.born_before(Date.today))
Contrived, of course, but it's not hard to see how you can use these sorts of things to build up record sorting, filtering, etc. Doing this by hand wouldn't be very fun.How are you canonically storing these predicates in code to reuse over 5-10 queries?
If you have a complex predicate that defines a set of rows, and you use that set in many different queries, are you rewriting it each time?
Maybe I do, maybe I don't. It depends. Most likely I won't, unless it's used in like 80 % or more of queries and the problem domain guarantees that it will basically never change and if it does it will for sure, absolutely, change everywhere at once.
I like easily testable SQL queries that are easy to read, right there in the code. No need to execute anything to get to the SQL that would execute in the database. 'But then I need to write thousands of queries?!' Yes, you do, and you'll have a chill and easy time whenever they change, and they will if your software gets use and your data grows. Because you can just search/replace on the SQL text, and don't need to figure out every place some mutilated piece might get called from and probably get it wrong a few times or need to figure out how to split it into two or more fragments.
In some cases you can figure out a robust way to generate SQL but it's rare, it mostly comes up in one-off scenarios.
Especially if you are defining types anyways to extract the data from the sql into.
“SQL strings are one of those things that make sense for really tiny projects but fail to scale once complexity settles in“
Large projects require reuse, composability and easy refactoring. All things ORMs excel at.
On a small code base it is easy to rename a column or add a column, etc.
On a large code base with already 100s of queries using that table, without an ORM it isn’t as straightforward to update all references and ensure that ever place consuming that table has the new column info.
In my experience, the way to get the best of both worlds is to use a query builder as opposed to a full ORM.
Never had an issue with Django on a large project at a previous $8b startup whose code base went over several major data refactors.
In fact Django’s excellent migrations was specially called out for the reason for our confidence in making large DB refactors.
In addition to that, over the years of writing web services, I always found raw queries much less maintainable. The ORM immediately tells you that you messed up your refactoring, whether it is just renaming a column, or removing a table completely.
Pretty much every ORM (including the one in Cot) also allows you to write your own custom queries if the abstractions are not enough, so there's always this fallback option.
Well sqlx checks at compile time your raw query if you use their macro, at the expense of increased compile times.
Yeah SQL is extremely simple to write, that isn’t really the problem ORMs solve for me, but rather they solve composability and reuse of queries.
I think there is a similar vein of people crying about sites not using 100% vanilla js without a framework. They are missing the point or don’t have enough experience with the actual problems the abstractions solve.
When using an ORM you already have to be good at both for any nontrivial query.
I actually find myself on crosspath where I did start with just SQL but now the database has grown a lot and it is just too much effort to keep writing queries as features pile up. Switching to ORM in one take would be nearly impossible but probably it could work as a step-by-step process, so have both SQL and ORM. Still thinking about this.
Haskell and Elixir, etc all have ORMs (or rather ecto calls itself a data mapper but functionally it is 1:1 analogous to an ORM in any other language)
I use ORM query interfaces 80% of the time, and for performance critical queries I can use direct SQL. But the query sanitization, smart preloading and performance checks, schema management and migration tools they often provide, and type checks / hints are really nice features of ORMs.
Furthermore, ORMs are ESPECIALLY useful in teams where you have more junior devs who need these guardrails.
I think the issue here is trying to reinvent a new ORM.
Using Django's ORM, that would simply be: link=Link.objects.get(slug="cot")
It'd still be very readable and manageable even for complex queries.
Also, people like ORMs. Type safety is nice too without having to map manually. Sqlx is also great
Don't you get type safety anyway with parameterised parameters?
I would have hoped that by 2025, new projects would have moved away from ORMs entirely. They really are a useless abstraction layer that always brings tons of trouble and usually makes queries harder to write.
Looking at the first example in the docs
https://cot.rs/guide/latest/db-models/#retrieving-models
I really don't get the point, and that'll certainly get worse with any somewhat non-trivial query.Why go through all the trouble of reinventing a new DSL when the SQL equivalent would be so much cleaner?