Introducing sqlsift: schema-aware static analysis for SQL, no database needed

#sql #postgres #typescript #rust

Nothing checks the SQL in most codebases before it runs. The compiler sees a string, the tests cover the queries someone thought to test, and the rest gets checked by the database, sometimes for the first time in production.

sqlsift moves that last check earlier. It replays your migrations into an in-memory schema and checks every query against it, without a database. Think of it as a type checker for your SQL.

An example

Say a migration renames a column:

ALTER TABLE page_views RENAME COLUMN site_id TO website_id;

Later, someone adds a cleanup job that still uses the old name:

await sql`
  DELETE FROM page_views
  WHERE site_id = ${siteId} AND created_at < ${cutoff}
`;

Tests pass and TypeScript is happy, because to both of them the SQL is a string. This exact shape happened in a real open-source app: the cleanup job kept failing until the maintainers spotted it and fixed it. The rename and the new job came from two separate changes a couple of hours apart, and nothing in the toolchain could connect them.

sqlsift catches it before it ships:

$ npx sqlsift-cli check
error[E0002]: Column 'site_id' not found in table 'page_views'
  --> src/retention.ts:6:11
    |
  6 |     WHERE site_id = ${siteId} AND created_at < ${cutoff}
    |           ^^^^^^^
    = help: 'site_id' was renamed to 'website_id' in migrations/002_rename_site_id.sql:1

More than renames

The goal is to catch everything the schema alone can tell you. There are 27 rules so far, in three categories:

correctness (errors): missing tables and columns, type mismatches, NOT NULL columns left out of an INSERT, ON CONFLICT targets with no unique key, GROUP BY errors, writes to generated columns, values out of range, functions given the wrong argument types
suspicious (warnings): = NULL, NOT IN over a nullable column, tables with no join condition, conditions that are always true or false, a WHERE that turns a LEFT JOIN into an inner join
restriction (off by default): UPDATE/DELETE without WHERE, INSERT without a column list, SELECT *

Each rule can be set to off, warn or error.

When it isn't sure, it stays quiet

If sqlsift can't work out a type or a name, it reports nothing rather than guess.

To check that, I ran it the way a user would on 16 open-source apps using sqlc, Prisma TypedSQL, postgres.js, MySQL and Supabase (687 files), and fixed it until it reported zero false positives. Along the way it found a real bug in podcst-web: an ON CONFLICT (name) on a column with no unique constraint, which PostgreSQL rejects when it runs. I've reported it.

Trying it

npx sqlsift-cli init    # finds migrations, dialect and query files, writes sqlsift.toml
npx sqlsift-cli check

Queries can be .sql files, sqlc and aiosql query files, or sql`...` templates in TypeScript and JavaScript (postgres.js, Slonik, also inside Vue and Svelte).

Existing codebase: sqlsift check --write-baseline records today's findings, so only new ones are reported.
CI: the GitHub Action annotates pull requests, and with diff-base only shows what the PR introduces.
Editor: a language server with a VS Code extension.
Browser: a WASM playground; your SQL never leaves the page.

There are example projects for sqlc, Prisma TypedSQL, postgres.js, sqlx, aiosql, MySQL, plain migrations and dbt, all checked in CI. The user guide has the rest.

What it doesn't do yet

Function bodies (PL/pgSQL) aren't analyzed.
SQL inside Rust, Go or Python code isn't read yet (.sql files are).
Schemas written in Drizzle or Prisma schema files need exporting to SQL first.
A few constructs the parser can't read yet.

How it's built

sqlsift is written in Rust on top of sqlparser-rs. I build it with heavy use of Claude Code: I set the direction, decide the design and review what goes in. Running it on real projects is how I check it isn't flagging valid SQL.

If it gets your SQL wrong, please open an issue; a schema and the query that broke it is all I need.

DEV Community