How to Insert Data into Specific Table of PostgreSQL Schemas with Node.js

Managing PostgreSQL schemas can be complex. This post shows a Node.js script to automate schema retrieval and data insertion using the pg and fs libraries.

Prerequisites
Ensure you have:

• Node.js installed on your machine.
• The pg library for PostgreSQL (npm install pg).
• Basic familiarity with PostgreSQL and Node.js.

Database Connection Configuration
First, set up your PostgreSQL client with the necessary connection details:

const { Client } = require('pg');
const fs = require('fs');

// Database connection configuration
const dbConfig = {
  user: 'your_username',
  host: 'your_host',
  database: 'your_database',
  password: 'your_password',
  port: 5432, // default port for PostgreSQL
};

Replace your_username, your_host, your_database, and your_password with your actual database credentials.

Retrieve Schemas
To retrieve all schemas from your PostgreSQL database, excluding default schemas, use the following function:

async function getSchemas() {
  const client = new Client(dbConfig);
  try {
    await client.connect();
    const res = await client.query(`
      SELECT schema_name
      FROM information_schema.schemata
      WHERE schema_name NOT IN ('information_schema', 'pg_catalog', 'public');
    `);
    return res.rows.map(row => row.schema_name);
  } catch (err) {
    console.error('Error retrieving schemas:', err);
  } finally {
    await client.end();
  }
}

Insert Data into Schemas
Create a function to insert data into a specific table within each schema. Replace your_table_name with the name of your target table:

async function insertDataIntoSchema(schemaName, data) {
  const client = new Client(dbConfig);

  try {
    await client.connect();
    const tableName = 'your_table_name'; // Replace with your actual table name

    const insertQuery = `
      INSERT INTO "${schemaName}"."${tableName}" ("column1", "column2", "column3")
      VALUES ($1, $2, $3)
    `;

    for (const row of data) {
      await client.query(insertQuery, [row.column1, row.column2, row.column3]);
    }

    console.log(`Data inserted into ${schemaName}.${tableName}`);
  } catch (err) {
    console.error(`Error inserting data into ${schemaName}:`, err);
  } finally {
    await client.end();
  }
}

Read JSON Data
To read and parse JSON data from a file, use the following function:

function readJSONFile(filePath) {
  return new Promise((resolve, reject) => {
    fs.readFile(filePath, 'utf8', (err, data) => {
      if (err) {
        console.error("File read error:", err);
        return reject(err);
      }
      try {
        const jsonData = JSON.parse(data);
        resolve(jsonData);
      } catch (parseErr) {
        console.error("JSON parse error:", parseErr);
        reject(parseErr);
      }
    });
  });
}

Main Function
Combine everything in the main function to fetch schemas, read JSON data, and insert it into the database:

async function main() {
  try {
    const schemas = await getSchemas();
    if (schemas && schemas.length) {
      const jsonFilePath = './path/to/your/json_file.json'; // Replace with your JSON file path
      const data = await readJSONFile(jsonFilePath);
      for (const schema of schemas) {
        await insertDataIntoSchema(schema, data);
      }
    } else {
      console.log('No schemas found.');
    }
  } catch (err) {
    console.error('Error in main function:', err);
  }
}

main().catch(err => console.error('Error in main function:', err));

Conclusion
This Node.js script simplifies the process of managing PostgreSQL schemas and inserting data. By automating these tasks, you can handle complex database operations more efficiently. Adapt the script to suit your specific needs and integrate it into your workflow to boost productivity.

Feel free to ask questions or share your enhancements in the comments!