When is the best moment to open and close the connection in Postgresql?

Question

I'm trying to make several inserts in database from an array but ... I have a problem with de connection and disconnection to the database

For example, with this code, I only insert the first element and then I've got an error because the access to the database has been disconnected.

async function saveData(destinations){
    let ddbb = new PostgresqlDDBB();
    ddbb.connect();
    await destinations.forEach(async (country, index) => {
        let params = [country.countryName, country.countryCode, country.urlProperties];
        let id = await ddbb.insertData(2, params);
        destinations[index].id = id;
    });
    ddbb.disconnect()
}

The object PostgresqlDDBB has this code:

import {Result} from "../../com/result";
import {clientPG} from "./pg_connection";
import {selectQuery} from "../queries/pg_queries";

class PostgresqlDDBB{
    constructor(){
        this.query = null;
    }

    set query(id){
        this.__query = selectQuery(id);
    }

    get query(){
        return this.__query;
    }

    async connect(){
        await clientPG.connect();
    }

    disconnect(){
        clientPG.end();
    }
    
    /**
     * 
     * @param {*} id_query ID of query to run
     * @param {*} params params to pass to the query
     * @returns The last id save in the database
     */
    async insertData(id_query, params){
        let result;

        try{
            let query = selectQuery(id_query);
            if (params !== null)
                result = await clientPG.query(query, params);
            else
                result = await clientPG.query(query);
            return result.rows[0].id;
        }catch(err){
            console.log(err.stack);
        }
    }
}

module.exports.PostgresqlDDBB = PostgresqlDDBB;

And the client of Postgress has this code:

import {Client} from "pg";
import config from "../../config";


    const clientPG = new Client({
        user: config.db_postgres.user,
        password: config.db_postgres.password,
        database: config.db_postgres.name,
        host: config.db_postgres.host,
        //ssl: config.db_postgres.ssl    
    });
    
    module.exports.clientPG = clientPG;

Looking for the code of saveData I don't knwo why forEach don't wait to finish the insert of all elements.

If I do the same instead of a forEach with a for and call the function twice:

async function saveData(destinations){
    let ddbb = new PostgresqlDDBB();
    await ddbb.connect();
    for(let i = 0; i < destinations.length; i++){
        let country = destinations[i];
        let params = [country.countryName, country.countryCode, country.urlProperties];
        let id = await ddbb.insertData(2, params);
    }
    ddbb.disconnect();
    console.log("Voy a salir!!!");
};

saveData(list);
saveData(list);

The first time works fine but the second time we've got this error:

Voy a salir!!!

/home/josecarlos/Workspace/BlueCode/Youmalou/mapping-service/node_modules/pg/lib/client.js:94
      const err = new Error('Client has already been connected. You cannot reuse a client.')
                  ^

Error: Client has already been connected. You cannot reuse a client.

The second time we run the function the connection is still open. How is that possible?

What is the best moment to open the connection? Is a good practive to open the connection and not close it because when the object will be deleted the connection too?

How can I open, insert data, close the connection, open connection again, insert data, close the connection etc.?

Am I doing something wrong?

Edit I:

If I modify my code to make await connect/disconnect. I've got this error the second time I try to run the method saveData.

      const err = new Error('Client has already been connected. You cannot reuse a client.')
                  ^

Error: Client has already been connected. You cannot reuse a client.

The code of the method is:

async function saveGIATAData(countries){
    let ddbb = new PostgresqlDDBB();
    await ddbb.connect();
    for(let i = 0; i < countries.length; i++){
        let country = countries[i];
        let params = [country.countryName, country.countryCode, country.urlProperties];
        let id = await ddbb.insertData(2, params);
    }
    await ddbb.disconnect();
    console.log("Voy a salir!!!");
}

The code of the object PostgresqlDDBB is:

import {Result} from "../../com/result";
import {clientPG} from "./pg_connection";
import {selectQuery} from "../queries/pg_queries";

class PostgresqlDDBB{
    constructor(){
        this.query = null;
    }

    set query(id){
        this.__query = selectQuery(id);
    }

    get query(){
        return this.__query;
    }

    async connect(){
        await clientPG.connect();
    }

    async disconnect(){
        await clientPG.end();
    }
    
    /**
     * 
     * @param {*} id_query ID of query to run
     * @param {*} params params to pass to the query
     * @returns The last id save in the database
     */
    async insertData(id_query, params){
        let result;

        try{
            let query = selectQuery(id_query);
            if (params !== null)
                result = await clientPG.query(query, params);
            else
                result = await clientPG.query(query);
            return result.rows[0].id;
        }catch(err){
            console.log(err.stack);
        }
    }
}

module.exports.PostgresqlDDBB = PostgresqlDDBB;

And the code of the client is:

import {Client} from "pg";
import config from "../../config";

const clientPG = new Client({
    user: config.db_postgres.user,
    password: config.db_postgres.password,
    database: config.db_postgres.name,
    host: config.db_postgres.host,
    //ssl: config.db_postgres.ssl    
});

module.exports.clientPG = clientPG;

And, like I said previously, I've got an error the second time I try to run the method saying to me that the client is already open.

What happend? I don't understand what is happend.

When is the best moment to open and close the connection in Postgresql?

Answers (1)

Related Questions