MySQL, Google Cloud, and a REST API that Generates Itself
It wasn’t too long ago that I haphazardly forced us down a journey of exploring Google Cloud’s cloud SQL service. The focus of this exploration was Google’s accompanying REST API for all of its cloud SQL instances. That API turned out to be a relatively disappointing administrative API which did little to extend the features you’d expect from the CLI or console.
You see, I’ve had a dream stuck in my head for a while now. Like most of my utopian dreams, this dream is related to data, or more specifically simplifying the manner in which we interact with it. For industry synonymous with AI and automation, many of our very own tools (including ETL tools) involve way too much manual effort in my opinion. That’s right: I’m talking about the aspiration to Slack while we Hack.
The pitch is this: why do we keep setting up databases, endpoints, and the logic to connect them when, 90% of the time, we’re building the same thing over and over? Let me guess: there’s a GET endpoint to get records from table X, or a POST endpoint to create users. I know you’ve built this because we all have, but why do we keep building the same things over and over in isolation? It looks like we might not have to anymore, but first let’s create our database.
Creating a MySQL Instance in GCP
Full disclosure here: the magical REST API thing is actually independent from Google Cloud; the service we’ll be using can integrate with any flavor of MySQL you prefer, so go ahead and grab that RDS instance you live so much if you really have to.
For the rest of us, hit up your GCP console and head into making a new SQL instance. MySQL and Postgres are our only choices here; stick with MySQL.
There isn’t much to spinning up your instance. Just be sure to create a user and database to work from.
Your SQL Firewall and Permissions
Your instance is set to “public” by default. Oddly, “public” in this case means “accessible to everybody on your IP whitelist, which is empty by default,” so really kind of the opposite of public really.
In fact, if you hypothetically did want to open your instance publicly, Google Cloud will not allow it. This is good on them, and is actually fairly impressive the depths they go to avoid the IP 0.0.0.0 from ever appearing anywhere in the instance. Go ahead, open the shell and try to add
bind address=0.0.0.0 yourself, wise guy (another fun fact you’ll learn in the shell: apparently GCP’s version of MySQL is actually a MariaDB instance)?
The point is, whitelist your IP address. Simply "Edit" your instance and add your address to the authorized networks.
The Magic API
Now, we’ve never actually endorsed any SaaS products on Hackers and Slackers, so this next part is going to feel a bit a bit weird. I’m not sure why, as the service is apparently free, thus I’m clearly not getting paid for any of this.
Anyway, the service is called Apisentris, and the idea is that it will build whatever time-consuming monstrosity of a REST API you were planning to build to access your data for you. Via their own words:
What does this actually mean? It means if you create a table called articles in your database, you will immediately have an endpoint to fetch said articles, and it would look like https://apisentris.com/api/v1/articles. Your client ID and credentials would obviously need to be provided to indicate that you're, well, you.
Grabbing entire tables at once would be silly, which is why they also autogenerate filters based on the contents of your table:
Oh yeah, and you can also handle user management via this API as well, if you're building an actual app:
I'll assume you're sold on the idea by now. If a free service that handles the hard parts of backend logic for free isn't your cup of tea, clearly you aren't Slacker material.
Setting it all up
As we did before with our own IP, we'll need to whitelist Apisentris' IP the same way in GCP console. Their IP is
Create a table in your database with some data just to test things out. When you're logged in, you'll be able to see all the endpoints available to you and the associated attributes they have:
Any way you slice it, the concept of a self-generating API is very cool and yet somehow still not the norm. I'm actually shocked that there are so few people in the Data industry who know "there must be a better way," but then again, data science and software engineering are two very different things. For my fellow Data Engineers out there, take this as a gift and a curse: you have the gift of knowing better from your software background, but are cursed with watching the world not quite realize how pointless half the things they do truly are.
Oh well. We'll be the ones building the robots anyway.