Hacker News Reader

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

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.

Isn't all the code bespoke? Or, less snarkily, what's wrong with writing SQL when your ORM starts leaking abstractions?

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.

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

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.

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.

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.

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?

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.

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.

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"

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

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

This article could be titled ORM considered harmful (2006)

http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

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.

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

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.

Knex is great. I use it together with Objection.

This match reminds me on the 2 sides of SQLAlchemy in a very good way :)

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?

I hear that TypeORM is nice.

I tried and in my case it was 2x times slower. Probably, because of extensive use of reflection or something like this.

I've been happily using Objection.js (https://vincit.github.io/objection.js/) for my last couple projects.

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.

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.

  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 :)

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 :)

Bind variables are the ? in "SELECT fname, lname, pcode FROM cust WHERE id = ?" and they're a better choice than concatenating the ID you want into the SQL string because (a) it prevents SQL injection and (b) it lets the query optimiser cache plans.

We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.

Another bonus - depending on your DB/driver, it may set this up as a prepared statement, so executions after the first one happen much quicker.

What are "bind variables"?

I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.

No. "bind variables" are Oracle 101. Everyone else calls them "query parameters".

What is wrong with writing a raw SQL for such a simple thing? I never want to fight with a tool for achieving such a simple query. Tools are supposed to help and when queries tend to get complicated, I think that's when ORM starts to fail with performance. So, I don't find a room for it. I'd rather learn how SQL works instead of how to get random ORM of the time to work like it should.

> What is wrong with writing a raw SQL for such a simple thing?

Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses.

Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.

This resonates with me.

But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.

In all fairness a properly used sql connector library will hopefully do this too.

Although the majority of what I do is in Python, I've taken to using Flyway for DB migrations.

A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.

If you're not doing anything to manage and autogen your queries, does that imply that any change to the model's table entails manually updating all N different queries that touch the table in your application?

I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs.

You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.

Don't get me wrong: everyone should learn SQL and learn it well. But a query construction and composition abstraction is also an invaluable tool. Fortunately, it is quite achievable to learn both things well.

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.

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

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?