Connect from Cloud Build

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.

Set up a Cloud SQL instance

  1. Enable the Cloud SQL Admin API in the project you are connecting from, if you haven't already done so:

    Enable the API

  2. Create a Cloud SQL for MySQL instance.

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

Configure Cloud Build

The steps to configure Cloud Build depend on the type of IP address you assigned to your Cloud SQL instance. If you route all egress traffic through the VPC connector, you must use a Private IP.

Public IP (default)

  • Make sure your Cloud Build service account has the IAM roles and permissions required to connect to the Cloud SQL instance. The Cloud Build service account is listed on the Google Cloud console IAM page as the Principal [YOUR-PROJECT-NUMBER]@cloudbuild.gserviceaccount.com.
      Your Cloud Build service account needs one of the following IAM roles:
      • Cloud SQL Client (preferred)
      • Cloud SQL Admin
      Or, you can manually assign the following IAM permissions:
      • cloudsql.instances.connect
      • cloudsql.instances.get

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

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.
  2. Create a Cloud Build private pool.

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

Connect to Cloud SQL

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

Public IP (default)

For public IP paths, Cloud Build supports both Unix and TCP sockets.

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 Container Registry.
  2. Builds a second container, copying in the Cloud SQL Auth proxy binary.
  3. Using the second container, starts the Cloud SQL Auth proxy and runs any migration commands.
steps:
  - id: "docker-build"
    name: "gcr.io/cloud-builders/docker"
    args: ["build", "-t", "${_IMAGE_NAME}", "sql-proxy/."]

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

  - id: "docker-layer"
    name: "gcr.io/cloud-builders/docker"
    entrypoint: /bin/bash
    args:
      - '-c'
      - |
        echo "FROM $_IMAGE_NAME
        COPY --from=gcr.io/cloudsql-docker/gce-proxy /cloud_sql_proxy /cloudsql/cloud_sql_proxy" > Dockerfile-proxy;

        docker build -f Dockerfile-proxy -t ${_IMAGE_NAME}-proxy .

  # For TCP connections
  - id: "migrate-tcp"
    name: "${_IMAGE_NAME}-proxy"
    dir: sql-proxy
    env:
      - "DATABASE_NAME=${_DATABASE_NAME}"
      - "DATABASE_USER=${_DATABASE_USER}"
      - "DATABASE_HOST=127.0.0.1"
      - "DATABASE_PORT=${_DATABASE_PORT}"
      - "DATABASE_TYPE=${_DATABASE_TYPE}"
    secretEnv:
      - DATABASE_PASS
    entrypoint: /bin/bash
    args:
      - '-c'
      - |
        /cloudsql/cloud_sql_proxy -instances=${_INSTANCE_CONNECTION_NAME}=tcp:${_DATABASE_PORT} & sleep 2;
        python migrate.py # for example

  # For Unix Socket connections
  - id: "migrate-socket"
    name: "${_IMAGE_NAME}-proxy"
    dir: sql-proxy
    env:
      - "DATABASE_NAME=${_DATABASE_NAME}"
      - "DATABASE_USER=${_DATABASE_USER}"
      - "INSTANCE_CONNECTION_NAME=${_INSTANCE_CONNECTION_NAME}"
      - "DATABASE_TYPE=${_DATABASE_TYPE}"
    secretEnv:
      - DATABASE_PASS
    entrypoint: /bin/bash
    args:
      - '-c'
      - |
        /cloudsql/cloud_sql_proxy -instances=${_INSTANCE_CONNECTION_NAME} -dir=/cloudsql & sleep 2;
        if [ $_DATABASE_TYPE = 'mssql' ]; then echo "MSSQL doesn't support Unix Sockets. Skippng."; exit 0; fi;
        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: '5432'
  _DATABASE_TYPE: postgres
  _DATABASE_PASSWORD_KEY: database_password
  _IMAGE_NAME: gcr.io/${PROJECT_ID}/sample-sql-proxy

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

    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 sample Cloud Build code above and paste it into the cloudbuild.yaml file.
    2. Use either a TCP connection or a Unix socket connection by removing the code block for the connection method not being used.
    3. Update the example code _DATABASE_TYPE within the substitutions: block to be mysql.
    4. If you're using a TCP connection, update the example code _DATABASE_PORT within the substitutions: block to be 3306, which is the port used by MySQL.
    5. 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.
  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 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

Private IP

For private IP paths, your application will connect 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 directly using the private IP address and port 3306 for your instance.

Python

To see this snippet in the context of a web application, view the README on GitHub.

# Remember - storing secrets in plaintext is potentially unsafe. Consider using
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
# secrets secret.
db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_host = os.environ["DB_HOST"]

# Extract port from db_host if present,
# otherwise use DB_PORT environment variable.
host_args = db_host.split(":")
if len(host_args) == 1:
    db_hostname = db_host
    db_port = os.environ["DB_PORT"]
elif len(host_args) == 2:
    db_hostname, db_port = host_args[0], int(host_args[1])

pool = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="mysql+pymysql",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        host=db_hostname,  # e.g. "127.0.0.1"
        port=db_port,  # e.g. 3306
        database=db_name,  # e.g. "my-database-name"
    ),
    **db_config
)

Java

To see this snippet in the context of a web application, view the README on GitHub.

Note:

  • INSTANCE_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 here .

// Note: For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections
// which is preferred to using the Cloud SQL Proxy with Unix sockets.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.

// The configuration object specifies behaviors for the connection pool.
HikariConfig config = new HikariConfig();

// The following URL is equivalent to setting the config options below:
// jdbc:mysql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&
// socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=<DB_USER>&password=<DB_PASS>
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url

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

config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", INSTANCE_CONNECTION_NAME);

// The ipTypes argument can be used to specify a comma delimited list of preferred IP types 
// for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the 
// SocketFactory to connect with an instance's associated private IP. 
config.addDataSourceProperty("ipTypes", "PUBLIC,PRIVATE");

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

// Initialize the connection pool using the configuration object.
DataSource pool = new HikariDataSource(config);

Node.js

To see this snippet in the context of a web application, view the README on GitHub.

const createTcpPool = async config => {
  // Extract host and port from socket address
  const dbSocketAddr = process.env.DB_HOST.split(':');

  // Establish a connection to the database
  return mysql.createPool({
    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'
    host: dbSocketAddr[0], // e.g. '127.0.0.1'
    port: dbSocketAddr[1], // e.g. '3306'
    // ... Specify additional properties here.
    ...config,
  });
};

Go

To see this snippet in the context of a web application, view the README on GitHub.

var (
	dbUser    = mustGetenv("DB_USER") // e.g. 'my-db-user'
	dbPwd     = mustGetenv("DB_PASS") // e.g. 'my-db-password'
	dbTCPHost = mustGetenv("DB_HOST") // e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
	dbPort    = mustGetenv("DB_PORT") // e.g. '3306'
	dbName    = mustGetenv("DB_NAME") // e.g. 'my-database'
)

dbURI := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", dbUser, dbPwd, dbTCPHost, dbPort, dbName)

// ...

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

// ...

return dbPool, nil

C#

To see this snippet in the context of a web application, view the README on GitHub.

            // Equivalent connection string:
            // "Uid=<DB_USER>;Pwd=<DB_PASS>;Host=<DB_HOST>;Database=<DB_NAME>;"
            var connectionString = new MySqlConnectionStringBuilder()
            {
                // The Cloud SQL proxy provides encryption between the proxy and instance.
                SslMode = MySqlSslMode.None,

                // Remember - storing secrets in plain text is potentially unsafe. Consider using
                // something like https://cloud.google.com/secret-manager/docs/overview to help keep
                // secrets secret.
                Server = Environment.GetEnvironmentVariable("DB_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'
                Database = Environment.GetEnvironmentVariable("DB_NAME"), // e.g. 'my-database'
            };
            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.

development:
  adapter: mysql2
  # Configure additional properties here
  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("DB_HOST") { "127.0.0.1" }%>" # '172.17.0.1' if deployed to GAE Flex
  port: <%= ENV.fetch("DB_PORT") { 3306 }%>
  socket: "<%= ENV.fetch("DB_SOCKET_DIR") { '/cloudsql' } %>/<%= ENV["INSTANCE_CONNECTION_NAME"] %>"

PHP

To see this snippet in the context of a web application, view the README on GitHub.

// $username = 'your_db_user';
// $password = 'yoursupersecretpassword';
// $dbName = 'your_db_name';
// $dbHost = "127.0.0.1";

// Connect using TCP
$dsn = sprintf('mysql:dbname=%s;host=%s', $dbName, $dbHost);

// Connect to the database
$conn = new PDO($dsn, $username, $password, $connConfig);

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

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
  dynamic_substitutions: true

substitutions:
  _DATABASE_PASSWORD_KEY: database_password
  _DATABASE_TYPE: postgres
  _IMAGE_NAME: gcr.io/${PROJECT_ID}/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

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

Best practices and other information

You can use the Cloud SQL Auth proxy when testing your application locally. See the quickstart for using the Cloud SQL Auth proxy for detailed instructions.

You can also test using the Cloud SQL Proxy via a docker container.

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 to include sensitive information in your builds.