Supabase

SQLAnvil treats Supabase as first-class Postgres: your models compile to idiomatic Postgres and run directly against your Supabase database — no data warehouse required. On top of that, Supabase gets native action types for RLS policies, Realtime publications, and pgvector indexes.

Quickstart

npm i -g @sqlanvil/cli
sqlanvil init my_project --warehouse supabase
cp my_project/.df-credentials.example.json my_project/.df-credentials.json   # then edit it (below)
sqlanvil compile my_project
sqlanvil run my_project --credentials my_project/.df-credentials.json

Prefer a working example? Start from the supabase-sqlanvil-starter template.

Connection

Connection settings are split in two — non-secret settings in workflow_settings.yaml (committed) and the connection itself in .df-credentials.json (gitignored, never committed):

workflow_settings.yaml

warehouse: supabase
defaultDataset: analytics              # the schema your models build into — keep it out of `public` (below)
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.26.2            # pin the release you installed — `sqlanvil init` writes this

// .df-credentials.json  (gitignored)
{
  "host": "aws-1-<region>.pooler.supabase.com",
  "port": 5432,
  "database": "postgres",
  "user": "postgres.<your-project-ref>",
  "password": "<your-db-password>",
  "sslMode": "require",
  "defaultSchema": "public"
}

Get these from your project’s Connect button in the Supabase dashboard. Two things to know:

• Use the Session pooler on IPv4 networks (most laptops, CI, containers). The direct connection (db.<ref>.supabase.co) is IPv6-only. Copy the host verbatim — the aws-0-/aws-1- prefix and region slug aren’t guessable; a hand-built host fails with tenant ... not found.
• The pooler user is postgres.<project-ref> (not just postgres), and sslMode must be require.

Troubleshooting

Symptom	Fix
tenant ... not found	Copy the pooler host verbatim from the Connect dialog; set user to postgres.<project-ref>.
Connection times out / ENETUNREACH	You’re using the direct (IPv6-only) host on IPv4 — switch to the Session pooler.
password authentication failed	Wrong DB password — reset it in Settings → Database.
ECIRCUITBREAKER / too many auth failures	A wrong password tripped Supabase’s pooler lockout; fix the password and wait ~1–2 min.

Keep models out of public

Supabase’s Data API serves the public schema to anyone holding your project’s publishable (anon) key. Supabase also sets default privileges so that every table the postgres role creates in public is granted to anon and authenticated, with SELECT, INSERT, UPDATE, DELETE and TRUNCATE. A model built there with RLS off can be read, changed or emptied through the API by anyone with that key. This applies to runs that connect as postgres, which is what the credentials above do. SQLAnvil Cloud builds as its own role, which these default privileges don’t cover.

Build into a dedicated schema instead. sqlanvil init --warehouse supabase writes defaultDataset: analytics (older releases wrote public; change it). A schema other than public isn’t served by the Data API unless you add it under Settings → Data API → Exposed schemas, and it doesn’t get those grants.

To serve a model through the API on purpose, build it in public (set schema: "public" in its config) and give it an RLS policy in the same project.

To find models that are already exposed, run this in the SQL editor:

select c.relname as table_name, c.relrowsecurity as rls_enabled,
       string_agg(distinct g.privilege_type, ', ') as anon_privileges
from pg_class c
join pg_namespace n on n.oid = c.relnamespace and n.nspname = 'public'
join information_schema.role_table_grants g
  on g.table_schema = 'public' and g.table_name = c.relname and g.grantee = 'anon'
where c.relkind in ('r', 'p', 'v', 'm')
  and not exists (  -- skip extension tables such as PostGIS's spatial_ref_sys
    select 1 from pg_depend d where d.objid = c.oid and d.deptype = 'e')
group by 1, 2
order by 1;

To move a project, change defaultDataset, run it (each model is rebuilt in the new schema, and incremental tables are rebuilt in full from their sources), point anything that reads the old tables at the new schema, then drop the old copies from public.

Working with Supabase declarative schemas

Supabase’s declarative schemas (supabase/schemas/*.sql, diffed by pg-delta) manage your app schema. SQLAnvil manages your models. They can share a database if you keep them apart:

• supabase db schema declarative sync is safe. It compares your schema files with your migration history, not with the live database, so it never sees tables SQLAnvil builds and never writes migrations for them.
• Commands that read the live database pull your models in. supabase db pull --declarative and supabase db schema declarative generate --linked write every table and view in the database into supabase/schemas/, including SQLAnvil’s. The next sync then writes a migration that creates them, so both tools own them. If those files are later deleted, sync writes DROP TABLE for each one, and supabase db push runs it against production. Incremental tables lose their history.
• After a pull or generate, delete SQLAnvil’s files before you run sync. With models in their own schema this is one folder (supabase/schemas/analytics/). Files that never reached a migration produce nothing when removed. As of Supabase CLI 2.120.0, the --schema flag doesn’t narrow either command, so you can’t exclude the schema up front.
• Review generated migrations for DROP statements on your models before committing them.

Supabase-native actions

These are JavaScript calls in a definitions/*.js file, not .sqlx action types. Each call becomes an operation in your graph that depends on the table it targets, so it runs after that table is built. The SQL below uses tables in public, the schema the Data API serves, which is where RLS matters.

From 1.33, rlsPolicy, realtimePublication and vectorIndex can also be entries in actions.yaml, with the same fields, and a missing required field is a compile error:

definitions/actions.yaml

actions:
- rlsPolicy:
    table: orders
    name: users_see_own_orders
    command: select
    roles: [authenticated]
    using: "user_id = auth.uid()"

wrapper stays JavaScript-only: it needs fields an actions.yaml entry can’t hold.

RLS policies (rlsPolicy)

definitions/supabase/policies.js

rlsPolicy({
  table: "orders",
  name: "users_see_own_orders",
  command: "select",
  roles: ["authenticated"],
  using: "user_id = auth.uid()"
});

Compiles to:

alter table "public"."orders" enable row level security;
drop policy if exists "users_see_own_orders" on "public"."orders";
create policy "users_see_own_orders" on "public"."orders" for SELECT to authenticated USING (user_id = auth.uid());

table and name (the Postgres policy name) are required. command defaults to all and roles to PUBLIC. Add withCheck for insert, update or all policies; Postgres rejects WITH CHECK on select and delete policies.

Realtime publications (realtimePublication)

definitions/supabase/realtime.js

realtimePublication({ table: "orders" });

Compiles to:

alter table "public"."orders" replica identity full;
alter publication supabase_realtime add table "public"."orders";

table is required. name picks a different publication (the default is supabase_realtime). Every change to the table is published; there is no per-event filter.

pgvector indexes (vectorIndex)

definitions/supabase/indexes.js

vectorIndex({
  table: "documents",
  name: "documents_embedding_idx",
  column: "embedding",
  indexType: "hnsw",
  params: { opclass: "vector_cosine_ops", m: "16", ef_construction: "64" }
});

Compiles to:

create extension if not exists vector cascade;
drop index if exists "documents_embedding_idx";
create index "documents_embedding_idx" on "public"."documents" using hnsw ("embedding" vector_cosine_ops) with (m = 16, ef_construction = 64);

table, name (the Postgres index name) and column are required. indexType is hnsw (the default) or ivfflat, and opclass defaults to vector_cosine_ops. Every other key in params becomes an index storage parameter, such as lists for ivfflat.

Table settings (supabase: {}, 1.33+)

For settings that belong to one table, put a supabase: {} block in its config instead of writing separate actions. It works on table and incremental configs, only with warehouse: supabase:

config {
  type: "table",
  supabase: {
    enableRls: true,
    publishToRealtime: true,
    ownerRole: "service_role",
    vectors: [{ column: "embedding", indexType: "hnsw", params: { m: "16" } }]
  }
}
SELECT id, body, embedding FROM ${ref("raw_documents")}

Each time the table is created, after its indexes and before its post_operations, this runs:

alter table "public"."documents" owner to "service_role";
alter table "public"."documents" enable row level security;
create extension if not exists vector cascade;
create index "documents_embedding_idx" on "public"."documents" using hnsw ("embedding" vector_cosine_ops) with (m = 16);
alter table "public"."documents" replica identity full;
alter publication supabase_realtime add table "public"."documents";

A plain table is created on every run. An incremental table is created only on its first run or a full refresh; appends keep the settings it already has.

• enableRls only turns row-level security on. Add policies with rlsPolicy.
• Each vectors entry indexes one column and is named <table>_<column>_idx. indexType is "hnsw" (the default) or "ivfflat". In params, opclass sets the operator class (default vector_cosine_ops), and every other key becomes an index storage parameter.
• A nested postgres: {} block is accepted here too, for the Postgres options.

Before 1.33 the block was accepted but did nothing; check your sqlanvilCoreVersion if you rely on it.

Foreign Data Wrappers (wrapper)

Query BigQuery (or any FDW-backed source) in place and join it with your Supabase tables. A single wrapper() call declares the extension, server, and ref()-able foreign tables:

definitions/sources/bigquery_zip_codes.js

wrapper({
  name: "bq_setup",
  provider: "bigquery",
  server: "bq_geo_server",
  serverOptions: { project_id: "bigquery-public-data", dataset_id: "geo_us_boundaries" },
  credential: { saKeyId: sqlanvil.projectConfig.vars.bq_sa_key_id },
  foreignTables: [
    { name: "zip_codes", schema: "bq_ext", options: { table: "zip_codes", location: "US" },
      columns: { zip_code: "text", internal_point_lat: "float8", internal_point_lon: "float8" } }
  ]
});

See the Foreign Data Wrappers guide for the full walkthrough, credentials, and a complete BigQuery + Supabase example.

Postgres features

Everything in the Postgres guide applies — incremental tables, materialized views, native partitioning, indexes (btree/gin/gist/brin), assertions, and operations for functions/procedures.