Building A Database Api

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

Tip #2: Paginate Search Results

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.

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

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.

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.

---

Source: https://launchany.com/should-you-expose-your-database-directly-using-a-rest-api/

---

uid: 202007210905 tags: #amazon #programming #insights

---

---

---