How do I manage concurrent connections with postgres and node-postgres?

Question

I have read a lot of posts here about the "too many connections" issue.

I am struggling to loop a modest amount of CSV data (10,000 rows) upload to ElephantSQL (psql).

I am on the free plan for ElephantSQL. And while I can upgrade to get more concurrent connections, the issue is that I am not sure how to manage connections.

Here is my code:

First I create individual URLs to pass to axios in the extractToRaw (raw_data table in psql)

readCSV(async (list) => {
    const apiURLList = await list.map((item) => {
        return `apiDomain=${domain}&api_key=${apiKey}`;
    });
    for (const url of apiURLList) {
            await extractToRaw(url);
    }
});

Then:

const extractToRaw = async (url) => {
    let records = [];

    try {
        await axios({
            method: "get",
            url: url,
            params: {
                //things here
            },
        }).then((data) => {
            const contactRecord = data.data.data;
            const emailData = data.data.data.emails;
            const metaData = data.data.meta;
            //

            if (metaData.results === 0) {
                try {
                    console.log(`no emails for ${contactRecord.domain}`);
                    upload_no_email(contactRecord.domain);
                } catch (err) {
                    console.log("name: ", err.name, "message: ", err.message);
                }
            } else
                for (const record of emailData) {
                    console.log(`Writing ${record.value} record...`);
                    records.push({
                        firstname: record.first_name,
                        lastname: record.last_name,
                        position: record.position,
                        seniority: record.seniority,
                        email: record.value,
                        website: record.value.split("@")[1],
                        confidence: record.confidence,
                    });
                    console.log(records);
                }
            //upload to table
            uploadToRaw(records);
        });
    } catch (err) {
        console.log(err);
    }
};

Finally - upload to PSQL

const uploadToRaw = (records) => {
    console.log(`uploading from ${records[0].website}`);
    for (const record of records) {
        const valuesArr = [
            record.firstname,
            record.lastname,
            record.position,
            record.seniority,
            record.email,
            record.website,
            record.confidence,
        ];
        pool.query(
            `
        INSERT INTO raw_data(firstname, lastname, position, seniority, email, website, confidence) VALUES($1, $2, $3, $4, $5, $6, $7)`,
            valuesArr
        );
    }
};

Without fail, I will get a "too many connections" error.

Am I using pool.query wrong?

UPDATE: add node-postgres initialise script

const { Pool, Client } = require("pg");
const connectionString =
    "string here";

const pool = new Pool({
    connectionString,
});

const client = new Client({
    connectionString,
});

Didn't post the most important part, which is where the postgres client is initialized. You need to give a max connection parameter that matches your server's settings — Asad Awadia
– Asad Awadia, Commented Aug 11, 2022 at 18:20
understood. I added it. Where do I add the max connection param? — Vinn
– Vinn, Commented Aug 11, 2022 at 18:30
To the new pool call - it takes a max parameter. Did you even look at the doc? — Asad Awadia
– Asad Awadia, Commented Aug 11, 2022 at 19:21
Amazing. Thank you so much. Can you add this as an answer Asad? — Vinn
– Vinn, Commented Aug 12, 2022 at 12:30

Asad Awadia · Accepted Answer · 2022-08-12 13:17:33Z

1

The call to create a new pool takes in a parameter call max, which is what the max number of connections in this pool will be set to

Care must be taken that it aligns with the number of connections available on the DB server

answered Aug 12, 2022 at 13:17

Asad Awadia

1,5652 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I manage concurrent connections with postgres and node-postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related