Use Node to Insert CSV Data into Postgres (Part 2)

Written by Data Pilot
October 24, 2019
  • Node.JS
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

Node.js runtime environment that enables developers to quickly build and manage scalable networks. A necessary component of database management is being able to easily perform a Node insert CSV data Postgres table. Since data comes in a variety of formats, it’s nice to incorporate the data from a CSV file when you’re ready. You learned the beginning steps in Part 1. Here, in Part 2, you’ll learn the remaining steps on how to accomplish the CSV data insertion. While you learn, you’ll be able to follow along and review the examples for further clarification.

If you already know the steps for how to insert CSV data into a Postgres table, skip to Just the Code.

Prerequistes

  • Verify that you have downloaded and installed PostgresSQL.

  • Make sure that Node.js](https://nodejs.org/en/download) is downloaded, installed and running.

  • Create a test PostresSQL table based on the same outline as your file containing the CSV data. For additional information on how to set this up, review Part 1 of this tutorial series again.

Here’s a CSV data sample to follow along with in this tutorial on Node insert CSV data Postgres000D:

1
2
3
4
ID, Str, Int, Bool
1, Hello world, 123, true
2, Yo what up?, 42, false
3, Excelsior!, 987, true

Screenshot of sample CSV data for PostgreSQL Node insert code

Postgres CSV data insertion steps

  • Make them constants when you add the node modules 'pg', 'fs', and 'csv-parser' requirements. The function require() is how you accomplish this. See below for an example of how it’s written:
1
2
3
const Pool = require("pg").Pool;
const csv = require("csv-parser");
const fs = require("fs");
  • Next, make a string declaration for the name of the table Postgres. The rows of CSV data that the fs module returns will be stored in the array object [] that doesn’t have anything in at the moment.
1
2
const tableName = "csv_table";
var allRows = [];

Make an instance declaration of the Pool library’s pg model in Node

  • Make a new pg model instance like this:
1
2
3
4
5
6
7
8
// declare a new client instance from Pool()
const client = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

NOTE: Check that your credentials are the same as your server Postgres. This way, when you call the method Pool(), it won’t raise an exception.

Make a function declaration to repair values of CSV

The string values of Postgre must have single quotation marks surrounding them.

The fixStringValue function handles it by parsing CSV data rows. As needed, it will change the double quotations to single ones or append the strings that are passed as the String data type.

  • Here’s an example of the function fixStringValue in action:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
function fixStringValue( dataRow, delimiter="," ) {

// split row using target delimiter
var splitRow = dataRow.split( delimiter )

// iterate the elements in the CSV row


// check for string escape char
if (row.includes("'") === false) {
console.log("\nFix non-strings in row:")

// try to fix the string values if needed
row = fixStringValue( row )
}

// append the closing parenthesis for the SQL string
sqlStatement += '\n(' + String(row) + '),'
}
}

// remove the last comma, and replace it with a semi-colon
sqlStatement = sqlStatement.substring(0, sqlStatement.length-1) + ";"

// log the SQL statement to console
console.log("\nsqlStatement:", sqlStatement, "\n")

Make a sqlStatement string pass to the method query()

  • The fixStringValue function ends when the sqlStatement is passed to the method query of the module pg.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// pass the SQL string to the the client.query() method call
client.query(sqlStatement, (err, res)=>{

if (err !== undefined) {
// log the error to console
console.log("Postgres INSERT error:", err)
}

// check if the response is not 'undefined'
if (res !== undefined) {

// log the response to console
console.log("Postgres response:", res)

if (res.rowCount > 0) {
console.log("# of records inserted:", res.rowCount)
} else {
console.log("No records were inserted.")
}
}
}) // end of client.query()
}

Use the method createReadStream() to construct a Node Read Stream

Obtain the data rows of CSV data with the method createReadStream() from the fs library. Make a CSV file name pass like this:

1
2
// create a read stream for the CSV file
fs.createReadStream("my-data.csv");

NOTE: Your CSV data file should be located in the project directory Node. If it’s not, check that your CSV data file’s path is specified.

Use JavaScript’s readStream() function and its method call on() to fix errors

  • The method call on() of the readStream() function repairs exceptions. Make a pipe for the CSV data file too. Complete both actions like this:
1
2
3
4
5
6
7
8
// handle any errors
.on('error', (err) => {
console.log("\nfs.createReadStream() error:", err)
console.log("Make sure the path and name for the CSV file are correct.")
})

// create a pipe for the CSV file
.pipe(csv(['id', 'str', 'int', 'bool']))

Get the rows of CSV data with the on() method

  • Pass the string data by using the readStream method on() to get the all of the rows of CSV data for the Node insert CSV data Postgres table:
1
2
3
4
5
6
7
8
9
10
11
// get the data
.on('data', (dataRow) => {

// log the data row to the console
console.log("dataRow:", dataRow);
console.log("dataRow type:", typeof dataRow);

// push the data row to the CSV data array
allRows.push( dataRow )

})

NOTE: The streamline the process, use the push() function from JavaScript to place all the rows into the array object. Do this because a single row returns per iteration.

Make a function insertRecords() array object pass.

  • Now that the ReadStream is finished, have the method on() run the insertRecords() function so that it will pass the CSV data rows that the array object holds to it.
1
2
3
4
5
6
7
// createReadStream ended
.on('end', () => {
console.log('CSV data iteration finished');

// call the insertRecords() function declared earlier
insertRecords( allRows )
});

Test the whole script for Node insert CSV data Postgres000D

Just like if you were running the script in its entirety in PostgreSQL, doing it in Node should have the same results.

1
2
3
4
5
6
INSERT INTO
csv_table (ID, Str, INT, Bool)
VALUES
(1, 'Hello, world', 123, TRUE),
(2, 'Yo, what up?', 42, FALSE),
(3, 'Excelsior!', 987, TRUE);

Run the command node to add the data CSV file to Postgres

  • Within your directory for your Node project, at the terminal window, execute the file with the node command like this:
1
node insert-csv.js
  • Your result should look similar to this:

NOTE: Check if Node is installed the right way if you don’t see a result that resembles this example below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Postgres response: Result {
command: 'INSERT',
rowCount: 3,
oid: 0,
rows: [],
fields: [],
_parsers: [],
_types: TypeOverrides {
_types: {
getTypeParser: [Function: getTypeParser],
setTypeParser: [Function: setTypeParser],
arrayParser: [Object],
builtins: [Object]
},
text: {},
binary: {}
},
RowCtor: null,
rowAsArray: false
}
# of records inserted: 3

Result of the PostgreSQL CSV data INSERT API call using Node

Confirm the CSV data rows were added

At the terminal window, make a PostgreSQL connection with the command psql.

  • Use the statement here to confirm the table received the rows.
1
SELECT * FROM csv_table;

SELECT all Postgres records in psql inserted from CSV data using Node

Conclusion

You’ve completed Part 2 of Use Node to Insert CSV Data into Postgres is a tutorial continuum of the instructional series. Today, you learned how to perform a Node insert CSV data Postgres table. You discovered how to fix any exceptions that were raised using the fixStringValue function as well. Be inspired to manage your data without limitations today.

Just the Code

Here’s the entire sample code for the Node insert CSV data Postgres instructions in this tutorial series titled: Use Node to Insert CSV Data into Postgres (Part 2):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
const Pool = require('pg').Pool
const csv = require('csv-parser');
const fs = require('fs');

const tableName = 'csv_table'
var allRows = []

/*
// original CSV data
ID, Str, Int, Bool
1, Hello world, 123, true
2, Yo what up?, 42, false
3, Excelsior!, 987, true


// final SQL string for the Node file
INSERT INTO
csv_table (ID, Str, Int, Bool)
VALUES
(1, 'Hello, world', 123, true),
(2, 'Yo, what up?', 42, false),
(3, 'Excelsior!', 987, true);
*/

// declare a new client instance from Pool()
const client = new Pool({
user: "objectrocket",
host: "localhost",
database: "some_database",
password: "1234",
port: "5432"
})

// create a function to fix string values in Postgres table row
function fixStringValue( dataRow, delimiter="," ) {

// split row using target delimiter
var splitRow = dataRow.split( delimiter )

// iterate the elements in the CSV row
bother="bother" with="with" integers="integers" or="or" floats="floats" if="if" add="add" quotes="quotes" around="around" value="value" needed="needed" var="var" lower="lower" single="single" to="to" values="values" (lower="(lower" !="t" &&="&&" )=")" replace="replace" double-quotes="double-quotes" 'g'),="'g')," "");=""");" put="put" the="the" +="\nVALUES" "'"=""'"" }="}" join="join" list="list" back="back" into="into" a="a" string="string" return="return" delimiter="delimiter" "=""" ");="");" function="function" that="that" iterates="iterates" over="over" rows="rows" and="and" concatenates="concatenates" before="before" inserting="inserting" records="records" declare="declare" an="an" empty="empty" iterate="iterate" data="data" for="for" (i="=" i++)="i++)" get="get" each="each" row="row" (key)="(key)" }).join(',')="}).join(',')" header="header" 0)="0)" statement="statement" ')'="')'" make="make" sure="sure" has="has" length="length" else="else" (row.length="(row.length" (isnan(parseint(splitrow[item])))="(isNaN(parseInt(splitRow[item])))" csv="CSV" boolean="Boolean" splitrow[item]="'" regexp('"',="RegExp('"'," string(splitrow[item]).trim()="String(splitRow[item]).trim()" splitrow.join(="splitRow.join(" sql="SQL" postgres="Postgres" insertrecords(="insertRecords(" datarows="dataRows" sqlstatement="sqlStatement"

// check for string escape char
if (row.includes("'") === false) {
console.log("\nFix non-strings in row:")

// try to fix the string values if needed
row = fixStringValue( row )
}

// append the closing parenthesis for the SQL string
sqlStatement += '\n(' + String(row) + '),'
}
}

// remove the last comma, and replace it with a semi-colon
sqlStatement = sqlStatement.substring(0, sqlStatement.length-1) + ";"

// log the SQL statement to console
console.log("\nsqlStatement:", sqlStatement, "\n")

// pass the SQL string to the the client.query() method call
client.query(sqlStatement, (err, res)=>{

if (err !== undefined) {
// log the error to console
console.log("Postgres INSERT error:", err)
}

// check if the response is not 'undefined'
if (res !== undefined) {

// log the response to console
console.log("Postgres response:", res)

if (res.rowCount > 0) {
console.log("# of records inserted:", res.rowCount)
} else {
console.log("No records were inserted.")
}
}
}) // end of client.query()
}

// create a read stream for the CSV file
fs.createReadStream("my-data.csv")

// handle any errors
.on('error', (err) => {
console.log("\nfs.createReadStream() error:", err)
console.log("Make sure the path and name for the CSV file are correct.")
})

// create a pipe for the CSV file
.pipe(csv(['id', 'str', 'int', 'bool']))

// get the data
.on('data', (dataRow) => {

// log the data row to the console
console.log("dataRow:", dataRow);
console.log("dataRow type:", typeof dataRow);

// push the data row to the CSV data array
allRows.push( dataRow )

})

// createReadStream ended
.on('end', () => {
console.log('CSV data iteration finished');

// call the insertRecords() function declared earlier
insertRecords( allRows )
});
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.