CODEWITHSUNDEEP
javascriptnode.js
MrScf
MrScf

Reputation: 2497

Nodejs - SQL queries in separate file?

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?

Upvotes: 8

Views: 4330

Answers (2)

Paul
Paul

Reputation: 36339

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.

Upvotes: 5

David Vicente
David Vicente

Reputation: 3121

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

Upvotes: 3

Related Questions