knex

Connecting to Cloud SQL - PostgreSQL

This demo application can be used to connect to Cloud SQL in two different ways:

The Cloud SQL Node.js Connector (recommended)
The Cloud SQL Auth Proxy

Using the Cloud SQL Node.js Connector package is recommended over the Cloud SQL Auth Proxy as it provides all the same functionality and features but as a npm package. See @google-cloud/cloud-sql-connector.

Before you begin

If you haven't already, set up a Node.js Development Environment by following the Node.js setup guide and create a project.
Create a Cloud SQL for PostgreSQL instance by following these instructions. Note the instance connection name of the instance that you create, and password that you specify for the default 'postgres' user.
- If you don't want to use the default user to connect, create a user.
Create a database for your application by following these instructions. Note the database name.
Set up Application Default Credentials

Note: Defining credentials in environment variables is convenient, but not secure. For a more secure solution, use Secret Manager to help keep secrets safe. You can then define export CLOUD_SQL_CREDENTIALS_SECRET='projects/PROJECT_ID/secrets/SECRET_ID/versions/VERSION' to reference a secret that stores your Cloud SQL database password. The sample app checks for your defined secret version. If a version is present, the app retrieves the DB_PASS from Secret Manager before it connects to Cloud SQL.

Cloud SQL Node.js Connector Usage

Running Locally

To run the demo application locally using the Cloud SQL Node.js Connector, set environment variables and install dependencies as shown below.

Note: The INSTANCE_CONNECTION_NAME for your instance can be found on the Overview page for your instance in the Google Cloud console or by running the following command:

gcloud sql instances describe <INSTANCE_NAME> --format='value(connectionName)'

Linux / Mac OS

Use these terminal commands to initialize environment variables:

export INSTANCE_CONNECTION_NAME='<INSTANCE_CONNECTION_NAME>'
export DB_USER='<DB_USER_NAME>'
export DB_PASS='<DB_PASSWORD>'
export DB_NAME='<DB_NAME>'

Windows/PowerShell

Use these PowerShell commands to initialize environment variables:

$env:INSTANCE_CONNECTION_NAME="<INSTANCE_CONNECTION_NAME>"
$env:DB_USER="<DB_USER_NAME>"
$env:DB_PASS="<DB_PASSWORD>"
$env:DB_NAME="<DB_NAME>"

Testing the application

Next, install the Node.js packages necessary to run the app locally by running the following command:
```
npm install
```
Run the sample app locally with the following command:
```
npm start
```

Navigate towards http://127.0.0.1:8080 to verify your application is running correctly.

Deploy to Google App Engine Standard

To allow your app to connect to your Cloud SQL instance when the app is deployed, modify the app.standard.yaml to add the user, password, database, and an instance connection name to the related environment variables, like in the example below:
```
env_variables:
  INSTANCE_CONNECTION_NAME: <MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
```
To deploy to App Engine Standard, run the following command:
```
gcloud app deploy app.standard.yaml
```
To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:
```
gcloud app browse
```

Deploy to Google App Engine Flexible

To allow your app to connect to your Cloud SQL instance when the app is deployed, modify the app.flexible.yaml to add the user, password, database, and an instance connection name to the related environment variables, like in the example below:
```
env_variables:
  INSTANCE_CONNECTION_NAME: <MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
```
To deploy to App Engine Node.js Flexible Environment, run the following command:
```
gcloud app deploy app.flexible.yaml
```
To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:
```
gcloud app browse
```

Deploy to Cloud Run

See the Cloud Run documentation for more details on connecting a Cloud Run service to Cloud SQL.

Build and deploy the service to Cloud Run:

gcloud run deploy run-sql --source . /
    --allow-unauthenticated \
    --set-env-vars INSTANCE_CONNECTION_NAME=[INSTANCE_CONNECTION_NAME],\
      DB_USER=[MY_DB_USER],DB_PASS=[MY_DB_PASS],DB_NAME=[MY_DB]

Replace environment variables with the correct values for your Cloud SQL instance configuration.

It is recommended to use the Secret Manager integration for Cloud Run instead of using environment variables for the SQL configuration. The service injects the SQL credentials from Secret Manager at runtime via an environment variable.

Create secrets via the command line:

echo -n $INSTANCE_CONNECTION_NAME | \
    gcloud secrets create [INSTANCE_CONNECTION_NAME_SECRET] --data-file=-

Deploy the service to Cloud Run specifying the env var name and secret name:

gcloud run deploy run-sql --source . /
    --allow-unauthenticated \
    --update-secrets INSTANCE_CONNECTION_NAME=[INSTANCE_CONNECTION_NAME_SECRET]:latest, \
      DB_USER=[DB_USER_SECRET]:latest, \
      DB_PASS=[DB_PASS_SECRET]:latest, \
      DB_NAME=[DB_NAME_SECRET]:latest

Navigate your browser to the URL noted in step 2.

For more details about using Cloud Run see http://cloud.run. Review other Node.js on Cloud Run samples.

Deploy to Cloud Functions

To deploy the service to Cloud Functions run the following command:

gcloud functions deploy votes --gen2 --runtime nodejs18 --trigger-http \
  --allow-unauthenticated \
  --entry-point votes \
  --region <INSTANCE_REGION> \
  --set-env-vars INSTANCE_CONNECTION_NAME=<PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME> \
  --set-env-vars DB_USER=$DB_USER \
  --set-env-vars DB_PASS=$DB_PASS \
  --set-env-vars DB_NAME=$DB_NAME

Cloud SQL Auth Proxy Usage

Running Locally

Setting up the Cloud SQL database for the app requires setting up the app for local use.

You may optionally download and install the cloud_sql_proxy by following the instructions.

Instructions are provided below for using the proxy with a TCP connection or a Unix Domain Socket. On Linux or Mac OS you can use either option, but on Windows the proxy currently requires a TCP connection.

Launch proxy with TCP

To run the sample locally with a TCP connection, set environment variables and launch the proxy as shown below.

Linux / Mac OS

Use these terminal commands to initialize environment variables:

export INSTANCE_HOST='127.0.0.1'
export DB_PORT='5432'
export DB_USER='<DB_USER_NAME>'
export DB_PASS='<DB_PASSWORD>'
export DB_NAME='<DB_NAME>'

Then use this command to launch the proxy in the background:

./cloud-sql-proxy --port=5342 "$INSTANCE_CONNECTION_NAME" &

Windows/PowerShell

Use these PowerShell commands to initialize environment variables:

$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
$env:INSTANCE_HOST="127.0.0.1"
$env:DB_PORT="5432"
$env:DB_USER="<DB_USER_NAME>"
$env:DB_PASS="<DB_PASSWORD>"
$env:DB_NAME="<DB_NAME>"

Then use this command to launch the proxy in a separate PowerShell session:

Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "<project-id>:<region>:<instance-name> --port=5342"

Launch proxy with Unix Domain Socket

NOTE: this option is currently only supported on Linux and Mac OS. Windows users should use the Launch proxy with TCP option.

To use a Unix socket, you'll need to create a directory and give write access to the user running the proxy. For example:

sudo mkdir ./cloudsql
sudo chown -R $USER ./cloudsql

Use these terminal commands to initialize other environment variables as well:

export INSTANCE_UNIX_SOCKET='./cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>'
export DB_USER='<DB_USER_NAME>'
export DB_PASS='<DB_PASSWORD>'
export DB_NAME='<DB_NAME>'

Then use this command to launch the proxy in the background:

./cloud_sql_proxy --unix-socket=./cloudsql $INSTANCE_CONNECTION_NAME &

Testing the application

Next, install the Node.js packages necessary to run the app locally by running the following command:
```
npm install
```
Run the sample app locally with the following command:
```
npm start
```

Navigate towards http://127.0.0.1:8080 to verify your application is running correctly.

Deploy to Google App Engine Standard

To allow your app to connect to your Cloud SQL instance when the app is deployed, add the user, password, database, and instance unix socket variables from Cloud SQL to the related environment variables in the app.standard.yaml file. The deployed application will connect via unix sockets.
```
env_variables:
  INSTANCE_UNIX_SOCKET: /cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
```
To deploy to App Engine Standard, run the following command:
```
gcloud app deploy app.standard.yaml
```
To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:
```
gcloud app browse
```

Deploy to Google App Engine Flexible

Add the user, password, database, and instance unix socket variables from Cloud SQL to the related environment variables in the app.flexible.yaml file. The deployed application will connect via unix sockets.
```
env_variables:
  INSTANCE_UNIX_SOCKET: /cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
```
To deploy to App Engine Node.js Flexible Environment, run the following command:
```
gcloud app deploy app.flexible.yaml
```
To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:
```
gcloud app browse
```

Deploy to Cloud Run

See the Cloud Run documentation for more details on connecting a Cloud Run service to Cloud SQL.

Build the container image:

gcloud builds submit --tag gcr.io/[YOUR_PROJECT_ID]/run-sql

Deploy the service to Cloud Run:

gcloud run deploy run-sql --image gcr.io/[YOUR_PROJECT_ID]/run-sql

Take note of the URL output at the end of the deployment process.

Configure the service for use with Cloud Run

gcloud run services update run-sql \
    --add-cloudsql-instances [INSTANCE_CONNECTION_NAME] \
    --set-env-vars INSTANCE_UNIX_SOCKET=[INSTANCE_UNIX_SOCKET],\
      DB_USER=[MY_DB_USER],DB_PASS=[MY_DB_PASS],DB_NAME=[MY_DB]

Replace environment variables with the correct values for your Cloud SQL instance configuration.

This step can be done as part of deployment but is separated for clarity.

It is recommended to use the Secret Manager integration for Cloud Run instead of using environment variables for the SQL configuration. The service injects the SQL credentials from Secret Manager at runtime via an environment variable.

Create secrets via the command line:

echo -n $INSTANCE_UNIX_SOCKET | \
    gcloud secrets create [INSTANCE_UNIX_SOCKET_SECRET] --data-file=-

Deploy the service to Cloud Run specifying the env var name and secret name:

gcloud beta run deploy SERVICE --image gcr.io/[YOUR_PROJECT_ID]/run-sql \
    --add-cloudsql-instances $INSTANCE_CONNECTION_NAME \
    --update-secrets INSTANCE_UNIX_SOCKET=[INSTANCE_UNIX_SOCKET_SECRET]:latest,\
      DB_USER=[DB_USER_SECRET]:latest, \
      DB_PASS=[DB_PASS_SECRET]:latest, \
      DB_NAME=[DB_NAME_SECRET]:latest

Navigate your browser to the URL noted in step 2.

For more details about using Cloud Run see http://cloud.run. Review other Node.js on Cloud Run samples.

Deploy to Cloud Functions

To deploy the service to Cloud Functions run the following command:

gcloud functions deploy votes --gen2 --runtime nodejs18 --trigger-http \
  --allow-unauthenticated \
  --entry-point votes \
  --region <INSTANCE_REGION> \
  --set-env-vars INSTANCE_UNIX_SOCKET=/cloudsql/<PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME> \
  --set-env-vars DB_USER=$DB_USER \
  --set-env-vars DB_PASS=$DB_PASS \
  --set-env-vars DB_NAME=$DB_NAME

Note: If the function fails to deploy or returns a 500: Internal service error, this may be due to a known limitation with Cloud Functions gen2 not being able to configure the underlying Cloud Run service with a Cloud SQL connection.

A workaround command to fix this is is to manually revise the Cloud Run service with the Cloud SQL Connection:

gcloud run deploy votes --source . \
  --region <INSTANCE_REGION> \
  --add-cloudsql-instances <PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME>

The Cloud Function command above can now be re-run with a successful deployment.

Name		Name	Last commit message	Last commit date
parent directory ..
certs		certs
server		server
test		test
views		views
Dockerfile		Dockerfile
README.md		README.md
app.flexible.yaml		app.flexible.yaml
app.standard.yaml		app.standard.yaml
connect-connector-with-iam-authn.js		connect-connector-with-iam-authn.js
connect-connector.js		connect-connector.js
connect-tcp.js		connect-tcp.js
connect-unix.js		connect-unix.js
deployment.yaml		deployment.yaml
functions.js		functions.js
index.js		index.js
package.json		package.json
service-account.yaml		service-account.yaml
service.yaml		service.yaml

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

knex

knex

README.md

Connecting to Cloud SQL - PostgreSQL

Before you begin

Cloud SQL Node.js Connector Usage

Running Locally

Linux / Mac OS

Windows/PowerShell

Testing the application

Deploy to Google App Engine Standard

Deploy to Google App Engine Flexible

Deploy to Cloud Run

Deploy to Cloud Functions

Cloud SQL Auth Proxy Usage

Running Locally

Launch proxy with TCP

Linux / Mac OS

Windows/PowerShell

Launch proxy with Unix Domain Socket

Testing the application

Deploy to Google App Engine Standard

Deploy to Google App Engine Flexible

Deploy to Cloud Run

Deploy to Cloud Functions

Files

knex

Directory actions

More options

Directory actions

More options

Latest commit

History

knex

Folders and files

parent directory

README.md

Connecting to Cloud SQL - PostgreSQL

Before you begin

Cloud SQL Node.js Connector Usage

Running Locally

Linux / Mac OS

Windows/PowerShell

Testing the application

Deploy to Google App Engine Standard

Deploy to Google App Engine Flexible

Deploy to Cloud Run

Deploy to Cloud Functions

Cloud SQL Auth Proxy Usage

Running Locally

Launch proxy with TCP

Linux / Mac OS

Windows/PowerShell

Launch proxy with Unix Domain Socket

Testing the application

Deploy to Google App Engine Standard

Deploy to Google App Engine Flexible

Deploy to Cloud Run

Deploy to Cloud Functions