How do I insert multiple records into my postgres database using Slonik?

Question

I am a front end developer and this is my first time using Slonik with postgresql.

I want to know how I can make this query dynamic by inserting the data (which is hardcoded) using function parameters:

const addMany = async (connection = slonik) => {
  const useResult = await connection.query(sql`
    INSERT into 
      users (username, email) 
    VALUES 
      ('amite', 'amite@gmail.com'),
      ('nilesh', 'nil@gmail.com'),
      ('nikhil', 'nik@gmail.com')
      RETURNING *;
  `);

  return useResult;
};

Do I need to create tuples using string concatenation? I am confused

      ('amite', 'amite@gmail.com'),
      ('nilesh', 'nil@gmail.com'),
      ('nikhil', 'nik@gmail.com')

What I have tried so far is:

const addManyUsers = async(connection = slonik) => {
  const keys = [
    'username', 'email'
  ];
  
  const values = [
    ['nilesh', 'bailey'],
    ['nilesh@gmail.com', 'bailey@gmail.com']
  ]
  
  const identifiers = keys.map((key) => {
    return sql.identifier([key]);
  });
  
  const query = sql`
    INSERT INTO users
      (${sql.join(identifiers, sql`, `)})
    VALUES
      (${sql.unnest(values, sql`, `)})
    RETURNING *
  `
  const records = await connection.query(query)
  return records
}

When I run this I get the error:

(node:5975) UnhandledPromiseRejectionWarning: Error: **Column types length must match tuple member length.**
    at Object.createUnnestSqlFragment (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/sqlFragmentFactories/createUnnestSqlFragment.js:29:19)
    at Object.createSqlTokenSqlFragment (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/factories/createSqlTokenSqlFragment.js:27:39)
    at sql (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/slonik/dist/factories/createSqlTag.js:39:65)
    at addManyUsers (/Users/shreekant/Documents/code/node/postgres-starter/app/models/db.js:58:20)
    at Object.<anonymous> (/Users/shreekant/Documents/code/node/postgres-starter/app/models/db.js:72:1)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
    at internal/main/run_main_module.js:17:47
(Use `node --trace-warnings ...` to show where the warning was created)
(node:5975) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:5975) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

This is what my table structure looks like. I am using `varchar(50)

What am I doing wrong?

@RaghavGarg. here is the updated code as per your suggestion:

const keys = [
  'username',
  'email',
];

const identifiers = keys.map((key) => {
  return sql.identifier([key]);
});

const values = [
  ['nilesh', 'nilesh@gmail.com'], // single full record
  ['bailey', 'bailey@gmail.com'], // single full record
]

const values_types = ['varchar', 'varchar'];

const main = async(connection = slonik) => {
  let query = sql`
    INSERT INTO users
      (${sql.join(identifiers, sql`, `)})
    VALUES
      (${sql.unnest(values, values_types)})
    RETURNING *
  `
  try {
    const results = await connection.query(query)
    console.log(results);
    return results
  } catch (error) {
    console.error(error);
  }
}

main()

The query above expands out to:

{
  sql: '\n' +
    'INSERT INTO users\n' +
    '  ("username", "email")\n' +
    'VALUES\n' +
    '  (unnest($1::"varchar(50)"[], $2::"varchar(50)"[]))\n' +
    'RETURNING *\n',
  type: 'SLONIK_TOKEN_SQL',
  values: [
    [ 'nilesh', 'bailey' ],
    [ 'nilesh@gmail.com', 'bailey@gmail.com' ]
  ]
}

The error I get from this now is:

error: type "varchar(50)[]" does not exist
    at Parser.parseErrorMessage (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/shreekant/Documents/code/node/postgres-starter/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 100,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: '81',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_type.c',
  line: '274',
  routine: 'typenameType',
  notices: []
}

Answer 1

If the server does not support unnest:

  const values = [
    { username: 'nilesh', email: 'nilesh@gmail.com' },
    { username: 'bailey', email: 'bailey@gmail.com' },
  ];

  const addMany = async connection => {
    const useResult = await connection.query(sql`
      INSERT INTO users (username, email)
      VALUES
        ${sql.join(
          values.map(({ username, email }) => sql`(${username}, ${email})`),
          sql`,`,
        )}
      RETURNING *;
    `);

    return useResult;
  };

Answer 2

There is an issue with the arguments passed to method sql.unnest. It takes the data array as the first argument and type array as the second argument.

That's what the error also says

Column types length must match tuple member length

So your code should change to something like

const values_types = ['text', 'text'];

const query = sql`
  INSERT INTO users
    (${sql.join(identifiers, sql`, `)})
  VALUES
    (${sql.unnest(values, values_types)})
  RETURNING *
`

Docs for sql.unnest

( tuples: $ReadOnlyArray<$ReadOnlyArray>, columnTypes: $ReadOnlyArray ): UnnestSqlTokenType;

---

Also, you should consider wrapping your code in try/catch block and handle the errors properly.

(node:5975) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

---

Update 1

The variable value_types is an array containing the type for each column that your inserting using the query.

So value_types should always have an equal number of elements as of any member of values

values[i].length === values_types.length

and every index in types array should correspond to the correct value of a member. So

// for
values_types = ["text", "text", "int4"]

// any values[i] should be
values[i] = ["nilesh", "nilesh@gmail.com", 123]

---

I missed one thing, the values is also wrong, every member should be a single valid record i.e. all the column values for a single record.

The variable should look something like this

const values = [
  ['nilesh', 'nilesh@gmail.com'], // single full record
  ['bailey', 'bailey@gmail.com'], // single full record
]

So your final code look something like this

const values = [
  ['nilesh', 'nilesh@gmail.com'], // single full record
  ['bailey', 'bailey@gmail.com'], // single full record
]

const values_types = ['text', 'text'];

const query = sql`
  INSERT INTO users
    (${sql.join(identifiers, sql`, `)})
  VALUES
    (${sql.unnest(values, values_types)})
  RETURNING *
`

Answer 3

This is what finally worked. I needed to use a SELECT * FROM instead of VALUES

let query = sql`
    INSERT INTO users
      (${sql.join(identifiers, sql`, `)})
    SELECT * FROM
      ${sql.unnest(values, values_types)}
    RETURNING *
  `

Here is the whole function:

const keys = [
  'username',
  'email',
];

const identifiers = keys.map((key) => {
  return sql.identifier([key]);
});

const values = [
  ['nilesh', 'nilesh@gmail.com'], // single full record
  ['bailey', 'bailey@gmail.com'], // single full record
]

const values_types = [`varchar`,`varchar`];

const main = async(connection = slonik) => {
  let query = sql`
    INSERT INTO users
      (${sql.join(identifiers, sql`, `)})
    SELECT * FROM
      ${sql.unnest(values, values_types)}
    RETURNING *
  `
  try {
    const results = await connection.query(query)
    console.log(results);
    return results
  } catch (error) {
    console.error(error);
  }
}

main()

This is what the query now expands to:

{
  sql: '\n' +
    'INSERT INTO users\n' +
    '  ("username", "email")\n' +
    'SELECT * FROM\n' +
    '  unnest($1::"varchar"[], $2::"varchar"[])\n' +
    'RETURNING *\n',
  type: 'SLONIK_TOKEN_SQL',
  values: [
    [ 'nilesh', 'bailey' ],
    [ 'nilesh@gmail.com', 'bailey@gmail.com' ]
  ]
}