admin管理员组

文章数量:1332382

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', '[email protected]'),
      ('nilesh', '[email protected]'),
      ('nikhil', '[email protected]')
      RETURNING *;
  `);

  return useResult;
};

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

      ('amite', '[email protected]'),
      ('nilesh', '[email protected]'),
      ('nikhil', '[email protected]')

What I have tried so far is:

const addManyUsers = async(connection = slonik) => {
  const keys = [
    'username', 'email'
  ];
  
  const values = [
    ['nilesh', 'bailey'],
    ['[email protected]', '[email protected]']
  ]
  
  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._pile (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 .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', '[email protected]'], // single full record
  ['bailey', '[email protected]'], // 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' ],
    [ '[email protected]', '[email protected]' ]
  ]
}

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

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', '[email protected]'),
      ('nilesh', '[email protected]'),
      ('nikhil', '[email protected]')
      RETURNING *;
  `);

  return useResult;
};

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

      ('amite', '[email protected]'),
      ('nilesh', '[email protected]'),
      ('nikhil', '[email protected]')

What I have tried so far is:

const addManyUsers = async(connection = slonik) => {
  const keys = [
    'username', 'email'
  ];
  
  const values = [
    ['nilesh', 'bailey'],
    ['[email protected]', '[email protected]']
  ]
  
  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._pile (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/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', '[email protected]'], // single full record
  ['bailey', '[email protected]'], // 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' ],
    [ '[email protected]', '[email protected]' ]
  ]
}

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_mons.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: []
}
  • javascript
  • node.js
  • postgresql
  • slonik
Share edited Oct 29, 2020 at 11:42 Amit Erandole asked Oct 29, 2020 at 4:31 Amit ErandoleAmit Erandole 12.3k23 gold badges70 silver badges103 bronze badges 1
  • 1 Can you please share the final value of the variable query before executing it? – Raghav Garg Commented Oct 29, 2020 at 5:15
Add a ment  | 

3 Answers 3

Reset to default 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", "[email protected]", 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', '[email protected]'], // single full record
  ['bailey', '[email protected]'], // single full record
]

So your final code look something like this

const values = [
  ['nilesh', '[email protected]'], // single full record
  ['bailey', '[email protected]'], // 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 *
`

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', '[email protected]'], // single full record
  ['bailey', '[email protected]'], // 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' ],
    [ '[email protected]', '[email protected]' ]
  ]
}

If the server does not support unnest:

  const values = [
    { username: 'nilesh', email: '[email protected]' },
    { username: 'bailey', email: '[email protected]' },
  ];

  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;
  };

本文标签： javascriptHow do I insert multiple records into my postgres database using SlonikStack Overflow

版权声明：本文标题：javascript - How do I insert multiple records into my postgres database using Slonik? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742293867a2448329.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。