Introduction

sqlsift catches broken SQL before it reaches production, without a database.

sqlsift reads your schema (CREATE TABLE files, migrations, structure.sql, a pg_dump --schema-only dump, …) and checks your raw SQL queries against it: missing tables, typo'd columns, type mismatches, wrong INSERT arity, ambiguous columns. It runs offline in milliseconds, so it fits in pre-commit hooks, CI and your editor.

-- schema.sql
CREATE TABLE users  (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email TEXT UNIQUE);
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total NUMERIC(10, 2));

-- queries/report.sql
SELECT u.naem, o.total
FROM users u
JOIN orders o ON o.user_id = u.email
WHERE u.id = 'abc';

$ npx sqlsift-cli check --schema schema.sql queries/report.sql
error[E0002]: Column 'naem' not found in table 'users'
  --> queries/report.sql:1:10
    |
  1 | SELECT u.naem, o.total
    |          ^^^^
    = help: Did you mean 'name'?

error[E0007]: JOIN condition type mismatch: integer vs text
  --> queries/report.sql:3:18
    |
  3 | JOIN orders o ON o.user_id = u.email
    |                  ^^^^^^^^^
    = help: JOIN condition should compare compatible types. Consider using explicit CAST.

error[E0003]: Type mismatch: cannot compare integer with text
  --> queries/report.sql:4:7
    |
  4 | WHERE u.id = 'abc';
    |       ^^^^
    = help: Types are not implicitly compatible. Consider using explicit CAST.


Found 3 error(s), 0 warning(s) in 1 file(s)

Want to see it first? The playground runs sqlsift in your browser via WebAssembly; your SQL never leaves the page.

Why sqlsift?

Raw SQL is usually only checked when it runs. Rename a column in a migration and a query in some other file breaks silently until it hits staging, or production. sqlsift closes that gap:

• No database needed. No Docker, no connection string, no test fixtures. Just your .sql files.
• Knows your schema. It understands CREATE TABLE, ALTER TABLE, views, enums and migration directories, and keeps track of what each query can see (CTEs, subqueries, LATERAL, aliases).
• Helpful diagnostics. Source spans plus "did you mean" suggestions for typos.
• Runs everywhere. CLI, GitHub Actions and SARIF code scanning, and a language server with a VS Code extension.
• Fast. Written in Rust; checks a typical project in milliseconds.
• PostgreSQL, MySQL and SQLite dialects, plus Snowflake, BigQuery, Redshift and Databricks in alpha.

How it compares

sqlsift complements these tools: keep your formatter and migration linter, and add sqlsift to make sure the queries still match the schema.

Getting started

Run sqlsift init in your project: it finds the schema, the queries and the dialect, and writes a sqlsift.toml (see Quick start). Or start from one of the example projects for sqlc, Prisma, postgres.js, sqlx, plain SQL with migrations, MySQL and dbt.

Status

sqlsift is in early development (alpha). Diagnostics may change between versions. Feedback, bug reports and real-world SQL that sqlsift gets wrong are very welcome: please open an issue.

Installation

npm

Prebuilt binaries for macOS (x64, ARM64), Linux (x64, ARM64) and Windows (x64) are published to npm as sqlsift-cli, which provides the sqlsift command:

npm install -g sqlsift-cli
sqlsift --version

Or run it without installing:

npx sqlsift-cli check --schema schema.sql queries/*.sql

In a Node project you can add it as a dev dependency (npm install --save-dev sqlsift-cli) and call sqlsift from your package.json scripts.

Prebuilt binaries

Archives for every platform are attached to each GitHub release.

From source

With a Rust toolchain installed:

cargo install --git https://github.com/yukikotani231/sqlsift sqlsift-cli

The language server for editors is a separate binary:

cargo install --git https://github.com/yukikotani231/sqlsift sqlsift-lsp

Try it without installing

The playground runs sqlsift in your browser.

Quick start

This page walks through a first check, then saves the options in a configuration file so later runs are just sqlsift check.

The fast way: sqlsift init

In an existing project, let sqlsift find the schema, the queries and the dialect:

$ cd my-app
$ sqlsift init
Detected:
  schema    Prisma migrations: prisma/migrations (12 SQL files)
  dialect   postgresql (detected from prisma/schema.prisma: provider = "postgresql")
  queries   Prisma TypedSQL: prisma/sql/**/*.sql (8 files)
            sql`...` / $queryRaw`...` templates in .ts: src/**/*.ts (5 files)

Wrote sqlsift.toml

Running a first check...
  2 error(s), 0 warning(s) in 2 of 31 file(s)
    E0002 column-not-found           2

Next steps:
  - Run `sqlsift check` to see each diagnostic
  ...

init recognizes sqlc configurations, Prisma, Supabase, Drizzle, Flyway, golang-migrate, sqlx, goose and dbmate migrations, Rails structure.sql and other schema dumps, dbt projects, plain .sql query files and SQL in TypeScript / JavaScript tagged templates. It writes a commented sqlsift.toml (never over an existing one unless you pass --force; --dry-run only prints it) and runs a first check. Review the file, then use sqlsift check from now on. See sqlsift init for what is detected and how.

The rest of this page does the same by hand.

1. Point sqlsift at your schema and queries

sqlsift needs two things: the SQL that defines your schema, and the query files to check.

# A single schema file
sqlsift check --schema db/schema.sql queries/*.sql

# Several schema files
sqlsift check -s db/users.sql -s db/orders.sql queries/*.sql

# A directory of migrations, applied in filename order
sqlsift check --schema-dir db/migrations 'queries/**/*.sql'

Glob patterns are expanded by sqlsift itself, so quote them when you want ** to work regardless of your shell.

If every query matches the schema, sqlsift prints a one-line summary and exits with code 0. Otherwise it prints each problem with its location and a hint, and exits with code 1.

Not sure where your schema comes from? See Loading your schema for Prisma, Rails, sqlx, Flyway, dbmate and pg_dump.

2. Pick the dialect

PostgreSQL is the default. For MySQL or SQLite (or, in alpha, Snowflake, BigQuery, Redshift or Databricks), pass --dialect:

sqlsift check --dialect mysql --schema schema.sql queries/*.sql

3. Save the options in sqlsift.toml

Create sqlsift.toml in your project root:

schema_dir = "db/migrations"
files = ["queries/**/*.sql"]
dialect = "postgresql"

Now sqlsift check with no arguments checks every query file. sqlsift looks for sqlsift.toml in the current directory and its parents, and the VS Code extension reads the same file. See the configuration reference for every key, and the example projects for a ready-made sqlsift.toml for sqlc, Prisma, postgres.js, sqlx, MySQL and dbt projects.

4. Decide what should fail the build

Rules that report queries the database rejects (correctness) are errors by default, rules for valid but almost certainly wrong queries (suspicious) are warnings, and stricter rules (restriction) are off until you enable them. To report a rule without failing the check, make it a warning:

[rules]
ambiguous-column = "warn"

Read Rules and levels for categories and command-line overrides, and Suppressing diagnostics for one-off exceptions in a query file.

5. Run it everywhere

• In CI: CI and GitHub Actions
• Before each commit: Pre-commit hooks
• While you type: Editors

Loading your schema

sqlsift never connects to a database. It builds an in-memory picture of your schema from SQL files: CREATE TABLE, CREATE VIEW, CREATE TYPE ... AS ENUM and ALTER TABLE statements, applied in order.

Schema files and directories

Both can be combined. Statements later in the order see the effect of earlier ones, so a migration that renames a column is reflected in the final schema.

Use it with your stack

sqlsift only needs SQL files for the schema, so it works with whatever produces them.

The example projects show complete setups for sqlc, Prisma TypedSQL, postgres.js, plain SQL migrations, MySQL and dbt.

Migrations: only the "up" direction

When reading migrations, sqlsift applies only the "up" direction:

• --schema-dir skips rollback files: *.down.sql (sqlx, golang-migrate) and Flyway undo files U<version>__*.sql.
• In any schema file, everything after a down marker (up to the next up marker) is ignored: dbmate's -- migrate:down / -- migrate:up, goose's -- +goose Down / -- +goose Up and sql-migrate's -- +migrate Down / -- +migrate Up.
• Files passed explicitly with --schema are always loaded, even if their name looks like a rollback.

What sqlsift understands

• CREATE TABLE with column types, NOT NULL, defaults, primary keys, foreign keys, UNIQUE and CHECK constraints
• SERIAL, GENERATED ... AS IDENTITY and AUTO_INCREMENT columns (they count as having a default)
• CREATE VIEW and CREATE MATERIALIZED VIEW, with column names and types inferred from the query
• CREATE TYPE ... AS ENUM, also schema-qualified (CREATE TYPE billing.state AS ENUM ..., columns of type public.mood), and MySQL inline ENUM(...) columns
• CREATE UNLOGGED TABLE, CREATE TABLE ... AS SELECT ... WITH [NO] DATA and SELECT ... INTO t
• SET search_path TO ..., for the rest of the file it is in
• COPY ... FROM stdin data blocks in pg_dump output are skipped
• ALTER TABLE: ADD / DROP / RENAME COLUMN, ADD CONSTRAINT, RENAME TO

Statements sqlsift doesn't model (functions, triggers, domains, grants, …) are skipped, and the rest of the file is still loaded. Problems while loading the schema (such as an ALTER TABLE on a table that doesn't exist) are reported as warnings on stderr by both check and schema.

Inspecting the loaded schema

When a query is flagged unexpectedly, check what sqlsift actually understood from your schema. sqlsift schema takes the same schema options as check (--schema, --schema-dir, --config, --dialect) and falls back to sqlsift.toml:

$ sqlsift schema --schema-dir migrations
Schema Information:
==================
Dialect: postgresql
Schema files:
  migrations/001_init.sql

Schema: public
  Table: users
    - id integer NOT NULL PRIMARY KEY DEFAULT nextval('users_id_seq'::regclass)
    - name text NOT NULL
    - feeling mood NULL
  View: user_names
    - id integer
    - name text
  Materialized view: user_count
    - n bigint

Enum types:
  mood: 'sad', 'ok', 'happy'

Objects are listed per schema in definition order. sqlsift schema --format json prints the same information as JSON; see Output formats.

Checking queries

sqlsift check parses each query file, resolves every table and column reference against the schema, and infers expression types to find mismatches.

Choosing files

Query files come from the command line or from files in sqlsift.toml. Both accept glob patterns; ** matches any number of directories. Directories a pattern matches are skipped ('queries/**/*' checks the files under queries/); a directory named on its own is an error.

sqlsift check 'queries/**/*.sql'

files = ["app/queries/**/*.sql", "db/reports/*.sql"]

To skip files, add ignore patterns (or --ignore on the command line). They apply to files from both sources:

ignore = ["queries/archive/**", "**/*.generated.sql"]

A pattern that matches a directory skips everything below it. Patterns in sqlsift.toml are relative to the file's directory; --ignore patterns are relative to the current directory and add to the file's list.

Reading from stdin

Pass - as the file name to read a single query from stdin. --stdin-filename sets the file name shown in diagnostics (default <stdin>):

git show :queries/users.sql | sqlsift check -s schema.sql --stdin-filename queries/users.sql -

This is how editors and pre-commit hooks can check content that isn't saved on disk.

File encoding

Query and schema files are read as UTF-8. A byte order mark at the start of a file (written by many Windows editors, SSMS and DBeaver exports) is ignored, both in files and on stdin. Columns in diagnostics don't count it, matching what an editor shows.

Very long expressions, such as generated WHERE id = 1 OR id = 2 OR ... filters with tens of thousands of terms, are fine: analysis runs with a large stack.

What a query can see

sqlsift follows SQL's visibility rules rather than just matching names:

• Table aliases, CTEs (including recursive CTEs) and derived tables
• Correlated subqueries in WHERE, SELECT and HAVING
• LATERAL vs non-LATERAL subqueries in FROM
• JOIN ... USING and NATURAL JOIN columns
• ORDER BY references to SELECT aliases (also in HAVING with MySQL and SQLite, which allow it; PostgreSQL doesn't)
• UPDATE ... FROM and DELETE ... USING
• Table-valued functions in FROM (for example generate_series)

DDL inside query files

Query files can create their own tables. CREATE [TEMP | UNLOGGED] TABLE, CREATE TABLE ... AS SELECT [WITH [NO] DATA], SELECT ... INTO [TEMP] t, CREATE VIEW, ALTER TABLE and DROP statements in a query file apply to the later statements of that file only:

CREATE TEMP TABLE recent_orders AS
SELECT id, user_id FROM orders WHERE created_at > now() - interval '7 days';

SELECT user_id, count(*) FROM recent_orders GROUP BY user_id;  -- OK

If a CREATE TABLE or CREATE VIEW can't be parsed, the "table not found" errors for it later in the file say so.

SET search_path TO analytics, public (also SET LOCAL search_path, SET search_path = ...) is followed for the rest of the file: unqualified names are looked up in the listed schemas, in order, and tables created without a schema go in the first one. SET search_path TO DEFAULT goes back to the default schema.

psql scripts

With the PostgreSQL dialect, files written for psql are accepted:

• Backslash meta-commands (\set, \i, \connect, \if, …) are skipped.
• \g, \gset and \gx end a query like ;.
• :var and :'var' interpolations are treated as untyped placeholders, and :"var" as an identifier whose name sqlsift can't know, so no "not found" diagnostic is reported for it.
• The data of a COPY ... FROM stdin; (the lines up to \., as in pg_dump output and seed files) is skipped, in query files and schema files.

MySQL syntax

With the MySQL dialect, these are accepted in schema and query files:

• Versioned comments (/*!50001 ... */, as written by mysqldump) are read as SQL, the way MySQL runs them. Views in a dump are loaded.
• ALGORITHM = ..., DEFINER = ... and SQL SECURITY ... in CREATE VIEW are ignored.
• INSERT ... SET col = value, ... is checked like INSERT ... (col, ...) VALUES (value, ...), including ON DUPLICATE KEY UPDATE.
• Index hints (USE, FORCE and IGNORE INDEX/KEY), STRAIGHT_JOIN and the SELECT modifiers (SQL_CALC_FOUND_ROWS, SQL_NO_CACHE, HIGH_PRIORITY, DISTINCTROW, ...) are ignored.

dbt and Jinja templates

dbt models are Jinja templates, not plain SQL. With --templating jinja (or templating = "jinja" in sqlsift.toml) sqlsift masks the template syntax before checking a query file. This is turned on automatically when a dbt_project.yml is in the current directory, in the directory of sqlsift.toml, or in a directory above the query file (so sqlsift check -s schema.sql 'analytics/models/**/*.sql' works from a monorepo root); set templating = "none" to turn it off. When a file with {{ or {% fails to parse without templating, the error suggests --templating jinja.

{{ config(materialized='incremental') }}

select c.id, c.frist_name, o.amount       -- E0002: 'frist_name' is checked against customers
from customers c
join {{ ref('stg_orders') }} o on o.customer_id = c.id   -- o.amount: not reported
{% if is_incremental() %}
where c.id > (select max(customer_id) from {{ this }})
{% endif %}

• {# comments #} and {% statements %} are skipped. The SQL inside a {% for %} block is checked once, as a single iteration (both loop.first and loop.last): separators such as {% if not loop.last %},{% endif %} and {{ ',' if not loop.last }} are dropped.
• Of {% if %} ... {% elif %} ... {% else %} ... {% endif %} only one branch is checked; the others are skipped. It is the first branch whose condition may be true: sqlsift evaluates is_incremental() (false, as on a model's first build), var('x', true) / var('x', false) (the default), target.type == 'snowflake' / != / in [...] (against the dialect's dbt adapter), {% set x = ... %} variables holding such a value, and not / and / or of these. Any other condition counts as true.
• The bodies of {% set x %}...{% endset %}, {% call %}...{% endcall %}, {% macro %}...{% endmacro %} (and test, materialization, docs blocks) are skipped. The body of {% raw %}...{% endraw %} is checked as SQL.
• {{ source('raw', 'customers') }} is the schema's table raw.customers when your schema has it, so its columns are checked.
• {{ ref('orders') }} and {{ source('shop', 'orders') }} have the columns of the model or source in dbt's catalog.json, when there is one (see Columns of models and sources below).
• Any other {{ ref(...) }} or {{ source(...) }}, {{ this }} and any other {{ ... }} where a table name is expected (after FROM, JOIN, INTO, UPDATE, USING) is a table whose columns sqlsift doesn't know: it is not reported as missing, and neither are columns qualified by it or unqualified columns that may come from it. Put the tables your models read from (the dbt sources) in the schema, or generate dbt's catalog, to get them checked.
• {{ ... }} as part of a name (total_{{ c }}, as {{ alias }}) is a name sqlsift doesn't know: an output column named by it has an unknown name, and a table alias built from it in a loop (left join o as {{ f }}_o) makes qualifiers of that shape (a_o.v) unknown.
• {{ ... }} at the start of a statement, after any comments ({{ config(...) }}), is skipped. When a query follows it directly, the macro may write the query's WITH clause, so table names in that query that aren't in the schema are not reported.
• {{ ... }} right after a complete expression is a macro that adds list items or a whole clause. In a SELECT list (select id {{ fivetran_utils.apply_source_relation() }} from t) it adds columns sqlsift doesn't know: the columns of that query are unknown to the queries that read it. Elsewhere ({{ dbt_utils.group_by(2) }}, partition by id {{ ... }}, a macro that writes a WHERE clause) it is skipped.
• {{ ... }} that is the whole body of a CTE or a subquery in FROM (with spine as ({{ dbt_utils.date_spine(...) }})) is a query whose columns sqlsift doesn't know.
• {{ var('x') }} where a table name is expected is the relation of the dbt project variable x when vars: in dbt_project.yml sets it to a {{ ref(...) }} or {{ source(...) }} that sqlsift knows (diagnostics name it var.x), and {{ var('x', ref('m')) }} is the default relation; otherwise it is a table with unknown columns.
• {{ ... }} as a type (x::{{ dbt.type_bigint() }}, cast(x as {{ ... }})) is a type sqlsift doesn't know.
• Any other {{ ... }}, and a string literal with one in it ('{{ var("start") }}'), is an untyped value, like a bind parameter: it is never a type mismatch.
• sqlsift:disable directives work in Jinja comments too: {# sqlsift:disable-file #}, {# sqlsift:disable E0002 #} (dbt users avoid -- comments, which end up in the compiled SQL).
• Everything else is checked as usual, and diagnostics point at the original file.

Macros that expand to other SQL can't be followed: a parse error on one shows the template tag (found: Jinja expression {{ dbt_utils.date_spine(...) }}). Use ignore or {# sqlsift:disable-file #} for such files.

Columns of models and sources from dbt's catalog (alpha)

dbt docs generate writes target/catalog.json, with the columns and types of every model, seed, snapshot and source as they are in the warehouse. When it exists in the dbt project (next to dbt_project.yml), sqlsift reads it, so the columns of {{ ref(...) }} and {{ source(...) }} are checked too, and schema files become optional:

$ dbt docs generate
$ sqlsift check 'models/**/*.sql'
error[E0002]: Column 'amount_usd' not found in table 'ref.stg_orders'
  --> models/marts/order_typo.sql:3:22

Use --dbt-catalog <PATH> (or dbt_catalog = "..." in sqlsift.toml) when the catalog is elsewhere, for example when target-path is changed or the catalog is downloaded from a CI artifact or dbt Cloud. It is read whatever the templating, and a configured catalog that is missing or not a dbt catalog is an error; one found in target/ that can't be read is a warning.

• {{ ref('orders') }} is the model, seed or snapshot named orders; diagnostics name it ref.orders. Its relation is also a table under its warehouse name (analytics.orders) for SQL that names it directly. Tables in your schema files take precedence over the catalog.
• {{ source('shop', 'orders') }} is the table orders of the source shop.
• A ref() of a model that isn't in the catalog (not built yet when the catalog was generated), a package-qualified ref('package', 'model'), a versioned ref('model', v=2), and a model name that two packages use, are tables with unknown columns, as without a catalog.
• Column types are the warehouse's type names. The common ones (integer, int64, number, varchar, string, timestamp_ntz, boolean, ...) are checked; others (variant, struct, arrays, geography types) are unknown types and never a type mismatch.

The catalog is a snapshot of the warehouse when dbt docs generate last ran: regenerate it after changing a model's columns, or sqlsift checks the models that use it against the old columns. The language server reads it when it starts. This support is alpha: how ref() names appear in diagnostics and how the catalog is found may change.

With Jinja templating, files in the dbt project's macros/, dbt_packages/ and target/ directories are skipped when they are matched by a glob pattern ('**/*.sql'); a file named on its own is still checked. Add other directories you don't want checked (analyses/, snapshots/) to ignore.

Set dialect to your warehouse. Snowflake, BigQuery, Redshift and Databricks are supported in alpha (see Dialects): their queries parse and names are checked, but many warehouse types and functions are not known yet, and are left unreported.

sqlc query files

sqlc query files are plain SQL with a -- name: comment before each query, so they can be checked as they are. Diagnostics name the query they are in:

-- name: ListPosts :many
SELECT id, titel FROM posts WHERE author_id = $1;

error[E0002]: Column 'titel' not found in table 'posts'
  --> queries/posts.sql:2:12
    |
  2 | SELECT id, titel FROM posts WHERE author_id = $1;
    |            ^^^^^
    = note: in query 'ListPosts'
    = help: Did you mean 'title'?

A statement belongs to the last -- name: <Name> :<command> comment before it. The name is the query_name field in JSON output, a logical location in SARIF output, and is added to the message in SARIF, github and editor diagnostics.

sqlc's named parameters are untyped placeholders, like $1:

-- name: ListPosts :many
SELECT id, title FROM posts
WHERE id > @after_id AND author_id = sqlc.arg(author_id)
  AND (title = sqlc.narg('title') OR sqlc.narg('title') IS NULL)
LIMIT @page_size;

-- name: GetPostsByIDs :many
SELECT id, title FROM posts WHERE id IN (sqlc.slice(ids));

• sqlc.arg(name), sqlc.narg(name) and sqlc.slice(name) (with the name bare or quoted) are placeholders in every dialect.
• @name is a placeholder with the PostgreSQL dialect only. PostgreSQL's @ operators are left alone: @>, <@, @@, and @ followed by a space (absolute value). With MySQL, @name stays a user variable (SET @x = 1), and with SQLite a bind parameter; sqlc supports @name for neither, so use sqlc.arg(name) there.
• Parameters in string literals, quoted identifiers and comments are left alone.

sqlx query files

The files sqlx's query_file! and query_file_as! read are plain SQL with $1 parameters (? with MySQL and SQLite), one statement each, so they are checked as they are, against the migrations sqlx::migrate!() applies:

schema_dir = "migrations"         # <timestamp>_name.sql; *.down.sql halves are skipped
files = ["queries/**/*.sql"]

sqlx's type overrides in column aliases (id AS "id!", status AS "status: Status") are ordinary quoted aliases. SQL written inline in Rust (query!("SELECT ...")) is not checked. See the sqlx example project.

aiosql and HugSQL query files

aiosql (Python) and HugSQL (Clojure) load named queries from .sql files. They are checked as they are, and diagnostics name the query they are in, as for sqlc:

-- name: get-user-by-id^
select id, nmae from users where id = :id;

error[E0002]: Column 'nmae' not found in table 'users'
  --> queries/users.sql:2:12
    |
  2 | select id, nmae from users where id = :id;
    |            ^^^^
    = note: in query 'get-user-by-id'
    = help: Did you mean 'name'?

The name comments are recognised in every dialect:

• aiosql: -- name: <name> with an optional parameter list and operation suffix: -- name: get-user-by-id^, -- name: list_posts(author), and $, !, <!, *!, #. The name is shown as written (get-user-by-id, not the Python method name get_user_by_id).
• HugSQL: -- :name <name> :<command> :<result> (-- :name get-user :? :1), -- :name- and -- :snip <name>.

A name comment starts a new statement, so queries need not end with ;. In a file with such comments, parameters are untyped placeholders, like $1:

HugSQL snippet bodies (after -- :snip) and queries with a Clojure expression (--~ ... or /*~ ... ~*/) are skipped: their SQL is only known at run time. :: casts (:id::int), :=, array slices (a[i:j]) and anything in string literals, quoted identifiers and comments are left alone. Without name comments, :name is a psql variable with the PostgreSQL dialect (see psql scripts) and a bind parameter with MySQL and SQLite.

The aiosql example is a complete project.

SQL in TypeScript and JavaScript

Files ending in .ts, .tsx, .js, .jsx, .mts, .cts, .mjs or .cjs are checked for SQL in tagged template literals, as used by postgres.js, Slonik, kysely, @vercel/postgres, Prisma and others. In Vue (.vue) and Svelte (.svelte) components, the <script> blocks are checked the same way:

const posts = await sql`
  SELECT id, titel FROM posts WHERE author_id = ${authorId}
`;

sqlsift check -s schema.sql 'src/**/*.ts'

Diagnostics point at the query's line and column in the source file. When a glob pattern matches TypeScript or JavaScript files in node_modules, dist, build, .next, .nuxt or .svelte-kit directories (installed packages and build output), they are skipped; name such a file, or start the pattern in such a directory ('dist/**/*.js'), to check it anyway.

Tags

Which templates are SQL is decided by their tag: embedded_sql_tags in sqlsift.toml lists the tags (default ["sql"]). The tag expression may be a chain of member accesses and calls, and matches when its last or its first identifier is one of the tags:

Type arguments are skipped (sql<boolean>`...`, prisma.$queryRaw<User[]>`...`). Tags per library:

embedded_sql_tags = ["sql", "$queryRaw", "$executeRaw"]

Statements and fragments

A template is checked only when it starts with a statement keyword (SELECT, WITH, INSERT, UPDATE, DELETE, VALUES, CREATE, ALTER, DROP, TRUNCATE, MERGE, ..., after comments and opening parentheses). Other templates written with the same tag are query fragments, such as kysely's sql`published = ${x}`, Prisma.sql`WHERE id > ${minId}` or sql`AND published`, and are skipped. (A misspelled statement keyword, as in sql`SELEC id FROM posts`, is still checked and reported as a syntax error.)

Each checked template is one statement:

• ${expr} is an untyped placeholder, like $1 (? for MySQL and SQLite), or a parenthesized list after IN.
• ${expr} where a table name is expected (after FROM, JOIN, INTO, UPDATE or TABLE) is a name sqlsift can't know, so no "not found" diagnostic is reported for it, as for psql's :"var".
• ${expr} after a value or a name is a fragment between clauses, and is left out: WHERE a = ${a} ${cond ? sql`AND b` : sql} ORDER BY id , or Prisma's SELECT id FROM users ${where} ``.
• postgres.js helpers are rows and columns sqlsift can't know: INSERT INTO users ${sql(user, 'name')}, INSERT INTO posts (a, b) VALUES ${sql(rows)} and UPDATE users SET ${sql(patch)} WHERE ... are checked without their column lists.
• Templates inside another SQL template's ${...} are fragments and are not checked on their own.
• SQL built by string concatenation, and untagged templates, are not checked.

--stdin-filename with a TypeScript, JavaScript, Vue or Svelte extension checks stdin the same way.

Directives

Suppression comments work as code comments (// sqlsift:disable-file, /* sqlsift:disable E0002 */) and as SQL comments inside a template. A sqlsift:disable comment on a line of its own applies to the next line of SQL (for a template starting on the next line, its first line of SQL); after a query, it applies to that line. A block comment followed by code on the same line is ignored. A sqlsift:disable-file comment applies to the whole file, also when it is written inside one of its templates.

Exit codes

Rules and levels

Every diagnostic comes from a rule. Each rule has a code (E0002), a name (column-not-found) and a category. You can refer to a rule by either its code or its name anywhere sqlsift takes a rule.

sqlsift rules lists them:

$ sqlsift rules
CODE   NAME                      CATEGORY     DEFAULT  DESCRIPTION
E0001  table-not-found           correctness  error    Referenced table does not exist in schema
E0002  column-not-found          correctness  error    Referenced column does not exist in table
E0003  type-mismatch             correctness  error    Type incompatibility in expression
E0004  potential-null-violation  correctness  error    Potential NOT NULL violation
E0005  column-count-mismatch     correctness  error    Column count doesn't match (INSERT, column aliases, subqueries)
E0006  ambiguous-column          correctness  error    Column reference is ambiguous across tables
E0007  join-type-mismatch        correctness  error    JOIN condition compares incompatible types
E0008  missing-required-column   correctness  error    INSERT omits a NOT NULL column without a default
E0009  duplicate-name            correctness  error    Table alias or CTE name given twice in one query
E0010  duplicate-target-column   correctness  error    Column given twice in an INSERT column list or UPDATE SET
E0011  position-out-of-range     correctness  error    ORDER BY / GROUP BY position is not in the select list
E0012  misplaced-aggregate       correctness  error    Aggregate or window function where it is not allowed
E0013  generated-column-write    correctness  error    INSERT or UPDATE gives a value to a generated column
E0014  unmatched-conflict-target correctness  error    ON CONFLICT columns match no unique constraint
E0015  distinct-order-by         correctness  error    SELECT DISTINCT ordered by a column it doesn't select
E0016  grouping-error            correctness  error    Column must appear in GROUP BY or be used in an aggregate
E0017  value-out-of-range        correctness  error    Literal too long or out of range for the column type
E0018  null-comparison           suspicious   warn     Comparison with NULL using = or <> is never true
E0019  not-in-with-nulls         suspicious   warn     NOT IN over a nullable subquery column
E0020  outer-column-in-subquery  suspicious   warn     IN subquery selects a column of the outer query
E0021  missing-join-condition    suspicious   warn     Tables in FROM with no condition linking them
E0022  constant-condition        suspicious   warn     Condition that is always or never true from the schema
E0023  outer-join-filtered       suspicious   warn     WHERE condition turns an outer join into an inner join
E0024  unfiltered-write          restriction  off      UPDATE or DELETE without WHERE
E0025  insert-without-columns    restriction  off      INSERT without a column list
E0026  select-star               restriction  off      SELECT * in a query's result columns
E0027  wrong-argument-type       correctness  error    Function or operator given an argument type it doesn't take
E0028  limit-without-order-by    restriction  off      LIMIT or OFFSET without ORDER BY
E1000  parse-error               correctness  error    SQL could not be parsed

Each rule has its own page with examples under Rules.

Levels

A rule is off, warn or error:

• error: reported, and makes sqlsift check exit with 1
• warn: reported, but doesn't fail the check (unless you set --max-warnings)
• off: not reported

Categories

Like oxlint, every rule belongs to a category that sets its default level:

Current rules are in correctness (errors), suspicious (warnings) and restriction (off), as sqlsift rules shows above.

Changing levels

In sqlsift.toml:

[rules]
E0008 = "warn"             # by code...
ambiguous-column = "off"   # ...or by name

[categories]
suspicious = "error"

On the command line, -A (allow, i.e. off), -W (warn) and -D (deny, i.e. error) take a rule code, a rule name or a category, and can be repeated:

sqlsift check -W ambiguous-column -A E0008 queries/*.sql

A rule's own level wins over its category's, and command-line flags win over sqlsift.toml. disable = ["E0006"] in the file is shorthand for E0006 = "off" under [rules].

Ratcheting warnings

Rolling out a rule on an existing codebase? Make it a warning and cap the number of warnings, so the backlog can only shrink:

sqlsift check -W missing-required-column --max-warnings 12

--max-warnings <N> (or max_warnings = N in sqlsift.toml) makes the check fail when more than N warnings are reported. 0 fails on any warning without turning warnings into errors.

Suppressing diagnostics

Sometimes a query is right and sqlsift is wrong, or a legacy file isn't worth fixing yet. There are three levels of suppression, from narrowest to widest.

If sqlsift reports valid SQL, please also open an issue with a minimal schema and query so it can be fixed.

One line: sqlsift:disable

A -- sqlsift:disable comment on its own line applies to the next line; at the end of a line, it applies to that line.

-- Suppress a specific rule on the next line
-- sqlsift:disable E0002
SELECT legacy_col FROM users;

-- Suppress on the same line
SELECT legacy_col FROM users; -- sqlsift:disable E0002

-- Suppress several rules (codes or names)
SELECT bad_col FROM missing_table; -- sqlsift:disable E0001, column-not-found

-- Suppress all rules on the next line
-- sqlsift:disable
SELECT bad_col FROM missing_table;

One file: sqlsift:disable-file

-- sqlsift:disable-file E0006, missing-required-column

-- or turn off every rule for this file, including parse errors (E1000)
-- sqlsift:disable-file

A disable-file comment may appear anywhere in the file (conventionally at the top) and applies to every line, before and after it. It is separate from sqlsift:disable: it never acts as a next-line directive, and sqlsift:disable never disables a rule for the whole file.

In TypeScript, JavaScript, Vue and Svelte files, write the directives as code comments (// sqlsift:disable-file, /* sqlsift:disable E0002 */) or as SQL comments inside a template; a disable-file comment inside one template applies to the whole file (see SQL in TypeScript and JavaScript).

Whole files, without editing them: ignore

To skip generated or archived files entirely, list them in ignore in sqlsift.toml, or pass --ignore:

ignore = ["queries/archive/**", "**/*.generated.sql"]

Ignored files aren't parsed at all, and editors show no diagnostics for them. See Checking queries for the pattern rules.

An existing backlog: baseline

To adopt sqlsift on a project that already has many diagnostics, record them in a baseline file and get reports only for new ones. (In GitHub Actions, the action's diff-base input does this for each pull request without a committed file.)

sqlsift check --write-baseline

This writes every current diagnostic (errors and warnings) to sqlsift-baseline.json and exits 0. Commit the file and point sqlsift at it, in sqlsift.toml (so the language server uses it too) or with --baseline <PATH>:

baseline = "sqlsift-baseline.json"

Diagnostics in the baseline are not printed and don't count toward the exit code, --max-errors or --max-warnings. sqlsift check prints how many were hidden, and a note when baseline entries no longer occur: the problem was fixed, or the entry's file was deleted, renamed or is now ignored. Re-run sqlsift check --write-baseline to remove them. That note never fails the check. Entries of files that still exist but weren't checked in this run (say, a pre-commit hook checking only changed files) are never reported as stale.

A diagnostic matches a baseline entry by its file (relative to the baseline file, with symbolic links resolved), rule code and the statement it is in. The statement is compared ignoring comments, whitespace (including whitespace around operators, commas and parentheses), the case of keywords and unquoted identifiers, and quotes around a lowercase identifier ("users" is users); string literals must be unchanged. In TypeScript and JavaScript files only the SQL template counts, not the code around it. So adding lines or statements elsewhere in the file, or running a formatter over the statement, keeps the match. Each entry hides one diagnostic, so the same mistake made twice in one statement needs two entries.

When a statement with baselined problems is changed, its diagnostics that are left are still matched by rule code and message against the file's unmatched entries: fixing one of several problems in a statement doesn't make the others new. The entries store no line numbers and are sorted by file, rule, statement hash and message, so adding lines to a file doesn't change the baseline (and doesn't cause merge conflicts in it).

--write-baseline records the diagnostics of the files it checks, and keeps the existing entries of other files that still exist and aren't ignored, printing how many were kept and removed. So it can be run over a few files, and two configurations can share one baseline file. To start over, delete the file first.

Baseline files written by sqlsift 0.1 (format version 1) are still read; --write-baseline writes version 2.

Project-wide: rule levels

To turn a rule off everywhere, set its level instead; see Rules and levels.

Dialects and SQL support

Dialects

The data warehouse dialects are alpha: queries parse with the warehouse's syntax and table and column names are checked, but many warehouse types (VARIANT, STRUCT, ...) and functions are not known yet. Like everything sqlsift can't work out, they are left unreported rather than guessed. For dbt projects on a warehouse, see dbt and Jinja templates.

In BigQuery, the fields of a STRUCT and of an UNNEST element are not modelled, so they are never reported (struct_col.* and f(x).* have unknown columns), and a query that uses UNNEST doesn't report unqualified column names (they may be fields of the element). Table names are matched case-insensitively. Quote a wildcard table with backticks (`dataset.events_*`): unquoted, it doesn't parse yet.

When the schema names projects (CREATE TABLE `my-project.dataset.t`), a table of another project is unknown and never reported, even if a dataset of the schema has a table with its name; a schema that names no project matches any project. In scripts, variables declared with DECLARE are known names in the statements after it. A GROUP BY, HAVING, QUALIFY or ORDER BY name that is an output column (SELECT a.uid AS uid ... GROUP BY uid, or the implicit name of a.uid) is that column, as in BigQuery. UNPIVOT output columns are checked; PIVOT columns are unknown. ANY TYPE function parameters, << / >>, field access on function results (f(x).field) and typed array literals (ARRAY<STRUCT<a INT64>>[...]) are supported; procedural blocks (FOR ... IN, BEGIN ... EXCEPTION, EXECUTE IMMEDIATE) don't parse yet.

Set it once in sqlsift.toml with dialect = "mysql".

Statements

• SELECT, INSERT, UPDATE, DELETE, including RETURNING
• JOINs (INNER, LEFT, RIGHT, FULL, CROSS, NATURAL) with ON / USING
• CTEs (WITH), including recursive CTEs
• Subqueries: IN / EXISTS, derived tables in FROM, scalar subqueries
• UNION / INTERSECT / EXCEPT, with column count and type checks
• Window functions (OVER, PARTITION BY, frames), aggregate FILTER
• GROUPING SETS, CUBE, ROLLUP, DISTINCT ON
• Expressions: CASE, CAST, EXTRACT, JSON operators, AT TIME ZONE, ARRAY, …

Schemas and qualified names

Tables, views and enum types are kept per schema, so auth.users and public.users are two tables (as in Supabase projects). Names can be written as table, schema.table or database.schema.table, and columns as table.column, schema.table.column or database.schema.table.column; the database part is not checked.

• PostgreSQL: an unqualified name is looked up in the search_path (public unless a SET search_path in the file changes it). A table in a schema off the path must be qualified: SELECT * FROM daily_signups is E0001 with a hint to write analytics.daily_signups. schema.table.column must name a table of the FROM clause, so auth.users.email is reported when FROM users is public.users.
• MySQL: databases act as schemas (shop.orders, USE shop; in schema and query files). The current database is chosen when the query runs, so an unqualified name is found in any database, and a database name the schema doesn't use (a dump without USE) matches the tables created without one.
• SQLite: main.users and temp.users refer to the tables of the schema; tables created as archive.users belong to the attached database archive.

A backtick-quoted name containing dots (`shop.orders`) is split into its parts. A double-quoted one ("a.b") stays one name, as in PostgreSQL.

Type checking

sqlsift infers expression types to report E0003, E0007, E0017 and E0027. It currently understands:

• Comparisons and arithmetic in WHERE, SELECT, JOIN ... ON, nested expressions
• INSERT ... VALUES and UPDATE ... SET values against the column type
• Numeric widening (SMALLINT → INTEGER → BIGINT → NUMERIC)
• String literals coerce to the other side's type like in the database (created_at > '2024-01-01', id = '42' are fine), while impossible values are still reported (id = 'abc')
• Date and time arithmetic (now() - interval '7 days', placed_on + 7)
• CAST, and the return types of common functions (COUNT, SUM, AVG, UPPER, LENGTH, COALESCE, …)
• CASE branch consistency and result type
• Enum values for PostgreSQL enum types and MySQL inline ENUM(...), with "did you mean" suggestions
• Column types through CTEs, subqueries, views and CREATE TABLE ... AS
• Argument types of built-in functions and LIKE (E0027), and literal lengths and ranges against the column type (E0017)

Anything sqlsift can't infer is treated as unknown and never reported, so missing type support leads to missed errors, not false positives.

Known limitations

• Function bodies and stored procedures are skipped, not analyzed.
• SQL embedded in application code is supported for tagged template literals in TypeScript, JavaScript, Vue and Svelte files (see SQL in TypeScript and JavaScript); strings in other languages (Python, Go, Rust query!("..."), …) are not. SQL those libraries read from .sql files (sqlc, sqlx query_file!, aiosql) is checked as it is; see the example projects.
• dbt / Jinja templates are masked, not rendered (see dbt and Jinja templates): macros aren't expanded, and the columns of {{ ref(...) }} models are unknown unless dbt's catalog.json is there (see Columns of models and sources, alpha).

Troubleshooting

A table or column "not found" that does exist

1. Run sqlsift schema with the same options (or the same sqlsift.toml) to see what sqlsift loaded. If the table is missing, the statement that creates it was probably skipped; skipped statements are listed as warnings on stderr.
2. Check the order of your schema files. With --schema-dir, files are applied in file-name order, so a migration named 10_add_column.sql runs before 2_create_table.sql. Zero-pad numeric prefixes.
3. Check the dialect. A MySQL schema parsed as PostgreSQL may be partly skipped.
4. In PostgreSQL, a table in a schema other than public must be schema-qualified (analytics.daily_signups) unless a SET search_path in the file puts its schema on the path, as in the database. See Schemas and qualified names.

"Pattern matched no files" (exit code 2)

Glob patterns are relative to the current directory on the command line, and to the directory of sqlsift.toml inside it. Quote patterns containing ** so your shell doesn't expand them first.

Seeing what sqlsift is doing

-v logs the files and configuration sqlsift uses to stderr; -vv adds debug output, such as which files were ignored.

Valid SQL reported as an error

That's a bug. Suppress it for now with a sqlsift:disable comment, and please open an issue with a minimal schema and query. The playground is handy for cutting the example down.

Example projects

The repository's examples/ directory has small, realistic projects for common stacks. Each has a schema (usually migrations), a few queries, a hand-written sqlsift.toml to copy, and a broken/ directory with intentional mistakes and the diagnostics they produce. They are checked in sqlsift's CI, so the setup they show keeps working.

A migration that breaks a query

postgres-migrations shows the case sqlsift is built for. Migration 0003 renames customers.name to full_name, and one report query still uses the old name:

error[E0002]: Column 'name' not found in table 'customers'
  --> broken/top_customers.sql:4:16
    |
  4 | SELECT c.id, c.name, sum(o.total) AS lifetime_value
    |                ^^^^
    = help: 'name' was renamed to 'full_name' in migrations/0003_rename_customer_name.sql:2

The query still parses and nothing else in the pull request touches it; without sqlsift it fails only once it runs against the migrated database. To get this in pull requests, check every query file in CI (see Re-check everything when the schema changes).

CI and GitHub Actions

GitHub Action

# .github/workflows/sqlsift.yml
name: SQL Lint
on: [push, pull_request]
jobs:
  sqlsift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: yukikotani231/sqlsift@main  # or pin a release tag
        with:
          schema: db/schema.sql           # or schema-dir: db/migrations
          files: queries/**/*.sql

Errors are shown as annotations on the pull request diff. All inputs are optional when you have a sqlsift.toml:

The exit-code output is 0 (clean), 1 (errors found) or 2 (configuration error).

Any CI, with plain commands

npx sqlsift-cli check --schema schema.sql 'queries/**/*.sql'

The exit code is non-zero when errors are found. Inside a GitHub Actions job that runs sqlsift itself (a make lint step, a script, a container), add --format github to get annotations on the pull request diff.

Report only what a pull request introduces

In a project that already has diagnostics, the ones a pull request adds are easy to miss among the old ones. With diff-base, the action checks the pull request's base too and reports only what is new, with no baseline file to commit or keep up to date:

on: [push, pull_request]
jobs:
  sqlsift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: yukikotani231/sqlsift@main
        with:
          schema-dir: db/migrations
          files: queries/**/*.sql
          diff-base: ${{ github.base_ref }}

A pull request that drops or renames a column fails with the queries it breaks, even when other queries already had problems. github.base_ref is empty outside pull requests, so pushes to main still report every diagnostic.

How it works: the action fetches the base (one commit, from the repository the workflow runs in), checks it out in a temporary git worktree, runs the same check there with --write-baseline into a temporary baseline file, and then checks the pull request against that baseline (see Baseline for how diagnostics are matched). On a pull_request event the checkout is the merge commit, so this compares the result of merging with the base branch as it is now. No token beyond the default one is needed, and pull requests from forks work the same way. The SARIF file (sarif-file) has only the new diagnostics too.

diff-base takes a branch or tag name (fetched from origin), or a commit SHA, e.g. ${{ github.event.pull_request.base.sha }}. Good to know:

• The base is checked with its own sqlsift.toml and the same inputs, run from the same directory. If it can't be checked at all (no sqlsift.toml or no query files there yet, a schema file named in schema that the pull request adds), the action prints a warning and reports every diagnostic. Prefer a glob or schema-dir for schema files, so that a new migration file doesn't make the base check fail.
• Diagnostics are matched by file, rule and statement, so a renamed query file's existing problems are reported as new.
• A committed baseline (baseline in sqlsift.toml) is not used in this mode: everything the base already reports is hidden anyway.

Rolling out a rule gradually

Make the rule a warning and cap the number of warnings with --max-warnings (or max_warnings in sqlsift.toml), so the backlog can only shrink:

sqlsift check -W missing-required-column --max-warnings 12

Adopting sqlsift on an existing codebase

When a project already has many diagnostics, record them in a baseline and fail CI only on new ones:

sqlsift check --write-baseline          # writes sqlsift-baseline.json; commit it

# sqlsift.toml
baseline = "sqlsift-baseline.json"

From then on sqlsift check (and the editor) hides the recorded diagnostics, and new ones fail the check as usual. When a recorded problem is fixed, sqlsift prints a note; re-run --write-baseline to shrink the file. See Baseline.

Re-check everything when the schema changes

sqlsift's main job is catching queries broken by a schema or migration change, and those query files usually aren't in the PR diff. The simplest setup is to always check every query file, as above: sqlsift checks hundreds of files in well under a second. If you only check changed files, check all of them whenever the schema changes:

on: pull_request
jobs:
  sqlsift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - id: changed
        env:
          BASE: ${{ github.event.pull_request.base.sha }}
        run: |
          changed=$(git diff --name-only --diff-filter=d "$BASE" HEAD)
          if grep -q '^db/' <<< "$changed"; then
            files='queries/**/*.sql'  # schema changed: check every query
          else
            files=$(grep '^queries/.*\.sql$' <<< "$changed" | tr '\n' ' ' || true)
          fi
          echo "files=$files" >> "$GITHUB_OUTPUT"
      - if: steps.changed.outputs.files != ''
        uses: yukikotani231/sqlsift@main
        with:
          schema-dir: db/migrations
          files: ${{ steps.changed.outputs.files }}

GitHub Code Scanning (SARIF)

Show results in the Security tab and as code scanning alerts:

jobs:
  sqlsift:
    runs-on: ubuntu-latest
    permissions:
      security-events: write
    steps:
      - uses: actions/checkout@v4
      - uses: yukikotani231/sqlsift@main
        with:
          schema: db/schema.sql
          files: queries/**/*.sql
          sarif-file: results.sarif
          fail-on-error: "false"
      - uses: github/codeql-action/upload-sarif@v3
        with:
          sarif_file: results.sarif

Pre-commit hooks

sqlsift is fast enough to run on every commit.

Plain Git hook

Check the staged version of each changed query file, not the working copy, by piping it through stdin:

#!/usr/bin/env bash
# .git/hooks/pre-commit (or .githooks/pre-commit with `git config core.hooksPath .githooks`)
set -euo pipefail

status=0
while IFS= read -r file; do
  git show ":$file" | sqlsift check --stdin-filename "$file" - || status=1
done < <(git diff --cached --name-only --diff-filter=ACM -- '*.sql' '*.ts' '*.tsx' '*.js' '*.jsx' '*.mts' '*.cts' '*.mjs' '*.cjs' '*.vue' '*.svelte')
exit $status

The pathspec covers .sql files and the files sqlsift checks for SQL in tagged template literals (TypeScript, JavaScript, Vue and Svelte), so a commit that only changes embedded SQL is still checked. If your project has no embedded SQL, keep just '*.sql', otherwise the hook runs on every commit that touches those files for nothing.

This uses the schema settings from sqlsift.toml. When a schema or migration file is staged, consider checking every query (sqlsift check) instead, since any of them may be affected.

pre-commit framework

With pre-commit, a local hook that checks all query files works well, because a run is fast:

# .pre-commit-config.yaml
repos:
  - repo: local
    hooks:
      - id: sqlsift
        name: sqlsift
        entry: npx --yes sqlsift-cli check
        language: system
        files: \.(sql|ts|tsx|js|jsx|mts|cts|mjs|cjs|vue|svelte)$
        pass_filenames: false

pass_filenames: false makes sqlsift check the files from sqlsift.toml, so a schema change re-checks every query.

As with the plain hook, files also matches the extensions of files with embedded SQL, so the hook runs when only those change. Drop them if your project has none, and make sure the files in sqlsift.toml include them (for example src/**/*.ts) so they are actually checked.

lefthook / husky

Any hook manager works the same way: run sqlsift check (or npx sqlsift-cli check) and let it read sqlsift.toml.

Editors

sqlsift ships a language server, sqlsift-lsp, that shows diagnostics as you type. It reads sqlsift.toml from the workspace root, so the editor reports the same problems as the CLI, and files matched by ignore get no diagnostics.

Besides .sql files, the server checks:

• TypeScript and JavaScript (typescript, typescriptreact, javascript, javascriptreact documents, or .ts, .tsx, .js, .jsx, .mts, .cts files): the SQL in tagged template literals whose tag is in embedded_sql_tags (default ["sql"]), as sqlsift check does. Files without such a template get no diagnostics, and hover and completion are only offered in SQL documents.
• dbt models: Jinja templates are masked in jinja-sql documents (the language the "dbt Power User" extension sets) and in any document with a dbt_project.yml in one of its parent directories, so a dbt project in a subdirectory of the workspace works too. An explicit templating in sqlsift.toml applies to every document.

VS Code

Install the sqlsift extension (sqlsift.sqlsift) from the Marketplace. Platform builds bundle the language server, so no extra setup is required. See editors/vscode for settings. It activates on SQL, jinja-sql, Jinja .sql files and, unless sqlsift.embeddedSql.enable is false, TypeScript and JavaScript files.

Other editors

Install the server:

cargo install --git https://github.com/yukikotani231/sqlsift sqlsift-lsp

Then register sqlsift-lsp (it talks LSP over stdin/stdout, with no arguments) as a language server for SQL files (and, to check embedded SQL, for TypeScript and JavaScript files).

Neovim (nvim-lspconfig)

vim.lsp.config('sqlsift', {
  cmd = { 'sqlsift-lsp' },
  filetypes = { 'sql' },
  root_markers = { 'sqlsift.toml', '.git' },
})
vim.lsp.enable('sqlsift')

Helix

# ~/.config/helix/languages.toml
[language-server.sqlsift]
command = "sqlsift-lsp"

[[language]]
name = "sql"
language-servers = ["sqlsift"]

Command line

sqlsift [OPTIONS] <COMMAND>

Commands:
  init    Detect the project layout and write sqlsift.toml
  check   Check SQL files against schema definitions
  impact  Show the queries a schema change breaks or fixes
  rules   List all rules with their category and default level
  schema  Display the schema sqlsift loaded (tables, views, enum types)
  parse   Parse SQL and display AST (for debugging)

Global options:
  -v, --verbose   Enable verbose logging to stderr (-vv for debug)
  -q, --quiet     Suppress summary/non-error output
  -h, --help      Print help
  -V, --version   Print version

sqlsift init

sqlsift init [OPTIONS] [DIR]

Arguments:
  [DIR]                  Project directory [default: current directory]

Options:
      --force            Overwrite an existing sqlsift.toml
  -d, --dialect <NAME>   SQL dialect to write instead of the detected one
      --no-check         Don't run a first check after writing sqlsift.toml
      --dry-run          Print the configuration to stdout instead of writing sqlsift.toml

Looks at the project, writes a commented sqlsift.toml in DIR, runs a first sqlsift check with it and prints a summary (diagnostics per rule) and the next steps. It never asks questions, so it also works in scripts. An existing sqlsift.toml is left alone unless --force is given. Directories of dependencies and build output (node_modules, target, vendor, dist, ...) and hidden directories are not searched.

When several schema sources are found, the most specific one is used and the others are written as comments. The dialect comes from the most specific source: the tool's own configuration first, then DATABASE_URL in .env.example / .env.sample / .env, database images in docker-compose.yml, database drivers in package.json, go.mod, Gemfile, Python requirements or Cargo.toml, and finally the syntax of the schema files; otherwise PostgreSQL. Disagreeing hints are printed. When the first check reports many diagnostics, init suggests a baseline.

Exit codes: 0 when the configuration was written (whatever the first check found), 2 when sqlsift.toml already exists (without --force) or the options are invalid.

sqlsift check

sqlsift check [OPTIONS] [FILES]...

Arguments:
  [FILES]...                SQL, TypeScript or JavaScript files to check (glob patterns supported; `-` reads stdin).
                            Defaults to `files` in sqlsift.toml.

Options:
  -s, --schema <FILE>       Schema definition file (repeatable)
      --schema-dir <DIR>    Directory containing schema files
      --ignore <PATTERN>    Skip query files matching a glob pattern (repeatable)
  -c, --config <FILE>       Path to configuration file [default: sqlsift.toml in the
                            current or a parent directory]
  -A, --allow <RULE>        Turn a rule or category off (alias: --disable)
  -W, --warn <RULE>         Report a rule or category as warnings
  -D, --deny <RULE>         Report a rule or category as errors
  -d, --dialect <NAME>      SQL dialect: postgresql, mysql, sqlite, snowflake, bigquery, redshift, databricks [default: postgresql]
      --templating <ENGINE> Query file templating: jinja (dbt models), none [default: jinja
                            when dbt_project.yml is in the current or the config file's
                            directory or above the query file, else none]
      --dbt-catalog <PATH>  dbt catalog.json with the columns of the models and sources
                            that ref() / source() name (alpha) [default:
                            target/catalog.json of the dbt project, if it exists]
  -f, --format <FORMAT>     Output format: human, json, sarif, github [default: human]
      --max-errors <N>      Maximum number of errors before stopping [default: 100, 0 = unlimited]
      --max-warnings <N>    Fail (exit 1) when more than N warnings are reported
      --baseline <PATH>     Baseline file of known diagnostics, which are not reported
      --write-baseline      Write every current diagnostic of the checked files to the
                            baseline file and exit 0, keeping the entries of other files
                            that still exist (--baseline, `baseline` in sqlsift.toml, or
                            sqlsift-baseline.json)
      --stdin-filename <PATH>
                            File name to report for the query read from stdin (`-`)

Command-line options override sqlsift.toml. -A, -W and -D accept a rule code (E0006), a rule name (ambiguous-column) or a category (suspicious), and can be repeated.

Exit codes: 0 no errors, 1 errors reported (or more warnings than --max-warnings), 2 usage or configuration error (including a missing or invalid baseline file). Diagnostics in the baseline don't count; see Baseline.

sqlsift impact

sqlsift impact [OPTIONS] [MIGRATIONS]...

Arguments:
  [MIGRATIONS]...           Migration (schema) files whose effect is shown (glob patterns
                            supported). Required unless --base is given.

Options:
      --base <REV>          Also the schema files added or changed since the current branch
                            left this git revision (e.g. origin/main), uncommitted and
                            untracked files included
      --references          Also list the lines that use a changed table or view without a
                            diagnostic changing (where to look when reviewing the change)
      --queries <PATTERN>   Query files to check (repeatable, glob patterns supported)
                            [default: `files` in sqlsift.toml]
  -s, --schema <FILE>       Schema definition file (repeatable)
      --schema-dir <DIR>    Directory containing schema files
      --ignore <PATTERN>    Skip query files matching a glob pattern (repeatable)
  -c, --config <FILE>       Path to configuration file
  -A, --allow <RULE>        Turn a rule or category off (alias: --disable)
  -W, --warn <RULE>         Report a rule or category as warnings
  -D, --deny <RULE>         Report a rule or category as errors
  -d, --dialect <NAME>      SQL dialect [default: postgresql]
      --templating <ENGINE> Query file templating: jinja, none
      --dbt-catalog <PATH>  dbt catalog.json (alpha)
  -f, --format <FORMAT>     Output format: human, json, sarif, github [default: human]

impact builds the schema twice, without and with the migrations, checks the query files against both and reports only the diagnostics that differ: those the change introduces and those it fixes. Problems the queries already had are not shown.

• A migration that is one of the schema files is left out of the "before" schema together with every schema file after it (files are applied in filename order). A migration that isn't one of them is applied after all schema files.
• Only query files that name a changed table or view are checked, so the run stays fast on large projects. Changes that can affect any query (enum types, the search path, functions the schema defines) check every file.
• A diagnostic whose message changes but stays at the same place (a different type in a type mismatch) counts as the same problem.

$ sqlsift impact --base origin/main
Impact of migrations/0042_rename_email.sql
  changed table public.users

error[E0002]: Column 'email' not found in table 'users'
  --> queries/users.sql:12:12
   ...
    = help: 'email' was renamed to 'email_address' in migrations/0042_rename_email.sql:1

The change introduces 1 problem(s) in 1 file(s) and fixes 0
Checked 3 of 41 query file(s); the others name no changed table or view

With --format json the output is {"migrations": [...], "changes": [{"name", "kind", "relation"}], "other_changes": bool, "introduced": [...], "resolved": [...]}, where introduced and resolved list files and diagnostics as check --format json does. sarif and github report what the change introduces.

With --references, the JSON output also has references: [{"file", "lines": [{"line", "uses": ["public.users", "public.users.email", ...]}]}].

Exit codes: 0 the change introduces no errors, 1 it does, 2 usage or configuration error.

sqlsift schema

sqlsift schema [OPTIONS] [FILES]...

Arguments:
  [FILES]...                Schema definition files (same as --schema; glob patterns supported)

Options:
  -s, --schema <FILE>       Schema definition file (repeatable)
      --schema-dir <DIR>    Directory containing schema files
  -c, --config <FILE>       Path to configuration file
  -d, --dialect <NAME>      SQL dialect: postgresql, mysql, sqlite, snowflake, bigquery, redshift, databricks [default: postgresql]
  -f, --format <FORMAT>     Output format: human, json [default: human]

Without schema arguments, the schema settings from sqlsift.toml are used. See Loading your schema.

sqlsift rules

Prints every rule with its code, name, category, default level and description. See Rules and levels.

sqlsift parse

sqlsift parse <FILE>

Prints the parsed syntax tree of a file. Useful when reporting a parse problem.

Configuration file

sqlsift check and sqlsift schema look for sqlsift.toml in the current directory and its parents, or use the file given with --config <FILE>. The language server reads it from the workspace root. Command-line options override values from the file. sqlsift init writes a commented starting point for your project.

# Schema
schema = ["db/schema/*.sql"]      # schema files (glob patterns supported)
schema_dir = "db/migrations"      # all .sql files under this directory, in file-name order

# Query files
files = ["queries/**/*.sql"]      # query files to check when none are given on the command line
ignore = ["queries/archive/**", "**/*.generated.sql"]  # query files to skip

dialect = "postgresql"            # postgresql, mysql, sqlite; alpha: snowflake, bigquery, redshift, databricks
templating = "jinja"              # jinja (dbt models) or none
dbt_catalog = "target/catalog.json"  # dbt catalog with the columns of ref() / source() (alpha)
format = "human"                  # human, json, sarif or github
max_warnings = 0                  # fail when more than this many warnings are reported
baseline = "sqlsift-baseline.json"  # known diagnostics that are not reported
embedded_sql_tags = ["sql", "$queryRaw"]  # template literal tags checked in .ts/.js files

# Rules
disable = ["E0006"]               # rules to turn off (same as `E0006 = "off"` below)

[rules]                           # per-rule level: "off", "warn" or "error"
E0008 = "warn"                    # by code...
ambiguous-column = "off"          # ...or by name

[categories]                      # level of every rule in a category
correctness = "error"

Keys

Paths

Relative paths and patterns in the file are resolved against the directory containing sqlsift.toml, so the same file works from any subdirectory.

Validation

Unknown keys produce a warning. Invalid dialect, templating or format values, unknown rules or categories and invalid levels are errors (exit code 2), with a suggestion when the name is close to a valid one.

Output formats

Choose a format with --format (-f) or format in sqlsift.toml. The machine-readable formats (json, sarif, github) write diagnostics to stdout and logs and the summary line to stderr, so stdout can be piped or redirected to a file. human output goes to stderr.

human (default)

Source excerpts with the problem underlined, plus a hint when sqlsift has one:

error[E0002]: Column 'naem' not found in table 'users'
  --> queries/report.sql:1:10
    |
  1 | SELECT u.naem, o.total
    |          ^^^^
    = help: Did you mean 'name'?

Colors are used only when stderr is a terminal and NO_COLOR is not set.

json

A single JSON document. Only files with diagnostics are listed; files is empty when everything passes.

{
  "files": [
    {
      "file": "queries/fetch.sql",
      "diagnostics": [
        {
          "code": "E0002",
          "kind": "ColumnNotFound",
          "severity": "error",
          "message": "Column 'user_id' not found in table 'users'",
          "help": "Did you mean 'id'?",
          "line": 3,
          "column": 15,
          "span": { "line": 3, "column": 15, "length": 7, "offset": 62 },
          "labels": []
        }
      ]
    }
  ]
}

line and column are 1-indexed (columns count characters); span.offset is the 0-indexed byte offset of the same position in the file, and span.length is in bytes. severity is error or warning. In sqlc query files, diagnostics also have a query_name field with the name from the query's -- name: comment; it is absent otherwise.

sarif

A SARIF 2.1.0 log with a single run containing the results for all files and a tool.driver.rules entry for every rule. A diagnostic in a named sqlc query has the query name as a logicalLocations entry (kind function) and at the end of its message text, as in (in query 'ListPosts'). Upload it to GitHub Code Scanning as shown in CI and GitHub Actions.

github

One GitHub Actions workflow command per diagnostic, which GitHub turns into annotations on the pull request diff:

::error file=queries/fetch.sql,line=3,col=15,endLine=3,endColumn=22,title=E0002 column-not-found::Column 'user_id' not found%0Ahelp: Did you mean 'id'?
::warning file=queries/report.sql,line=6,col=8,endLine=6,endColumn=10,title=E0006 ambiguous-column::Column 'id' is ambiguous

Warnings become ::warning, errors ::error. Use it when you run sqlsift inside your own job step; the GitHub Action sets up annotations for you.

Schema JSON

sqlsift schema --format json prints the loaded schema:

{
  "dialect": "postgresql",
  "default_schema": "public",
  "schema_files": ["migrations/001_init.sql"],
  "schemas": [{
    "name": "public",
    "tables": [{
      "name": "users",
      "columns": [{ "name": "id", "type": "integer", "nullable": false, "primary_key": true,
                    "identity": null, "auto_increment": false, "default": "nextval(...)" }],
      "primary_key": ["id"],           // or null
      "foreign_keys": [{ "name": null, "columns": ["..."], "references_table": "...", "references_columns": ["..."] }],
      "unique": [["..."]]
    }],
    "views": [{ "name": "user_names", "materialized": false,
                "columns": [{ "name": "id", "type": "integer" }] }]   // type is null when unknown
  }],
  "enums": [{ "name": "mood", "schema": null, "values": ["sad", "ok", "happy"] }]
}

Rules

Every rule has a code, a name and a category. Rules in the correctness category report queries the database rejects (or that fail at run time) and are errors by default; rules in the suspicious category report valid queries that almost certainly don't do what was meant, and are warnings by default. Rules in the restriction category ban constructs some codebases don't want and are off until you enable them. See Rules and levels to change their levels and Suppressing diagnostics for exceptions.

The E in a rule code is sqlsift's prefix, not a severity: a rule's level comes from its category and your configuration, so a warning can carry an E code too. Codes are assigned in order as rules are added and never change or get reused, so they are safe to keep in config files, suppression comments and baselines. Names are easier to read in config and comments; codes are shorter.

The examples on these pages use this schema:

CREATE TYPE order_status AS ENUM ('open', 'paid', 'shipped');
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  status order_status NOT NULL DEFAULT 'open',
  total NUMERIC(10, 2) NOT NULL
);
CREATE TABLE coupons (
  code TEXT PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id)
);

E0001 table-not-found

Referenced table does not exist in schema.

What it reports

A query refers to a table or view that isn't defined in the schema, and isn't a CTE or a table created earlier in the same file.

Example

SELECT * FROM user;

error[E0001]: Table 'user' not found
  --> q.sql:1:15
    |
  1 | SELECT * FROM user;
    |               ^^^^
    = help: Did you mean 'users'?

Fixed:

SELECT * FROM users;

Notes

• Typos in a table name. sqlsift suggests the closest existing name.
• A table that was renamed or dropped by a later migration. The help names the schema file and line of the ALTER TABLE ... RENAME TO or DROP TABLE ('people' was renamed to 'persons' in db/migrations/0005_rename_people.sql:2), or the line of the query file when the change is in the query file itself ('tmp' was dropped at line 3 of this file).
• A table created in a migration that sqlsift didn't load. Run sqlsift schema to see what was loaded, and check the order of files in --schema-dir.
• Qualifying a column with a table's name after giving the table an alias (SELECT users.id FROM users u). Once aliased, the table must be referred to by its alias: Table or alias 'users' not found in FROM clause.

Configuring

# sqlsift.toml
[rules]
table-not-found = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0001. The examples use the schema on the Rules page.

E0002 column-not-found

Referenced column does not exist in table.

What it reports

A column reference doesn't match any column of the tables, views, CTEs or subqueries in scope.

Example

SELECT naem FROM users;

error[E0002]: Column 'naem' not found in table 'users'
  --> q.sql:1:8
    |
  1 | SELECT naem FROM users;
    |        ^^^^
    = help: Did you mean 'name'?

Fixed:

SELECT name FROM users;

Notes

• Typos, and columns renamed or dropped by a migration. This is the most common way a schema change breaks a query in another file. The help says what became of the column and names the schema file and line of the ALTER TABLE that changed it, so you can find the migration without searching:

  = help: 'body' was renamed to 'content' in db/migrations/0003_rename_body.sql:2
  = help: 'nick' was dropped in db/migrations/0004_drop_nick.sql:1
  

  Paths are shown the way the schema files were given (relative to the current directory, or to the workspace in the editor). When the ALTER TABLE is in the query file itself, the help says at line N of this file.

• Columns of a subquery or CTE that the subquery doesn't select.

• PostgreSQL: a column created with a quoted name that is not all lowercase ("authorId", as Prisma migrations create them) is matched only by a quoted reference with the same case. PostgreSQL folds the unquoted authorId to authorid, so sqlsift reports it with the hint Column names are case-sensitive when quoted; did you mean "authorId"?. Columns created unquoted or all lowercase match references in any case, and other dialects match column names case-insensitively.

Configuring

# sqlsift.toml
[rules]
column-not-found = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0002. The examples use the schema on the Rules page.

E0003 type-mismatch

Type incompatibility in expression.

What it reports

An expression combines values whose types the database won't implicitly convert: a comparison or arithmetic between incompatible types, an INSERT or UPDATE value that doesn't fit the column, CASE branches of different types, or a value that isn't one of an enum's labels.

String literals are treated like the database treats them: created_at > '2024-01-01' and id = '42' are fine, while id = 'abc' is reported.

Example

SELECT id FROM users WHERE id = 'abc';

error[E0003]: Type mismatch: cannot compare integer with text
  --> q.sql:1:28
    |
  1 | SELECT id FROM users WHERE id = 'abc';
    |                            ^^
    = help: Types are not implicitly compatible. Consider using explicit CAST.

Fixed:

SELECT id FROM users WHERE id = 42;

Notes

Enum values are checked too, with a suggestion for typos:

SELECT id FROM orders WHERE status = 'opne';

error[E0003]: Invalid value 'opne' for enum type 'order_status'
  --> q.sql:1:29
    |
  1 | SELECT id FROM orders WHERE status = 'opne';
    |                             ^^^^^^
    = help: Did you mean 'open'?

See Dialects and SQL support for what sqlsift can infer. Anything it can't infer is never reported, and neither is arithmetic on a type whose operators sqlsift doesn't model: user-defined types, domains and extension types (MONEY, citext, ...), enums, arrays and JSONB.

Configuring

# sqlsift.toml
[rules]
type-mismatch = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0003. The examples use the schema on the Rules page.

E0004 potential-null-violation

Potential NOT NULL violation.

What it reports

An INSERT or UPDATE explicitly assigns NULL to a column declared NOT NULL.

Example

UPDATE users SET name = NULL WHERE id = 1;

error[E0004]: Potential NOT NULL violation: column 'name' cannot be assigned NULL
  --> q.sql:1:18
    |
  1 | UPDATE users SET name = NULL WHERE id = 1;
    |                  ^^^^
    = help: This column is defined as NOT NULL. Provide a non-NULL value or change the schema constraint.

Fixed:

UPDATE users SET name = 'Anonymous' WHERE id = 1;

Notes

Only a literal NULL is reported; sqlsift doesn't track whether other expressions can be null. A table with a BEFORE INSERT / BEFORE UPDATE trigger in the schema isn't checked for that statement: the trigger may replace the NULL.

Configuring

# sqlsift.toml
[rules]
potential-null-violation = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0004. The examples use the schema on the Rules page.

E0005 column-count-mismatch

Column count doesn't match (INSERT, column aliases, subqueries).

What it reports

• An INSERT lists a different number of values (or SELECT columns) than target columns.
• A column alias list names more columns than the relation has: FROM users AS u(a, b, c, d, e, f), WITH x(a, b) AS (SELECT 1). MySQL and SQLite need exactly one name per column of a CTE or derived table.
• A subquery used as a value (x = (SELECT ...), x IN (SELECT ...)) returns more than one column, or a row ((a, b) IN (SELECT ...), (a, b) = (1, 2, 3)) is compared with a different number of values.

Example

INSERT INTO users (name, email) VALUES ('a');

error[E0005]: INSERT has 1 value(s) but 2 column(s) were specified
  --> q.sql:1:13
    |
  1 | INSERT INTO users (name, email) VALUES ('a');
    |             ^^^^^
    = help: Provide 2 value(s) to match the column list

Fixed:

INSERT INTO users (name, email) VALUES ('a', 'a@example.com');

Notes

Without a column list, the values are compared with all columns of the table. PostgreSQL (and Redshift) accept fewer values there and fill the remaining columns with their defaults, so only too many values are reported; every VALUES row must still have the same length, and a required column left out is reported as E0008. MySQL, SQLite and the other dialects need a value for every column.

Configuring

# sqlsift.toml
[rules]
column-count-mismatch = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0005. The examples use the schema on the Rules page.

E0006 ambiguous-column

Column reference is ambiguous across tables.

What it reports

An unqualified column name exists in more than one table in scope, so the database would reject the query.

Example

SELECT id FROM users JOIN orders ON orders.user_id = users.id;

error[E0006]: Column 'id' is ambiguous (found in tables: users, orders)
  --> q.sql:1:8
    |
  1 | SELECT id FROM users JOIN orders ON orders.user_id = users.id;
    |        ^^
    = help: Qualify the column with a table name: users.id

Fixed:

SELECT users.id FROM users JOIN orders ON orders.user_id = users.id;

Notes

Columns joined with USING or NATURAL JOIN are not ambiguous and are not reported.

A CTE or subquery whose SELECT * covers a join can output the same column name twice; referring to that name is ambiguous too (qualified or not):

WITH j AS (SELECT * FROM users JOIN orders ON orders.user_id = users.id)
SELECT id FROM j;

error[E0006]: Column 'id' is ambiguous (CTE 'j' has more than one column named 'id')

This is reported in every dialect except SQLite, which resolves the name to the first such column. Names sqlsift guesses for unaliased expressions (count(*) is count in PostgreSQL but count(*) in MySQL) are never compared.

Configuring

# sqlsift.toml
[rules]
ambiguous-column = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0006. The examples use the schema on the Rules page.

E0007 join-type-mismatch

JOIN condition compares incompatible types.

What it reports

A JOIN ... ON condition compares columns whose types can't be compared. This usually means the wrong column was joined.

Example

SELECT u.id FROM users u JOIN orders o ON o.user_id = u.email;

error[E0007]: JOIN condition type mismatch: integer vs text
  --> q.sql:1:43
    |
  1 | SELECT u.id FROM users u JOIN orders o ON o.user_id = u.email;
    |                                           ^^^^^^^^^
    = help: JOIN condition should compare compatible types. Consider using explicit CAST.

Fixed:

SELECT u.id FROM users u JOIN orders o ON o.user_id = u.id;

Notes

The same mismatch outside a JOIN condition is reported as E0003.

Configuring

# sqlsift.toml
[rules]
join-type-mismatch = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0007. The examples use the schema on the Rules page.

E0008 missing-required-column

INSERT omits a NOT NULL column without a default.

What it reports

An INSERT with a column list leaves out a column that is NOT NULL and has no default, so the database would reject the row. Columns with a DEFAULT, identity columns, generated columns, SERIAL and AUTO_INCREMENT columns may be omitted.

Example

INSERT INTO users (name) VALUES ('Alice');

error[E0008]: INSERT into 'users' is missing required column 'email'
  --> q.sql:1:13
    |
  1 | INSERT INTO users (name) VALUES ('Alice');
    |             ^^^^^
    = help: 'email' is NOT NULL without a default. Provide a value, or add a DEFAULT to the schema

Fixed:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Notes

Tables with a BEFORE INSERT (or INSTEAD OF INSERT) trigger in the schema are not checked: the trigger may fill the column. If something else fills it in a way sqlsift can't see, set this rule to warn or off.

Configuring

# sqlsift.toml
[rules]
missing-required-column = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0008. The examples use the schema on the Rules page.

E0009 duplicate-name

Table alias or CTE name given twice in one query.

What it reports

A FROM clause gives the same name twice (an alias, or a table name without an alias), or a WITH clause defines two CTEs with the same name. The database rejects the query: a qualified reference like o.id could mean either.

Example

SELECT o.id FROM orders o JOIN users o ON o.id = o.user_id;

error[E0009]: Table name 'o' is specified more than once in FROM
  --> q.sql:1:38
    |
  1 | SELECT o.id FROM orders o JOIN users o ON o.id = o.user_id;
    |                                      ^
    = help: Give each occurrence a different alias

Fixed:

SELECT o.id FROM orders o JOIN users u ON u.id = o.user_id;

Notes

Two tables with the same name in different schemas (FROM auth.users, public.users) are fine, and so is a name reused in a subquery. Checked for PostgreSQL, Redshift and MySQL (where aliases are case sensitive); SQLite accepts a repeated table alias, so only its repeated CTE names are reported.

Configuring

# sqlsift.toml
[rules]
duplicate-name = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0009. The examples use the schema on the Rules page.

E0010 duplicate-target-column

Column given twice in an INSERT column list or UPDATE SET.

What it reports

An INSERT column list names a column twice, or UPDATE ... SET (or ON CONFLICT DO UPDATE SET) assigns a column twice. The database rejects the statement instead of picking one of the values.

Example

UPDATE orders SET status = 'paid', status = 'shipped' WHERE id = 1;

error[E0010]: Column 'status' is assigned more than once
  --> q.sql:1:36
    |
  1 | UPDATE orders SET status = 'paid', status = 'shipped' WHERE id = 1;
    |                                    ^^^^^^
    = help: Remove one of the assignments

Fixed:

UPDATE orders SET status = 'shipped' WHERE id = 1;

Notes

Repeated INSERT columns are reported for PostgreSQL, Redshift and MySQL; repeated SET assignments for PostgreSQL and Redshift only, since MySQL and SQLite apply them in order. Assignments to fields of a composite column (SET c.f1 = 1, c.f2 = 2) are not repeats.

Configuring

# sqlsift.toml
[rules]
duplicate-target-column = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0010. The examples use the schema on the Rules page.

E0011 position-out-of-range

ORDER BY / GROUP BY position is not in the select list.

What it reports

ORDER BY n or GROUP BY n refers to the n-th column of the select list, and the select list has fewer columns (or n is 0). This often happens when a column is removed from the select list and the positions are not updated.

Example

SELECT id, total FROM orders ORDER BY 3;

error[E0011]: ORDER BY position 3 is not in the select list
  --> q.sql:1:1
    |
  1 | SELECT id, total FROM orders ORDER BY 3;
    | ^^^^^^
    = help: The select list has 2 columns; positions count from 1

Fixed:

SELECT id, total FROM orders ORDER BY 2;

Notes

Only plain integers are positions: ORDER BY 2 + 0 is an expression. When the select list contains * over a relation whose columns are unknown, nothing is reported. Not checked for Databricks, where positions can be turned off by configuration.

Configuring

# sqlsift.toml
[rules]
position-out-of-range = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0011. The examples use the schema on the Rules page.

E0012 misplaced-aggregate

Aggregate or window function where it is not allowed.

What it reports

An aggregate function (count, sum, max, ...) is used in WHERE, JOIN ... ON, GROUP BY, UPDATE SET or VALUES; an aggregate is nested inside another aggregate; or a window function (... OVER (...)) is used in WHERE, JOIN ... ON, GROUP BY, HAVING, UPDATE SET or VALUES. Aggregates are computed after WHERE filters the rows, so the database rejects these.

Example

SELECT user_id FROM orders WHERE count(*) > 1;

error[E0012]: Aggregate function 'count' is not allowed in WHERE
  --> q.sql:1:34
    |
  1 | SELECT user_id FROM orders WHERE count(*) > 1;
    |                                  ^^^^^
    = help: Filter on aggregates in HAVING, or aggregate in a subquery

Fixed:

SELECT user_id FROM orders GROUP BY user_id HAVING count(*) > 1;

Notes

Aggregates inside a subquery belong to the subquery (WHERE total > (SELECT avg(total) FROM orders) is fine), and a window function over an aggregate (sum(count(*)) OVER ()) is fine. Only built-in aggregates are recognized, by unqualified name: a schema-qualified call such as stats.max(x) may be a user-defined function and is not reported. SQLite's min / max with several arguments are scalar functions.

Configuring

# sqlsift.toml
[rules]
misplaced-aggregate = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0012. The examples use the schema on the Rules page.

E0013 generated-column-write

INSERT or UPDATE gives a value to a generated column.

What it reports

An INSERT gives a value other than DEFAULT to a generated column (GENERATED ALWAYS AS (expr) STORED, MySQL / SQLite AS (expr)), or UPDATE sets one. In PostgreSQL the same holds for identity columns declared GENERATED ALWAYS AS IDENTITY. The database computes these values itself and rejects the statement.

The examples on this page also use this table:

CREATE TABLE line_items (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  quantity INT NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL,
  amount NUMERIC(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

Example

INSERT INTO line_items (quantity, unit_price, amount) VALUES (2, 9.50, 19.00);

error[E0013]: Column 'amount' is a generated column: INSERT can't give it a value
  --> q.sql:1:47
    |
  1 | INSERT INTO line_items (quantity, unit_price, amount) VALUES (2, 9.50, 19.00);
    |                                               ^^^^^^
    = help: Leave the column out of the INSERT, or give it DEFAULT

Fixed:

INSERT INTO line_items (quantity, unit_price) VALUES (2, 9.50);

Notes

DEFAULT is always allowed, and so are GENERATED BY DEFAULT AS IDENTITY columns. Generated columns may be left out of an INSERT even when they are NOT NULL (E0008 doesn't report them). SQLite doesn't count generated columns in an INSERT without a column list.

Configuring

# sqlsift.toml
[rules]
generated-column-write = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0013. The examples use the schema on the Rules page.

E0014 unmatched-conflict-target

ON CONFLICT columns match no unique constraint.

What it reports

INSERT ... ON CONFLICT (columns) names columns that are not exactly the columns of the table's primary key, a unique constraint or a unique index. PostgreSQL and SQLite reject the statement ("there is no unique or exclusion constraint matching the ON CONFLICT specification"). It usually means the unique index was never created, or covers more columns than the upsert names.

Example

INSERT INTO users (name, email) VALUES ('Alice', 'a@example.com') ON CONFLICT (name) DO NOTHING;

error[E0014]: No unique constraint or primary key of 'users' matches ON CONFLICT (name)
  --> q.sql:1:80
    |
  1 | INSERT INTO users (name, email) VALUES ('Alice', 'a@example.com') ON CONFLICT (name) DO NOTHING;
    |                                                                                ^^^^
    = help: The unique keys of 'users' are (id), (email); add a unique constraint or index on (name)

Fixed:

INSERT INTO users (name, email) VALUES ('Alice', 'a@example.com') ON CONFLICT (email) DO NOTHING;

Notes

Unique keys come from PRIMARY KEY, UNIQUE (in CREATE TABLE or ALTER TABLE) and CREATE UNIQUE INDEX; the column order doesn't matter. Nothing is reported for a table with a partial or expression unique index, for a partition (which gets the unique indexes of its parent), or when a schema statement that may define a unique key could not be parsed.

Configuring

# sqlsift.toml
[rules]
unmatched-conflict-target = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0014. The examples use the schema on the Rules page.

E0015 distinct-order-by

SELECT DISTINCT ordered by a column it doesn't select.

What it reports

SELECT DISTINCT is ordered by a column that is not in its select list, so each distinct row could have several values to sort by. PostgreSQL and MySQL reject the query. In PostgreSQL, SELECT DISTINCT ON (...) must also start its ORDER BY with the DISTINCT ON expressions.

Example

SELECT DISTINCT user_id FROM orders ORDER BY total DESC;

error[E0015]: ORDER BY 'total' is not in the select list of SELECT DISTINCT
  --> q.sql:1:46
    |
  1 | SELECT DISTINCT user_id FROM orders ORDER BY total DESC;
    |                                              ^^^^^
    = help: With DISTINCT, ORDER BY can only use selected columns: select it too, or drop DISTINCT

Fixed:

SELECT user_id FROM orders GROUP BY user_id ORDER BY max(total) DESC;

Notes

Only plain column references in ORDER BY are checked; a column is considered selected when any select item or alias has its name. SQLite accepts such queries, so nothing is reported for it.

Configuring

# sqlsift.toml
[rules]
distinct-order-by = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0015. The examples use the schema on the Rules page.

E0016 grouping-error

Column must appear in GROUP BY or be used in an aggregate.

What it reports

In a query that aggregates (it has GROUP BY or HAVING, or calls an aggregate function such as count), the select list, HAVING or ORDER BY uses a column outside an aggregate function that GROUP BY doesn't group. Each group has many values for that column, so PostgreSQL rejects the query.

Example

SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.email;

error[E0016]: Column 'u.name' must appear in GROUP BY or be used in an aggregate function
  --> q.sql:1:10
    |
  1 | SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.email;
    |          ^^^^
    = help: Add 'u.name' to GROUP BY, or aggregate it (for example max(u.name))

Fixed:

SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;

Notes

A column counts as grouped when GROUP BY lists it (also by position or output alias), when it is used inside a grouped expression (GROUP BY lower(name) allows lower(name)), or when GROUP BY lists the whole primary key of its table, as PostgreSQL allows (above, u.id makes every column of users grouped).

Checked for PostgreSQL and Redshift only: MySQL's result depends on ONLY_FULL_GROUP_BY, and SQLite allows such columns. To avoid false positives nothing is reported for GROUPING SETS / ROLLUP / CUBE, joins with USING, relations whose columns are unknown, SELECT *, statements with template tags (dbt macros can expand to GROUP BY columns), and columns inside calls of functions sqlsift doesn't know, which may be user-defined aggregates.

Configuring

# sqlsift.toml
[rules]
grouping-error = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0016. The examples use the schema on the Rules page.

E0017 value-out-of-range

Literal too long or out of range for the column type.

What it reports

INSERT ... VALUES or UPDATE ... SET gives a column a literal its type can't hold:

• a string longer than a VARCHAR(n) / CHAR(n) column,
• an integer outside the range of a SMALLINT / INTEGER / BIGINT column (and MySQL's TINYINT / MEDIUMINT),
• a number with more integer digits than a NUMERIC(p, s) / DECIMAL(p, s) column allows (p - s).

Example

INSERT INTO orders (user_id, total) VALUES (1, 123456789.50);

error[E0017]: Value 123456789.50 is out of range for column 'orders.total' of type numeric(10,2)
  --> q.sql:1:30
    |
  1 | INSERT INTO orders (user_id, total) VALUES (1, 123456789.50);
    |                              ^^^^^
    = help: Use a value the column type can hold or a wider column type

Fixed:

INSERT INTO orders (user_id, total) VALUES (1, 1234567.50);

Notes

Checked for PostgreSQL, Redshift and MySQL (which rejects these values in its default strict mode); SQLite stores any value. Only literals are checked, and only where they are assigned: WHERE age = 99999 is a valid comparison.

Trailing spaces beyond the length are allowed, as the database truncates them. Numbers with a fraction assigned to an integer column, and numbers with an exponent, are rounded by the database and not checked. MySQL integer columns may be UNSIGNED, so for MySQL a value is only reported when no signed or unsigned column of that size can hold it.

Configuring

# sqlsift.toml
[rules]
value-out-of-range = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0017. The examples use the schema on the Rules page.

E0018 null-comparison

Comparison with NULL using = or <> is never true.

What it reports

A comparison operator (=, <>, !=, <, <=, >, >=) with a NULL literal operand, and CASE x WHEN NULL. Any comparison with NULL is NULL, so the condition never holds and the WHEN branch never runs.

Example

SELECT name FROM users WHERE email = NULL;

warning[E0018]: Comparison with NULL using '=' is never true
  --> q.sql:1:30
    |
  1 | SELECT name FROM users WHERE email = NULL;
    |                              ^^^^^
    = help: Use IS NULL to test for NULL

Fixed:

SELECT name FROM users WHERE email IS NULL;

Notes

Assignments (UPDATE users SET email = NULL), IS [NOT] DISTINCT FROM NULL and MySQL's NULL-safe <=> are not comparisons in this sense and are not reported.

Configuring

# sqlsift.toml
[rules]
null-comparison = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0018. The examples use the schema on the Rules page.

E0019 not-in-with-nulls

NOT IN over a nullable subquery column.

What it reports

x NOT IN (SELECT c FROM t) where t.c is a nullable column. If the subquery returns a single NULL, x NOT IN (...) is NULL for every x that isn't in the list, so the query returns no rows at all. It works in testing and breaks the day a NULL arrives.

Example

SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM coupons);

warning[E0019]: NOT IN over the nullable column 'coupons.order_id' returns no rows if it has a NULL
  --> q.sql:1:47
    |
  1 | SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM coupons);
    |                                               ^^^^^^^^
    = help: Use NOT EXISTS, or add WHERE order_id IS NOT NULL to the subquery

Fixed:

SELECT id FROM orders o WHERE NOT EXISTS (SELECT 1 FROM coupons c WHERE c.order_id = o.id);

Notes

Only a column of a schema table is checked, and only when it is nullable and not part of the primary key. Nothing is reported when the subquery mentions the column anywhere else (a WHERE, JOIN ... ON, GROUP BY or HAVING condition on it may exclude NULLs) or joins with USING / NATURAL.

Configuring

# sqlsift.toml
[rules]
not-in-with-nulls = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0019. The examples use the schema on the Rules page.

E0020 outer-column-in-subquery

IN subquery selects a column of the outer query.

What it reports

The select list of an IN / NOT IN subquery is an unqualified column that the subquery's own tables don't have, so it silently refers to the outer query's column. The subquery then returns the outer row's own value: IN is true for every row (when the subquery has rows) and NOT IN is never true. A typo or a wrong column name, and SQL accepts it.

Example

SELECT id, total FROM orders WHERE id IN (SELECT id FROM coupons);

warning[E0020]: Column 'id' in the subquery is the outer query's 'orders.id': coupons has no column 'id'
  --> q.sql:1:50
    |
  1 | SELECT id, total FROM orders WHERE id IN (SELECT id FROM coupons);
    |                                                  ^^
    = help: The subquery then returns the outer row's own value. Select a column of coupons, or qualify it (orders.id) if this is intended

Fixed:

SELECT id, total FROM orders WHERE id IN (SELECT order_id FROM coupons);

Notes

Only the select list of the subquery is checked: outer references in its WHERE (correlated subqueries) are normal. A qualified column (orders.id) is taken as intended. Nothing is reported when the columns of a relation in the subquery are unknown.

Configuring

# sqlsift.toml
[rules]
outer-column-in-subquery = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0020. The examples use the schema on the Rules page.

E0021 missing-join-condition

Tables in FROM with no condition linking them.

What it reports

A table in a comma-separated FROM list that nothing links with the other tables: no WHERE condition references it together with another table. Every row is combined with every row of the others (a cross join), which multiplies the result and is usually a forgotten join condition.

Example

SELECT u.name, o.total FROM users u, orders o;

warning[E0021]: No condition links 'o' with the other tables: every row is joined with every row (cross join)
  --> q.sql:1:38
    |
  1 | SELECT u.name, o.total FROM users u, orders o;
    |                                      ^^^^^^
    = help: Add a join condition for 'o' to WHERE, or write CROSS JOIN if every combination is intended

Fixed:

SELECT u.name, o.total FROM users u, orders o WHERE o.user_id = u.id;

Notes

Explicit joins (CROSS JOIN, JOIN ... ON) are never reported. To keep deliberate cross joins quiet, nothing is reported when:

• a WHERE condition filters one of the tables on its own (WHERE t.name = 'rust': every row combined with the chosen ones),
• the other item is a CTE, a subquery or a table function (it may return a single row), or refers to an earlier table (LATERAL, unnest(u.tags), BigQuery u.tags),
• the statement has template tags (they may add conditions), or a condition uses a column sqlsift can't attribute to one table.

A condition that references several tables links all of them, also inside OR or a subquery.

Configuring

# sqlsift.toml
[rules]
missing-join-condition = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0021. The examples use the schema on the Rules page.

E0022 constant-condition

Condition that is always or never true from the schema.

What it reports

• col IS NULL in WHERE on a NOT NULL column of a schema table: never true. Usually the wrong column, or a check that can never fire.
• A column compared with itself (a.x = a.x, x <> x), in any clause: =, <=, >= are true for every non-NULL value and <>, <, > never are. Usually a copy-paste slip in a join condition.

Example

SELECT o.id FROM orders o JOIN users u ON u.id = o.user_id WHERE u.email IS NULL;

warning[E0022]: Column 'users.email' is NOT NULL: IS NULL is never true
  --> q.sql:1:66
    |
  1 | SELECT o.id FROM orders o JOIN users u ON u.id = o.user_id WHERE u.email IS NULL;
    |                                                                  ^^^^^^^
    = help: Remove the condition, or test a nullable column

Notes

A column on the optional side of an outer join can be NULL whatever its definition (LEFT JOIN orders o ... WHERE o.id IS NULL finds users without orders), so it is not reported. Neither are columns of subqueries, CTEs and views, statements with template tags, nor IS NOT NULL (always true, but harmless).

Configuring

# sqlsift.toml
[rules]
constant-condition = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0022. The examples use the schema on the Rules page.

E0023 outer-join-filtered

WHERE condition turns an outer join into an inner join.

What it reports

A WHERE condition, AND-ed with the rest, that compares a column of the optional side of an outer join (the right side of LEFT JOIN, the left side of RIGHT JOIN, either side of FULL JOIN). For the rows the join adds without a match that column is NULL, so the condition is not true and removes them: the outer join returns what an inner join would.

Example

Users with their number of paid orders, including users with none:

SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' GROUP BY u.id;

warning[E0023]: WHERE condition on 'o.status' removes the rows the outer join adds without a match in 'o': it works as an inner join
  --> q.sql:1:86
    |
  1 | SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' GROUP BY u.id;
    |                                                                                      ^^^^^^^^
    = help: Move the condition into the join's ON clause to keep unmatched rows, or write an inner JOIN (or test 'o.status IS NULL OR ...')

Fixed (users without paid orders are listed with 0):

SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid' GROUP BY u.id;

Notes

Comparisons (=, <>, <, ...), IN (...), BETWEEN and LIKE with the column as an operand are reported. Conditions that hold for NULL (IS NULL, IS DISTINCT FROM, coalesce(...)), conditions inside OR, and statements with template tags are not.

Configuring

# sqlsift.toml
[rules]
outer-join-filtered = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0023. The examples use the schema on the Rules page.

E0024 unfiltered-write

UPDATE or DELETE without WHERE.

What it reports

An UPDATE or DELETE with no WHERE clause changes or deletes every row of the table. Teams that want every such statement to be deliberate can require a WHERE.

Example

DELETE FROM orders;

warning[E0024]: DELETE without WHERE deletes every row of the table
  --> q.sql:1:13
    |
  1 | DELETE FROM orders;
    |             ^^^^^^
    = help: Add a WHERE clause (WHERE true if every row is meant), or use TRUNCATE

Fixed:

DELETE FROM orders WHERE status = 'open';

Notes

Write WHERE true when every row is meant. MySQL's DELETE ... LIMIT n is not reported.

Configuring

This rule is off by default. To enable it:

# sqlsift.toml
[rules]
unfiltered-write = "warn"   # or "error"

Or on the command line: -W unfiltered-write, or -W restriction for every restriction rule. The examples use the schema on the Rules page.

E0025 insert-without-columns

INSERT without a column list.

What it reports

INSERT INTO t VALUES (...) or INSERT INTO t SELECT ... without a column list assigns values by the position of the table's columns. When a column is added, the statement fails, or worse, silently puts values in the wrong columns.

Example

INSERT INTO users VALUES (DEFAULT, 'Ann', 'ann@example.com', now());

warning[E0025]: INSERT without a column list depends on the order of the table's columns
  --> q.sql:1:13
    |
  1 | INSERT INTO users VALUES (DEFAULT, 'Ann', 'ann@example.com', now());
    |             ^^^^^
    = help: List the target columns: INSERT INTO t (a, b) ...

Fixed:

INSERT INTO users (name, email) VALUES ('Ann', 'ann@example.com');

Notes

INSERT INTO t DEFAULT VALUES is not reported.

Configuring

This rule is off by default. To enable it:

# sqlsift.toml
[rules]
insert-without-columns = "warn"   # or "error"

Or on the command line: -W insert-without-columns, or -W restriction for every restriction rule. The examples use the schema on the Rules page.

E0026 select-star

SELECT * in a query's result columns.

What it reports

SELECT * or t.* in the columns a statement returns (also in each branch of UNION and in INSERT ... SELECT). The result changes when a column is added to the table, which can break or slow down the application reading it.

Example

SELECT * FROM orders WHERE user_id = 1;

warning[E0026]: SELECT * returns whatever columns the tables have
  --> q.sql:1:8
    |
  1 | SELECT * FROM orders WHERE user_id = 1;
    |        ^
    = help: List the columns the query needs

Fixed:

SELECT id, status, total FROM orders WHERE user_id = 1;

Notes

A star inside a subquery, CTE or EXISTS (SELECT * ...) doesn't change the result columns and is not reported, nor is count(*).

Configuring

This rule is off by default. To enable it:

# sqlsift.toml
[rules]
select-star = "warn"   # or "error"

Or on the command line: -W select-star, or -W restriction for every restriction rule. The examples use the schema on the Rules page.

E0027 wrong-argument-type

Function or operator given an argument type it doesn't take.

What it reports

A built-in PostgreSQL function or operator called with an argument of a type it has no version for. PostgreSQL doesn't convert numbers, dates, booleans or UUIDs to text (or text to numbers) implicitly here, so the query fails with "function ... does not exist" or "operator does not exist":

• sum / avg of text, booleans, dates, timestamps, UUIDs or JSON,
• lower, upper, initcap, length, char_length, ltrim, rtrim, btrim, reverse, md5 of numbers, booleans, dates and times, UUIDs or JSON,
• LIKE / ILIKE (and NOT LIKE) on numbers, booleans, dates and times, UUIDs or JSON.

Example

SELECT id FROM orders WHERE user_id LIKE '12%';

error[E0027]: Operator LIKE does not exist for integer: it matches text
  --> q.sql:1:29
    |
  1 | SELECT id FROM orders WHERE user_id LIKE '12%';
    |                             ^^^^^^^
    = help: Cast the value to text: x::text LIKE '...'

Fixed:

SELECT id FROM orders WHERE user_id::text LIKE '12%';

Notes

PostgreSQL only: MySQL and SQLite convert the argument. Only arguments whose type sqlsift knows are checked. A function the schema defines with the same name (CREATE FUNCTION lower(integer) ..., CREATE AGGREGATE sum(text) ...) may overload the built-in one, so that name is never reported; neither is LIKE when the schema defines operators. Functions called with a schema other than pg_catalog (util.lower(x)) are not the built-in ones and are not checked.

Configuring

# sqlsift.toml
[rules]
wrong-argument-type = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0027. The examples use the schema on the Rules page.

E0028 limit-without-order-by

LIMIT or OFFSET without ORDER BY.

What it reports

A query with LIMIT, OFFSET or FETCH FIRST and no ORDER BY, also in subqueries and CTEs. Which rows it returns is up to the database and can change between runs; with OFFSET pagination, pages can repeat or skip rows.

Example

SELECT id, total FROM orders LIMIT 20 OFFSET 40;

warning[E0028]: LIMIT without ORDER BY returns an arbitrary set of rows
  --> q.sql:1:36
    |
  1 | SELECT id, total FROM orders LIMIT 20 OFFSET 40;
    |                                    ^^
    = help: Add an ORDER BY clause that puts the rows in a fixed order

Fixed:

SELECT id, total FROM orders ORDER BY id LIMIT 20 OFFSET 40;

Notes

The query of an EXISTS (...) is not reported, since which of its rows come back doesn't matter. (SELECT ... ORDER BY id) LIMIT 5 counts as ordered. The rule doesn't check that the ORDER BY columns are unique, so rows that tie can still come back in any order.

Configuring

This rule is off by default. To enable it:

# sqlsift.toml
[rules]
limit-without-order-by = "warn"   # or "error"

Or on the command line: -W limit-without-order-by, or -W restriction for every restriction rule. The examples use the schema on the Rules page.

E1000 parse-error

SQL could not be parsed.

What it reports

The file contains SQL that sqlsift's parser can't read for the selected dialect.

Example

SELEC id FROM users;

error[E1000]: Parse error: Expected: an SQL statement, found: SELEC
  --> q.sql:1:1
    |
  1 | SELEC id FROM users;
    | ^

Fixed:

SELECT id FROM users;

Notes

• Check --dialect: MySQL, SQLite or warehouse syntax (Snowflake, BigQuery, Redshift, Databricks) may not parse as PostgreSQL. See Dialects.
• dbt / Jinja templates parse only with templating on (--templating jinja, automatic in a dbt project); the error then suggests it. With templating on, an error on a template tag shows the tag (found: Jinja expression {{ ... }}): sqlsift can't tell what SQL it expands to there. See dbt and Jinja templates.
• In TypeScript and JavaScript files, only tagged template literals (sql`...` and the tags in embedded_sql_tags) are parsed as SQL, and the error points into the template. See SQL in TypeScript and JavaScript.
• If the SQL is valid for your database, please open an issue. Meanwhile, -- sqlsift:disable-file or ignore skips the file.

Configuring

# sqlsift.toml
[rules]
parse-error = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E1000. The examples use the schema on the Rules page.

Contributing

Contributions are welcome. The most useful things right now are:

• Real-world SQL that sqlsift gets wrong: false positives and missed errors. Please open an issue with a minimal schema and query.
• Dialect coverage: MySQL and SQLite edge cases, and the alpha warehouse dialects (Snowflake, BigQuery, Redshift, Databricks).
• Editor integrations: setup guides or plugins for editors other than VS Code.

The development setup, repository layout and how to add a rule are described in CONTRIBUTING.md.