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)

woz.posthaven.com

Post reply on HN
Print view View original

281–290 of 360 posts

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

#281

Post by scarface74 »

zaphar wrote: post #273
mgkimsal wrote: post #238

Earlier quoted context omitted.

> And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place? The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.

"saved time" at the least optional time to save time. ORM's are a maintenance burden. They obfuscate DB performance behind usually an enormous API surface. Most software work is maintenance work. Optimizing for initial deployment is shortsighted.

What "enormous" API surface? It's not about "optimizing initial deployment". It's about "optimizing continuous deployment" and having "always releasable Software". I've worked for departments with 15 devs all working on the same codebase and we could release and rollback releases (A/B deployments) every week because we treated the database as a dumb data store. We had multiple branches at the same time, etc.

I've also worked at a companies where all of the logic was in ungodly stored procedures and getting anything released took months and we had a whole two weeks "hardening sprint" because we had to coordinate with the "database developers" and of course the entirety of the business logic was In stored procedures.

Most software being maintenance work is even more of a reason to optimize deployment. What good is unreleased software? The most important part of the business is releasing software. Most of my emphasis asan Architect is making sure we can release fast.

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

#282

Post by scarface74 »

zaphar wrote: post #274
scarface74 wrote: post #267

Earlier quoted context omitted.

I meant a good ORM. But then again, my definition of a good ORM is an ORM with a language that treats queries as a first class citizen. EF with Linq doesn't really act feel like a separate framework since Linq and Expressions are built into the language.

Those are more of a query framework than an ORM. If that's your bar then most ORMs are going to fail to meet it.

How is it a "query framework"? Linq works with objects and the Linq expression provider treats the Linq as data that translates the objects to Sql at runtime. That is by definition an Object Relational Mapping.

Of course most ORMs do fall short because most languages don't have the powerful concept of "code as data".

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

#283

Post by weberc2 »

mgkimsal wrote: post #238
zimpenfish wrote: post #229

Earlier quoted context omitted.

If you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?

> And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place? The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.

You haven't saved time compared to using a query builder though; in fact you've lost time due to the hit to readability and debugability.

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

#284

Post by zkomp »

scarface74 wrote: post #266
zkomp wrote: post #256

Earlier quoted context omitted.

I think bussiness logic in the database has prevented disasters in the projects I have worked on. I honestly dont see how it could have been solved better... It probably depends on the domain/problems. My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages... The web app or java worker should be allowed to focus in its problems,…

And what's stopping you from having a tightly controlled interface with a REST Api that is easily deployed, rolled back, unit tested, source controlled and deployed?

I like data. A database is created to handle it, give you tools to query, modify, scale, secure the data.

A rest api... how and why should it be responsible for your data? It solved a different problem.

You might not even need a database I guess, and then anything goes.

I need and like my database, and have suffered trying to get along with different ORMs. SQL is so good at what it is designed to do if you just let it.

(And why just one rest api? How about 100 restapis, some microservices, some web apps, some background workers, many different languages. One database. No ORM)

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

#285

Post by ZenoArrow »

spdionis wrote: post #246
ZenoArrow wrote: post #215

Earlier quoted context omitted.

> "Then you lose the ability to do unit testing without a database dependency" Not really, you just mock the database calls in the code you're unit testing.

I think GP meant that you can't/it's hard to test the stored procedures themselves. In this case if you mock the database calls you will not test the database logic.

You can unit test stored procedures. For example, with T-SQL can use the open source unit testing framework tSQLt:

http://tsqlt.org/

tSQLt is used by the commercial SQL Test product from Red Gate if you wanted a more polished user experience:

https://www.red-gate.com/products/sql-development/sql-test/i...

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

#286

Post by ZenoArrow »

scarface74 wrote: post #224
ZenoArrow wrote: post #215

Earlier quoted context omitted.

> "Then you lose the ability to do unit testing without a database dependency" Not really, you just mock the database calls in the code you're unit testing.

If all of your business logic is in the stored procedures, what are you actually testing? And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List - and still test your Linq queries.

> "If all of your business logic is in the stored procedures, what are you actually testing?"

Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.

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

#288

Post by GFischer »

wasted_intel wrote: post #25
djrobstep wrote: post #16

Earlier quoted context omitted.

I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.

Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.

Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.

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

#289

Post by bunderbunder »

manigandham wrote: post #252

One of the most endless and pointless debates. 99% of the time, an ORM is fantastic and will make you more productive while providing performance, security, and maintainability. They come in many sizes from thin wrappers around a db connection to full-featured frameworks. For the other 1%, use raw SQL, or perhaps a query building tool to help with parameterization, composability, etc. In fact, modern ORMs will even l…

All of these endless debates seem to boil down to two different groups who work in two different problem domains talking past each other.

I've personally never seen an ORM lead to success in the long run. But I also work in a space where queries frequently end up involving something that ORMs typically don't handle well: merge statements and pivot statements, window functions, management of the lock escalation policy to fine-tune performance, temp tables... The list is endless.

What I have not ever worked on is a relatively basic CRUD datastore. Which I realize is what most people are using databases for. So at this point, I'm putting my money on ORMs being a hole in one for that application. Because, otherwise, I just can't reconcile a statement like, "99% of the time, an ORM is fantastic" with the reality I'm living in. In my career, 100% of the time, when an ORM was present, it was invariably the single biggest piece of technical debt.

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

#290

Post by nottorp »

You can write more efficient C(++) if you know assembly, even if you don't write anything in assembly.

You can write more efficient python if you know C(++) and the various tradeoffs between data structures, even if you don't write any C++ and don't do any data structure by hand in the current project.

...

You can use an ORM more efficiently if you know SQL. Same thing isn't it?

That assumes the ORM doesn't completely get in the way, of course.

Post reply on HN