Cloud SQL Setup

Ready to try out Cloud SQL? Great! You’re in the right place.

In this post, we’ll look at the things you need to do to create a Cloud SQL database instance, set up a service account, and run the Cloud SQL Proxy so that you can connect from your own code (in any language) or client tools running locally on your dev machine.

I’ve used SQL Server for this example, but the only difference for setting up a MySQL or PostgreSQL instance is the default port number, as covered below. Once you have a database instance provisioned in Cloud SQL and have the proxy running and listening for connections, working with the database is pretty much the same as working with those database types in any other environment.

Let’s get started!

Create a project and SQL Server instance

To get started, the first step is to create a Google Cloud project, and the quickest and simplest way to do that is through the Google Cloud Console. If you’ve never created a project before, see the documentation for Creating and Managing Projects.

Once you have a project created, add a SQL Server instance:

• In the console, click the navigation menu and then SQL. You’ll see options for Create Instance or Migrate Data — select Create Instance.
• Next step is to select a database engine — select SQL Server. You’ll need to wait a minute for the Compute Engine API to be initialized (this is used to create the instance), and once that is done you’ll be prompted for Instance info.
• Enter an Instance ID, which will identify this particular Cloud SQL instance within your project.
• Enter a password for the sqlserver default admin account, or (recommended) click the Generate button to generate a secure random password.
• For this demo, we’ll leave all the other instance settings at their default values. Click Create to create the instance, and wait for the provisioning to be completed, which can take a few minutes.

After the instance has been created, you will see it in the list of SQL instances for your project. (If you’re not on that list, select SQL from the navigation menu.) Click on the instance, and make a note of the Instance connection name, which you’ll need for accessing the instance later.

As a final step in setting up our SQL Server instance, we need to enable the Cloud SQL Admin API, which will be used by the Cloud SQL Proxy. Follow these steps in the Cloud Console:

• Select API & Services from the navigation menu, then Dashboard.
• Select Enable APIs & Services, then type Cloud SQL Admin API, select it from the list, and click on Enable.

Create a service account

Google Cloud IAM provides fine-grained access control for project resources, and there are many ways to configure and manage IAM options. You can use your own identity as the project owner for quick access, but the recommended approach is to create a service account and then assign the appropriate roles and permissions to that account.

Note: in this example we’re creating a new service account, but in some cases you may choose to use an existing service account. For example, you could use the default service account that was created for your project type, such as an App Engine default service account.

If you’re new to service accounts, refer to the documentation for Creating and managing service accounts. Here are the steps to create a service account we can use for working with our SQL Server instance:

• In the Cloud Console, select IAM & admin from the navigation menu, then select Service accounts and then Create Service Account.
• Enter a brief Service account name, and note that a Service account ID is auto-generated from the name. You can also enter an optional Service account description, to help remember the purpose of this service account later.
• Select Create.

After the service account is created, you’ll have an option to create Service account permissions. Click the dropdown for select a role, then select Cloud SQL and Cloud SQL Client, then select Continue.

Selecting the Cloud SQL Client role for a service account

Next select Create Key. This will download a JSON file to your local machine, which you can use for access to your project’s resources under that service account. Protect that file and treat it like a password — be careful not to push it to a GitHub repo, for example.

To recap, here’s the information we have after completing the above setup steps:

• A Project ID for the Google Cloud project.
• The Instance connection name for the SQL Server instance.
• The password for the sqlserver default admin account.
• The JSON credentials file for the service account.

With that information in hand, we’re ready to install and launch the Cloud SQL Proxy.

The Cloud SQL Proxy

The Cloud SQL Proxy is a small executable that you can download and run on Windows, Mac, or Linux platforms. When you’re writing code or running client tools locally, your code will communicate with the SQL Server database via the proxy, which provides a fast, secure, encrypted connection to Cloud SQL without any need to manage allowed IP addresses or SSL configuration details.

Example of doing local Python development via the Cloud SQL Proxy

The proxy may also be used for various types of Google Cloud deployments, such as Connecting from Google Kubernetes Engine, where it is deployed in the sidecar container pattern. But in this post we’re just looking at how to set up a local development environment.

You can find thorough instructions for downloading the proxy at About the Cloud SQL Proxy. Once you have it downloaded, you can launch the proxy to connect to your SQL Server instance. On the proxy command line, you need to specify your instance connection name as well as the JSON credentials file for the service account to be used, as shown here:

<Cloud SQL Proxy executable> -instances=<instance connection name>=tcp:1433 -credential_file=<JSON credentials file>

Note the TCP port number of 1433, which is the default for SQL Server connections. Here are the default port numbers for the three types of database engines currently available in Cloud SQL:

• SQL Server — port 1433
• MySQL — port 3306
• PostgreSQL — port 5432

For Windows development, I like to create a desktop shortcut that launches the proxy using the command shown above. Then I can just double-click that icon and I’m ready to start working with my project and communicating with my database. Alternatively, on Linux platforms you can simply append & to the proxy invocation command line to run it in a separate process, or in PowerShell you can use this syntax:

Start-Process -filepath "<Cloud SQL Proxy executable>" -ArgumentList "-instances=<instance connection name>=tcp:1433 -credential_file=<JSON credentials file>"

Regardless of how you launch the proxy, if you have everything configured as described above then you should see a message telling you that the proxy is listening on 127.0.0.1 and ready for new connections:

2019/12/12 10:52:44 using credential file for authentication; email=<service account>@<project ID>.iam.gserviceaccount.com
2019/12/12 10:52:45 Listening on 127.0.0.1:1433 for <instance connection name>
2019/12/12 10:52:45 Ready for new connections