Quickstart¶
A 5-minute walkthrough from zero to generated code.
1. Create a Schema¶
Create sql/schema.sql:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
status TEXT NOT NULL DEFAULT 'active'
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
total NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
2. Write Annotated Queries¶
Create sql/queries.sql:
-- @name GetUser
-- @returns :one
SELECT id, name, email, status
FROM users
WHERE id = $1;
-- @name ListUsers
-- @returns :many
SELECT id, name, email, status
FROM users
WHERE status = $1
ORDER BY name;
-- @name CreateUser
-- @returns :exec
INSERT INTO users (name, email)
VALUES ($1, $2);
3. Create scythe.toml¶
Configure your target language and database driver:
Optional: Add row_type to customize the generated row type style. For Python backends, use "pydantic" or "msgspec" instead of the default "dataclass". For TypeScript, use "zod" instead of the default "interface". For example:
See the Configuration guide for all row_type options.
4. Generate Code¶
Output:
[main] Parsing schema...
[main] Analyzing 3 queries...
[main] Writing output to src/generated/queries.*
Done.
5. Generated Code¶
Scythe produces idiomatic, type-safe code for your target language. Each snippet below shows a representative sample: a row struct/type, a :one query, a :many query, and an :exec query -- all generated from the same SQL.
// Auto-generated by scythe. Do not edit.
#![allow(dead_code, unused_imports, clippy::all)]
#[derive(Debug, sqlx::FromRow)]
pub struct GetUserByIdRow {
pub id: i32,
pub name: String,
pub email: Option<String>,
pub status: UserStatus,
pub created_at: chrono::DateTime<chrono::Utc>,
}
pub async fn get_user_by_id(
pool: &sqlx::PgPool,
id: i32,
) -> Result<GetUserByIdRow, sqlx::Error> {
sqlx::query_as!(
GetUserByIdRow,
"SELECT id, name, email, status AS \"status: UserStatus\", \
created_at FROM users WHERE id = $1",
id
)
.fetch_one(pool)
.await
}
#[derive(Debug, sqlx::FromRow)]
pub struct ListActiveUsersRow {
pub id: i32,
pub name: String,
pub email: Option<String>,
}
pub async fn list_active_users(
pool: &sqlx::PgPool,
status: &UserStatus,
) -> Result<Vec<ListActiveUsersRow>, sqlx::Error> {
sqlx::query_as!(
ListActiveUsersRow,
"SELECT id, name, email FROM users WHERE status = $1",
status as &UserStatus
)
.fetch_all(pool)
.await
}
pub async fn update_user_email(
pool: &sqlx::PgPool,
email: &str,
id: i32,
) -> Result<(), sqlx::Error> {
sqlx::query!(
"UPDATE users SET email = $1 WHERE id = $2",
email, id
)
.execute(pool)
.await?;
Ok(())
}
"""Auto-generated by scythe. Do not edit."""
import datetime
from dataclasses import dataclass
from enum import Enum
from psycopg import AsyncConnection
@dataclass
class GetUserByIdRow:
"""Row type for GetUserById query."""
id: int
name: str
email: str | None
status: UserStatus
created_at: datetime.datetime
async def get_user_by_id(
conn: AsyncConnection, *, id: int
) -> GetUserByIdRow | None:
"""Execute GetUserById query."""
cur = await conn.execute(
"SELECT id, name, email, status, created_at "
"FROM users WHERE id = %(id)s",
{"id": id},
)
row = await cur.fetchone()
if row is None:
return None
return GetUserByIdRow(
id=row[0], name=row[1], email=row[2],
status=row[3], created_at=row[4],
)
@dataclass
class ListActiveUsersRow:
"""Row type for ListActiveUsers query."""
id: int
name: str
email: str | None
async def list_active_users(
conn: AsyncConnection, *, status: UserStatus
) -> list[ListActiveUsersRow]:
"""Execute ListActiveUsers query."""
cur = await conn.execute(
"SELECT id, name, email FROM users "
"WHERE status = %(status)s",
{"status": status},
)
rows = await cur.fetchall()
return [
ListActiveUsersRow(id=r[0], name=r[1], email=r[2])
for r in rows
]
async def update_user_email(
conn: AsyncConnection, *, email: str, id: int
) -> None:
"""Execute UpdateUserEmail query."""
await conn.execute(
"UPDATE users SET email = %(email)s "
"WHERE id = %(id)s",
{"email": email, "id": id},
)
/** Auto-generated by scythe. Do not edit. */
import type { PoolClient } from "pg";
export enum UserStatus {
Active = "active",
Inactive = "inactive",
Banned = "banned",
}
/** Row type for GetUserById queries. */
export interface GetUserByIdRow {
id: number;
name: string;
email: string | null;
status: UserStatus;
created_at: Date;
}
/** Fetch a single GetUserByIdRow or null. */
export async function getUserById(
client: PoolClient,
id: number,
): Promise<GetUserByIdRow | null> {
const { rows } = await client.query<GetUserByIdRow>(
`SELECT id, name, email, status, created_at
FROM users WHERE id = $1`,
[id],
);
return rows[0] ?? null;
}
/** Row type for ListActiveUsers queries. */
export interface ListActiveUsersRow {
id: number;
name: string;
email: string | null;
}
/** Fetch all ListActiveUsersRow rows. */
export async function listActiveUsers(
client: PoolClient,
status: UserStatus,
): Promise<ListActiveUsersRow[]> {
const { rows } = await client.query<ListActiveUsersRow>(
`SELECT id, name, email FROM users WHERE status = $1`,
[status],
);
return rows;
}
/** Execute a query returning no rows. */
export async function updateUserEmail(
client: PoolClient,
email: string,
id: number,
): Promise<void> {
await client.query(
`UPDATE users SET email = $1 WHERE id = $2`,
[email, id],
);
}
package queries
import (
"context"
"time"
"github.com/jackc/pgx/v5/pgxpool"
)
type UserStatus string
const (
UserStatusActive UserStatus = "active"
UserStatusInactive UserStatus = "inactive"
UserStatusBanned UserStatus = "banned"
)
type GetUserByIdRow struct {
Id int32 `json:"id"`
Name string `json:"name"`
Email *string `json:"email"`
Status UserStatus `json:"status"`
CreatedAt time.Time `json:"created_at"`
}
func GetUserById(
ctx context.Context, db *pgxpool.Pool, Id int32,
) (GetUserByIdRow, error) {
row := db.QueryRow(ctx,
"SELECT id, name, email, status, created_at "+
"FROM users WHERE id = $1", Id)
var r GetUserByIdRow
err := row.Scan(
&r.Id, &r.Name, &r.Email, &r.Status, &r.CreatedAt,
)
return r, err
}
type ListActiveUsersRow struct {
Id int32 `json:"id"`
Name string `json:"name"`
Email *string `json:"email"`
}
func ListActiveUsers(
ctx context.Context, db *pgxpool.Pool,
Status UserStatus,
) ([]ListActiveUsersRow, error) {
rows, err := db.Query(ctx,
"SELECT id, name, email FROM users WHERE status = $1",
Status)
if err != nil {
return nil, err
}
defer rows.Close()
var result []ListActiveUsersRow
for rows.Next() {
var r ListActiveUsersRow
if err := rows.Scan(&r.Id, &r.Name, &r.Email); err != nil {
return nil, err
}
result = append(result, r)
}
return result, rows.Err()
}
func UpdateUserEmail(
ctx context.Context, db *pgxpool.Pool,
Email string, Id int32,
) error {
_, err := db.Exec(ctx,
"UPDATE users SET email = $1 WHERE id = $2",
Email, Id)
return err
}
// Auto-generated by scythe. Do not edit.
import java.sql.*;
import java.time.OffsetDateTime;
import javax.annotation.Nullable;
public enum UserStatus {
active("active"),
inactive("inactive"),
banned("banned");
private final String value;
UserStatus(String value) { this.value = value; }
public String getValue() { return value; }
}
public record GetUserByIdRow(
int id,
String name,
@Nullable String email,
UserStatus status,
OffsetDateTime created_at
) {
public static GetUserByIdRow fromResultSet(
ResultSet rs
) throws SQLException {
return new GetUserByIdRow(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getObject("status"),
rs.getObject("created_at")
);
}
}
public static GetUserByIdRow getUserById(
Connection conn, int id
) throws SQLException {
try (var ps = conn.prepareStatement(
"SELECT id, name, email, status, created_at "
+ "FROM users WHERE id = ?"
)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return GetUserByIdRow.fromResultSet(rs);
}
return null;
}
}
}
public record ListActiveUsersRow(
int id, String name, @Nullable String email
) {
public static ListActiveUsersRow fromResultSet(
ResultSet rs
) throws SQLException {
return new ListActiveUsersRow(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email")
);
}
}
public static java.util.List<ListActiveUsersRow>
listActiveUsers(
Connection conn, UserStatus status
) throws SQLException {
try (var ps = conn.prepareStatement(
"SELECT id, name, email FROM users WHERE status = ?"
)) {
ps.setObject(1, status);
try (ResultSet rs = ps.executeQuery()) {
var result = new java.util.ArrayList
<ListActiveUsersRow>();
while (rs.next()) {
result.add(
ListActiveUsersRow.fromResultSet(rs));
}
return result;
}
}
}
public static void updateUserEmail(
Connection conn, String email, int id
) throws SQLException {
try (var ps = conn.prepareStatement(
"UPDATE users SET email = ? WHERE id = ?"
)) {
ps.setString(1, email);
ps.setInt(2, id);
ps.executeUpdate();
}
}
import java.sql.Connection
enum class UserStatus(val value: String) {
active("active"),
inactive("inactive"),
banned("banned");
}
data class GetUserByIdRow(
val id: Int,
val name: String,
val email: String?,
val status: UserStatus,
val created_at: java.time.OffsetDateTime,
)
fun getUserById(conn: Connection, id: Int): GetUserByIdRow? {
conn.prepareStatement(
"SELECT id, name, email, status, created_at " +
"FROM users WHERE id = ?"
).use { ps ->
ps.setInt(1, id)
ps.executeQuery().use { rs ->
return if (rs.next()) {
GetUserByIdRow(
id = rs.getInt("id"),
name = rs.getString("name"),
email = rs.getString("email"),
status = rs.getObject("status"),
created_at = rs.getObject("created_at"),
)
} else null
}
}
}
data class ListActiveUsersRow(
val id: Int,
val name: String,
val email: String?,
)
fun listActiveUsers(
conn: Connection, status: UserStatus
): List<ListActiveUsersRow> {
conn.prepareStatement(
"SELECT id, name, email FROM users WHERE status = ?"
).use { ps ->
ps.setObject(1, status)
ps.executeQuery().use { rs ->
val result = mutableListOf<ListActiveUsersRow>()
while (rs.next()) {
result.add(ListActiveUsersRow(
id = rs.getInt("id"),
name = rs.getString("name"),
email = rs.getString("email"),
))
}
return result
}
}
}
fun updateUserEmail(
conn: Connection, email: String, id: Int
) {
conn.prepareStatement(
"UPDATE users SET email = ? WHERE id = ?"
).use { ps ->
ps.setString(1, email)
ps.setInt(2, id)
ps.executeUpdate()
}
}
// Auto-generated by scythe. Do not edit.
using Npgsql;
public static class Queries {
public enum UserStatus {
Active,
Inactive,
Banned,
}
public record GetUserByIdRow(
int Id,
string Name,
string? Email,
UserStatus Status,
DateTimeOffset CreatedAt
);
public static async Task<GetUserByIdRow?> GetUserById(
NpgsqlConnection conn, int id
) {
await using var cmd = new NpgsqlCommand(
"SELECT id, name, email, status, created_at "
+ "FROM users WHERE id = @p1", conn);
cmd.Parameters.AddWithValue("p1", id);
await using var reader = await cmd.ExecuteReaderAsync();
if (!await reader.ReadAsync()) return null;
return new GetUserByIdRow(
reader.GetInt32(0),
reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2),
Enum.TryParse<UserStatus>(
reader.GetString(3), true, out var e)
? e : throw new InvalidOperationException(),
reader.GetFieldValue<DateTimeOffset>(4)
);
}
public record ListActiveUsersRow(
int Id, string Name, string? Email
);
public static async Task<List<ListActiveUsersRow>>
ListActiveUsers(
NpgsqlConnection conn, UserStatus status
) {
await using var cmd = new NpgsqlCommand(
"SELECT id, name, email FROM users "
+ "WHERE status = @p1::user_status", conn);
cmd.Parameters.AddWithValue(
"p1", status.ToString().ToLower());
await using var reader = await cmd.ExecuteReaderAsync();
var results = new List<ListActiveUsersRow>();
while (await reader.ReadAsync()) {
results.Add(new ListActiveUsersRow(
reader.GetInt32(0),
reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2)
));
}
return results;
}
public static async Task UpdateUserEmail(
NpgsqlConnection conn, string email, int id
) {
await using var cmd = new NpgsqlCommand(
"UPDATE users SET email = @p1 WHERE id = @p2", conn);
cmd.Parameters.AddWithValue("p1", email);
cmd.Parameters.AddWithValue("p2", id);
await cmd.ExecuteNonQueryAsync();
}
}
defmodule GetUserByIdRow do
@moduledoc "Row type for GetUserById queries."
@type t :: %__MODULE__{
id: integer(),
name: String.t(),
email: String.t() | nil,
status: UserStatus,
created_at: DateTime.t()
}
defstruct [:id, :name, :email, :status, :created_at]
end
@spec get_user_by_id(pid(), integer()) ::
{:ok, %GetUserByIdRow{}} | {:error, term()}
def get_user_by_id(conn, id) do
case Postgrex.query(conn,
"SELECT id, name, email, status, created_at "
<> "FROM users WHERE id = $1", [id]) do
{:ok, %{rows: [row]}} ->
[id, name, email, status, created_at] = row
{:ok, %GetUserByIdRow{
id: id, name: name, email: email,
status: status, created_at: created_at}}
{:ok, %{rows: []}} -> {:error, :not_found}
{:error, err} -> {:error, err}
end
end
defmodule ListActiveUsersRow do
@moduledoc "Row type for ListActiveUsers queries."
@type t :: %__MODULE__{
id: integer(),
name: String.t(),
email: String.t() | nil
}
defstruct [:id, :name, :email]
end
@spec list_active_users(pid(), UserStatus) ::
{:ok, [%ListActiveUsersRow{}]} | {:error, term()}
def list_active_users(conn, status) do
case Postgrex.query(conn,
"SELECT id, name, email FROM users "
<> "WHERE status = $1", [status]) do
{:ok, %{rows: rows}} ->
results = Enum.map(rows, fn row ->
[id, name, email] = row
%ListActiveUsersRow{
id: id, name: name, email: email}
end)
{:ok, results}
{:error, err} -> {:error, err}
end
end
@spec update_user_email(
pid(), String.t(), integer()
) :: :ok | {:error, term()}
def update_user_email(conn, email, id) do
case Postgrex.query(conn,
"UPDATE users SET email = $1 WHERE id = $2",
[email, id]) do
{:ok, _} -> :ok
{:error, err} -> {:error, err}
end
end
# frozen_string_literal: true
# Auto-generated by scythe. Do not edit.
module Queries
module UserStatus
active = "active"
inactive = "inactive"
banned = "banned"
ALL = [active, inactive, banned].freeze
end
GetUserByIdRow = Data.define(
:id, :name, :email, :status, :created_at
)
def self.get_user_by_id(conn, id)
result = conn.exec_params(
"SELECT id, name, email, status, created_at " \
"FROM users WHERE id = $1", [id])
return nil if result.ntuples.zero?
row = result[0]
GetUserByIdRow.new(
id: row["id"].to_i, name: row["name"],
email: row["email"]&.then { |v| v },
status: row["status"],
created_at: row["created_at"]
)
end
ListActiveUsersRow = Data.define(:id, :name, :email)
def self.list_active_users(conn, status)
result = conn.exec_params(
"SELECT id, name, email FROM users " \
"WHERE status = $1", [status])
result.map do |row|
ListActiveUsersRow.new(
id: row["id"].to_i, name: row["name"],
email: row["email"]&.then { |v| v })
end
end
def self.update_user_email(conn, email, id)
conn.exec_params(
"UPDATE users SET email = $1 WHERE id = $2",
[email, id])
nil
end
end
<?php
declare(strict_types=1);
namespace App\Generated;
// Auto-generated by scythe. Do not edit.
enum UserStatus: string {
case active = "active";
case inactive = "inactive";
case banned = "banned";
}
readonly class GetUserByIdRow {
public function __construct(
public int $id,
public string $name,
public ?string $email,
public UserStatus $status,
public \DateTimeImmutable $created_at,
) {}
public static function fromRow(array $row): self {
return new self(
id: (int) $row['id'],
name: (string) $row['name'],
email: $row['email'] !== null
? (string) $row['email'] : null,
status: UserStatus::from($row['status']),
created_at: new \DateTimeImmutable(
$row['created_at']),
);
}
}
readonly class ListActiveUsersRow {
public function __construct(
public int $id,
public string $name,
public ?string $email,
) {}
}
final class Queries {
public static function getUserById(
\PDO $pdo, int $id
): ?GetUserByIdRow {
$stmt = $pdo->prepare(
"SELECT id, name, email, status, created_at "
. "FROM users WHERE id = :p1");
$stmt->execute(["p1" => $id]);
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
return $row
? GetUserByIdRow::fromRow($row) : null;
}
public static function listActiveUsers(
\PDO $pdo, UserStatus $status
): \Generator {
$stmt = $pdo->prepare(
"SELECT id, name, email FROM users "
. "WHERE status = :p1");
$stmt->execute(["p1" => $status->value]);
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
yield ListActiveUsersRow::fromRow($row);
}
}
public static function updateUserEmail(
\PDO $pdo, string $email, int $id
): void {
$stmt = $pdo->prepare(
"UPDATE users SET email = :p1 "
. "WHERE id = :p2");
$stmt->execute(["p1" => $email, "p2" => $id]);
}
}
6. Validate and Lint¶
# Validate SQL parses and types resolve correctly
scythe check
# Lint SQL for correctness, performance, and style
scythe lint
Next Steps¶
- Configuration -- full scythe.toml reference
- Annotations -- all 7 annotation types
- Type Inference -- how nullability analysis works
- Linting -- 22 built-in rules plus sqruff integration