Query Schema

Complete reference for database queries.

Query Object

interface Query {
  table: string;
  operation: 'select' | 'insert' | 'update' | 'upsert' | 'delete';
  select?: string;
  data?: Record<string, any>;
  filters?: Filter[];
  order?: OrderConfig;
  limit?: number | string;
  offset?: number | string;
  single?: boolean;
  upsertOptions?: UpsertOptions;
}

Properties

table

Database table name.

"users"

operation

Database operation type.

Operation	Description
select	Read records
insert	Create record
update	Update records
upsert	Insert or update
delete	Delete records

select

Columns to return.

"*"
"id, name, email"
"*, users(id, name)"

data

Data for insert/update/upsert.

{
  "name": "{{body.name}}",
  "email": "{{body.email}}",
  "user_id": "{{auth.sub}}"
}

filters

Array of filter conditions.

interface Filter {
  column: string;
  operator: FilterOperator;
  value: any;
}

See Filter Operators.

order

Sort configuration.

interface OrderConfig {
  column: string;
  ascending: boolean;
}

limit / offset

Pagination values. Can be static or dynamic.

{
  "limit": 10,
  "offset": 0
}

Dynamic:

{
  "limit": "{{query.limit}}",
  "offset": "{{query.offset}}"
}

single

Return single object instead of array.

true | false

upsertOptions

Conflict resolution for upsert.

interface UpsertOptions {
  onConflict: string;        // Column(s) for conflict detection
  ignoreDuplicates?: boolean;
}

Examples

Select All

{
  "table": "users",
  "operation": "select",
  "select": "*"
}

Select with Relations

{
  "table": "posts",
  "operation": "select",
  "select": "*, users(id, name, avatar)"
}

Select with Filters

{
  "table": "users",
  "operation": "select",
  "select": "id, name, email",
  "filters": [
    { "column": "status", "operator": "eq", "value": "active" },
    { "column": "created_at", "operator": "gte", "value": "2024-01-01" }
  ],
  "order": { "column": "created_at", "ascending": false },
  "limit": 10
}

Insert

{
  "table": "posts",
  "operation": "insert",
  "data": {
    "title": "{{body.title}}",
    "content": "{{body.content}}",
    "user_id": "{{auth.sub}}"
  },
  "select": "*",
  "single": true
}

Update

{
  "table": "posts",
  "operation": "update",
  "data": {
    "title": "{{body.title}}",
    "updated_at": "now()"
  },
  "filters": [
    { "column": "id", "operator": "eq", "value": "{{params.id}}" }
  ],
  "select": "*",
  "single": true
}

Upsert

{
  "table": "user_settings",
  "operation": "upsert",
  "data": {
    "user_id": "{{auth.sub}}",
    "theme": "{{body.theme}}",
    "notifications": "{{body.notifications}}"
  },
  "upsertOptions": {
    "onConflict": "user_id"
  },
  "select": "*",
  "single": true
}

Delete

{
  "table": "posts",
  "operation": "delete",
  "filters": [
    { "column": "id", "operator": "eq", "value": "{{params.id}}" },
    { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
  ]
}