Ask HN: Is it reasonable to choose direct SQL over an ORM

[thymanl23]

I've been using an ORM for years - and often still find myself frustrated trying to map the complexity and nuance of SQL to an ORM api.

What is attractive to me about ORMs, though, is the marshaling of raw result rows into predefined structured objects (preferable pure objects). It seems like if I missed this, I could always reach for something like https://github.com/craigmichaelmartin/sql-toolkit - a small library atop database drivers which accepts raw SQL and return back pure, properly nested business objects.

Is this reasonable?

[mattbillenstein]

It is! And it's a fun way to program imo.

I'm working on a project now, the api is graphql using Graphene in Python (flask-graphql with graphiql support), so we basically define the models using that. The graphene resolvers talk to a package called "services" which is where all the business logic and queries live, and services talks to another package "db" which deals with taking sql+params and returning dict rows.

It is exceedingly simple, easy to debug, and we're using postgres, so if you have a sensible schema, postgres can do just about anything you want to do very easily. We're using postgis, and postgres search features under the hood.

[thymanl23]

Wow, that sounds like a really neat approach!!

[cjbprime]

For something other than a webapp, sure.

If you're writing a webapp, I suppose I would suspect that you just haven't spent enough time with a good ORM. It's natural to get frustrated with it occasionally, but seems like you can drop to SQL for those cases as a one-off, rather than discard the ORM totally?

[thymanl23]

Hmm, yeah, but I think more than just not using a query building api, I prefer pure result objects. Even when dropping to SQL, the result objects are still database-aware objects. I prefer distinct layers between the business domain layer and the data access layer. Having a data access layer return pure business objects allows for this decoupling, but maybe I'm being to strict and forcing a distinction where there doesn't need to be one?

[alexmingoia]

It is more than reasonable, you’ll be reducing your dependencies and the surface area for bugs. SQL is well documented and provides more features than any ORM, especially if you’re using postgres. And if you ever need to expose a REST API you can just slap postgrest in front of the DB.

[cimmanom]

Depends a lot on context.

A simple CRUD query with a filter or two and a couple joins, in a large project maintained by a large team, is going to be more maintainable in an ORM.

But many ORMs will be awkward for writing queries used for complex reporting - if they can be written using the ORM at all.

And if you’re working on a project that only you will touch, just use whatever you’re most comfortable with.

[thedevindevops]

Dapper is a pretty good, lightweight ORM

[mattmanser]

ORMs are great for simple stuff, but there's nothing stopping you from switching to raw SQL for more complex stuff.

Why can't you do both? I do it all the time.

[thymanl23]

In an ORM, the returned objects from even raw SQL is still ORM objects (database-aware objects). I prefer keeping the business logic layer distinct from the data access layer, so I prefer for the return objects to be pure business objects. Maybe I'm being too anal and forcing a distinction where there doesn't need one?