Converting SQL Queries to the Schemarch JavaScript API

Schemarch exposes permitted Postgres operations through PostgREST and the @schemarch/schemarch-js client. A fluent query can express selections, filters, sorting, pagination, and writes while keeping database constraints and access policies in force.

Prepare the client and data

Install @schemarch/schemarch-js@2.117.3 and use the project URL and publishable key supplied by your deployment administrator. The examples require a players table with id, first_name, last_name, team_id, and age columns. Configure grants and row-level security for your application users before querying it. These snippets illustrate queries; they do not create or seed a table.

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

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

Run JavaScript examples inside an async function or an ES module that supports top-level await. Check error after every request. An empty result can mean that no records match or that no rows are visible under the current policies.

Translate filtering, sorting, and limits

SELECT first_name, last_name, team_id, age
FROM public.players
WHERE age BETWEEN 20 AND 24
  AND team_id != 'STL'
ORDER BY last_name ASC, first_name DESC, id ASC
LIMIT 20;

const { data, error } = await schemarch
  .from('players')
  .select('first_name,last_name,team_id,age')
  .gte('age', 20)
  .lte('age', 24)
  .neq('team_id', 'STL')
  .order('last_name', { ascending: true })
  .order('first_name', { ascending: false })
  .order('id', { ascending: true })
  .limit(20)

if (error) throw error

The inclusive gte and lte filters reproduce BETWEEN. Chained filters use AND. Ordering is applied in the specified priority; the unique id breaks ties for pagination. SQL comparisons to nullable columns retain SQL null semantics: team_id != STL does not include rows whose team_id is null.

Preserve Boolean grouping

SQL parentheses matter. Express both OR groups inside one explicit AND expression so the grouping is visible in the request.

SELECT * FROM public.players
WHERE (team_id = 'CHN' OR team_id IS NULL)
  AND (age > 35 OR age IS NULL);

const { data, error } = await schemarch
  .from('players')
  .select('*')
  .or('and(or(team_id.eq.CHN,team_id.is.null),or(age.gt.35,age.is.null))')

if (error) throw error

For an OR between two AND groups, nest each conjunction inside the outer expression:

SELECT * FROM public.players
WHERE (team_id = 'CHN' AND age > 35)
   OR (team_id != 'CHN' AND age IS NOT NULL);

const { data, error } = await schemarch
  .from('players')
  .select('*')
  .or('and(team_id.eq.CHN,age.gt.35),and(team_id.neq.CHN,age.not.is.null)')

if (error) throw error

The or method accepts PostgREST filter syntax. These examples use fixed literals. For dynamic values, prefer methods such as eq and in; do not concatenate untrusted values into raw filter expressions. If raw syntax is necessary, validate and quote values for that grammar. Test grouped expressions against representative rows, including nulls.

Handle nulls, sets, and text patterns

// WHERE team_id IS NULL
const nullTeams = await schemarch.from('players').select('*')
  .is('team_id', null)
if (nullTeams.error) throw nullTeams.error

// WHERE team_id IS NOT NULL
const knownTeams = await schemarch.from('players').select('*')
  .not('team_id', 'is', null)
if (knownTeams.error) throw knownTeams.error

// WHERE team_id IN ('CHN', 'STL', 'NYK')
const teams = await schemarch.from('players').select('*')
  .in('team_id', ['CHN', 'STL', 'NYK'])
if (teams.error) throw teams.error

// WHERE last_name ILIKE 'sm%'
const names = await schemarch.from('players').select('*')
  .ilike('last_name', 'sm%')
if (names.error) throw names.error

Use IS NULL rather than = NULL in SQL. ILIKE is case-insensitive pattern matching; wildcard searches still need an indexing and performance review for the intended workload.

Paginate with stable ordering

SELECT * FROM public.players
ORDER BY id ASC LIMIT 20 OFFSET 40;

const { data, error } = await schemarch
  .from('players')
  .select('*')
  .order('id', { ascending: true })
  .range(40, 59)

if (error) throw error

Range endpoints are inclusive, so 40 through 59 requests 20 records. Concurrent changes can still shift offset pages. For large or changing datasets, consider keyset pagination using an indexed, stable ordering key.

Insert, update, and delete records

Append select to return changed rows. Omit it when returned row data is unnecessary. Each write remains subject to constraints, grants, and RLS; returning rows also requires appropriate read access.

// INSERT ... RETURNING *
const inserted = await schemarch.from('players')
  .insert({ first_name: 'Alex', last_name: 'Morgan', team_id: 'CHN', age: 27 })
  .select()
if (inserted.error) throw inserted.error

// UPDATE ... WHERE id = 42 RETURNING *
const updated = await schemarch.from('players')
  .update({ team_id: 'NYK' }).eq('id', 42).select()
if (updated.error) throw updated.error

// DELETE ... WHERE id = 42 RETURNING *
const deleted = await schemarch.from('players')
  .delete().eq('id', 42).select()
if (deleted.error) throw deleted.error

Include explicit filters on updates and deletes. A missing filter can change every row that the caller is allowed to modify. Decide how deletion interacts with foreign keys, retention, audit records, and recovery.

Keep advanced SQL close to the database

Complex reporting, window functions, recursive queries, and atomic multi-step workflows may be clearer as SQL functions. Several awaited SDK calls are separate requests and do not form one transaction.

CREATE FUNCTION public.get_team_statistics()
RETURNS TABLE (team_id text, player_count bigint, average_age numeric)
LANGUAGE sql STABLE SECURITY INVOKER
SET search_path = ''
AS $$
  SELECT p.team_id, count(*), avg(p.age)
  FROM public.players AS p
  GROUP BY p.team_id;
$$;

const { data, error } = await schemarch.rpc('get_team_statistics')
if (error) throw error

The example assumes team_id is text and age is integer. Configure function execution privileges and API exposure deliberately. SECURITY INVOKER preserves caller privileges; review functions that run with elevated privileges separately. Add an explicit ORDER BY inside the function when callers need deterministic result ordering.

Verify the application contract

• Compare SQL and API results using the same role and representative fixtures. Administrative SQL sessions can see different rows from an application user.
• Test nulls, Boolean grouping, empty results, denied writes, and cross-user access.
• Use schema-derived TypeScript definitions when configuring a typed client; types do not enforce runtime authorization.
• Measure expensive queries with realistic data and bounded result sets.
• Keep secret keys on trusted servers and avoid displaying raw database errors to end users.

Use JavaScript for application data access and SQL for schemas, constraints, indexes, and database-side workflows. The two interfaces share the same data model and should preserve the same intended business rules.