javascript - 如何使用 Slonik 将多条记录插入我的 postgres 数据库？

Question

我是一名前端开发人员，这是我第一次将Slonik与 postgresql 一起使用。

我想知道如何通过使用函数参数插入数据（硬编码）来使这个查询动态化：

const addMany = async (connection = slonik) => {
  const useResult = await connection.query(sql`
    INSERT into 
      users (username, email) 
    VALUES 
      ('amite', '[e-mail]'),
      ('nilesh', '[e-mail]'),
      ('nikhil', '[e-mail]')
      RETURNING *;
  `);

  return useResult;
};

我需要使用字符串连接创建元组吗？我很困惑

      ('amite', '[e-mail]'),
      ('nilesh', '[e-mail]'),
      ('nikhil', '[e-mail]')

到目前为止，我尝试过的是：

const addManyUsers = async(connection = slonik) => {
  const keys = [
    'username', 'email'
  ];
  
  const values = [
    ['nilesh', 'bailey'],
    ['[e-mail]', '[e-mail]']
  ]
  
  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
}

当我运行它时，我得到了错误：

(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.

这就是我的表结构的样子。我正在使用`varchar(50)

我究竟做错了什么？

@RaghavGarg。这是根据您的建议更新的代码：

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

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

const values = [
  ['nilesh', '[e-mail]'], // single full record
  ['bailey', '[e-mail]'], // 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()

上面的查询扩展为：

{
  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' ],
    [ '[e-mail]', '[e-mail]' ]
  ]
}

我现在从中得到的错误是：

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

标签： javascriptnode.jspostgresqlslonik

Answer 1

传递给 method 的参数存在问题sql.unnest。它将数据数组作为第一个参数，将类型数组作为第二个参数。

这也是错误所说的

列类型长度必须匹配元组成员长度

所以你的代码应该变成类似

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

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

文档sql.unnest

（元组：$ReadOnlyArray<$ReadOnlyArray>，columnTypes：$ReadOnlyArray）：UnnestSqlTokenType；

---

此外，您应该考虑将代码包装在try/catch块中并正确处理错误。

（节点：5975）[DEP0018] DeprecationWarning：不推荐使用未处理的承诺拒绝。将来，未处理的 Promise 拒绝将使用非零退出代码终止 Node.js 进程。

---

更新 1

该变量value_types是一个数组，其中包含您使用查询插入的每一列的类型。

所以value_types应该总是有与任何成员相同数量的元素values

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

并且 types 数组中的每个索引都应对应于成员的正确值。所以

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

// any values[i] should be
values[i] = ["nilesh", "[e-mail]", 123]

---

我错过了一件事，这values也是错误的，每个成员都应该是一个有效的记录，即单个记录的所有列值。

变量应该看起来像这样

const values = [
  ['nilesh', '[e-mail]'], // single full record
  ['bailey', '[e-mail]'], // single full record
]

所以你的最终代码看起来像这样

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