Execute SQL statements using the Cloud SQL Data API

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.

Before you begin

Before you can execute SQL statements on an instance, do the following:

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.

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=DENY_DATA_API flag:

gcloud sql instances patch INSTANCE_NAME --data-api-access=DENY_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 gcloud CLI, use the gcloud beta sql instances execute-sql command:

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

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

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.

Modify truncation behavior

You can control how large results are handled when executing SQL.

  • Include the "partialResultMode" field in the request. This field accepts the following values:
    • FAIL_PARTIAL_RESULT: 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.

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 MySQL 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 SESSION MAX_EXECUTION_TIME isn't supported. For Cloud SQL for MySQL 5.6 and 5.7, long running DDL statements timing out can cause orphaned files or tables that can't be safely rolled back. Be cautious with statements like ALTER TABLE on large tables.
  • 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 "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.
  • For Cloud SQL for MySQL, notices and warnings are available only for the last statement of a multi-statement execution.
  • 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.