Real Time App With Socket.io and Postgresql
Example Code: https://github.com/Nahid5/sockets.io-and-postgres
Why?
Searching for Socket.IO examples can lead to many examples of people creating real time chat applications, and not many other things. After quite a bit of searching, I was unable to find anyone using Socket.IO to update a database in real time. I also ran into issues with libraries not supporting sockets, which led me into implementing many features that would have otherwise been available if I were using REST.
Because of this I created an example real time database updating app using VUE for the front-end and node for the backend.
What The Example App Can Do
This example app will allow users to iterate through a table which contains an id, name, and notes field. Users can also:
- Update the notes field in real time, so other clients that are connected, will immediately see changes made to the notes field
- Apply filters that are done through case insensitive regex
- Change how many elements are displayed on the page
- Iterate through multiple pages of results
Back-end Setup
Things you will need to install on the backend are express, pg, and socket.io. One other module I would highly recommend is dotenv or something similar to avoid credentials hardcoded credentials. With dotenv, all you have to do is create a .env file locally and add .env to gitignore.
After all the modules are imported and set up, you can use sockets to listen in for connecting clients. We will also create all of the events that the back-end will be listening/emitting inside the connection event.
io.on('connection', function(socket) {
//Alerts us when someone successfully connects
console.log('User Connected');
//Alerts us when someone disconnects
socket.on('Disconnect', () => {
console.log('User Disconnected')
})
})
First let’s implement querying the data. This event will be triggered when a client emits getAllData. In here we can query the back-end and apply filters to fit what the front-end expects.
//Get everything from a table
socket.on("getAllData", page => {
//Base query
var queryText = 'SELECT id, name, notes FROM public."Notes" ';
//Position and valus are for parameterization. Help with sql injection.
//If you want to add filters, you can add them here and follow the pattern
var position = 1;
var values = [];
//Filter by text
if(page.filters.filterText.length > 0) {
var fieldsToSearch = ["name", "notes"];
if(!queryText.includes("WHERE")) queryText += "WHERE ";
else queryText += "AND ";
fieldsToSearch.forEach(element => { queryText += element + " ~* $" + position++ + " OR "; values.push(page.filters.filterText)}); //Adds the where statement and prepares teh query to do case insensitive regex search
queryText = queryText.slice(0, -3); //Remove OR
queryText += " "
}
queryText += 'ORDER BY id ASC ';
//Offset and fetch next is needed when the database is huge.
queryText += "OFFSET $" +position++ + " ROWS FETCH NEXT $" + position++ + " ROWS ONLY;";
values.push(page.page.offset);
values.push(page.page.limit);
pool.query(queryText, values, (err ,res) => {
if(err) throw err
socket.emit("allData", res.rows);
})
})
The queryText variable will initially hold our base query which will get what we want from the database. We can append to the query text by adding statements such as the WHERE clause. The filters use the positions and values variables to dynamically add filters to the query. The position will always be a positive integer and it will correlate to what you are trying to filter. The values will hold the user inputs and will correlate to the positions. ([1,2,3,4…]). This is used to parameterize everything and reduce the risk of sql injection and escape user inputs for special characters.
Next, let’s create a helper event that will aid in the front-end implementing pagination. The following event will help prevent users from jumping or incrementing to pages larger than how many pages we actually have. Once getDataSize is emitted from a client, it will count the number of rows in the table. The first index is taken from rows because the SELECT command will return an array of objects with only one value, so we just grab the only value. Then we emit the results back with the name tableSize.
socket.on("getDataSize", () => {
pool.query("SELECT COUNT(*) as count FROM public.\"Notes\";", (err ,res) => {
if(err) throw err
socket.emit("tableSize", res.rows[0]);
})
})
If we want to update something on the database we can do something like this:
socket.on("updateNotes", data => {
pool.query('UPDATE public."Notes" SET notes = $1 WHERE id = $2;', [data.notes, data.id] ,(err ,res) => {
if(err) throw err
io.emit('dataIdUpdated', data.id);
})
})
In the example above, we take the row which is represented by the data variable and we get the notes and id variable from it and use those to update the notes using the id. Then we emit to all of the clients the id of the row that was updated. The front-end will take the id and check if the id exists in the current batch of rows, and if it does, it will ask for an update by emitting getAllData.
Front-end Setup
The front-end uses socket.io-client, vuex, and vuetable-2. Vuex and vuetable-2 is not necessary, but I would encourage the usage of it to help manage the socket when there are multiple views that we can switch between. If Vuex is not used, you will need to create the socket client multiple times, and every time you switch views, a disconnect event will trigger, which will terminate the current socket connection and establish a new one. I used vuetable-2 to help display my table. There are a few things vuetable specific that I need, such as the field definitions, and the stylings since vuetable uses semantic instead of bootstrap.
First we will need to set the variable.
data() {
return {
data: [],
fields: FieldDef,
css: SemanticStyle,
filters: {
filterText: ""
},
page: {
limit: 10,
offset: 0,
index: 0,
pageSize: 10,
tableSize: null
}
}
}
The data array will store all of the rows retrieved from the database, fields will store the field headers, css is needed to make our table look good, and the page object will store information required to display/navigate the database. The limit value is how many rows are requested from the database. offset is used when a user changes to another page and the value is how many rows to skip when getting the next batch of values. index is what page you are on, pageSize The data variable will also need to be watched for updates and with vegetables you need to refresh the table once data is updated to update what is displayed. pageSize is how many rows to display, and tableSize is the number of elements that are in the table. The filters object will store all of our filters. Whether we filter by text or specific values based on a row, we can put all of our filters here for easy access.
Next we need to watch for changes and update our view accordingly.
watch: {
data() {
this.$refs.vuetable.refresh();
},
filters: {
handler: function() {
this.setFilters();
},
deep: true
}
},
The data variable changes will tell us if the data being shown has changed, and if it has, we refresh the table. Next we also want to watch for changes in all of our filters. This is an ease of access feature that allows for automatic filtering by watching all of the variables in the filters object and calling the filter method on each change.
Next, let’s implement getting the table size, and getting the initial batch of rows when the page is loaded. This can be done inside the mounted function and adding these lines in.
store.state.socket.emit("getAllData", {page: this.page, filters: this.filters});
store.state.socket.emit("getDataSize");
We also need to create all of our listeners which will listen for events sent by the back-end. We need a listener to listen for the table size, data being returned from the back-end, and one more to listen for any rows that were updated.
//Check if the row updated is in the current view and ask for an updated rows if it is
store.state.socket.on("dataIdUpdated", data => {
if(this.data.some(item => item.id === data)) {
store.state.socket.emit("getAllData", {page: this.page, filters: this.filters});
}
})
//Listen for the latest data
store.state.socket.on("allData", data => {
this.data = data;
})
//Listen for table size
store.state.socket.on("tableSize", data => {
this.page.tableSize = data.count;
})
In the dataIdUpdated we check if the id of the row returned from the back-end exists in the current data set, and if it does, request an update to refresh the data.
The final thing needed is handling pagination. If the user wants to go to the next page, they can use the following:
nextPage() {
this.page.index += 1; //Increment page number
if(this.page.index > this.page.tableSize) {
this.page.index = this.page.tableSize;
}
//Offset is set to the the current page number * the number of elements shows on the current page, so you get shown rows you have not seen before
//Limit is how many to get
//So offset is the skip that many rows, and limit is how many to get
this.page.offset = Number(this.page.index) * Number(this.page.pageSize);
this.page.limit = Number(this.page.pageSize);
store.state.socket.emit("getAllData", {page: this.page, filters: this.filters});
}
You can see, after changing all of the variables, we request the next page with our emit. The function prevPage is the same except we need to use this.page.index -= 1. If we want to jump to a page, we can use the jumpToPage function which is the same as the nextPage, except we omit the first line in code.
If we want to change how many rows we want to display per page we can use the following code:
setPageSize() {
if(this.page.index < 0) this.page.index = 0;
this.page.offset = Number(this.page.index) * Number(this.page.pageSize);
this.page.limit = Number(this.page.pageSize);
store.state.socket.emit("getAllData", {page: this.page, filters: this.filters});
}
This will set the new page limit (how many to display per page) and we also get the offset so if the user is many pages ahead, they can increase the number of rows shown, without having to go back to the first page.
Next let’s create the method to update the notes.
updateNotes(data) {
store.state.socket.emit("updateNotes", data);
}
This is pretty straight forward, we take the row data and emit that back to the backend which will take the id and notes from the data object and update the row’s notes.
Lastly, we have the filtering method.
setFilters() {
this.page.index = 0;
this.page.offset = 0;
store.state.socket.emit("getAllData", {page: this.page, filters: this.filters});
}
In this we set the index and offset to 0 so we display the results starting from the beginning.
And that’s it. Implement socket.io is a very simple process and I hope more developers will pick this up or websockets and use it for their own projects.