Should I open/close database connection after every query/insert?

Question

Hey everyone i'm developer a simple app in last days using nodejs and create this function to return client instance from mongodb

const mongodb = require("mongodb");
const { db } = require("../config/env");

const conection = async () => {
    try {
        const client = await mongodb.MongoClient.connect(db.uri, {
            useNewUrlParser: true,
            useUnifiedTopology: true,
        });
        return client;
    } catch (error) {
        throw error;
    }
};

module.exports = conection;

and i make this simple function for acess data layer and return records instered

const index = async ({ limit = 10, offset = 0, filter = {} }) => {
    const client = await conection();
    if (filter._id) {
        filter._id = mongodb.ObjectID(filter._id);
    }
    try {
        const collection = client.db("api").collection("user");
        const data = await collection
            .find({ ...filter })
            .skip(offset)
            .limit(limit)
            .toArray();
        return data;
    } catch (error) {
        throw new Error(error);
    } finally {
        await client.close();
    }
};

I would like to know if I really need to make the connection and close it with each query or should I keep the connection open

NOTE: in this case I am using a simple Atlas cluster (free) but I would like to know if I should do this also when working with sql banks like postgres

Answer 1

Don't close your connection unless you are exiting your app, and then make sure you do. Ensure that you are using the same connection when you come back to do more I/O. Database connections are resource-intensive and should be established as few times as possible and shared as much as possible. You can also get middleware problems with connections like ODBC if you don't pick up existing connections and get weird errors like connection pools running out. Get to know your connector and how to use it most effectively, it will be a rewarding activity :-)