Synchronization server with a sick combo: Express, Socket.IO, PostgreSQL and Knex.js

In this article I will try to guide you on implementing a simple but scalable synchronization server. This server, based on node.js will be able to receive and process a bulk of CRUD requests from a client, to then acting in consequence and replying back with individualized responses. We will use several tools: The framework Express as middleware, PostgreSQL as DBMS, Knex.js to create some data and access it in a transactional way and finally Socket.IO for an efficient client-server communication. Get ready for takeoff!


First of all we will need to install node.js, npm and PostgreSQL. I’m sure that Google will explain better than me how to do so. I will consider this prerequisites as being met if you continue reading.


We can now initialize a node.js app on an empty directory and install the required dependencies.

Note: For a secure connection, an HTTPS communication should be defined. This is not the case since I wanted to keep the article as simple as possible.

Knex, the friend of our database

Despite what this article states (worth reading), we may want some abstraction (or help) when creating and dealing with the database. ORM’s like Sequelize are pretty simple to use, but in my opinion, our code gets unnecessarily more complicated. We can find an alternative on Query Builders, that work with a language closer to SQL and do not decrease performance so much. Knex.js was my choice.

For security, include this .env in the .gitignore of any git repo
increments() defines an auto-incrementatitve integer used as primary key ID column
If we want to reset our database with knex migrate:rollback, “down” function is used
We define a simple FK on product that refers to category

Why Socket.IO?

We assume that we want a server capable of efficiently managing the synchronization of data of a potentially high volume. So even though messages from the client to the server could be bulk, they had to be individual in the opposite direction. That is, despite the client must be able to send multiple demands in one unique request, these must be answered exclusively. With that, when an action requested by the customer has been carried out (whether or not satisfactorily), the client receives a response without having to wait for the rest of the actions to complete.

io.of() is used to create a Socket.IO namespace called “/sendActions”

Meet the boss: product controller

As we can see above, we use a function of productController.js to receive the block of actions from the socket. Later on we will see that this actions are actually a JSON file. It is also responsible for classifying the actions (create, read, update or delete) and calling the indicate service. Here a short version of this code:

  1. The action was completed with status “ACK”. A timer starts on the server, that waits for a “FIN”. This message is meant to confirm the reception of the action result and finish the transaction. Unfortunately any “FIN” message from the client is received, so we rollback the transaction too.
  2. Same as 2 but now we receive the client’s “FIN”. We commit the transaction, making the changes of that action permanent on the database.

At your service sir

Finishing up with the explanation of the server code, lets see how the createProduct service performs the CREATE action returning then a result.

Is the client is always right?

To test our server we can use a very simple Socket.IO client. We only have to initialize another node project as we did before and install the package


Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store