How to access MariaDB query result in Node.js?

Question

I've recently started to teach myself Node.js to create websites and I need to retrieve a SELECT query from a mariaDB in order to ultimately use this data as a JSON response to a POST request from a web page on my client browser. I've searched the web and stackoverflow but not found any relevant solution.

I have created Node/Express code which successfully retrieves the database query and logs it to the server console - but I am unable to successfully send the query result back to the client. What am I missing / doing wrong?

This is the server side code:-

(the code for get_publishers & accessDB functions is closely based on an example from 'MariaDB Connector/Node.js (Promise API) Examples')

const express = require('express')
const mariadb = require('mariadb')

// get list of publishers
function get_publishers(conn) {
    return conn.query("SELECT * FROM publishers")
}

// get database SELECT query
async function accessDB() {
    let conn
    let queryResult
    try {
        conn = await mariadb.createConnection({
            host: "localhost",
            user: "****",
            password: "****",
            database: "*****"
        })
        queryResult = await get_publishers(conn)
        console.log('db query complete')
    }  catch (err) {
        console.log(err) 
    } finally {
       console.log(queryResult)
        if (conn)  conn.end()
        console.log('db connection closed')
        return queryResult
    }
}

const app = express()
app.use(express.static(__dirname + '/public'))

const http = require('http');
const fs = require('fs');
const port = process.env.PORT || 3000;

app.get('/listPublishers', (req, res) => {
    res.type('text/plain')
    const now = new Date()
   res.send( now + ' query result = ' + JSON.stringify(accessDB()))
//   res.send(accessDB())
})

app.listen(port, () => console.log(
    'bookStore 16:26 started with accessDB on http://localhost:${port}; ' +
    'press Ctrl-C to terminate.....'))

This is the output to the console log. It shows the query result is as expected.

$ node bookstore.js
bookStore 16:26 started with accessDB on http://localhost:${port}; press Ctrl-C to terminate.....
db query complete
[
  { publisherID: 1, publisherName: 'Jonathon Cape' },
  { publisherID: 2, publisherName: 'W. W. Norton & Co' },
  { publisherID: 3, publisherName: 'Corgi Books' },
  ...
  { publisherID: 10, publisherName: 'Gollanz' },
  { publisherID: 11, publisherName: 'Continuum' },
  meta: [
    ColumnDef {
      collation: [Collation],
  ...
      type: 'SHORT'
    },
    ColumnDef {
      collation: [Collation],
  ...
      type: 'BLOB'
    }
  ]
]
db connection closed

..and here is the result on the client browser

Mon Oct 03 2022 15:15:14 GMT+0100 (British Summer Time) query result = {}

.. which, unless I've misunderstood, seems to be an empty object.

Answer 1

Your accessDB() call is an async function, you need to call res.send() after it is completed:

accessDB().then(data => res.send(new Date() + ' query result = ' + JSON.stringify(data)))