Member Avatar for B_3

Hi,

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.

Approach 1
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

Approach 2
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

Approach 3

  • 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

Thoughts?

Edited by B_3
  • 2 Contributors
  • 1 Reply
  • 2K Views
  • 6 Minutes Discussion Span
  • Latest Post Latest Post by Dani
Member Avatar for Dani

I rolled my own MySQL-based ORM for DaniWeb. Not every table, but most tables, have a corresponding class, where methods invoke changes in the database. When it makes sense, we use the ORM to make changes. Other times, the class methods invoke SQL statements directly when it's more efficient to do so. For tables that dont't really make sense for our ORM, we use basic SQL statements and stored procedures.

commented: In short, what was your reasoning for doing this? +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.