linux 使用knex从Compute Engine上的node.js应用连接到Cloud SQL

如何使用knex从在Compute引擎中运行的node.js应用程序连接到Cloud SQL？我尝试使用公共IP使用和不使用云SQL authproxy（尝试在startup-script中安装和启动代理），但在使用knex-connection调用端点时出错：KnexTimeoutError：Knex：获取连接超时。游泳池可能已经满了。您是否错过了.transacting（trx）呼叫？
下面是我的启动脚本：

set -v

# Talk to the metadata server to get the project id
PROJECTID=$(curl -s "http://metadata.google.internal/computeMetadata/v1/project/project-id" -H "Metadata-Flavor: Google")
REPOSITORY="..."
# CMS="..."

# Install logging monitor. The monitor will automatically pick up logs sent to
# syslog.
curl -s "https://storage.googleapis.com/signals-agents/logging/google-fluentd-install.sh" | bash
service google-fluentd restart &

# Install dependencies from apt
apt-get update
apt-get install build-essential

apt-get install -yq ca-certificates git supervisor postgresql-client
apt-get install --yes liblcms2-2 liblcms2-dev liblcms2-utils

# Install nodejs
rm -r /opt/nodejs
mkdir /opt/nodejs
curl https://nodejs.org/dist/v14.0.0/node-v14.0.0-linux-x64.tar.gz | tar xvzf - -C /opt/nodejs --strip-components=1
ln -s -f /opt/nodejs/bin/node /usr/bin/node
ln -s -f /opt/nodejs/bin/npm /usr/bin/npm
# Get the application source code from the Google Cloud Repository.
# git requires $HOME and it's not set during the startup script.
export HOME=/root
rm -r /opt/app
# rm -r /cms
git config --global credential.helper gcloud.sh
git clone https://source.developers.google.com/p/${PROJECTID}/r/${REPOSITORY} /opt/app

# Install app dependencies
cd /opt/app
git checkout development

npm install
npm install bunyan @google-cloud/logging-bunyan 
npm install sharp@0.29.0



# Create a nodeapp user. The application will run as this user.
cd /
cd /home/app
userdel -f app
cd /
rm -r /home/app
cd /opt/app
useradd -m -d /home/app app
chown -R app:app /opt/app
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.3.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy
export GOOGLE_APPLICATION_CREDENTIALS=/service-account-key/key.json
export INSTANCE_HOST='<public-ip>'
export DB_PORT='5432'
export DB_USER=’...’
export DB_PASS=’...’
export DB_NAME=’...’
./cloud_sql_proxy -instances=dreamlouvre:europe-west1:dream-louvre-sql=tcp:5432 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
# Configure supervisor to run the node app.
rm /etc/supervisor/conf.d/node-app.conf
cat >/etc/supervisor/conf.d/node-app.conf << EOF
[program:app]
directory=/opt/app
command=npm start
autostart=true
autorestart=true
user=app
environment=HOME="/home/app",USER="app",NODE_ENV="development",SQL_CONNECTION_NAME="...",SQL_DB_NAME="...",SQL_USER="...",SQL_PW="...",
stdout_logfile=syslog
stderr_logfile=syslog
EOF
supervisorctl reread
supervisorctl update
# Application should now be running under supervisor

下面是我的knex示例脚本：

exports.initKnex = () => {

    const configuration = {
        user: config.config.sqlUser, // e.g. 'my-user'
        password: config.config.sqlPw, // e.g. 'my-user-password'
        database: config.config.sqlDbName, // e.g. 'my-database'
    };
    configuration.host = `${config.config.sqlConnectionName}`;
    const knex = Knex({client: 'pg', connection: configuration});
    knex.client.pool.max = 1;
    knex.client.pool.min = 1;
    knex.client.pool.createTimeoutMillis = 30000; // 30 seconds
    knex.client.pool.idleTimeoutMillis = 600000; // 10 minutes
    knex.client.pool.createRetryIntervalMillis = 200; // 0.2 seconds
    knex.client.pool.acquireTimeoutMillis = 600000; // 10 minutes
    return knex;
}

下面是导致错误的端点：

exports.getCurrency = async (req, res) => {

    let response = {};
    try{
        response = await knex('exchangeRates').where({'countryCode': req.params.cc}).select('currencyCode', 'rate', 'digits');
        //console.log("RESPONSE: ", response);
      }
    catch (err){
        console.log("error: ", err);
        return res.status(500).json(err);
    }
    return res.status(200).json({'data':response});

}

我注意到你的配置有些问题。
首先，您正在下载Cloud SQL Proxy的v2（v2.3.0），但您的CLI调用命令使用的是v1语法./cloud_sql_proxy。这是旧的v1方法，而./cloud-sql-proxy现在在v2中使用。您可以查看migration guide以了解这两个CLI命令和标志之间的差异。
简而言之，v2格式的./cloud_sql_proxy命令如下：

./cloud-sql-proxy --port=5432 --credentials-file=$GOOGLE_APPLICATION_CREDENTIALS dreamlouvre:europe-west1:dream-louvre-sql &

其次，由于您使用的是Cloud SQL Proxy，因此您的INSTANCE_HOST变量不应设置为您的Cloud SQL示例IP地址，而应设置为127.0.0.1以与Proxy交互。
第三，看起来好像您的Knex配置缺少指定的port字段。它应该看起来像这样（see full code sample here）

const configuration = {
  host: process.env.INSTANCE_HOST, // e.g. '127.0.0.1'
  port: process.env.DB_PORT, // e.g. '5432'
  user: process.env.DB_USER, // e.g. 'my-user'
  password: process.env.DB_PASS, // e.g. 'my-user-password'
  database: process.env.DB_NAME, // e.g. 'my-database'
}

最后，我的建议是使用Cloud SQL Node.js Connector而不是云SQL代理。它是Proxy的直接进程内替代，提供所有相同的好处，但在本机npm包中。
您可以按如下方式安装它：

npm install @google-cloud/cloud-sql-connector

它还支持Knex，如下所示：

const Knex = require('knex');
const {Connector} = require('@google-cloud/cloud-sql-connector');

// connectWithConnector initializes connection pool for a Cloud SQL instance
// of Postgres using the Cloud SQL Node.js Connector.
const connectWithConnector = async config => {
  const connector = new Connector();
  const clientOpts = await connector.getOptions({
    instanceConnectionName: 'project-id:region:instance-name', // Cloud SQL Instance Connection Name
    ipType: 'PUBLIC',
  });
  const dbConfig = {
    client: 'pg',
    connection: {
      ...clientOpts,
      user: process.env.DB_USER, // e.g. 'my-user'
      password: process.env.DB_PASS, // e.g. 'my-user-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
    },
    // ... Specify additional properties here.
    ...config,
  };
  // Establish a connection to the database.
  return Knex(dbConfig);

linux 使用knex从Compute Engine上的node.js应用连接到Cloud SQL

1条答案

相关问题

热门标签

最新问答