API for retrieving/send data from/to a database

Question

I would like to hear experiences from you? How can I best create an API for retrieving data from a database. If you send the view name or procedures name, so would not this be totally fine with tankne on safety. So I'm wondering if anyone has experience or ideas on this?

Should I for example have a field that tells what the user who connects the API has access to? Table and row access.

Answer 1

I'll give it a shot.

You've already mentioned Web API, so I'll assume you're using that which means you have a REST API on top of a database.

Things to do:

1. Come up with the data model you want to expose to the users. This will likely be different in various ways from your database model. If you're a store you might have 2-3 different tables to store products, but you will want to expose a "product" with one API call.

2. Once you have your user facing data model, start writing tests. You need unit tests for your Web API controllers and you need to find a way to mock the database calls you're making. Tests are well worth the effort!

3. For security you have many options. You can pick from things like HMAC (https://en.wikipedia.org/wiki/Hash-based_message_authentication_code) or OAuth (Best way to create a TOKEN system to authenticate web service calls?) or even JWT (Secure WebAPI with a JWT).

4. Once you have a user authenticated, you can assign them privileges. They can read, write and update depending on what they have access to. You can have a database table to control this potentially.

5. I would recommend thinking about versioning ahead of time. My recommendation would be to always have two versions of your API - current and previous. You deploy the API and when you replace it for the first time you support the deprecated API and the new one. Don't try to support more than two versions.

6. Do your best to write some kind of interface that abstracts the database before sending data via Web API. This helps you swap out the database entirely in the future if you need to. It also helps unit testing.