Mysql + Node/Express

[0llieW]

I am using mysql with Express and Node.
Am I going about things the right way? I have never set up a mysql connection before…

router.get('/people', (req, res) => {
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'dbuser',
    password: 'password',
    database: 'dbname'
  });
 
  connection.connect((err) => {
    if (err) throw err;
    console.log('Connected to mysql!!!!');
  })
  connection.query("SELECT firstname FROM users", (error, results, fields) => {
    console.log(JSON.stringify(results));
 res.render('people', {people: results});
 connection.end();
  })
})

Is this the best way to be going about things?

[fretburner]

Hi @0llieW,

Unlike PHP, where the entire app is reloaded for each request, a Node app stays in-memory between requests - this means that every time someone visits the /people route, the app is going to try to create a new DB connection instead of reusing the existing one.

I would create the connection when initializing the app, and only start serving requests if the DB connection is successful:

const express = require('express');
const mysql = require('mysql');

const app = express();
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'dbuser',
  password: 'password',
  database: 'dbname'
});

app.get('/people', (req, res) => {
  connection.query("SELECT firstname FROM users", (error, results, fields) => {
    res.render('people', {people: results});
  });
});

connection.connect((err) => {
  if (err) throw err;
  app.listen(3000, () => console.log('Example app listening on port 3000!'));
});

// close connection when app is shutdown
process.on('SIGINT', () => connection.end());