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!



We create a server.js file on a dedicated folder to implement the server itself. A simple code similar to this one from Express documentation will be enough for now.

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.

An script to run the server with nodemon will be needed, so we add a new line on package.json file, located on the root of our project. After that we can execute the script with npm start .

Knex, the friend of our database

Executing npx knex init a template file called knexfile.js will be generated on the root of our project. We modify it with a simple development configuration:

As we can see, we are providing to Knex the URL to connect to our database and also the directories where our migrations and seeds will be stored. TheDATABASE_URL variable must be defined on an environment file .env like this:

For security, include this .env in the .gitignore of any git repo

The URL parameters must refer to an existing database in PostgreSQL. In order to not getting too long, we will create a simple database that simulates a market storing products with a category . This can be created as follows:

The commandsnpx knex migrate:make product and npx knex migrate:make categorywill create two migration files that we will use to define our database tables. Another migration npx knex migrate:make product_update will be necessary to update a table defining a foreign key. We modify it to our needs:

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

As you can see, our tables are very simple but multiple data types and functionalities for building schemes can be found on this documentation. Executing npx knex migrate:latest our tables will be created.

Similarly, Knex allows us to seed a table too with the use of the command npx knex seed:run. For example we can define a seed file (npx knex seed:make seed_categories)to populate the categories of the products.

To finish up with this configuration, we need a knex.js file to hold the single instance of the Knex module with the correct environment config.

Why Socket.IO?

This avoids timeout problems in case that a transaction takes longer than expected. Therefore, efficiency is improved and we also protect ourselves from a possible error of connection conditions an entire synchronization.

For this purpose, the request/response paradigm on which HTTP is based does not serve us, so we use the Socket.IO library, which enables real-time, bidirectional and event-based communication.

Knowing that, we modify server.js to work with this tool. We alsocreate and import a file called ioHandler.js in charge of managing the logic and namespaces (endpoints) of Socket.IO separately from the server file.

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

Meet the boss: product controller

For every action from the client, the controller gets a result from the specific service (in the case above, CREATE) and sends it back to him. When analyzing the services, we will see that this result is formed by some info and a transaction (this is why has two components).

Both components are passed to handleFinishTransaction function, that faces three possible situations:

  1. The action was not completed, so its status is “NACK”. We rollback the transaction, so any change related to that action is performed on the database.
  2. 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.
  3. 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

Here is where we use the instance of Knex that we mentioned previously. We obtain a reusable transaction object, building the query from it. The attributes to insert are taken from the received action. We also define an empty action result, filled later.

Knex provides a very simple but extense syntax for building queries. In this case we insert an entry to products table, returning the ID and the name (if everything OK) or an error. The transaction object (that will be committed or rolled back by the controller) is also returned.

The complete code of the server can be found HERE.

Is the client is always right?

With the code below, we connect a client to the server’s /sendActions namespace. Once communication is established we emit a sendProductActions event with a JSON file on it. When acknowledged results are received, we wait 3 seconds (only to simulate net delay) and then we emit a “FIN” for each action.

The JSON above contains several CREATE actions for adding multiple products to our database. Obviously, we can try with the other action types. Nevertheless, an important think to remark is that we cannot read, update or delete a register that is being created or accessed. So although it may not make much sense, we cannot create and update a certain register in the same syncronization, for instance.

To conclude, I want to say that the scope of this article only reaches a basic implementation with four functions for the product entity. Despite this, the amount of possibilities is huge. More tables and relations can be defined implying new controllers, services and actions. The synchronization exemplified here must be expanded too. For example, it can be unidirectional or bidirectional, we may need versioned data and so on. My final degree thesis derstands these aspects in more detail.

Thats all folks, happy coding!

PS: Shout-out to Lauren Fazah, Ali Spittel and Aman Mittal. Their articles related to this topic really helped me.