Does Anyone Have Experience with Bulk Inserting Rows into PG Database?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

does anyone have experience bulk inserting rows into pg databased? having trouble building this step

2

Hi , could you share more details on the issue? Screenshots or Screen recording would work

3 
import pg from "pg";

export default defineComponent({
  props: {
    postgresql: {
      type: "app",
      app: "postgresql",
    },
    tableName: {
      type: "string",
      label: "Table Name",
      description: "The name of the table to insert rows into",
    },
    rows: {
      type: "string",
      label: "Rows",
      description: "A JSON string representing an array of objects, with each object as a row to insert",
    },
  },
  async run({ steps, $ }) {
    const { Client } = pg;

    const client = new Client({
      host: this.postgresql.$auth.host,
      port: this.postgresql.$auth.port,
      user: this.postgresql.$auth.user,
      password: this.postgresql.$auth.password,
      database: this.postgresql.$auth.database,
      ssl: {
        rejectUnauthorized: false,  // Ensures SSL is used, but without verification
      },
    });

    try {
      await client.connect();

      // Parse the rows from the string into a JSON array of objects
      const rows = JSON.parse(this.rows);

      if (!Array.isArray(rows)) {
        throw new Error("Rows should be a JSON array of objects");
      }

      // Extract the column names and values from the first object in the array
      const columns = Object.keys(rows[0]);
      const values = rows.map(row => columns.map(col => row[col]));

      // Construct the query for bulk inserting
      const queryText = `
        INSERT INTO ${this.tableName} (${columns.join(", ")})
        VALUES ${values.map((_, i) => `(${columns.map((_, j) => `$${i * columns.length + j + 1}`).join(", ")})`).join(", ")}`;

      // Flatten the values for the parameterized query
      const flatValues = values.flat();

      // Execute the query
      const result = await client.query({
        text: queryText,
        values: flatValues,
      });

      return result;
    } catch (error) {
      throw new Error(`Error inserting rows: ${error.message}`);
    } finally {
      // Ensure the database connection is closed
      await client.end();
    }
  },
});
4

hey - thx for the quick response, when trying to execute this, i keep getting this sort of error - Error inserting rows: “[object Object]” is not valid JSON

is there a best strategy for doing a bulk insert?

5

I believe this is a code issue - which is not related to Pipedream platform.

What I would suggest is to investigate the root cause of your issue "[object Object]" is not valid JSON . Maybe you can try to reproduce it in your local machine for easier debuging.