Why Compile SQL?#

SQL is a 50-year-old language supported by every major database. It is expressive, optimizable, and already understood by every developer on your team and every tool in your stack. The part that is tedious is the glue code: every application that talks to a database needs code that maps query parameters in, maps result rows out, and keeps types aligned between the two worlds. That code changes every time your schema or queries change.

Scythe eliminates that glue code. You write .sql files — schema definitions and annotated queries — and scythe compiles them into fully typed functions and data structures for your target language. The generated code is readable, has no runtime dependencies beyond your database driver, and stays in sync with your SQL automatically.

Where ORMs Still Win#

ORMs provide database portability. If your application supports bring-your-own-database (BYOD) — letting a user choose between PostgreSQL, MySQL, or SQLite — an ORM abstracts the dialect differences at runtime, and the same application code works across databases without maintaining separate SQL files per engine.

Scythe takes the opposite approach: you write SQL for a specific database engine, which gives you full access to engine-specific features and lets the database's own optimizer do its job. Targeting multiple engines with scythe means maintaining separate SQL files and [[sql]] config blocks per engine.

If your application must run on whichever database the end user provides, an ORM is the right tool. If you control the database and want type-safe, optimized SQL, scythe is the right tool.

SQL Features Scythe Understands#

Scythe's type inference engine handles SQL features that many ORM query builders struggle with:

• CTEs — basic, recursive, and chained (WITH a AS (...), b AS (SELECT ... FROM a))
• Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, FIRST_VALUE, LAST_VALUE, each with correct nullability inference
• Complex JOINs — INNER, LEFT, RIGHT, FULL OUTER, CROSS, with automatic nullability propagation
• CASE WHEN — type widening across branches (integer + null = nullable integer, integer + bigint = bigint)
• RETURNING clauses — INSERT/UPDATE/DELETE ... RETURNING with full column inference
• Enums and composite types — mapped to language-native enums and structs
• Arrays and JSONB/JSON — mapped to language-appropriate array and JSON types, with configurable overrides

See Type Inference for the mechanics behind nullability, and Custom Types for overriding types scythe doesn't recognize out of the box.

SQL Should Be Linted and Formatted#

SQL is the source of truth in a scythe project, so it gets the same quality tooling as application code: 59 built-in rules covering correctness (UPDATE without WHERE, ambiguous columns, NULL comparisons with = instead of IS), performance (ORDER BY without LIMIT, leading-wildcard LIKE, SELECT *), and style, plus integrated formatting via sqruff. Scythe runs linting and formatting as part of the compilation pipeline, so bad SQL is caught before code generation, not at runtime.

Next Steps#

• Compare Scythe to sqlc, SQLDelight, jOOQ, and ORMs for a framework-by-framework breakdown.
• Follow the Quickstart to see the compile pipeline end to end.