This page contains information and examples for connecting to a
Cloud SQL instance from a service running in Cloud Build.


Cloud SQL is a fully-managed database service that helps you set up,
maintain, manage, and administer your relational databases in the cloud.

<br />

[Cloud Build](https://docs.cloud.google.com/build/docs/overview.md.txt) is a service that
executes your builds on Google Cloud infrastructure.

<br />

## Set up a Cloud SQL instance

1. Enable the Cloud SQL Admin API in the Google Cloud project that you are connecting from, if you haven't already done so: <br />

   **Roles required to enable APIs**


   To enable APIs, you need the `serviceusage.services.enable` permission. If you
   created the project, then you likely already have this permission through the
   Owner role (`roles/owner`). Otherwise, you can get this permission through the
   Service Usage Admin role (`roles/serviceusage.serviceUsageAdmin`).
   [Learn how to grant roles](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt).

   [Enable the API](https://console.cloud.google.com/apis/enableflow?apiid=sqladmin&redirect=https://console.cloud.google.com)
2. [Create a Cloud SQL
   for SQL Server instance](https://docs.cloud.google.com/sql/docs/sqlserver/create-instance). We recommend that you choose a Cloud SQL [instance location](https://docs.cloud.google.com/sql/docs/sqlserver/locations.md.txt) in the same region as your Cloud Build service for better latency, to avoid some networking costs, and to reduce cross region failure risks.

   By default, Cloud SQL assigns a public IP address to a new
   instance.

   You also have the option to assign a private IP address. For more
   information about the connectivity options for both, see the
   [Connecting Overview](https://docs.cloud.google.com/sql/docs/sqlserver/connect-overview)
   page.
3. When you create the instance, you can choose the [server certificate (CA) hierarchy](https://docs.cloud.google.com/sql/docs/mysql/authorize-ssl.md.txt#ca-hierarchies) for the instance and then configure the hierarchy as the `serverCaMode` for the instance. You must select the per-instance CA option (`GOOGLE_MANAGED_INTERNAL_CA`) as the server CA mode for instances that you want to connect to from web applications.

## Set up an Artifact Registry Repository

1. If you haven't already done so, then enable the Artifact Registry API in the Google Cloud project that you are connecting from: <br />

   **Roles required to enable APIs**


   To enable APIs, you need the `serviceusage.services.enable` permission. If you
   created the project, then you likely already have this permission through the
   Owner role (`roles/owner`). Otherwise, you can get this permission through the
   Service Usage Admin role (`roles/serviceusage.serviceUsageAdmin`).
   [Learn how to grant roles](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt).

   [Enable the API](https://console.cloud.google.com/apis/enableflow?apiid=artifactregistry&redirect=https://console.cloud.google.com)
2. [Create a Docker Artifact Registry](https://docs.cloud.google.com/artifact-registry/docs/repositories/create-repos.md.txt). To improve latency, reduce the risk of cross-region failure, and avoid additional networking costs, we recommend that you choose an Artifact Registry [location](https://docs.cloud.google.com/artifact-registry/docs/repositories/repo-locations.md.txt) in the same region as your Cloud Run service.

## Configure Cloud Build

The steps to configure Cloud Build depend on the type of IP address that you assigned to your Cloud SQL instance.

### Public IP (default)


Make sure your [Cloud Build service account](https://docs.cloud.google.com/build/docs/cloud-build-service-account.md.txt) has the
[IAM
roles and permissions required to connect to the Cloud SQL instance](https://docs.cloud.google.com/sql/docs/mysql/project-access-control#roles).
The Cloud Build service account is listed on the Google Cloud console
[IAM](https://console.cloud.google.com/iam-admin)
page as the **Principal**
`[YOUR-PROJECT-NUMBER]@cloudbuild.gserviceaccount.com`.

To view this service account in the Google Cloud console,
select the **Include Google-provided role grants** checkbox.

Your Cloud Build service account needs the
`Cloud SQL Client` [IAM role](https://docs.cloud.google.com/iam/docs/roles-permissions/cloudsql.md.txt).

If the Cloud Build service account belongs to a different project
than the Cloud SQL instance, then the Cloud SQL Admin API and the role need to be added for both projects.

<br />

### Private IP


To connect to your Cloud SQL instance over private IP, Cloud Build
must be in the same VPC network as your Cloud SQL instance. To configure this:

1. Set up a private connection between the [VPC network of your Cloud SQL instance and the service producer network](https://docs.cloud.google.com/build/docs/private-pools/set-up-private-pool-environment#setup-private-connection).
2. Create a [Cloud Build private pool](https://docs.cloud.google.com/build/docs/private-pools/create-manage-private-pools.md.txt#creating_a_new_private_pool).

Once configured, your application will be able to connect directly using your
instance's private IP address and port `1433` when your build is run in the pool.

<br />

## Connect to Cloud SQL

After you configure Cloud Build, you can connect to your
Cloud SQL instance.

### Public IP (default)

<br />

<br />


For public IP paths, Cloud Build supports TCP sockets.


### Connect with TCP

### Python

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/python-docs-samples/tree/master/cloud-sql/sql-server/sqlalchemy/README.md).


```python
import os

import sqlalchemy


def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    """Initializes a TCP connection pool for a Cloud SQL instance of SQL Server."""
    # Note: Saving credentials in environment variables is convenient, but not
    # secure - consider a more secure solution such as
    # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
    # keep secrets safe.
    db_host = os.environ[
        "INSTANCE_HOST"
    ]  # e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
    db_user = os.environ["DB_USER"]  # e.g. 'my-db-user'
    db_pass = os.environ["DB_PASS"]  # e.g. 'my-db-password'
    db_name = os.environ["DB_NAME"]  # e.g. 'my-database'
    db_port = os.environ["DB_PORT"]  # e.g. 1433

    pool = sqlalchemy.create_engine(
        # Equivalent URL:
        # mssql+pytds://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
        sqlalchemy.engine.url.URL.create(
            drivername="mssql+pytds",
            username=db_user,
            password=db_pass,
            database=db_name,
            host=db_host,
            port=db_port,
        ),
        # ...
    )

    return pool

```

### Java

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/java-docs-samples/blob/master/cloud-sql/sqlserver/servlet/README.md).


Note:

- CLOUD_SQL_CONNECTION_NAME should be represented as \<MY-PROJECT\>:\<INSTANCE-REGION\>:\<INSTANCE-NAME\>
- Using the argument ipTypes=PRIVATE will force the SocketFactory to connect with an instance's associated private IP
- See the [JDBC socket factory version requirements for the pom.xml
  file](https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/main/docs/jdbc.md).


```java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class TcpConnectionPoolFactory extends ConnectionPoolFactory {

  // Note: Saving credentials in environment variables is convenient, but not
  // secure - consider a more secure solution such as
  // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  // keep secrets safe.
  private static final String DB_USER = System.getenv("DB_USER");
  private static final String DB_PASS = System.getenv("DB_PASS");
  private static final String DB_NAME = System.getenv("DB_NAME");

  private static final String INSTANCE_HOST = System.getenv("INSTANCE_HOST");
  private static final String DB_PORT = System.getenv("DB_PORT");


  public static DataSource createConnectionPool() {
    // The configuration object specifies behaviors for the connection pool.
    HikariConfig config = new HikariConfig();

    // Configure which instance and what database user to connect with.
    config.setJdbcUrl(
        String.format("jdbc:sqlserver://%s:%s;databaseName=%s", INSTANCE_HOST, DB_PORT, DB_NAME));
    config.setUsername(DB_USER); // e.g. "root", "sqlserver"
    config.setPassword(DB_PASS); // e.g. "my-password"


    // ... Specify additional connection properties here.
    // ...

    // Initialize the connection pool using the configuration object.
    return new HikariDataSource(config);
  }
}
```

### Node.js

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/cloud-sql/sqlserver/mssql/README.md).


```javascript
const mssql = require('mssql');

// createTcpPool initializes a TCP connection pool for a Cloud SQL
// instance of SQL Server.
const createTcpPool = async config => {
  // Note: Saving credentials in environment variables is convenient, but not
  // secure - consider a more secure solution such as
  // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  // keep secrets safe.
  const dbConfig = {
    server: process.env.INSTANCE_HOST, // e.g. '127.0.0.1'
    port: parseInt(process.env.DB_PORT), // e.g. 1433
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    options: {
      trustServerCertificate: true,
    },
    // ... Specify additional properties here.
    ...config,
  };
  // Establish a connection to the database.
  return mssql.connect(dbConfig);
};
```

### Go

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/golang-samples/tree/master/cloudsql/sqlserver/database-sql/README.md).


```go
package cloudsql

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"strings"

	_ "github.com/denisenkom/go-mssqldb"
)

// connectTCPSocket initializes a TCP connection pool for a Cloud SQL
// instance of SQL Server.
func connectTCPSocket() (*sql.DB, error) {
	mustGetenv := func(k string) string {
		v := os.Getenv(k)
		if v == "" {
			log.Fatalf("Fatal Error in connect_tcp.go: %s environment variable not set.\n", k)
		}
		return v
	}
	// Note: Saving credentials in environment variables is convenient, but not
	// secure - consider a more secure solution such as
	// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
	// keep secrets safe.
	var (
		dbUser    = mustGetenv("DB_USER")       // e.g. 'my-db-user'
		dbPwd     = mustGetenv("DB_PASS")       // e.g. 'my-db-password'
		dbTCPHost = mustGetenv("INSTANCE_HOST") // e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
		dbPort    = mustGetenv("DB_PORT")       // e.g. '1433'
		dbName    = mustGetenv("DB_NAME")       // e.g. 'my-database'
	)

	dbURI := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%s;database=%s;",
		dbTCPHost, dbUser, dbPwd, dbPort, dbName)


	// dbPool is the pool of database connections.
	dbPool, err := sql.Open("sqlserver", dbURI)
	if err != nil {
		return nil, fmt.Errorf("sql.Open: %w", err)
	}

	// ...

	return dbPool, nil
}
```

### C#

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/dotnet-docs-samples/blob/master/cloud-sql/sql-server/README.md).


```c#
using Microsoft.Data.SqlClient;
using System;

namespace CloudSql
{
    public class SqlServerTcp
    {
        public static SqlConnectionStringBuilder NewSqlServerTCPConnectionString()
        {
            // Equivalent connection string:
            // "User Id=<DB_USER>;Password=<DB_PASS>;Server=<INSTANCE_HOST>;Database=<DB_NAME>;"
            var connectionString = new SqlConnectionStringBuilder()
            {
                // Note: Saving credentials in environment variables is convenient, but not
                // secure - consider a more secure solution such as
                // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
                // keep secrets safe.
                DataSource = Environment.GetEnvironmentVariable("INSTANCE_HOST"), // e.g. '127.0.0.1'
                // Set Host to 'cloudsql' when deploying to App Engine Flexible environment
                UserID = Environment.GetEnvironmentVariable("DB_USER"),         // e.g. 'my-db-user'
                Password = Environment.GetEnvironmentVariable("DB_PASS"),       // e.g. 'my-db-password'
                InitialCatalog = Environment.GetEnvironmentVariable("DB_NAME"), // e.g. 'my-database'

                // The Cloud SQL proxy provides encryption between the proxy and instance
                Encrypt = false,
            };
            connectionString.Pooling = true;
            // Specify additional properties here.
            return connectionString;
        }
    }
}
```

### Ruby

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/ruby-docs-samples/tree/master/cloud-sql/sqlserver/activerecord/README.md).


```ruby
tcp: &tcp
  adapter: sqlserver
  # Configure additional properties here.
  # Note: Saving credentials in environment variables is convenient, but not
  # secure - consider a more secure solution such as
  # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  # keep secrets safe.
  username: <%= ENV["DB_USER"] %>  # e.g. "my-database-user"
  password: <%= ENV["DB_PASS"] %> # e.g. "my-database-password"
  database: <%= ENV.fetch("DB_NAME") { "vote_development" } %>
  host: <%= ENV.fetch("INSTANCE_HOST") { "127.0.0.1" }%> # '172.17.0.1' if deployed to GAE Flex
  port: <%= ENV.fetch("DB_PORT") { 1433 }%> 
```

### PHP

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/php-docs-samples/tree/main/cloud_sql/sqlserver/pdo/README.md).


```php
namespace Google\Cloud\Samples\CloudSQL\SQLServer;

use PDO;
use PDOException;
use RuntimeException;
use TypeError;

class DatabaseTcp
{
    public static function initTcpDatabaseConnection(): PDO
    {
        try {
            // Note: Saving credentials in environment variables is convenient, but not
            // secure - consider a more secure solution such as
            // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
            // keep secrets safe.
            $username = getenv('DB_USER'); // e.g. 'your_db_user'
            $password = getenv('DB_PASS'); // e.g. 'your_db_password'
            $dbName = getenv('DB_NAME'); // e.g. 'your_db_name'
            $instanceHost = getenv('INSTANCE_HOST'); // e.g. '127.0.0.1' ('172.17.0.1' for GAE Flex)

            // Connect using TCP
            $dsn = sprintf(
                'sqlsrv:server=%s;Database=%s',
                $instanceHost,
                $dbName
            );

            // Connect to the database
            $conn = new PDO(
                $dsn,
                $username,
                $password,
                # ...
            );
        } catch (TypeError $e) {
            throw new RuntimeException(
                sprintf(
                    'Invalid or missing configuration! Make sure you have set ' .
                        '$username, $password, $dbName, and $instanceHost (for TCP mode). ' .
                        'The PHP error was %s',
                    $e->getMessage()
                ),
                $e->getCode(),
                $e
            );
        } catch (PDOException $e) {
            throw new RuntimeException(
                sprintf(
                    'Could not connect to the Cloud SQL Database. Check that ' .
                        'your username and password are correct, that the Cloud SQL ' .
                        'proxy is running, and that the database exists and is ready ' .
                        'for use. For more assistance, refer to %s. The PDO error was %s',
                    'https://cloud.google.com/sql/docs/sqlserver/connect-external-app',
                    $e->getMessage()
                ),
                (int) $e->getCode(),
                $e
            );
        }

        return $conn;
    }
}
```


You can use the Cloud SQL Auth Proxy in a Cloud Build step to allow connections to your database.

This configuration:

1. Builds your container and pushes it to Artifact Registry.
2. Builds a second container, copying in the Cloud SQL Auth Proxy binary.
   - Containers built by Cloud Build don't need to be pushed to any registry and are [discarded on build completion](https://docs.cloud.google.com/build/docs/build-config-file-schema.md.txt#images).
3. Using the second container, starts the Cloud SQL Auth Proxy and runs any migration commands.

```bash
steps:
  - id: install-proxy
    name: gcr.io/cloud-builders/wget
    entrypoint: sh
    args:
      - -c
      - |
        wget -O /workspace/cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/2.28.0
        chmod +x /workspace/cloud-sql-proxy

 - id: migrate
    waitFor: ['install-proxy']
    name: YOUR_CONTAINER_IMAGE_NAME
    entrypoint: sh
    env:
      - "DATABASE_NAME=${_DATABASE_NAME}"
      - "DATABASE_USER=${_DATABASE_USER}"
      - "DATABASE_PORT=${_DATABASE_PORT}"
      - "INSTANCE_CONNECTION_NAME=${_INSTANCE_CONNECTION_NAME}"
    secretEnv:
      - DATABASE_PASS
    args:
      - "-c"
      - |
        /workspace/cloud-sql-proxy ${_INSTANCE_CONNECTION_NAME} --port ${_DATABASE_PORT} & sleep 2;
        # Cloud SQL Proxy is now up and running, add your own logic below to connect
        python migrate.py # For example

  options:
    dynamic_substitutions: true

  substitutions:
    _DATABASE_USER: myuser
    _DATABASE_NAME: mydatabase
    _INSTANCE_CONNECTION_NAME: ${PROJECT_ID}:us-central1:myinstance
    _DATABASE_PORT: 1433
    _DATABASE_PASSWORD_KEY: database_password
    _AR_REPO_REGION: us-central1
    _AR_REPO_NAME: my-docker-repo
    _IMAGE_NAME: ${_AR_REPO_REGION}-docker.pkg.dev/${PROJECT_ID}/${_AR_REPO_NAME}/sample-sql-proxy

  availableSecrets:
    secretManager:
      - versionName: projects/$PROJECT_ID/secrets/${_DATABASE_PASSWORD_KEY}/versions/latest
        env: "DATABASE_PASS"
```

The Cloud Build code sample shows how you might run a hypothetical
`migrate.py` script after deploying the previous sample app to update
its Cloud SQL database using the Cloud SQL Auth Proxy and Cloud Build.
To run this Cloud Build code sample the setup steps required are:

1. Create a folder name `sql-proxy`
2. Create a `Dockerfile` file in the `sql-proxy` folder with the following single line of code for its file contents:

   ```bash
       FROM gcr.io/gcp-runtimes/ubuntu_20_0_4
       
   ```
3. Create a `cloudbuild.yaml` file in the `sql-proxy` folder.
4. Update the `cloudbuild.yaml` file:
   1. Copy the previous sample Cloud Build code and paste it into the `cloudbuild.yaml` file.
   2. Update the example code `_DATABASE_PORT` within the `substitutions:` block to be `1433`, which is the port used by SQL Server.
   3. Replace the following placeholder values with the values used in your project:
      - `mydatabase`
      - `myuser`
      - `myinstance`
5. Create a secret named `database_password` in [Secret Manager](https://console.cloud.google.com/security/secret-manager/).
   - In order for the Cloud Build service account to access this secret, you have to grant it the [Secret Manager Secret Accessor](https://docs.cloud.google.com/iam/docs/roles-permissions/secretmanager.md.txt) role in [IAM](https://console.cloud.google.com/iam-admin/iam). See [Using secrets from Secret Manager](https://docs.cloud.google.com/build/docs/securing-builds/use-secrets.md.txt) for more information.
6. Create a **migrate.py** script file in the `sql-proxy` folder.
   - The script can reference the following environment variables and the secret created in the `cloudbuild.yaml` file using the following examples:
     - `os.getenv('DATABASE_NAME')`
     - `os.getenv('DATABASE_USER')`
     - `os.getenv('DATABASE_PASS')`
     - `os.getenv('INSTANCE_CONNECTION_NAME')`
   - To reference the same variables from a Bash script (for example: `migrate.sh`) use the following examples:
     - `$DATABASE_NAME`
     - `$DATABASE_USER`
     - `$DATABASE_PASS`
     - `$INSTANCE_CONNECTION_NAME`
7. Run the following [`gcloud builds submit`](https://docs.cloud.google.com/sdk/gcloud/reference/builds/submit.md.txt) command to build a container with the Cloud SQL Auth Proxy, start the Cloud SQL Auth Proxy, and run the `migrate.py` script:

   ```bash
       gcloud builds submit --config cloudbuild.yaml
       
   ```

<br />

### Private IP


For private IP paths, your application connects directly to your
instance through private pools.
This method uses TCP to connect directly to the Cloud SQL
instance without using the Cloud SQL Auth Proxy.

### Connect with TCP

Connect using the private IP address of your Cloud SQL instance as the host and port `1433`.

### Python

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/python-docs-samples/tree/master/cloud-sql/sql-server/sqlalchemy/README.md).


```python
import os

import sqlalchemy


def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    """Initializes a TCP connection pool for a Cloud SQL instance of SQL Server."""
    # Note: Saving credentials in environment variables is convenient, but not
    # secure - consider a more secure solution such as
    # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
    # keep secrets safe.
    db_host = os.environ[
        "INSTANCE_HOST"
    ]  # e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
    db_user = os.environ["DB_USER"]  # e.g. 'my-db-user'
    db_pass = os.environ["DB_PASS"]  # e.g. 'my-db-password'
    db_name = os.environ["DB_NAME"]  # e.g. 'my-database'
    db_port = os.environ["DB_PORT"]  # e.g. 1433

    pool = sqlalchemy.create_engine(
        # Equivalent URL:
        # mssql+pytds://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
        sqlalchemy.engine.url.URL.create(
            drivername="mssql+pytds",
            username=db_user,
            password=db_pass,
            database=db_name,
            host=db_host,
            port=db_port,
        ),
        # ...
    )

    return pool

```

### Java

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/java-docs-samples/blob/master/cloud-sql/sqlserver/servlet/README.md).


Note:

- CLOUD_SQL_CONNECTION_NAME should be represented as \<MY-PROJECT\>:\<INSTANCE-REGION\>:\<INSTANCE-NAME\>
- Using the argument ipTypes=PRIVATE will force the SocketFactory to connect with an instance's associated private IP
- See the [JDBC socket factory version requirements for the pom.xml
  file](https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/main/docs/jdbc.md).


```java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class TcpConnectionPoolFactory extends ConnectionPoolFactory {

  // Note: Saving credentials in environment variables is convenient, but not
  // secure - consider a more secure solution such as
  // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  // keep secrets safe.
  private static final String DB_USER = System.getenv("DB_USER");
  private static final String DB_PASS = System.getenv("DB_PASS");
  private static final String DB_NAME = System.getenv("DB_NAME");

  private static final String INSTANCE_HOST = System.getenv("INSTANCE_HOST");
  private static final String DB_PORT = System.getenv("DB_PORT");


  public static DataSource createConnectionPool() {
    // The configuration object specifies behaviors for the connection pool.
    HikariConfig config = new HikariConfig();

    // Configure which instance and what database user to connect with.
    config.setJdbcUrl(
        String.format("jdbc:sqlserver://%s:%s;databaseName=%s", INSTANCE_HOST, DB_PORT, DB_NAME));
    config.setUsername(DB_USER); // e.g. "root", "sqlserver"
    config.setPassword(DB_PASS); // e.g. "my-password"


    // ... Specify additional connection properties here.
    // ...

    // Initialize the connection pool using the configuration object.
    return new HikariDataSource(config);
  }
}
```

### Node.js

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/cloud-sql/sqlserver/mssql/README.md).


```javascript
const mssql = require('mssql');

// createTcpPool initializes a TCP connection pool for a Cloud SQL
// instance of SQL Server.
const createTcpPool = async config => {
  // Note: Saving credentials in environment variables is convenient, but not
  // secure - consider a more secure solution such as
  // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  // keep secrets safe.
  const dbConfig = {
    server: process.env.INSTANCE_HOST, // e.g. '127.0.0.1'
    port: parseInt(process.env.DB_PORT), // e.g. 1433
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    options: {
      trustServerCertificate: true,
    },
    // ... Specify additional properties here.
    ...config,
  };
  // Establish a connection to the database.
  return mssql.connect(dbConfig);
};
```

### Go

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/golang-samples/tree/master/cloudsql/sqlserver/database-sql/README.md).


```go
package cloudsql

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"strings"

	_ "github.com/denisenkom/go-mssqldb"
)

// connectTCPSocket initializes a TCP connection pool for a Cloud SQL
// instance of SQL Server.
func connectTCPSocket() (*sql.DB, error) {
	mustGetenv := func(k string) string {
		v := os.Getenv(k)
		if v == "" {
			log.Fatalf("Fatal Error in connect_tcp.go: %s environment variable not set.\n", k)
		}
		return v
	}
	// Note: Saving credentials in environment variables is convenient, but not
	// secure - consider a more secure solution such as
	// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
	// keep secrets safe.
	var (
		dbUser    = mustGetenv("DB_USER")       // e.g. 'my-db-user'
		dbPwd     = mustGetenv("DB_PASS")       // e.g. 'my-db-password'
		dbTCPHost = mustGetenv("INSTANCE_HOST") // e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
		dbPort    = mustGetenv("DB_PORT")       // e.g. '1433'
		dbName    = mustGetenv("DB_NAME")       // e.g. 'my-database'
	)

	dbURI := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%s;database=%s;",
		dbTCPHost, dbUser, dbPwd, dbPort, dbName)


	// dbPool is the pool of database connections.
	dbPool, err := sql.Open("sqlserver", dbURI)
	if err != nil {
		return nil, fmt.Errorf("sql.Open: %w", err)
	}

	// ...

	return dbPool, nil
}
```

### C#

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/dotnet-docs-samples/blob/master/cloud-sql/sql-server/README.md).


```c#
using Microsoft.Data.SqlClient;
using System;

namespace CloudSql
{
    public class SqlServerTcp
    {
        public static SqlConnectionStringBuilder NewSqlServerTCPConnectionString()
        {
            // Equivalent connection string:
            // "User Id=<DB_USER>;Password=<DB_PASS>;Server=<INSTANCE_HOST>;Database=<DB_NAME>;"
            var connectionString = new SqlConnectionStringBuilder()
            {
                // Note: Saving credentials in environment variables is convenient, but not
                // secure - consider a more secure solution such as
                // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
                // keep secrets safe.
                DataSource = Environment.GetEnvironmentVariable("INSTANCE_HOST"), // e.g. '127.0.0.1'
                // Set Host to 'cloudsql' when deploying to App Engine Flexible environment
                UserID = Environment.GetEnvironmentVariable("DB_USER"),         // e.g. 'my-db-user'
                Password = Environment.GetEnvironmentVariable("DB_PASS"),       // e.g. 'my-db-password'
                InitialCatalog = Environment.GetEnvironmentVariable("DB_NAME"), // e.g. 'my-database'

                // The Cloud SQL proxy provides encryption between the proxy and instance
                Encrypt = false,
            };
            connectionString.Pooling = true;
            // Specify additional properties here.
            return connectionString;
        }
    }
}
```

### Ruby

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/ruby-docs-samples/tree/master/cloud-sql/sqlserver/activerecord/README.md).


```ruby
tcp: &tcp
  adapter: sqlserver
  # Configure additional properties here.
  # Note: Saving credentials in environment variables is convenient, but not
  # secure - consider a more secure solution such as
  # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
  # keep secrets safe.
  username: <%= ENV["DB_USER"] %>  # e.g. "my-database-user"
  password: <%= ENV["DB_PASS"] %> # e.g. "my-database-password"
  database: <%= ENV.fetch("DB_NAME") { "vote_development" } %>
  host: <%= ENV.fetch("INSTANCE_HOST") { "127.0.0.1" }%> # '172.17.0.1' if deployed to GAE Flex
  port: <%= ENV.fetch("DB_PORT") { 1433 }%> 
```

### PHP

To see this snippet in the context of a web application, view
[the README on GitHub](https://github.com/GoogleCloudPlatform/php-docs-samples/tree/main/cloud_sql/sqlserver/pdo/README.md).


```php
namespace Google\Cloud\Samples\CloudSQL\SQLServer;

use PDO;
use PDOException;
use RuntimeException;
use TypeError;

class DatabaseTcp
{
    public static function initTcpDatabaseConnection(): PDO
    {
        try {
            // Note: Saving credentials in environment variables is convenient, but not
            // secure - consider a more secure solution such as
            // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
            // keep secrets safe.
            $username = getenv('DB_USER'); // e.g. 'your_db_user'
            $password = getenv('DB_PASS'); // e.g. 'your_db_password'
            $dbName = getenv('DB_NAME'); // e.g. 'your_db_name'
            $instanceHost = getenv('INSTANCE_HOST'); // e.g. '127.0.0.1' ('172.17.0.1' for GAE Flex)

            // Connect using TCP
            $dsn = sprintf(
                'sqlsrv:server=%s;Database=%s',
                $instanceHost,
                $dbName
            );

            // Connect to the database
            $conn = new PDO(
                $dsn,
                $username,
                $password,
                # ...
            );
        } catch (TypeError $e) {
            throw new RuntimeException(
                sprintf(
                    'Invalid or missing configuration! Make sure you have set ' .
                        '$username, $password, $dbName, and $instanceHost (for TCP mode). ' .
                        'The PHP error was %s',
                    $e->getMessage()
                ),
                $e->getCode(),
                $e
            );
        } catch (PDOException $e) {
            throw new RuntimeException(
                sprintf(
                    'Could not connect to the Cloud SQL Database. Check that ' .
                        'your username and password are correct, that the Cloud SQL ' .
                        'proxy is running, and that the database exists and is ready ' .
                        'for use. For more assistance, refer to %s. The PDO error was %s',
                    'https://cloud.google.com/sql/docs/sqlserver/connect-external-app',
                    $e->getMessage()
                ),
                (int) $e->getCode(),
                $e
            );
        }

        return $conn;
    }
}
```


You can then create a Cloud Build step to run your code directly.


```yaml
steps:
  - id: "docker-build"
    name: "gcr.io/cloud-builders/docker"
    args: ["build", "-t", "${_IMAGE_NAME}", "sql-private-pool/."]

  - id: "docker-push"
    name: "gcr.io/cloud-builders/docker"
    args: ["push", "${_IMAGE_NAME}"]

  - id: "migration"
    name: "${_IMAGE_NAME}"
    dir: sql-private-pool
    env:
      - "DATABASE_NAME=mydatabase"
      - "DATABASE_USER=myuser"
      - "DATABASE_HOST=${_DATABASE_HOST}"
      - "DATABASE_TYPE=${_DATABASE_TYPE}"
    secretEnv:
      - DATABASE_PASS
    entrypoint: python   # for example
    args: ["migrate.py"] # for example

options:
  pool:
    name: projects/$PROJECT_ID/locations/us-central1/workerPools/private-pool
  dynamicSubstitutions: true

substitutions:
  _DATABASE_PASSWORD_KEY: database_password
  _DATABASE_TYPE: postgres
  _AR_REPO_REGION: us-central1
  _AR_REPO_NAME: my-docker-repo
  _IMAGE_NAME: ${_AR_REPO_REGION}-docker.pkg.dev/${PROJECT_ID}/${_AR_REPO_NAME}/sample-private-pool

availableSecrets:
  secretManager:
    - versionName: projects/$PROJECT_ID/secrets/${_DATABASE_PASSWORD_KEY}/versions/latest
      env: DATABASE_PASS
```

The Cloud Build code sample above shows how you might run a hypothetical
**migrate** script after deploying the sample app above to update
its Cloud SQL database using Cloud Build.
To run this Cloud Build code sample the setup steps required are:

1. Create a folder name `sql-private-pool`
2. Create a `Dockerfile` file in the `sql-private-pool` folder with the following single line of code for its file contents:


       FROM gcr.io/gcp-runtimes/ubuntu_20_0_4

   <br />

3. Create a `cloudbuild.yaml` file in the `sql-private-pool` folder.
4. Update the `cloudbuild.yaml` file:
   1. Copy the sample Cloud Build code above and paste it into the `cloudbuild.yaml` file.
   2. Replace the following placeholder values with the values used in your project:
      - `mydatabase`
      - `myuser`
      - `databasehost`, in the form `host:port`.
5. Create a secret named `database_password` in [Secret Manager](https://console.cloud.google.com/security/secret-manager/).
   - In order for the Cloud Build service account to access this secret, you will have to grant it the [Secret Manager Secret Accessor](https://docs.cloud.google.com/iam/docs/roles-permissions/secretmanager.md.txt) role in [IAM](https://console.cloud.google.com/iam-admin/iam). See [Using secrets from Secret Manager](https://docs.cloud.google.com/build/docs/securing-builds/use-secrets.md.txt) for more information.
6. Create a **migrate.py** script file in the `sql-proxy` folder.
   - The script can reference the following environment variables and the secret created in the `cloudbuild.yaml` file using the following examples:
     - `os.getenv('DATABASE_NAME')`
     - `os.getenv('DATABASE_USER')`
     - `os.getenv('DATABASE_PASS')`
     - `os.getenv('DATABASE_HOST')`
   - To reference the same variables from a Bash script (for example: `migrate.sh`) use the following examples:
     - `$DATABASE_NAME`
     - `$DATABASE_USER`
     - `$DATABASE_PASS`
     - `$DATABASE_HOST`
7. Run the following [`gcloud builds submit`](https://docs.cloud.google.com/sdk/gcloud/reference/builds/submit.md.txt) command to build a container with the Cloud SQL Auth Proxy, start the Cloud SQL Auth Proxy, and run the `migrate.py` script:


       gcloud builds submit --config cloudbuild.yaml

   <br />

<br />

## Best practices and other information

You can use the [Cloud SQL Auth Proxy](https://docs.cloud.google.com/sql/docs/sqlserver/sql-proxy.md.txt) when testing
your application locally. See the
[quickstart for using the Cloud SQL Auth Proxy](https://docs.cloud.google.com/sql/docs/mysql/connect-instance-auth-proxy.md.txt)
for detailed instructions.

You can also test using the
[Cloud SQL Proxy via a docker container](https://docs.cloud.google.com/sql/docs/sqlserver/connect-docker.md.txt).

### Database schema migrations

By configuring Cloud Build to connect to Cloud SQL, you can run database schema
migration tasks in Cloud Build using the same code you would deploy to any other serverless
platform.

### Using Secret Manager

You can use
[Secret Manager](https://docs.cloud.google.com/build/docs/securing-builds/use-secrets.md.txt)
to include sensitive information in your builds.