The query DSL for ORMs is fine, works well in a large majority of the situations. When it gets problematic, you can almost always drop down to pure SQL.

The mapping part of the ORM on the other hand is a disaster. Table rows make poor OO classes. The best OO classes are "workers" that have some concrete task at hand. "Active record" style classes have no scope whatsoever - anything connected to the data can be potentially added to the model class.

So on projects with ORMs my main problem is dealing with the responsibility spaghetti. Typically one class which is the central point of the domain grows to crazy proportions. In a document-related software for example, you can potentially put everything in a "Document" entity class. "DocumentConverter" on the other hand has clear responsibility scope - to convert documents - but of course, a converter doesn't have a database table.

Plain-old-data DB record types (and their respective owner modules) are to ORMs, as code-gen'ed wire record types (and their respective owner modules) are to ProtoBuf-like wire-format codec libraries. They're the containers you put data into and take it out of, to ensure it's in the right types when being sent to, or received from, your DB. No more than that.

And, corollary to this idea: the POD record-type MyDB.FooTable shouldn't be taken as a type to use to stuff in the results from any random query you run against the DB table `foo`. It should be taken to represent the specific row type that the DB table `foo` has—the one you get when you `SELECT * from foo`. If you do an SQL query, and the result of that SQL query has fewer or extra or differently-typed columns, then the resulting row-set will have a different record type, and so you should have a separate POD data-type that does match these records. (Example: if you join two tables with a query, you don't need the ORM to spit out two records objects with a fancy OO-style relationship between them; you just need one record type that represents the resulting joined rows, without any particular idea that it happens to be an amalgamation of data from two tables.)

For an example of this approach to ORM, see Elixir's https://github.com/elixir-ecto/ecto.

P.S. I bet you would never guess which typed language with great support for records makes it easy (for the most part) to build most type-safe SQL queries on the fly, even with projections, without explicitly defining types for every possible projection variation.

Somewhere in one of his talks, Rich Hickey goes on this great mini-rant about, "Why can't we just let data be data?" I wish I could remember exactly which one it is. Maybe "Simple Made Easy"?

...like, say, Hibernate.

I have a reasonably mature Hibernate app. It is almost exactly as easy to type createNativeQuery() as it is to type createQuery(). Many simple queries are shorter in JPQL, but for anything complex I always use SQL. I never feel like Hibernate is keeping me apart from the database.

I genuinely think most people who bash ORMs just haven't gotten far enough in their SQL learning curve that they get tired of writing the same dumb I/O code over and over. ORMs never "freed you from SQL", they just freed you from boilerplate. Even venerable and much-maligned ORMs like Hibernate.

If all one is doing is calling createNativeQuery() all the time, and having stored procedures as well, then it is just needless fat.

I do not understand your point. Most business apps are not trivial.

It was an ETL stack for distributed networking monitoring, and data reporting.

Using SQL with JDBC isn't a synonym for bare bones string concatenation.

Offload complex queries to external files, or better yet, stored procedures.

There is another small dsl for bootstrap data, and I just check in all stored procs like normal code.

What this doesn't give you is good change column type semantics but any other tool I've ever used when this happens, can't be trusted anyway you have manage it yourself. This type of change is pretty infrequent though.

For me it works better to assert the schema I want now. Drop columns arn't done due to similar thorny issues that usually a human has to deal with. Or if its simple I put those in a cleanup proc that runs as the last part of the script.

Using normal code to describe structure I guess is similar its just you're fighting the language with meta data extensions, and also whatever you do isn't language agnostic. You're also at the mercy of whatever magic is going to happen when you change schema with that lib.

Automatic migrations I like less. In particular, Entity Framework and Entity Framework Core migrations.

Firstly, it seems that the second you stray from the most basic of scenarios, thry generate incorrect DDL.

Secondly, I'm just not a fan of 'magic' code gen.

Thirdly, every entity/model change a dev makes does not need to equate to a schema migration - that's how you end up quickly accumulating hundreds of schema migrations.

When I have to use MS SQL Server, the approach I favour is to use CQRS, using EF Core for the majority of cases, then using Dapper when I need more control. And for migrations I like DbUp, because it provides me with full control over when it's time to create a migration file, and what better DSL for migrations than... SQL.

I guess I'm making it sound more complicated than it really is then, because this approach is actually a lot simpler than the 'N-tier' abstraction-hell of yesteryear.

Jimmy Bogard has a simple example here: https://github.com/jbogard/ContosoUniversityDotNetCore

Some code can probably explain it better than me ;)

Not to mention refactoring issues. If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL.

Seems a textbook case of using a view for sharing query logic.

> If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL.

If you can do it in C# instead of SQL, you must be talking about a change in the interface used by an app (so, with good DB design, a view, not a base table). But this is very safe in SQL, you are changing a view definition and, if its a read/write view, insert and update triggers.

Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update.

You can't combine views easily, you can't cache results, you don't get static type checking.

Views are not "unmaintainable".

> restricted to SQL structures,

I'm not sure how that's a problem, since SQL is a fully-general data language.

> require absurd hacks or custom dlls,

Views require neither hacks nor "custom DLLs" in any RDBMS I am aware of. Can you provide a concrete example?

> not properly source controlled,

There's no reason DB schemas (including, but not limited to, view definitions) can't be source controlled.

> You can't combine views easily

Yes you can, whether by "combine" you mean as joined source relations, or as criteria that need to be combined to slice and dice the same source data. That's, like, playing right to the strength of SQL and RDBMS systems.

> you can't cache results,

Well, not generally incrementally or in something like a recently-used cache; a number of RDBMS's do support materialized views, though.

> restricted to SQL structures

So is your ORM; it just adds a layer of abstraction

> require absurd hacks or custom dlls

Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs"

> not properly source controlled

Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you?

> difficult to perform performance analysis on

I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes

> hard to update

It's just SQL, and for views typically rather basic SQL at that.

I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.

Views can be a problem from a performance side, Oracle has materialized views but MS SQL lags behind (I think Postgre has them too).

<cough>Views</cough>

Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity.

Keeping the logic in one place is better than the alternative. And of the two, the tools to manage source control are better.

Just like concepts like inheritance can be a burden when overused, views can be overused as well. Like inheritance, having views go too many levels deep generally winds up causing more harm than good.

Used correctly though, views are able to capture and centralize concepts in a way that are reusable, can be more easily tuneable, and make maintenance easier in the long run. Queries can be simpler and encompass less responsibility as the common logic is implemented by the view, and the logic that is specific to that query is more readily apparent.

IMHO thinking of the DB as just a collection of tables misses out on a key db feature.

>Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity.

I disagree that there's no good solution, but I understand that it's not as "out of the box" as many ORMs offer. That being said, writing SQL scripts that coordinate schema changes is not hard, and any CI/CD pipeline that can run commandline utilities can run a sql script against a database.

At my work, we use DbUp (https://dbup.github.io/) and are able to easily write small, isolated SQL scripts that correspond with code changes and get committed to source control, run all of those scripts with our CI/CD pipeline across multiple sharded databases, and do so quite seamlessly. We don't have any questions about migrations or what a tool did, as running the SQL script individually is the same as it runs on deployment. Coordinating db changes on developer machines is a breeze as well.

>And of the two, the tools to manage source control are better.

All of our sql scripts are committed into source control. It's not any easier or harder than looking at a code file, an html file, or a config file to get to the logic your working with.

XML is the default on the page, but wow the SQL is _so_ much shorter and more readable than the others! To the extent that I wonder what's the point, the obvious is machine readabaility, but one not three, and is SQL really so machine-unreadable that it's worth the extra depth and complexity?

I write SQL almost daily, but for the things that can easily be expressed with Liquibase's operations, I can write them faster and with fewer errors in XML, so long as I'm using a text editor with XML schema support (hence: autocompletion and validation as-you-type). PyCharm works well for this.

  1. Embrace SQL
  2. Handle the ON clauses for JOINs
  3. Fetch relations of sets of records (eager or lazy at the call site)
     in batches without making N+1 queries
  4. (bonus) async composability

Edit (you might want the link): https://github.com/karmakaze/safeql

It's lightweight when you want it to be, but if you want to do some heavier/generic use...it can take you quite far.

A lot of ORMs are built in layers so you can use the foundational bits or the higher level bits at your discretion. Which is why I don't understand the binary argument. "It depends", but a lot of ORMs span the entire "It depends" spectrum..

1. http://jdbi.org

Possibly still hibernate for the really advanced crazy stuff, too, though this is more doubtful. I've had pretty good success in the past with Hibernate Geospatial and full text search, for example.

In between those two extremes, just use SQL. It can still be mapped pretty easily with Hibernate and isn't practically harder than SQL would be without Hibernate.

For the few that actually read the manual, there's strongly typed Criteria queries that have the full power of HQL. Which is basically DB agnostic SQL with a few really advanced features removed. There's also lazy loaded collections, caching, change auditing, HBM2DDL validation, mappers that can transparently encrypt/decrypt, calculated columns (calculated by Java code, not SQL), and automatic versioning. All database agnostic.

And with the right settings, it's blazing fast. Maybe only 30% slower than native SQL.

We use all MetaModel strongly typed Criteria queries and it's given us the Holy Grail. Any table or column type or name we change is a compiler error until all the queries are fixed. This has allowed us to rewrite large parts of the schema with confidence that our queries won't break. The ultimate bane of working in SQL without an ORM

I've also run into a lot of people that loved it at first and then hated it when it first went into production because they didn't read the manual and didn't pay attention to what kind of queries they were actually generating.

I get the feeling that this is why there is often a lot of angst against it from the operations folks.

Hibernate is a microcosm of Java. If you read the manual things are fine, but it's not designed to be "wingable". It abstracts away enough of the DB that you need to understand the magic or you'll make massive blunders that haunt you later.

Given that maybe 10% of devs read the manual for anything after leaving school there's a lot of Java and Hibernate haters

Turn on the MetaModel code generation stuff. Use Criteria with strongly typed queries for everything that's not a simple find. Much less potential mistakes in combination with validation.

Always turn on HBM2DDL validation! Without this you won't know if there's a problem between entity->table mappings until a query explodes.

For the same reason, don't use HQL or Raw DB queries. You lose training wheels Hibernate is meant to give. In the same vein always use MetaModel strongly typed Criteria .get() syntax. Column/table name strings are evil, you lose type safety.

Turn on collection lazy loading. Otherwise N+1 queries will be everywhere. Beware future detatch() advice when doing this, you will need to trigger the load before detaching if you want that collection to be there.

Use a JDBC proxy to monitor for slow transactions and pretty print generated SQL if needed.

Be very careful with Hibernate lifecycle. It normally returns object proxies, the objects it returns to you are not of your actual class. They have magic in them that can explode if you continue to pass the POJO around after the transaction is complete or session flushed. Rule of thumb, detatch any entity you're passing to a different method.

Again with lifecycle... Be aware that asking for the same object twice will not give you a copy. It will give you the same object! Reference and all! This is another reason to detatch. You don't want people modifying POJO's built by Hibernate, they're magically linked to actual DB data.

Use Identity Type = Sequence. It's less dumb than normal auto-increment PK

Never use your entity classes directly in REST endpoints. You're in for a world of pain when refacgoring DB columns when it also changes your endpoints. Use something like MapStruct to map your entities to DTO easily.

Use Lombok to greatly reduce the POJO insanity of entity classes.

Be very very careful with session.clear() . Unless you 110% understand it, don't use it.

Be careful when trying to serialize hibernate entities. If you have a circular reference between two entities (bidirectional parent child for instance), Jackson and GSON will recurse infinitely. To prevent this, use their annotations to prevent serializing in the direction that makes less sense. Mapping to MapStruct DTO's before REST will allow you to directly prevent this.

For things like PK, use primitive types to prevent hibernate from inserting nulls. This is kinda a hack, but nice if 0 is an acceptable default value which it often is.

Enable code generation style dirty checking. Otherwise, for checking whether child collections and such have been altered hibernate will call .equal(), which nobody ever implements correctly (or at all). Better to use code generation dirty checking mode so hibernate can use magic to check fields for you.

Be aware that hibernate doesn't care if FK's or indexes exist. If you want things to run at realistic speeds, you still need to make these yourself.

We managed to make our entire app, with about 110 REST endpoints, entirely strongly typed Criteria queries. Not a single line that isn't DB agnostic or bypasses hibernate. Trust me, things are much better this way. Once you cross the barrier nothing is certain.

There's more, I should probably write a blog post instead of novels on HN

Thanks for sharing

In javascript land there is PureORM which seeks this balance (https://github.com/craigmichaelmartin/pure-orm)

> Dapper's simplicity means that many feature that ORMs ship with are stripped out. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn't attempt to solve every problem.

so basically stack overflow used a ORM for years. and i think they still use LINQ to SQL in coexistence with dapper.

Not that a little detail like that would stop someone from complaining about something that they don't put in the effort to learn.

I'm all for SQL, but if the project's set in its ORM then it doesn't make sense to do ORM.raw(...) or whatever out of preference alone.