Last Updated: 2020-05-04
Thinking about the connection
There are many types of applications and frameworks out there. In this codelab, we'll be covering connecting to Cloud SQL from anywhere. The ability to connect is enabled by authorizing the IPs allowed to connect explicitly. This is arguably the least secure option for connecting to a Cloud SQL database, but is also the easiest to setup and start using.
Sometimes it's necessary to do this in production, but if you can avoid doing this, you should opt for a more secure alternative (e.g. using the Cloud SQL Proxy). This setup is most ideally suited for development and testing.
What you'll build
This codelab is very minimalistic. The idea is to walk you through the nuts and bolts of the connection piece, without thinking too heavily about the application itself. In a perfect world, connecting to Cloud SQL is just like connecting to any other instance of a SQL database so you should be able to take what you create in this codelab, and apply it to any production application.
The instructions will include both a walk-through of doing things in the GCP console, as well as including the gcloud command equivalents for using on the CLI or automation.
The individual steps are:
- Create a Cloud SQL instance (this tutorial uses Postgres, but works similarly for MySQL or SQL Server) and authorize specific IPs allowed to connect to it
What you'll need
- A GCP account you have permissions to enable APIs and create services on
- The Postgres client installed to verify connectivity (or MySQL client if you want to use MySQL instead of Postgres)
2. Creating the Cloud SQL instance
Cloud SQL is our managed relational databases offering. It supports MySQL, PostgreSQL and SQL Server. For this codelab, we'll create a Postgres database, but the instructions are similar for all three.
On the Console
Go to the Cloud SQL page, and click on the button.
As I mentioned, most of this codelab is generic to any SQL flavor, but for this codelab, choose PostgreSQL.
- Give your instance an ID
- Pick a region close to wherever you are
- Enter a password for the default user (username will be the default for the selected DB, e.g.
rootfor MySQL or
- Scroll down and click the
show configuration options
- Expand the
- Confirm that
Public IPis checked and that
Private IPis unchecked
- Click the button
- Get the IP address from where you're connecting from. Easiest is just to do a google search for "What's my IP" and the search results has your public facing IP in it.
- Enter the IP specification into the network field, give it a name if you want, and click Done in the
- Scroll down and click Create
The instance takes a couple minutes to start usually.
Once the instance is done being created, click into it in the list, and on the overview page, under the
Connect to this instance header, copy the
public IP address listed there. It won't be there until the instance is fully created even though you can click into the details before it's fully instantiated.
First, you need to grab the IP address you wish to authorize to connect to the Cloud SQL instance. The easiest way to do this is to open a browser, and search for "What's my IP" and the search results has your public facing IP address. If you don't have the ability to open a browser from where you're doing this though, you can use a utility like
dig @resolver1.opendns.com ANY myip.opendns.com +short -4
You'll need to specify a region for your Cloud SQL instance closest to where you are. You can see the list of regions by running:
gcloud sql tiers list
Each tier is available at only certain regions. For the gcloud part of the tutorial, we're just creating a micro instance, so you could find the regions for this tier available by running specifically (as long as you have grep installed):
gcloud sql tiers list | grep db-f1-micro
The command to create the instance itself then looks like (don't forget to replace <AUTHORIZED_IP> with the IP you got from the browser or dig, and <REGION> with one close to you, and a password for the root user ‘postgres'):
gcloud sql instances create sql-codelab-00 --database-version=POSTGRES_11 --tier=db-f1-micro --region=<REGION> --authorized-networks=<AUTHORIZED_IP> --root-password=<PASSWORD>
This takes a few minutes to complete.
Once it's complete, the output on the CLI will have the instance's
PRIMARY_ADDRESS listed. Copy that for the next step.
3. Test the connection and wrapping up
To verify the instance was setup properly, from the machine who's IP address you entered, you can run on the commandline:
psql "host=<IP copied from previous step> port=5432 sslmode=disable user=postgres"
Then specify the default user's password you set up when creating the Cloud SQL instance.
Congratulations! If all has gone well, you should have the Postgres prompt and can run commands against your database.
Check out some of these codelabs...