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.24.0   # 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.24.0   # 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.24.0   # 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: 0,               -- numeric enum: RANGE=0, LIST=1, HASH=2
      columns: ["order_date"]
    },
    indexes: [
      {
        name: "ix_orders_customer",
        columns: ["customer_id"],
        method: 0,             -- numeric enum: BTREE=0, HASH=1, GIN=2, GIST=3, BRIN=4 (omit for btree)
        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) plus the rlsPolicy, realtimePublication, and vectorIndex action types. 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.