grizzle

README

Grizzle

A type-safe, code-generated query builder and migration toolkit for Go — inspired by Drizzle ORM.

Grizzle generates Go structs that mirror your database schema. Every column is a strongly-typed handle: you can only compare a UUIDColumn with another UUID, a StringColumn with a string, and so on. Type mismatches become compile errors, not runtime surprises.

Features

• Type-safe query builders — SELECT, INSERT, UPDATE, DELETE, UPSERT
• Aggregate functions — COUNT, SUM, AVG, MAX, MIN with HAVING / ORDER BY
• Window functions — ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, LAST_VALUE with OVER / PARTITION BY / ORDER BY
• CASE expressions — searched CASE (WHEN cond THEN val) and simple CASE (CASE col WHEN val THEN result)
• CTEs — WITH name AS (SELECT …) prepended to any SELECT
• Subquery support — EXISTS, NOT EXISTS, IN (subquery), NOT IN (subquery), FROM (subquery)
• Relations — BelongsTo, HasMany, HasOne with JoinRel / InnerJoinRel
• Eager loading — batch preloading to avoid N+1 queries
• JSONB operators — ->, ->>, #>, #>>, @>, <@, ?, ?|, ?&
• Code generator — grizzle generate turns a schema file into Go table types
• Migration kit — diff live DB vs desired schema, apply DDL atomically with history tracking
• Multi-dialect — PostgreSQL (primary), MySQL / MariaDB, SQLite

---

Installation

go get github.com/sofired/grizzle

Install the CLI:

go install github.com/sofired/grizzle/cmd/grizzle@latest

---

Quick Start

1. Define your schema

# schema.grizzle
table "users" {
  column "id"         { type = "uuid"        default = "gen_random_uuid()" primary_key = true }
  column "username"   { type = "varchar(80)" not_null = true unique = true }
  column "email"      { type = "varchar(255)" not_null = true }
  column "realm_id"   { type = "uuid"        not_null = true }
  column "created_at" { type = "timestamptz" default = "now()" not_null = true }

  index "users_username_idx" { columns = ["username"] }
}

table "realms" {
  column "id"   { type = "uuid"        default = "gen_random_uuid()" primary_key = true }
  column "name" { type = "varchar(80)" not_null = true unique = true }
}

2. Generate table types

grizzle generate --schema schema.grizzle --out db/schema.go --package db

This creates typed table handles like:

var UsersT = UsersTable{
    ID:        expr.UUIDColumn{...},
    Username:  expr.StringColumn{...},
    Email:     expr.StringColumn{...},
    RealmID:   expr.UUIDColumn{...},
    CreatedAt: expr.TimestampColumn{...},
}

---

Query Builders

All builders produce (sql string, args []any) via .Build(dialect).

SELECT

import (
    "github.com/sofired/grizzle/query"
    "github.com/sofired/grizzle/dialect"
    db "myapp/db"
)

// SELECT * FROM "users"
q := query.Select().From(db.UsersT)

// SELECT "users"."id", "users"."username" FROM "users" WHERE "users"."email" = $1
q := query.Select(db.UsersT.ID, db.UsersT.Username).
    From(db.UsersT).
    Where(db.UsersT.Email.EQ("alice@example.com"))

sql, args := q.Build(dialect.Postgres)

Filtering

// WHERE ... AND ...
Where(expr.And(
    db.UsersT.RealmID.EQ(realmID),
    db.UsersT.Username.ILike("%alice%"),
))

// WHERE ... OR ...
Where(expr.Or(
    db.UsersT.Email.EQ("a@x.com"),
    db.UsersT.Email.EQ("b@x.com"),
))

// nil conditions are dropped — safe for dynamic filters
Where(expr.And(
    whenPtr(req.RealmID, func(id uuid.UUID) expr.Expression {
        return db.UsersT.RealmID.EQ(id)
    }),
    whenPtr(req.Email, func(e string) expr.Expression {
        return db.UsersT.Email.EQ(e)
    }),
))

Ordering, pagination

query.Select().From(db.UsersT).
    OrderBy(db.UsersT.CreatedAt.Desc(), db.UsersT.Username.Asc()).
    Limit(20).
    Offset(40)

IN / NOT IN

Where(db.UsersT.ID.In(id1, id2, id3))
Where(db.UsersT.Username.NotIn("admin", "root"))

Aggregates

// SELECT "realm_id", COUNT(*) AS "cnt" FROM "users" GROUP BY "realm_id" HAVING COUNT(*) > $1
query.Select(db.UsersT.RealmID, expr.Count().As("cnt")).
    From(db.UsersT).
    GroupBy(db.UsersT.RealmID).
    Having(expr.Count().GT(5)).
    OrderBy(expr.Count().Desc())

// Other aggregate functions
expr.CountCol(db.UsersT.Email)         // COUNT("email")
expr.CountDistinct(db.UsersT.RealmID) // COUNT(DISTINCT "realm_id")
expr.Sum(db.UsersT.Score)             // SUM("score")
expr.Avg(db.UsersT.Score)             // AVG("score")
expr.Max(db.UsersT.CreatedAt)         // MAX("created_at")
expr.Min(db.UsersT.CreatedAt)         // MIN("created_at")

Subqueries

// WHERE EXISTS (SELECT * FROM realms WHERE realms.id = users.realm_id)
sub := query.Select().From(db.RealmsT).Where(db.RealmsT.ID.EQCol(db.UsersT.RealmID))
query.Select(db.UsersT.ID).From(db.UsersT).Where(query.Exists(sub))

// WHERE realm_id IN (SELECT id FROM realms WHERE name = $1)
sub := query.Select(db.RealmsT.ID).From(db.RealmsT).Where(db.RealmsT.Name.EQ("acme"))
query.Select(db.UsersT.ID).From(db.UsersT).Where(query.SubqueryIn(db.UsersT.RealmID, sub))

// FROM (SELECT realm_id, COUNT(*) AS cnt FROM users GROUP BY realm_id) AS sub
inner := query.Select(db.UsersT.RealmID, expr.Count().As("cnt")).
    From(db.UsersT).GroupBy(db.UsersT.RealmID)
query.Select().From(query.FromSubquery(inner, "sub"))

Parameter numbers are shared between outer and inner queries — no collisions.

Window Functions

// ROW_NUMBER() OVER (PARTITION BY realm_id ORDER BY created_at ASC) AS "rn"
expr.RowNumber().
    PartitionBy(db.UsersT.RealmID).
    OrderBy(db.UsersT.CreatedAt.Asc()).
    As("rn")

// RANK() OVER (PARTITION BY realm_id ORDER BY score DESC) AS "score_rank"
expr.Rank().PartitionBy(db.UsersT.RealmID).OrderBy(db.UsersT.Score.Desc()).As("score_rank")

// Other window functions
expr.DenseRank()                    // DENSE_RANK()
expr.Lead(db.UsersT.Username)       // LEAD("username")
expr.Lag(db.UsersT.Username)        // LAG("username")
expr.FirstValue(db.UsersT.Score)    // FIRST_VALUE("score")
expr.LastValue(db.UsersT.Score)     // LAST_VALUE("score")
expr.WinSum(db.UsersT.Score)        // SUM("score") — as window function
expr.WinCount()                     // COUNT(*) — as window function

Window expressions implement SelectableColumn so they can be used in SELECT and ORDER BY.

CASE Expressions

// Searched CASE: CASE WHEN cond THEN val … END
expr.Case().
    When(db.UsersT.Score.GTE(90), expr.Lit("A")).
    When(db.UsersT.Score.GTE(75), expr.Lit("B")).
    Else(expr.Lit("C")).
    As("grade")

// Simple CASE: CASE col WHEN val THEN result … END
expr.SimpleCase(db.UsersT.Status).
    WhenVal("active", expr.Lit("Active")).
    WhenVal("banned", expr.Lit("Banned")).
    Else(expr.Lit("Unknown")).
    As("status_label")

expr.Lit(v) wraps any Go value as a bound parameter — use it in THEN and ELSE clauses.

CTEs (Common Table Expressions)

// WITH active AS (SELECT …) SELECT * FROM active
active := query.Select(db.UsersT.ID, db.UsersT.Email).
    From(db.UsersT).
    Where(db.UsersT.DeletedAt.IsNull())

query.Select().
    With("active", active).
    From(query.CTERef("active"))

// Multiple CTEs render as WITH a AS (…), b AS (…)
query.Select().
    With("a", subA).
    With("b", subB).
    From(query.CTERef("a"))

Parameters are shared across all CTEs and the outer query — placeholders remain sequential.

INSERT

type NewUser struct {
    ID       uuid.UUID `db:"id"`
    Username string    `db:"username"`
    Email    string    `db:"email"`
}

q := query.InsertInto(db.UsersT).
    Values(NewUser{ID: uuid.New(), Username: "alice", Email: "alice@x.com"}).
    Returning(db.UsersT.ID, db.UsersT.CreatedAt)

sql, args := q.Build(dialect.Postgres)

UPSERT

// PostgreSQL: ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email
query.InsertInto(db.UsersT).
    Values(row).
    OnConflict(db.UsersT.Username).
    DoUpdateSetExcluded(db.UsersT.Email, db.UsersT.UpdatedAt)

// DO NOTHING
query.InsertInto(db.UsersT).Values(row).OnConflict(db.UsersT.Username).DoNothing()

// MySQL: ON DUPLICATE KEY UPDATE (auto-detected from dialect)
query.InsertInto(db.UsersT).
    Values(row).
    OnConflict(db.UsersT.Username).
    DoUpdateSetExcluded(db.UsersT.Email)

// INSERT IGNORE (MySQL) / INSERT OR IGNORE (SQLite) — silently skip conflicting rows
query.InsertInto(db.UsersT).Values(row).IgnoreConflicts()

UPDATE

query.Update(db.UsersT).
    Set(db.UsersT.Email, "new@x.com").
    Set(db.UsersT.UpdatedAt, time.Now()).
    Where(db.UsersT.ID.EQ(userID)).
    Returning(db.UsersT.UpdatedAt)

DELETE

query.DeleteFrom(db.UsersT).
    Where(db.UsersT.ID.EQ(userID)).
    Returning(db.UsersT.ID)

---

Relations

Define relations once, reuse in queries:

// db/relations.go
var UserRealm = query.BelongsTo("realm", db.RealmsT, db.RealmsT.ID.EQCol(db.UsersT.RealmID))
var RealmUsers = query.HasMany("users", db.UsersT, db.UsersT.RealmID.EQCol(db.RealmsT.ID))

Use in SELECT:

// LEFT JOIN "realms" ON "realms"."id" = "users"."realm_id"
query.Select(db.UsersT.ID, db.RealmsT.Name).
    From(db.UsersT).
    JoinRel(UserRealm)

// INNER JOIN
query.Select().From(db.UsersT).InnerJoinRel(UserRealm)

Eager loading (avoid N+1)

// Step 1: load users
users, _ := pgx.CollectRows(rows, pgx.RowToStructByName[User])

// Step 2: collect foreign keys
realmIDs := query.UniqueUUIDs(query.Pluck(users, func(u User) uuid.UUID { return u.RealmID }))

// Step 3: batch load realms
realmsQ := query.PreloadUUIDs(query.Select().From(db.RealmsT), db.RealmsT.ID, realmIDs)
realms, _ := pgx.CollectRows(realmRows, pgx.RowToStructByName[Realm])

// Step 4: index and attach
realmByID := query.Index(realms, func(r Realm) uuid.UUID { return r.ID })
for i, u := range users {
    users[i].Realm = realmByID[u.RealmID]
}

---

JSONB Operators (PostgreSQL)

// col -> 'key'        (returns JSON)
db.UsersT.Attributes.Arrow("role")

// col ->> 'key'       (returns text)
db.UsersT.Attributes.ArrowText("name")

// col #> ARRAY['a','b']   (path extraction, returns JSON)
db.UsersT.Attributes.Path("address", "city")

// col @> $1           (contains)
db.UsersT.Attributes.Contains(map[string]any{"role": "admin"})

// col ? $1            (key exists)
db.UsersT.Attributes.HasKey("role")

// col ?| $1           (any key exists)
db.UsersT.Attributes.HasAnyKey("role", "scope")

// col ?& $1           (all keys exist)
db.UsersT.Attributes.HasAllKeys("role", "region")

---

Migration Kit

Applying migrations

import "github.com/sofired/grizzle/kit"

// Introspect live DB, diff, apply DDL, record in _grizzle_migrations
err := kit.Migrate(ctx, pool,
    db.UsersTableDef,
    db.RealmsTableDef,
)

Dry run

changes, err := kit.DryRun(ctx, pool, db.UsersTableDef, db.RealmsTableDef)
for _, c := range changes {
    fmt.Println(c)
}

Status

// Show applied migrations and any pending changes
kit.Status(ctx, pool, db.UsersTableDef, db.RealmsTableDef)

MySQL / SQLite migrations

import (
    _ "github.com/go-sql-driver/mysql"
    _ "github.com/mattn/go-sqlite3"
    "github.com/sofired/grizzle/kit"
)

// MySQL
db, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb?parseTime=true")
result, err := kit.MigrateMySQL(ctx, db, db.UsersTableDef, db.RealmsTableDef)

// SQLite
db, _ := sql.Open("sqlite3", "./mydb.sqlite?_foreign_keys=on")
result, err := kit.MigrateSQLite(ctx, db, db.UsersTableDef, db.RealmsTableDef)

// Both support the same Push / DryRun / Migrate / Status API:
kit.PushMySQL(ctx, db, ...)       // apply without history
kit.DryRunMySQL(ctx, db, ...)     // preview changes, no apply
kit.StatusMySQL(ctx, db, ...)     // show history + pending

kit.PushSQLite(ctx, db, ...)
kit.DryRunSQLite(ctx, db, ...)
kit.StatusSQLite(ctx, db, ...)

---

CLI

grizzle generate --schema schema.grizzle --out db/schema.go --package db
grizzle migrate  --dsn "postgres://..." --schema schema.grizzle [--dry-run]
grizzle status   --dsn "postgres://..." --schema schema.grizzle

---

Dialects

Feature	PostgreSQL	MySQL / MariaDB	SQLite
Named params	$1, $2 …	?	?
RETURNING	✓	✗	✓ (3.35+)
ON CONFLICT	✓	✗	✓
ON DUPLICATE KEY	✗	✓	✗
INSERT IGNORE	✗	✓	✓ (OR IGNORE)
JSONB operators	✓	✗	✗
UUID native type	✓	CHAR(36)	TEXT
Migrations (kit)	✓	✓	✓
ALTER COLUMN	✓	✓	comment stub†

† SQLite does not support ALTER COLUMN — type/nullability/default changes require a manual table rebuild. The migration kit emits a -- SQLite does not support ALTER COLUMN comment so you can identify and apply these manually.

---

Acknowledgements

Grizzle is a Go port of Drizzle ORM, originally created by the Drizzle Team. The API design, schema definition patterns, query builder concepts, and migration toolkit architecture in this project are derived from and inspired by their work.

Drizzle ORM is licensed under the Apache License 2.0. The Go implementation is original work by the Grizzle maintainers and contributors.

License

MIT