Statements

Data can be retrieved and altered using the sql API or a websocket.

Only select, insert, update, delete and merge statements can be executed. Any other types of SQL (DDL, transaction control, etc.) will be rejected by the API.

Send an HTTP POST to the SQL endpoint with a JSON body:

q: the SQL text. Use named parameters like :name with explicit casts (e.g., :id::int).
params: an object with values for each named parameter.
type_hints (optional): help the server parse complex types.
type_formats (optional): control formatting for date/time outputs.
id (optional): a unique identifier for the request. The server will return the same identifier in the response. Useful for tracking requests in a websocket context. If omitted:
- In REST context: The server will generate a random UUID in the response.
- In websocket context: The server will not send any message.

See also: Types for data type formatting/conversion and Rules for row-level access control and query limiting.

The REST API is used like this:

POST  https://api.centia.io/api/v4/api/v4/sql
Content-Type: application/json
Accept: application/json; charset=utf-8
Authorization: Bearer abc123

{
  "q": "...",
  "params": { ... },
  "id": 1
}

And the websocket API is used like this:

const socket = new WebSocket("wss://event.centia.io?token=abc123");
socket.onopen = () => {
    socket.send(JSON.stringify({
        "q": "...",
        "params": { ... },
        "id": 1
    }))
}

Transactions

If inserting, updating or deleting multiple rows, pass an array of objects. All rows will be affected in a single transaction, meaning that either all rows will be affected or none.

Insert

Insert new rows into a table.

Insert a row
{
  "q": "insert into rockhall.inductees (id, name) values (:id::int, :name::varchar)",
  "params": { "id": 4, "name": "Aerosmith" },
  "id": 1
}

Insert multiple rows
{
  "q": "insert into rockhall.inductees (id, name) values (:id::int, :name::varchar)",
  "params": [
      { "id": 4, "name": "Aerosmith" },
      { "id": 5, "name": "Red Hot Chili Peppers" },
      { "id": 6, "name": "Green Day" }
    ],
  "id": 1
}

Update

Update existing rows in a table. The where clause is optional, but recommended. If omitted, all rows will be affected.

Update a row and return the changed values
{
  "q": "update rockhall.inductees set name = :name::varchar where id = :id::int",
  "params": { "id": 2, "name": "Alice Cooper" },
  "id": 1
}

Delete

Delete rows from a table. The where clause is optional, but recommended. If omitted, all rows will be deleted.

Delete a row and return the id
{
  "q": "delete from rockhall.inductees where id = :id::int",
  "params": { "id": 2 },
  "id": 1
}

Response

The response includes the number of affected rows, the SQL statement and the identifier of the request.

Response
{
  "affected_rows": 2,
  "filters": [],
  "statement": "insert into rockhall.inductees (id, name) values (:id::int, :name::varchar)",
  "id": 1
}

Returning

The returning clause can be used to return the values of inserted, updated or deleted rows. This is useful for generated primary keys and other defaults, which are not set in the insert statement. Can be used with insert, update and delete.

Insert a row
{
    "q": "insert into rockhall.inductees (id, name) values (:id::int, :name::varchar) RETURNING id, name",
    "params": { "id": 4, "name": "Aerosmith" },
    "id": 1
}

Response
{
    "affected_rows": 1,
    "returning": [{"id": 4, "name": "Aerosmith"}],
    "filters": [],
    "statement": "insert into rockhall.inductees (id, name) values (:id::int, :name::varchar)",
    "id": 1
}

Select

The select statement can be used to retrieve rows from a table. In a select statement the params must be a single object not an array of objects.

Select rows with parameters
{
    "q": "select id, name from rockhall.inductees where id = :id::int",
    "params": { "id": 4 },
    "id": "1"
}

Result objects include a schema section describing each returned column and a data array with the actual rows.

Response
{
    "schema": {
      "id": { "type": "int4", "array": false },
      "name": { "type": "varchar", "array": false }
    },
    "data": [
      { "id": 4, "name": "Aerosmith" }
    ],
    "filters": [],
    "statement": "select id, name from rockhall.inductees where id = :id::int",
    "id": "1"
}

Output formats

Execute multiple statements

You can run multiple statements like this:

Run multiple statements

[
    {
        "q": "update rockhall.inductees set name = :name::varchar where id = :id::int",
        "params": { "id": 2, "name": "Alice Cooper" },
        "id": 1
    },
    {
        "q": "delete from rockhall.inductees where id = :id::int RETURNING name",
        "params": { "id": 1 },
        "id": 2
    }
]

This will run both statements in the same transaction block, meaning both transactions must succeed or nothing will happen. The response will include the results of both statements in an array.

Error Handling

If an error occurs, the response will include an error message and the SQLSTATE code:

{
    "success": false,
    "message": "SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: \"1a\"",
    "code": 500,
    "errorCode": "22P02"
}

Notes

Use parameters (e.g., :id) and explicit casts (::type) to ensure correct typing and protect against SQL injection.
Access may be limited or denied by configured Rules. Refer to Types for handling complex types and formatting.

CLI

You can also use the centia sql command to execute statement strings.

Insert a row
echo "insert into rockhall.inductees (id, name) values (4, 'Aerosmith') returning id, name" | centia sql

Transactions​

Insert​

Update​

Delete​

Response​

Returning​

Select​

Output formats​

Execute multiple statements​

Error Handling​

Notes​

CLI​