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: public                 # the schema your models build into
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.24.0            # 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.

Supabase-native actions

RLS policies (rlsPolicy)

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

Compiles to:

CREATE POLICY users_see_own_orders ON orders
  FOR SELECT TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Realtime publications (realtimePublication)

config {
  type: "realtimePublication",
  table: "orders",
  events: ["insert", "update", "delete"]
}

Compiles to ALTER PUBLICATION supabase_realtime ADD TABLE orders;

pgvector indexes

config {
  type: "table",
  supabase: {
    vectors: [
      {
        column: "embedding",
        dimensions: 1536,
        indexType: "hnsw",
        params: { m: "16", ef_construction: "64" }
      }
    ]
  }
}
SELECT ...

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.