每当我编写项目并使用 node-postgres 时，我都喜欢在其中创建一个文件并使与数据库的所有交互都通过此文件进行。这有几个目的：
¥Whenever I am writing a project & using node-postgres I like to create a file within it and make all interactions with the database go through this file. This serves a few purposes:
-
允许我的项目适应对 node-postgres API 的任何更改，而无需追踪我在应用中直接使用 node-postgres 的所有地方。
¥Allows my project to adjust to any changes to the node-postgres API without having to trace down all the places I directly use node-postgres in my application.
-
允许我有一个地方来放置数据库周围的日志和诊断信息。
¥Allows me to have a single place to put logging and diagnostics around my database.
-
允许我对我的数据库访问代码进行自定义扩展并在整个项目中共享它。
¥Allows me to make custom extensions to my database access code & share it throughout the project.
-
允许在一个地方引导和配置数据库。
¥Allows a single place to bootstrap & configure the database.
example
位置并不重要 - 我发现它通常最终会变得有些特定于应用，并且符合你使用的任何文件夹结构约定。对于这个例子，我将使用一个像这样结构的 express 应用：
¥The location doesn't really matter - I've found it usually ends up being somewhat app specific and in line with whatever folder structure conventions you're using. For this example I'll use an express app structured like so:
- app.js
- index.js
- routes/
- index.js
- photos.js
- user.js
- db/
- index.js <--- this is where I put data access code通常，我会像这样开始我的 db/index.js 文件：
¥Typically I'll start out my db/index.js file like so:
import pg from 'pg'
const { Pool } = pg
const pool = new Pool()
export const query = (text, params, callback) => {
return pool.query(text, params, callback)
}就是这样。但现在，在我的应用中的其他地方，我不再直接需要 pg，而是需要这个文件。这是 routes/user.js 中路由的示例：
¥That's it. But now everywhere else in my application instead of requiring pg directly, I'll require this file. Here's an example of a route within routes/user.js:
// notice here I'm requiring my database adapter file
// and not requiring node-postgres directly
import * as db from '../db/index.js'
app.get('/:id', async (req, res, next) => {
const result = await db.query('SELECT * FROM users WHERE id = $1', [req.params.id])
res.send(result.rows[0])
})
// ... many other routes in this file想象一下，我们在 routes/ 目录下的许多文件中分散了许多路由。我们现在想要返回并记录执行的每个查询、花费的时间以及返回的行数。如果我们在每个路由文件中都直接需要 node-postgres，则我们必须去编辑每个路由 - 这将花费很长时间并且真的很容易出错！但幸运的是，我们将数据访问放入了 db/index.js。让我们去添加一些日志记录：
¥Imagine we have lots of routes scattered throughout many files under our routes/ directory. We now want to go back and log every single query that's executed, how long it took, and the number of rows it returned. If we had required node-postgres directly in every route file we'd have to go edit every single route - that would take forever & be really error prone! But thankfully we put our data access into db/index.js. Let's go add some logging:
import pg from 'pg'
const { Pool } = pg
const pool = new Pool()
export const query = async (text, params) => {
const start = Date.now()
const res = await pool.query(text, params)
const duration = Date.now() - start
console.log('executed query', { text, duration, rows: res.rowCount })
return res
}这很快！现在，我们应用中的所有查询都已记录。
¥That was pretty quick! And now all of our queries everywhere in our application are being logged.
注意：我没有记录查询参数。根据你的应用，你可能在数据库中存储加密密码或其他敏感信息。如果你记录查询参数，你可能会意外记录敏感信息。不过，每个应用都不同，因此请做最适合你的事情！
¥note: I didn't log the query parameters. Depending on your application you might be storing encrypted passwords or other sensitive information in your database. If you log your query parameters you might accidentally log sensitive information. Every app is different though so do what suits you best!
现在如果我们需要从池中检出一个客户端以在事务中连续运行多个查询怎么办？当需要执行此操作时，我们可以在 db/index.js 文件中添加另一种方法：
¥Now what if we need to check out a client from the pool to run several queries in a row in a transaction? We can add another method to our db/index.js file when we need to do this:
import pg from 'pg'
const { Pool } = pg
const pool = new Pool()
export const query = async (text, params) => {
const start = Date.now()
const res = await pool.query(text, params)
const duration = Date.now() - start
console.log('executed query', { text, duration, rows: res.rowCount })
return res
}
export const getClient = () => {
return pool.connect()
}好的。很棒 - 可能起作用的最简单的事情。似乎我们的一条检查客户端运行事务的路由在某些情况下忘记调用 release！哦不！我们正在泄漏客户端，并且有数百条这样的路由需要审核。好在我们所有的客户端访问都通过这个单一文件。让我们在这里添加一些更深入的诊断信息，以帮助我们追踪客户端泄漏发生的位置。
¥Okay. Great - the simplest thing that could possibly work. It seems like one of our routes that checks out a client to run a transaction is forgetting to call release in some situation! Oh no! We are leaking a client & have hundreds of these routes to go audit. Good thing we have all our client access going through this single file. Lets add some deeper diagnostic information here to help us track down where the client leak is happening.
export const query = async (text, params) => {
const start = Date.now()
const res = await pool.query(text, params)
const duration = Date.now() - start
console.log('executed query', { text, duration, rows: res.rowCount })
return res
}
export const getClient = async () => {
const client = await pool.connect()
const query = client.query
const release = client.release
// set a timeout of 5 seconds, after which we will log this client's last query
const timeout = setTimeout(() => {
console.error('A client has been checked out for more than 5 seconds!')
console.error(`The last executed query on this client was: ${client.lastQuery}`)
}, 5000)
// monkey patch the query method to keep track of the last query executed
client.query = (...args) => {
client.lastQuery = args
return query.apply(client, args)
}
client.release = () => {
// clear our timeout
clearTimeout(timeout)
// set the methods back to their old un-monkey-patched version
client.query = query
client.release = release
return release.apply(client)
}
return client
}希望这能为我们提供足够的诊断信息来追踪任何泄漏。
¥That should hopefully give us enough diagnostic information to track down any leaks.