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

1. The Cloud SQL Node.js Connector (recommended)
2. 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.

⚠️ Note that this sample currently does not support connection pooling, this issue is being tracked in the Cloud SQL Node.js Connector repository. Ideally the mssql example will be updated to support a modern version of tedious that has support to the Cloud SQL Node.js Connector but for now, keep in mind that this is a limited example.

1. If you haven't already, set up a Node.js Development Environment by following the Node.js setup guide and create a project.

2. Create a Google Cloud SQL "SQL Server" instance. Note the instance connection name of the instance that you create, and password that you specify for the default 'sqlserver' user.

3. Under the instance's "DATABASES" tab, create a new database.

   1. Click Create database.

   2. For Database name, enter votes.

   3. Click CREATE.

4. 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.

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)'

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>'

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>"

1. Next, install the Node.js packages necessary to run the app locally by running the following command:

   npm install

2. 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.

1. 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

2. To deploy to App Engine Standard, run the following command:

   gcloud app deploy app.standard.yaml

3. To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:

   gcloud app browse

1. 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

2. To deploy to App Engine Node.js Flexible Environment, run the following command:

   gcloud app deploy app.flexible.yaml

3. To launch your browser and view the app at https://[YOUR_PROJECT_ID].appspot.com, run the following command:

   gcloud app browse

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

1. 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.

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

You may optionally download and install the cloud-sql-proxy by following the instructions as an alternative to using the Cloud SQL Node.js Connector.

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

Use these terminal commands to initialize environment variables:

export INSTANCE_HOST='127.0.0.1'
export DB_PORT='1433'
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=1433 <PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME> &

Use these PowerShell commands to initialize environment variables:

$env:INSTANCE_HOST="127.0.0.1"
$env:DB_PORT="1433"
$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=1433"

1. Next, install the Node.js packages necessary to run the app locally by running the following command:

   npm install

2. 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.

To run on GAE-Standard, create an App Engine project by following the setup for these instructions.

First, update app.standard.yaml with the correct values to pass the environment variables into the runtime. e.g:

env_variables:
  INSTANCE_HOST: 127.0.0.1
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
  DB_PORT: 1433

Next, the following command will deploy the application to your Google Cloud project:

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

App Engine Flexible supports connecting to your SQL Server instance through TCP

First, update app.flexible.yaml with the correct values to pass the environment variables and instance name into the runtime. e.g:

env_variables:
  INSTANCE_HOST: 172.17.0.1
  DB_USER: MY_DB_USER
  DB_PASS: MY_DB_PASSWORD
  DB_NAME: MY_DATABASE
  DB_PORT: 1433

Then, make sure that the service account service-{PROJECT_NUMBER}>@gae-api-prod.google.com.iam.gserviceaccount.com has the IAM role Cloud SQL Client.

The following command will deploy the application to your Google Cloud project:

gcloud app deploy app.flexible.yaml

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_HOST='127.0.0.1'> \
  --set-env-vars DB_PORT='1433' \
  --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.