13 ms·

My example is a bit dated, but may be illustrative: I chose PostgreSQL over MongoDB for a MEAN webapp (making it PEAN?) project started in 2013 and regretted i

My example is a bit dated, but may be illustrative: I chose PostgreSQL over MongoDB for a MEAN webapp (making it PEAN?) project started in 2013 and regretted it. I was constantly struggling with the poor support for SQL in Node at the time and spent way too much time fixing bugs or adding workarounds for deficiencies in the SQL abstraction libraries and ORMs, or writing bespoke SQL and object mapping. After switching to MongoDB, I spent significantly less time on storage issues and development speed increased noticeably. Later we did run into several issues caused by MongoDB which could have been avoided by using SQL (mostly related to consistency, constraint enforcement, and problems with MongoDB's aggregation capabilities). MongoDB got us to market faster, but had higher costs in the long-term. I think there are two lessons that I'd take away: 1) There are costs to choosing a less popular technology combination, even if the individual technologies might be better (for your particular needs) in isolation. 2) Sometimes it's better to choose the more costly technology in the long-run to avoid costs early (i.e. incurring some technical debt can be the right choice). MongoDB vs SQL for us was an example of both of these. Note: I assume SQL libraries in Node have matured significantly since 2013, so I wouldn't necessarily recommend MongoDB over SQL for new projects starting today.

tonious 8y ago
IMHO, this is more of an indictment of the ORM (sounds like sequelize) than anything else.
- kevinoid 8y ago
  Partly it is. I had issues with both sequelize and any-db. However, since not using them resulted in a lot of bespoke SQL and object mapping, I found MongoDB preferable to SQL without these libraries as well.
  - bkrn 8y ago
    Isn't all the code bespoke? Or, less snarkily, what's wrong with writing SQL when your ORM starts leaking abstractions?
  - piva00 8y ago
    I don't really get what was the problem on creating your own queries though. If the ORM layer sucked you don't really need it, queries and object mappers (if you needed some kind of typed objects) is powerful enough without making you have to learn the ORM's quirks and gotchas.
    - kevinoid 8y ago
      Sure, but I found that I was wasting a lot of time writing dead-simple SQL queries and object mapping that could have been better spent writing application code (and was after switching to MongoDB).
      - EpicEng 8y ago
        Why wouldn't you auto generate the simple SQL stuff? It's a no brainier really. It's so easy to dynamically create simple selects, updates, etc.
        kevinoid 8y ago
        I played around with that a little, but it seemed like once you start trying to auto-generate SQL from objects you are basically writing a simple ORM. So you either spend time writing queries, spend time building an ORM, spend time fixing an existing ORM, or some combination of those.
        EpicEng 8y ago
        Sure, but it's about finding that sweet spot. I dislike large ORMs The majority of queries are simple and trivial to auto generate. The more entities you add the more value you get from it.
      - piva00 8y ago
        So instead of writing dead simple SQL queries (that mostly you could copy-paste if you don't want the complexity of dynamically generating them) it was easier and simpler to switch the whole data layer?
  - flexd 8y ago
    So the whole problem could have been avoided if you had chosen a more mature programming language at the time time? Perl, Python and PHP all had perfectly good SQL libraries at the time, as well as a lot of other languages that have been around a long time.
    - kevinoid 8y ago
      That's a fair criticism. There are a lot of tradeoffs in language choice. For this project the choice was out of my hands, but in retrospect I don't think Node was necessarily a bad choice. Node worked well for a lot of things we did. Was the cost spent struggling with SQL outweighed by the other benefits? It's hard to say for sure.
  - grncdr 8y ago
    I'm really confused why you would put any-db in the same category as Sequelize... it's not anything like an ORM and never wanted to be. It's the wrong tool for any job that isn't "writing a DB-agnostic SQL generator"
slezyr 8y ago
> fixing bugs or adding workarounds for deficiencies in the SQL abstraction libraries and ORMs TBH I had same experience with all ORMs I have used sqlpp(c++), odb(c++), diesel(rust).
- sametmax 8y ago
  ORM are one of those kind of libs where very few ones are great. Python has lots of them, and only SQLAlchemy can be considered of high quality. Not that Django's ORM, peewee or eloquent don't get the job done. But they only cover 80% of the use cases. SQLA gets you to 95%, and you bridge the five remaining percents with raw sql injected in the proper places for those rare projects where the extra is important. The cost of it is that it's a harder to use ORM, and a more verbose one. Which is why I generally use https://github.com/absent1706/sqlalchemy-mixins https://github.com/absent1706/sqlalchemy-mixins to make things easier. What's more, ORM for languages like c++ or Rust have it extra hard as they are used from a low level tech while SQL is quite high level and easy to manipulate. It's hence attractive to use it directly. If you use PHP, Ruby or Python, SQL doesn't seem that big of a gain anymore for the simple operations. Now diesel is quite young, and the rust ecosystem has proven to be moving not only fast, but generally in a sane direction, so I wouldn't count it out just yet. However, I've yet to find an even acceptable JS ORM. Not only do they fail to provide any decent operation beyond simple ones, but they have such poor introspection capabilities that they can't be used to create an ecosystem around them. Which is, after all, __the main point of an ORM__. Avoiding to write SQL is definitively NOT the most important feature of an ORM. The critical one is having a common API you can build upon to create form generators, auth systems, data validation units, serializers, automatic UI, etc. Without this, the case for using an ORM is way weaker.
- lucio 8y ago
  This article could be titled ORM considered harmful (2006) http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
  - ris 8y ago
    There's a lot of hatred for ORMs that floats around but most of the ire seems to revolve around the cases where the ORM's flexibility doesn't allow it to expose the power of the underlying database properly. But people often fail to note how few and far between these examples are and how the vast majority of an app's queries tend to be dumb & boring and handled extremely competently by the ORM. Contrast this with the number of dumb typos & sql mistakes through verbosity that the ORM has saved. Don't get distracted by the 1 or 2 cases per app.
nurettin 8y ago
2013 I used knex and had no problems whatsoever interfacing with postgres from node.
- kevinoid 8y ago
  That's great to hear! But given that the first release of knex wasn't until May of 2013, it wouldn't have been an option for me.
- AndresSRG 8y ago
  Knex is great. I use it together with Objection. This match reminds me on the 2 sides of SQLAlchemy in a very good way :)
h1d 8y ago
Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an unnecessary and ineffective layer.
- sanderjd 8y ago
  I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM. I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.
  - michaelt 8y ago
    How are you constructing queries? Entirely with bind variables. given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns. Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)
    - sanderjd 8y ago
      What are "bind variables"? I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)
ex3ndr 8y ago
In 2018 i feel same (we are using sequelize). I haven't found something that work faster than sequelize, but API is absolutely useless for anything other than simple joins. May be someone knows good ORM for js with nice query builders?
- pitaj 8y ago
  I hear that TypeORM is nice.
  - ex3ndr 8y ago
    I tried and in my case it was 2x times slower. Probably, because of extensive use of reflection or something like this.
- Scooty 8y ago
  I've been happily using Objection.js (https://vincit.github.io/objection.js/ https://vincit.github.io/objection.js/) for my last couple projects.
  - deleted 8y ago
    [deleted]
pytyper2 8y ago
The constraint and consistency problems in mongo are and were well documented, this is a failure in the technology due diligence process. Any app that is more than a toy needs to be back by an ACID compliant database.
- nailer 8y ago
  It's also a failure of Mongo developers, who have a well known record of shipping dangerous defaults.
humbleMouse 8y ago
This sounds like a poor choice of tools for the project. If implementing something that needs a json front-end and relational back-end, why not use java?

tonious 8y ago

IMHO, this is more of an indictment of the ORM (sounds like sequelize) than anything else.

kevinoid 8y ago
Partly it is. I had issues with both sequelize and any-db. However, since not using them resulted in a lot of bespoke SQL and object mapping, I found MongoDB preferable to SQL without these libraries as well.
- bkrn 8y ago
  Isn't all the code bespoke? Or, less snarkily, what's wrong with writing SQL when your ORM starts leaking abstractions?
- piva00 8y ago
  I don't really get what was the problem on creating your own queries though. If the ORM layer sucked you don't really need it, queries and object mappers (if you needed some kind of typed objects) is powerful enough without making you have to learn the ORM's quirks and gotchas.
  - kevinoid 8y ago
    Sure, but I found that I was wasting a lot of time writing dead-simple SQL queries and object mapping that could have been better spent writing application code (and was after switching to MongoDB).
    - EpicEng 8y ago
      Why wouldn't you auto generate the simple SQL stuff? It's a no brainier really. It's so easy to dynamically create simple selects, updates, etc.
      - kevinoid 8y ago
        I played around with that a little, but it seemed like once you start trying to auto-generate SQL from objects you are basically writing a simple ORM. So you either spend time writing queries, spend time building an ORM, spend time fixing an existing ORM, or some combination of those.
        EpicEng 8y ago
        Sure, but it's about finding that sweet spot. I dislike large ORMs The majority of queries are simple and trivial to auto generate. The more entities you add the more value you get from it.
    - piva00 8y ago
      So instead of writing dead simple SQL queries (that mostly you could copy-paste if you don't want the complexity of dynamically generating them) it was easier and simpler to switch the whole data layer?
- flexd 8y ago
  So the whole problem could have been avoided if you had chosen a more mature programming language at the time time? Perl, Python and PHP all had perfectly good SQL libraries at the time, as well as a lot of other languages that have been around a long time.
  - kevinoid 8y ago
    That's a fair criticism. There are a lot of tradeoffs in language choice. For this project the choice was out of my hands, but in retrospect I don't think Node was necessarily a bad choice. Node worked well for a lot of things we did. Was the cost spent struggling with SQL outweighed by the other benefits? It's hard to say for sure.
- grncdr 8y ago
  I'm really confused why you would put any-db in the same category as Sequelize... it's not anything like an ORM and never wanted to be. It's the wrong tool for any job that isn't "writing a DB-agnostic SQL generator"

slezyr 8y ago

> fixing bugs or adding workarounds for deficiencies in the SQL abstraction libraries and ORMs TBH I had same experience with all ORMs I have used sqlpp(c++), odb(c++), diesel(rust).

sametmax 8y ago
ORM are one of those kind of libs where very few ones are great. Python has lots of them, and only SQLAlchemy can be considered of high quality. Not that Django's ORM, peewee or eloquent don't get the job done. But they only cover 80% of the use cases. SQLA gets you to 95%, and you bridge the five remaining percents with raw sql injected in the proper places for those rare projects where the extra is important. The cost of it is that it's a harder to use ORM, and a more verbose one. Which is why I generally use https://github.com/absent1706/sqlalchemy-mixins https://github.com/absent1706/sqlalchemy-mixins to make things easier. What's more, ORM for languages like c++ or Rust have it extra hard as they are used from a low level tech while SQL is quite high level and easy to manipulate. It's hence attractive to use it directly. If you use PHP, Ruby or Python, SQL doesn't seem that big of a gain anymore for the simple operations. Now diesel is quite young, and the rust ecosystem has proven to be moving not only fast, but generally in a sane direction, so I wouldn't count it out just yet. However, I've yet to find an even acceptable JS ORM. Not only do they fail to provide any decent operation beyond simple ones, but they have such poor introspection capabilities that they can't be used to create an ecosystem around them. Which is, after all, __the main point of an ORM__. Avoiding to write SQL is definitively NOT the most important feature of an ORM. The critical one is having a common API you can build upon to create form generators, auth systems, data validation units, serializers, automatic UI, etc. Without this, the case for using an ORM is way weaker.
lucio 8y ago
This article could be titled ORM considered harmful (2006) http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
- ris 8y ago
  There's a lot of hatred for ORMs that floats around but most of the ire seems to revolve around the cases where the ORM's flexibility doesn't allow it to expose the power of the underlying database properly. But people often fail to note how few and far between these examples are and how the vast majority of an app's queries tend to be dumb & boring and handled extremely competently by the ORM. Contrast this with the number of dumb typos & sql mistakes through verbosity that the ORM has saved. Don't get distracted by the 1 or 2 cases per app.

nurettin 8y ago

2013 I used knex and had no problems whatsoever interfacing with postgres from node.

kevinoid 8y ago
That's great to hear! But given that the first release of knex wasn't until May of 2013, it wouldn't have been an option for me.
AndresSRG 8y ago
Knex is great. I use it together with Objection. This match reminds me on the 2 sides of SQLAlchemy in a very good way :)

h1d 8y ago

Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an unnecessary and ineffective layer.

sanderjd 8y ago
I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM. I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.
- michaelt 8y ago
  How are you constructing queries? Entirely with bind variables. given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns. Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)
  - sanderjd 8y ago
    What are "bind variables"? I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)

ex3ndr 8y ago

In 2018 i feel same (we are using sequelize). I haven't found something that work faster than sequelize, but API is absolutely useless for anything other than simple joins. May be someone knows good ORM for js with nice query builders?

pitaj 8y ago
I hear that TypeORM is nice.
- ex3ndr 8y ago
  I tried and in my case it was 2x times slower. Probably, because of extensive use of reflection or something like this.
Scooty 8y ago
I've been happily using Objection.js (https://vincit.github.io/objection.js/ https://vincit.github.io/objection.js/) for my last couple projects.
- deleted 8y ago
  [deleted]

pytyper2 8y ago

The constraint and consistency problems in mongo are and were well documented, this is a failure in the technology due diligence process. Any app that is more than a toy needs to be back by an ACID compliant database.

nailer 8y ago
It's also a failure of Mongo developers, who have a well known record of shipping dangerous defaults.

humbleMouse 8y ago

This sounds like a poor choice of tools for the project. If implementing something that needs a json front-end and relational back-end, why not use java?