vi
new
+6
Avatar
NGUYỄN ANH TUẤN @Clarence161095
21.9K 520 370
Đã đăng vào thg 3 6, 2023 3:05 SA 8 phút đọc
699

Connect to PostgreSQL with Node.js and automatically reconnect when the connection is lost and restored.

As container environments become more widespread, you may hear that applications and services can be operated more stably. While that is true to some extent, it's also important to have stable backend systems for the application to operate stably, and this aspect is often overlooked. This blog entry will give an example of such a discussion by introducing what points should be considered when using a relational database (PostgreSQL in this case) in an application that uses Node.js.

The code for connecting from Node.js to PostgreSQL can be easily implemented using an npm package called "node-postgres". Specific methods can also be easily found by searching online.

On the other hand, I think that designs based on microservices are becoming more common, especially in cloud or container environments. In such cases, it is necessary to anticipate that "PostgreSQL may enter maintenance mode (connection may be lost)." As an application, it requires a somewhat troublesome implementation that behaves like "there is a possibility that the database connection may be disconnected, and if it is disconnected, it should reconnect (and continue to reconnect if it fails)".

image.png

I was looking for a sample code that would work concretely, but I couldn't find it precisely, so I tried various things and made it myself. I was able to confirm that the application can also reconnect by starting a PostgreSQL image with Docker and stopping or restarting the container: https://github.com/dotnsf/postgresql_reconnect

I will be introducing code that assumes a connection and SQL execution based on the assumption that connection pooling is used (which is usually the case in general applications) with PostgreSQL.

【Operation check】

We will now introduce the steps to verify the application's operation using a PC with Node.js installed and a local docker.

First, obtain the entire source code. Either git clone from the above Github repository or download and expand the postgresql_reconnect/ project to your local machine.

First, let's run PostgreSQL with docker. We will create the DB at startup, but there is no need to create any tables or data, just start it up:

$ docker run -d --name postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=P@ssw0rd -e POSTGRES_DB=mydb -p 5432:5432 postgres

↑This command instructs to start the official PostgreSQL image as a container with the following options:

  • User name: admin
  • Password: P@ssw0rd
  • Database name: mydb
  • Public port number: 5432

It is assumed that the docker engine is installed on the local host. You can change the specified options and run it, but the sample source code is written on the assumption that the PostgreSQL instance is generated from here, so if you change it from here, please edit the sample code accordingly and execute it.

image.png

First, let's try running the code oldapp.js, which does not consider reconnection when disconnecting. By the way, the contents of oldapp.js are as follows:

//. oldapp.js
var express = require("express"),
  app = express();

var PG = require("pg");

//. PostgreSQL
var pg_hostname = "localhost";
var pg_port = 5432;
var pg_database = "mydb";
var pg_username = "admin";
var pg_password = "P@ssw0rd";

var pg_clinet = null;
var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database; //+ "?sslmode=verify-full";
var pg = new PG.Pool({
  connectionString: connectionString,
});
pg.connect(function (err, client) {
  if (err) {
    //. DB not running on first boot
    console.log("no db on startup", err.code);
  } else {
    console.log("connected.");
    pg_client = client;
  }
});

//. top
app.get("/", function (req, res) {
  res.contentType("application/json; charset=utf-8");
  res.write(JSON.stringify({ status: true }, null, 2));
  res.end();
});

//. ping
app.get("/ping", function (req, res) {
  res.contentType("application/json; charset=utf-8");
  var sql = "select 1";
  var query = { text: sql, values: [] };
  pg_client.query(query, function (err, result) {
    if (err) {
      console.log({ err });
      res.status(400);
      res.write(JSON.stringify({ status: false, error: err }, null, 2));
      res.end();
    } else {
      //console.log( { result } );
      res.write(JSON.stringify({ status: true, result: result }, null, 2));
      res.end();
    }
  });
});

var port = process.env.PORT || 8080;
app.listen(port);
console.log("server starting on " + port + " ...");

To run the program with Node.js, you need to install the necessary libraries first. Run the following command (in an environment with Node.js installed):

$ npm install

Then run the following command to start oldapp.js:

$ node oldapp

When you start it, it will display "server starting on 8080..." and the server will be waiting for requests on port 8080. In the case of oldapp.js, it will also try to connect to PostgreSQL and display "connected."

$ node oldapp
server starting on 8080 ...
connected.

The contents of the above oldapp.js make it clear that this application handles two REST APIs: "GET /" and "GET /ping". The former simply returns a JSON of { status: true }. The latter executes an SQL of "SELECT 1" against the connected PostgreSQL and returns the execution result (if it can connect to PostgreSQL, some result will be returned).

Let's try executing the former. Access "http://localhost:8080/" in a web browser and make sure { status: true } is displayed.

image.png

Let's try the latter as well. Similarly, access "http://localhost:8080/ping" in a web browser and make sure the string "{ status: true, result: { .... } }" is displayed.

image.png

Up to this point, it should be successful normally. This is the real deal. Let's stop the PostgreSQL server while running the application in this state. If you stop it with the docker command,

$ docker stop postgres

If you are using Docker Desktop, press the STOP button on the running container to stop it.

image.png

If we access each page in the web browser again in this state, what will happen? I think it will be like this in terms of the expected behavior:

  • For a request to GET / (since we are not using the DB), it will return { status: true }.
  • For a request to GET /ping (since the DB is stopped), it will return "DB error".
  • When the DB is restarted, it will automatically reconnect and return the SQL execution result for GET /ping. However, in reality, the server has crashed at this point. In the terminal where $ node oldapp was executed, an Exception is displayed and the application is terminated, and the prompt is displayed:

image.png

In other words, the server has crashed. Therefore, not only GET /ping requests, but also GET / requests will result in an error, and since it is not running, automatic reconnection is not possible.

image.png

I'm in a tough spot here. If the goal is just a simple demo, then I think it's okay to use the contents of oldapp.js (since it will work for now), but if we're thinking of a situation where it needs to be stable and run continuously, then it's a bit worrisome.

So, how can we implement something like newapp.js, where the application won't die even if the DB is stopped, and will automatically reconnect and execute SQL again when the DB is revived?

//. newapp.js
var express = require("express"),
  app = express();

var PG = require("pg");

//. PostgreSQL
var pg_hostname = "localhost";
var pg_port = 5432;
var pg_database = "mydb";
var pg_username = "admin";
var pg_password = "P@ssw0rd";

var retry_ms = 5000; //. retry every 5 sec

var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database; //+ "?sslmode=verify-full";
console.log("connecting...");
var pg = new PG.Pool({
  connectionString: connectionString,
});
pg.on("error", function (err) {
  console.log("db error on starting", err);
  if (err.code && err.code.startsWith("5")) {
    //. terminated by admin?
    try_reconnect(retry_ms);
  }
});

function try_reconnect(ts) {
  setTimeout(function () {
    console.log("reconnecting...");
    pg = new PG.Pool({
      connectionString: connectionString,
    });
    pg.on("error", function (err) {
      console.log("db error on working", err);
      if (err.code && err.code.startsWith("5")) {
        //. terminated by admin?
        try_reconnect(ts);
      }
    });
  }, ts);
}

//. top
app.get("/", function (req, res) {
  res.contentType("application/json; charset=utf-8");
  res.write(JSON.stringify({ status: true }, null, 2));
  res.end();
});

//. ping
app.get("/ping", async function (req, res) {
  res.contentType("application/json; charset=utf-8");
  var conn = null;
  try {
    conn = await pg.connect();
    var sql = "select 1";
    var query = { text: sql, values: [] };
    conn.query(query, function (err, result) {
      if (err) {
        console.log({ err });
        res.status(400);
        res.write(JSON.stringify({ status: false, error: err }, null, 2));
        res.end();
      } else {
        //console.log( { result } );
        res.write(JSON.stringify({ status: true, result: result }, null, 2));
        res.end();
      }
    });
  } catch (e) {
    res.status(400);
    res.write(JSON.stringify({ status: false, error: e }, null, 2));
    res.end();
  } finally {
    if (conn) {
      conn.release();
    }
  }
});

var port = process.env.PORT || 8080;
app.listen(port);
console.log("server starting on " + port + " ...");

Let's first check the behavior before introducing the differences. To get PostgreSQL running again:

image.png

Run the command "$ node newapp" to start newapp.js in this state.

$ node newapp
server starting on 8080 ...
connecting...

The same message will be displayed and the request will be in a waiting state. First, execute GET / and GET /ping as before (the execution result itself is the same as before):

image.png

image.png

Let's try to force stop PostgreSQL here as well. Last time the application crashed and stopped waiting for requests, but this time it should stay in waiting mode without returning to the prompt.

image.png

Try accessing GET / and GET /ping again in this state. GET / will still return { status: true }, and GET /ping will return { status: false, error: ... } since it can't access the DB, but it is able to return a response.

image.png

image.png

Then start the PostgreSQL that had stopped again.

image.png

If you wait a bit and then run the GET /ping command, the application will automatically reconnect and the screen showing the successful SQL execution will be displayed.

image.png

This enabled the practical behavior of automatically reconnecting and continuing to operate even when maintenance is done on the database.

【Code description】

First, the one without automatic reconnection. The connection part and the SQL execution part were as follows:

var pg_clinet = null;
var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.connect( function( err, client ){
  if( err ){
    //. DB not running on first boot
    console.log( 'no db on startup', err.code );
  }else{
    console.log( 'connected.' );
    pg_client = client;
  }
});

  :
  :

//. ping
app.get( '/ping', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var sql = 'select 1';
  var query = { text: sql, values: [] };
  pg_client.query( query, function( err, result ){
    if( err ){
      console.log( { err } );
      res.status( 400 );
      res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
      res.end();
    }else{
      //console.log( { result } );
      res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
      res.end();
    }
  });
});

In the connection process, the connection string is simply generated to create a connection pool, and then connect() is executed to retrieve one client. This client is then reused for subsequent SQL execution. In the SQL execution process, the SQL string is defined and the client retrieved above is used to execute it. The process itself is understandable, but on the other hand, it does not consider exceptions (so the DB can stop and cause the application to crash).

The connection part and SQL execution part of the latter, which was designed to be able to automatically reconnect after disconnection considering the occurrence of exceptions, was as follows:

var retry_ms = 5000;  //. retry every 5 sec

var connectionString = "postgres://" + pg_username + ":" + pg_password + "@" + pg_hostname + ":" + pg_port + "/" + pg_database;//+ "?sslmode=verify-full";
console.log( 'connecting...' );
var pg = new PG.Pool({
  connectionString: connectionString
});
pg.on( 'error', function( err ){
  console.log( 'db error on starting', err );
  if( err.code && err.code.startsWith( '5' ) ){
    //. terminated by admin?
    try_reconnect( retry_ms );
  }
});

function try_reconnect( ts ){
  setTimeout( function(){
    console.log( 'reconnecting...' );
    pg = new PG.Pool({
      connectionString: connectionString
    });
    pg.on( 'error', function( err ){
      console.log( 'db error on working', err );
      if( err.code && err.code.startsWith( '5' ) ){
        //. terminated by admin?
        try_reconnect( ts );
      }
    });
  }, ts );
}

  :
  :

//. ping
app.get( '/ping', async function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  var conn = null;
  try{
    conn = await pg.connect();
    var sql = 'select 1';
    var query = { text: sql, values: [] };
    conn.query( query, function( err, result ){
      if( err ){
        console.log( { err } );
        res.status( 400 );
        res.write( JSON.stringify( { status: false, error: err }, null, 2 ) );
        res.end();
      }else{
        //console.log( { result } );
        res.write( JSON.stringify( { status: true, result: result }, null, 2 ) );
        res.end();
      }
    });
  }catch( e ){
    res.status( 400 );
    res.write( JSON.stringify( { status: false, error: e }, null, 2 ) );
    res.end();
  }finally{
    if( conn ){
      conn.release();
    }
  }
});

At first, the connection process is the same until the connection pool is generated, but here it ends as it is. The process of executing connect() to retrieve the client has been changed to just before actually executing SQL. Also, the variable pg, which manages the connection pool, is used to handle errors, so that it can be handled correctly when the DB is disconnected (specifically, wait a few seconds and generate the connection pool again, and handle errors for the newly generated connection pool).

When executing SQL, we do the following:

  1. (wrap the whole process in try{ .. }catch{ .. }finally{ .. } so that exceptions can be handled no matter where it is disconnected
  2. get the client with pg.connect just before actually executing the SQL in try{ .. }
  3. release the client and return it to the connection pool in finally{ .. }.

By adding these three processes, we make sure that if the DB suddenly dies, it will handle it correctly and try to reconnect (create a new connection pool). Even if it takes a long time to reconnect, the application itself will not crash, so it can respond to requests from users (even though it cannot access the DB).

In conclusion, what I'm providing is just a sample, but I think this approach can make it possible to automatically reconnect and keep running even when the PostgreSQL server is disconnected. It can also be applied when using RDBs with connections other than PostgreSQL. Depending on the infrastructure platform you use, there may be special features for reconnecting during maintenance, so you don't necessarily have to do it this way, but I think it's a best practice.

Mình hy vọng bạn thích bài viết này và học thêm được điều gì đó mới.

Donate mình một ly cafe hoặc 1 cây bút bi để mình có thêm động lực cho ra nhiều bài viết hay và chất lượng hơn trong tương lai nhé. À mà nếu bạn có bất kỳ câu hỏi nào thì đừng ngại comment hoặc liên hệ mình qua: Zalo - 0374226770 hoặc Facebook. Mình xin cảm ơn.

Momo: NGUYỄN ANH TUẤN - 0374226770

TPBank: NGUYỄN ANH TUẤN - 0374226770 (hoặc 01681423001)

image.png

Resource

Node.js LEARN ENGLISH PostgreSQL

All rights reserved

Mục lục


Viblo
Hãy đăng ký một tài khoản Viblo để nhận được nhiều bài viết thú vị hơn.
Đăng kí