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.
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)))