The Excel User's Guide to Databases: Databases and APIs

If you’ve gotten this far, you probably know (a) what a database is and (b) what an API is. A question that has always bothered me is why do we need both? Allow me to further develop the question.

Imagine you’re a software engineer at Twitter (yikes). The data for tweets is stored in a database that probably looks something like this:

The code that builds the Twitter homepage needs to query this database, so you can show the latest tweets, what’s in them, who wrote them, how many likes they have, and such. A basic query to get that information might look like this:

    SELECT
      tweets.tweet_text,
      tweets.sent_at,
      authors.author_name
    FROM tweets
    JOIN authors ON tweets.author_id = authors.id

You’ve also got an API (well many, but this is one) that sits in front of that database. The API was built by some other software engineer, and what it does is runs the exact query above, retrieves the information from the database, and returns it to the requestor. The API request looks something like this:

    GET https://api.twitter.com/tweets

This is standard practice in every engineering organization I’ve ever seen. Database. API in front of database. Query API in application, instead of querying database.

But why don’t you just write that query in your frontend code? Why do you have to go through the trouble of creating all of these different API endpoints to sit in front of your database? Why not just query it directly from your app?

There are basically two answers:

1. There are a lot of good reasons to separate your database from direct queries
2. There are many, many other types of APIs other than single query ones

Let us dive in.

Separation of database and API#

The lens to look at this through is more about fundamental separation between your database and things that use your database. It’s almost like, why is there a front of the house in restaurants? Why don’t you just go and pick up your food from the counter yourself? 

Protecting your backend from strangers#

The data in your database is proprietary; you don’t want just anyone to be able to query it. That’s why apps have authentication, so I can’t see what’s in your Gmail inbox, and vice versa. If you issue queries directly against your database, you’d need to use the database for authentication. But when you build an API in front of it, you can implement your own, custom authentication logic. 

To translate that to a bit more English, let’s take a look at a hypothetical Gmail endpoint that gives you the 50 most recent emails for a particular email address:

    GET https://api.google.com/mail/messages
    {
      username: “gagejustins”
    }

If you knew this URL, what’s stopping you from making this API request yourself, passing whoever’s email address you want, and getting access to their private data?

The answer is authentication: for every API request, you need to prove that you deserve access to the data you’re asking for. When you successfully log into your Gmail account, a few API requests are made that tell Google’s systems that you’re you, and you’re logged in: accordingly, any subsequent requests from your browser – well, at least for the next hour or so – are coming from you. 

With APIs, you can decide how you want your users to authenticate. You can make things more or less secure, revoke access after a short period of time, anything you want. But if each individual copy of your application running in a browser were to query the database directly, then the database would need to be figuring out who deserves access to what data itself. And databases aren’t built for that! 

So in short, having an API sit between your application and your database makes it easier to secure your data and have all requests for it go through a centralized place.

Data cleaning, formatting, and organization#

Databases store data for many reasons, and your frontend is only one of many consumers of that data. The world doesn’t revolve around you! The data you’re storing about tweets is primarily for using to display on people’s homepages, but there’s actually a lot more your team might be doing with it:

• Displaying it in different ways on other pages
• Using it for analytics, Machine Learning, etc.
• Regulatory purposes

Because of all of that, the format your data is stored in the database might not be the format you need it in for the page you’re displaying it on. 

Whatever formatting you need to do – be it as simple as adding a dollar sign, or as complex as pulling in data from multiple sources – is usually something you want to do before your data makes it to your frontend. When you put an API in front of your database, you can do whatever manipulation you need on your server before it makes it to someone’s browser.

Centralizing query logic for re-use#

Several pages in your app might reuse the same data: e.g. the content for a particular tweet is displayed on the homepage, but also in a list, on a particular user’s profile page, etc. Instead of rewriting that query in every single place, you can just use a single API endpoint each time.

Let’s return to our basic homepage query that gets all tweets:

    SELECT
      tweets.tweet_text,
      tweets.sent_at,
      authors.author_name
    FROM tweets
    JOIN authors ON tweets.author_id = authors.id

Suppose some engineer decides they didn’t like the name of the author_id column, and they’re changing it to user_id instead. So we have to update the query to this:

    SELECT
      tweets.tweet_text
      tweets.sent_at,
      authors.author_name
    FROM tweets
    JOIN authors ON tweets.user_id = authors.id

Not that bad, in the scheme of things.

But imagine you’ve written this query in 7 different places. It can be a pain in the ass to update, especially for things more complex than just a column name change. But if you have the query defined once, and wrapped in an API, you just update that centralized query once and move on with your jolly day.