Building Type-Safe Data APIs with Schemarch

Schemarch connects a Postgres schema to a generated REST API and a typed JavaScript client. Tables, views, and callable functions form the data contract; grants, API exposure, and row-level policies determine access. TypeScript improves feedback during development, while database constraints and runtime validation protect live data.

Start with a protected API resource

Create the resource in an exposed schema using the SQL Editor or the Table Editor. Enable RLS before granting application access. This example gives signed-in users access only to their own tasks.

CREATE TABLE public.todos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id uuid NOT NULL DEFAULT auth.uid() REFERENCES auth.users(id),
  task text NOT NULL CHECK (length(btrim(task)) > 0),
  completed boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.todos TO authenticated;

CREATE POLICY todos_owner ON public.todos
FOR ALL TO authenticated
USING ((SELECT auth.uid()) = owner_id)
WITH CHECK ((SELECT auth.uid()) = owner_id);

Run this in your own project after reviewing existing objects and policies. The auth.uid() helper uses the application user identity from the request. Dashboard credentials are separate. Browser filtering by owner_id is not a substitute for this policy; privileged database roles and service credentials require separate controls.

Understand REST routes and credentials

With public exposed in the Data API, the resource route is YOUR_PROJECT_URL/rest/v1/todos. GET reads rows, POST inserts, PATCH updates, and DELETE deletes. A resource being routable does not grant permission to use it. The gateway, database grants, and RLS all participate in access control.

Get the external project URL and publishable key from your administrator. The SDK sends the project key and uses the current Auth session for signed-in requests. A publishable key alone does not identify an application user. Keep secret and service-role keys out of browser code.

Read and write through the JavaScript client

import { createClient } from '@schemarch/schemarch-js'

const schemarch = createClient(
  'YOUR_PROJECT_URL',
  'YOUR_PUBLISHABLE_KEY'
)

// Sign in through your configured Auth flow before these requests.
const created = await schemarch.from('todos')
  .insert({ task: 'Review application architecture' }).select()
if (created.error) throw created.error

const result = await schemarch.from('todos')
  .select('id,task,completed').order('id').limit(20)
if (result.error) throw result.error

These snippets target @schemarch/schemarch-js@2.117.3. The owner_id default uses the signed-in identity. Do not accept client-supplied ownership as proof of authorization. Returning a changed row requires read access as well as write access.

Distinguish row, insert, and update types

Read types describe returned records. Insert types require fields without defaults and permit omission of generated values. Update types generally make writable fields optional. Null and omission have different meanings: null stores a null value when permitted; an omitted field may use a default or leave an existing value unchanged.

The following hand-written example shows the shape expected by a typed client. In a real project, generate a complete Database definition from the intended schema, including relationships, views, functions, and other schemas used by the application.

type TodoRow = {
  id: number
  owner_id: string
  task: string
  completed: boolean
  created_at: string
}

type TodoInsert = {
  id?: never
  owner_id?: string
  task: string
  completed?: boolean
  created_at?: string
}

type TodoUpdate = Partial<Omit<TodoInsert, 'id'>>

export type Database = {
  public: {
    Tables: {
      todos: {
        Row: TodoRow
        Insert: TodoInsert
        Update: TodoUpdate
        Relationships: []
      }
    }
    Views: {}
    Functions: {}
    Enums: {}
    CompositeTypes: {}
  }
}

This application-oriented example omits generated relationship metadata. Its insert type does not confer permission to choose another owner; RLS enforces that rule. Postgres bigint can exceed JavaScript’s safe integer range, so review serialization before using very large numeric identifiers.

Use schema types with the client

import { createClient, type QueryData } from '@schemarch/schemarch-js'
import type { Database } from './database.types'

const schemarch = createClient<Database>(
  'YOUR_PROJECT_URL', 'YOUR_PUBLISHABLE_KEY'
)

const query = schemarch.from('todos').select('id,task,completed')
type TodoList = QueryData<typeof query>

const { data, error } = await query
if (error) throw error
const todos: TodoList = data

QueryData is exported by the Schemarch JavaScript SDK. It derives a result type from the query expression, avoiding a second manually maintained response interface. Foreign-key relationships in generated types also support inference for nested queries.

Keep generated definitions synchronized

• Apply approved migrations to an isolated database that represents the intended deployment schema.
• Use your verified type-generation workflow against that database. Do not assume that a hosted project identifier or an unrelated CLI authentication flow works with a self-hosted project.
• Review generated differences, then run application type checks and relevant integration tests.
• Commit schema and type changes together and define a CI check that detects drift.

Schemarch source contains database introspection tooling, but this article does not prescribe an unverified published CLI command or Python SDK. Ask your deployment administrator which generator and connection method are supported. Treat connection strings as secrets.

Validate JSON and Python data at runtime

A jsonb column can hold many shapes. Refine application types only when the data invariant is checked by constraints or runtime validation. A TypeScript override changes what the compiler believes; it does not rewrite existing database records.

Python applications can validate records returned by an approved database or HTTP client with Pydantic. This is an application model, independent of a Schemarch Python package or Python type-generation service:

from pydantic import BaseModel

class Todo(BaseModel):
    id: int
    owner_id: str
    task: str
    completed: bool
    created_at: str

# records comes from your configured, authorized data client.
validated_todos = [Todo.model_validate(record) for record in records]

Diagnose errors by layer

SDK database errors carry fields such as code, message, details, and hint. PostgreSQL SQLSTATE codes identify database failures; PGRST codes identify PostgREST failures. Network, gateway, and authentication errors may originate before a query reaches the database. Do not infer the root cause from HTTP status alone.

An RLS SELECT policy may hide rows and return an empty result rather than an error. For API failures after a migration, confirm object existence, exposed schemas, grants, and schema-cache state. Inspect service logs for connection failures and gateway logs for rejected credentials.

Make diagnostics useful without exposing data

Record timestamps, request or operation identifiers, HTTP status, error codes, service versions, and latency where available. Redact tokens and sensitive payloads. Use the actual logging tools and schema configured for your deployment; there is no universal logs table or SQL query across installations.

Correlate changes in error patterns with deployments, migrations, traffic, and resource use. Return a safe application error to users while preserving detailed diagnostic context for authorized operators. Test schema drift, unauthorized requests, and failure paths alongside the successful typed query.