Scythe

Scythe SQL-to-Code Generator

Scythe compiles annotated SQL into type-safe database access code. You write SQL queries with annotations, scythe generates the boilerplate -- structs, functions, type mappings -- in 10 languages across 10 databases with 56 backends. Built-in linting (59 rules) and formatting catch SQL bugs before they ship.

Use this skill when:

• Writing SQL queries with scythe annotations (@name, @returns, @optional, etc.)
• Configuring scythe.toml for code generation
• Choosing which backend driver to use for a language/database combination
• Linting or formatting SQL files
• Setting up pre-commit hooks for SQL quality
• Migrating from sqlc to scythe

Installation

cargo install scythe-cli
# or
brew install Goldziher/tap/scythe

Quick Start

1. Write annotated SQL:

-- @name GetUserById
-- @returns :one
SELECT id, name, email FROM users WHERE id = $1;

2. Configure scythe.toml:

[scythe]
version = "1"

[[sql]]
name = "main"
engine = "postgresql"
schema = ["sql/schema/*.sql"]
queries = ["sql/queries/*.sql"]

[[sql.gen]]
backend = "rust-sqlx"
output = "src/generated"

3. Generate code:

scythe generate

CLI Commands

scythe generate [--config <path>]              # Generate code (default: scythe.toml)
scythe check [--config <path>]                 # Validate SQL without generating
scythe lint [--config <path>] [--fix] [files]  # Lint SQL files
scythe audit [--config <path>] [files]         # Security scan (SC-SEC*)
scythe inspect [database_url]                  # Operational checks on a live database
scythe fmt [--config <path>] [--check] [files] # Format SQL files
scythe migrate [sqlc_config]                   # Convert sqlc project

Exit codes: 0 clean, 2 on error-severity findings from check, lint, audit or inspect, 1 on operational failure (unreadable config, unparseable SQL, I/O error). --exit-zero collapses 2 to 0 and leaves 1 alone. Findings failures and "scythe could not run" are deliberately distinguishable, so CI never mistakes a crashed run for a clean one.

See references/cli-reference.md for the full flag tables.

Annotations

All annotations use -- @ prefix in SQL comments.

@returns values

@optional rewriting

@optional rewrites WHERE col = $1 into WHERE ($1 IS NULL OR col = $1). Works with: =, <>, !=, >, <, >=, <=, LIKE, ILIKE.

-- @name SearchUsers
-- @returns :many
-- @optional status
-- @optional name_pattern
SELECT id, name FROM users
WHERE status = $1 AND name ILIKE $2;

@json typed mapping

-- @name GetEvent
-- @returns :one
-- @json data = EventData
SELECT id, data FROM events WHERE id = $1;

Generates Json<EventData> (Rust), EventData (TypeScript), etc.

@returns :grouped

-- @name GetUsersWithOrders
-- @returns :grouped
-- @group_by users.id
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u JOIN orders o ON o.user_id = u.id;

Generates a parent struct with nested child collection.

Configuration

scythe.toml structure

[scythe]
version = "1"

[[sql]]
name = "main"
engine = "postgresql"           # postgresql, mysql, sqlite, duckdb, cockroachdb, mssql, oracle, mariadb, redshift, snowflake
schema = ["sql/schema/*.sql"]
queries = ["sql/queries/*.sql"]

# Multiple backends from one SQL block
[[sql.gen]]
backend = "rust-sqlx"
output = "src/generated/rust"

[[sql.gen]]
backend = "typescript-pg"
output = "src/generated/ts"
row_type = "zod"                # optional: interface (default) or zod

[[sql.gen]]
backend = "python-psycopg3"
output = "src/generated/python"
row_type = "pydantic"           # optional: dataclass (default), pydantic, or msgspec

# Type overrides
[[sql.type_overrides]]
column = "users.metadata"      # specific column
type = "json"

[[sql.type_overrides]]
db_type = "citext"             # all columns of this type
type = "string"

# Lint configuration
[lint.categories]
safety = "error"
naming = "warn"

[lint.rules]
"SC-S03" = "off"               # disable SELECT * warning

Engine aliases

row_type options

structs_only option

structs_only = "true" emits only row types (interfaces/Zod schemas, enums, composites) and suppresses query functions and the driver import. Supported by rust-sqlx and every TypeScript backend (typescript-postgres, typescript-pg, typescript-mysql2, typescript-better-sqlite3, typescript-node-sqlite, typescript-wasm-sqlite, typescript-duckdb, typescript-mssql, typescript-oracledb, typescript-snowflake, typescript-kysely). Combine with row_type = "zod" for a types-only package with no driver dependency:

[[sql.gen]]
backend = "typescript-pg"
output = "src/generated/types"
row_type = "zod"
structs_only = "true"

Supported Backends

PostgreSQL

MySQL

SQLite

typescript-node-sqlite and typescript-wasm-sqlite generate synchronous code -- plain export function, no async, no Promise -- unlike every other TypeScript backend (#66). typescript-node-sqlite requires --experimental-sqlite on Node 22 and is unflagged from Node 23.4 onward; generated code needs Node 23.4+ to run without the flag.

javascript-postgres, javascript-pg, javascript-mysql2, and javascript-better-sqlite3 are a JSDoc emit mode on the matching TypeScript backend, not separate backends (#81). Output is queries.js: plain ESM, no driver import (handles are typed inline as import("pg").PoolClient in @param), row types as @typedef {object} with nullable columns always {T | null}. row_type = "zod", outer_join_unions, and field_case = "camelCase" are hard errors there -- each needs TypeScript-only syntax; structs_only and field_case = "snake_case" work. No other TypeScript backend has a JavaScript counterpart.

DuckDB

CockroachDB

CockroachDB uses PostgreSQL backends with engine = "cockroachdb": rust-sqlx, python-psycopg3, typescript-pg, go-pgx, java-jdbc, kotlin-jdbc, csharp-npgsql, ruby-pg, php-pdo, php-amphp, elixir-postgrex.

MSSQL

Oracle

php-pdo does not support Oracle (no oci engine mapping) despite PDO's own OCI driver; there is no php-pdo Oracle backend.

MariaDB

MariaDB uses MySQL drivers with MariaDB-specific type resolution: rust-sqlx, python-aiomysql, typescript-mysql2, typescript-kysely, javascript-mysql2, go-database-sql, java-jdbc, kotlin-jdbc, csharp-mysqlconnector, elixir-myxql, ruby-mysql2, ruby-trilogy, php-pdo, php-amphp.

Redshift

Redshift uses PostgreSQL backends with engine = "redshift": rust-sqlx, rust-tokio-postgres, python-psycopg3, python-asyncpg, typescript-pg, typescript-postgres, typescript-kysely, javascript-pg, javascript-postgres, go-pgx, java-jdbc, kotlin-jdbc, csharp-npgsql, elixir-postgrex, ruby-pg, php-pdo.

Snowflake

Type System

Type resolution pipeline

SQL type  -->  neutral type  -->  language type
SERIAL         int32              i32 (Rust) / int (Python) / number (TS)
TIMESTAMPTZ    datetime_tz        chrono::DateTime<Utc> / datetime / Date
TEXT[]         array<string>      Vec<String> / list[str] / string[]

Type inference

Scythe infers nullability from SQL context:

• LEFT JOIN: Right-side columns become nullable
• RIGHT JOIN: Left-side columns become nullable
• COALESCE: Result is non-nullable
• Aggregates: COUNT is non-nullable; SUM/AVG/MIN/MAX are nullable
• CASE WHEN: Nullable unless all branches and ELSE are non-nullable
• Subqueries: Scalar subqueries are nullable unless the subquery is a single ungrouped, non-windowed aggregate

Override with @nullable and @nonnull annotations.

Custom type overrides

# Column-level (takes precedence)
[[sql.type_overrides]]
column = "users.metadata"
type = "json"

# Database type-level
[[sql.type_overrides]]
db_type = "ltree"
type = "string"

Common PostgreSQL extension mappings:

Linting

23 built-in scythe lint rules + 35 audit rules = 58 built-in, plus sqruff's 69 style rules via integration.

The 11 SC-PRV* provenance rules and the 7 SC-DRF* schema drift rules are not part of the 58. They run only from scythe check and never appear in scythe lint or scythe audit --list-rules output.

Rule categories

Configuration

[lint.categories]
safety = "error"
naming = "warn"
style = "off"

[lint.rules]
"SC-S03" = "off"        # allow SELECT *
"SC-N03" = "error"      # enforce query naming

SC-PRV* and SC-DRF* are configured from the same table -- scythe check applies [lint] to their registries before resolving severities:

[lint.rules]
"SC-PRV02" = "error"    # fail CI on scythe version drift
"SC-DRF02" = "error"    # fail on tables the DDL never declares

[lint.categories]
provenance = "off"      # skip provenance verification
drift = "off"           # skip schema drift checking

Pre-commit Hooks

repos:
  - repo: https://github.com/Goldziher/scythe
    rev: v0.18.1
    hooks:
      - id: scythe-fmt       # Format SQL files
      - id: scythe-lint      # Lint SQL with auto-fix
      - id: scythe-audit     # Security scan
      - id: scythe-inspect   # Live-database operational checks
      - id: scythe-generate  # Regenerate code on SQL changes
      - id: scythe-check     # Validate SQL without generating

Common Pitfalls

1. Missing @returns: Every query needs both @name and @returns annotations.
2. :one vs :many: Use :one only for queries guaranteed to return 0-1 rows (WHERE id = $1). :one returns Option<T> / T | null.
3. LEFT JOIN nullability: Columns from the right side of LEFT JOIN are always nullable. Use @nonnull to override if you know better.
4. @optional parameter names: Must match a parameter in the query. Typos produce errors.
5. Engine mismatch: Backend must support the configured engine (e.g., python-asyncpg only works with postgresql).
6. Multiple [[sql.gen]] blocks: Each needs its own output directory.
7. Type overrides: column and db_type are mutually exclusive in each override entry.

Additional Resources

Detailed reference files for specific topics:

• Configuration Reference -- Full scythe.toml reference
• Annotations Reference -- All annotations with examples
• Backends Reference -- All 56 backend names with engine support
• Lint Rules Reference -- All rules with codes and examples
• CLI Reference -- All commands, flags, exit codes

Full documentation: https://goldziher.github.io/scythe GitHub: https://github.com/Goldziher/scythe