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: []
}

Can you please share the final value of the variable `query` before executing it? — Raghav Garg, Oct 29 '20 at 05:15

Raghav Garg · Answer 1 · 2021-07-07T05:36:59.130

4

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 *
`

edited Jul 07 '21 at 05:36

answered Oct 29 '20 at 06:00

Raghav Garg

3,601
2
23
32

my data looks like this: ``` const values = [ ['nilesh', 'bailey'], ['nilesh@gmail.com', 'bailey@gmail.com'] ] ``` This is a nested array and does not match the `value_types` you have mentioned. How do Insert nested arrays? – Amit Erandole Oct 29 '20 at 06:42
I tried changing `value types` to `[['text'], ['text']];` but that doesn't work either :( – Amit Erandole Oct 29 '20 at 06:45
1

@AmitErandole I have updated the code with more explanation, please check it out and share if it is still not working. Also, let me know if there is still any doubt. – Raghav Garg Oct 29 '20 at 07:42
I tried this already. The error I get from the above code is: ` UnhandledPromiseRejectionWarning: error: function unnest(text[], text[]) does not exist` Actually those columns are `varchar ` (see update in screenshot above) – Amit Erandole Oct 29 '20 at 11:28
1

Hey, thanks for the update, your code seems right to me, can you try to run the raw query inside of DB?? `INSERT INTO users ("username", "email") VALUES (unnest(ARRAY[ 'nilesh', 'bailey' ]::varchar(50)[], ARRAY[ 'nilesh@gmail.com', 'bailey@gmail.com' ]::varchar(50)[])) RETURNING *` I have removed the double quotes from around the varchar(50). – Raghav Garg Oct 29 '20 at 12:07
Ok just tried that. Now I get `Query 1 ERROR: ERROR: syntax error at or near "[" LINE 10: (unnest([ 'nilesh', 'bailey' ]::varchar(50)[], [ 'nilesh@gm...` – Amit Erandole Oct 29 '20 at 12:10
Could I be doing unnest all wrong? https://stackoverflow.com/questions/41295572/how-do-i-insert-into-a-table-values-from-two-arrays-in-postgres I am totally new to this so I can't interpret this properly – Amit Erandole Oct 29 '20 at 12:15
1

I got it working @Raghav. I need to learn most about postgres to figure this out honestly – Amit Erandole Oct 29 '20 at 12:30

score 3 · Accepted Answer · answered Oct 29 '20 at 12:30

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' ]
  ]
}

Ohh, nice find. I missed, `SELECT * FROM ` incase of `unnest`. :) — Raghav Garg, Oct 29 '20 at 12:38

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

2 Answers2

Update 1