12 ms·

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL qu

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One thing led to another and suddenly I looked at what I had done and said, "Wait a minute..."

stephen82 7y ago
Hahaha! ^_^ Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it? First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language. You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak. It's a vicious cycle I'm afraid...
- mruts 7y ago
  So are you suggesting that we can’t do better than assembly machine code?
vbezhenar 7y ago
Sometimes you just should live with duplicated code. It's OK.
- dymk 7y ago
  But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives
  - pknopf 7y ago
    There's a middle ground. Micro ORMs.
    - chii 7y ago
      A micro ORM is just an ORM, written well and modularly. It isn't a middle ground - it's choosing to use a well written library. A lot of people conflate ORM's leaking because of poor designed library with ORM being a bad abstraction in general.
      - scarface74 7y ago
        One of the most popular Micro ORMs for C# is Dapper which is used by Stack Overflow. There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running. There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Insert or Update.
        ct520 7y ago
        So.. pattern I see emerging. Use orm for the common stuff and execute sql for complicated queries (like reports)
        Trisell 7y ago
        That’s how I’ve done it on my last two projects. We used TypeORM for the standard repeated simple queries, and then wrote custom SQL for our complicated queries that the ORM failed at and then just executed them with the ORM. It was really nice and made for easier table refactors because we didn’t have to go through and audit every query that was calling that table.
        Rapzid 7y ago
        TypeORM is a step in the right direction for JS ORMs bit it's like 1/8th of the way there IMHO. A nearly fully typed ORM is possible now with Typescript and of course proxy's are out now.. TypeORM was doing too much ADHOC string building under the covers as well. I believe a SQL AST is the way to go. It can be transformed and compiled to database specific SQL allowing for things like predicate push down, optimization, and a sane way to implement db specific optimizations and extensions.
- kieckerjan 7y ago
  Thank you! You just said something I very much agree to but never dare to say out loud.
  - __MatrixMan__ 7y ago
    I think that the way to say it without starting a war is to preface it with something like: > Well, redundancy and dependency both have downsides, but in this case...
- ABeeSea 7y ago
  Well sort of. In my view, duplicate SQL chunks that have defined business logic should either be a new table/view or extremely well-documented with really rigid communication policies for changes. For example, a company with many data analysts/scientists who may each be writing their own queries. As a basic example, the definition of some “very important” company metric changes, then there would need to be a large number of disperse queries to change. But an ORM isn’t the answer for the above situation either.
  - lugg 7y ago
    It's relative, if the duplication is that large maybe you do need to abstract. It also sounds like you would be well served using a service abstraction at that point to remove the data layer from client scope entirely. The "model changes, now we have to change it every where" isn't going to be solved by abstraction, it's only limited by the amount you're willing to limit access to the underlying model, if you need that information, you need to share the model. The best solution to this I've seen in practice is domain modelling, colocating shared code near other users. When things get too distant you start using anti corruption layers which allows more flexible model changing. But at the end of the day this is essential complexity, orm, or any other solution is never going to be able to hide the fact that you need information elsewhere in the system to be useful.
- benjaminbrodie2 7y ago
  The best of both worlds is write your own universal preprocessor...
- cjfd 7y ago
  No, it is not okay. If you need insert/update/select for every object/table that is way too much duplication. It becomes very irritative when the schema changes. There should be table meta data in such a case but one should also know what one is doing. Having no idea that under the water 14 joins are done is not a good situation either.
draw_down 7y ago
Yep. Pick your poison.
takeda 7y ago
> and handle mapping data explicitly. Here's your problem
jdsully 7y ago
SQL is great if you will have multiple applications looking at the same dataset. E.g. An employee management program and a payroll program. In this case you should design a sane schema and mold the app around it. ORMs are terrible in this sort of world since they tightly couple the application to the data. But if you will only ever have one application anyway the abstraction of a separate schema is pointless.
- erik_seaberg 7y ago
  A lot of people who believe only one app (or one language) accesses their org's datastore are mistaken. You have to take extreme measures to prevent ad hoc uses from popping up.
  - philwelch 7y ago
    Yes, yes, yes. Why is this the case? 1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database. 2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy service and whatever you're replacing it with. I suspect any alternative to this arrangement will be an even worse idea, e.g. taking a deliberate outage to perform a likely-irreversible migration.
    - vips7L 7y ago
      The simple solution to 1 is to never allow direct database access. Api only.
      - troxwalt 7y ago
        What do you mean by this? What other way , other then digging right into the data is there to access the database? Isn't it all through APIs?
      - xwolfi 7y ago
alexandercrohde 7y ago
Views are another way to gain reuse.
- akho 7y ago
  As are stored procs, user-defined functions, triggers, scheduled jobs,.. SQL databases are programming environments, not pure datastores.
  - baq 7y ago
    Most are exceptionally bad at being programming environments, though.
kls 7y ago
Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relational theory, whether that be via home rolled or an ORM. That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition. I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly. If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.
- zachrose 7y ago
  > I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly. What alternatives have you tried?
  - kls 7y ago
    ClojureScript, Reflex, Grapefruit, Seesaw and a host of others, It's my opinion (so take it with a grain of salt) and it very well may be the way my brain works but I just find functional to not marry well to UI development. For the service and process oriented stuff associated with the front end I think it is great, but when it comes to modeling components, I find objects and inheritance work far better. This is one of the reasons I have long been a huge proponent of Javascript despite it warts, as it can be OO when I need it to be OO and functional when I need it to be functional.
CraneWorm 7y ago
you could consider something like slick onstead of an ORM: http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html
Mikhail_Edoshin 7y ago
But now you know it's not magic :) Maybe the problem is not that you don't need a mapping layer, but because ORMs are obscure. And maybe they are obscure not because SQL is such a cursed spot, but because object-oriented programming ITSELF drift toward obscurity and magic. Don't you get the same feeling of obscurity about other libraries, e.g. web servers or clients? I often find the bare specs much clearer than (supposedly simplified) OO libraries that implement them.
- olau 7y ago
  Yes. If you stick to using the ORM for what amounts to (mostly) just PODs, it's syntactic sugar that can really help readability.
kccqzy 7y ago
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries. [0]: https://docs.sqlalchemy.org/en/13/ https://docs.sqlalchemy.org/en/13/
- moring 7y ago
  QueryDSL (http://www.querydsl.com/ http://www.querydsl.com/) does something like this for Java. It can generate classes from tables, but even with those, all queries / statements that hit the database are manually built using a query builder to avoid syntax and type errors. I.e. no caching or automatic database updates.
  - nikolasburk 7y ago
    knex.js is another example of such a query builder library in the Node.js ecosystem
- gmac 7y ago
  Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md https://github.com/jawj/mostly-ormless/blob/master/README.md).
  - timmy-turner 7y ago
    Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe I'm trying this instead I'm already half sold. Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.
arendtio 7y ago
ORMs seem to be a typical example of over-engineering. Often you don't need all that complexity they come with and when you do, you are probably better of understanding exactly what you are doing. So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.
beagle3 7y ago
The problem in many cases is actually in the OO part, in my experience - in the vast majority of cases where databases and persistence is concerned, staying in the procedural/structured + relational world keeps things simple, whereas objects often obscure what is actually happen, and invoke opaque magic such as ORMs. I wonder what your experiences had been if after dropping the ORM you had gone one step more and dropped the objects.
- sjwright 7y ago
  This. After contemplating my distaste for ORMs more carefully, I've come to the realisation that my objections aren't so much to do with the concept of an ORM but rather object orientation itself—and the fetish of treating it as the perfect hammer for every nail. For the projects I've worked on, I've almost never wanted to turn data into objects. And on the occasions when I've thought otherwise, it usually turns out to be a mistake; de-objectifying can often result in simpler, shorter code with fewer data bugs. Ultimately, the right answer depends on the nature of your particular business logic, how data flows in your wider ecosystem, and pragmatically, the existing skills of your workforce.
dkersten 7y ago
I like Clojure’s HugSQL[1] for this reason: you can simply write raw SQL, but when you start duplicating code, you can start factoring those bits out into composable “snippets”. The best of both worlds: composability and reuse, while still writing raw SQL. [1] https://www.hugsql.org/ https://www.hugsql.org/
rocho 7y ago
I have the opposite view. I find ORMs annoying and obscure, and I think they introduce duplicated code. If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names. This approach has a lot of advantages over ORMs: * you know exactly what gets executed * automatic DRY code * the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time Schema definitions are in standalone SQL files, as well as my migrations. The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.
- aidos 7y ago
  Your argument does not stand up. I can have a file full of ORM sql fragments the same as you file of strings. And I can compose mine together safely and more flexibly than strings.
- scardine 7y ago
  Have you tried Python's SQLAlchemy, the ORM parent posts are praising? The `sqlalchemy.sql` module is awesome and pretty much maps 1:1 to raw SQL. Composing SQL expressions using this library instead of using string interpolation/concatenation has several advantages: * DRY and composition * safety * portability (if you have switch the underlying DBMS) Often the result is as good or better than my raw SQL. The fact that Python has an amazing REPL makes the process pretty much like testing queries in the database prompt but with less cognitive switch between languages. In the end it is a matter of taste, but I have to agree with parent posts, SQLAlchemy raises the bar for other ORMs.
- dsego 7y ago
  > I find much better to have a module with all my SQL queries as strings. But you can't compose them, so there is a lot of duplication. Also, how would you handle dynamic filters and columns? Concatenating strings? That seems error prone. At least a nice query builder would be useful, but then the whole just write sql thing falls apart.
_the_inflator 7y ago
It is striking the balance between your own queries and ORM. My rule of thumb is that I always go with ORMs for MVPs and small apps. Optimizing for speed usually means going deeper and building a system or queries for yourself. Until that point I usually stick to less verbose code and more to business rules.
thymanl23 7y ago
The things I've found positive about ORMs are exactly that mapping of results to business objects. The things I've found "not worth it" are the query-building APIs baked into the objects. These principles can be seen in a lightweight ORM I made, PureORM [1]. [1] https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm
inimino 7y ago
There is a lot of ancillary complexity in database connection libraries that we could attack before replacing the standard structured query language by some poorly considered mapping of objects to and from relation(s), inspired by poorly understood bad old OOP, which is generally what all ORMs boil down to.
seunosewa 7y ago
> I started coding some simple functions to help map the tabular data to objects. Maybe you should not do that? Can you give us an idea of the domain problem you were trying to solve that made you feel the need for that?
0x445442 7y ago
MyBatis
iamsb 7y ago
I use jdbi.org in Java all the time because it does just that for me.
hhas01 7y ago
Query builders != ORMs. A query builder carefully preserves the underlying relational and RPC semantics and exposes all of that to the user in an easier-to-use form. That’s just good cautious modest abstraction. An ORM believes it knows way better than those dumb RDBs how a database ought to behave by ingeniously pretending that everything you’re dealing with is just nice simple familiar arrays of local native class instances. Which, like all lies, ends up spawning more lies as each one starts to crack under inspection, until the whole rotten pile catastrophically collapses under the weight of its own total bullshit. And of course it goes without saying which of these approaches our arrogant grandstanding consequence-shirking industry most likes to adopt.
inanutshellus 7y ago
This is exactly what MyBATIS is for. You throw in SQL, provide a simple mapper, done. IMHO it's far superior to ORMs when your database is or may become complicated.

stephen82 7y ago

Hahaha! ^_^ Seriously Jesse, isn't this the very same reason __some__ people end up implementing yet another programming language without realizing it? First they start out of exasperation with X language they use, because they hit some obstacles or limitations, and before they know it they end up implementing a newly created language. You know what's the fun part? In their attempt to fix the aforementioned language's issues, they end up introducing __the very same problems__ in their own language, only under different "cloak" so to speak. It's a vicious cycle I'm afraid...

mruts 7y ago
So are you suggesting that we can’t do better than assembly machine code?

vbezhenar 7y ago

Sometimes you just should live with duplicated code. It's OK.

dymk 7y ago
But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives
- pknopf 7y ago
  There's a middle ground. Micro ORMs.
  - chii 7y ago
    A micro ORM is just an ORM, written well and modularly. It isn't a middle ground - it's choosing to use a well written library. A lot of people conflate ORM's leaking because of poor designed library with ORM being a bad abstraction in general.
    - scarface74 7y ago
      One of the most popular Micro ORMs for C# is Dapper which is used by Stack Overflow. There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running. There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Insert or Update.
      - ct520 7y ago
        So.. pattern I see emerging. Use orm for the common stuff and execute sql for complicated queries (like reports)
        Trisell 7y ago
        That’s how I’ve done it on my last two projects. We used TypeORM for the standard repeated simple queries, and then wrote custom SQL for our complicated queries that the ORM failed at and then just executed them with the ORM. It was really nice and made for easier table refactors because we didn’t have to go through and audit every query that was calling that table.
        Rapzid 7y ago
        TypeORM is a step in the right direction for JS ORMs bit it's like 1/8th of the way there IMHO. A nearly fully typed ORM is possible now with Typescript and of course proxy's are out now.. TypeORM was doing too much ADHOC string building under the covers as well. I believe a SQL AST is the way to go. It can be transformed and compiled to database specific SQL allowing for things like predicate push down, optimization, and a sane way to implement db specific optimizations and extensions.
kieckerjan 7y ago
Thank you! You just said something I very much agree to but never dare to say out loud.
- __MatrixMan__ 7y ago
  I think that the way to say it without starting a war is to preface it with something like: > Well, redundancy and dependency both have downsides, but in this case...
ABeeSea 7y ago
Well sort of. In my view, duplicate SQL chunks that have defined business logic should either be a new table/view or extremely well-documented with really rigid communication policies for changes. For example, a company with many data analysts/scientists who may each be writing their own queries. As a basic example, the definition of some “very important” company metric changes, then there would need to be a large number of disperse queries to change. But an ORM isn’t the answer for the above situation either.
- lugg 7y ago
  It's relative, if the duplication is that large maybe you do need to abstract. It also sounds like you would be well served using a service abstraction at that point to remove the data layer from client scope entirely. The "model changes, now we have to change it every where" isn't going to be solved by abstraction, it's only limited by the amount you're willing to limit access to the underlying model, if you need that information, you need to share the model. The best solution to this I've seen in practice is domain modelling, colocating shared code near other users. When things get too distant you start using anti corruption layers which allows more flexible model changing. But at the end of the day this is essential complexity, orm, or any other solution is never going to be able to hide the fact that you need information elsewhere in the system to be useful.
benjaminbrodie2 7y ago
The best of both worlds is write your own universal preprocessor...
cjfd 7y ago
No, it is not okay. If you need insert/update/select for every object/table that is way too much duplication. It becomes very irritative when the schema changes. There should be table meta data in such a case but one should also know what one is doing. Having no idea that under the water 14 joins are done is not a good situation either.

draw_down 7y ago

Yep. Pick your poison.

takeda 7y ago

> and handle mapping data explicitly. Here's your problem

jdsully 7y ago

SQL is great if you will have multiple applications looking at the same dataset. E.g. An employee management program and a payroll program. In this case you should design a sane schema and mold the app around it. ORMs are terrible in this sort of world since they tightly couple the application to the data. But if you will only ever have one application anyway the abstraction of a separate schema is pointless.

erik_seaberg 7y ago
A lot of people who believe only one app (or one language) accesses their org's datastore are mistaken. You have to take extreme measures to prevent ad hoc uses from popping up.
- philwelch 7y ago
  Yes, yes, yes. Why is this the case? 1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database. 2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy service and whatever you're replacing it with. I suspect any alternative to this arrangement will be an even worse idea, e.g. taking a deliberate outage to perform a likely-irreversible migration.
  - vips7L 7y ago
    The simple solution to 1 is to never allow direct database access. Api only.
    - troxwalt 7y ago
      What do you mean by this? What other way , other then digging right into the data is there to access the database? Isn't it all through APIs?
    - xwolfi 7y ago

alexandercrohde 7y ago

Views are another way to gain reuse.

akho 7y ago
As are stored procs, user-defined functions, triggers, scheduled jobs,.. SQL databases are programming environments, not pure datastores.
- baq 7y ago
  Most are exceptionally bad at being programming environments, though.

kls 7y ago

Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relational theory, whether that be via home rolled or an ORM. That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition. I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly. If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.

zachrose 7y ago
> I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly. What alternatives have you tried?
- kls 7y ago
  ClojureScript, Reflex, Grapefruit, Seesaw and a host of others, It's my opinion (so take it with a grain of salt) and it very well may be the way my brain works but I just find functional to not marry well to UI development. For the service and process oriented stuff associated with the front end I think it is great, but when it comes to modeling components, I find objects and inheritance work far better. This is one of the reasons I have long been a huge proponent of Javascript despite it warts, as it can be OO when I need it to be OO and functional when I need it to be functional.

CraneWorm 7y ago

you could consider something like slick onstead of an ORM: http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html http://slick.lightbend.com/doc/3.2.0/orm-to-slick.html

Mikhail_Edoshin 7y ago

But now you know it's not magic :) Maybe the problem is not that you don't need a mapping layer, but because ORMs are obscure. And maybe they are obscure not because SQL is such a cursed spot, but because object-oriented programming ITSELF drift toward obscurity and magic. Don't you get the same feeling of obscurity about other libraries, e.g. web servers or clients? I often find the bare specs much clearer than (supposedly simplified) OO libraries that implement them.

olau 7y ago
Yes. If you stick to using the ORM for what amounts to (mostly) just PODs, it's syntactic sugar that can really help readability.

kccqzy 7y ago

There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you connect to the database and construct queries. [0]: https://docs.sqlalchemy.org/en/13/ https://docs.sqlalchemy.org/en/13/

moring 7y ago
QueryDSL (http://www.querydsl.com/ http://www.querydsl.com/) does something like this for Java. It can generate classes from tables, but even with those, all queries / statements that hit the database are manually built using a query builder to avoid syntax and type errors. I.e. no caching or automatic database updates.
- nikolasburk 7y ago
  knex.js is another example of such a query builder library in the Node.js ecosystem
gmac 7y ago
Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md https://github.com/jawj/mostly-ormless/blob/master/README.md).
- timmy-turner 7y ago
  Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe I'm trying this instead I'm already half sold. Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.

arendtio 7y ago

ORMs seem to be a typical example of over-engineering. Often you don't need all that complexity they come with and when you do, you are probably better of understanding exactly what you are doing. So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.

beagle3 7y ago

The problem in many cases is actually in the OO part, in my experience - in the vast majority of cases where databases and persistence is concerned, staying in the procedural/structured + relational world keeps things simple, whereas objects often obscure what is actually happen, and invoke opaque magic such as ORMs. I wonder what your experiences had been if after dropping the ORM you had gone one step more and dropped the objects.

sjwright 7y ago
This. After contemplating my distaste for ORMs more carefully, I've come to the realisation that my objections aren't so much to do with the concept of an ORM but rather object orientation itself—and the fetish of treating it as the perfect hammer for every nail. For the projects I've worked on, I've almost never wanted to turn data into objects. And on the occasions when I've thought otherwise, it usually turns out to be a mistake; de-objectifying can often result in simpler, shorter code with fewer data bugs. Ultimately, the right answer depends on the nature of your particular business logic, how data flows in your wider ecosystem, and pragmatically, the existing skills of your workforce.

dkersten 7y ago

I like Clojure’s HugSQL[1] for this reason: you can simply write raw SQL, but when you start duplicating code, you can start factoring those bits out into composable “snippets”. The best of both worlds: composability and reuse, while still writing raw SQL. [1] https://www.hugsql.org/ https://www.hugsql.org/

rocho 7y ago

I have the opposite view. I find ORMs annoying and obscure, and I think they introduce duplicated code. If you need to run a certain query in multiple places, you need to repeat the same ORM expression or refractor it into a function. I find much better to have a module with all my SQL queries as strings. That way whenever I need to run a query I reference it from there. Of course it helps to use meaningful names. This approach has a lot of advantages over ORMs: * you know exactly what gets executed * automatic DRY code * the names of the SQL queries in the code are self-explanatory and the reader doesn't have to parse the ORM expression every time Schema definitions are in standalone SQL files, as well as my migrations. The only disadvantage is that it may be difficult to switch to a different database system, but that is not a problem for us.

aidos 7y ago
Your argument does not stand up. I can have a file full of ORM sql fragments the same as you file of strings. And I can compose mine together safely and more flexibly than strings.
scardine 7y ago
Have you tried Python's SQLAlchemy, the ORM parent posts are praising? The `sqlalchemy.sql` module is awesome and pretty much maps 1:1 to raw SQL. Composing SQL expressions using this library instead of using string interpolation/concatenation has several advantages: * DRY and composition * safety * portability (if you have switch the underlying DBMS) Often the result is as good or better than my raw SQL. The fact that Python has an amazing REPL makes the process pretty much like testing queries in the database prompt but with less cognitive switch between languages. In the end it is a matter of taste, but I have to agree with parent posts, SQLAlchemy raises the bar for other ORMs.
dsego 7y ago
> I find much better to have a module with all my SQL queries as strings. But you can't compose them, so there is a lot of duplication. Also, how would you handle dynamic filters and columns? Concatenating strings? That seems error prone. At least a nice query builder would be useful, but then the whole just write sql thing falls apart.

_the_inflator 7y ago

It is striking the balance between your own queries and ORM. My rule of thumb is that I always go with ORMs for MVPs and small apps. Optimizing for speed usually means going deeper and building a system or queries for yourself. Until that point I usually stick to less verbose code and more to business rules.

thymanl23 7y ago

The things I've found positive about ORMs are exactly that mapping of results to business objects. The things I've found "not worth it" are the query-building APIs baked into the objects. These principles can be seen in a lightweight ORM I made, PureORM [1]. [1] https://github.com/craigmichaelmartin/pure-orm https://github.com/craigmichaelmartin/pure-orm

inimino 7y ago

There is a lot of ancillary complexity in database connection libraries that we could attack before replacing the standard structured query language by some poorly considered mapping of objects to and from relation(s), inspired by poorly understood bad old OOP, which is generally what all ORMs boil down to.

seunosewa 7y ago

> I started coding some simple functions to help map the tabular data to objects. Maybe you should not do that? Can you give us an idea of the domain problem you were trying to solve that made you feel the need for that?

0x445442 7y ago

MyBatis

iamsb 7y ago

I use jdbi.org in Java all the time because it does just that for me.

hhas01 7y ago

Query builders != ORMs. A query builder carefully preserves the underlying relational and RPC semantics and exposes all of that to the user in an easier-to-use form. That’s just good cautious modest abstraction. An ORM believes it knows way better than those dumb RDBs how a database ought to behave by ingeniously pretending that everything you’re dealing with is just nice simple familiar arrays of local native class instances. Which, like all lies, ends up spawning more lies as each one starts to crack under inspection, until the whole rotten pile catastrophically collapses under the weight of its own total bullshit. And of course it goes without saying which of these approaches our arrogant grandstanding consequence-shirking industry most likes to adopt.

inanutshellus 7y ago

This is exactly what MyBATIS is for. You throw in SQL, provide a simple mapper, done. IMHO it's far superior to ORMs when your database is or may become complicated.