Best practice to accessing mysql database by nodejs

Question

Currently I'm running a node js backend with express to access the mysql database from the client. Now I'm refactoring my code to put the "fat" things from the client also to the nodejs backend. My first idea is to keep the express route like:

ModuleA:

router.get('/getitem/:code',(req, res) => {
let sql = `SELECT * FROM xyz WHERE CODE = ${req.params.code}`;
let query = db.query(sql, (err, result) =>{
  if(err == null){
    console.log(result);
    res.send(result);
  }else{
    console.log("Error by getting item from db: " + err);
    throw err;
  }
 });
});

And access it via http request by a other module if needed:

function geodataByLocationcode(locationcode){
 request({
  method: 'GET',
  url: "http://" + server_connection.host + ":" + server_connection.port + '/getitem/' + locationcode,
  headers: {
    "Content-Type": "application/json"
  }
 },function(error, response, body){
 ....
 }

But is this the best method to do this? Or would it be better to access the db in a more direct way since the request now also comes from the backend?

Well In my honest opinion have database access in our controllers is not a good approach... why not have a service module to handle database queries? Because having db dependencies in your controller it will make hard to unit test... — Marco Talento
– Marco Talento, Commented Oct 10, 2017 at 13:44
Just a big security warning! Don't EVER send valyes from the request into the database query like you are doing in the above code with req.params.code. Users can easily send something like "1; DELETE FROM users;" — frodeborli
– frodeborli, Commented Apr 18, 2021 at 0:33

Marco Talento · Accepted Answer · 2017-10-10 14:34:51Z

7

You can find a good example how to use MySql and Node.js using native drivers (without any query builder like knex or ORM like bookshelf).

Dependencies:

expressjs 4.x
mysql2

expressjs 4.x

The core concepts are:

Have a separate file to handle database connection (https://github.com/Talento90/organization-api/blob/master/organizations-api/src/database.js)
Always use a connection pool
Separate the database logic from the API controllers (it makes them decoupled from database and easier to test)
If you are using the latest version of Node.js also shows you how to use async/await with express.js and mysql

Example: https://github.com/Talento90/organization-api/tree/master/organizations-api/src

answered Oct 10, 2017 at 14:34

Marco Talento

2,4052 gold badges22 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Best practice to accessing mysql database by nodejs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related