How to Use Node to Insert CSV Data into Postgres (Part 1)
Introduction to using Node to insert CSV data into Postgres
When you’re using PostgreSQL to store and manage your data, you’ll find that you’ll sometimes need to insert data from a CSV file into a table. This task can be easily accomplished in a simple Node.js application. In this two-part article series, we’ll show you how to use Node to insert CSV data into Postgres. First, we’ll create a PostgreSQL table to use in our example, then we’ll set up our Node project and create some CSV data that can be inserted into our table.
Sample CSV data to use for the PostgreSQL insertion
Shown below is the sample CSV data that we’ll be using to insert into our Postgres table with Node.js:
1 2 3 4 | ID, Str, Int, Bool 1, Hello world, 123, true 2, Yo what up?, 42, false 3, Excelsior!, 987, true |
Preventing SQL injection attacks while inserting Postgres data with Node
There’s a very real danger of SQL injection attacks when you attempt to insert CSV data into a PostgreSQL table.
To minimize the risk of these attacks, avoid escaping any SQL row values, and steer clear of using any potentially volatile operators such as +
, -
, =
, or even @
. If you must have such operators in your SQL file, it’s wise to put a tab character (\t
) before them.
Another option is to use a third-party Node middleware package such as sequelize
or sql-injection
. The following command shows how you can install Sequelize for a Node-Postgres project:
1 2 | npm install --save sequelize npm install --save pg pg-hstore |
Packages like node-mysql
can also help to escape values safely by using the ?
placeholder in the SQL statement string.
Prerequisites to using PostgreSQL and Node.JS
Before we turn our attention to our code examples, let’s go over some prerequisites that will be necessary for this task:
You’ll need to have PostgreSQL installed on your device. To check whether you have this service installed, just use the command
service postgresql status
, which will indicate if the status isactive
.You’ll also need to have
psql
, the command-line interface for Postgres, inatalled. You can use the commandpsql -V
to check if you already have it installed. You can also use theps aux | grep postgres
command in Linux or macOS, which will return a list of all processes running Postgres.
Check that both Node and the ‘npm’ package manager are installed
If you have Node.JS installed on your machine, you can use the commands node -v
and npm -v
to see which version of Node and the Node Package Manager you have.
Use the Node Package Manager to install the ‘pg’ Postgres packages
To interact with PostgreSQL from a Node.js application, you’ll also need to install the pg
package and add it to your dependencies. You can accomplish this by using the following command in your project directory:
1 | npm i pg |
You can also use npm
to install the fast-csv
, csv-writer
, json2csvand
csv-parser` modules:
1 | npm i csv-parser |
Create a PostgreSQL table for the SQL data
We can use psql
to create a new PostgreSQL table that we’ll be using in our code examples. The following command shows how to connect to Postgres with psql
:
1 | psql -d some_database -U objectrocket |
NOTE: The -d
and -U
flags are used to connect to Postgres using a specific database name and Postgres user role.
Once we’re in the psql
interface, we can execute the following SQL statement to create our table:
1 2 3 4 5 6 | CREATE TABLE csv_table ( ID INT PRIMARY KEY, Str VARCHAR(64) NOT NULL, INT INT NOT NULL, Bool BOOLEAN NOT NULL ); |
This SQL statement should return a response saying CREATE TABLE
. At this point, we can type \q
and press Return to exit psql
.
Create a Node project directory and the necessary files
Next, we’ll set up our Node project. We can use the mkdir
command followed by the directory name to create a directory on our server. Once the directory is created, we’ll use cd
to change into that directory, and then we’ll create a CSV file with some data that we’d like to insert into Postgres. We’ll also create a new JavaScript file for the Node application.
Let’s check out an example of how to do this using the UNIX touch
command to create a file:
1 2 3 4 | mkdir node-postgres cd node-postgres touch insert-csv.js # create a file for Node touch my-data.csv # create a CSV file |
NOTE: You can also use the commands code
and subl
instead of touch
if you’d like to use the VS Code or Sublime IDEs.
Conclusion to setting up a Node project for CSV data insertion into Postgres
If you’re working with data in PostgreSQL, it’s important to know how to insert data into a table from a CSV file. In this tutorial, we looked at some of the initial steps involved in this process. We created our table, set up our Node project on the file system and created some sample CSV data. The next article in this two-part series will pick up where we left off, examining the code needed when using Node to insert CSV data into a PostgreSQL table.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started