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, 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
sql-injection. The following command shows how you can install Sequelize for a Node-Postgres project:
npm install --save pg pg-hstore
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 is
You’ll also need to have
psql, the command-line interface for Postgres, inatalled. You can use the command
psql -Vto check if you already have it installed. You can also use the
ps aux | grep postgrescommand 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:
You can also use
npm to install the
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
-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:
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
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
Let’s check out an example of how to do this using the UNIX
touch command to create a file:
touch insert-csv.js # create a file for Node
touch my-data.csv # create a CSV file
NOTE: You can also use the commands
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