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?

Paul · Accepted Answer · 2017-12-31 16:25:09Z

5

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.

answered Dec 31, 2017 at 16:25

Paul

36.4k12 gold badges98 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Vicente · Accepted Answer · 2017-12-31 16:23:52Z

I think is always a good practice to separate DB code from business code, and from API code if it exists.

Creating these different layers, you get different advantages:

Testing every layer separately (with unit tests), mocking other layers. With this you can detect errors very fast when you make changes in your code.
You can change very easy your DB connector, or even your database, without impacting your business code (e.g. MySQL by MongoDB)
You can change your API or add a new one without changing your business code (e.g. REST API by/and GraphQL)

If you want to see a project with this layers, we published recently a simple project that allow you to create a collaborative newsletter. You can check backend part, which has db folder, domain folder and api folder. Those are the 3 layers I was talking about:

Colaborative newsletter

Hope it helps you

Collectives™ on Stack Overflow

Nodejs - SQL queries in separate file?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related