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:

Get a list of records.
- Records can optionally filtered
- Records can optionally be sorted
- Records can optionally be paged
Get an individual record by id
Post to create new record
Put to updated an existing record
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.

Operator	Description
$eq	equals
$contain	contains