[Database] (Re)Evaluierung - NestJS/Node-Postgres/Database Driver

[dschlarmann]

Short:
Currently we are using NestJS (or the TypeOrmModule) for building and querying database resources in our first service version. From the first look, it's not the most "straight-forward" looking approach as SQL statements are encapsulate in separate functions.

Details:
For simpleness/complexity it could be worthwhile to reevaluate the database (driver) usage. Although the TypeOrmModule might be the most straight forward looking technique within NestJS Framework, other approaches are possible, too.
@artmarks @aspringer1: Could you please check if there are any advantages to use plain Node-PG driver?

ToDo:

• Node-Postgres: https://node-postgres.com/
  • Of course this is only suitable for postgres itself...
• NestJS: https://docs.nestjs.com/techniques/database
• Sequelize: https://sequelize.org/
• Other framework for integrating different database and which encapsulte the conncetion handling
  • ❗ Keep in mind that multiple database should be integraded (cf [Backend] Evaluierung - Integration von mehreren Datenbanken #2 )

Points to evaluate:

• Performance
• ConnectionPooling
• Flexibility
  • Easy integration of other other databases ystems
• Timeout parameter
  • QueryTimeout
  • ConnectionTimeout
• other?

[artmarks]

Approach basic connectors

• use Node-Postgres: https://node-postgres.com/ for postgres & other for oracle, mySQL and others

Advantage:

• max performance possible

Disadvantage

• Every connector has to be integrated by ourselves.
  • Connection pooling
  • Query-Syntax
  • Query execution
• Every query needs to implemented multiple times for all supported query languages

Comments

• Users need a good knowledge of the database query language.
• There is no object-oriented design by default

Approach Object relatationsship manager

• typeOrm
• Sequelize
• MikroORM, Knex.js , Prisma

Advantage

• Max flexibility
  • Multiple databases are supported.
  • Connection pooling is supported if available
  • Same query language for all databases
• Clean code design
  • Maintainable code

Disadvantages

• Performance is slower then raw sql
  • Complex queries can be specially slow

Comments

• This approach is complete object-oriented

Approach Hybrid

• Use the orm-framworks for database integration
• Use the orm-framworks for object response
• Use raw sql queries

Advantage:

• Max performance possible
• Comfortable database integration

Disadvantage

• Every query needs to implemented multiple times for all supported query languages

Comments

• the main aspect of clean and maintainable code gets lost
• Database result has to be carefully integrated back to object relation

Conclusion

Depending on most important factor of this API:

Performance:

• Hybrid solution
  • uses fastes approach and has confortable database integration
  • more effort in querying
    Maintainable code:
• ORM framework
  • clean, maintanable code
  • slow

Speed comparison:

Average speed of select * from adressen_p for about 50.000 datasets

• TypeOrm: 363.25 ms
• raw sql: 173.25 ms

[dschlarmann]

Result

We are using the Hybrid Approach.

• For accessing and querying the overall data, we use the ORM as this is the preferred way for the integration within NestJS.
• For (edge) cases, where the ORM approach might be unfavorable (in regard to complexity and performance), we will use "native" way.