Cloud SQL: Relational Databases on Google Cloud

Cloud SQL: Relational Databases on Google Cloud

Create a database in Google Cloud using Cloud SQL, compare Cloud SQL to RDS, and learn the admin API.

    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.

    Performance

    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:

    Benchmark comparison of SQL offerings.

    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.

    Price

    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):

    My Cloud SQL costs by month in the last year.

    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:

    My Cloud SQL costs 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.

    That's pretty much it.

    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:

    Dashboard for managing a Cloud SQL instance.

    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:

    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:

    Pick whichever overpriced client suits you best!

    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:

    Refresh tokens? Scopes? Uh oh.

    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.

    Interactive API learning tools beat reading documentation any day.

    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.

    Wrapping Up

    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.

    Todd Birchard's' avatar
    New York City Website
    Engineer with an ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.

    Engineer with an ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.