PostgreSQL Cloud Database on Google Cloud

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

PostgreSQL Cloud Database on Google Cloud

    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 PostgreSQL, and Google Cloud. While such polygamy may be shunned by the masses, I believe that somehow, some way, we can just make this ménage à 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 Our Cloud 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.

    Databases 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.

    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, 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.

    UPDATE: As it turns out, there is a service out there that accomplishes everything we hoped to achieve in Google cloud, and it is called Apisentris. It's awesome, it's free, and the guy behind it is a chill dude.

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

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