Using an ORM and escape hatching to raw SQL is pretty much industry standard practice these days and definitely better than no ORM imho. I have code that's basically a lot of

    result = orm.query({raw sql}, parameters)

It's as optimal as any other raw SQL query. Now that may make some people scream "why use an ORM at all then!!!" but in the meantime;

I have wonderful and trivially configurable db connection state management

I have the ability to do things really simply when i want to; i still can use the ORM magic for quick prototyping or when i know the query is actually trivial object fetching.

The result passing into an object that matches the result of the query is definitely nicer with a good ORM library than every raw SQL library i've used.

▲ RedShift1 11 hours ago | parent [-]

Every project I've come across that uses an ORM has terrible database design. All columns nullable, missing foreign key indexes, doing things in application code that could easily be done by triggers (fields like created, modified, ...), wrong datatypes (varchar(n) all over the place, just wwwhhhhyyy, floats for money, ...), using sentinel values (this one time, at bandcamp, I came across a datetime field that used a sentinel value and it only worked because of two datetime handling bugs (so two wrongs did make a right) and the server being in the UTC timezone), and the list goes on and on...

I think this happens because ORMs make you treat the database as a dumb datastore and hence the poor schema.

▲

AnotherGoodName 10 hours ago | parent [-]

Honestly database schema management doesn't scale particularly well under any framework and i've seen those issues start to crop up in every org once you have enough devs constantly changing the schema. It happens with ORMs and with raw SQL.

When that happens you really really should look into the much maligned no-sql alternatives. Similarly to the hatred ORMs get, no-sql data stores actually have some huge benefits. Especially at the point where db schema maintenance starts to break down. Ie. Who cares if someone adds a new field to the FB Newsfeed object in development when ultimately it's a key-value store fetched with graphQL queries? The only person it'll affect is the developer who added that field, no one else will even notice the new key value object unless they fetch it. There's no way to make SQL work at all at scale (scale in terms of number of devs messing with the schema) but a key-value store with graphQL works really well there.

Small orgs where you're the senior eng and can keep the schema in check on review? Use an ORM to a traditional db, escape hatch to raw SQL when needed, keep a close eye on any schema changes.

Big orgs where there's a tons of teams wanting to change things at high velocity? I have no idea how to make either SQL or ORMs work in these cases. I do know from experience how to make graphQL and a key-value store work well though and that's where the above issues happen in my experience. It's really not an ORM specific issue. I suggest going down the no-sql route in those cases.

	▲	RedShift1 6 hours ago \| parent [-]
		NoSQL is even worse, data gets duplicated and then forgotten, so it doesn't get updated correctly, or somebody names a field "mail" and another person names it "email" and so on... There is zero guarantee that whatever you ask the database for contains anything valid, so your code gets littered with null and undefined checks, and if you ask for example a field "color" what is it going to contain? A hex value? rgb(), rgba(), integer? So you need to check that too. In my experience NoSQL is even worse, they are literally data dumps (as in garbage dump).