Hacker News Reader

Comment by valenterry | Hacker News Reader

Stop using ORMs already. We now have at least a decade of experience with them and found out that they are a suboptimal solution to the problem.

Just use JOOQ or any other kind of library that removes the boilerplate and is 100x more maintainable and productive.

View on HN

Stop using SQL already. We now have at least 4 decades of experience with them and found out that they are a suboptimal solution to the problem.

Just use an ORM or any other kind of library that removes the boilerplate and is 100x more maintainable and productive.

See how useful it is to not explain anything.

I've never seen an orm work 100% correctly though, with indexes/pagination/etc.

Far easier to write correct sql than to convince the orm how to form correct sql.

ORMs seem amazing up until the inevitable point of hitting a performance issue or bug, and having to print the underlying statement. At that point you are no longer fighting with the bug impacting your systems, you are fighting with the ORM midddle-ware it self, and having to quickly improve your SQL competency above everything else. That is the moment you 've lost all the benefits advertised.

For smaller systems it might be ok I guess, but stay alive long enough and you 'll see that sooner or later every small system desires to grow, grow and grow!

the way to debug performance on a large sql system is to log things on the sql server itself and monitor for weird queries. Tjis is part of monitoring a db and this has to be done no matter which tech you used to generate those queries.

good ORMs all have a way to fine-control the underlying statements to avoid querying individual rows one by one.

I'm fairly proficient with sql queries and i still don't like writing sql queries and binding to objects manually

> That is the moment you 've lost all the benefits advertised.

No, that makes no sense.

If you have to investigate 2 places where performance issues have cropped up and hand-write the SQL in those places, while 95% of your application works just fine, how did you lose all the benefits?

My SQL competency was fine from the beginning, thank you very much.

My post is provocative and not explaining, true.

However, your comparison does not make sense. JOOQ and similar libraries don't make you write SQL, even if their API is closer to SQL than what most ORMs do.

If you replace "ORM" in your example with NoSQL or some other technology that directly competes with SQL then it would make sense.

It might be just my experience but when using ORMs I just constantly think of how to write a SQL query in my head using the ORMs syntax. As a result I’m usually using ORMs for schema maintenance and inserts. For getting the data out of the DB SQL is a simple way to go.

Just my experience so far.

1. at least 20 years

2. ORMs work just fine in lots of applications. There’s no real need to replace them.

3. JOOQ does not remove enough boilerplate and it’s not 100x more maintainable.

I think this discussion here is good. Whether it be an ORM or something like JOOQ it is always a tradeoff. I've found that the main pain point for me with these technologies is, that in any relatively complex project, you might want to query a similar but not identical thing in different ways. For either performance or business logic reasons. So lets say you have a POJO (in the Java world), that has a bunch of annotations on it. Once you need 3 different ways to query the same thing in different contexts, that breaks down. It also hides the database layer and structure from you, that you are in fact dealing with tables, not a hierarchy of objects, which can be misleading (especially to less experienced developers). We ourselves have gone the following route: All SQL is written as SQL (We use MyBatis for interfacing with that). And the only real features we use, are mapping and flow control constructs/loops for batch data. It is a lot more writing in that sense, but it also means that the query you write is the exact same one that gets executed. As for writing some database agnostic SQL, that can be executed on all of the big 3 (Oracle, Postgres, MySQL) - I have literally never seen anyone need that in practise. It also prevents you from optimizing to database specific features (in case of postgres, CTE is a good example).

Both table-centric ORMs like JPA and query-centric Query Builders like JOOQ are popular approaches to DBMS agnostic data access. ObjectiveSQL’s main differentiator compared to JPA is a JOOQ-like fluent query builder API instead of JPQL, as far as I can see after a quick scan of the project.

If your app does not have a requirement to be DBMS agnostic then DAO (Data Access Objects) frameworks like Spring JDBC or MyBATIS might meet your needs. If you don’t like using an Internal DSL and/or embedding SQL in your code, then a YeSQL framework like HugSQL might work for you.

For a strictly typed object orient language like Java, no single data access framework style has emerged as ideal. All have to address the same issues: integrating DDL/DML into source control, supporting different DBMSes, generating DAOs, and DDL migrations.

JOOQ doesn’t seem like it removes much boilerplate. It looks like it does a ton of code generation, and then on the other side you have an extremely clunky way of getting your data in a usable format.

I don’t really have a horse in this race (in Clojure there’s no need to map between results and idiomatic data structures in your domain, and you can add as much or as little schema/type checking as you want) but in previous lives doing OOP I have never lamented them 5% of problematic SQL queries to the point I’d throw away the rest that an ORM gives me. Just break out into a more powerful query language, hide it behind an interface and you still get to return meaningful objects in your domain.

I think JOOQ "Fluent API" is a kind of silly.

Just embed the SQL statement in a string.

In same way you would embed a snippet of HTML, XML or anything else.

(You can remove some of the boiler plate of a classic Java prepared statement using a library though ... nothing wrong in that.)

One of the advantages of just having your embedded SQL as a string is that you can copy it back and forth between a SQL console when you are developing.

Instead of something like this:

   Query query = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))
     .from(table("BOOK"))
     .join(table("AUTHOR"))
     .on(field("BOOK.AUTHOR_ID").eq(field("AUTHOR.ID")))
     .where(field("BOOK.PUBLISHED_IN").eq(1948));

Plus you may sit with a SQL dialect not supported by the "Fluent API".

If you don't like JOOQ and prefer to use bona fide SQL statements, JDBI [1] is a battle-tested OSS alternative that maps Java objects to statements.

[1] https://github.com/jdbi/jdbi

JDBI is awesome :)

> Just embed the SQL statement in a string.

This makes sense for simple software, but jOOQ generates DB specific SQL queries depending on your DB dialect. E.g. we have a big product which supports nearly all big databases and jOOQ abstracts away the DB specific stuff wonderfully.

Keeping it simple in general also makes sense for complex software ...

JOOQ's fluent API gives you safety since it is introspected from the actual DB structure. Invalid code does not compile, instead of blowing up in production at runtime as it would in the case of the SQL string.

Honest question: When you ditch ORMs, who or what controls the schema? Who executes migrations?

You and you.

You can use something like https://sqitch.org/ or https://flywaydb.org/ or https://www.liquibase.org/ or https://github.com/rust-db/refinery where ideally your migrations should be written in plain sql (or a language that can output plain sql so you can see what it does). Then it's a matter of running those sql queries and knowing which migrations already happened.

The problem is: what is the single source of truth (TM) for the expected DB schema? An ORM will (hopefully) manage a meta table with all the information. If I work on a collaborative project, I might rely on an outdated table structure. So ideally, the app reflects the DB schema in some type-safe manner.

There is no problem and the answer has already been giving: the migration library is the source of truth for the schema and that's it. There is just no ORM and if there is one it should not even have the permissions to execute any schema changes.

> So ideally, the app reflects the DB schema in some type-safe manner.

That's right! But there is no need to use an ORM for that. JooQ and other libraries (in my world it would be Quill https://getquill.io/) do that for you.

I generally like ORMs and use them often, but I still usually use a separate migration framework for writing and executing database migrations.

Two examples in .NET world: Roundhouse [0] and FluentMigrator [1].

[0] - https://github.com/chucknorris/roundhouse [1] - https://github.com/fluentmigrator/fluentmigrator

Reply on news.ycombinator.com