Tips on API and Database Creation

Intro

Lately I've been working on creating an API that interacts with a database and I picked up on some mistakes that I made and thought it would help everyone out to share it. I'll be starting off with the importance of using standards. The main one is HTTP status codes for web apps. Second, keep your database simple and put more logic onto your API's. Last, but not least, ensure you have tests for everything.

HTTP Status Codes

I’m gonna start this off by talking about pretty much THE biggest tool we have at our disposal when creating a web-based API layer. HTTP status codes. If you don’t know what HTTP status codes are I really recommend checking them out youself: HTTP Status Codes Basically this is a list of responses that have been standardized that use numeric codes to represent them. You have everything from “returned content” to “not found” and of course everything in between. Almost all coders know of this standard so when other people begin using your APIs they will have a good idea of what is going wrong on a request by simple looking at the status code that is returned.

When creating APIs another thing to look out for is creating an API with only itself in mind. Many times we will consider how our APIs are going to be designed and then make the database schema exactly the same as the data moving through the API. Frankly I disagree with this.

Differences in APIs and Databases when Designing

APIs and databases are very different and should be treated as such. On an API you can do more logical statements that will help transfer the data over to your database. On the reverse side of things you want your database to be simple since it is used for storing data. A lot of times people will simply match the database to the API this results in a lot of extra information that didn’t have to be stored.

For example: Let's say you have an invoice table that has a list of charges and a total. In the API layer we would be passing out the list of charges and a total included for the user.

Table
  Invoice : {
    Charges,
    Total,
    Created,
    Updated,
    ID,
}

API
  Invoice : {
    Charges,
    Total,
    ID,
}

Now in this small example the problem is that the total doesn’t have to be included in the database. We could easily sum up the list of charges ourselves before our API sends it out. This could cause many application errors that would involve charges being added but the total not being updated causeing everything to be out of sync and overall that really is just a big loss of data because of inaccuracy. So be aware of what you can trim down and don’t get tunnel visioned into making your database exactly like your API.

APIs

Remember APIs can do so much more then just take in data. They can manipulate data in any way and prepare it all for the database to be stored. APIs can allow you to store simpler data by doing manipulations to the data after pulling it out of the database as well.

Database

Databases are mainly used for storing data and when it comes to massive amounts of data a little bit goes a long way. We should always be striving for a nice simple database that is easy to navigate not only for us but our computers/servers. Remember KISS: Keep It Simple Stupid. We don't want to be able to have these Application errors that would be cause by programmers or usage of API.

Tests

One last note: test everything. A lot of times people will simply test each part on its own and never have tests that are the full flushed out deal. People make mistakes and people mess up. It's only nature. What you can take from this is to test every part together. Many times i’ve made both sides of the coin only to find out I had data that never crossed over to the database in the right format cause many errors once it got pushed up. This isn’t as big of a problem with databases that are very close to the API. But when you have many data manipulations before sending it out or storing it into the db there could be some misteps that are entirely logic errors. So always have a few tests set up that will ensure that your API and database are mingling together happily.

TL;DR

• (Too Long;Didn’t Read)

• Make database simple.

• Make API smart to keep database small.

• Test everything - ie, the API and DB together

• Use HTTP status codes standard on web-based APIs