Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What ORMs have taught me: just learn SQL (2014)

wozniak.ca

Post reply on HN
Print view View original

511–520 of 654 posts

Re: What ORMs have taught me: just learn SQL (2014)

#511

Post by rocho »

JesseAldridge wrote: post #227

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…

I have the opposite view. I find ORMs annoying and obscure, and I think they introduce duplicated code.

If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names.

This approach has a lot of advantages over ORMs: * you know exactly what gets executed * automatic DRY code * the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time

Schema definitions are in standalone SQL files, as well as my migrations.

The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.

Re: What ORMs have taught me: just learn SQL (2014)

#512

Post by chousuke »

I think that my primary issue with ORMs is that they often allow you to code as if the database doesn't exist, instead of explicitly structuring code to handle network and database failures at the points where you deal with external data.

It is a problem if potentially any part of your program can just fail, and that happens if you allow magic ORM-objects to leak onto code paths that aren't written to handle them.

Re: What ORMs have taught me: just learn SQL (2014)

#513

Post by smitty1e »

philwelch wrote: post #309
smitty1e wrote: post #307

> My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL. I feel the same way about Wiki syntax as ORMs. While SQLAlchemy is great for getting the DDL done, my SQL-fu is such that I wind up just using SQLAlchemy as a glorified connection manager while I construct the SQL strings directly rather than muddy up a sophisticated query by…

In terms of wiki syntax, HTML is just so damned noisy that I'm fine with Markdown--but mostly because Markdown is fairly standardized and I don't have to learn a totally different syntax for everything.

"Fairly"

So I spend as much time faffing about with how this particular tool does anchors as I would just doing the "a" tag outright in HTML.

Re: What ORMs have taught me: just learn SQL (2014)

#514

Post by burpsnard »

stickfigure wrote: post #208

Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.

When-i-were-a-lad, it went SQL -> views -> stored procedures -> ProC

Re: What ORMs have taught me: just learn SQL (2014)

#515

Post by dkersten »

wvenable wrote: post #61
geophile wrote: post #44

Earlier quoted context omitted.

ORMs make the simple things simple, and the complicated things impossible.

ORMs let you drop into SQL whenever you need, usually in a way that is fully compatible with the model, so that's entirely false.

In my personal experience, ORM’s seem to encourage queries to get scattered throughout your logic (they’re just normal code and function calls, afterall... at least, they look like it) and encourage mixing application-side logic with query logic. The former makes it incredibly hard to remove if you need to reach for raw SQL and the latter leads to bad performance due to many application-database roundtrips snd not filtering enough before sending data to the application.

Yes, both of these things can be solved through disciplined modularisation of ORM logic, but in my personal experience across multiple companies, most developers simply aren’t that disciplined and treat ORM code as any other application code, instead of treating it as the remotely executed database code that it actually is.

In my experience, writing raw SQL (through https://www.hugsql.org/ in my case), you are instead encouraged to think of them as separate and carefully consider the boundaries, which helps keep the queries and application logic modular and allows for more carefully crafted queries that minimise roundtrips and data shuffling.

Again, this has been my experience, across a number of companies. Perhaps your experience differs, in which case, I’m jealous.

Re: What ORMs have taught me: just learn SQL (2014)

#516

Post by dkersten »

Glyptodon wrote: post #150
daenz wrote: post #101

Earlier quoted context omitted.

Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQ…

I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.

Yeah, I’ve never seen any complex applications, using ORM’s, that were easy to port to another database. Hell, one company I was at switched from MySQL to MariaDB and even that took some work despite that they should be almost the dame thing. If you switch to a more substantially different database, eg, from MySQL to Postgres, then its even harder. I’m also a believer in using a databases features when it makes sense and not limiting myself to the standardised subset of SQL just in case I might want to change databases later.

Re: What ORMs have taught me: just learn SQL (2014)

#517

Post by aidos »

rocho wrote: post #511
JesseAldridge wrote: post #227

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…

I have the opposite view. I find ORMs annoying and obscure, and I think they introduce duplicated code. If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names. Th…

Your argument does not stand up. I can have a file full of ORM sql fragments the same as you file of strings. And I can compose mine together safely and more flexibly than strings.

Re: What ORMs have taught me: just learn SQL (2014)

#518

Post by SkyBelow »

rmilejczz wrote: post #299
dymk wrote: post #246

Earlier quoted context omitted.

But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives

An in house solution is almost always better than an external dependency

In house means more customized to the specific problem but with far less expertise in the general technology. I find the latter almost always outweighs the former when working at any cost center tech shop.

Re: What ORMs have taught me: just learn SQL (2014)

#519

Post by gwn7 »

> I’ve found that reflection techniques to get the data definition are not worth it and I succumb to managing the redundancy of data definitons in two places.

Not sure if much has changed regarding the "reflection techniques" since 2014, but I think Postgres does a fine job reflecting. (Don't know much about others)

For example my favorite library Massive.js (a data mapper) depends completely on reflection. It allows its users to access tables, views, functions, extensions, and even enum types from its Javascript API, without the need for models. This completely solved the data definition redundancy problem for me.

I even made a small layer on top of it to get the constraint information too using the information_schema, and everything is working like a charm.

Re: What ORMs have taught me: just learn SQL (2014)

#520

Post by xadoc »

Too wrote: post #473
noisem4ker wrote: post #259

Earlier quoted context omitted.

Something like jOOQ? https://www.jooq.org

That looks fantastic. No magic mumbo jumbo mapping, just a simple type safe sql. Both syntax safety (no need to remember which of WHERE and HAVING comes first) and type safety on all fields. It's not advertised in the examples on the front page but i also take for granted sql injections are completely impossible since all data goes into functions and are not string formatted, without the mess of having to remember th…

Dapper for C# made by StackOverflow team

https://github.com/StackExchange/Dapper

Post reply on HN