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:

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:

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 is active.

• You’ll also need to have psql, the command-line interface for Postgres, inatalled. You can use the command psql -V to check if you already have it installed. You can also use the ps 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:

You can also use npm to install the fast-csv, csv-writer, json2csvandcsv-parser` modules:

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:

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:

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:

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.