Import CSV data into PostgreSQL using Node.js

In this tutorial, we’re gonna import CSV data into PostgreSQL Database table using Node.js. There are 2 steps:

• Reading CSV file with fs & fast-csv module
• Connecting to PostgreSQL Database to save CSV data with pg module

Related Post: Export PostgreSQL data to CSV file using Node.js

Rest API: Node.js: Upload CSV file data into Database with Express

CSV Standardization overview

A CSV (comma-separated values) file is a plain text file.

This is a sample:

id,name,createdAt
1,Node,2019-09-03
2,Vue,2019-09-06
3,Angular,2019-09-09

There is no formal format, most implementations follow 7 rules that you can find at RFC4180.

Setup Node.js modules

At first, we run the command: npm install fast-csv pg.

There are 2 modules that we use:
– fast-csv to read CSV file
– pg to work with PostgreSQL database.

Read CSV file using fast-csv

As usual, we need to import necessary modules to js file, in this case, they are fs & fast-csv:

const fs = require("fs");
const fastcsv = require("fast-csv");

Then we create a ReadStream from csv file using fs.createReadStream() function. This ReadStream object will ‘pipe’ a CsvParserStream object generated from fast-csv parse() function:

let stream = fs.createReadStream("bezkoder.csv");
let csvData = [];
let csvStream = fastcsv
  .parse()
  .on("data", function(data) {
    csvData.push(data);
  })
  .on("end", function() {
    // remove the first line: header
    csvData.shift();

    // connect to the PostgreSQL database
    // save csvData
  });

stream.pipe(csvStream);

In the process of creating CsvParserStream, we listen 2 events:
– on('data') is triggered when a record is parsed, so we will get the record (data) in the handler function. Each record is pushed to csvData array.
– on('end') is triggered after the parsing is done, at the time that we have all records. Hence, we will save data to PostgreSQL in this handler function.

Create PostgreSQL table

Now we have the csv file bezkoder.csv with content looks like:

id,name,description,createdAt
1,Node.js,JavaScript runtime environment,2019-09-03
2,Vue.js,JavaScript Framework for building UI,2019-09-06
3,Angular.js,Platform for building mobile & desktop web app,2019-09-09

What we need is a table in PostgreSQL database named category.
The table has 4 columns: [id, name, description, created_at]. So we run the script below:

CREATE TABLE category(
    id integer NOT NULL,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    created_at DATE);

Check the category table:

\d category
             Table "public.category"
   Column    |          Type          | Modifiers
-------------+------------------------+-----------
 id          | integer                | not null
 name        | character varying(255) | not null
 description | character varying(255) |
 created_at  | date                   |

Import CSV data to PostgreSQL using pg

After the step Reading CSV file, we have all rows (except header) which are pushed in csvData array. Now we’re gonna use pg module to connect to PostgreSQL database and save them.

Remember that the code will be written inside 'end' event handler function.

const Pool = require("pg").Pool;

// remove the first line: header
csvData.shift();

// create a new connection pool to the database
const pool = new Pool({
  host: "localhost",
  user: "postgres",
  database: "testdb",
  password: "123",
  port: 5432
});

const query =
  "INSERT INTO category (id, name, description, created_at) VALUES ($1, $2, $3, $4)";

pool.connect((err, client, done) => {
  if (err) throw err;
  try {
    csvData.forEach(row => {
      client.query(query, row, (err, res) => {
        if (err) {
          console.log(err.stack);
        } else {
          console.log("inserted " + res.rowCount + " row:", row);
        }
      });
    });
  } finally {
    done();
  }
});

In the code above, we iterate over csvData array, each row will be saved to PostgreSQL using pg client pool.

done() function is used to release the client when process finishes.

More details about Pooling with pg could be find at:
https://node-postgres.com/features/pooling.

Write full code

The whole code looks like:

const fs = require("fs");
const Pool = require("pg").Pool;
const fastcsv = require("fast-csv");

let stream = fs.createReadStream("bezkoder.csv");
let csvData = [];
let csvStream = fastcsv
  .parse()
  .on("data", function(data) {
    csvData.push(data);
  })
  .on("end", function() {
    // remove the first line: header
    csvData.shift();

    // create a new connection to the database
    const pool = new Pool({
      host: "localhost",
      user: "postgres",
      database: "testdb",
      password: "123",
      port: 5432
    });

    const query =
      "INSERT INTO category (id, name, description, created_at) VALUES ($1, $2, $3, $4)";

    pool.connect((err, client, done) => {
      if (err) throw err;

      try {
        csvData.forEach(row => {
          client.query(query, row, (err, res) => {
            if (err) {
              console.log(err.stack);
            } else {
              console.log("inserted " + res.rowCount + " row:", row);
            }
          });
        });
      } finally {
        done();
      }
    });
  });

stream.pipe(csvStream);

Check the result

Run the code, console shows:

inserted 1 row: [ '1', 'Node.js', 'JavaScript runtime environment', '2019-09-03' ]
inserted 1 row: [ '2',
  'Vue.js',
  'JavaScript Framework for building UI',
  '2019-09-06' ]
inserted 1 row: [ '3',
  'Angular.js',
  'Platform for building mobile & desktop web app',
  '2019-09-09' ]

Check PostgreSQL table:

SELECT * FROM category;

 id |    name    |                  description                   | created_at
----+------------+------------------------------------------------+------------
  1 | Node.js    | JavaScript runtime environment                 | 2019-09-03
  2 | Vue.js     | JavaScript Framework for building UI           | 2019-09-06
  3 | Angular.js | Platform for building mobile & desktop web app | 2019-09-09

Conclusion

Now we’ve known way to read CSV file using fs & fast-csv, we also connected our Node.js app with PostgreSQL database to save CSV data with pg module.

In the next tutorial, we’ll show you how to extract PostgreSQL data to a CSV file.

You can also know how to build Rest API with the post:
Node.js: Upload CSV file data into Database with Express
(just change configuration from MySQL to PostgreSQL)

Happy learning! See you again.

Further Reading

• RFC4180
• https://www.npmjs.com/package/fast-csv
• https://www.npmjs.com/package/pg
• pg Document

Source Code

You can find the complete source code for this example on Github.