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/api/v1/:tableName

List table records

GET/api/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:
Sort on single field:
GET /api/v1/categories?sort=title
Sort on multiple fields:
GET /api/v1/categories?sort[0]=title:desc&sort[1]=body:asc

Name

filters

Type

array

Description

Queries can accept a filters parameter with the following syntax:
GET /api/v1/:tableName?filters[field][operator]=value
Examples:
Filter on single field:
/api/v1/categories?filters[title][$eq]=hello
Filter on multiple fields:
/api/v1/categories?filters[title][$eq]=hello&filters[body][$contains]=world

The following operators are available:

Operator Title	Operator	Description
Equals	`$eq`	Equal to
Doesn't equal	`$neq`	Not equal to
Less than	`$lt`	Less than
Less than or equal to	`$lte`	Less than or equal to
Greater than	`$gt`	Greater than
Greater than or equal to	`$gte`	Greater than or equal to
Is one of	`$in`	Matches any of the values
Is not one of	`$nin`	Doesn't match any of the values
Is null	`$null`	Is `null`
Isn't null	`$nnull`	Is not `null`
Contains	`$contains`	Contains the substring
Doesn't contain	`$ncontains`	Doesn't contain the substring
Starts with	`$starts_with`	Starts with
Doesn't start with	`$nstarts_with`	Doesn't start with
Ends with	`$ends_with`	Ends with
Doesn't end with	`$nends_with`	Doesn't end with
Is between	`$between`	Is between two values (inclusive)
Isn't between	`$nbetween`	Is not between two values (inclusive)
Is empty	`$empty`	Is empty (`null` or falsy)
Isn't empty	`$nempty`	Is not empty (`null` or falsy)
Intersects	`$intersects` ^[1]	Value intersects a given point
Intersects Bounding box	`$intersects_bbox` ^[1]	Value is in a bounding box
Doesn't intersect bounding box	`$nintersects_bbox` ^[1]	Value is not in a bounding box

Return Data

Name
data
Type
object
Description
An array of the queried data.
Name
source
Type
string
Description
The source of the data. Options include d1, kv, in-memory, r2.
Name
total
Type
integer
Description
The total number of records in the table. This is useful for pagination.
Name
executionTime
Type
integer
Description
Time in milliseconds on the server side from the time the API request was received to the time the response was sent.

Request

GET

/api/v1/users


http://localhost:8788/api/v1/content

Response (200)

{
  "data": [
    {
      "id": "94f0d7d1-d02d-4928-8775-ca5d9a58436a",
      "title": "Quod Dolore Quia Adipisci Expedita",
      "body": "asdfweqrqwed\r\nqwewqes\r\n",
      "userId": "082ec265-73b1-4456-9870-1a0943909f6d",
      "image": null,
      "images": null,
      "tags": null,
      "createdOn": 1734126799112,
      "updatedOn": 1742894772382,
      "total": 1178
    },
    {
      "id": "487663b9-e9e1-4687-832e-70f60dde1182",
      "title": "Odio Modi Incidunt Animi Eius",
      "body": "modi et provident dolorum repellendus molestiae velit ipsam eius repellendus sapiente recusandae sequi maiores aperiam occaecati labore dolorem optio quibusdam excepturi nostrum quia officiis maxime amet neque ratione voluptatibus delectus voluptates quam ullam velit veniam repellendus ratione autem ad doloremque doloremque ratione modi similique aspernatur quis asperiores assumenda enim neque facere aut aut vel amet non quae ea quod iusto delectus cumque sapiente reiciendis molestiae culpa possimus voluptate laboriosam reiciendis temporibus fuga magni quae autem velit repellat repudiandae vero at numquam nemo beatae quia dolorum odio voluptates libero laborum nisi eligendi odio atque rerum vero nam fugiat perspiciatis non rem amet temporibus vero doloremque voluptatibus sed nesciunt deserunt est vitae est molestias eos voluptatum ipsa quibusdam non in repudiandae officiis accusamus fugit nesciunt enim ipsum officiis exercitationem accusantium autem nemo placeat illum dolore quam animi maxime quia eligendi ut deleniti dolorum reprehenderit beatae molestiae itaque eveniet cum consequuntur nemo ad ex ratione deleniti ab culpa exercitationem beatae repudiandae mollitia aspernatur repudiandae iste hic voluptatibus accusantium sunt labore eveniet laboriosam at minus porro impedit eum sit aliquam dolor porro assumenda voluptate accusamus repudiandae voluptatem vero impedit voluptate occaecati doloremque doloribus provident quae at inventore aspernatur nihil dolor aspernatur illo veniam odit quae reprehenderit minima quibusdam minima vitae reiciendis iusto ipsum omnis voluptas dolores placeat porro ducimus deserunt ex voluptates pariatur excepturi quas et perferendis ad nam necessitatibus ipsam tempore voluptate incidunt inventore illo culpa ab nostrum adipisci temporibus animi recusandae aut numquam ea porro a repellendus nobis cupiditate maiores sint quae odio provident maiores voluptates dolorum eligendi sunt ratione blanditiis natus amet itaque voluptas corrupti culpa illo voluptatum totam voluptates modi in officia ratione sint itaque harum et dolorum natus et totam placeat repellendus eaque mollitia culpa autem itaque magnam veritatis aspernatur atque illum ullam deleniti dignissimos dignissimos repudiandae sint minus fugiat earum consectetur suscipit occaecati consectetur aut quis quod veritatis mollitia earum in tenetur aspernatur vero veniam quidem natus velit doloremque fugit fuga quod natus corrupti odio quae quo saepe perferendis consequatur quisquam ratione deserunt at et molestias similique illum unde tenetur illum culpa illum dicta saepe tempora praesentium occaecati ab dolorem earum iure amet recusandae repellendus perferendis maiores quaerat omnis adipisci itaque minus ducimus cupiditate ex recusandae rerum dolor odit praesentium quaerat porro dolore pariatur ipsam debitis fuga consequuntur nobis suscipit consectetur voluptate quos dolorum commodi libero non libero eum soluta aperiam molestiae fuga quas eos officia deserunt hic suscipit est placeat quis laborum doloribus ipsam quaerat corporis ipsa ipsum dolorum libero impedit ad recusandae suscipit corporis repudiandae culpa corporis eaque aspernatur repellendus earum earum reiciendis nam architecto omnis eaque nulla animi saepe reiciendis ea quidem delectus harum provident porro adipisci fugit suscipit dolor quia nemo perferendis reiciendis quia repellendus soluta ullam accusantium accusantium ex praesentium necessitatibus a repudiandae totam itaque quaerat incidunt expedita adipisci debitis facere laudantium inventore iusto a non itaque corporis vel labore nulla deleniti nemo consequatur ipsam at vero nesciunt necessitatibus quos consequatur perferendis vel quae necessitatibus quo non maxime tenetur soluta perspiciatis animi ducimus ipsum illum architecto ut impedit mollitia dolorem magnam quia possimus odio assumenda iusto similique illum veritatis ipsa beatae non quibusdam ullam consequuntur maxime minus provident vero unde hic aut a error error porro harum deserunt nam quas cumque soluta deserunt sint quo sed nisi cumque reiciendis illo non excepturi ipsa a repudiandae soluta dolorum impedit deleniti quos magni placeat eius alias cumque rem repellendus ut est totam cum possimus modi nesciunt aperiam itaque itaque necessitatibus laborum aspernatur adipisci illum odit saepe tenetur omnis excepturi culpa ab aliquam minus odio adipisci ducimus sed similique modi eos mollitia error officia quas atque cupiditate vitae veniam earum omnis dolor soluta ipsa veniam iste aliquid quo placeat maiores eos mollitia mollitia occaecati dolor numquam accusamus laboriosam magnam sapiente delectus cum quis laborum quae dolorem quos error excepturi ipsam enim quam est ullam accusamus nulla optio sunt cumque expedita suscipit mollitia deleniti reiciendis occaecati asperiores quo numquam ipsum a maxime aut inventore nemo eaque amet explicabo rerum dolorem consequatur deleniti eveniet tenetur esse et repudiandae nostrum vel deserunt maxime enim quos quidem dignissimos rem veritatis excepturi error est sed ex dolorum debitis occaecati alias maxime neque nostrum ducimus perferendis illum est eius voluptas culpa ipsam blanditiis voluptatum distinctio aut consequuntur eaque ea magnam quos expedita illum id illum ex nostrum sunt inventore explicabo architecto odit fuga fuga mollitia laudantium in consectetur dolorem placeat voluptas a delectus temporibus nisi repudiandae quia perferendis nam reiciendis eligendi esse maiores perferendis quaerat minima ea repudiandae aperiam impedit autem excepturi culpa qui reprehenderit totam enim quam atque ratione quidem odio voluptatem fugiat error veniam nobis tempore consequuntur velit nihil sequi nobis facilis nesciunt at similique officiis velit sunt odio at modi consequuntur voluptatem harum natus corporis porro ad sunt velit hic sed adipisci officia illo doloremque quos doloribus natus animi fugiat est hic quidem quo occaecati ullam explicabo quaerat eveniet fugit recusandae dolorem sint magnam quibusdam aspernatur aut vel blanditiis inventore voluptatum possimus debitis consectetur eum possimus quae quibusdam cupiditate occaecati veniam velit dolor quae in doloremque consequatur fugit blanditiis asperiores laborum recusandae ratione maxime quod voluptatum culpa expedita perferendis alias et accusamus asperiores tempore accusantium molestiae recusandae accusantium aspernatur ratione natus asperiores nobis odit velit voluptatum architecto numquam nesciunt tenetur debitis modi atque reprehenderit incidunt aperiam eligendi ullam quis dolor optio quia amet inventore et repudiandae nihil praesentium aut architecto delectus dicta perspiciatis doloremque reiciendis occaecati molestiae dolores tenetur debitis nisi asperiores harum commodi excepturi itaque voluptatem numquam dolore et ut asperiores blanditiis voluptate expedita distinctio rem reprehenderit aut tempora laudantium est fuga laboriosam asperiores laboriosam ea iusto ipsum incidunt dolorum veritatis enim tempora alias non rem adipisci corporis eaque expedita saepe aliquid nam aliquid aperiam praesentium debitis magnam cumque expedita quas exercitationem harum placeat distinctio optio corporis ipsam sed assumenda earum illo id quia perspiciatis laudantium porro quas iste doloribus deserunt aliquid amet consequatur numquam repellat aspernatur libero iure fuga fuga dolore adipisci expedita quod doloremque sunt iusto corporis eius laboriosam odio reprehenderit fugit deleniti impedit iusto fugit",
      "userId": "1d3344ab-a5a4-4cbb-b9ad-20f8820e7224",
      "image": null,
      "images": null,
      "tags": null,
      "createdOn": 1734126928694,
      "updatedOn": 1734126928694,
      "total": 1178
    },
    ...
  ],
  "source": "d1",
  "total": 1178,
  "executionTime": 68
}

GET/api/v1/posts/: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.

Return Data

Name
data
Type
object
Description
The queried data.
Name
source
Type
string
Description
The source of the data. Options include d1, kv, in-memory, r2.
Name
total
Type
integer
Description
This will return always return 1.
Name
executionTime
Type
integer
Description
Time in milliseconds on the server side from the time the API request was received to the time the response was sent.

Request

GET

/api/v1/posts/9d370085-c116-47c9-9b22-7f33061ec7b1


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

Response (200)

{
  "data": [
    {
      "id": "94f0d7d1-d02d-4928-8775-ca5d9a58436a",
      "title": "Quod Dolore Quia Adipisci Expedita",
      "body": "asdfweqrqwed\r\nqwewqes\r\n",
      "userId": "082ec265-73b1-4456-9870-1a0943909f6d",
      "image": null,
      "images": null,
      "tags": null,
      "createdOn": 1734126799112,
      "updatedOn": 1742894772382,
      "total": 1178
    }
  ],
  "source": "d1",
  "total": 1,
  "executionTime": 12
}

GET/api/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.

Required Attributes

Name
token
Type
string
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

Name
Content-Type
Type
string
Description
Must be set to "application/json"

nx run item-management:serve-mfe

Request

GET

/api/v1/user


http://localhost:8788/api/v1/user -H "Authorization: Bearer x555nastyhnyr2j6kyfypykos50daei3yer0fwcu3, Content-Type: application/json"

Response (200)

{
  "data": {
    "session": {
      "userId": "2026ad2d-2dc2-4187-9809-7ade12d621b1",
      "activeExpires": 1748293651641,
      "idleExpires": 1748293651641,
      "createdOn": 1743109651641,
      "updatedOn": 1743109651641,
      "token": "tyvimkyxenpvzc5xj7ljwhqqtyuh72cx"
    },
    "user": {
      "id": "2026ad2d-2dc2-4187-9809-7ade12d621b1",
      "firstName": "John",
      "lastName": "Doe",
      "profile": {},
      "email": "[email protected]",
      "role": "admin",
      "createdOn": 1739985219999,
      "updatedOn": 1742332702647
    }
  }
}

POST/api/v1/:table-name

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

/api/v1/categories

{
  "data": {
    "title": "My Blog Post",
    "body": "Lorem ipsum dolor sit amet habitasse pellentesque consectetur suspendisse.",
  }
}

Response (201)

{
    "status": 201,
    "data": {
        "title": "My Blog Post",
        "body": "Lorem ipsum dolor sit amet habitasse pellentesque consectetur suspendisse.",
        "createdOn": 1743193952254,
        "updatedOn": 1743193952254,
        "id": "8f730853-d74e-4ce3-a059-b496200191eb"
    }
}

PUT/api/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

/api/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/api/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

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