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.