heart2heart/csvtosql.js

import pg from "pg";
import fs from "fs";
import readline from 'readline';

const { Client } = pg;


const getClient = async () => {
    const client = new Client({
        user: process.env.POSTGRES_USER,
        password: process.env.POSTGRES_PASSWORD,
        host: process.env.POSTGRES_HOST,
        port: process.env.POSTGRES_PORT,
        database: process.env.POSTGRES_DB
    });
    await client.connect();

    await client.query(fs.readFileSync('./scheme.psql').toString());

    return client;
};

const db = await getClient();

const file = fs.readFileSync("./output.csv").toString();

const inFileStream = fs.createReadStream("./output.csv");
await db.query("BEGIN");
const rl = readline.createInterface({
    input: inFileStream,
    output: null,
    crlfDelay: Infinity
})

rl.on('line', async (line) => {
    let splitted = line.split(",")
    console.log(splitted);
    await db.query(`INSERT INTO cities (name, lat, lng, country) VALUES ($1, $2, $3, $4)`, [splitted[0], splitted[1], splitted[2], splitted[3]])
});

rl.on('close', async () => {
    await db.query("COMMIT");
    process.exit(0)

})