Hacker News Reader

Comment by rabidferret | Hacker News Reader

Stored procedures are no more composeable than SQL strings are. Now it's certainly easy to say that I haven't used SQL directly. But I did spend years working that way (though of course this statement shouldn't give you much confidence by itself).

Ultimately an ORM can help you to build more maintainable and resilient (to changing market conditions) software than direct SQL. Diesel in particular can catch mistakes that SQL strings cannot.

I do understand why a lot of people have reservations about ORMs. I share a lot of them. I've maintained Active Record for about 2 years now, and learned a lot from doing so (note: This does not mean that I like AR or want this to be anything like it).

TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)

View on HN

> Stored procedures are no more composeable than SQL strings are[...]Ultimately an ORM can help you to build more maintainable and resilient (to changing market conditions) software than direct SQL.

I couldn't disagree more. Creating the separation between SQL DB and web application allows you to maintain, deploy, fix and upgrade things independently and it's easy (maybe not quite as easy as an ORM would make it but not really harder either; you're going to have to know how SQL works anyway if you want to effectively optimize and secure your data).

Not only that but you can provide far better security controls because you can limit certain users access to certain procedures, never direct access to tables and various other things and if you don't do any dynamic SQL in the procedures itself you not only never have to worry about SQL injection but unless there is a critical vulnerability in the SQL system itself it's going to be impossible to conduct an injection attack.

Yeah you can separate your code to make it easier to deploy separately to get a similar benefit and yeah your code can prevent SQL injections too but it's so much more work, more possible points of failure and the differences between databases can really bite you in the ass when you do everything generically. SQL is fun :)

> TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)

Yeah I'm not saying NEVER use ORMs; they're great for creating things quickly that don't need to scale. But after my experience with them I'm highly skeptical using them outside of that type of use case. Of course you can scale ORMs but I've never seen it done well.

I think we're violently agreeing in a lot of ways. :)

I'm curious what you mean by scale. Are you referring to performance, or code size? If you're referring to performance, you'll probably find it interesting that we out-perform or are competitive with ideomatic C.

By scale I mean performance though I'm not sure the application's language matters as much as the SQL that's being generated and run against the database. I've just never had good luck with ORMs generating the best, most optimized SQL for the query I'm trying to run and I've known a few people who used an ORM until they started getting heavy traffic, tested out talking directly to the RDMS, found it to be more performant and end up dropping the ORM.

Don't get me wrong ORMs can certainly be performant. I just haven't seen or know anyone who used it in a manner that scaled to thousands of concurrent users and, at the same time, didn't have a lot of pain trying to do that with the ORM.

I think this conversation shows a mismatch between two different philosophies about how to use the database. The traditional way of doing things you've described involves multiple different applications connecting to the database. Data access is restricted by locking their database users down to specific APIs exposed by stored procedures. Non-trivial logic lives in the database in order to present these APIs.

A lot of systems lately are being built in a different way, that is, only one application connects to the database. This application presents an API (ReST, protobufs, thrift) for other apps that need that data. It handles access control and business logic, using the DB as a backing store. That means you don't have to worry about dababase user access control, because you don't have more than one user.

I don't have a strong opinion about either of these, but the latter way of working is how we do things at my current job (reasonably large payments software company), and it does work quite well. One advantage I appreciate is that there isn't arbitrary business logic sitting in the database where it can be changed on the fly - I wouldn't consider that a feature.

I'll make a stronger statement - stored procedures are generally not composable in production.

At least in Postgres they act as an optimization fence. In practice this means you can use them as an abstraction layer only in very limited ways where they happen to fit into a decent execution plan.

Reply on news.ycombinator.com