Execute SQL statements using the Cloud SQL Data API Stay organized with collections Save and categorize content based on your preferences.

Console

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. From the SQL navigation menu, select Connections.
4. Click the Networking tab.
5. Select the Allow Data API checkbox.
6. Click Save.

gcloud

To enable Data API access on an instance, use the gcloud sql instances patch command with the --data-api-access=ALLOW_DATA_API flag:

gcloud sql instances patch INSTANCE_NAME --data-api-access=ALLOW_DATA_API

To disable Data API access, use the --data-api-access=DISALLOW_DATA_API flag:

gcloud sql instances patch INSTANCE_NAME --data-api-access=DISALLOW_DATA_API

Replace INSTANCE_NAME with the name of the instance on which to enable or disable the Data API.

gcloud

To execute a SQL statement against a database on an instance using the gcloud CLI, use the gcloud sql instances execute-sql command.

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--user=USER \
--password-secret-version=PASSWORD_SECRET_VERSION \
--partial-result-mode=PARTIAL_RESULT_MODE

Make the following replacements:

• INSTANCE_NAME: the name of the instance.
• DATABASE_NAME: the name of the database within the instance.
• SQL_STATEMENT: the SQL statement to execute. If the statement contains spaces or shell special characters, then it must be quoted.
• USER: the database user to authenticate as.
• PASSWORD_SECRET_VERSION: the resource name of the Secret Manager secret holding the password for the database user. The secret should be a regional secret and stored in the same region as the Cloud SQL instance. The expected resource name format is projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE: optional. Controls how to respond when result is incomplete. Can be ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT, or PARTIAL_RESULT_MODE_UNSPECIFIED. See Modifying truncation behavior.

Terraform

You can use Data API on Terraform to provision in-database resources such as databases, tables, extensions, users, and privilege grants, without manually connecting to the instance. To execute a SQL script on Terraform, use the google_sql_provision_script Terraform resource.

resource "google_sql_user" "built_in_user" {
  name     = "tf-user"
  host     = "%"  # Don't set this field for PostgreSQL and SQL Server.
  instance = google_sql_database_instance.instance.name
  password = "changeme"
  type     = "BUILT_IN"
}

# Create a regional secret. Global secrets are not supported even if
# located in one region only.
resource "google_secret_manager_regional_secret" "secret" {
  secret_id = "db-password"

  # Use the same region as the Cloud SQL instance.
  location = "us-central1"
}

resource "google_secret_manager_regional_secret_version" "secret_version" {
  secret = google_secret_manager_regional_secret.secret.id
  secret_data = "changeme"
}

resource "google_sql_provision_script" "script" {
  # You can inline the script or import from a file like script  = file("${path.module}/script.sql")
  # When modified, the whole script will be executed again. It's recommended to
  # make the script idempotent with patterns like create if not exists ... or
  # if not exists (select ...) then ... end if.
  script  = "CREATE TABLE IF NOT EXISTS table1 ( col VARCHAR(16) NOT NULL );"

  instance = google_sql_database_instance.instance.name
  database = google_sql_database.database.name
  description = "sql script to create tables"
  user = google_sql_user.built_in_user.name

  # The location should be the same as the Cloud SQL instance's location.
  password_secret_version = "projects/my-project/locations/us-central1/secrets/db-password/versions/latest"

  # The built-in database user and password secret version must be created
  # first. Cloud SQL will retrieve password from Secret Manager
  # and connect to this user account to execute your script.
  depends_on = [
    google_sql_user.built_in_user,
    google_secret_manager_regional_secret_version.secret_version
  ]
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Delete the changes

Deleting a google_sql_provision_script resource won't delete the in-database resources it created. To delete them, you can explicitly add statements in the script such as drop ... if exists and then apply the changes.

REST

To execute a SQL statement against a database on an instance using the REST API, send a POST request to the executeSql endpoint:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/executeSql

The request body should contain the database name and the SQL statement:

{
  "database": "DATABASE_NAME",
  "sqlStatement": "SQL_STATEMENT",
  "user": "USER",
  "passwordSecretVersion": "PASSWORD_SECRET_VERSION",
  "partialResultMode": "PARTIAL_RESULT_MODE"
}

Make the following replacements:

• PROJECT_ID: your project ID.
• INSTANCE_NAME: the name of the instance.
• DATABASE_NAME: the name of the database within the instance.
• SQL_STATEMENT: the SQL statement to execute.
• USER: the database user to authenticate as.
• PASSWORD_SECRET_VERSION: the resource name of the Secret Manager secret holding the password for the database user. The secret should be a regional secret and stored in the same region as the Cloud SQL instance. The expected resource name format is projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• PARTIAL_RESULT_MODE: optional. Controls how the API responds when the result exceeds 10 MB. Can be FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT, or PARTIAL_RESULT_MODE_UNSPECIFIED. See Modifying truncation behavior.