Database

Every OrbitNest project has its own PostgreSQL 15 database. You define the schema with SQL, browse it in Studio, protect rows with row-level security (RLS), and read and write data from your apps through the SDKs or the REST API.

How access works

Who is calling decides which database role runs the query and whether RLS applies.

The SQL endpoint and the query endpoint run as the anon role with the anon key alone, and as authenticated with the user's claims when the anon key comes with a user token, so RLS policies and auth.uid() apply. That covers the SDK query builders, which compile to the SQL endpoint. Each call is a single statement, and anon and user calls can't use set_config, pg_settings or the *_to_xml functions (403).

PostgreSQL has the standard anon, authenticated and service_role roles in every project database. OrbitNest creates them, grants them SELECT, INSERT, UPDATE and DELETE on tables in the public schema, and lets policies target them with TO authenticated and so on. anon and authenticated can read but not write the auth_* tables.

Tables and schema

Create and change tables with SQL: in the SQL Editor, in a migration, or through the SQL endpoint with a service-role key. Studio has no form-based table designer.

CREATE TABLE posts (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    uuid REFERENCES auth_users(id) ON DELETE CASCADE,
  title      text NOT NULL,
  body       text,
  published  boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX posts_user_created_idx ON posts (user_id, created_at DESC);

In Studio:

• Tables lists your tables and whether RLS is on for each (rls_enabled in the API). From a table's menu you can view its data (search, filter and page through rows), view its columns and keys, run bulk inserts and updates, manage RLS, or delete the table.
• Schema draws your tables, including the built-in auth tables, and their foreign-key relationships.

Built-in auth tables

Each project database includes the tables behind Authentication, such as auth_users, auth_sessions, auth_refresh_tokens, auth_identities and auth_mfa_factors. They live in the public schema. Reference users with a foreign key to auth_users(id), which is a uuid.

These tables are protected:

• With the anon key, with or without a user token, every row and table data endpoint returns 403 for an auth_* table, and SQL can't reference the auth tables at all.
• No caller, including the service-role key, Studio, the CLI and the MCP server, can drop, truncate, alter, rename or DELETE FROM an auth_* table through SQL. See SQL restrictions.
• You can still reference them, for example with a foreign key to auth_users(id) or a trigger on auth_users.

Use the auth endpoints to work with users.

Generate TypeScript types

Generate interfaces for your tables with orbitnest gen types (see CLI) or from Node.js:

import { writeFileSync } from 'fs';
import { createClient, generateTypes } from '@orbitneststudio/node';

const client = createClient({ apiKey: process.env.ORBITNEST_SERVICE_KEY! });
writeFileSync('orbitnest.types.ts', await generateTypes(client.db));

SQL Editor

Open SQL Editor in a project. It has two tabs:

• Queries: write and run SQL, view results, and export them as CSV. Every run is saved automatically as a saved query, whether it succeeds or fails. Search, pin and delete saved queries, switch between grid and list views, and open the editor full screen.
• Migrations: a read-only view of the migrations registry table written by the migration tools.

Queries run with your Studio account's privileged role, so RLS doesn't apply.

SQL restrictions

Every SQL request, from Studio, the CLI, the MCP server, the SDKs or the REST API, passes the same server-side checks before it runs:

• Comments are stripped before the SQL runs. The raw request can be up to 200 KB, and the SQL must be under 50,000 characters after comments are removed.
• Multiple statements. The service-role key and your Studio account (Studio, the CLI and the MCP server) can send several ;-separated statements in one request, including a BEGIN; … COMMIT; script. Send the script as sql with no params. It runs as one batch on one connection, so if any statement fails, none of them are applied. With the anon key, any second statement after a ; is rejected with 403.
• UNION SELECT and UNION ALL SELECT are rejected.
• Database, role, user, tablespace and foreign-server commands, COPY, SET SESSION, RESET ALL, dblink, server file functions such as pg_read_file, and references to pg_catalog. are rejected.
• Studio, the CLI, the MCP server and the service-role key run SQL as your project's own database role, not as a superuser. It owns your tables, so DDL works, but COPY to files or programs, untrusted extensions, ALTER SYSTEM and changes to the auth_* tables fail with a permission error.
• information_schema is limited to columns, tables, table_constraints, key_column_usage and constraint_column_usage.
• Anon-key SQL can't reference the built-in auth tables.
• For every caller, DROP TABLE, TRUNCATE, ALTER TABLE (including RENAME and DISABLE ROW LEVEL SECURITY) and DELETE FROM on an auth_* table are rejected, also when the name is schema-qualified or quoted. DROP SCHEMA public, DROP SCHEMA auth and DROP OWNED are rejected too.
• The same rule covers dynamic SQL in a DO block or function: a body that runs EXECUTE with a destructive statement and names an auth_ table is rejected, and so is a loop over pg_tables, pg_class or information_schema.tables that drops, truncates, alters or deletes without excluding auth tables. Add a filter such as AND tablename NOT LIKE 'auth\_%' to the loop.

Text inside string literals and dollar-quoted bodies is ignored by the keyword checks, so data that happens to contain words like drop or copy is fine. A rejected request returns 403 with a message such as SQL contains malicious patterns that could compromise security, SQL contains forbidden keyword: COPY TO, or, for the auth table rule, … is not allowed: auth_* tables are managed by OrbitNest and cannot be dropped, truncated, altered, renamed or bulk-deleted through SQL. Exclude them (e.g. AND tablename NOT LIKE 'auth\_%') or use the project auth endpoints.

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT tablename FROM pg_tables
           WHERE schemaname = 'public' AND tablename NOT LIKE 'auth\_%'
  LOOP
    EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;

Database size quota

Each plan has a database size limit, counted across all projects in the workspace. See Billing. When the workspace is at or over the limit, writes that can grow the database fail with 402: row inserts and updates, bulk inserts and updates, and SQL containing INSERT, UPDATE, CREATE, ALTER, MERGE, DO or similar statements. Reads, DELETE, DROP and TRUNCATE keep working so you can free space. Sign-ups through the auth endpoints are not blocked. Size is measured at most every 5 minutes, so a write can be refused for a few minutes after you free space.

{
  "statusCode": 402,
  "error": "Payment Required",
  "code": "QUOTA_EXCEEDED",
  "quota": "database",
  "plan": "free",
  "limit": 536870912,
  "usage": 541065216,
  "message": "Database size quota exceeded on the free plan. Reads and deletes still work; delete data or upgrade to write again."
}

Query from your app

The JavaScript and Node.js SDKs share the same database client. Use the anon key in browsers and the service-role key only on servers.

import { createClient, localStorageAdapter } from '@orbitneststudio/js';

const orbit = createClient({
  apiKey: import.meta.env.VITE_ORBITNEST_ANON_KEY,
  storage: localStorageAdapter,
});

const { data, error } = await orbit.db
  .from('posts')
  .eq('published', true)
  .order('created_at', { ascending: false })
  .limit(10)
  .select('id, title, created_at');

if (error) throw new Error(error.message);
console.log(data.rows);

The builder compiles to one parameterized SELECT and sends it to the SQL endpoint, so values are always bound, never interpolated. Available methods:

For anything the builder can't express, run parameterized SQL:

const { data } = await orbit.db.query(
  'SELECT id, title FROM posts WHERE user_id = $1 ORDER BY created_at DESC',
  [userId],
);

With the anon key, SQL must start with SELECT, SHOW, DESCRIBE or INSERT. UPDATE, DELETE and DDL need the service-role key.

Writes

insert works with the anon key. update, delete, bulkUpdate and bulkDelete need either the service-role key or the anon key together with a signed-in user's access token. bulkInsert needs the service-role key.

For Flutter, see Flutter SDK → Database.

Full-text search

textSearch matches a column with PostgreSQL text search in the JavaScript and Node.js SDKs:

const { data } = await orbit.db
  .from('articles')
  .textSearch('body', 'postgres -mysql', { type: 'websearch', config: 'english' })
  .limit(10)
  .select('id, title');

type is websearch (the default, Google-style syntax), plain or phrase. config is the text search configuration and defaults to english. The filter compiles to to_tsvector(config, column::text) @@ websearch_to_tsquery(config, $1), or plainto_tsquery or phraseto_tsquery. For large tables, add a matching index:

CREATE INDEX articles_body_fts_idx ON articles USING gin (to_tsvector('english', body));

Vector search (pgvector)

Store embeddings in a vector column and return the rows closest to a query embedding.

1. Enable pgvector in Project Settings → Extensions (workspace admins only), or run CREATE EXTENSION IF NOT EXISTS vector; in the SQL Editor.

2. Add a column and, optionally, an index:

   CREATE TABLE documents (
     id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
     title     text,
     embedding vector(1536)
   );

   CREATE INDEX documents_embedding_idx ON documents USING hnsw (embedding vector_cosine_ops);

3. Query with nearest:

   const { data } = await orbit.db
     .from('documents')
     .nearest('embedding', queryEmbedding, { metric: 'cosine', limit: 5 })
     .select('id, title');

metric is l2 (the default, <->), cosine (<=>) or ip (inner product, <#>). The vector is sent as a bound parameter and cast to ::vector. nearest combines with filters, and any order() calls sort after the distance.

Extensions

The Extensions tab in Project Settings enables pgvector. To enable other extensions that ship with PostgreSQL, such as pg_trgm, citext, unaccent or uuid-ossp, run CREATE EXTENSION in the SQL Editor or with a service-role key. The MCP tool orbitnest_enable_extension accepts vector, pg_trgm, citext, unaccent and uuid-ossp.

gen_random_uuid() is built into PostgreSQL 15 and needs no extension.

Row-level security

RLS policies decide which rows each request can see and change. Turn RLS on for every table your apps reach with the anon key: without it, the anon key can read every row.

Keep these rules in mind when writing policies:

• auth.uid() returns the user id from the access token as text. Compare it with user_id::text.
• Requests sent with the anon key plus a user access token run as the authenticated role, with the user's claims set for auth.uid(). This covers table data reads and row writes.
• The SQL endpoint, the query endpoint and the SDK query builders follow the same rules: the anon key alone runs as anon, and the anon key plus a user token runs as authenticated with the user's claims.
• Writes return the new row, so a table needs a SELECT policy that lets the writer read the row it inserts or updates. Without one, Postgres reports the write as new row violates row-level security policy.
• A view runs with its owner's rights, which bypass RLS, so the anon key would see every row of the tables behind it. Create views over RLS tables with WITH (security_invoker = true) so they apply the caller's policies.
• Privileged callers (Studio, CLI, MCP, and the service-role key where noted in How access works) bypass RLS.

Set up RLS with SQL

Run the statements one at a time in the SQL Editor, or together as one script. See SQL restrictions.

1. Enable RLS:

   ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

2. Let anyone read published posts:

   CREATE POLICY posts_read_published ON posts
     FOR SELECT USING (published = true);

3. Let signed-in users manage their own posts:

   CREATE POLICY posts_owner_all ON posts
     FOR ALL TO authenticated
     USING (auth.uid() = user_id::text)
     WITH CHECK (auth.uid() = user_id::text);

Tables you create through Studio, the CLI, the MCP server or the service-role key are granted to anon, authenticated and service_role automatically. If you create a table over a direct connection, grant it yourself:

GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO anon, authenticated, service_role;

Manage RLS in Studio

On the Tables page, open a table's menu and choose Manage RLS. You can enable or disable RLS, list policies, and create or delete policies by choosing a name, a command (SELECT, INSERT, UPDATE, DELETE or ALL), a role, a USING expression and an optional WITH CHECK expression.

Manage RLS over HTTP

The RLS endpoints, including listing policies, take a service-role key:

curl -X POST "https://api.orbitnest.io/api/project/my-app/database/tables/posts/policies" \
  -H "Authorization: Bearer $ORBITNEST_SERVICE_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "posts_read_published",
    "command": "SELECT",
    "role": "public",
    "using": "published = true"
  }'

• name must be a plain identifier: letters, digits and underscores, starting with a letter or underscore, up to 63 characters.
• command is SELECT, INSERT, UPDATE, DELETE or ALL.
• role is public (the default), anon, authenticated or service_role. To target several roles, send roles as an array instead.
• using (or definition) is required, except for INSERT.
• with_check (or check) is applied for INSERT, UPDATE and ALL. An INSERT policy gets only a WITH CHECK clause: send with_check, or a lone using is used as the check.
• Expressions can't contain semicolons, DDL or DML keywords, or references to pg_catalog, and are limited to 2,000 characters.

The SDKs' createPolicy sends definition, check and roles, which this endpoint accepts.

Direct Postgres connection

A direct connection string lets ORMs, GUI clients and psql connect to the project database. This feature is off unless the deployment's operator enables it with DB_DIRECT_CONNECT_ENABLED=true and exposes Postgres to the network. While it's off, every way of fetching the string returns 403, and Studio shows "Direct connections aren't enabled".

When it's enabled, you can get the connection details from:

• The Connect button in the Studio top bar, on the Postgres tab.
• orbitnest db connect in the CLI.
• The MCP tool orbitnest_get_connection_string.
• GET /api/admin/projects/:projectId/connection with a Studio session token.

The response contains host, port, database, user, password, sslmode (default require) and a ready-made connection_string in the form postgresql://user:password@host:port/database?sslmode=require. If the operator configured a pooler, it also includes a pooler connection string, which suits serverless and other short-lived connections.

The credentials belong to the project's own database role, not a superuser, so RLS still applies. Treat them as a secret.