Comment by elcritch
1 day ago
There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness.
I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries:
# Multiple joins with pagination
let page = query:
select Post(title)
join Person(name) on author == id
join Category(title) on category == id
orderby desc(post.creation)
limit 5 offset 10
I think that's better since defining SQL should be the source-of-truth for the DB and the code. ORM's always ended up causing trouble in my experience.
Things like indexes, defaults, partitions, etc generally aren't expressible in code without a lot of kludges. Then each DB engine have pretty different rules, syntax, etc for tables.
However having the queries compile time checked, type conversions handled, and the nuances between SQL query syntax handled is rather nice. As you mention it's a much easier subset.
Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with.
Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. The things that are annoy you about it are often there because of something you don't yet even realize is something you need to be aware of, or because your fundamental understanding of things is just wrong or incomplete.
I think the issue is that while ORMs etc, stuff like ecto…whilst they’re never going to be database native like actual SQL, the value in the abstraction isn’t making querying easier, but making more robust and useful the integration into the host language. It brings it out of database domain and into application domain so that doesn’t have to to constantly reinvented.
You can always be more expressive and portable in raw SQL, that’s obvious, but the things you’re doing have to be used somewhere, so at some point the things you are doing have to cross a barrier. For the 90% use case, ORMs are a pragmatic choice because the good abstractions aren’t about the syntax, they’re about allowing you to talk about and mutate data within the language paradigms that everything else is written in.
I already know SQL which is why I like the above. It's SQL with some tweaks to match Nim syntax and to have less ambiguous table/column identification.
Meanwhile embedding SQL in a string with `?` everywhere, manually converting the results, and remembering some of the SQL syntax is annoying.
Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none)
It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’s absolutely horrid at it, with random keywords, extraneous syntax, and general lack of compositional capabilities.
The relational model is fantastic — Codd is Godd, after all. The engines are a work of art. The SQL language is a shitshow. PL/SQL and all its variants are a crime upon the PL community. The programmatic interface to a database is a shitshow, because it is SQL and only SQL. The SQL standard is a joke and standardizes nothing.
None of this is contentious, or should be, once you’ve learned SQL.
Only because some people are very opinated in avoiding stored procedures, and think smashing strings together is a much better solution.
5 replies →
> Just learn SQL....
I agree. In my experience, ORMs are more complex and harder to learn to an expert level than SQL. Knowing Java (but not SQL) doesn't help much with learning Java ORMs (Again, to an expert level). Besides not supporting all the SQL features of some DB, ORMs also covers other things such as caching.
Learning ORMs is likely just as difficult as learning SQL. It is likely harder to learn how to optimize performance with ORMs.
SQL as opposed to code has the advantage that it can be kept in a separate file, and thus modified by experts in databases without changing the code. The article claims the author found migrations harder with SQL than with his framework. I would think it would depend a great deal on the database one is migrating.
I'm not convinced that LLMs make things easier, you still need an expert to verify the generated code, and to tune it, as often the database is business critical with serious consequences if wrong, slow, or turns out to be infringement of someone's copyright.
Just learn SQL!
No. SQL is just bad. It's an old way of doing things. It's not hard but it's not good.
Take this for example. Why do we have static type checking for typescript? Why do we have a build step for this?
Why DON'T we have it for SQL? Why is it runtime strings? So no static checking and the only way to test if a query works is to run it?
The purpose of these replacement layers is to get it all under one language. Once it's all under one language you get full safety and fusion across the two concepts. Query builders and ORMs are shooting for an ideal, and the ideal makes sense. It's just a nightmare to implement and thus fundamentally there are compatibility issues and that's why a lot of people in general don't like orms.
There's also a sync step where the model in the language has to be aligned with the model in the database which is just an extra mutating state layer which further compounds the bugs.
Only true when avoiding stored procedures.