- Manage Files in Google Cloud Storage With Python
- Google BigQuery's Python SDK: Creating Tables Programmatically
- Deploy Isolated Applications with Google App Engine
- Working With Google Cloud Functions
- Connect to your Google Cloud Compute Engine
- Cloud SQL: Relational Databases on Google Cloud
- Create a VPS with Google Cloud: Introducing Compute Engine
I've found that the strength of Google Cloud's services comes from consistently delivering across a few key metrics, namely reliability and performance. When dealing with SQL databases, it's hard to imagine any metrics more important than these two things, and Google's Cloud SQL delivers.
To become familiar with Cloud SQL, we're going to walk through the creation of a SQL database and explore the advantages Google Cloud offers us when compared to other solutions. We'll also be dipping into the Cloud SQL API to see how we might manage our SQL database programmatically.
Cloud SQL Versus Alternatives
There aren't many metrics to consider when comparing Cloud database instances aside from price and performance. I've found Cloud SQL to come in on the higher end of both these metrics.
Google Cloud's databases tend to clock in at the higher end of performance benchmarks. To demonstrate, I'm going to borrow a quick comparison of GCP vs AWS cloud databases:
When compared to AWS RDS (green), GCP SQL instances (purple) clock in at better performance speeds across the board. There are other metrics we could compare, but chances are you're going to go with the database of whichever cloud vendor you're locked in with anyway.
A complaint I have with Cloud SQL is that the cost of operation is strangely unpredictable, and not entirely transparent. A lot of managed database offers have straightforward pricing, typically a fixed cost per month depending on the size of your deployment. I haven't found this to be the case. Here's a look at how my monthly Cloud SQL costs have varied in the past year (Cloud SQL in blue, the rest can be ignored):
You can see the first month of running Cloud SQL cost me a little under 50 bucks. That seems a bit on the higher side for a 10gb instance, but I was fine with this. What I didn't anticipate is how much this would vary on a month-month basis. In some cases, the same instance broke 100 bucks per month!
To better understand this, we can look at my bill for the current month:
Not only does the cost of my database rise depending on how much information I'm storing, but take a look at the second line item: Network Internet Egress from Americas to Americas. Costs apparently also rise based on the bandwidth hitting a Cloud SQL instance. Perhaps this is my fault for not during enough due diligence up front. Nonetheless, I just wished this had been a bit more transparent.
Create A Cloud SQL Database
Hit up the Cloud SQL section of your console to get this party started. Database creation on GCP is surprisingly easy. Aside from the instance ID and password, all we really need to specify is region, zone, and version.
Yes, that's it takes to create a Cloud SQL instance!
With our instance created, we can now access the home dashboard for our instance. We can get here simply by clicking into any instance of a SQL database under the Cloud SQL tab. Here's a look at the dashboard:
This is a bit subjective, but I prefer the UI of GCP's SQL instances over almost every other cloud provider I've used so far (I've used all of them).
The Cloud SQL dashboard is split into multiple tabs:
- Overview: This is the home tab of our SQL database. The first visible component is a chart of our db's performance over time, where we can set the metric to whatever we want (pictured as "CPU utilization"). This tab is also where we can easily find connection details to our database, or even connect to the database directly using Google's "Cloud Shell" terminal.
- Users: Manage the database users on this instance.
- Databases: Create or delete top-level databases in this instance.
- Backups: Either schedule automated backups, or create an ad-hoc backup on the spot.
- Replicas: Replicate data in this Cloud SQL instance to other Cloud SQL databases, or an external database.
- Operations: Log of high-level database operations, such as backups.
Databases Gone Wild: Postgres Exposed
To connect to our database, we need to whitelist the IPs which are allowed to hit our database. To do this, click the edit button on your SQL instance's dashboard and expand the Set connectivity tab. From here, you can add your home IP (or whatever) to enable access:
With your IP whitelisted, you officially have a SQL database up and running that you can connect to!
I went ahead and populated my database with some data just to try things out. Connect with whichever GUI you'd prefer:
Enabling the Cloud SQL API
As always with GCP, we need to explicitly activate the API for SQL; that way, they can charge us money forever, long after we've forgotten this tutorial. We can do this here. Are you starting to feel excited? I know I am; just think, all those API calls right around the corner, coming from a real SQL database. Wow.
In the overall process, we've made it here: the part where we run into OAuth2:
I'll admit it took me a good amount of time to decrypt the information which failed to convey here. After clicking into every related link and failing at attempts to hit the API via Postman, the bad vibes started kicking in. What if this isn't the dream after all? To spare you the process, let me introduce you to a very useful GCP tool.
Google API Explorer
Google's API explorer is a GUI for playing with any API, connected to any of your services. This is a cool way to preview what the exact scope of an API is before you sign up for it. Better yet, you can use placeholder User_IDs and User_Secrets since this is basically just a sandbox.
After selecting an 'endpoint' and specifying some details like your project and database instance, you can immediately see (theoretical) results of what the live API can do. This is very useful for exploring what a service API might have to offer before purchasing it entirely!
As we scroll through the endpoints for Cloud SQL, we'll notice a few words popping up over and over, such as "admin" and "list", while lacking phrases such as "show me my god damn data". Google's Cloud SQL API is NOT, in fact, an API to interact with your data, but rather an admin API which enables you to do things probably better suited for, you know, admin consoles.
There's really not much to say to sum this all up: SQL databases are SQL databases, and chances are whichever one you choose will serve you well. If your circumstances happen to make Google's Cloud SQL an option, you're getting a high-end database option that will treat you well.