Skip to content
SQLAnvil SQLAnvil SQLAnvil

Getting Started

import { Tabs, TabItem, Steps } from ‘@astrojs/starlight/components’;

SQLAnvil compiles SQLX files and action configs into SQL, then executes them against your data warehouse. Projects are driven by a workflow_settings.yaml file that declares your warehouse connection and default schema.

Prerequisites

Section titled “Prerequisites”
  • Node.js 20 LTS or later
  • One of: a BigQuery project with a service account, a PostgreSQL database, a Supabase project, or a MySQL/MariaDB database

Install

Section titled “Install”
Terminal window
npm install -g @sqlanvil/cli

Or run without installing:

Terminal window
npx @sqlanvil/cli --help

Create a project

Section titled “Create a project”

  1. Initialize a new SQLAnvil project (pick your warehouse):

    Terminal window
    sqlanvil init my-project --warehouse postgres   # or: supabase | bigquery | mysql
    cd my-project

    Prefer to be walked through it? sqlanvil init --interactive (1.23+) asks the same questions one at a time — including whether to start fresh or convert an existing Dataform project — and writes your .df-credentials.json from a credentials Q&A, so steps 1–2 collapse into one. Want the opposite? sqlanvil init --bare scaffolds only the directories and config, no sample files.

    Every generated project also includes an AGENTS.md (1.24+) — a warehouse-tailored guide in the cross-agent standard that Codex, Cursor, Gemini CLI, and 30+ tools read natively, plus a CLAUDE.md bridge for Claude Code — so any coding agent pointed at your repo knows the sqlanvil dialect from the first prompt.

    The default creates a sample project in the shape real ones take — source declarations, staging views over them, output tables built from the staging layer, and a business-rule assertion:

    my-project/
    ├── definitions/
    │   ├── sources/
    │   │   ├── app_orders.sqlx          ← declaration: a table your app already has
    │   │   └── bigquery_zip_codes.sqlx  ← declaration: BigQuery public data via a connection
    │   ├── intermediate/
    │   │   ├── stg_app_orders.sqlx      ← staging views over the sources
    │   │   └── stg_zip_codes.sqlx
    │   ├── outputs/
    │   │   ├── sales/daily_sales.sqlx           ← tables built from the staging views
    │   │   └── reporting/product_revenue.sqlx
    │   │       reporting/orders_by_region.sqlx  ← joins both staged sources
    │   └── test/assert_sales_amounts_positive.sqlx
    ├── includes/                    ← shared JavaScript helpers
    └── workflow_settings.yaml      ← includes the bigquery_public connection

    compile works immediately. To run, point the app_orders declaration at a real table (and add BigQuery credentials for the zip_codes source, or delete that pair) — sqlanvil validate tells you exactly what’s still missing. MySQL projects skip the BigQuery source (cross-warehouse connections need a Postgres/Supabase warehouse).

  2. Add your connection. init already wrote workflow_settings.yaml (with your warehouse: and default schema) plus a .df-credentials.json template. Fill in the credentials file — it’s gitignored, so secrets never get committed:

    ```json // .df-credentials.json { "host": "db.example.com", "port": 5432, "database": "analytics", "user": "sqlanvil_writer", "password": "...", "sslMode": "require", "defaultSchema": "public" } ``` ```json // .df-credentials.json — Session pooler (copy the host verbatim from the dashboard Connect dialog) { "host": "aws-1-.pooler.supabase.com", "port": 5432, "database": "postgres", "user": "postgres.", "password": "...", "sslMode": "require", "defaultSchema": "public" } ``` ```json // .df-credentials.json — projectId + location. Auth via `gcloud auth application-default login`, // or add a "credentials" field containing a service-account key JSON. { "projectId": "my-gcp-project", "location": "US" } ``` ```json // .df-credentials.json — no defaultSchema field: the defaultDataset in // workflow_settings.yaml IS the MySQL database. sslMode: "disable" for local Docker. { "host": "db.example.com", "port": 3306, "database": "analytics", "user": "sqlanvil_writer", "password": "...", "sslMode": "require" } ```

  3. Write your first action (or start from the generated demo models) in definitions/my_view.sqlx:

    config {
      type: "view",
      description: "My first SQLAnvil view."
    }
    

    SELECT 1 AS id, 'hello' AS greeting

  4. Compile to inspect the SQL that will run (no database needed):

    Terminal window
    sqlanvil compile .

  5. Run against your warehouse:

    Terminal window
    sqlanvil run .

    With the sample project this stages your orders, builds daily_sales and the reporting tables, and checks the assertion. run reads .df-credentials.json from the project directory by default; pass --credentials <path> only to use a differently-named or relocated file.

Project structure

Section titled “Project structure”
PathPurpose
workflow_settings.yamlWarehouse connection, default schema/dataset, vars
definitions/SQLX files, SQL files, actions.yaml, JS files
includes/Shared JS macros and constants
package.jsonDeclares @sqlanvil/core version and any packages

Action types

Section titled “Action types”
TypeCreates
tableA full-replace table
viewA SQL view
incrementalAn incrementally-updated table
assertionA data quality test
operationArbitrary SQL statements
declarationA reference to an external table

See the Reference section in the sidebar for full API documentation on each action type.