Database

The database module is the foundation of every OrbitNest project. You get a real Postgres database — not a proprietary store — with full DDL access, extensions, and a REST API generated from your schema.

Tables & schema

Create tables from the visual Table Editor or directly in the SQL Editor. Every table automatically gets REST endpoints for GET, POST, PATCH, and DELETE — scoped by your RLS policies.

CREATE TABLE posts (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id    UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  title        TEXT NOT NULL,
  body         TEXT,
  published    BOOLEAN DEFAULT false,
  created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON posts(author_id, created_at DESC);

SQL Editor

The SQL Editor is a full-featured workspace for running ad-hoc queries, writing migrations, and exploring your data. Every query you run is saved automatically — browse, pin, rename, and re-run past queries from the grid/list view.

• Auto-save of every execution (success and error)
• Grid and list views of saved queries
• Pin favorites to the top
• Fullscreen editor with syntax highlighting
• CSV export of results

Full-text search

Search text columns with PostgreSQL full-text search via textSearch on the query builder.

await client.db
  .from('articles')
  .textSearch('body', 'postgres & search')  // type: 'websearch' (default), 'plain', 'phrase'
  .limit(10)
  .select();

Vector / semantic search (pgvector)

Store embeddings in a vector column and find the nearest rows by similarity — the basis for semantic search and RAG. Enable the extension once per project, then order by distance with nearest.

// One-time (service-role) — or use Settings → Extensions
await client.db.query('CREATE EXTENSION IF NOT EXISTS vector');

// Nearest rows to a query embedding, closest first
const { data } = await client.db
  .from('documents')
  .nearest('embedding', queryEmbedding, { metric: 'cosine', limit: 5 })
  .select('id, title');

metric is l2 (default), cosine, or ip. The vector is bound as a parameter and cast to ::vector; nearest composes with filters. In the Flutter SDK use db.vectorSearch(table, column, embedding).

Row-Level Security

Postgres RLS lets you define access policies at the row level. Combined with OrbitNest's authentication, you can safely expose tables directly to the client — policies enforce who can read or write what.

-- Enable RLS on the table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Anyone can read published posts
CREATE POLICY "published posts are public" ON posts
  FOR SELECT USING (published = true);

-- Authors can manage their own posts
CREATE POLICY "authors manage own posts" ON posts
  FOR ALL USING (auth.uid() = author_id);

Auto-generated REST API

Every table is exposed as a REST resource under /api/projects/:slug/database/tables/:table. Auth is via Bearer token (your anon or service_role key).

# List published posts
curl "https://studio.orbitnest.io/api/projects/my-app/database/tables/posts?published=eq.true" \
  -H "Authorization: Bearer ${ANON_KEY}"

# Insert a new row
curl -X POST "https://studio.orbitnest.io/api/projects/my-app/database/tables/posts" \
  -H "Authorization: Bearer ${ANON_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"title":"Hello","body":"World"}'

Extensions

Every database ships with the Postgres extensions you'd expect: pgcrypto, uuid-ossp, pg_trgm, citext, and vector (for embeddings). Enable them from the Extensions tab in Studio.