Purpose

Provide an HTTP REST API to an sqlite database file.

Terminology

Mostly taken from here and here.

• Resource is an object or representation of something, which has some associated data with it and there can be set of methods to operate on it. E.g. Animals, schools and employees are resources and delete, add, update are the operations to be performed on these resources.
• Collections are set of resources, e.g Companies is the collection of Company resource.

Sample API

We are going to use two URLs structures per resource

• /employees represents an action for the whole employees table.
• /employees/3 represents an action just for the entries from the employees table that their primary key is 3.

We need to make a decision on how we will treat tables that do not have a primary key. For now we will be returning an error.

The actual data returned in the response body is going to be wrapped in a data field

This applies for the requests that actually have a response body.

e.g.

GET /employees/3

{
  "data": {
    "id": 3,
    "name": "Makis"
  }
}

We are going to have metadata headers

e.g.

HEAD /companies

...
sqlite/rows 129
sqlite/primary-key id
sqlite/foreign-key employees

We should map HTTP Status Codes to SQLite error codes

• The sqlite error codes can be found here and here.
• Available HTTP Status codes can be found here

Ideally, in the case of an error we should be populating an errors object in the response, e.g.

{
  "errors": [
    {
      "status": 403,
      "detail": "SQLITE_CONSTRAINT",
      "code": 19,
      "links": {
        "about": "https://www.sqlite.org/rescode.html#constraint"
      }
    }
  ]
}

HATEOAS

i.e. Provide links for API navigation in the responses:

GET /employees
{
  "data": [
    {
      "id":1,
      "name":"Paul",
      "links": [
        {
          "salary": "http://www.domain.com/employees/1/salaryStatements"
        }
      ]
    }
  ]
}

Pagination

We can possibly do this with sqlite limit / first.

GET - Request data for a collection/resource

• Idempotent: yes

• Side-effects: no

• request body: no

• response body: yes

• GET /companies should return a list of all companies.

• GET /companies/3 should return the data for the company 3.

• GET /companies/3/employee should return the data for all employees from company 3. This assumes that the companies table has a foreign key to the employees table.

HEAD - Get metadata for a collection/resource

• Idempotent: yes

• Side-effects: no

• request body: no

• response body: no

• HEAD /companies should return metadata headers for the companies table.

PUT - Update/create a resource

• Idempotent: yes

• Side-effects: yes

• request body: yes

• response body: no

• Used for full updates, for partial updates we will be using PATCH.

• PUT /companies should create a company.

• PUT /companies/3 should update the company 3.

• PUT /companies/3/employees/john should update the employee john of company 3.

POST - Create a new resource

• Idempotent: no

• Side-effects: yes

• request body: yes

• response body: yes

• POST /companies should create a new company.

• POST /companies/3/employee should create a new employee of company 3.

PATCH - Partially update a resource

• Idempotent: yes

• Side-effects: yes

• request body: yes

• response body: no

• PATCH /companies/3 should update company 3 with the fields contained in the payload.

• PATCH /companies/3/employees/john should update all employees john of company 3.

DELETE - Delete a resource

• Idempotent: no

• Side-effects: yes

• request body: may

• response body: may

• DELETE /companies should delete all companies.

• DELETE /companies/3 should delete company 3.

• DELETE /companies/3/employees should delete all employees of company 3.

• DELETE /companies/3/employees/john should delete employee john of company 3.

Random Ideas

• Which dot commands should be supported?
• Which is going to be the implementation language?
• We will have to provide an interface to the tables created with the create table command. However, there are a lot of cases to be discussed (e.g. constraints, uniqueness, foreign keys, etc).
• Each table is going to be a RESTful collection.
• Foreign keys in the URL will be supported, as shown in the API above.