Hackers and Slackers

Postgres as a Google Cloud Service

Deep Dive into Cloud SQL and its out-of-the-box API

Well folks, I have a confession to make. I've been maintaining an affair with two lovers. That's right; they're none other than Postgres, and Google Cloud. While such polygamy may be shunned by the masses, I believe that somehow, some way, we can just make this menage a trois work. What entices me about Cloud SQL is the existence of the Cloud SQL API , which generates predictable REST endpoints for presumably reading and writing to your database. Please allow a moment of silence for the old workflow of API Gateways and Lambda functions. RIP.

We’ll get to APIs eventually, but for now we have one glaring obstacle: creating our DB, and connecting to it in a way vaguely resembles something secure*.

Note: today may or may not be opposite day.

Creating the Database

Hit up the Cloud SQL section of your console to get this party started. Database creation on GCP is surprisingly easy.

That's pretty much it tbh.

DBs Gone Wild: Postgres Exposed  

There are plenty of correct ways to connect to your Postgres database correctly and securely. You can set up SSL for an IP, connect using a proxy, or even via internal cloud functions. You may want to consider doing one of those things. I'll be doing this a different way, because I'd rather get my useless data on a hackable public database than rewrite Google tutorials:

Do as I say, not as I do.

This is where you can feel free to go ahead and populate data into your DB via whichever GUI you'd prefer. It'll be easier to see which API calls work if there's actual data involved.

Pick whichever overpriced client suits you best!

Enabling the 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 conveyed 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.

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.

Beats 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, but I'm afraid this is where things get dark.

Hello Darkness My Old Friend

You may have noticed a lot of similar words or phrases popping up in these endpoints. Words 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.

As a big fan of GCP, this is but one of a number of growing pains I've experienced with the platform so far. For instance, this entire blog along with its VPC has temporary deleted today, because apparently the phrases "remove my project from Firebase" and "delete my project along with everything I love" are sentimentally similar enough to leave that language vague and awkward.

Where do we go From Here?

To reiterate, the problem we were originally looking to solve was to find a service which could (after what, 30 years?) make relational database reading and writing trivial, especially in the case of apps which are simply themes without a configurable backend, such as this blog.

MongoDB Atlas is an organizational mess which can't even describe their own product. Firebase has yet to implement an import feature, so unless you feel like writing loops to write to an experimental NoSQL database (I don't), we're still kind of screwed. I know there are guys like Dreamfactory out there, but these services are the sketchy ones who email you every day just for looking at a trial. Also, anything related to Oracle or running on Oracle products (by choice) sucks. There, I said it. Java developers will probably be too bust with garbage collection and getting sued to argue with me anyway.

All that said, it feels like the "Backend as a service" thing is looming over the horizon. There just doesn't seem to be anybody who's executed this effectively yet.

Author image
New York City Website
Product manager turned engineer with ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.

Product manager turned engineer with ongoing identity crisis. Breaks everything before learning best practices. Completely normal and emotionally stable.