Also, the ORM allows me to specify models that are not only used for structuring the database, but also for validation of incoming JSON requests and easily serialize queries back to JSON.

The former almost invariably spurt out inefficient queries, or too many queries, or both. They usually require you to let the ORM generate tables. If you just want to have your object oriented design persist in a database, that's great.

The latter almost invariably results in trying to reinvent the SQL syntax in a quasi-language-native, quasi-database-agnostic way. They almost never manage to replicate more than a quarter of the power of real SQL, and in order to do anything non-trivial (or have things done in a way that lets your database server scale) they force you to become an expert SQL anyway, PLUS an expert in how your ORM translates its own syntax into SQL.

And once you become more expert at SQL than your ORM, it's not long before you find the ORM is a net loss to productivity—in particular by how it encourages you to write too much data manipulation logic in code rather than directly in the database.

All an ORM needs is a mapping between database fields and object properties so a good ORM should allow you to separately define a mapping between your object model and relational model so you retain full control of both.

> it encourages you to write too much data manipulation logic in code rather than directly in the database

I find doing too much business logic related data manipulation directly via SQL to be an anti-pattern that creates significant problems with testing and separation of concerns.

ORMs are good at hydrating objects and persisting updates to those objects. Hand writing code to do this is a waste of time.

SQL is good at running reports and performing mass updates an ORM that doesn't allow you to easily do this is bad.

My model of thinking is that any copy of data that isn't currently resting in the database is potentially stale; avoid round trips like the plague; get new data into the database as soon as possible.

For me and the way I work, it's less about good vs bad ORMs, rather more often a question of whether I even want my data hydrated into a special object at all. I've come to the realisation that for the kind of work I do, data objects almost always end up being an unnecessary layer of indirection that don't give me any real benefits—and they change the way you think, because every transform becomes an opportunity to write a method on an object and not a straightforward query.

Nothing about an ORM stops you from persisting data as soon as it is ready or updating the state from the DB to ensure consistency (or from using transactions).

> rather more often a question of whether I even want my data hydrated into a special object at all. I've come to the realisation that for the kind of work I do, data objects almost always end up being an unnecessary layer of indirection that don't give me any real benefits

Yeah, if you don't need to use objects than there is no reason to use an ORM. Knowing the right tool for the job is critical and Objects and ORMs are not infrequently used when they are not needed.

In my work, updates are rarely atomic and business logic is complicated and intricate. It is extremely hard know what data you will actually need and so it makes sense to pass around a complicated object that has all the potentially needed state. This also gives me the option to separate logic about when to commit/rollback from logic about what to persist.

> because every transform becomes an opportunity to write a method on an object and not a straightforward query.

For me, this is a plus, not a minus :). Methods are easier to test and re-use as part of a complicated business logic flows. They make it easier for me to manage when data gets synced with the DB without having to duplicate code.

> —and they change the way you think,

I am going to pay more attention to this and see where I may have made mistaken presumptions and used objects unnecessarily when I could use atomic updates or queries instead.

> I am going to pay more attention to this

Everyone thinks about things in their own way I suppose, but perhaps a way to parse it could be to think about whether you're approaching data from a "load and store" mentality or a "truth and snapshot" mentality.

In my mind, unless you wrap the entire programming round trip in a transaction, all data sitting in variables are a snapshot of the past and thus stale by definition.

Hibernate and JPA encourage designing your domain classes first and then generate the DDL from that.

> Also, the ORM allows me to specify models that are not only used for structuring the database, but also for validation of incoming JSON requests and easily serialize queries back to JSON.

Postgres has great JSON support, does the ORM something with JSON that Postgres cannot do?

This is a feature, not requirement or need of the ORM. It seems pretty silly to let the existence of a feature prevent you from making designing the structure of your DB correctly.

> does the ORM something with JSON that Postgres cannot do?

Postgres's json functionality is used for manipulating and querying data stored in the DB.

I believe the poster is talking about deserializing and validating json from REST requests and serializing json for REST responses using the mapping defined for the ORM.

These are also things that the json functionality of Postgres can do. For example, look at to_json and json_agg.

A couple other things I've learned:

* Never re-use complex types in both your API and your schema. These things evolve at different paces and you should never have to worry that a change to your schema will break an API (or vice-versa). The minimal extra typing to have dedicated API types is well worth it.

* Storing untrusted client-submitted JSON in your database is a terrible idea. This is a great attack surface, either by DOSing your system with large blobs or by guessing keys that might have meaning in the future.

This is not true. There's a culture of doing that in demos, but every production shop I've ever been in curates DDL by hand. Flyway is pretty popular.

  import json
  data_from_json_request = json.loads(*request body*)

  import pymysql
  import json

  conn = pymysql.connect(*connection details*, 
                          cursorclass = pymysql.cursors.DictCursor)

  cur = conn.cursor()
  cur.execute(*query*)
  json_query_result = json.dumps(cur.fetchall())

    @Path("/things/{thingId}/tags")
    public class ThingTagsResource {
        @PUT
        @Transactional
        public Thing setTags(final @PathParm("thingId") long thingId, final SortedSet<String> tags) {
            final Thing thing = dao().load(Thing.class, thingId);
            thing.setTags(tags);
            return thing;
        }
    }

It does, if you want it to, with several typecheckers available.

For example if I'm going to use a value in a query, because I'm using parameterized queries the type conversion to string happens implicitly so type doesn't actually matter. If I get 2 or '2' it all ends up as '2' and the database infers type by the column type.

If I need something to be a integer and I don't trust the upstream system then you have to:

  int(*number*)

On the other hand services that aren't actively utilizing data I write to be fairly agnostic about that data. "be conservative in what you do, be liberal in what you accept from others." is sort of how I aim.

My working principle is to have data spend as little time as possible being thrown around within application code. I tend to find that the longer data spends being sieved through layers and tossed around inside your application, the more data bugs you'll end up having.

And when it comes time to display data to the user, it's rarely inconvenient to write an SQL query that fetches exactly what you want to display in exactly the right format and exactly the right order—obviating the need to have any "objects" that "understand" your data model.

The problem is that far too few programmers realise how deep the SQL rabbit hole goes; it's treated like a little side-hustle like regular expressions, when for so many programmers it's the most valuable skill to level up.

... or both. Both is always a possibility. Welcome to programming.

... or both. Both is always a possibility. Welcome to databases.

I agree that most ORMs are shit, and if you want to make specific complaints, I'll probably agree with most of them.

But if the choice of ORM is forcing you to design your database to its limitations, you should really be asking yourself whether it's time to switch to a different ORM.

Thanks for the condescension though.

(As for the condescension, I agree with that too. It was aimed squarely at the GP in the marginal hope that he gets to experience his own tone mirrored back at himself. It might just offer him some insights into perspective.)

Of course, it's the internet, so dry british cynicism and condescension aren't as trivially distinguishable as one might hope. Sorry my tone didn't come across correctly.

Why does your database structure have to be structured by your ORM?

The only ORM that I have used are LINQ based ones and they can model any database relationship.

Don’t get me wrong, my first instinct when starting a project is to use Dapper - a Micro ORM written by Stack Overflow that just maps a sql query result to object and doesn’t generate sql.