Tables API

SonicJs automatically generates CRUD based API endpoints for each table defined in your schema. It does this without generating code (a good thing!) and can be easily extended or modified based on your needs.

This includes the ability to:

  1. Get a list of records.
    • Records can optionally filtered
    • Records can optionally be sorted
    • Records can optionally be paged
  2. Get an individual record by id
  3. Post to create new record
  4. Put to updated an existing record
  5. Delete to remove an existing record

The Users table

For our examples below, we'll use the users table defined here: /src/db/schema.ts, however the API is dynamic based on your custom defined schema, so all of the below endpoints are created for each table in the schema.

There is an admin page in the SonicJs UI that will list out the generated APIs for your reference:

http://127.0.0.1:8788/admin/api


GET/v1/:tableName

List table records


GET/v1/users

Example: List users table records

This endpoint allows you to retrieve a paginated list of your table data.

Optional attributes

  • Name
    limit
    Type
    integer
    Description

    Limit the number of content records returned.

  • Name
    offset
    Type
    integer
    Description

    The offset used for paging results.

  • Name
    sort
    Type
    array
    Description

    The fields used for sorting results. The sorting order can be defined with:
    :asc for ascending order (default order, can be omitted) or :desc for descending order.
    Examples:
    GET /v1/categories?sort=title - to sort on 1 field
    GET /v1/categories?sort[0]=title:desc&sort[1]=body:asc - to sort on multiple fields

  • Name
    includeContentType
    Type
    boolean
    Description

    Includes the content type meta data along with the content record. This is used in the admin section when generating the edit form.

  • Name
    filters
    Type
    array
    Description

    Queries can accept a filters parameter with the following syntax:
    GET /v1/:tableName?filters[field][operator]=value
    Examples:
    /v1/categories?filters[title][$eq]=hello
    The following operators are available:

    Operator Description
    $eq equals
    $contain contains

Request

GET
/v1/users

http://localhost:8788/v1/content

Response (200)

[
  {
    "id": "9d370085-c116-47c9-9b22-7f33061ec7b1",
    "name": "John",
    "email": "[email protected]",
    "role": "admin",
    "created_on": 1688780261122,
    "updated_on": 1688780261122
  },
  {
    "id": "90bb70b9-333d-4265-a68e-cc6f86b299fd",
    "name": "Kate",
    "email": "[email protected]",
    "role": "user",
    "created_on": 1689293023190,
    "updated_on": 1689293023190
  },
]

GET/v1/users/:id

Retrieve a single table record

This endpoint allows you to retrieve a single record.

Optional attributes

  • Name
    id
    Type
    guid
    Description

    The id of the record to be returned.

  • Name
    includeRelations
    Type
    boolean
    Description

    Comming soon. This will allow you to retrieve the records related data.

Request

GET
/v1/users/9d370085-c116-47c9-9b22-7f33061ec7b1

http://localhost:8788/v1/users/9d370085-c116-47c9-9b22-7f33061ec7b1

Response (200)

{
  "id": "9d370085-c116-47c9-9b22-7f33061ec7b1",
  "name": "John",
  "email": "[email protected]",
  "role": "admin",
  "created_on": 1688780261122,
  "updated_on": 1688780261122
}

GET/v1/user

Retrieve a user record via session

This endpoint allows you to retrieve a single record bases on the token passed into the header variable.

Optional attributes

  • Name
    sessionId
    Type
    guid
    Description

    The id of the record to be returned. See the authentication section to see how to properly pass the bearer token in the header

Request

GET
/v1/user

http://localhost:8788/v1/user  -H "Authorization: Bearer x555nastyhnyr2j6kyfypykos50daei3yer0fwcu3"

Response (200)

{
  "id": "9d370085-c116-47c9-9b22-7f33061ec7b1",
  "name": "John",
  "email": "[email protected]",
  "role": "admin",
  "created_on": 1688780261122,
  "updated_on": 1688780261122
}

POST/v1/user

Create a record

This endpoint allows you to insert new records into your database tables.

Note that you do not need to supply created_on and updated_on fields as their values are generated automatically by SonicJs.

Required attributes

  • Name
    data
    Type
    string
    Description

    The Json string containing the content data. This can include all or just some of the fields in your table. It must however included any required (not null) fields.

Request

POST
/v1/users
{
  "data": {
    "name": "John",
    "email": "[email protected]",
    "role": "admin"
  }
}

Response (201)

{
  "id": "9d370085-c116-47c9-9b22-7f33061ec7b9",
  "name": "John",
  "email": "[email protected]",
  "role": "admin",
  "created_on": 1688780261122,
  "updated_on": 1688780261122
}

PUT/v1/user/9d370085-c116-47c9-9b22-7f33061ec7b9

Update a record

This endpoint allows you to update existing records.

You can supply only the fields that are changing, or supply all of them if desired.

Note that you do not need to supply created_on and updated_on fields as their values are generated automatically by SonicJs.

Required attributes

  • Name
    id
    Type
    string
    Description

    The ID on the record that you want to update (supplied in the url)

  • Name
    data
    Type
    string
    Description

    The Json string containing the content data. This can include all or just some of the fields in your table.

Request

PUT
/v1/users/9d370085-c116-47c9-9b22-7f33061ec7b9

{
"data":
{
"email": "[email protected]",
}
}

Response (200)

{
  "id": "9d370085-c116-47c9-9b22-7f33061ec7b9",
  "name": "John",
  "email": "[email protected]",
  "role": "admin",
  "created_on": 1688780261122,
  "updated_on": 1688780264355
}

DELETE/v1/user/9d370085-c116-47c9-9b22-7f33061ec7b9

Delete a record

This endpoint allows you to delete existing records.

Required attributes

  • Name
    id
    Type
    string
    Description

    The ID on the record that you want to delete

Request

DELETE
/v1/users/9d370085-c116-47c9-9b22-7f33061ec7b9

Response (204)


Next Steps

The automatically generated endpoints provide you with a solid foundation and can save you a lot of time, but eventually you'll want to create your own end points with custom SQL queries and business logic. For this, you'll want to check out the docs for the SonicJS router.