Ask HN: SQL ORM in production? | Hacker News Reader

Ask HN: SQL ORM in production?

2 pointsgigatexal6 comments

SQL is second nature to me. I am no expert at it, maybe an intermediate user, but I’m definitely a fan. And a believer in well formed relational databases. So I would rather a service call into a database via a stored procedure or function call. I know this tightly couples the application to the database: stored procedures aren’t easily migratable but you can via a good ORM just migrate the schema and data and then switch connection strings and should be good to go more or less. That and then there’s the issue that the queries that the ORM might craft can’t be as optimized as a hand rolled one at least in my experience. And then the overhead of defining relationships in models. Most recently I’ve worked on microservices in C# with entity framework core and it was dog slow compared to raw SQL. So the question is what is your take on ORMs?

View on HN

They are good query composers for simple queries (I'm thinking about Ruby on Rails and Django), useless for complex ones.

They're very useful to decode results in objects of the language and extremely useful to migrate the database.

I don't know about C# but probably the slow downs you're experiencing are in the decode phase.

I'm ok with stored procedures but I'd use them only where it makes sense.

I’m super biased but I still hate them. There’s something powerful and simple being able to execute parametered queries. Alas if the industry is going this way then I’ll have to adapt.

Parametered queries == prepared statements? Rails' ActiveRecord creates prepared statements and even caches query results by default. It's pretty useful to write stuff like Book.where(genre: 'scifi').count or Author.where(name: 'Frank Herbert').books.order_by(:publishing_date).first.title. You get the result immediately. Of course title = sql(select title from books ... ) would be quite ok too ( * ), but it turns out that some simple queries are quite long to write. I tend to use SQL only when I have to start looking at the documentation of the ORM.

( * ) I know no tool that can write queries like that but it would be great.

See that conciseness I don’t see as a benefit. I do acknowledge the downside of having a query in a method and maybe having to tweak the same type of query in a handful of methods any time the schema changes. It’s a good way to introduce bugs I guess. At that point I would just use stored procedures.

We mainly do DB construction ourselves, and for C# use LinqToSql and our own repos to integrate with it.

This is mainly because entity framework is terrible. It’s slow enough that the lack of speed which is worse in Linq gets negligible. It’s unproductive in that it’s really verbose and unintuitive in how it works, especially code first. And worst of all, it’s migrations often break, leading to a lot of time fixing it.

So if you’re doing C# I’d probably keep doing raw sql if I were you.

Django by contrast has an excellent ORM, and with node you have stuff like prisma (though it doesn’t have a mssql driver.

So really, I’d suggest you looked into python for your services.

I think it's generally pretty low effort to maintain a repository layer with raw SQL exposing your domain or entity objects (depending on the complexity of the whole project), so the most aggressive I get with an ORM is something like Dapper. RepoDb also looks promising.

Reply on news.ycombinator.com