Getting Started

Install dfmt, run it in your project, and answer one screen of questions. After that every command works the same for you, your team and your CI.

1. Install

curl -fsSL https://dfmt.datoria.ai/install.sh | sh

On Windows, in PowerShell: irm https://dfmt.datoria.ai/install.ps1 | iex. The Install page covers the supported platforms, PATH, upgrading and uninstalling.

dfmt is free and needs no account or sign-in.

2. Run it in your project

cd your-project
dfmt

The first time, there is no project setup yet, so dfmt says so and opens the setup screen. It reads the SQL under the folder, and proposes a setup:

• which dialect each folder of SQL is written in (PostgreSQL, Snowflake, T-SQL, …),
• what runs its files (psql, sqlcmd, Flyway, dbt, or nothing at all),
• the variables those files read, if any.

Check the proposal, change what is wrong, and press s to save it. The answer goes into a file called dlab.json at the root of the project. Commit it: it is how your team and your CI get the same result as you.

dfmt asks rather than guesses because a wrong guess does not fail. A parser that recovers from errors accepts almost any input under almost any dialect, so a guessed dialect produces a confident layout of a query you did not write.

When you leave the screen, dfmt carries on with what you asked for. Bare dfmt is a dry run: it formats every file in memory, says what would change, and writes nothing.

3. Look, then format

dfmt diff          # what would change; a browser of the changes in a terminal
dfmt write         # format in place, touching only files that change
dfmt check         # exit 1 if anything needs formatting (for CI)

Each takes paths too: dfmt write models/staging formats one folder, dfmt diff q.sql one file. dfmt format q.sql prints one file formatted to stdout and writes nothing.

4. Tune it

dfmt setup

dfmt setup opens the same screen whenever you want it. Its how it is formatted side shows the options for your project, keyword case, line width, comma style and the rest, applied live to your own files as you change them. Save with s and every command uses them from then on.

To set one option without the screen:

dfmt options --set keywordCase=upper

dfmt options lists every option with this project's value and where it came from. The Configuration Reference lists them all, and the Options, in order pages show each one with examples.

What dlab.json holds

A project with one dialect needs little more than its name:

{
  "dialect": "postgres",
  "include": [
    "**.sql",
    "**.pgsql",
    "**.pg"
  ],
  "exclude": [],
  "dfmt": {
    "keywordCase": "upper"
  }
}

The include list is the dialect's own file suffixes: PostgreSQL tooling also uses .pgsql and .pg, while most dialects only use .sql. Formatting options go under "dfmt", and only the ones you changed are written: the rest follow dfmt's defaults, so the project picks up better defaults when dfmt improves. A key that is not an option is an error, reported before any file is read.

dfmt finds dlab.json by walking up from the file it is formatting, so the folder that holds it is the project root. The file is named for the toolkit dfmt is part of rather than for dfmt: the project setup in it is shared by every Datoria tool, so you set a project up once.

In a script or a CI job, where there is no screen to answer, write the setup in one line instead:

dfmt setup --dialect postgres

What formatting does to your SQL

dfmt parses each statement into a syntax tree and lays the tree out under your options and the line width. It works from the structure, not the text, so a one-line query and a hand-wrapped one come out the same.

Given this on one line:

with first_stage as (select id, name, created_at from raw_customers where created_at > '2024-01-01'), second_stage as (select id, name from first_stage where name is not null) select * from second_stage

dfmt produces:

with
    first_stage as (
        select id, name, created_at
        from raw_customers
        where created_at > '2024-01-01'
    ),

    second_stage as (select id, name from first_stage where name is not null)
select *
from second_stage

The first CTE is too wide for one line, so it breaks clause by clause. The second fits, so it stays on one line. That rule applies everywhere: what fits stays on its line, what does not breaks.

dfmt also checks its own work. The output must hold the same tokens, in the same order, as the input. A run that would drop or reorder any of your SQL fails instead of writing.

A dbt project

dfmt finds a dbt project by its dbt_project.yml and reads the dialect from the adapter type in your profiles.yml (in the project folder or in ~/.dbt/). Each model is formatted as a template: dfmt runs the Jinja itself, lays out the SQL that comes out, and puts every {{ ref() }} and {% if %} back where you wrote it.

You do not need dbt installed to format. One exception: if the project has a packages.yml, its packages must be installed, because a model can call their macros. Run dbt deps once, locally and in CI, before dfmt. dfmt stops with a message when they are missing rather than format the models as if those macros produced nothing.

A model written like this:

with
    repository_teams as (
        select
        {% if var('flag', true) %}
            *
        from {{ ref('t1') }}
        {% else %}
            a, b
        from {{ ref('t2') }}
        {% endif %}
    )
select *
from repository_teams

comes back with the SQL laid out and every tag kept exactly as written:

with
    repository_teams as (
        select
        {% if var('flag', true) %}
            * from {{ ref('t1') }}
        {% else %}
            a, b from {{ ref('t2') }}
        {% endif %}
    )
select *
from repository_teams

The {% else %} branch is formatted too, even though flag is true and its SQL never ran. dfmt gives every branch its own pass. dbt Integration covers the rest.

To print one model formatted, name it:

dfmt format --model stg_orders

Runners

dbt is one runner. dfmt also reads files the way Flyway, schemachange and Qlik send them, and scripts written for a database's own client (psql, sqlcmd, SQL*Plus, the db2 command line and others). It finds most runners from their project files, and you can name one per folder in dlab.json. See Runners.

Only what changed

On a branch, format or check just the files you touched:

dfmt write --changed-since main
dfmt check --changed-since HEAD~1

The changed files are filtered through your include and exclude. If git cannot resolve the ref, check exits 2 instead of reporting a pass for files it never looked at.

When dfmt will not format a file

dfmt leaves a file untouched, and says why, rather than guess:

• No dialect. There is no dlab.json and no project above the file that names one. Run dfmt or dfmt setup in a terminal to set the project up.
• A Jinja file with no project to run it. {% if x %} handed to an SQL parser does not fail; it reads as operators and names and gives a confident parse of a query nobody wrote. dfmt reads a template through the project that runs it (dbt, schemachange), looking upward from the file, and leaves the file as it is, with a warning, if there is none.

dfmt check counts both as not assessed, and fails. A file nobody could check is not a file that passed.

Telemetry

Release builds of dfmt send one anonymous usage event per run: counts and timings, never file names or SQL. Set DLAB_TELEMETRY=0 or DO_NOT_TRACK=1, or put "telemetry": false in dlab.json, to turn it off. The Telemetry page lists every field.