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

111–120 of 305 posts

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

#111

Post by dhd415 »

craigvn wrote: post #98
PeCaN wrote: post #47

Earlier quoted context omitted.

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…

But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.

It's overly reductive to consider any code that interacts with a database an ORM. Broadly speaking, the big claims ORMs have made are 1) the ability to automatically map between an object model and a relational data model and 2) portability between database vendors. #1 falls apart when the object model becomes complex enough to suffer from object-relational impedance mismatch. #2 may work for smaller or simpler applications, but you're generally going to have to take advantage of vendor-specific database features to achieve optimal performance with applications that have anything more than trivial scale or performance requirements. Going with stored procedures generally means giving up on both #1 and #2 in exchange for a more flexible design (relational model can be very different from your object model) and higher performance (at the cost of having to hand-write all your own SQL, of course).

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

#112

Post by rhizome »

dkarapetyan wrote: post #89
rhizome wrote: post #62

Earlier quoted context omitted.

It strikes me as a thick/thin client tug of war. You can supplant ORMs by e.g. moving your logic into stored procedures on the db side, until you find an inconvenience there, then back and forth until the end of time. As with many dyadic architectural choices, there are good arguments on both sides.

I agree. The problem is that striking that balance is hard. If you go all-in with an ORM then trying to backpedal and figure out at which point you went wrong becomes almost impossible and so now you have a tangled web of ORM queries and no clear path to untangling things.

Sure, but the balance is entirely context-dependent. But hey, I guess this is where all polemics fall down.

That said, where is the academic problem in refactoring into the db and decomposing ORM queries into straight SQL where necessary/desired? I'm not a Fortune 500 Systems Architect, but I'm under the impression that this is a fairly straightforward process. Now, management of the systems will necessarily incur extra complexity, but that's a second order effect that should be assessed in the decision process as part of the cost estimations. Extra technical debt, for sure.

So, choosing one is technically cheaper and more simple and maintainable. Choosing multiple may be faster (or, in many cases, "academically satisfying"), but more complex with a steeper learning curve. Be sure to hire the best of the best!

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

#113

Post by dhd415 »

Animats wrote: post #72
StevePerkins wrote: post #34

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…

Marshalling support is useful. SQL result -> struct and struct -> SQL insert code is repetitive to write. Getting fancier than that may be overkill. Marshalling in general needs more compile-time support. Kludges such as Google protocol buffer preprocessors are a fast but clunky way to do it. It would be useful if languages could be given a reference to an SQL CREATE TABLE and could use that information usefully. Fie…

This is the sweet spot of ORMs. A team lead early in my career wrote a tool that read the database schema and generated all the marshaling code at the application layer. We got a degree of type safety and relief from a lot of boilerplate code but no promises of automatically generating SQL or abstracting away the database schema, etc. It worked so well that I wrote one from scratch in another language a decade later for another database-backed application.

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

#114

Post by PavlovsCat »

prodigal_erik wrote: post #21
PavlovsCat wrote: post #16

Earlier quoted context omitted.

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "don't", at least not without telling me how to achieve what I need the proper way :) For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search resu…

Queries with and without user_id probably have different plans (e.g., table scan vs. index lookup) and need to be prepared separately. I've never seen an ORM that takes prepared statements and query planning seriously, generally they only emit trivial queries.

Since the queries only get re-used per connection IIRC, that's moot for my case (personal derpy CMS) anyway, since they all just get used once per request, anything loopish is admin-related and fast enough anyway.

What really bugged me though was the idea of having to repeat the decision logic that determines what the query ends up being twice... though I just realized that putting name/value pairs into an array while building the query, and then using that when the query is built to bind them to it, is probably fine.

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

#115

Post by Udik »

platz wrote: post #76
sopooneo wrote: post #70

Earlier quoted context omitted.

I myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.

What is the source of the wariness?

Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also deeply hate ORMs, I much prefer to use thinner layer to interface with a database (query builders and row mappers).

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

#116

Post by gozur88 »

teacup50 wrote: post #65
StevePerkins wrote: post #34

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…

> If you're not using an ORM, then you ultimately end up writing one. Only if you assume that Object Relational Mapping is the only way to express type checked database access at the application layer. ORMs are broken by design; there's no lossless bridge between relational theory and OO -- only inconsistent, error-prone, complex approximations. Instead of bringing relational theory to the programming language, ORMs…

This. Ye Gods, this.

A project with nontrivial data relationships should start with the data model, not with a code model superimposed on data. ORM leads to bad data models and performance problems that are unfixable.

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

#117

Post by platz »

Udik wrote: post #115
platz wrote: post #76

Earlier quoted context omitted.

What is the source of the wariness?

Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also…

I also do think putting too much logic in stored procs is a danger, and was/is a scourge for some shops. But was curious if that is the primary objection.

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

#118

Post by PavlovsCat »

krapp wrote: post #33
PavlovsCat wrote: post #16

Earlier quoted context omitted.

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "don't", at least not without telling me how to achieve what I need the proper way :) For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search resu…

Just use separate statements. You should already know before that point whether or not the request is coming from a logged in user, so to me even the if statements are redundant - have functions or methods that just take parameters for and return the result set of a single SQL query, and figure out which function to call and how valid the results are elsewhere: function getResultsForUser($DB, $user_id) { $query="sear…

Deciding between user or not is the most simple case though. The most monstrous function I have by far considers things such as

    - is the user logged in, and maybe even owner of the profile we're at?
    - are we looking at all nodes, or the subnodes/subtree (don't ask) of a node?
    - do we want to display tags/authors/sources?
    - are we filtering by tag/author?
    - what's the display mode: list or full?
There might be more too, that's from the top of my head... Yes, it's messy, but also kina DRY. To "unroll" that would be even more monstrous, that can't be the way D:

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

#119

Post by PeCaN »

craigvn wrote: post #98
PeCaN wrote: post #47

Earlier quoted context omitted.

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…

But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.

> If you write some code, then you have just written an ORM.

What? No, I've written code to query a database. Not an Object-Relational Mapper.

  my $q = $db->prepare("get_my_stuff_by_name(?);");
  $q->execute($name);
or with fancy metaprogramming:

  use DBMagicStuff 'postgres://…';
  get_my_stuff_by_name($name);
That's not really an ORM.

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

#120

Post by PeCaN »

Udik wrote: post #115
platz wrote: post #76

Earlier quoted context omitted.

What is the source of the wariness?

Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also…

It's fat models vs thin models, just with fat database vs thin database. You have to be careful.
Post reply on HN