Node.js, Express.js and @mysql/xdevapi based Boilerplate code?

Question

I am using @mysql/xdevapi in my Express.js application for creating REST web APIs.

My current way of using @mysql/xdevapi is

const mysqlx = require('@mysql/xdevapi')

const options = {
    host: 'mysql',
    port: 33060,
    password: 'password',
    user: 'root',
    schema: 'DB_name'
}

const session = await mysqlx.getSession(options)

var getData = async function (req, res) {
    try {
        let { id } = req.body
        var data = (await session.sql(`SELECT * FROM data_table WHERE id = ${id}`).execute()).fetchAll()
        res.json({ success: true, data: data })
    } catch (error) {
        res.json({ success: false, message: "Error in getData" })
    }
}

var app = express()
app.post('/getData', getData)

var server = app.listen(config.express.bindPort, config.express.bindIp, function () {
    console.log(`Express listening on port ${config.express.bindIp}:${config.express.bindPort}`)
})

This setup give me error after few hours

Error: This session was closed because the connection has been idle too long. Use "mysqlx.getSession()" or "mysqlx.getClient()" to create a new one.

What should be right way to use @mysql/xdevapi with Express.js? Any good Boilerplate code?

Answer 1

You are creating a connection to the database on startup. The connection is always bound to the value specified by the wait_timeout variable in the MySQL server. This means that eventually, if there is no activity, the server will close it.

In your specific case, I would suggest you use a connection pool and then each handler should acquire its own connection from the pool. In theory, you can do the same with standalone connections, but it's way more costly.

So, you can do something like the following (simplified):

const mysqlx = require('@mysql/xdevapi')

// ...

const pool = mysqlx.getClient(options)

var getData = async function (req, res) {
  let session
  
  try {
    // acquire a connection from the pool
    session = await pool.getSession()
    // ...
    let { id } = req.body
    var data = (await session.sql(`SELECT * FROM data_table WHERE id = ${id}`).execute()).fetchAll()
    res.json({ success: true, data: data })
  } catch (error) {
    res.json({ success: false, message: "Error in getData" })
  } finally {
    if (session) {
      // ensure the connection returns to the pool
      await session.close()
    }
  }
}

// ...

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js