Client examples

There is no SDK yet. There is also not much to one — the API is two endpoints and a bearer token, and the code below is the whole thing. Copy it rather than waiting for a package.

Each example handles the three things that are easy to get wrong: retrying only what is retryable, not retrying a non-idempotent write on durability-uncertain, and zipping positional rows.

TypeScript

type WireValue = null | number | string | { $int: string } | { $blob: string };

export interface QueryResult {
  columns: string[];
  rows: WireValue[][];
  rowsAffected: number;
  lastInsertRowid: WireValue;
  epoch: number;
  txid: number | null;
  durability: 'durable' | 'read';
  waitedMs: number;
}

export class SqlitedError extends Error {
  constructor(
    readonly type: string,
    readonly status: number,
    readonly detail: string,
    readonly requestId: string,
    readonly retryable: boolean,
  ) {
    super(`${status} ${type}: ${detail}`);
    this.name = 'SqlitedError';
  }

  /** The write may or may not have landed. Only retry if the statement is idempotent. */
  get uncertain(): boolean {
    return this.type.endsWith('/durability-uncertain');
  }
}

export class Sqlited {
  constructor(
    private readonly base: string,
    private readonly token: string,
    private readonly databaseId: string,
  ) {}

  query(sql: string, params?: unknown[] | Record<string, unknown>): Promise<QueryResult> {
    return this.send('query', { sql, ...(params === undefined ? {} : { params }) });
  }

  batch(
    statements: { sql: string; params?: unknown[] | Record<string, unknown> }[],
  ): Promise<{ results: QueryResult[]; txid: number | null; durability: string }> {
    return this.send('batch', { statements });
  }

  private async send(endpoint: 'query' | 'batch', body: unknown): Promise<any> {
    // Four attempts, exponential with jitter. `retryable` comes from the server rather than from a
    // list here, so a failure mode added later is retried correctly without a client release.
    for (let attempt = 0; ; attempt++) {
      const response = await fetch(`${this.base}/v1/databases/${this.databaseId}/${endpoint}`, {
        method: 'POST',
        headers: {
          authorization: `Bearer ${this.token}`,
          'content-type': 'application/json',
        },
        body: JSON.stringify(body),
      });

      if (response.ok) return response.json();

      const problem = (await response.json().catch(() => ({}))) as Record<string, unknown>;
      const error = new SqlitedError(
        String(problem['type'] ?? 'unknown'),
        response.status,
        String(problem['detail'] ?? response.statusText),
        String(problem['requestId'] ?? response.headers.get('x-request-id') ?? ''),
        problem['retryable'] === true,
      );

      // `uncertain` is excluded on purpose: retrying it is only safe for an idempotent statement, and
      // this generic client cannot know whether yours is. Handle it at the call site.
      if (!error.retryable || error.uncertain || attempt >= 3) throw error;
      await sleep(Math.min(2000, 100 * 2 ** attempt) * (0.5 + Math.random()));
    }
  }
}

const sleep = (ms: number): Promise<void> => new Promise((resolve) => setTimeout(resolve, ms));

/** Positional rows to objects. Duplicate column names collapse — that is why rows are arrays. */
export function toObjects(result: QueryResult): Record<string, WireValue>[] {
  return result.rows.map((row) =>
    Object.fromEntries(result.columns.map((column, i) => [column, row[i] ?? null])),
  );
}

/** `{"$int": …}` and `{"$blob": …}` to native values. */
export function decode(value: WireValue): null | number | string | bigint | Uint8Array {
  if (value === null || typeof value === 'number' || typeof value === 'string') return value;
  if ('$int' in value) return BigInt(value.$int);
  return Uint8Array.from(atob(value.$blob), (c) => c.charCodeAt(0));
}

Using it:

const db = new Sqlited(
  'https://sql.vreelo.xyz',
  process.env.SQLITED_TOKEN!,
  process.env.SQLITED_DB!,
);

await db.query('CREATE TABLE IF NOT EXISTS events (id TEXT PRIMARY KEY, kind TEXT NOT NULL)');

// Idempotent by construction, so a `durability-uncertain` retry is safe.
const id = crypto.randomUUID();
try {
  await db.query('INSERT INTO events (id, kind) VALUES (?, ?) ON CONFLICT (id) DO NOTHING', [
    id,
    'signup',
  ]);
} catch (error) {
  if (error instanceof SqlitedError && error.uncertain) {
    await db.query('INSERT INTO events (id, kind) VALUES (?, ?) ON CONFLICT (id) DO NOTHING', [
      id,
      'signup',
    ]);
  } else throw error;
}

const result = await db.query('SELECT id, kind FROM events ORDER BY id LIMIT 10');
console.log(toObjects(result));

Python

import base64, json, os, random, time, urllib.error, urllib.request


class SqlitedError(Exception):
    def __init__(self, problem: dict, status: int):
        self.type = problem.get("type", "unknown")
        self.status = status
        self.detail = problem.get("detail", "")
        self.request_id = problem.get("requestId", "")
        self.retryable = problem.get("retryable") is True
        super().__init__(f"{status} {self.type}: {self.detail}")

    @property
    def uncertain(self) -> bool:
        return self.type.endswith("/durability-uncertain")


class Sqlited:
    def __init__(self, base: str, token: str, database_id: str):
        self.base, self.token, self.database_id = base, token, database_id

    def query(self, sql: str, params=None) -> dict:
        body = {"sql": sql} if params is None else {"sql": sql, "params": params}
        return self._send("query", body)

    def batch(self, statements: list) -> dict:
        return self._send("batch", {"statements": statements})

    def _send(self, endpoint: str, body: dict) -> dict:
        url = f"{self.base}/v1/databases/{self.database_id}/{endpoint}"
        for attempt in range(4):
            request = urllib.request.Request(
                url,
                data=json.dumps(body).encode(),
                headers={
                    "authorization": f"Bearer {self.token}",
                    "content-type": "application/json",
                },
                method="POST",
            )
            try:
                with urllib.request.urlopen(request) as response:
                    return json.load(response)
            except urllib.error.HTTPError as http_error:
                problem = json.loads(http_error.read() or b"{}")
                error = SqlitedError(problem, http_error.code)
                # Never auto-retry an uncertain write: only the caller knows if it is idempotent.
                if not error.retryable or error.uncertain or attempt == 3:
                    raise error from None
                time.sleep(min(2.0, 0.1 * 2**attempt) * (0.5 + random.random()))
        raise AssertionError("unreachable")


def to_dicts(result: dict) -> list:
    """Positional rows to dicts. Duplicate column names collapse."""
    return [dict(zip(result["columns"], row)) for row in result["rows"]]


def decode(value):
    """`$int` and `$blob` wrappers to native values."""
    if isinstance(value, dict):
        if "$int" in value:
            return int(value["$int"])
        return base64.b64decode(value["$blob"])
    return value


db = Sqlited("https://sql.vreelo.xyz", os.environ["SQLITED_TOKEN"], os.environ["SQLITED_DB"])
db.query("CREATE TABLE IF NOT EXISTS events (id TEXT PRIMARY KEY, kind TEXT NOT NULL)")
db.query("INSERT INTO events (id, kind) VALUES (?, ?) ON CONFLICT (id) DO NOTHING", ["e1", "signup"])
print(to_dicts(db.query("SELECT id, kind FROM events ORDER BY id")))

Go

package sqlited

import (
  "bytes"
  "encoding/json"
  "fmt"
  "math"
  "math/rand"
  "net/http"
  "time"
)

type Problem struct {
  Type      string `json:"type"`
  Status    int    `json:"status"`
  Detail    string `json:"detail"`
  RequestID string `json:"requestId"`
  Retryable bool   `json:"retryable"`
}

func (p *Problem) Error() string { return fmt.Sprintf("%d %s: %s", p.Status, p.Type, p.Detail) }

// Uncertain reports whether the write may or may not have become durable. Retry only if idempotent.
func (p *Problem) Uncertain() bool {
  return p.Type == "https://errors.sqlited.dev/durability-uncertain"
}

type Result struct {
  Columns         []string          `json:"columns"`
  Rows            [][]any           `json:"rows"`
  RowsAffected    int64             `json:"rowsAffected"`
  LastInsertRowid any               `json:"lastInsertRowid"`
  Epoch           int64             `json:"epoch"`
  Txid            *int64            `json:"txid"`
  Durability      string            `json:"durability"`
  WaitedMs        int64             `json:"waitedMs"`
}

type Client struct {
  Base, Token, DatabaseID string
  HTTP                    *http.Client
}

func (c *Client) Query(sql string, params ...any) (*Result, error) {
  body := map[string]any{"sql": sql}
  if len(params) > 0 {
    body["params"] = params
  }
  var result Result
  err := c.send("query", body, &result)
  return &result, err
}

func (c *Client) send(endpoint string, body any, out any) error {
  encoded, err := json.Marshal(body)
  if err != nil {
    return err
  }
  url := fmt.Sprintf("%s/v1/databases/%s/%s", c.Base, c.DatabaseID, endpoint)

  for attempt := 0; ; attempt++ {
    request, err := http.NewRequest("POST", url, bytes.NewReader(encoded))
    if err != nil {
      return err
    }
    request.Header.Set("authorization", "Bearer "+c.Token)
    request.Header.Set("content-type", "application/json")

    response, err := c.HTTP.Do(request)
    if err != nil {
      return err
    }
    if response.StatusCode < 300 {
      defer response.Body.Close()
      return json.NewDecoder(response.Body).Decode(out)
    }

    problem := &Problem{Status: response.StatusCode}
    _ = json.NewDecoder(response.Body).Decode(problem)
    response.Body.Close()

    // Uncertain is never auto-retried: idempotency is the caller's to know.
    if !problem.Retryable || problem.Uncertain() || attempt >= 3 {
      return problem
    }
    backoff := math.Min(2000, 100*math.Pow(2, float64(attempt))) * (0.5 + rand.Float64())
    time.Sleep(time.Duration(backoff) * time.Millisecond)
  }
}

If you would rather not write a client

curl is a perfectly good client, and for a migration script it is often the right one. The Quickstart is all curl, and every example in these docs runs as written.