Node Postgres Insert Multiple Rows

14 Sep 2018

You might be wondering how to insert multiple rows into a table using Node Postgres. Most examples show adding only one row at a time. Some solutions recommend using a control flow library like async. But this technique is slow and inefficient because it uses too many connections or sends the queries one by one. Other solutions recommend using a heavyweight ORM, but this might require a significant rewrite of your application.

It's important to note the Postgres supports inserting multiple rows in query natively. We need to issue a query that looks like this:

INSERT INTO "users" ("email") VALUES ('test1@example.com'), ('test2@example.com')

This query inserts two rows into the table users. The rows we are inserting have the email column defined.

You may be wondering how many rows can you insert with one query? According to the PostgreSQL 9.5 docs a query has about 1GB of memory to use for parsing the query. I think this is plenty to be inserting thousands of rows depending on how many columns you have. But eventually, this technique may strain both your node process and your Postgres database. Brian Carlson, author + maintainer of node-pg, has a module to help copy large amounts of data into Postgres utilizing streams. Because node and Postgres both support streams they are a potent combo for streaming huge datasets. Check out pg-copy-streams if you need to insert millions of rows.

So Postgres supports inserting multiple rows in one query. But the parameterized queries that ship with node-postgres do not. There are two lightweight options I want to introduce you to for inserting multiple rows in one query.

  1. pg-format by datalanche
  2. node-sql by Brian Carlson

Let me show you an example of each.

Using pg-format to insert multiple rows with Node Postgres

'use strict';

const pg = require('pg');
const format = require('pg-format');

let users = [['test@example.com', 'Fred'], ['test2@example.com', 'Lynda']];
let query1 = format('INSERT INTO users (email, name) VALUES %L returning id', users);

async function run() {
  let client;
  try {
    client = new pg.Client({
      connectionString: 'postgresql://localhost/node_example'
    });
    await client.connect();
    let {rows} = await client.query(query1);
    console.log(rows);
  } catch (e) {
    console.error(e);
  } finally {
    client.end();
  }
}

run();

pg-format provides the %L pattern that outputs an escaped SQL literal. In the example above query1 is equal to:

INSERT INTO users (email, name) VALUES ('test@example.com', 'Fred'), ('test2@example.com', 'Lynda') returning id

format takes two parameters. The first is the formatted SQL string containing pattern markers similar to a c style sprintf function. The second parameter is the array of arrays representing the user rows we want to insert into the table users.

If you have more parameters for you formatted SQL string, they must match the position following the string. For example, let's say you want to make the table name dynamic, query1 could be adjusted to:

format('INSERT INTO %s (email, name) VALUES %L returning id', 'users', users);

Using pg-format is a great lightweight addition library to support inserting multiple rows with node Postgres.

Using node-sql to insert multiple rows with Node Postgres

'use strict';

const pg = require('pg');
const sql = require('sql');

let usersToInsert = [{
  email: 'test@example.com',
  name: 'Fred'
}, {
  email: 'test2@example.com',
  name: 'Lynda'
}];

let User = sql.define({
  name: 'users',
  columns: [
    'id',
    'name',
    'email'
  ]
});

async function run() {
  let client;
  try {
    client = new pg.Client({
      connectionString: 'postgresql://localhost/node_example'
    });
    await client.connect();
    let query = User.insert(usersToInsert).returning(User.id).toQuery();
    console.log(query);
    let {rows} = await client.query(query);
    console.log(rows);
  } catch (e) {
    console.error(e);
  } finally {
    client.end();
  }
}

run();

This code accomplishes the same thing as the pg-format. We insert multiple user rows into the table users. With the sql module we must define the user table using sql.define. The name is the name of the table. The columns are an array of strings representing the columns in the table.

Once we have an sql table object we can generate the query using:

let query = User.insert(usersToInsert).returning(User.id).toQuery();

This snippet produces a parameterized query that the node Postgres can use. Brian Carlson, the creator of node-postgres, also created this module so I believe this is why it produces the same format.

{ text:
   'INSERT INTO "users" ("email", "name") VALUES ($1, $2), ($3, $4) RETURNING "id"',
  values: [ 'test@example.com', 'Fred', 'test2@example.com', 'Lynda' ] }

The User.insert function can take an object representing one row or an array of objects representing many.

I have a personal preference for sql because it's kind of like a lightweight ORM that enforces some structure to my query generation code.

For inserting multiple rows using node-postgres, I believe that it's still, a lightweight module that you could bring in to accomplish only the bulk insertion of rows.