Execute SQL statements using the Cloud SQL Data API

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

You can also include the --project=PROJECT_ID flag if needed.

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_database_instance" "instance" {
  name             = "my-instance"
  database_version = "MYSQL_8_4"

  settings {
    tier            = "db-perf-optimized-N-2"
    data_api_access = "ALLOW_DATA_API"  # This allows the use of Data API.
    database_flags {
      name  = "cloudsql_iam_authentication"
      value = "on"
    }
  }
}

/*
 * Create a database user for your account and grant roles so it has privilege
 * to access the database. Set the type to CLOUD_IAM_USER for huamn
 * account or CLOUD_IAM_SERVICE_ACCOUNT for service account.
 */
resource "google_sql_user" "iam_user" {
  name     = "account-used-to-apply-this-config@example.com"
  instance = google_sql_database_instance.instance.name
  type     = "CLOUD_IAM_USER"

  # Roles granted to the user. Smaller roles are preferred, if exist.
  # This field doesn't support MySQL 5.6 and 5.7.
  database_roles = ["cloudsqlsuperuser"]
}

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 DATABASE pets;"
  instance = google_sql_database_instance.instance.name

  # Some of your queries may require a database. You can create and use a
  # database in the script or explicitly create and reference a database
  # like database = google_sql_database.database.name.

  description = "sql script to create DBs"

  # The identity account used to apply your Terraform config must exist as an
  # IAM user or IAM service account in the instance. Terraform connects to the
  # instance via IAM database authentication to execute the script.
  depends_on = [google_sql_user.iam_user]
}

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",
  "partialResultMode": "PARTIAL_RESULT_MODE"
  "autoIamAuthn": true
}

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.
• PARTIAL_RESULT_MODE: optional. Controls how the API responds when the result exceeds 10 MB. Can be FAIL_PARTIAL_RESULT or ALLOW_PARTIAL_RESULT. See Modifying truncation behavior.