postgresql 启动应用程序时使用 pg-promise 验证数据库连接

Verify database connection with pg-promise when starting an app

提问by Steven L.

I am building an express application that connects to a postgres database using the pg-promisemodule.

我正在构建一个使用pg-promise模块连接到 postgres 数据库的快速应用程序。

I would like to ensure that the database connection is successful when starting the application server. In other words, if the connection to the database fails, I'd like to throw an error.

我想确保启动应用服务器时数据库连接成功。换句话说，如果与数据库的连接失败，我想抛出一个错误。

My server.js file is as follows:

我的 server.js 文件如下：

const express = require("express");

const databaseConfig= {
  "host": "localhost",
  "port": 5432,
  "database": "library_app",
  "user": "postgres"
};

const pgp = require("pg-promise")({});
const db = pgp(databaseConfig);

const app = express();
const port = 5000;

app.listen(port, (err) => {
  console.log(`running server on port: ${port}`);
});

The current configuration will start the express server regardlessof whether the database connection is valid, which is not the behavior I would like.

无论数据库连接是否有效，当前配置都会启动 express 服务器，这不是我想要的行为。

I tried browsing the docs but couldn't find a solution. I also tried

我尝试浏览文档但找不到解决方案。我也试过

const db = pgp(databaseConfig).catch((err) => { // blow up });

but that didn't work because pgpdoes not return a promise.

但这不起作用，因为pgp不返回承诺。

回答by vitaly-t

I am the author of pg-promise;) And this isn't the first time this question is asked, so I'm giving it a detailed explanation here.

我是pg-promise的作者；) 这不是第一次被问到这个问题，所以我在这里给它一个详细的解释。

When you instantiate a new database object like this:

当你像这样实例化一个新的数据库对象时：

const db = pgp(connection);

...all it does - creates the object, but it does not try to connect. The library is built on top of the connection pool, and only the actual query methods request a connection from the pool.

...它所做的一切 - 创建对象，但它不尝试连接。该库建立在连接池之上，只有实际的查询方法从池中请求连接。

From the official documentation:

来自官方文档：

Object dbrepresents the database protocol, with lazy database connection, i.e. only the actual query methods acquire and release the connection. Therefore, you should create only one global/shared dbobject per connection details.

对象db代表数据库协议，采用惰性数据库连接，即只有实际的查询方法获取和释放连接。因此，您应该db为每个连接详细信息仅创建一个全局/共享对象。

However, you can ask the library to connect without executing any query, by using method connect, as shown further.

但是，您可以使用connect方法要求库在不执行任何查询的情况下进行连接，如下所示。

And while this method is no longer a recommended way for chaining queries, ever since support for Taskshas been introduced (as a safer approach), it still comes in handy checking for the connection in general.

虽然此方法不再是链接查询的推荐方式，但自从引入了对任务的支持（作为一种更安全的方法）后，它通常仍然可以方便地检查连接。

I copied the example from my own post: https://github.com/vitaly-t/pg-promise/issues/81

我从我自己的帖子中复制了示例：https: //github.com/vitaly-t/pg-promise/issues/81

Below is an example of doing it in two ways at the same time, so you can choose whichever approach you like better.

下面是同时以两种方式进行的示例，因此您可以选择更喜欢的方法。

const initOptions = {
    // global event notification;
    error(error, e) {
        if (e.cn) {
            // A connection-related error;
            //
            // Connections are reported back with the password hashed,
            // for safe errors logging, without exposing passwords.
            console.log('CN:', e.cn);
            console.log('EVENT:', error.message || error);
        }
    }
};

const pgp = require('pg-promise')(initOptions);

// using an invalid connection string:
const db = pgp('postgresql://userName:password@host:port/database');

db.connect()
    .then(obj => {
        // Can check the server version here (pg-promise v10.1.0+):
        const serverVersion = obj.client.serverVersion;

        obj.done(); // success, release the connection;
    })
    .catch(error => {
        console.log('ERROR:', error.message || error);
    });

Outputs:

输出：

CN: postgresql://userName:########@host:port/database EVENT: getaddrinfo ENOTFOUND host host:5432 ERROR: getaddrinfo ENOTFOUND host host:5432

CN: postgresql://userName:########@host:port/database EVENT: getaddrinfo ENOTFOUND host host:5432 ERROR: getaddrinfo ENOTFOUND host host:5432

Every error in the library is first reported through the global errorevent handler, and only then the error is reported within the corresponding .catchhandler.

库中的每个错误首先通过全局错误事件处理程序报告，然后才在相应的.catch处理程序中报告错误。

Alternative

选择

Instead of establishing the connection manually, you can simply execute a type of query that would always succeed for a valid connection, like the following one:

无需手动建立连接，您只需执行一种对于有效连接始终会成功的查询，如下所示：

db.func('version')
    .then(data => {
        // SUCCESS
        // data.version =
        // 'PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit'
    })
    .catch(error => {
        // connection-related error
    });

However, the example with connecthas advantages, such as no queries needs to be executed, and it provides the server version in a simple form.

但是，带有的示例connect具有优点，例如不需要执行查询，并且以简单的形式提供服务器版本。

API links:

API链接：

• Method func
• Method connect
• Event error

• 方法函数
• 方法连接
• 事件错误