I am using node and refactoring an older database. Therefore, i want to ensure i incorporate correct practices from the start.
I know that this is a debated topic and that there is not nessecarily a correct answer but i want to see what people have to say about the topic. I have done my fair share of googling and it seems pretty split.
Currently, i am using mysql2 as my abstraction layer and writing the SQL.
Use an ORM such as Bookshelf or Sequelize.
- I see this as future proofing the database by placing it into OO classes (Basically a duplication of the database schema in code format)
- As it is a duplication there is redundancy and a longer time to end users
- Ive read that complicated SQL statements get lost in these ORMs
- BUT - if i jump ship and move to another database (currenly MariaDB - which is used by google) i am covered
- BUT BUT what is the likelihood of transferring databases
Use mysql or mysql2 as the abstraction layer
- Keep basic SQL statements inline and write stored procedures(SPs) for more complicated SQL
- Eliminates the duplication caused by ORM
- Pretty straighforward
- Easy to version the inline SQL
- I would 100% agree that the database is the business, the api is method to interact with the database and the UI is a visual extrapolation of the database
- BUT i need people that need to know SQL
- Similar to Apporach 2 but i keep everything in SPs
- I would need to keep detailed records of the scripts calling the SPs so changes are reflected correctly