• features
• 查询

为了简洁起见，我使用 client.query 方法而不是 pool.query 方法 - 两种方法都支持相同的 API。事实上，pool.query 在内部直接委托给 client.query。

¥For the sake of brevity I am using the client.query method instead of the pool.query method - both methods support the same API. In fact, pool.query delegates directly to client.query internally.

仅限文本

¥Text only

如果你的查询没有参数，则无需将它们包含在查询方法中：

¥If your query has no parameters you do not need to include them to the query method:

await client.query('SELECT NOW() as now')

参数化查询

¥Parameterized query

如果你要将参数传递给查询，则需要避免将字符串参数直接连接到查询文本中。这可能（并且经常）导致 sql 注入漏洞。node-postgres 支持参数化查询，将你的查询文本不加改变地以及你的参数传递给 PostgreSQL 服务器，在那里，使用服务器本身内经过实战测试的参数替换代码将参数安全地替换到查询中。

¥If you are passing parameters to your queries you will want to avoid string concatenating parameters into the query text directly. This can (and often does) lead to sql injection vulnerabilities. node-postgres supports parameterized queries, passing your query text unaltered as well as your parameters to the PostgreSQL server where the parameters are safely substituted into the query with battle-tested parameter substitution code within the server itself.

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', 'brian.m.carlson@gmail.com']
 
const res = await client.query(text, values)
console.log(res.rows[0])
// { name: 'brianc', email: 'brian.m.carlson@gmail.com' }

作为 query() 的第二个参数传递的参数将使用以下规则转换为原始数据类型：

¥Parameters passed as the second argument to query() will be converted to raw data types using the following rules:

null 和 undefined

¥null and undefined

如果参数化 null 和 undefined，则两者都将转换为 null。

¥If parameterizing null and undefined then both will be converted to null.

日期

¥Date

自定义转换为 UTC 日期字符串。

¥Custom conversion to a UTC date string.

Buffer

Buffer 实例未更改。

¥Buffer instances are unchanged.

数组

¥Array

转换为描述 Postgres 数组的字符串。使用此处描述的规则对每个数组项进行递归转换。

¥Converted to a string that describes a Postgres array. Each array item is recursively converted using the rules described here.

对象

¥Object

如果参数化值具有方法 toPostgres，则将调用它，并且其返回值将用于查询。toPostgres 的签名如下：

¥If a parameterized value has the method toPostgres then it will be called and its return value will be used in the query. The signature of toPostgres is the following:

toPostgres (prepareValue: (value) => any): any

提供的 prepareValue 函数可用于将嵌套类型转换为适合数据库的原始数据类型。

¥The prepareValue function provided can be used to convert nested types to raw data types suitable for the database.

否则，如果没有定义 toPostgres 方法，则在参数化值上调用 JSON.stringify。

¥Otherwise if no toPostgres method is defined then JSON.stringify is called on the parameterized value.

其他一切

¥Everything else

所有其他参数化值都将通过对值调用 value.toString 进行转换。

¥All other parameterized values will be converted by calling value.toString on the value.

查询配置对象

¥Query config object

pool.query 和 client.query 都支持将配置对象作为参数，而不是采用字符串和可选参数数组。上面的相同示例也可以这样执行：

¥pool.query and client.query both support taking a config object as an argument instead of taking a string and optional array of parameters. The same example above could also be performed like so:

const query = {
  text: 'INSERT INTO users(name, email) VALUES($1, $2)',
  values: ['brianc', 'brian.m.carlson@gmail.com'],
}
 
const res = await client.query(query)
console.log(res.rows[0])

查询配置对象允许一些更高级的场景：

¥The query config object allows for a few more advanced scenarios:

准备好的语句

¥Prepared statements

PostgreSQL 有 准备好的声明 的概念。node-postgres 通过向查询配置对象提供 name 参数来支持此功能。如果你提供 name 参数，查询执行计划将根据每个连接缓存在 PostgreSQL 服务器上。这意味着如果你使用两个不同的连接，每个连接都必须解析和计划一次查询。node-postgres 为你透明地处理此问题：客户端仅在该特定客户端第一次看到该查询名称时请求解析查询：

¥PostgreSQL has the concept of a prepared statement. node-postgres supports this by supplying a name parameter to the query config object. If you supply a name parameter the query execution plan will be cached on the PostgreSQL server on a per connection basis. This means if you use two different connections each will have to parse & plan the query once. node-postgres handles this transparently for you: a client only requests a query to be parsed the first time that particular client has seen that query name:

const query = {
  // give the query a unique name
  name: 'fetch-user',
  text: 'SELECT * FROM user WHERE id = $1',
  values: [1],
}
 
const res = await client.query(query)
console.log(res.rows[0])

在上面的例子中，客户端第一次看到名为 'fetch-user' 的查询时，它将向 PostgreSQL 服务器发送 'parse' 请求并正常执行查询。第二次，它将跳过 'parse' 请求并将查询的名称发送到 PostgreSQL 服务器。

¥In the above example the first time the client sees a query with the name 'fetch-user' it will send a 'parse' request to the PostgreSQL server & execute the query as normal. The second time, it will skip the 'parse' request and send the name of the query to the PostgreSQL server.

行模式

¥Row mode

默认情况下，node-postgres 读取行并将它们收集到 JavaScript 对象中，其中键与列名匹配，值与每列的相应行值匹配。如果你不需要或不想要此行为，则可以将 rowMode: 'array' 传递给查询对象。这将通知结果解析器绕过将行收集到 JavaScript 对象中，而是将每行作为值数组返回。

¥By default node-postgres reads rows and collects them into JavaScript objects with the keys matching the column names and the values matching the corresponding row value for each column. If you do not need or do not want this behavior you can pass rowMode: 'array' to a query object. This will inform the result parser to bypass collecting rows into a JavaScript object, and instead will return each row as an array of values.

const query = {
  text: 'SELECT $1::text as first_name, $2::text as last_name',
  values: ['Brian', 'Carlson'],
  rowMode: 'array',
}
 
const res = await client.query(query)
console.log(res.fields.map(field => field.name)) // ['first_name', 'last_name']
console.log(res.rows[0]) // ['Brian', 'Carlson']

类型

¥Types

你可以传入一组自定义类型解析器，以便在解析特定查询的结果时使用。types 属性必须符合 类型 API。这是一个每个值都作为字符串返回的示例：

¥You can pass in a custom set of type parsers to use when parsing the results of a particular query. The types property must conform to the Types API. Here is an example in which every value is returned as a string:

const query = {
  text: 'SELECT * from some_table',
  types: {
    getTypeParser: () => val => val,
  },
}