
Ask HN: SQL ORM in production? - gigatexal
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?
======
pmontra
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.

~~~
gigatexal
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.

~~~
pmontra
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.

~~~
gigatexal
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.

------
eksemplar
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.

------
yawgmoth
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.

