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
Source Code
You can find the complete source code for this example on Github.
I like this tutorial and the code makes so much sense but I did run into an issue I hope you can help with. How would we handle a case where the we have a db column of integer data type, and the stream value is ‘ ‘? Postgres wont accept ‘ ‘, so I am thinking we need to change ‘ ‘ to null. Where would we integrate a condition to check and update empty fields?
Thanks for the tutorial! Keep writing.
Excellent tutorial from you, man. I actually like what you’ve written here. I can’t wait to learn far more from you. This is actually a great site for developers.
Thank you! Just what I was looking for. Great tutorial format!