Omiting column names / inserting objects directly into node-postgres

Question

I'd like to pass dictionaries with column names as keys, thus avoiding declaring the column names within the query itself (typing them directly).

---

Assume I have a table User with 2 column names:

• idUser(INT)
• fullName(VARCHAR)

To create a record using node-postgres, I'll need to declare within the query the column names like so:

    var idUser   = 2;
    var fullName = "John Doe";
    var query = 'INSERT INTO User(idUser, age) VALUES ($1, $2)';

    database.query(query, [idUser, fullName], function(error, result) {
      callback(error, result.rows);
      database.end();
    });

I'd prefer if there was a way to just pass a dictionary & have it infer the column names from the keys - If there's an easy trick I'd like to hear it.

E.g something like this:

    var values = {
      idUser  : 2,
      fullName: "John Doe"
    };
    var query = 'INSERT INTO User VALUES ($1)';

    database.query(query, [values], function(error, result) {
      callback(error, result.rows);
      database.end();
    });

Answer 1

A complete example of doing it with pg-promise:

const pgp = require('pg-promise')(/*options*/);
const cn = 'postgres://username:password@host:port/database';
const db = pgp(cn);

const values = {
    idUser: 2,
    fullName: 'John Doe'
};

// generating the insert query:
const query = pgp.helpers.insert(values, null, 'User');
//=> INSERT INTO "User"("idUser","fullName") VALUES(2,'John Doe')

db.none(query)
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
    });

And with focus on high performance it would change to this:

// generating a set of columns from the object (only once):
const cs = new pgp.helpers.ColumnSet(values, {table: 'User'});

// generating the insert query:
const query = pgp.helpers.insert(values, cs);
//=> INSERT INTO "User"("idUser","fullName") VALUES(2,'John Doe')

Answer 2

There's no support for key-value values in the insert statement, so it can not be done with native sql.

However, the node-postgres extras page mentions multiple sql generation tools, and for example Squel.js parameters can be used to construct sql in a way very close like what you're looking for:

squel.insert()
    .into("User")
    .setFieldsRows([
      { idUser: 2, fullName: "John Doe" }
    ])
    .toParam()

// => { text: 'INSERT INTO User (idUser, fullName) VALUES (?, ?)',
//      values: [ 2, 'John Doe' ] }

Answer 3

My case was a bit special as I had a field named order in the JSON object which is a keyword in SQL. Therefore I had to wrap everything in quotes using a JSONify() function.

Also note the numberedParameters argument as well as the double quotes around the 'Messages' string.

import { pool } from './connection';

function JSONify(obj: Map<string, any>) {
  var o = {};
  for (var i in obj) {
    o['"' + i + '"'] = obj[i]; // make the quotes
  }
  return o;
}

// I have a table named "Messages" with the columns order and name
// I also supply the createdAt and updatedAt timestamps just in case
const messages = [
  {
    order: 0,
    name: 'Message with index 0',
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  }
]

// Create the insert statement
const insertStatement = insert({ numberedParameters: true })
    .into('"Messages"')
    .setFieldsRows(messages.map((message) => JSONify(message)))
    .toParam();

console.log(insertStatement);
// Notice the quotes wrapping the table and column names
// => { text: 'INSERT INTO "Messages" ("order", "name", "createdAt", "updatedAt") VALUES ($1, $2, $3, $4)',
//      values: [ 0, 'Message with index 0', '2022-07-22T13:51:27.679Z', '2022-07-22T13:51:27.679Z' ] }

// Create 
await pool.query(insertStatement.text, insertStatement.values);

See the Squel documentation for more details.

And this is how I create the pool object if anyone is curious.

import { Pool } from 'pg';

import { DB_CONFIG } from './config';

export const pool = new Pool({
  user: DB_CONFIG[process.env.NODE_ENV].username,
  host: DB_CONFIG[process.env.NODE_ENV].host,
  database: DB_CONFIG[process.env.NODE_ENV].database,
  password: DB_CONFIG[process.env.NODE_ENV].password,
  port: DB_CONFIG[process.env.NODE_ENV].port,
});