How to perform contingent SQL query, package result as JSON, and send back to client?

Question

How to perform contingent SQL query, package result as JSON, and send back to client?

I'm using node.js, sqlite3 (with anyDB) and javascript on the front end.

I made a table:

conn.query('CREATE TABLE messages (id INTEGER PRIMARY KEY AUTOINCREMENT, room TEXT, nickname TEXT, body TEXT, time INTEGER)').on('end', function(){
        console.log('Made table!');
    });

I inserted stuff into the table:

conn.query('INSERT INTO messages VALUES ($1, $2, $3, $4, $5)', [null, request.params.roomName, request.body.nickname, request.body.message, null]).on('error', console.error);

In the client-side js, I wrote this code:

window.addEventListener('load', function(){
     var intervalID = setInterval(updateMessages, 4000);
}, false);

function updateMessages() {
    var req = new XMLHttpRequest();
    req.open('GET', '/' + roomName + '/messages.json', true);
    req.send();
    //GET ACCESS TO RESPONSE IN ORDER TO ADD IT TO THE PAGE
}

On the server side, I wrote this code:

app.get('/:roomName/messages.json', function(request, response){
    var roomName = request.params.roomName;
    var messages = conn.query("SELECT ALL body FROM messages where room="+roomName+";");
    response.send(messages);
});

Do my SQL statements look correct? How do I write the select statement so that it doesn't crash the server if the room isn't in the database yet?

Finally, how do I package my response and send it back to the public js file and then to the page?

javascript
node.js
sqlite3

Answer 1

Do my SQL statements look correct?

Yes.

How do I write the select statement so that it doesn't crash the server if the room isn't in the database yet?

You need to do some error handling. Try using a callback with something like this:

var sql = "SELECT ALL body FROM messages where room="+roomName+";";
conn.query(sql, function(error, result) { 
  if(error) {
    console.log("There was an error.");
  }
  response.send(result);
});

Finally, how do I package my response and send it back to the public js file and then to the page?

You are sending an object returned from the SQL query. Your client needs to expect that object. I'd suggest looking at how to send and receive AJAX requests from the client. You won't be "updating the public js", but rather sending the message and your client will expect to receive it.