Posted by steveklabnik 10/23/2024
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.
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".
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.
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 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?
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.
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?
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:
users = select("*").from("users")
admin_users = users.where("role = 'admin'")
recent_admin_users = admin_users.where("created > LAST_WEEK()")
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.
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();
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.
That gets you halfway there, perhaps, but I'm not sure it explains how you would use it in an actual application. Are you back to writing hundreds of "SELECT * FROM recent_admin_users"-type queries in the application, once again exploding the development effort and maintenance surface – exactly what you're trying to avoid with these types of tools?
> ORMs, the original topic at hand, are an entirely different beast.
The original topic at hand is active record, not ORM. ORM is, as the name literally tells, about mapping between relations (or, in practice, tables) and objects. No sane person is going to bash ORM. They may dislike a particular toolkit that tries to help with ORM, but at very least they are going to do ORM by hand.
But that's not what we're talking about. The original comment that set the context for this particular discussion is about query building. It literally proposed using an LLM to generate queries instead. You can query build in the active record style: e.g. `User::all().admins().latest()`, but that's still query building, exactly like the earlier example except with a different API.
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.
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.
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.
I guarantee you it is if the reader isn't already intimately familiar with SQLAlchemy.
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'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.
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.
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.
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.
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.
Every language has it's own (multiple) language specific ORMs.
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.
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.
You do realize that you can make your own migrations using raw SQL, and still use the ORM with your tables?
There's something fundamentally broken with SQL syntax, and yelling at people to "Just Use SQL" doesn't really help.
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.
? Are you referring to something specific?