load-db.js

require("dotenv").config();
const pg = require("pg");
const { Client } = pg;
const client = new Client({ connectionString: process.env.DB_URL });
const fs = require("fs");
const csv = require("csv-parser");
let stmt;

async function doTables() {
  try {
    await client.connect();

    stmt = `DROP TABLE IF EXISTS line_items;`;
    await client.query(stmt);
    stmt = `DROP TABLE IF EXISTS orders;`;
    await client.query(stmt);
    stmt = `DROP TABLE IF EXISTS customers;`;
    await client.query(stmt);
    stmt = `DROP TABLE IF EXISTS products;`;
    await client.query(stmt);
    stmt = `DROP TABLE IF EXISTS employees;`;
    await client.query(stmt);

    stmt = `CREATE TABLE IF NOT EXISTS customers (
            customer_id INTEGER PRIMARY KEY generated by default as identity,          
            customer_name TEXT NOT NULL,
            contact TEXT,
            street TEXT,
            city TEXT,
            postal_code TEXT,
            country TEXT,
            phone TEXT
            );`;
    await client.query(stmt);
    await client.query("BEGIN");
    await loadCsvToPostgres("./csv/customers.csv", "customers");
    await client.query("COMMIT");
    stmt = `SELECT setval('customers_customer_id_seq', (SELECT MAX(customer_id) FROM customers));`
    await client.query(stmt)

    stmt = `CREATE TABLE IF NOT EXISTS employees (
            employee_id INTEGER PRIMARY KEY generated by default as identity,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            phone TEXT      
            );`;
    await client.query(stmt);
    await client.query("BEGIN");
    await loadCsvToPostgres("./csv/employees.csv", "employees");
    await client.query("COMMIT");
    stmt = `SELECT setval('employees_employee_id_seq', (SELECT MAX(employee_id) FROM employees));`
    await client.query(stmt)

    stmt = `CREATE TABLE IF NOT EXISTS products (
            product_id INTEGER PRIMARY KEY generated by default as identity,
            product_name TEXT NOT NULL,
            price REAL NOT NULL
            );`;
    await client.query(stmt);
    await client.query("BEGIN");
    await loadCsvToPostgres("./csv/products.csv", "products");
    await client.query("COMMIT");
    stmt = `SELECT setval('products_product_id_seq', (SELECT MAX(product_id) FROM products));`
    await client.query(stmt)

    stmt = `CREATE TABLE IF NOT EXISTS orders (
            order_id INTEGER PRIMARY KEY generated by default as identity,
            customer_id INTEGER NOT NULL,
            employee_id INTEGER NOT NULL,
            date TEXT NOT NULL,
            FOREIGN KEY(customer_id) REFERENCES customers(customer_id),
            FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
            );`;
    await client.query(stmt);
    await client.query("BEGIN");
    await loadCsvToPostgres("./csv/orders.csv", "orders");
    await client.query("COMMIT");
    stmt = `SELECT setval('orders_order_id_seq', (SELECT MAX(order_id) FROM orders));`
    await client.query(stmt)

    stmt = `CREATE TABLE IF NOT EXISTS line_items (
            line_item_id INTEGER PRIMARY KEY generated by default as identity,
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            FOREIGN KEY(order_id) REFERENCES orders(order_id),
            FOREIGN KEY(product_id) REFERENCES products(product_id)
            );`;
    await client.query(stmt);
    await client.query("BEGIN");
    await loadCsvToPostgres("./csv/line_items.csv", "line_items");
    await client.query("COMMIT");
    stmt = `SELECT setval('line_items_line_item_id_seq', (SELECT MAX(line_item_id) FROM line_items));`
    await client.query(stmt)
  } catch (error) {
    console.log(`Error loading data: ${error}`);
  } finally {
    await client.end();
  }
}

async function loadCsvToPostgres(csvFilePath, tableName) {
  return new Promise((resolve, reject) => {
    const results = [];

    fs.createReadStream(csvFilePath)
      .pipe(csv())
      .on("data", (data) => results.push(data))
      .on("end", async () => {
        try {
          let count = 0;
          let stmt;
          for (const row of results) {
            const columns = Object.keys(row).join(", ");
            const values = Object.values(row)
              .map((value) => `'${value.replace(/'/g, "''")}'`) // Escape single quotes
              .join(", ");
            if (count == 0) {
              stmt = `INSERT INTO ${tableName} (${columns}) VALUES (${values})`;
            } else {
              stmt += `,(${values})`;
            }
            count++;
          }
          await client.query(stmt);
          console.log(
            `Table ${tableName} ${count} rows of data loaded successfully.`
          );
          resolve(); // Resolve the promise when done
        } catch (err) {
          reject(err); // Reject on error
        }
      })
      .on("error", (err) => {
        reject(err); // Reject on stream error
      });
  });
}

doTables();