Use Node to Insert CSV Data into Postgres (Part 2)
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 |
Postgres CSV data insertion steps
- Make them constants when you add the node modules
'pg'
,'fs'
, and'csv-parser'
requirements. The functionrequire()
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 thesqlStatement
is passed to the methodquery
of the modulepg
.
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 thereadStream()
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 thereadStream
methodon()
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 theinsertRecords()
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 |
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; |
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 ) }); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started