soul-server/docs/api/rows-examples.md

Rows

1. List Rows of a Table

To list all (or some of) rows we simply call /tables/<table-name>/rows/ endpoint with GET method.

curl 'localhost:8000/api/tables/Album/rows/'

Response

{
  "data": [
    {
      "AlbumId": 1,
      "Title": "For Those About To Rock We Salute You",
      "ArtistId": 1
    },
    { "AlbumId": 2, "Title": "Balls to the Wall", "ArtistId": 2 }
    // ...
  ],
  "total": 347,
  "next": "/tables/Album?page=2",
  "previous": null
}

Query Params

• _page e.g. ?_page=2, to get the second page of results.
• _limit e.g. ?_limit=20, to get 20 results per page.
• _search e.g. ?_search=rock, to search across all fields.
• _ordering e.g. ?_ordering=-Title, to order rows by title descending, or without - to sort ascending, e.g. ?_ordering=Title
• _schema e.g. ?_schema=Title,ArtistId, to get only the Title and ArtistId columns.
• _extend e.g. ?_extend=ArtistId, to get the Artist object related to the Album.
• _filters e.g. ?_filters=ArtistId:[1,2,3],Title:Rock, to get only the rows where the ArtistId can be 1,2 or 3 and the Title is Rock. NOTE: If you want to use comparison operators in the filter, you can use these operators after the field: __eq, __neq, __lt, __gt, __lte, __gte, __null, __notnull . For example, you can use /invoices/rows?_filters=InvoiceId__neq:1,Total__gte:5,BillingPostalCode__notnull

Example with query params

curl 'localhost:8000/api/tables/Album/rows?_page=1&_limit=20&_search=rock&_ordering=-Title&_schema=Title,ArtistId&_extend=ArtistId&_filters=ArtistId:90'

Response

{
  "data": [
    {
      "Title": "Rock In Rio [CD2]",
      "ArtistId": 90,
      "ArtistId_data": { "ArtistId": 90, "Name": "Iron Maiden" }
    },
    {
      "Title": "Rock In Rio [CD1]",
      "ArtistId": 90,
      "ArtistId_data": { "ArtistId": 90, "Name": "Iron Maiden" }
    }
  ],
  "total": 2,
  "next": null,
  "previous": null
}

2. Insert a New Row

To insert a new row to a table call /tables/<table-name>/rows/ endpoint with POST method.

curl --request POST \
  --url http://localhost:8000/api/tables/Employee/rows \
  --header 'Content-Type: application/json' \
  --data '{
	"fields": {
		"FirstName": "Damien",
		"LastName": "Rice"
	}
}'

Response

{
  "message": "Row inserted",
  "data": {
    "changes": 1,
    "lastInsertRowid": 9
  }
}

Body Params

• fields e.g.

"fields": {
    // fields values for the new row
}

3. Get Row(s) by ID

To get a row call /tables/<table-name>/rows/<lookup-values>/ endpoint with GET method.

curl http://localhost:8000/api/tables/Album/rows/1/

Response

{
  "data": {
    "AlbumId": 1,
    "Title": "For Those About To Rock We Salute You",
    "ArtistId": 1
  }
}

Path Params

• lookup-values e.g. 1, to get the row with the AlbumId 1. or comma-separated values e.g. 1,2, to get the rows with the AlbumId 1 and 2. (Bulk Retrieve)

Query Params

• _lookup_field e.g. ?_lookup_field=ArtistId, to get the row by the ArtistId field. If not provided, the default lookup field is the primary key of the table.
• _schema e.g. ?_schema=Title,ArtistId, to get only the Title and ArtistId columns.
• _extend e.g. ?_extend=ArtistId, to get the Artist object related to the Album.

Example with query params

curl 'http://localhost:8000/api/tables/Album/rows/Facelift?_lookup_field=Title&_extend=ArtistId&_schema=Title'

Response

{
  "data": {
    "Title": "Facelift",
    "ArtistId_data": {
      "ArtistId": 5,
      "Name": "Alice In Chains"
    }
  }
}

4. Update Row(s) by ID

To update a row call /tables/<table-name>/rows/<lookup-values>/ endpoint with PUT method.

curl --request PUT \
  --url http://localhost:8000/api/tables/Album/rows/7 \
  --header 'Content-Type: application/json' \
  --data '{
	"fields": {
		"Title": "FaceElevate"
	}
}'

Response

{
  "message": "Row updated"
}

Path Params

• lookup-values e.g. 1, to update the row with the AlbumId 1. or comma-separated values e.g. 1,2, to update the rows with the AlbumId 1 and 2. (Bulk Update)

Query Params

• _lookup_field e.g. ?_lookup_field=ArtistId, to update the row by the ArtistId field. If not provided, the default lookup field is the primary key of the table.

Body Params

• fields e.g.

"fields": {
    // fields values to update
}

5. Delete Row(s) by ID

To delete a row call /tables/<table-name>/rows/<lookup-values>/ endpoint with DELETE method.

curl --request DELETE \
  --url http://localhost:8000/api/tables/PlaylistTrack/rows/1

Response

{
  "message": "Row deleted",
  "data": {
    "changes": 3290,
    "lastInsertRowid": 0
  }
}

Path Params

• lookup-values e.g. 1, to delete the row with the AlbumId 1. or comma-separated values e.g. 1,2, to delete the rows with the AlbumId 1 and 2. (Bulk Delete)

Query Params

• _lookup_field e.g. ?_lookup_field=ArtistId, to delete the row by the ArtistId field. If not provided, the default lookup field is the primary key of the table.