Hacker News Reader

Comment by dahwolf | Hacker News Reader

"this is mostly because i relied on a SQL ORM which in short is a tool that makes writing SQL easier to pick up and faster to develop. the biggest downside is that it might execute 50 queries to your database to get a list of information, when it probably only needs 1, which will cause slowdown."

I appreciate this honesty. Listen to this old man's advise: learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Then just write SQL directly.

I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls.

You can then become proficient in trying to reason and predict about what your ORM calls do in the actual database, but when you're several joins deep, this becomes near impossible. At which point you become the ORM, and might as well just write SQL.

View on HN

Honestly, I started on the ORM hate train when I first started and wrote my own queries in an effort to be "performant", but my frankenstein of writing direct queries and abstracting code to be reusable became basically a super shitty ORM that was a nightmare to maintain for a large codebase lol. I don' think there's anything wrong with it and for 90% of CRUD actions it's what you should choose. By the time you hit performance issues you've probably thought of something else anyway.

I am the same, the advice of "learn SQL" is still very important. Most ORMs have the escape hatch of just mapping results of a straight SQL query to an expected structure (thinking of typed languages like C#/Java here). 95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL.

I've been on projects where the team has re-invented an ORM organically, and I've never seen it go well. Likewise with projects that are ORM 'purest', bending themselves over backwards to use an ORM for a query that can't easily be represented by whatever query syntax it has.

This is why I still think 'lite' weight ORMs are the best of both worlds since they usually have a pretty good experience for common/easy queries but then when things get tricky, it is best to just use SQL and map to an app language data structure for the results. I mostly use OrmLite [0] in dotnet and have found it has a balance that done well by me for years (note I now work at ServiceStack who built OrmLite).

[0] https://docs.servicestack.net/ormlite/ormlite-apis#query-exa...

"95% of the time I can just use the ORM to make simple queries in a easy to maintain way for your application. For the 5% of queries that are either complex or can't be easily represented via your ORM of choice, declare the expected type response and use that escape hatch to write your performant SQL."

site creator here - yeah this is the approach i'm taking now. the ORM is useful for sure and there's still a benefit to using it, but anything that needs to read from a few different tables, i'm definitely going with raw SQL

I felt the same way until I found sqlx (https://github.com/launchbadge/sqlx). The package lets you write sql in your app with compile-time checks. Was so much easier to get up and running than learning a new ORM.

Need this for Go. Been considering writing a static checker for ages.

Yeah, I am old enough to have started back when ORM did not existed yet. World with actual tested ORM is way more maintennable, waaay more easy to deal with. You can write your own queries when you really need to ... but should do it only when you really really need to.

Writing queries is easy part. Maintaining that thing is the hard part.

I'm wired differently.

Even if I'd run a database operation just on a local machine and it takes 100x more load than an optimized SQL query, I still care. It's craftsmanship.

Sounds like you may have a case of the abstraction disease. You keep reaching for abstractions that never accomplish the actual benefits of a truly solid abstraction.

> I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls

That doesn’t sound like ORM… More like an N+1 problem. Eager-loading makes N+1 more likely with ORMs, but it’s easy to avoid when you know what to look for.

ORMs are designed to reduce querying, not increase it a thousand-fold :)

N+1 is a common problem with many ORMs, and a classic example of the object-relation impedance mismatch.

Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory.

Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.

Upfront: I really dislike ORMs. They're an unnecessary abstraction and a performance headache as an extra layer of middleware to worry about keeping up to date. I'd never use one in production.

But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.

Right. In fact any ORM worth its salt will have methods specifically designed to let you address the N+1 problem easily.

E.G: django "select_related"

Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query.

Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with.

In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".

It's hard to explain to developers why an ORM isn't really that great of a solution....but at a high level:

* your database is the core of your app. Abstraction implies that you don't care about the details of your data store, which eventually will lead to two major problems:

1. When things are slow you won't be able to debug it, because you're abstracted away your database and don't understand what queries your ORM is using to do things, and

2. when your ORM migration fails you won't know what to do, because you don't understand your database. In general this happens with every ORM product, ever, because you didn't put in a constraint initially because you didn't know what that meant and then you did later, which caused the migration to fail because your constraint is being violated. Or the migration fails because the tool uses some attribute and not others for drift detection, so it tries to delete your table/database because it's detected drift.

* the way you deal with data in code and in a database are different. Code iterates over objects. If you just use your ORM naively you'll end up doing some ridiculous number of selects because in code it looks like you're just iterating over objects...when in reality the ORM is doing a select/join for each one of those objects. And if you start getting more complicated there are structures that are just harder to do with ORMs...because you have to map what you're trying to do in SQL to the way your ORM works. At that point why not ditch your ORM?

SQL isn't really that hard...but thinking about how do queries is harder than you would expect. It's almost an order of magnitude faster to do joins in the database than to join stuff in-code...but that also assumes that your schema isn't screwed up, it has indexes, etc.

Lastly, a lot of ORMs don't put indexes on columns for some reason, which kills performance. You'd think that would be a detail that would be abstracted away for you at the ORM level, but it isn't. I mean it knows what you're using to select data, so it should auto-create indexes for you, right?

> I mean it knows what you're using to select data, so it should auto-create indexes for you, right?

Curious, how does it know what you're using to select data?

Because you're using the ORM to do selects.

I'll give you an example: prisma has a "find/findMany." As part of the build process it should examine your usage and put indexes in the column that you're using in find/findMany

Indexes are an implementation detail - but to use RDBMSs successfully you need indexes. ORMs try to hide those implementation details, but without those kinds of details you get bad performance.

Prisma just fixed another problem it had with transaction isolation levels. To even understand the problem you need to understand SQL, how databases work, and how each database implements that. It's another important implementation detail that's abstracted away...until it bites you in the ass.

Prisma also finally allowed you to use database-specific types instead of just using varchar(191), which was its default for strings.

I'm picking on prisma because I happen to be using it right now. There are good things about it, like the way it does migrations and handles schema changes. But there are times its abstractions get in the way, like when you're trying to do aggregated group by (which is easier in SQL than it is in prisma). And its migrations often fail, and to understand why you really need to understand SQL.

Hibernate was worse, because it provides a transparent object-level backing store.

> As part of the build process it should examine your usage and put indexes in the column that you're using in find/findMany

This is not as easy as it sounds, though. Reliably inspecting source code is hard, and definitely not in the scope of an ORM. It also can't catch dynamically generated queries (think user configurable filters). It might be a good idea for a third party product that analyses your code and suggest adding indexes to models.

You know who knows best about your queries? It's the RDBMS. There are tools that can add/suggest indexes by searching your database logs for slow running queries.

Ad2: Every single real world java project I have seen used hibernate (ORM) with flyway. I never ever seen any project in production where some underlying tech would be deleting tables autonomously.

Ad1: People do debug ORMs, actually. Including queries effectivity.

Ad2 -> I've run into lots of hibernate apps. They also tend to use Spring, and the team often gets lost trying to figure out which one is the cause of the problem. It's usually both.

Hibernate and Prisma migrations will delete tables if you allow them to.

Ad1 -> I'm sure they do...but usually they call in a DBA, who basically says "this is a disaster" then recommends that they refactor everything because their database schema is fucked up because it's based on some weird object hierarchy instead of RDBMS principle.

I dont find neither of your responses consistent with reality. First, database administrators do not debug ORM. Second, it does not take specialist to debug those, developers debug those. Third, recommendation is rarely "refactor everything". Usually it is adding an index, change the query or some other minor tweak. Fourth, "weird object hierarchy instead of RDBMS principle" does not even mean anything.

Fifth, spring has nothing to do with database whatsoever. Sixth, every production code I have ever seen used flyway.

I think you're mixing up someone who's decided to use an ORM rather than some hodgepodge of string concatenation with someone who has no idea what a database is, is unable to set it up effectively, and is unable for some reason to debug the sql emitted by ORM queries or use their ORM at all effectively to avoid N+1 problems. Not really a fair comparison.

Learn SQL and use the ORM is usually the correct answer. Let it print out the SQL queries and how long they take (ORMs do that out of the box), then check for indexes and other standard issues. If there’s some unusual complex query to be written, sure, write that by hand.

If writing SQL directly, what process do you use to update your queries during schema changes? Do you rely on a test suite to catch errors then update queries by hand? Are you using compile-time checks through libraries like sqlx [1]?

[1]: https://github.com/launchbadge/sqlx

This is exactly why I find no solution fits all here. For me, I use an ORM as a catch-all and then for certain applications I manually write the queries. It's best of both worlds, and I know what components in my app have custom queries so I can test against them.

Sorry to butt heads there, but what ORM does automatically handle schema changes? I have so many teams with highly abstracted ORMs telling me that no-downtime schema changes are impossible, no matter how trivial the changes are, or would be in e.g. Hibernate.

And the only team capable of zero-downtime schema changes uses a minimal DSL to SQL lib.

This problem seems foreign to me. Are your teaming not using Laravel? If not maybe it's time to upgrade. https://laravel.com/docs/10.x/eloquent

I don't know of any ORM that handles schema changes automatically (edit: for the most strict definition of "automatically" -- I've used ORMs that generate migration files and am generally happy with them).

My experience here is quite limited, so I'm inclined to defer to yours. With that caveat, I find that ORMs play more nicely with my IDE (e.g. I can rename a column name and automatically update all call sites) and often raise early warnings if their declarative classes are out of sync with the underlying table schema.

There are two parts to the schema change, the table changes (migrations) and the code changes. I think the person you were replying to was asking about the code part. Every ORM I've used or looked at[0] does that automatically in the sense that you make a change to a model and all queries generated by that model will now have that change. Depending on the change they could break, but the point is the changes propagate. If you're not using an ORM and are just using string templates, this won't happen. There are query builders, too, which are sort of in between.

[0] Django, SQLAlchemy, Active Record, Piccolo, Tortoise, SQLModel, peewee

^yes, that's indeed what I had in mind, and you've articulated the position very clearly.

Thanks, I’m glad! It’s a topic that pops up for me a lot. Related: https://news.ycombinator.com/item?id=26905647

The persistent[0] library can automatically generate and run migrations to match schema changes.

[0]: https://hackage.haskell.org/package/persistent-2.14.5.0/docs...

Finally something that bucks my instinct that integration tests are a waste of time. If I’m deploying a SQL query, everything that gets in or out gets thorough automated testing. Regardless of your attitude on the subject, directly interfacing a database is business logic. Test it like you would anything else that interacts with an external dependency. If you made a schema change and it broke something, you’ll either get test failures, or learn where your coverage is incomplete, or learn where you don’t care about a particular failure mode.

Normally I’d prefer testing more isolated units, but if it’s your own data access, just write the integration tests. They’re at least implicitly part of your “unit” anyway.

I recommend taking a step back before making a backward-incompatible schema change. Do you really need this? Chances are, it's just faffing about out of a sense of neatness and orderliness and you don't actually need to break the schema.

I'm happy to write my own SELECT queries, but it sucks to be manually schlepping row[0], row[1], etc. into the fields of my domain entities, or making sure I provide the parameters to the parameterized INSERT statement in the right order. ORMs usually let you drop down to SQL as desired, and I do that when I have a nontrivial query to express, but CRUD plumbing is nice.

Not sure which programming language you are using. For Java, I find Spring JDBC templates with Row Mappers more productive than ORMs. DataClassRowMapper will automatically populate the domain entity.

Go, Java language-level 8, Python. All have an approach, usually with some third party library, but the bone stock database drivers deal in field indexes. And people consider those libraries "ORM" whether or not they really are technically.

The core problem with ORMs is that you end up having to learn the ORM on top of learning SQL and the particular database. It ends up being like working with the database through an opaque indirect layer.

Better yet, learn stored procedures, then call them just like any API with a well established set of inputs and expected return typed outputs. You'll thank me later once you decide to split your DB from your app server.

It's been at least 12 years since I had a DB on the same server as the app or service that called it... I can't fathom why anyone would do that these days. But either way, the stored procedures would work just as well. The issue you're alluding to (I think) is the time to set up connections for a dozen calls between the backend and the DB if they're on separate servers. But that's actually less of a bottleneck now than it used to be, with persistent pooled connections and most things hosted in the same VPN zones. I'm not arguing for ORMs... but appside processing [edit: "joins"] can still be appropriate if you're dealing with a monolithic DB and don't want certain giant queries or procedures to block rapid reads. The point is more to understand your infrastructure, and ORMs obfuscate it to a point that makes you irresponsible as a developer in some cases.

Also, with lateral joins and windows in mysql 8 you can really have control over whether you prefer your execution plan to loop or scan. Which takes away most of the rest of the argument for appside processing.

In my limited experience, the best thing about ORM isn't the querying, but the mapping to objects. This is why I really like ActiveRecord's `find_by_sql`[1], which allows to write a raw SQL query and get an array of objects.

[1]: https://guides.rubyonrails.org/v3.2/active_record_querying.h...

> learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life.

Exactly the same is true of ORMs. I find most of the people who advocate "just use SQL" have a bizarre aversion to applying the same learning effort to their ORM.

> I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls.

That kind of problem happens with regular SQL all the time, and the tools to test/investigate it are less available.

> have a bizarre aversion to applying the same learning effort to their ORM.

SQL is a lingua franca while an ORM is specific to a stack.

If you know that you will always, say, be using SQLAlchemy on Python, then learning it well might be a good investment, but I don't find learning multiple ORMs to be a good time investment over just learning SQL. Most every stack has good, lightweight query builders.

> bizarre aversion to applying the same learning effort to their ORM

Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated, and then I'll commit a patch to the open source project to fix the undocumented internals and shepherd a custom dependency for 6 months while it gets into a numbered release. Then I'll do it all over again when I switch stacks and have to learn a completely new ORM.

Or I could just use SQL.

How do I map my complex join into a list of structs with embedded associations? I'd probably need to write a good bit of code just to unmarshal the data, right? How are you doing this every time you write a new query?

Can you give an example?

> the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated

ORMs are much less bad than SQL at that, IME. If the ORM generated a particular query there is usually documentation for why, often an option you can change. If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do.

"SQL is worse" is a nonsensical comparison. The problems of ORMs add to the problems of SQL and compound the problems of SQL, they do not substitute the problems of SQL.

I'm glad you found an ORM you like, but it seems we have very different experiences with ORMs.

Depends. Sometimes the ORM knows about the pitfalls and works around them automatically. Same as how using a compile-to-JS language can often end up being much nicer than writing javascript yourself.

> If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do.

MySQL Index Hints: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

MariaDB Index Hints: https://mariadb.com/kb/en/use-index/

PostgreSQL: No. More information: https://stackoverflow.com/questions/309786/how-do-i-force-po...

SQL Server INDEX hint: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...

Oracle INDEX Hint: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e...

Of course, knowing exactly what is wrong and what hint you need to use (whether the issue is the index in particular or something else) is also a bit of work to figure out.

I recall working on a project that had Oracle as the RDBMS of choice and there was this one query that took approx. 45 minutes to execute, which wasn't acceptable. Merely a SELECT that JOINed a few tables and checked the data with some EXISTS constructions and such. If memory serves me right, looking through the AWR reports and adding a single NO_EXPAND hint made the query execute in not 45 minutes but around 3 seconds.

Of course, ORMs don't necessarily solve the aforementioned types of problems either, since it's still SQL statements being executed under the hood and figuring out how to add hints to those might be a bit more challenging. I've had cases where you need to drop down to native query level when using ORMs for particular queries, because there were no elegant ways of doing it otherwise (apart from creating a DB view and mapping against that in some cases, for read only access).

I think both using ORMs and using SQL directly suck, just in their own unique ways. That said, I wouldn't outright dismiss either: depending on what you're doing, one or the other is going to be a good enough tool for the job. You occasionally also see some pretty interesting projects in the ORM space, which is nice.

For example, jOOQ allows you to use a type safe fluent API for constructing your queries: https://www.jooq.org/

Oh and MyBatis let's you generate your own SQL dynamically, which is an interesting approach: https://mybatis.org/mybatis-3/dynamic-sql.html

I would always use an ORM over raw SQL but this doesn't make any sense, your ORM is still going to run a query on the same DB engine. It is much harder to optimize an ORM query, but if you're only writing very simple operations you don't typically need to. The power of ORMs (at least light weight ones) is compile time types and parameters by default.

Yes, both SQL and ORMs can give you problems, but I have seen far more serious, hard to diagnose trouble from ORMs than straight SQL.

Learning straight SQL is far more transferable across databases than learning an ORM.

There are reasons to have ORMs, for e.g. to keep a large code base consistent. It's useful for keeping relatively simple queries consistent across an ERP system, stuff like that.

However, learning SQL is going to have a far higher payoff across your career.

I’m not sure I understand the consistency argument… Do you have an example, even if it’s just a silly one?

The example is from a python ERP that I modified a few years ago. All the db interaction was done through an ORM and I thought that it was an OK strategy for keeping the SQL consistent and manageable.

There were other problems with the ORM approach -- mostly that it ran tens or hundreds of extra queries for relatively straightforward SQL queries. But the software was only used by a few people at a time, so the performance wasn't that big of a deal.

> Learning straight SQL is far more transferable across databases than learning an ORM.

Not my experience. For example a recursive/tree query usually has one way to do it in an ORM, but three or four different ways to express it in SQL depending on the database, some of which seem like they're wilfully screwing you over (one popular database requires you to only write "WITH" and won't recognise "WITH RECURSIVE"; another requires you to write "WITH RECURSIVE", even though the error message it gives you when you only write "WITH" tells you that it knew exactly what you meant).

"That kind of problem happens with regular SQL all the time"

Well, no, it doesn't. I told you to learn SQL properly. When you do, you know what a query plan is. Which is something you'd rarely need if you know how to design a database and its indices.

All of this would be covered in every beginner's book.

I'm aware of what a query plan is. I can see that the query plan is wrong. There's nothing I can do about it, since I took the standard advice on HN and used PostgreSQL as my database.

Meanwhile if you'd applied the same "learning it properly" standard to your ORM you would never have had the problem you described.

An alternative to learning SQL is to use a non relational database, that has more of a impedance match to your host language to start with. (Eg XTDB or another Datalog DB in case of Clojure)

I'm pretty proficient with SQL, and I completely agree that you need to know it well if you use a relational DB for something that isn't trivial, but I still love using ActiveRecord. In most web applications I worked on I needed to manually optimise some queries, either massaging AR a bit or directly writing SQL when necessary, but I would say that's much less than 1% of all interactions with the DB. In all the other situations, using AR saved me a lot of time.

ORMs are great for avoiding boilerplate and adding some typesafety. But once you start doing complicated things like JOINs, it's better to drop into raw SQL.

> Listen to this old man's advise: learn SQL properly. It's not that hard.

I couldn't agree more with the first half, or disagree more with the second :) Once you're operating at scale, it takes a lot of fine-tuning, know-how, experimentation, and reading docs to get things moving efficiently.

What are some examples of fine-tuning or trial-and-error you might need at scale?

Postgres has a ton of parameters that can greatly affect performance. I've used https://pgtune.leopard.in.ua/ to help w/ that.

In terms of schemas, where you put your indexes matters a TON. Indexes make reads faster and writes slower (to oversimplify).

Figuring out where to break out a separate table vs keep everything in one table is also a common issue.

True, all of these would still be an issue with ORMs.

Honestly, ORM is an unnecessary layer most of the time.

You can have database schemas, and you can have software schemas, and they don't necessarily need to coincide as long as your database schema allows efficient queries. The rest of your relational-manager-logic can be embedded in the post-processing methods you apply to the query results.

What do you think of "micro ORMs" like knex.js, or Dapper for C#? I personally agree with you and my personal projects just use stored procedures, but these micro orms have always piqued my interest since they're just a SQL abstraction for building single queries.

I would consider those "query builders" rather than fully fledged ORMs.

Yeah no. A micro ORM still expects you to write the full, raw SQL.

The raison d'etre for a micro ORM is to capture the results of that SQL query into a simple, flat collection of objects in a way that has a better UX than your framework's default behavior.

Micro ORMs are the perfect middle ground for debates like these.

This is typically as far as I go with an ORM, CRUD operations on single tables that give you some type safety at compile time. If you need more complex data shapes I usually create a view or occasional stored procedure but that's about it. I use Entity Framework in my day job and do like the migrations though...

I personally like those. They are qualitatively different from ORMs. They give you three things:

- sensible ways of composing queries beyond direct string concatenation

- safe parametrization

- return results into usable data structures (not objects)

None of these things carry the problems of ORMs. They don’t impose a paradigm that isn’t SQL. They simply let you interface with SQL in an ergonomic manner.

These days I build applications that actually use SQL.

Typically a single statement to get the job done for any query.

who you callin old man i'm still in my 20s!

One foot in the grave. Practically a fossil.

Reply on news.ycombinator.com