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

261–270 of 305 posts

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

#261

Post by lostcolony »

fishtoaster wrote: post #31
maxxxxx wrote: post #3

I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why…

I completely agree. 90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find users = User.where(has_foo: true).limit(10) to be a lot more readable than rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10") users = rows.map { |row| User.build(row) } (And that's an example with no user-provided input) Likewise, any app of sufficien…

Read and understand, yes, the first is slightly simpler. Modify? Now I want to add an 'and' to the where clause. What's easier to figure out how to modify, for a random developer? I'd contend the latter, and that that is true for any modification you have to make.

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

#262

Post by coldtea »

sangnoir wrote: post #242
toast0 wrote: post #121

Earlier quoted context omitted.

> 2. If you're not using an ORM, then you ultimately end up writing one. And doing a far worse job than the people who focus on that for a living. It's no different from people who "don't need a web framework", and then go on to re-implement half of Rails or Spring (without accounting for any CSRF protection). Learning any framework at a professional level is a serious time investment, and many student beginners or q…

> It's way less effort to just output some HTML, or just make a database query, than to learn a framework or ORM, and as a bonus it's usually faster too. Also, it's a lot easier to fix slow queries when there's one place that has the whole query (with placeholders where possible -- I may be crazy, but I'm not insane). Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some…

>Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some point in time? If not, then by all means, go ahead.

Did anything of what the parent wrote sounded like it was worse for teams?

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

#263

Post by pwilsony »

"quasi-professional cowboy" here. I have used both ORM and direct SQL systems with the problems outlined above. I finally gave up and wrote my own row mapper:

1. Using the DB Schema, generate stored procedures to load and save data together with code/validation in partial C# classes.

2. Transport data in XML format using single letter (or two) table/element and column/attribute names (auto generated). Use .Net to automatically build/ serialize the XML into objects. SQL Server/.Net have some nice XML features that make this simple.

This means that the only user access to the database is through authorized stored procedures. The users have no access to database views or tables.

I have implemented the means to load and save arbitrary hierarchical objects (e.g. an Order/Order Lines). A single request to the database can return a complex three level object which would otherwise take hundreds to round trips to load.

I agree with the observation that this would be hell to maintain. However, the people likely to maintain this system would be in just a different hell if they had to work with Hibernate or MS Entity Data Model.

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

#264

Post by peschkaj »

btilly wrote: post #185
peschkaj wrote: post #169

Earlier quoted context omitted.

I've yet to see empirical proof that stored procedures don't scale well. If you write an application in your SQL dialect of choice, that likely won't scale well, but putting data access behind an API should scale well no matter if that API is in Rails, Spring, or a stored procedure.

I don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way. The natural bottleneck for any syst…

I think we're in agreement writing applications in the database isn't a good idea.

By "empirical proof", I mean that I have never seen anyone conclusively prove that switching data access from stored procedures to ad hoc SQL through an application was the cause of performance improvement. I've seen proof that removing complex application logic from the database fixed a problem, or that better indexing improved performance, but I've never seen anyone prove that removing stored procedures alone fixed the problem.

Further, there's nothing in your proposed solution of scaling out with readable replicas that precludes the use of stored procedures for data access. As a huge fan of both databases and separation of concerns, stored procedures make tremendous sense. They let a domain expert tune the database as needed. Even going so far as allowing that expert to re-write queries, provide optimizer hints, or even change the physical data model for better performance without ever changing application code.

Although I may or may not be competent, I have scaled "stuff" in a database. But I do appreciate you getting a vague ad hominem into the first sentence. Bravo.

The typical bottlenecks that I found were almost always IO - either through crappy storage, crappy indexing, or some combination of the two. Modern databases typically aren't bottlenecked by the lock manager.

I'd also agree that moving work out to a NoSQL database is particularly tricky. For three years I maintained the .NET Riak client and helped developers make better decisions when they were considering moving away from an RDMBS.

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

#265

Post by kedean »

Udik wrote: post #224
spacecowboy_lon wrote: post #208

Earlier quoted context omitted.

That is normally considered a good thing other wise different applications accessing the same data have to roll there own - do you really want multiple versions of biz logic. eg a large organizations like a bit telco may have multiple applications that update customer records.

Should they do that through a single service api instead of directly hitting the database?

Yeah, the proper way to be doing that is via a middleware api that can be maintained and versioned by it's own team, rather than coupling all these applications to a very specific database. If you have 4 apps talking to the same oracle database, and you decide to ditch the license, that's going to be way harder to deal with than if you just had one api being maintained by a team who's job is to know databases.

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

#266

Post by cptskippy »

lmm wrote: post #203
bunderbunder wrote: post #172

Earlier quoted context omitted.

I've been at companies that did use ORMs, and still ended up with that kind of mess. No tool is ever a substitute for discipline.

> No tool is ever a substitute for discipline. My experience is exactly the opposite. Humans are too fallible for discipline to ever be reliable. If you want something done, automate it.

In my experience you're both right.

The enterprise wants everyone to be a jack of all trades and to understand everything. Part of the reason for the massive layering that takes place is so that things are compartmentalized and easier for an individual to pickup and put down as they're shuffled around from project to project. Standardization, documentation, consistency, and discipline are essential for enterprise developers to be able to quickly grok a project and make the necessary changes.

Automation also plays an important role because it allows less skilled and knowledgeable individuals of lower pay grades monitor the blinky lights and only call on the big boys when things fail.

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

#267

Post by rkuykendall-com »

coldtea wrote: post #262
sangnoir wrote: post #242

Earlier quoted context omitted.

> It's way less effort to just output some HTML, or just make a database query, than to learn a framework or ORM, and as a bonus it's usually faster too. Also, it's a lot easier to fix slow queries when there's one place that has the whole query (with placeholders where possible -- I may be crazy, but I'm not insane). Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some…

> Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some point in time? If not, then by all means, go ahead. Did anything of what the parent wrote sounded like it was worse for teams?

Because toast0 knows what they wrote, but a team member wouldn't. So instead of googling "Flask add cookie to response" you have to start digging through all of toast0's code to see if they even have a way.

At least that's why I would consider working on a team like that much worse.

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

#268

Post by nommm-nommm »

EToS wrote: post #14

I love ORMs, they help abstract the database technology which has been really useful in a recent project switching an app from MSSQL to PostgreSQL. I also personally as developer appreciate the programmatic syntax. I would caveat the performance issue, sometimes there is no better way, however i would always try to make things work in the ORM first before switching into native SQL to get the job done.

> they help abstract the database technology

If you are going to do anything even remotely complex you're going to need to know that database technology otherwise your ORM is going to spit out queries that are dead stupid and a performance nightmare (pages taking 1 full minute to load). Then you end up fighting with the ORM to do what you want.

That's my experience.

I guess there are projects that are so simple the database layer can be abstracted away but I've never worked on one.

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

#269

Post by ZanyProgrammer »

intrasight wrote: post #146
ZanyProgrammer wrote: post #131

Earlier quoted context omitted.

As a C# dev myself, fuck stored procedures, to be quite honest.

Please elaborate

1.They are a pain in the ass to keep track of in version control. 2. They are hard to debug, especially when lots of business logic gets dumped to them

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

#270

Post by coldtea »

rkuykendall-com wrote: post #267
coldtea wrote: post #262

Earlier quoted context omitted.

> Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some point in time? If not, then by all means, go ahead. Did anything of what the parent wrote sounded like it was worse for teams?

Because toast0 knows what they wrote, but a team member wouldn't. So instead of googling "Flask add cookie to response" you have to start digging through all of toast0's code to see if they even have a way. At least that's why I would consider working on a team like that much worse.

>Because toast0 knows what they wrote, but a team member wouldn't.

They wouldn't know vanilla SQL, HTML, WSGL, etc, --decade old standards-- etc, but they would know some random framework like Flask?

Post reply on HN