James Higginbotham

API Architect

5 Tips For Wrapping Your Database With a REST API

6 minute read

Just about every mobile and web application is backed by at least one database. These applications often require a web-based API to expose the database(s) for storing, querying, and retrieving necessary application data. How can we build an API that exposes our application data but also ensure API longevity to support future needs?

The Challenge of Mapping Tables 1-to-1 Using APIs

I have seen development teams build APIs that offer a 1-to-1 mapping of their database. While there isn’t anything wrong with doing this, it does require some careful thought to prevent problems from occurring in the future. Otherwise, you can end up with an API that exposes too much data, requires too many round-trip API requests, or doesn’t provide the right amount of functionality desired by API consumers.

To help you avoid these common issues, I have outlined 5 tips that will help you when building your next REST API around your backend database.

Tip #1: Expose Resources, Not Implementation Details

One of the common patterns I see is that table names are used within a generic URL naming scheme. It might look something like this (in pseudocode):

get /{table_name}
 results = execute("SELECT * from {table_name}")
 return 200, results.to_json
end

 

While the API developer may feel clever by writing “DRY” code that simplifies the API implementation, it can lead to problems. Too often, I have seen database names that contain abbreviations and special naming conventions that mean something to the internal developers but may mean nothing to other developers. This leads to hard-to-understand URLs, like the following:

  • /Cntry
  • /tblUsers
  • /SCOItems

While abbreviations and naming conventions are not all bad, these implementation details will leak into your API. Instead, choose clear resource names that map to these tables. This will make it easier for developers to understand and allow implementation details to change (such as renaming database table) without impacting other developers or requiring time to push out mobile app updates.

Remember: Most API consumers will not have your source code (nor should they), and they will not want to understand your data model. Even if you are only building an internal API, exposing these details will create a brittle API that can change if your database changes, causing errors for API consumers.

Summary:

  • Use the resource concept names, fully spelled out, to avoid exposing or tightly binding to implementation details
  • Don’t use abbreviations, naming conventions, etc. in your URLs
  • Don’t require the API consumer to know or have access to your code or database schema

Tip #2: Paginate Search Results

During the first version of most APIs, thoughts rarely go to data quantity. Specifically, tables that will likely grow from 100s to 1000s or more records. While some systems start off empty and APIs will “just work” as-is, tables that grow beyond a few hundred records will eventually cause APIs to take longer to query, build the response, and return results. While the server may be able to handle simple requests, if many concurrent requests are made against the API the server may become slow or unresponsive.

In addition to server-side concerns, clients likely will not want to parse hundreds or thousands of records when they can’t all be displayed or processed immediately. Mobile clients in particular must be cautious about how much data comes across the network and processed, to ensure that applications remain fast and responsive to the end user.

To avoid these situations, add pagination support to your API for all search or list-oriented API endpoints. This will limit the data returned on a single request to some defined limit, often 100 or 250 results. You can either include a page number, or an offset and max per page parameters to the URL to control navigation. These values are then passed into the SQL query to limit the number of results returned for a query. Clients will receive the first set of results initially and can page through the results in subsequent requests to obtain more records.

Bonus points: Use Hypermedia links inside the response payload to help clients navigate between first, last, next, and previous pages (but this isn’t necessary) without requiring them to compose the proper request parameter values themselves.

Summary:

  • Missing pagination support can slow down servers and overwhelm clients (especially mobile API consumers)
  • Add pagination into your API, limiting SQL queries to a maximum number of records per request
  • Consider using Hypermedia to allow clients to navigate easily to first, last, next, previous pages

Tip #3: Return Proper Response Codes

API consumers want to know when something worked (and when they didn’t). While you can embed the result into the payload, there are a variety of HTTP response codes that can be used to indicate the success or failure of a request, grouped into five classes: Informational (1xx), Success (2xx), Redirection (3xx), Client Error (4xx) and Server Error (5xx).

The most common response codes used by APIs include:

  • 200 OK – the API request succeeded (general purpose)
  • 201 Created – request to create a new record succeeded
  • 204 No Content – the API request succeeded, but there is no response payload to return
  • 400 Bad Request – the API request is bad or malformed and could not be processed by the server
  • 404 Not Found – couldn’t find a record by ID

Using these more specific response codes enable clients to quickly determine if a request succeeded or failed, without the need to parse the response payload. This is often valuable for mobile API consumers, since they may not want to spend the CPU and battery necessary to parse a payload to determine the result of the request.

Tip: Do not to create your own response codes – stick to the ones outlined in the HTTP specification.

Summary:

  • Use fine-grained response codes to inform clients of the results of a request
  • Choose the code that best represents the server result (e.g. 201 Created), reverting to a more general response code when necessary (e.g. 200 OK)
  • Don’t invent your own response codes

Tip #4: Validate Input Data

While it would be great to have every API client send the right data in the right format every time, this isn’t always the case. If you are wrapping a database in an API, data validation is often forgotten, leading to ingestion of bad data or failed database queries. Be sure to validate all fields to ensure that required fields exist and that fields match their expected types and formats. If the client sends data that is invalid, consider returning a 400 Bad Request response code and include details about which field(s) were invalid and how to correct them. The client can then use the details of the response payload to visualize the errors to a client inside a mobile or web application.

In addition, be sure to scrub any incoming data before including it as part of a SQL statement, to prevent SQL injection attacks. Always verify that submitted data isn’t malformed and encode then it into the SQL statement before using it. Finally, never allow SQL to be passed directly inside the URL – this is just badness waiting to happen.

Read more: I wrote about this recently in the blog post, “The Accidental API”. This post examines the common risks associated with quickly building and deploying APIs that are missing things such as authentication/authorization, monitoring, and data security.

Summary:

  • Validate required fields, field types (e.g. string, integer, boolean, etc), and format requirements
  • Return 400 Bad Request with details about any errors from bad or missing data
  • Escape parameters that will become part of the SQL statement to protect from SQL injection attacks
  • Never allow SQL to be passed in the URL

Tip #5: Consider Adding Workflow-Based APIs

Finally, APIs often need to offer more functionality than just data access. In fact, APIs become more powerful, and therefore more valuable, when common workflows are made available. This will allow API consumers to get more done with fewer API calls, creating a pleasant experience for both the developer and the end user.

Learn how to build workflow-based APIs: I recently wrote a book on the subject of designing great APIs, so I would suggest referring to the book for more details on how to accomplish this. Click this link to get a free sample of the book and a coupon for the purchase of the book.

Summary: 

  • Build in workflow-based APIs in addition to your data access APIs
  • These APIs will help developers get more done with less work
  • The end result is happier developers and end users

 

You May Also Enjoy