Execute SQL statements using the Cloud SQL Data API

MySQL | PostgreSQL | SQL Server

This page describes how to execute SQL statements against databases on Cloud SQL instances using the Data API. With the Data API, you use the Cloud SQL Admin API and gcloud CLI to run SQL statements on any instance where you've enabled Data API access.

You can use the Data API with instances that use public IP addresses, private services access, or Private Service Connect. The Data API supports all types of SQL statements including data manipulation language (DML), data definition language (DDL), and data query language (DQL). The Data API is good for running small and quick administrative statements, such as creating database roles or users and making small schema updates. You can also use the Data API to enable PostgreSQL extensions.

Before you begin

Before you can execute SQL statements on an instance, take the following steps.

Configure the database user

The Data API needs to authenticate as a database user to execute SQL statements. You can authenticate as a built-in user, IAM user, IAM service account, or IAM group.

To authenticate using IAM, do the following:

Configure the instance for IAM database authentication.
Add an IAM user, service account, or group to the instance.
Grant the account the required roles or privileges to execute SQL statements. You can assign database roles while creating the account or updating the account. If you have created custom database roles with least privileges, assign them to the account. Otherwise, assign the predefined cloudsqlsuperuser role to the account, use the Data API to create new custom database roles with lesser privileges, and then grant the new roles to the account in place of cloudsqlsuperuser.

To authenticate as a built-in user using password, do the following:

Create a user.
Use Secret Manager to create a regional secret to store the password. For security, the Data API asks for the secret's resource name instead of the password in the API request. The regional secret should be stored in the same region as your Cloud SQL instance. A secret created using Secret Manager's global endpoint are not supported even if it's stored in the same region.
define optional IAM conditions to allow a user to access a specific secret but not other secrets in the project.

Required roles or permissions

By default, user or service accounts with one of the following roles have the permission to execute SQL statements on a Cloud SQL instance (cloudsql.instances.executesql):

Cloud SQL Admin (roles/cloudsql.admin)
Cloud SQL Instance User (roles/cloudsql.instanceUser)
Cloud SQL Studio User (roles/cloudsql.studioUser)

You can also define an IAM custom role for the user or service account that includes the cloudsql.instances.executesql permission. This permission is supported in IAM custom roles.

Enable or disable the Data API

To use the Data API, you must enable it for each instance. You can disable the Data API at any time.

Console

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

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
From the SQL navigation menu, select Connections.
Click the Networking tab.
Select the Allow Data API checkbox.
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.

Execute a SQL statement

You can execute SQL statements against databases on your Cloud SQL instance using either gcloud CLI or the REST 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.

To connect using IAM:

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.

To connect as a built-in user using password:

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:

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

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

  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. If a service account is used
 * and the instance is Postgres, trim the ".gserviceaccount.com"
 * suffix to avoid exceeding the username length limit.
*/
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. For least privilege, you can create smaller roles
  # and then assign them to this user in place of `cloudsqlsuperuser`.
  database_roles = ["cloudsqlsuperuser"]
}

resource "google_sql_database" "database" {
  name     = "my-database"
  instance = google_sql_database_instance.instance.name
}

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"

  # 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

Launch Cloud Shell.
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).

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
```
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.
Review and modify the sample parameters to apply to your environment.
Save your changes.
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

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.
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.
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, ALLOW_PARTIAL_RESULT, or PARTIAL_RESULT_MODE_UNSPECIFIED. See Modifying truncation behavior.

Modify truncation behavior

You can control how large results are handled when executing SQL, by including the "partialResultMode" field in the request. This field accepts the following values:

FAIL_PARTIAL_RESULT: Default. Throw an error if the result exceeds 10 MB or if only a partial result can be retrieved. Don't return the result.
ALLOW_PARTIAL_RESULT: Return a truncated result and set partial_result to true if the result exceeds 10 MB or if only a partial result can be retrieved due to error. Don't throw an error.
PARTIAL_RESULT_MODE_UNSPECIFIED: Unspecified mode, effectively the same as FAIL_PARTIAL_RESULT.

Limitations

The size limit for a response is 10 MB. Results exceeding this size are truncated if partialResultMode is set to ALLOW_PARTIAL_RESULT, otherwise an error is thrown.
Requests are limited to 0.5 MB.
You can only run SQL statements for Cloud SQL for PostgreSQL instances that are running.
Cloud SQL doesn't support using the Data API with instances that are set up for external server replication.
Requests taking longer than 30 seconds are canceled. Setting a higher statement timeout using SET STATEMENT_TIMEOUT isn't supported.
Cloud SQL limits the number of concurrent executeSql requests to 10 per instance for each user. If this limit is reached, subsequent requests fail with "At most 10 concurrent queries may be run on this instance. Try again later." or "Maximum concurrent reads 10 reached."
Each response can contain a maximum of 10 database messages or warnings.
If there is a statement syntax or execution error, then no result is returned.
Statements that consume a large amount of memory can cause out-of-memory errors. For more information on avoiding these errors, see Best practices for managing memory usage. A database instance running with high memory utilization often causes performance issues, stalls, or even database downtime.
Data API can be temporarily blocked for data integrity purposes when certain maintenance operations are ongoing on the instance. Retry later if it happens.
The SQL script and its execution response might transit through intermediate locations between your client and the location of the target instance. For this reason, requests will fail with the error "not supported for instances in certain Assured Workloads control packages folders" for certain Assured Workloads projects and for projects with constraints/sql.restrictNoncompliantResourceCreation manually enforced.

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

Before you begin

Configure the database user

Required roles or permissions

Enable or disable the Data API

Console

gcloud

Execute a SQL statement

gcloud

Terraform

Apply the changes

Prepare Cloud Shell

Prepare the directory

Apply the changes

Delete the changes

REST

Modify truncation behavior

Limitations

Execute SQL statements using the Cloud SQL Data API