I'm using Node.js along with SQLite3 to talk to my database. I can successfully add new data and read existing data. My problem is, I cannot retrieve data that I add through my interface unless I restart my Node.js server.
I realise the database is likely locked or the functions to re-read the new data need to be altered but I just cant for the life of me work out how.
Please excuse the code, it's designed to keep track of how much milk has been expressed over time and graph it.
server.js
var express = require('express')
var tempstats = require('./db.js')
var path = require('path')
var app = express()
// var db = require('./db.js')
var sqlite3 = require('sqlite3')
var db = new sqlite3.Database('web-app.db')
var publicPath = path.resolve(__dirname, "public");
app.use(express.static(publicPath));
// Parse URL-encoded bodies (as sent by HTML forms)
app.use(express.urlencoded());
// Parse JSON bodies (as sent by API clients)
app.use(express.json());
// All general page links should be listed below
app.get("/", function(request, response) {
response.sendFile("/index.html", {});
});
// Access the parse results as request.body
app.post('/', function(request, response) {
console.log(request.body.user.leftBrest);
console.log(request.body.user.rightBrest);
db.run('INSERT INTO milk VALUES (NULL, ?, ?, CURRENT_TIMESTAMP)', [request.body.user.leftBrest, request.body.user.rightBrest], function(err) {
if (err) {
console.log("There's another error!" + err.message);
} else {
console.log('Record successfully added with id: ' + this.lastID);
response.sendFile(__dirname + "/public/index.html");
}
});
});
// SQL data interaction functions from db.js
app.get('/left', function(req, res) {
res.send(tempstats.lastAmountLeft + '')
console.log(tempstats.lastAmountLeft + '')
})
app.get('/right', function(req, res) {
res.send(tempstats.lastAmountRight + '')
console.log(tempstats.lastAmountRight + '')
})
app.get('/selectedTemp', function(req, res) {
res.send(tempstats.selectedTemp + '')
})
// Json testing sendfile is different to sendFile
app.get('/json', function(req, res) {
res.sendfile('./test.json', {})
})
// 4xx Errors are served from here
// app.use(function(req, res) {
// res.status(404)
// res.render('404.html', {
// urlAttempted: req.url
// })
// })
// Server listen code
var port = 3000
app.listen(port, function() {
console.log('The server is listening on port ' + port)
})
db.js
lastAmountLeft and LastAmountRight need to update but don't. (I'm also using their values inside my html page)
'use strict'
var sqlite3 = require('sqlite3')
// var db = new sqlite3.Database('web-app.db')
let db = new sqlite3.Database('web-app.db', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQlite database.');
});
const tempstats = {}
db.each('SELECT * FROM milk WHERE rowid = 3', function(err, row) {
if (err) {
console.log('Oh no!' + err.message);
} else {
console.log('Row ID: ' + row._id + " shows the left breast had an expressed volume of: " + row.left + "ml")
tempstats.specificAmountLeft = row.left;
}
})
db.get('SELECT * FROM milk WHERE date order by date desc limit 1', function(err, row) {
if (err) {
console.log('Oh no!' + err.message);
return;
}
console.log(row)
console.log("The last expressed volume from the left breast was: " + row.left + "ml")
tempstats.lastAmountLeft = row.left;
})
db.get('SELECT * FROM milk WHERE date order by date desc limit 1', function(err, row) {
if (err) {
console.log('Oh no!' + err.message);
return;
}
console.log(row)
console.log("The last expressed volume from the right breast was: " + row.right + "ml")
tempstats.lastAmountRight = row.right;
})
module.exports = tempstats
// module.exports = db
The answer was actually quite easy, I can't believe I missed it. I just wrapped my "lastAmountLeft" and "lastAmountRight" functions in a setInterval and call it every X seconds. My page now updates with the latest figures.
setInterval(function() {
db.get('SELECT * FROM milk WHERE date order by date desc limit 1', function(err, row) {
if (err) {
console.log('Oh no!' + err.message);
return;
}
console.log(row)
console.log("The last expressed volume from the right breast was: " + row.right + "ml")
tempstats.lastAmountRight = row.right;
})
}, 1000)