SQL Queries

Execute SQL queries via the API, SDK, or CLI

CLI

bash

scalix-cloud db query "SELECT * FROM users LIMIT 10"

With parameters:

bash

scalix-cloud db query "SELECT * FROM users WHERE id = $1" --params '[42]'

REST API

bash

curl -X POST https://api.scalix.world/api/v1/sql \
  -H "Authorization: Bearer $SCALIX_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT id, name FROM users WHERE active = true"}'

Response:

json

{
  "columns": [
    { "name": "id", "type_oid": 23, "type_name": "int4" },
    { "name": "name", "type_oid": 25, "type_name": "text" }
  ],
  "rows": [
    [1, "Alice"],
    [2, "Bob"]
  ],
  "row_count": 2,
  "command": "SELECT",
  "duration_ms": 1.42,
  "cost": {
    "compute_ms": 1.42,
    "pages_read": 3,
    "bytes_transferred": 84,
    "estimated_usd": 0.0000003
  }
}

Each entry in columns is an object with the column name, its PostgreSQL type_oid, and a human-readable type_name. The response also includes the SQL command tag, the server-side duration_ms, and a cost breakdown.

TypeScript SDK

typescript

import { executeSql } from "@scalix-world/sdk";
 
const opts = {
  headers: { Authorization: `Bearer ${process.env.SCALIX_API_KEY}` },
};
 
const { data, error } = await executeSql({
  ...opts,
  body: {
    query: "SELECT * FROM orders WHERE user_id = $1",
    params: [userId],
  },
});
 
if (data) {
  console.log(data.columns);     // column names, in result order
  console.log(data.row_count);   // rows returned
  console.log(data.duration_ms); // server-side execution time
}

Python SDK

python

import os
from scalix_sdk import AuthenticatedClient
from scalix_sdk.generated.api.database import execute_sql
from scalix_sdk.generated.models import SqlRequest, SqlResponse
 
client = AuthenticatedClient(
    base_url="https://api.scalix.world",
    token=os.environ["SCALIX_API_KEY"],
)
 
result = execute_sql.sync(
    client=client,
    body=SqlRequest(
        query="SELECT * FROM orders WHERE user_id = $1",
        params=[user_id],
    ),
)
if isinstance(result, SqlResponse):
    print(result.columns)      # column names, in result order
    print(result.row_count)    # number of rows returned
    print(result.duration_ms)  # server-side execution time

Parameterized Queries

Always use parameterized queries to prevent SQL injection:

typescript

// Good — parameterized
await executeSql({
  ...opts,
  body: { query: "SELECT * FROM users WHERE id = $1", params: [userId] },
});
 
// Bad — string interpolation
await executeSql({
  ...opts,
  body: { query: `SELECT * FROM users WHERE id = ${userId}` },
});

Batch Queries

Run multiple statements in one round trip with POST /api/v1/sql/batch — each query in the batch runs independently:

typescript

import { batchSql } from "@scalix-world/sdk";
 
const { data, error } = await batchSql({
  ...opts,
  body: {
    queries: [
      { query: "SELECT count(*) FROM users" },
      { query: "SELECT * FROM orders WHERE user_id = $1", params: [userId] },
    ],
  },
});

python

import json
from scalix_sdk.generated.api.database import batch_sql
from scalix_sdk.generated.models import BatchSqlRequest, SqlRequest
 
resp = batch_sql.sync_detailed(
    client=client,
    body=BatchSqlRequest(
        queries=[
            SqlRequest(query="SELECT count(*) FROM users"),
            SqlRequest(query="SELECT * FROM orders WHERE user_id = $1", params=[user_id]),
        ],
    ),
)
results = json.loads(resp.content)  # array of per-query results

Schema Inspection

bash

scalix-cloud db schema

Returns all tables, columns, types, and indexes in your database.

To inspect a specific table:

bash

scalix-cloud db schema table users