So w.r.t the ORMs / QueryBuilders / Raw SQL; Below are my observations (some from my own experiences)

• Advocating an ORM against SQL learning curve should not be a point at first place.
  • While the ORMs generally follow the core concepts from a couple of patterns (DataMapper, ActiveRecord, EntityManager, UoW, etc...) but their implementation and syntax differs a lot between each ORM implementation.
  • Meaning, there will always be a learning curve for any ORM that you pick. And every couple of years there will be a shiny new project that wants to fix the issues with existing ORMs and the cycle goes on and on. However, with SQL, you can easily transfer the knowledge across different tooling / programming languages.
  • We always fall back to raw SQL when in need of complex querying. And the data layer can quickly become multi-faceted and low DX.
  • So, fallback to SQL / Query builder may be is the better option if we are looking for long term.
• Architecture evolves with the time, today a monolith may become a micro-service architecture down the road.
  • ORMs may become the bottleneck (or at least there is a high chance of additional effort to migrate) for this endeavour.
  • SQL / query builders allows us to stay flexible as much as possible in this regard. There will be some effort to migrate but I strongly believe it would be less effort comparing to ORMs.
• More abstractions / layers means performance degradation + debugging troubles
  • We had this with not knowing where the issue is with entity cache in Document State module ?!?
  • On the other hand, If we are worried about every millisecond, NodeJS may not be the right choice in first place
• ORMs quickly becomes bottleneck when you especially need more control over a query
  • Those queries would be replaced with raw SQL always 😞
• Dynamic query capability is hard to achieve with raw SQL
  • So, we must keep raw sql as less as possible. query builders can solve this to some extent and has better DX to follow the code (readability)

Keeping these things aside; I believe we should consider the following checklist when choosing a future ORM / query builder library.

• Writing models (a.k.a types)
  • Code first --> map to database schema
  • There should not be any intermediate step to generate types from a handwritten DSL schema (e.g. Prisma)
• Type safety
  • Preserving a correct type when selecting specific columns
  • Mixing a raw statement into a query with the ability to specify its type
  • Knowing which relations were included
  • Auto completion --> DX
  • runtime validation ?!?
• Easiness of use for more complex cases
  • Complex joins
  • Not introducing N+1
  • Reduce duplicate joins (--> Prisma1 😡 )
• Raw SQL
• Transaction support
  • a big plus if there is a nested transaction support as well
• Logging support
  • Able to see the sql queries
  • connection pool stats
  • A detailed stacktrace is a big plus (ref: https://github.com/gajus/slonik#user-content-slonik-debugging)
• Performance
• Retry-ability
  • query retry, transaction retry
• Middleware support
  • so that we can enable CDC events and etc... (if needed)
• Schema Migrations
  • good to have. A big plus if the migrations are inferred from the models. Otherwise, we may need some kind of check in the CI to ensure there is no drift.