Configs Overview

Configs Reference

SQLAnvil actions are configured at two levels:

Project-level — workflow_settings.yaml at the root of your project, defining the warehouse connection and global defaults.
Action-level — the config {} block in SQLX files, or the action entry in actions.yaml, defining per-action behavior (type, dependencies, schema, warehouse-specific options).

For the complete proto-generated field reference (all action config fields), see Configs Proto Reference.

`workflow_settings.yaml` — warehouse config

workflow_settings.yaml holds non-secret settings; warehouse: is a string (bigquery | postgres | supabase). The connection lives in a separate, gitignored .df-credentials.json.

BigQuery

warehouse: bigquery
defaultProject: my-gcp-project
defaultLocation: US
defaultDataset: analytics
sqlanvilCoreVersion: 1.26.2   # pin the release you installed — `sqlanvil init` writes this

// .df-credentials.json — ADC by default; add a "credentials" service-account key JSON to override
{ "projectId": "my-gcp-project", "location": "US" }

PostgreSQL

warehouse: postgres
defaultDataset: analytics
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.26.2   # pin the release you installed — `sqlanvil init` writes this

{
  "host": "db.example.com", "port": 5432, "database": "analytics",
  "user": "sqlanvil_writer", "password": "...", "sslMode": "require", "defaultSchema": "public"
}

Supabase

warehouse: supabase
defaultDataset: public
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.26.2   # pin the release you installed — `sqlanvil init` writes this

// .df-credentials.json — Session pooler (copy host verbatim from the dashboard Connect dialog)
{
  "host": "aws-1-<region>.pooler.supabase.com", "port": 5432, "database": "postgres",
  "user": "postgres.<project-ref>", "password": "...", "sslMode": "require", "defaultSchema": "public"
}

MySQL / MariaDB

warehouse: mysql                  # one adapter for MySQL 8 and MariaDB 11
defaultDataset: analytics         # the MySQL DATABASE ("schema" == database)
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.5.0        # MySQL landed in core 1.5.0

// .df-credentials.json — MysqlConnection (NOTE: no defaultSchema)
{
  "host": "localhost", "port": 3306, "database": "analytics",
  "user": "root", "password": "", "sslMode": "disable"
}

`workflow_settings.yaml` — named environments

Define dev/staging/prod once; select with --environment <name> on compile/run/test. Each environment carries non-secret overrides plus a pointer to its own gitignored credentials file — secrets never enter the committed config.

environments:
  dev:
    schemaSuffix: dev                      # output schemas get a _dev suffix
    credentials: .df-credentials.dev.json
  prod:
    defaultDatabase: prod_db
    vars: { region: us-prod }
    credentials: .df-credentials.prod.json

Per-env fields: schemaSuffix, vars, defaultDatabase, defaultLocation, credentials. Precedence: explicit CLI flag > environment > workflow_settings defaults (vars merge per-key). See the Environments guide.

Action config blocks

Common fields (all warehouses)

config {
  type: "table",                  -- table | view | incremental | assertion | operation | declaration
  schema: "my_schema",           -- overrides defaultDataset / defaultSchema
  database: "my_db",             -- overrides default project / database
  description: "My table.",
  metadata: {                    -- free-form descriptor metadata (table/view/incremental/assertion)
    overview: "longer notes",
    extraProperties: { owner: "analytics" }
  },
  tags: ["daily", "core"],
  disabled: false,
  hermetic: true,
  dependOnDependencyAssertions: true,
  dependencies: ["other_table"]
}

metadata (an overview string + arbitrary extraProperties) attaches to the action’s descriptor and is accepted on table, view, incremental, and assertion configs. Unlike description/columns (which become warehouse COMMENTs), metadata is carried in the compiled graph for tooling/lineage — it is not written to the warehouse.

BigQuery-specific fields

config {
  type: "table",
  partitionBy: "DATE(created_at)",          -- BigQuery only
  partitionExpirationDays: 90,              -- BigQuery only
  clusterBy: ["customer_id", "region"],     -- BigQuery only
  labels: { team: "analytics" },            -- BigQuery only
  additionalOptions: { kms_key_name: "..." }, -- BigQuery only
  reservation: "projects/.../reservations/my-res" -- BigQuery only
}

PostgreSQL-specific fields

config {
  type: "table",
  postgres: {
    tablespace: "fast_ssd",
    fillfactor: 80,
    unlogged: false,
    partition: {
      kind: "range",         -- "range", "list" or "hash"
      columns: ["order_date"]
    },
    indexes: [
      {
        name: "ix_orders_customer",
        columns: ["customer_id"],
        method: "btree",       -- "btree" (default), "hash", "gin", "gist" or "brin"
        unique: false,
        where: "",           -- partial index predicate
        include: []          -- covering index columns
      }
    ]
  }
}

MySQL / MariaDB-specific fields

config {
  type: "table",
  mysql: {
    engine: "InnoDB",                 -- ENGINE=
    charset: "utf8mb4",               -- DEFAULT CHARSET=
    collation: "utf8mb4_unicode_ci",  -- COLLATE=
    indexes: [
      { name: "ix_label", columns: ["label"], unique: false }
    ]
  }
}

Plain B-tree indexes only (no where/include/opclass/method — those are Postgres-only). description:/columns: apply as real comments (tables/incrementals; not views). type: "view", materialized: true is emulated as a refreshed table snapshot (drop + CTAS each run). Partitioning, FULLTEXT/SPATIAL/prefix indexes, and row_format use raw DDL in operations. See the MySQL/MariaDB guide.

Supabase-specific fields

The supabase: {} block (on table/incremental configs, warehouse: supabase only) is applied each time the table is created. RLS policies, Realtime publications, vector indexes and wrappers are separate actions, defined with JavaScript calls or in actions.yaml, not in this block. See the Supabase guide.

config {
  type: "table",
  supabase: {
    enableRls: true,
    publishToRealtime: true,
    ownerRole: "postgres",
    vectors: [
      {
        column: "embedding",
        dimensions: 1536,
        indexType: "hnsw",
        params: { m: "16", ef_construction: "64" }
      }
    ]
  }
}

Export action (`type: "export"`)

Writes a SELECT result to a Parquet/CSV/JSON file. BigQuery compiles to native EXPORT DATA (gs:// only); Postgres/Supabase export runner-side via DuckDB (s3:///gs:///local). See the File Exports guide.

config {
  type: "export",
  export: {
    location: "s3://bucket/orders/",   -- gs:// | s3:// | local:// | path
    format: "parquet",                  -- parquet | csv | json
    overwrite: true,                    -- default true
    filename: "orders"                  -- optional; defaults to action name
  }
}
SELECT * FROM ${ref("orders")}

Cloud credentials live in a storage section of the gitignored .df-credentials.json (keyed by scheme — s3/gcs), used only by the DuckDB path; local:// needs none and BigQuery uses its own GCS access.

Cross-warehouse compatibility

Feature	BigQuery	Postgres	Supabase	MySQL/MariaDB
`partitionBy` / `clusterBy`	✓	✗ (use `postgres.partition`)	✗	✗
`postgres.indexes`	✗	✓	✓	✗ (use `mysql.indexes`)
`mysql.indexes` / `mysql.engine`	✗	✗	✗	✓
`labels` / `reservation`	✓	✗	✗	✗
`description:` / `columns:` comments	✓	✓	✓	✓ (tables, not views)
`pre_operations` / `post_operations`	✓	✓	✓	✓
`materialized` view	✓ (auto-refresh)	✓ (manual refresh)	✓ (manual refresh)	✓ (emulated table snapshot)
named `connections` (FDW)	source	✓	✓	✗
`type: "export"` (files)	✓ (`EXPORT DATA`, gs:// only)	✓ (DuckDB)	✓ (DuckDB, incl. Supabase Storage)	✗
`supabase.enableRls`	✗	✗	✓	✗

SQLAnvil emits a compilation error if a warehouse-specific config field is used against the wrong warehouse.kind.