Database

The mental model

A LingCode Cloud backend is a real Postgres database — but you don't connect to it. Instead, the SDK speaks to a small CRUD gateway that sits in front of the database and accepts one operation against one table at a time. That gateway is what makes the architecture safe to put in a browser bundle.

Here's the chain of reasoning:

• The anon key is public by design — it lives in your client code.
• Because the client can't run arbitrary SQL, it can't escalate. Every request is "select/insert/update/delete on table X with these filters."
• Each of those requests runs on the server under a low-privilege role with row-level security (RLS) applied, scoped to the signed-in user. The user only ever sees and touches rows the policies allow.
• Anything genuinely relational — JOINs, views, indexes, constraints, generated columns — lives one level down, in your migrations, which run real DDL.

The query builder

Start every query with client.from('table'). That returns a chainable builder: you stack filters and modifiers, then end with a terminal verb that actually runs the request and returns a promise. Inside /try and the Mac preview the client is already on the page as window.lingcode; in your own app you create it once (see Use LingCode Cloud in your own app).

const { data, error } = await lingcode
  .from('todos')
  .eq('done', false)
  .order('created_at', { ascending: false })
  .limit(50)
  .select();

if (error) {
  console.error(error.code, error.status);
} else {
  render(data); // T[]
}

Every terminal op resolves to a Result: { data, error }. On success error is null and data is an array of rows. On failure data is null and error is { code, status } — a stable string code plus the HTTP status. Always check error; the SDK does not throw on a request that the server rejects.

Filters

Chain as many filters as you like — they AND together. Each returns the builder, so order doesn't matter.

Filter	Meaning
.eq(col, val)	column equals value
.neq(col, val)	column does not equal value
.gt(col, val)	greater than
.gte(col, val)	greater than or equal
.lt(col, val)	less than
.lte(col, val)	less than or equal
.like(col, str)	case-sensitive pattern match
.ilike(col, str)	case-insensitive pattern match
.in(col, [vals])	column is one of the listed values
.is(col, null | 'not_null')	IS NULL / IS NOT NULL
.match({ a: 1, b: 2 })	shorthand for several .eq filters at once

Modifiers

Modifier	Meaning
.order(col, { ascending?: boolean })	sort by a column; defaults to ascending
.limit(n)	cap the number of rows returned
.range(from, to)	return an inclusive slice of rows (for paging)

Terminal operations

The verb runs the request. Each returns Promise<Result<T[]>>.

Op	Does
.select()	read rows matching the chained filters
.insert(row | row[])	insert one row or an array of rows (batch, one transaction)
.upsert(row | row[], { onConflict })	insert-or-update by a unique key (ON CONFLICT)
.update(patch)	apply a partial patch to matching rows
.delete()	delete matching rows
.subscribe(onChange, onError?)	open a realtime subscription to row changes

// Update — filter first, then patch:
await lingcode.from('todos').eq('id', 7).update({ done: true });

// Delete — same rule:
await lingcode.from('todos').eq('id', 7).delete();

// This THROWS / is refused (no filter):
await lingcode.from('todos').delete(); // → error.code === 'where_required'

.subscribe() is the entry point to realtime — subscribe to INSERT/UPDATE/DELETE on a table, RLS-filtered so users only see their own rows. It's documented in full on the Realtime guide; we won't repeat it here.

The query builder is single-table — reach for RPC when you need SQL

This is the section to read twice. The chainable builder (.select/.insert/.update/.delete) operates on one table. For a multi-table read — a JOIN, a CTE, an aggregate, full-text ranking — you don't drop down to raw SQL from the client; you define a SQL function in a migration and call it with lingcode.rpc(). The SQL lives server-side and runs as your RLS-gated tenant role. Coming from PostgREST or raw SQL, this is where the first-try-vs-400 line gets drawn.

JOINs & complex reads → rpc()

You can't express a JOIN in the builder. Options, in rough order of preference:

• Define a function and call rpc() — write the JOIN/CTE/aggregate/ranking as a CREATE FUNCTION in a migration, then call it by name. The fastest path for a genuinely relational read.
• Create a VIEW in a migration — define the JOIN once as a view, then select() from it exactly like a table (good when you don't need parameters).
• Fetch each table and stitch in code — pull the parents, collect their ids, fetch the children with .in(), and assemble the shape in JavaScript.
• Denormalize — if you always read a field together with another table's row, copy it onto the row at write time.

The rpc() path — function in a migration, call from app code:

-- migration (runs once):
create function search_todos(q text, owner uuid)
returns setof todos language sql stable as $$
  select * from todos
  where user_id = owner and fts @@ websearch_to_tsquery(q)
  order by ts_rank(fts, websearch_to_tsquery(q)) desc
  limit 50;
$$;

// runtime — named args (object) or positional (array):
const { data } = await lingcode.rpc('search_todos', { q: 'milk', owner: myId });
// returns up to 1000 rows; runs as your tenant role with RLS enforced

Before (the instinct — does not work at runtime):

// ✗ There is no join. This is not a thing the gateway can do.
const { data } = await lingcode
  .from('todos')
  .select('*, users(name)'); // → no relational expansion

After — stitch in code:

const { data: todos } = await lingcode.from('todos').select();

const userIds = [...new Set(todos.map(t => t.user_id))];
const { data: users } = await lingcode
  .from('users')
  .in('id', userIds)
  .select();

const byId = Object.fromEntries(users.map(u => [u.id, u]));
const rows = todos.map(t => ({ ...t, user: byId[t.user_id] }));

After — or define a view once in a migration, then read it like a table:

-- migration (runs once, full DDL):
create view todos_with_user as
  select t.*, u.name as user_name
  from todos t
  join users u on u.id = t.user_id;

// runtime — the view is just a table to the SDK:
const { data } = await lingcode.from('todos_with_user').select();

Upserts & batch writes

insert() and upsert() both take a single row or an array — an array writes every row in one transaction (no row-at-a-time HTTP loop), up to the tier's maxRowsPerWrite. upsert() is INSERT … ON CONFLICT, the idempotent path for sync/ingest: a re-run updates by key instead of erroring.

// Batch insert — many rows, one call:
await lingcode.from('events').insert([row1, row2, row3]);

// Upsert by a unique key — merge (default) updates the conflicting row:
await lingcode.from('events').upsert(rows, { onConflict: 'source_hash' });

// Composite key, and merge:false to keep the existing row (DO NOTHING):
await lingcode.from('prefs').upsert(row, { onConflict: ['user_id', 'key'] });
await lingcode.from('events').upsert(rows, { onConflict: 'source_hash', merge: false });

The conflict target must have a unique or primary-key constraint (add it in a migration). merge: true sets every non-conflict column from the incoming values; merge: false leaves a matching row untouched.

Pages of 200

select() returns at most 200 rows. For anything larger, page explicitly with .limit() and .range(). And because there are no JOINs or aggregates at runtime, counts and sums are computed either in your code (over the page) or in a view created in a migration.

// Page through results 200 at a time:
async function* allTodos() {
  const PAGE = 200;
  for (let from = 0; ; from += PAGE) {
    const { data } = await lingcode
      .from('todos')
      .order('id', { ascending: true })
      .range(from, from + PAGE - 1)
      .select();
    if (!data || data.length === 0) return;
    yield* data;
    if (data.length < PAGE) return;
  }
}

Migrations & schema

All schema — tables, indexes, views, constraints, generated columns, seed data, and RLS policies — comes from migrations. There are two ways to run one:

• From the console's SQL tab: open lingcode.dev/backends.html and pick your backend.
• Let the agent call apply_migration for you.

Migrations run full DDL. Use IF NOT EXISTS so a migration is idempotent and safe to re-run:

create table if not exists todos (
  id         bigint generated always as identity primary key,
  user_id    uuid,
  title      text not null,
  done       boolean not null default false,
  created_at timestamptz not null default now()
);

create index if not exists todos_user_idx on todos (user_id);

For the full workflow — writing, ordering, and re-running migrations — see Manage database migrations. Once you have schema, run the linter: Scan your backend with advisors flags missing RLS policies, missing indexes, and missing primary keys before they bite you in production.

Row-level security & tenant isolation

Each backend lives in its own Postgres schema with a dedicated, low-privilege role. Runtime queries — the ones the SDK makes with the anon key — run as that role, with the signed-in user's id exposed to the policies. That's what makes a policy like "a user can only see rows where user_id is their own id" work: the gateway runs your query, but Postgres silently filters it to the rows the policy permits.

-- in a migration: turn on RLS and scope rows to their owner
alter table todos enable row level security;

create policy todos_owner on todos
  using (user_id = current_user_id());

The console / owner path is different: it bypasses RLS so you can administer every row across all users from backends.html. That's the privileged side — distinct from the anon-key runtime path your app uses.

This is the other half of why the anon key is safe to ship: it can only ever exercise the low-privilege, RLS-gated runtime role. Write your policies and the public key carries no risk.

Per-tier limits

Each backend has a table cap by plan:

Limit	Free	Pro	Max Pro
maxTables	10	50	200
maxRowsPerWrite	100	1,000	5,000

maxRowsPerWrite caps the rows accepted in one insert()/upsert() array — split larger loads into batches. There are also per-tier limits on maxObjects (stored objects) and maxUsers — the full numbers live on the pricing page. Exceeding any cap returns HTTP 402 with code quota_exceeded.

Error codes

When error is non-null, the code tells you what happened and status carries the HTTP status:

Code	Status	Means
where_required	—	an update or delete was issued with no filter
table_not_found	404	the table doesn't exist (typo, or no migration yet)
invalid_request	400	the request was malformed
not_provisioned	409	the backend isn't live yet — provisioning hasn't finished
quota_exceeded	402	a per-tier cap (tables, objects, users) was hit

Under the hood (REST)

The SDK is a thin wrapper over four REST endpoints. The base is https://lingcode.dev/api/cloud/be/<backend-id>, and every call carries the anon key — either as a Bearer token or as a ?apikey= query parameter. Each endpoint takes a JSON body describing the table plus filters and the row or patch.

Endpoint	SDK verb
POST /select	.select()
POST /insert	.insert()
POST /upsert	.upsert()
POST /update	.update()
POST /delete	.delete()
POST /rpc	lingcode.rpc()

curl -X POST 'https://lingcode.dev/api/cloud/be/<backend-id>/select' \
  -H 'Authorization: Bearer <your-anon-key>' \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "todos",
    "filters": [{ "op": "eq", "col": "done", "val": false }],
    "order":   [{ "col": "created_at", "ascending": false }],
    "limit":   50
  }'

You'll almost always reach for the SDK, which gives you the chainable builder over exactly these endpoints. The full request/response shapes are in the API reference.