Cloud SQL

​Cloud SQL is managed MySQL, PostgreSQL, and SQL Server. Cloud SQL automates backups, replication, and failover to ensure your database is reliable, highly available.
Cloud SQL has automatic data encryption at rest and in transit. Private connectivity with Virtual Private Cloud (VPC) and user-controlled network access that includes firewall protection. Compliant with SSAE 16, ISO 27001, PCI DSS v3.0, and HIPAA
Cloud SQL Instance
Enable API
1
gcloud services enable sqladmin.googleapis.com
Copied!
Create an Instance
MySQL
PostgreSQL
SQL Server
Create a new Cloud SQL - MySQL Instance.
1
gcloud sql instances create mysql-instance \
2
  --database-version=MYSQL_5_7 \
3
  --region=us-central1 \
4
  --cpu=2 \
5
  --memory=4G \
6
  --root-password=[CHOOSE A PASSWORD]
Copied!
Create a new Cloud SQL - PostgreSQL instance.
1
gcloud sql instances create postgresql-instance \
2
  --database-version=POSTGRES_11 \
3
  --region=us-central1 \
4
  --cpu=2 \
5
  --memory=4G \
6
  --root-password=[CHOOSE A PASSWORD]
Copied!
Create a new Cloud SQL - SQL Server instance.
1
gcloud beta sql instances create sqlserver-instance \
2
  --database-version=SQLSERVER_2017_STANDARD \
3
  --region=us-central1 \
4
  --cpu=2 \
5
  --memory=4G \
6
  --root-password=[CHOOSE A PASSWORD]
Copied!
Create a Database
MySQL
PostgreSQL
SQL Server
Create a new database inside of the MySQL database instance.
1
gcloud sql databases create orders --instance=mysql-instance
Copied!
Create a new database inside of the PostgreSQL database instance.
1
gcloud sql databases create orders --instance=postgresql-instance
Copied!
Create a new database inside of the SQL Server database instance.
1
gcloud sql databases create orders --instance=sqlserver-instance
Copied!
Connect to Database instance
By default, every database instance has a public IP address. However, the instance is not publicly accessible because it's protected by the firewall.
To easily connect to the database instance from command line:
MySQL
PostgreSQL
SQL Server
You need the MySQL client installed locally first, so that you can use mysql to connect to any MySQL server.
Connect to the MySQL instance using gcloud CLI.
1
gcloud sql connect mysql-instance
Copied!
You need the PostgreSQL client installed locally first, so that you can use psql to connect to any PostgreSQL server.
Connect to the PostgreSQL instance using gcloud CLI.
1
gcloud sql connect postgresql-instance
Copied!
You need the MS SQL Server client installed locally first, so that you can use mssql-cli to connect to any SQL Server.
Connect to the SQL Server instance using gcloud CLI.
1
gcloud sql connect sqlserver-instance
Copied!
You can configure Cloud SQL instances to only have private IP addresses, so that it's only accessible from a Virtual Private Cloud network.
Create a Table
From the command line connection, you can use the client to create a table for the corresponding database. For example:
MySQL
PostgreSQL
SQL Server
1
# Change to orders database
2
USE orders;
3
​
4
CREATE TABLE order_items (
5
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
6
  order_id BIGINT,
7
  description VARCHAR(255),
8
  quantity INT DEFAULT 1
9
);
10
​
11
CREATE TABLE orders (
12
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
13
  description VARCHAR(255),
14
  creation_timestamp TIMESTAMP
15
);
16
​
17
ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id);
Copied!
1
# Change to orders database
2
\c orders
3
​
4
CREATE TABLE order_items (
5
  id BIGSERIAL NOT NULL PRIMARY KEY,
6
  order_id BIGINT,
7
  description VARCHAR(255),
8
  quantity INT DEFAULT 1
9
);
10
​
11
CREATE TABLE orders (
12
  id BIGSERIAL NOT NULL PRIMARY KEY,
13
  description VARCHAR(255),
14
  creation_timestamp TIMESTAMP
15
);
16
​
17
ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id);
Copied!
1
USE orders;
2
​
3
CREATE TABLE order_items (
4
  id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
5
  order_id BIGINT,
6
  description VARCHAR(255),
7
  quantity INT DEFAULT 1
8
);
9
​
10
CREATE TABLE orders (
11
  id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
12
  description VARCHAR(255),
13
  creation_timestamp TIMESTAMP
14
);
15
​
16
ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id);
Copied!
Add a User
You can add a user using gcloud command line:
MySQL
PostgreSQL
SQL Server
Use gcloud command line to create a new user:
1
gcloud sql users create order-user
2
  --instance=mysql-instance \
3
  --password=...
Copied!
The new user has no privileges. Connect to the database server and grant privileges. Refer to MySQL documentation to use GRANT.
Use gcloud command line to create a new user:
1
gcloud sql users create order-user \
2
  --instance=postgresql-instance \
3
  --password=...
Copied!
The new user has no privileges. Connect to the database server and grant privileges. Refer to PostgreSQL documentation to use GRANT.
Use gcloud command line to create a new user:
1
gcloud sql users create order-user \
2
  --instance=sqlserver-instance \
3
  --password=...
Copied!
The new user has no privileges. Connect to the database server and grant privileges. Refer to SQL Server documentation to use GRANT.
Instance Connection Name
Every Cloud SQL Instance has a unique instance connection name for the form of PROJECT_ID:REGION:INSTANCE_NAME.
Find the Instance Connection Name using gcloud command line:
1
gcloud sql instances describe INSTANCE_NAME --format='value(connectionName)'
Copied!
MySQL
PostgreSQL
SQL Server
MySQL instance's Instance Connection Name
1
gcloud sql instances describe mysql-instance \
2
  --format='value(connectionName)'
Copied!
PostgreSQL instance's Instance Connection Name
1
gcloud sql instances describe postgresql-instance \
2
  --format='value(connectionName)'
Copied!
SQL Server instance's Instance Connection Name
1
gcloud sql instances describe sqlserver-instance \
2
  --format='value(connectionName)'
Copied!
JDBC
There are different ways to connect to a Cloud SQL instance. All methods will configure a JDBC URL to allow you to use the corresponding JDBC Driver, and subsequently, JPA / Hibernate and Spring Data.
Method
MySQL
PostgreSQL
SQL Server
Considerations
Cloud SQL Starter
✅
✅
🚫
Easy to configure for Spring Boot projects.
Cloud SQL Socket Factory
✅
✅
🚫
Works with non Spring Boot projects.
Cloud SQL Proxy
✅
✅
✅
Offloads authentication to proxy.
VPC Private IP
✅
✅
✅
Access via VPC. Can be used with all of the other methods above.
Cloud SQL Starter
When using Spring Boot, you can use Spring Cloud GCP's Cloud SQL starter.
Cloud SQL starter will automatically:
Add dependency to the corresponding JDBC driver, and the Cloud SQL socket factory. You do not need to add those dependency separately.
Configure the JDBC URL for the corresponding database instance.
Dependency
Add the Cloud SQL Starter dependency:
MySQL
PostgreSQL
SQL Server
Maven:
pom.xml
1
<dependency>
2
    <groupId>org.springframework.cloud</groupId>
3
    <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
4
</dependency>
Copied!
Gradle:
build.gradle
1
dependencies {
2
    compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-sql-mysql'
3
}
Copied!
Maven:
pom.xml
1
<dependency>
2
    <groupId>org.springframework.cloud</groupId>
3
    <artifactId>spring-cloud-gcp-starter-sql-postgresql</artifactId>
4
</dependency>
Copied!
Gradle:
build.gradle
1
compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-sql-postgresql'
Copied!
Cloud SQL Starter is not supported for SQL Server. Use Cloud SQL Proxy instead.
Configuration
Configure Spring Boot application'sapplication.properties with Instance Connection Name and the database name:
application.properties
1
# Retrieve instance connection name from the previous step
2
spring.cloud.gcp.sql.instance-connection-name=INSTANCE_CONNECTION_NAME
3
spring.cloud.gcp.sql.database-name=orders
4
​
5
# Cloud SQL starter automatically configures the JDBC URL
6
​
7
# Configure username/password
8
spring.datasource.username=...
9
spring.datasource.password=...
10
​
11
# Configure connection pooling if needed
12
spring.datasource.hikari.maximum-pool-size=10
Copied!
Sample
​Spring Boot with Cloud SQL PostgreSQL​
​Spring Boot with Cloud SQL MySQL​
Cloud SQL Socket Factory
If you don't use Spring Cloud GCP's Cloud SQL starter, and need to configure JDBC URL directly, you can use Cloud SQL Socket Factory with existing JDBC driver.
Dependency
In addition to the JDBC Driver dependency, add the Cloud SQL Socket Factory dependency:
MySQL
PostgreSQL
Maven:
1
<dependency>
2
    <groupId>com.google.cloud.sql</groupId>
3
    <artifactId>mysql-socket-factory-connector-j-8</artifactId>
4
    <version>1.1.0</version>
5
</dependency>
Copied!
Gradle:
build.gradle
1
dependencies {
2
    compile 'com.google.cloud.sql:mysql-socket-factory-connector-j-8:1.1.0'
3
}
Copied!
Different MySQL Socket Factory artifact is needed for different MySQL Connector/J versions. See MySQL Socket Factory README for more information.
Maven:
1
<dependency>
2
    <groupId>com.google.cloud.sql</groupId>
3
    <artifactId>postgres-socket-factory</artifactId>
4
    <version>1.1.0</version>
5
</dependency>
Copied!
Gradle:
build.gradle
1
dependencies {
2
    compile 'com.google.cloud.sql:postgres-socket-factory:1.1.0'
3
}
Copied!
Configuration
MySQL
PostgreSQL
SQL Server
MySQL instance's JDBC URL with Cloud SQL Socket Factory follows the format of:
1
jdbc:mysql:///DATABASE_NAME?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.mysql.SocketFactory
Copied!
The JDBC URL for the Cloud SQL instance in this example is:
1
jdbc:mysql:///orders?cloudSqlInstance=PROJECT_ID:us-central1:mysql-instance&socketFactory=com.google.cloud.sql.mysql.SocketFactory
Copied!
PostgreSQL instance's JDBC URL with Cloud SQL Socket Factory follows the format of:
1
jdbc:postgresql:///DATABASE_NAME?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.postgres.SocketFactory
Copied!
The JDBC URL for the Cloud SQL instance in this example is:
1
jdbc:postgresql:///orders?cloudSqlInstance=PROJECT_ID:us-central1:postgresql-instance&socketFactory=com.google.cloud.sql.postgres.SocketFactory
Copied!
Cloud SQL Socket Factory is not supported for SQL Server. Use Cloud SQL Proxy instead.
Cloud SQL Proxy
​Cloud SQL Proxy is the generic way of establishing secured connection to a Cloud SQL instance. Rather than using the Cloud SQL Socket Factory to exchange certificates, Cloud SQL Proxy will authenticate and exchange the certificates.
Cloud SQL Proxy diagram
Install Cloud SQL Proxy:
1
gcloud components install cloud_sql_proxy
Copied!
Start the proxy:
MySQL
PostgreSQL
SQL Server
1
# Refer to Instance Connection Name from previous section
2
cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:3306
Copied!
1
# Refer to Instance Connection Name from previous section
2
cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:5432
Copied!
1
# Refer to Instance Connection Name from previous section
2
cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:1433
Copied!
You can then establish connections on localhost with the corresponding ports.
MySQL
PostgreSQL
SQL Server
Connect with mysql CLI:
1
mysql -u root -p
Copied!
Or, connect with JDBC using JDBC URL:
1
jdbc:mysql://localhost/orders
Copied!
Connect with psql CLI:
1
psql -h localhost -U postgres
Copied!
Or, connect with JDBC using JDBC URL:
1
jdbc:postgresql://localhost/orders
Copied!
Connect with mssql-cli CLI:
1
mssql-cli -U sqlserver
Copied!
Or, connect with JDBC using JDBC URL:
1
jdbc:sqlserver://localhost/databaseName=orders
Copied!
Unix Socket Domain
You can optionally configure Cloud SQL Proxy to expose not a TCP IP port, but using Unix Socket Domain instead, and configure the Cloud SQL Socket Factory to connect using the Unix Socket Domain. See Connect External App documentation for more details.
VPC Private IP
If your Cloud SQL instance is on VPC and has a private IP, and your application is running in the Cloud able to access the same VPC, then configure JDBC drivers normally connecting to the private IP address.
R2DBC
You can use R2DBC driver for reactive database access when you connect to Cloud SQL instances using:
Cloud SQL Proxy
VPC Private IP
Using R2DBC Cloud SQL Connector
Cloud SQL Proxy or VPC Private IP
You can use standard R2DBC driver to connect using the IP address. See R2DBC documentation for corresponding driver usages:
​r2dbc-mysql​
​r2dbc-postgresql​
​r2dbc-mssql​
Cloud SQL Connector
You can use R2DBC Cloud SQL Connector that automatically exchanges the certificates like the Cloud SQL Socket Factory.
See Cloud SQL Socket Factory README for more information on configuring the R2DBC driver for Cloud SQL.