SQL Editor & Raw SQL

For anything beyond structured DDL — complex queries, functions, triggers, custom constraints, data backfills — AnvilBase gives you raw SQL.

In the console

Project → SQL opens a CodeMirror editor with syntax highlighting, a results grid, saved queries, and execution history. Write SQL, run it, and the result set renders below. This is the fastest way to explore and to author functions/triggers you’ll later capture as migrations.

Programmatically: the `exec_sql` RPC

Raw SQL execution is a data-plane operation (not a management endpoint). It runs as the virtual RPC rpc/exec_sql and requires the service_role scope — it bypasses RLS, so it must never be exposed to clients.

curl -X POST "http://localhost:39001/v1/rest/<project_id>/rpc/exec_sql" \
  -H "apikey: $SERVICE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query":"select count(*) from todos where done = true"}'

// Server-side only — uses the service_role key
const { data } = await admin.rpc('exec_sql', {
  query: 'create index on todos (user_id)',
})

Use it for:

Creating functions and triggers (the structured Schema API doesn’t cover these).
One-off backfills and migrations you then commit as .sql files.
Operational queries (VACUUM, ANALYZE, inspecting pg_stat_activity).

Multi-statement scripts are supported. You can send several statements separated by ; (e.g. create table …; create policy …;) in one call — they run in a single transaction. Note: for multi-statement payloads, result values are returned as text; single-statement queries preserve native Postgres types.

Never put the service_role key — and therefore exec_sql — in client code. It is a full database bypass. Keep it server-side. See API Keys & Scopes.

Example: a Postgres function you can call over REST

Define a function with exec_sql, then call it from any client via rpc/:

-- create the function (run via exec_sql or the SQL editor)
create or replace function search_todos(q text)
returns setof todos
language sql stable
as $$
  select * from todos
  where task ilike '%' || q || '%'
  order by created_at desc
$$;

// call it from the SDK with the anon key (subject to RLS)
const { data } = await db.rpc('search_todos', { q: 'ship' })

See RPC & Stored Procedures.

Saved queries & history (console backing store)

The console’s SQL editor persists saved queries and execution history via the management API, so they follow you across sessions and devices:

Endpoint	Purpose
`GET/POST /api/v1/projects/{id}/queries/saved`	list / upsert a saved query (by name)
`DELETE /api/v1/projects/{id}/queries/saved/{query_id}`	delete a saved query
`GET/POST /api/v1/projects/{id}/queries/history`	list / record execution history

History is automatically trimmed to the most recent 500 entries per project. These endpoints store SQL text for the UI — they do not execute it (execution is exec_sql on the data plane).

curl -X POST http://localhost:39001/api/v1/projects/<id>/queries/saved \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" -H "Content-Type: application/json" \
  -d '{"name":"Active users","sql":"select * from users where active"}'

Safety notes

exec_sql runs under a least-privileged connection role (authenticator) that assumes service_role for the duration of your statement — it bypasses RLS inside the project database but cannot escape it (no owner privileges, no CREATEROLE/CREATEDB, no dblink, no reach into other projects). Still validate any SQL you build from user input; prefer parameterized functions over string concatenation.
For schema changes you want to keep and replay across environments, write them as migration files rather than running ad-hoc exec_sql — that’s how they get version-tracked and promoted to staging/prod.

Next: Extensions.