Nodejs - SQL queries in separate file?

Question

I would like to know what do you think about the following task. I want to write data from JSON object in a database. I would like to separate the SQL logic with the business logic.

I read t'hi strategy has not good performance, when the file js contain a lot of queries.

Which approach is the best practice in your opinion? Can you provide a little example?

Answer 1

Your performance question is definitely a 'race your horses' scenario (i.e. test it and see). But in general, if you're going to do this I'd simply export an object with all your named queries like so:

module.exports = {
  getAllUsers: "SELECT username, email, displayName FROM users;",
  /* other queries */
}

Your calling code can then just require that file and get what it needs:

const queries = require('./db/queries');
queries.getAllUsers // <-- this is now that string

Performance should be about as good as it gets, since your require cache will ensure the file is only read once, and a key-based lookup in JS is pretty quick, even with a thousand or two entries.