Item — HN Reader

So I want to respond to this because there is a genuine question behind your rhetoric. And that question is "How do I cleanly do complex things in SQL without an ORM?"

And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables.

The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views).

The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.

4 comments

I wrote SQLAlchemy after a five year job where all we did was work with enormous views in an Oracle database. They performed nightmarishly bad. The slightest attempt to query from one of these views using something as heretical as a join would kill the database. The source code for the views was locked up tight by DBAs who had no idea how our application worked, didn't care, and they could never be bothered to help us with their horrendously inefficient views or to give us new ones that our application sorely needed in order to get data from the database in an efficient manner.

SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.

On the one hand the article itself is offering a anecdote about being unhappy with ORMs. On the other hand you have an experience in which you were unhappy with views.

That would leave us at an impasse (he said / she said), unless we get more nuanced. So let's.

It sounds like some of your frustration is at some DBAs you knew. Firstly I've never worked with a DBA, I'm just a senior software engineer / DevOps and to me that includes an intimate knowledge of SQL perfomance, so maybe this is why I don't mind an in-database approach.

I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).

Now maybe you agree with that, and see ORMs as a tool for the kind of job where the dev is locked out of the database. If so then I can't really disagree as I haven't worked at such places much at all.

> I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).

if you have the option to use materialized views, that can be helpful when appropriate, however materialized views have their own issues, not the least of which is that they need to be refreshed in order to have current data, they can take up a vast amount of storage, not to mention a lot of databases don't support them at all. It should not be necessary to emit CREATE MATERIALIZED VIEW with super-user privileges and to build the infrastructure to store them and keep them up-to-date for every query one needs to write or modify that happens to have some degree of composed complexity.

you don't need an ORM for these queries either, just a composable query builder (like SQLAlchemy Core) that allows the composition task to be more easily organizable in application code.

none of this means you can't use views but it's unnecessary to dictate that they should be the only tool available for dealing with large composed queries.

SQL Alchemy will spit out SQL query. Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.

> Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.

No, on two levels. Firstly, there are many features in SQL that an ORM will not support (e.g. no substitute for a materialized view).

Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column). Part of being expert in DB technologies is knowing how/why some ways of writing a query are very fast, and others are very slow. The idea of an ORM is to hide that complexity, which is fine for a toy todo-mvc app. But once you start querying tables with 100k rows you need to understand that complexity and master it if you want to write a fast query.

> Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column).

SQLAlchemy supports case-insensitive searches, per-column / per-expression collation settings, index and other SQL hint formats for all databases that support them, e.g. SQLAlchemy's query language supports most optimizing SQL syntaxes, with the possible exception of very esoteric / outdated Oracle things like which table you list first in the FROM clause (use index hints instead). We are adding esoteric performance features all the time to support not just SQL-level tricks but driver level tricks too which are usually much more consequential, such as the typing information applied to bound parameters matching up for indexes as well as special driver-level APIs to improve the speed of bulk operations.

SQLAlchemy has been around for thirteen years, plenty of SQL experts have come along and requested these features and we implement them all. Feel free to come up with examples of SQL-expert level performance optimizations that SQLAlchemy doesn't support and we'll look into them.

Yeah, don't get me wrong, I haven't used SQL alchemy and I'm not trying to besmirch it. I'm not even sure we disagree.

What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work. My point about views is that in my experience, the answer to disgusting queries is cleaning up the DB design (and views can be the tool to accomplish this). My point about case-insensitive searching (you can create a case-insensitive index if you want) is that a lot of db-performance stuff just can't be solved on the query side alone anyways.

It sounds like SqlAlchemy is designed with a lot of flexibility around how queries are run, so maybe you agree that understanding what's going on beneath the hood is important to handle these complicated cases.

> What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work.

Thank you for this response and I agree, we are likely on the same page. I don't know that anyone actually espouses that philosophy. This is the anxiety that ORM skeptics have, and certainly you get beginners who rush into using ORMs not knowing what they are doing, but if someone wants to be a DB expert, I'm pretty sure they go to read about databases :) These ORM beginners will fail either with the ORM or without out. I guess the idea is you'd prefer they "fail fast", e.g. the ORM covers for them while they proceed to screw up more deeply? This is arguable; if you've seen much of the non-ORM raw DB code I've seen, it too fails pretty hard. But even with this argument, if ORMs produce the problem of incompetents who are not found out fast enough, why hoist the denial of useful tools to those developers who do know what they're doing.

You have shifted the discussion from ability of DB engine to optimize queries to the ability of a developer to write fast queries.

But in any case, we are not discussing some abstract ORM whose "idea is to hide complexity", but SQL Alchemy whose idea (one of them, at least) is to allow you to write composable queries yet still retain full power of SQL.

You are correct about needing to understand the underlying dB to be able to optimise it. I consider myself lucky to have come from a raw sql background and to have a deep understanding about how the query planners work.

You are incorrect in your other assertion. Sqlalchemy allows for optimising your queries at runtime in a way that just isn’t available to sql views. The beauty of sqlalchemy is that allows for arbitrary composition of blocks of sql. I too have had to fight with dbas in a previous life because they didn’t want me to construct a query in code (non sqla), but I eventually won because I was magnitudes faster because I had runtime knowledge they couldn’t use.

Many of us also know stories about how ORMs can be slow if developers don't pay sufficient attention. In some cases told by database experts who see the query code locked up tight by the app team, etc :)

Having a separate "DBA" from the developers or having an adverserial relationship with said person is not really a technical problem I think.

I've had similar issues, but I got the added benefit of being "DBA-splained" on why what we were doing was terrible.

I feel like if a DBA keeps thinking of the database as the solution to all of the problems (by implementing views, triggers, stored procedures, etc) then maybe that person should be willing to support them, otherwise they really shouldn't complain that the developers moved to an ORM.

Okay, obviously I've never worked in an enterprise enough company. I've always assumed a DBA was just a member of a backend team who likes thinking about SQLy stuff. Or a sysadmin who upgrades the computer the db runs on. Basically, a hat someone wears.

Do I seriously make the correct assumption that the people who are writing the backend of many apps are actually separated from the people who decide how the data those apps process the data, and you may have no direct say about the database schema? that effectively just a client - perhaps the only client, but nothing more than a client.

Obviously you've described the disadvantage of this approach; what advantage does it have?

It makes some sense when the data is extremely valuable, like in a bank. As a developer you can only access the db through stored procedures created by the database team. Which limits the damage you can do. I haven't worked in a big enterprise in years though so I don't know how common this still is.

Wait, so you wrote this library to solve the political issue of crap DBAs?

Code inside a database is a nightmare to maintain.

Just like Data in a codebase ;-)

> The idealistic answer is good DB design from the start.

The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too).

And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."

There were multiple literal questions embedded in his rhetoric; which without answers, invalidate the author's solution.

> The idealistic answer is good DB design from the start.

I don't feel like this answers the question. My ORM supports views.