Last Updated: 2020-05-04
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.
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:
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.
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.
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.
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...