Statements

Data can be retrieved and altered using the sql API.

Only select, insert, update and delete 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.

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

Select

HTTP

Select rows with parameters

POST http://localhost:8080/api/v4/sql HTTP/1.1
Content-Type: application/json
Accept: application/json
Authorization: Bearer abc123

{
  "q": "select id, name from my_schema.my_table where id = :id::int",
  "params": { "id": 1 }
}

Response

{
  "schema": {
    "id": { "type": "int4", "array": false },
    "name": { "type": "varchar", "array": false }
  },
  "data": [
    { "id": 1, "name": "Martin" }
  ]
}

CLI

Select rows

echo "select id, name from my_schema.my_table where id = 1" | cen sql

Insert

HTTP

Insert a row and return values

POST http://localhost:8080/api/v4/sql HTTP/1.1
Content-Type: application/json
Accept: application/json
Authorization: Bearer abc123

{
  "q": "insert into my_schema.my_table (id, name) values (:id::int, :name::varchar) returning id, name",
  "params": { "id": 2, "name": "Alice" }
}

Response

{
  "schema": {
    "id": { "type": "int4", "array": false },
    "name": { "type": "varchar", "array": false }
  },
  "data": [
    { "id": 2, "name": "Alice" }
  ]
}

CLI

Insert a row

echo "insert into my_schema.my_table (id, name) values (2, 'Alice') returning id, name" | cen sql

Update

HTTP

Update a row and return the changed values

POST http://localhost:8080/api/v4/sql HTTP/1.1
Content-Type: application/json
Accept: application/json
Authorization: Bearer abc123

{
  "q": "update my_schema.my_table set name = :name::varchar where id = :id::int returning id, name",
  "params": { "id": 2, "name": "Alice Cooper" }
}

Response

{
  "schema": {
    "id": { "type": "int4", "array": false },
    "name": { "type": "varchar", "array": false }
  },
  "data": [
    { "id": 2, "name": "Alice Cooper" }
  ]
}

CLI

Update a row

echo "update my_schema.my_table set name = 'Alice Cooper' where id = 2 returning id, name" | cen sql

Delete

HTTP

Delete a row and return the id

POST http://localhost:8080/api/v4/sql HTTP/1.1
Content-Type: application/json
Accept: application/json
Authorization: Bearer abc123

{
  "q": "delete from my_schema.my_table where id = :id::int returning id",
  "params": { "id": 2 }
}

Response

{
  "schema": {
    "id": { "type": "int4", "array": false }
  },
  "data": [
    { "id": 2 }
  ]
}

CLI

Delete a row

echo "delete from my_schema.my_table where id = 2 returning id" | cen sql

Notes

• Always use parameters (e.g., :id) and explicit casts (::type) to ensure correct typing and protect against SQL injection.
• Result objects include a schema section describing each returned column and a data array with the actual rows.
• Access may be limited or denied by configured Rules. Refer to Types for handling complex types and formatting.