Scythe: SQL as a first-class language (and where that leaves your ORM)

I build and maintain scythe (MIT). This is the argument behind it: why SQL deserves the same tooling as the rest of your code, what generating your data access layer from it replaces, and the one case where it doesn't.

Every app has a data access layer. The question is who writes it

Whether or not anyone on your team calls it that, your application has a data access layer: the code that maps parameters in, maps result rows out, keeps the types on both sides aligned, and gets rewritten every time a query or the schema changes. It is tedious, it is where a lot of quiet bugs live, and nobody enjoys maintaining it.

There are roughly five ways teams get one. A full ORM writes it for you at runtime and hides it. A data mapper writes most of it and asks you to be explicit. A query builder gives you the pieces to assemble — this is what people usually mean when they say "we skipped the ORM and wrote our own DAL". Raw SQL plus manual mapping means you write every line by hand. And then there is the option this article is about: generate the layer from the SQL itself, at build time.

sqlc did this cleanly for Go. You write plain .sql files, it reads your schema and your queries, and it generates typed Go functions and structs before anything compiles. No runtime ORM, no DSL, just SQL in and a typed access layer out. It is one of the nicer developer experiences in the Go ecosystem, and it is the tool scythe is most directly inspired by.

The obvious question is why that idea should be locked to one language. Scythe is my answer: the same SQL-first, compile-time approach, generating an idiomatic layer for 10 languages, with a real type-inference engine and a SQL quality toolchain underneath it.

SQL as a first-class language

Here is the thing that bothers me about how most codebases treat SQL. Your Python gets a formatter, a linter, a type checker, and a pre-commit hook that blocks the bad version from ever reaching a branch. Your .sql files, holding the queries that decide whether the product is fast or correct, get none of that. They sit in a directory as untyped strings, reviewed by eye.

SQL is a 50-year-old language every database speaks, expressive and heavily optimized. Treating it as a first-class language in your repo means giving it exactly what everything else already has:

• A formatter. scythe fmt normalizes style so SQL diffs show intent instead of whitespace.
• A linter. 23 schema-aware rules — UPDATE without WHERE, = NULL instead of IS NULL, ambiguous columns in joins, leading-wildcard LIKE, unbounded ORDER BY — plus sqruff's 69 style rules underneath.
• A security auditor. scythe audit is static analysis for SQL: GRANT ALL, grants to PUBLIC, SECURITY DEFINER without a pinned search_path, literal passwords, SELECT * over PII. 35 rules, emitting SARIF with CWE tags so findings land in GitHub code scanning like any other scanner's.
• Pre-commit hooks. scythe-fmt, scythe-lint, and scythe-audit ship as hooks, so bad SQL fails at commit time rather than in review or production.
• Types. Which is the part that needs a real example.

The boundary between a query and your program's types is where this has always been weakest, so that is where the interesting work is. Here is the case that shows why the inference has to be genuine — a query with a LEFT JOIN:

-- @name GetUserOrders
-- @returns :many
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;

The right side of a LEFT JOIN is nullable. A user with no orders still produces a row, with total and notes as NULL. If your generated types say those columns are non-null, you have a crash waiting for the first user without an order. Scythe knows this, and encodes it in every language it targets:

#[derive(Debug, sqlx::FromRow)]
pub struct GetUserOrdersRow {
    pub id: i32,
    pub name: String,
    pub total: Option<rust_decimal::Decimal>,
    pub notes: Option<String>,
}

@dataclass
class GetUserOrdersRow:
    id: int
    name: str
    total: decimal.Decimal | None
    notes: str | None

interface GetUserOrdersRow {
    id: number;
    name: string;
    total: string | null;
    notes: string | null;
}

Same query, three languages, and in each one total and notes come out correctly optional. You did not annotate anything. The nullability fell out of reading the SQL.

That inference is the core of the tool, and it goes well past joins: COALESCE, CASE branch widening, window functions (ROW_NUMBER, LAG, LEAD, and friends each with their own nullability), aggregates, CTEs including recursive ones, and RETURNING clauses. Postgres enums, arrays, and composite types map to native language types too.

Where scythe goes past sqlc

sqlc is excellent and I want to be precise about the difference. Scythe is wider on two axes.

Breadth: it generates native, idiomatic code for 10 languages (Rust, Python, TypeScript, Go, Java, Kotlin, C#, Elixir, Ruby, PHP) across 10 databases (PostgreSQL, MySQL, MariaDB, SQLite, DuckDB, CockroachDB, Redshift, SQL Server, Oracle, Snowflake) with 70+ backend drivers. Adding a language or a driver is mostly a declarative type-mapping manifest, not a plugin in a separate repo.

Depth on the SQL itself: sqlc infers nullability primarily from column constraints. Scythe infers it from the query structure (the join and expression analysis above). And it treats SQL as code you lint, format and audit, which sqlc leaves to other tools: 58 built-in rules (23 lint, 35 audit) with sqruff's 69 style rules layered underneath, plus scythe inspect, which runs operational health checks against a live database — foreign keys with no covering index, tables with policies but RLS disabled, duplicate indexes. All of it runs in the compile pipeline, so bad SQL is caught before code generation rather than at runtime.

The one job ORMs still do better

There are ORMs I genuinely like. SQLAlchemy is probably the most sophisticated data-access library in any language, largely because Core and ORM are separable layers rather than one monolith. Drizzle fits its ecosystem well. Ecto is a better-designed thing than the category it gets filed under. This is not an argument that the category was a mistake.

It is an argument about which layer you want, for this app. And there is exactly one job an ORM does that scythe does not answer: bring-your-own-database portability. If your product lets the user pick the database, so the same application code has to run on PostgreSQL or MySQL or SQLite depending on deployment, an ORM abstracts those dialect differences at runtime and that is genuinely valuable.

Scythe takes the opposite bet on purpose. You write SQL for a specific engine, which means you get that engine's real features (Postgres arrays, MySQL JSON functions, whatever you actually deploy on) and let its optimizer do its job. The cost is that targeting multiple engines means multiple SQL files.

So the honest split is simple. If you must run on whatever database the customer brings, use an ORM. If you control the database, which most teams building most products do, then a full ORM is mostly overhead: N+1 queries from lazy loading, SQL you cannot see or predict, model-versus-schema drift, and weak types exactly where SQL gets interesting — aggregates, conditional expressions, nullable joins.

There is also a related decision people conflate with this one, and shouldn't: how thick a layer you put over whatever you chose. Repository, DAO, Unit of Work, or nothing. That is orthogonal — you can put a repository over raw SQL or over Hibernate, and teams do both. It is also where a lot of wasted effort lives, since wrapping a full ORM in a repository is largely rebuilding what a Data Mapper with a Unit of Work already is. The pattern earns its keep further down, where you are the one deciding what a "user" is when it comes back from three joined tables.

For a single-engine architecture on Postgres, generating the layer removes the boilerplate and the runtime dependency, kills a class of hidden query-generation bugs, and leaves you with code you can actually read.

Why this matters more with coding agents

There is a newer reason this approach wins, and it is about who reads the code now. A coding agent working in your repo reasons about plain, explicit, statically typed code far better than it reasons about ORM runtime behavior. With scythe the SQL is right there in a file, the generated function has concrete argument and return types, and the compiler and linter catch a mistake immediately. There is no lazy-loading, no identity map, no query builder assembling strings at runtime for the agent (or you) to simulate in its head. Legible, static, deterministic output is easier for a human to review and easier for a model to get right. SQL-first codegen produces exactly that.

What scythe deliberately does not do

Static queries are the trade. Scythe does not build queries dynamically at runtime the way jOOQ's DSL does. If you need conditional composition you write separate queries, or push the condition into SQL (WHERE ($1 IS NULL OR status = $1)). That is a deliberate choice: static queries are the ones you can lint, analyze, and optimize. If runtime query composition is central to your app, jOOQ or an ORM query builder is the better fit, and if you are a Go-only shop already invested in sqlc, staying there is completely reasonable.

Coming from sqlc, scythe migrate sqlc.yaml reads your existing config and emits a scythe.toml to get you started.

Try it

cargo install scythe-cli
# or prebuilt binaries:
cargo binstall scythe-cli
# or
brew install Goldziher/tap/scythe

To hold the line at commit time, in .pre-commit-config.yaml:

- repo: https://github.com/Goldziher/scythe
  rev: v0.11.0
  hooks:
    - id: scythe-fmt
    - id: scythe-lint
    - id: scythe-audit

• Repo: https://github.com/Goldziher/scythe
• Docs: https://goldziher.github.io/scythe
• Discord: https://discord.gg/xt9WY3GnKR

If you try it against a gnarly query, a chain of CTEs, a pile of window functions, a join graph that should confuse the nullability, I want to know where it gets the types wrong. That is the feedback the inference engine gets built from.

Comments

[Mads Hansen]

The SQL-first build-time model makes schema provenance part of the type system. The generated code is only as correct as the schema snapshot Scythe read.

I’d make CI create an empty database from the committed migrations, run generation against that database, and fail if the generated tree is dirty. Embedding the dialect, database version, migration head, and a schema fingerprint in the generated output would make stale artifacts obvious in review and at startup.

That closes an awkward gap where code compiles perfectly against types generated from a developer’s drifted local schema, then runs against a different migration state in production. For the LEFT JOIN example, I’d also keep executable fixtures with and without a matching order so inferred nullability is checked against the real engine, not only the parser’s model.