
Ask HN: Is it reasonable to choose direct SQL over an ORM - thymanl23
I&#x27;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.<p>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:&#x2F;&#x2F;github.com&#x2F;craigmichaelmartin&#x2F;sql-toolkit - a small library atop database drivers which accepts raw SQL and return back pure, properly nested business objects.<p>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?

