Quick survey, when should I use orm? for example in nodejs? Currently working on an sis api using node. Just wondering if I create another prototype and use orm. Performance wise?

Perf-wise? Never :)

@netikras Ohhh I see. Thank you sir

Never. And if possible, never ever.

Can I ask why?

@kjing
1. You completely lose control of your queries. COMPLETELY
2. You have limited control of mapping [what is transient? What should be lazy fetched? Can you fetch lazy data after tx is over? If not - you have an impaired data structure whic fields cannot be accessed. The n+1 problem. Changing the db ddl will echo through your code everywhere your orm-ed model is used]
3. You add overhead of resolving the mapping

to start with :)

@SortOfTested I wouldn't say *never* though. I could completely justify orm at the beginning of the project. It does speed things up and the performance is not yet a concern.

@netikras so you're saying that I can use orm but if perf wise but when I comes to perf? it's not quite good?

@kjing yes, perf-wise it's a terrible idea to use an orm. Perf-wise you want to NOT be locked in in any aspect. Perf-wise you want to have complete control of your system, which orm usually takes away from you.

If your bad at DB an ORM could help you get a project running and with a good ORM even get decent performance out of it.

But no ORM can account for data dependent issues like very skewed distribution and also, it rarely will know how you are going to use it and might over or under fetch data causing unnecessary load on the db.

Some light weight orms like dapper can be used for very simple queries to reduce code but almost all orm will fall short for more complex or large datasets.

And the real danger is that they work well in dev and early life but once you reach a threshold it can start acting up creating very bad queries that tank the db.

And by then to replace the orm will be a chore at a time where you might have lots of customers suffering.

So unless you either suck at databases or know exactly why you are using the ORM, I would take the effort to learn to use the database.

Most ORMs I've seen do unspeakable things.

For example, Django's ORM will insert fields, at runtime, into other models that reverse-lookup fields on the original model.

For example, if your Company class has a field called 'employees' of type User[], Django will create some field kind of like employee_of_companies of type Company[] on the User model - at runtime, magically.

This field is usually a dynamic property, meaning it excecutes something when it's accessed (which means your code looks like a simple property access when it's actually executing something).

Worse, it's producing a side effect of introducing network traffic. This is entirely opaque to the developer writing code that interacts with that property, and it can be absolutely killer and hard to debug in e.g. a loop.

This sort of magic is awful and unfortunately is the category of arcane nonsense most ORMs employ.

Learn to write queries. You'll be happy you did.

@junon thats an m2m relationship with an table that connects Users and companies.

ORMs seem like a great idea at the start of a project because they're so *neat* for simple stuff. For said simple stuff, they're also near enough as fast as anything else performance wise too.

The problem is, the magic disappears any time the real world problems appear. Want to do a 3 way join with limits and sub queries? Sure, an ORM will do that - in the least efficient way known to man. Want to insert a bunch of stuff across tables and update indices at the same time? Sure, it'll do that - but maybe not in the same transaction, so you'll get weird consistency bugs sometimes. Want to bypass the crap and specify your own query? Sure... but then other stuff breaks as some naming convention changes as a result.

They might seem like magic, but they're fragile, slow things that cause more problems than they solve. Theres very few cases where we don't have a saner, better alternative these days.

@stop Right, yeah. It's magic though. Magically changing the prototype of your classes in a way that is invisible.

@junon that is documented and intended behaviour.

@stop Doesn't matter if it's documented OR intended. It's magic, and it makes code fragile; it becomes very difficult to determine if a simple property access (syntactically, visually) will result in a DB hit.

I spent 6 months working on a moderate sized Django app that someone else wrote, and I felt like I went at a tenth of the speed because I had to check and re-check which things I did could result in a network request.