Back

ORM

A lightweight, fluent ORM built into the framework. Uses generics, cached reflection, and explicit SQL with placeholders — no magic, no surprises.

import "kyrux/core/orm"

Model definition

A model is any Go struct with exported fields. Use the kyrux tag to configure behavior:

type Post struct {
    ID        int64     `kyrux:"pk"`
    Titulo    string    `kyrux:"size:200"`
    Slug      string    `kyrux:"size:200,unique"`
    Publicado bool      `kyrux:"default:false"`
    CriadoEm time.Time `kyrux:"column:criado_em,default:NOW()"`
}

// Model with sensitive fields:
type Cliente struct {
    ID    int64  `kyrux:"pk"`
    Nome  string
    CPF   string `kyrux:"size:14,encrypt"` // AES-256-GCM: encrypted in the database, decrypted on read
    Token string `kyrux:"hash"`            // Argon2id: hashed on write, never reversed
}

Available tags

Tag	Description
`kyrux:"pk"`	Primary key. Ignored on INSERT, filled back in after creation.
`kyrux:"column:name"`	Overrides the SQL column name. Default: `snake_case` of the Go field name.
`kyrux:"size:N"`	Maximum length — used by `makemigrations` to generate `VARCHAR(N)`.
`kyrux:"unique"`	Generates `CREATE UNIQUE INDEX` in `makemigrations` (only affects the migration).
`kyrux:"default:value"`	SQL value used on INSERT when the field is the Go zero value. E.g. `default:NOW()`, `default:true`, `default:0`. SQL literal — no `?` placeholder.
`kyrux:"hash"`	Automatic Argon2id+pepper hashing on write (Create/Update). Never reversed.
`kyrux:"encrypt"`	AES-256-GCM — encrypts on write, decrypts automatically on read. Requires `FIELD_ENCRYPTION_KEY`.
`kyrux:"login"`	Exclusive to `auth.User`. Marks the login field (username or email). Only one field per struct. Immutable after the first migrate.
`kyrux:"autonow"`	Automatic `CURRENT_TIMESTAMP` on every `Update` (e.g. `updated_at`). Also fills in on `Create` if the field is zero.
`kyrux:"fk:table"`	`REFERENCES table(id)` + index in the migration. The referenced table must exist beforehand (declare it first, or in an earlier migration).

default:value — when the field holds the Go zero value ("", 0, false), the ORM uses the literal directly in the SQL (VALUES (..., NOW(), ...)), without passing it as an argument. Useful for timestamps, UUIDs, and any database SQL function. Since it goes in without a placeholder, value only accepts a number, a single-quoted string, or an identifier/keyword optionally followed by () (NOW(), CURRENT_TIMESTAMP, true, 'pending') — any other form makes the framework reject the model with a panic the first time it's used (a configuration error, not a runtime one). The same applies to fk:table and fklabel:column: plain identifiers only.

Table name

Automatically generated from the struct name in plural snake_case:

Struct	Table
`User`	`users`
`Post`	`posts`
`Category`	`categories`
`UserProfile`	`user_profiles`
`Address`	`addresses`

`From` vs `FromDB`

Function	Takes	Use
`orm.From[T](connName string)`	The name of a registered connection (`"default"`, `"analytics"`, ...)	When you only have the connection name on hand, without `fw.DB` nearby.
`orm.FromDB[T](db *database.DB)`	An already-resolved connection (`fw.DB.Use()`, `fw.DB.Use("analytics")`, with or without `WithSchema`)	The standard used throughout this section — what views and functions in `models/` normally receive.

orm.From[T]("default") and orm.FromDB[T](fw.DB.Use()) end up in the same place — From just resolves the name to a *database.DB internally and delegates to FromDB. Passing a *database.DB to From (or a string to FromDB) doesn't compile — they're different signatures; pick whichever matches what you already have on hand.

Reading

All — fetch everything

db := fw.DB.Use()

posts, err := orm.FromDB[Post](db).All()

// With filters
posts, err := orm.FromDB[Post](db).
    Where("publicado = ?", true).
    OrderBy("criado_em DESC").
    Limit(10).
    Offset(20).  // page 3
    All()

First — fetch the first row

Returns sql.ErrNoRows if no row is found.

post, err := orm.FromDB[Post](db).
    Where("slug = ?", slug).
    First()

if errors.Is(err, sql.ErrNoRows) {
    ctx.Error(404)
    return
}

Count — count rows

total, err := orm.FromDB[Post](db).Count()

publicados, err := orm.FromDB[Post](db).
    Where("publicado = ?", true).
    Count()

Chainable filter methods

For common filters (equality, comparison, LIKE, NULL), prefer the typed methods below: they validate the column name and carry no risk of accidental SQL injection. Where/OrWhere still exist for whatever the typed methods don't cover — free-form SQL, so safety depends entirely on never concatenating user input into the condition, only passing it via args.

Method	Description
`WhereEq(col string, val any)` / `OrWhereEq(...)`	`col = ?` — typed, safe form of `Where("col = ?", val)`.
`WhereNe(col string, val any)`	`col <> ?`
`WhereGt/WhereGte/WhereLt/WhereLte(col string, val any)`	`col >`, `>=`, `<`, `<=` `?`
`WhereLike(col, pattern string)` / `OrWhereLike(...)`	`col LIKE ?` — include the `%` in the pattern yourself: `WhereLike("nome", "%maria%")`.
`WhereNull(col string)` / `WhereNotNull(col string)`	`col IS NULL` / `col IS NOT NULL`.
`Where(cond string, args ...any)`	Adds an `AND` condition in free-form SQL — use `?` as a placeholder, never concatenate values into the string. Multiple `Where` calls are combined with `AND`. Identical to `WhereSQL`, kept for compatibility.
`OrWhere(cond string, args ...any)`	Adds an `OR` condition in free-form SQL (precedence: AND binds tighter, as in SQL). Identical to `OrWhereSQL`.
`WhereSQL(cond string, args ...any)` / `OrWhereSQL(...)`	Synonyms for `Where`/`OrWhere` — the recommended name when the filter is intentionally free-form SQL, making that explicit when reading the code.
`WhereIn(col string, vals ...any)`	`col IN (...)` with placeholder expansion. Accepts a slice: `WhereIn("id", ids)`. Empty list = no results; a list with more than 5000 values returns an error (paginate or use a subquery/JOIN instead).
`OrderBy(cols ...string)`	Sets `ORDER BY` — multiple columns: `OrderBy("criado_em DESC", "id ASC")`. `orm.Asc("col")`/`orm.Desc("col")` are alternative syntax for building this programmatically: `OrderBy(orm.Desc("criado_em"), orm.Asc("id"))`.
`Join(table, on)` / `LeftJoin(table, on)`	JOIN to filter by the related table. The SELECT becomes `base_table.*` — the result is still `[]T`.
`Select(cols ...string)`	Restricts the `SELECT` columns (default: `SELECT *`). Columns outside the list keep the struct's zero value — don't use this if you plan to later `Update` the whole record back.
`Distinct()`	`SELECT DISTINCT`.
`Limit(n int)`	Maximum number of rows returned.
`Offset(n int)`	Rows to skip — use with `Limit` for pagination.

// Before (free-form SQL) — still works, but with no column validation:
orm.FromDB[Post](db).Where("status = ?", status).All()

// Preferred — column validated, same result:
orm.FromDB[Post](db).WhereEq("status", status).All()

Execution methods

Method	Returns	Description
`All()`	`[]T, error`	All rows matching the filter.
`Each(fn func(*T) error)`	`error`	Row-by-row streaming — O(1) memory, for large result sets.
`First()` / `Last()`	`*T, error`	First/last row (Last reverses the ordering; without OrderBy it uses PK DESC).
`Exists()`	`bool, error`	`SELECT 1 ... LIMIT 1` — cheaper than Count.
`Count()`	`int64, error`	Number of rows matching the filter.
`Sum/Avg/Min/Max(col)`	`float64, error`	Numeric aggregations (NULL → 0).
`GetOrCreate(defaults *T)`	`*T, bool, error`	Looks up; if not found, inserts `defaults` (created=true). Fill `defaults` with the filter values too. If `Create` collides with a `UNIQUE` constraint (a race: another process created the row in between the lookup and the insert), it retries the lookup automatically instead of propagating the error — this only truly protects you when the filter columns actually have a `UNIQUE` constraint in the database.
`UpdateOrCreate(values, defaults *T)`	`bool, error`	Updates the rows matching the filter; if none, inserts `defaults`. Same race recovery as `GetOrCreate`.

Creating

Always pass a pointer so the PK field gets filled back in.

post := Post{
    Titulo:    "Hello Kyrux",
    Slug:      "ola-kyrux",
    Publicado: true,
}

err := orm.Create(db, &post)
fmt.Println(post.ID) // filled with the ID generated by the database

PostgreSQL uses RETURNING internally — no extra round-trip. MySQL and SQLite use LastInsertId.

To insert many rows, CreateAll performs a single multi-VALUES INSERT (chunks of 500 — equivalent to Django's bulk_create):

posts := []*Post{{Titulo: "a"}, {Titulo: "b"}, {Titulo: "c"}}
err := orm.CreateAll(db, posts)
// On PostgreSQL, posts[i].ID are filled back in.

Updating

Requires at least one Where to avoid accidental updates to the entire table.

err := orm.FromDB[Post](db).
    Where("id = ?", 1).
    Update(map[string]any{
        "titulo":    "Updated title",
        "publicado": true,
    })

Deleting

Requires at least one Where to avoid accidental deletion of the entire table.

err := orm.FromDB[Post](db).
    Where("id = ?", 1).
    Delete()

Pagination

Paginate runs a COUNT(*) + SELECT with LIMIT/OFFSET in a single call and returns metadata ready to use in the template.

page := ctx.QueryInt("page", 1)

p, err := orm.FromDB[Post](db).
    Where("publicado = ?", true).
    OrderBy("criado_em DESC").
    Paginate(page, 20) // current page, items per page

// p.Items      → []Post for the current page
// p.Total      → total number of records
// p.TotalPages → number of pages
// p.HasNext    → true if there's a next page
// p.HasPrev    → true if there's a previous page
// p.Page       → current page
// p.PageSize   → items per page

render.For("blog").Render(ctx, "lista.html", map[string]any{
    "page": p,
})

For large tables and infinite feeds, PaginateNoCount avoids the COUNT(*) (expensive on PostgreSQL): it fetches pageSize+1 rows and infers HasNext. Total is -1 and TotalPages is 0 (unknown):

p, err := orm.FromDB[Post](db).OrderBy("id DESC").PaginateNoCount(page, 20)

Paginate/PaginateNoCount cap pageSize at 1000 (larger values are silently reduced) — important when page/pageSize come straight from a query string without their own validation.

Both still use OFFSET, which gets more expensive as the page number grows (the database still scans and discards the skipped rows). For very large tables or infinite scroll, use PaginateAfter — keyset (cursor) pagination, whose cost doesn't grow with the page position:

cursor := ctx.QueryInt64("cursor", 0) // 0 = first page

p, err := orm.FromDB[Post](db).
    Where("publicado = ?", true).
    PaginateAfter("id", cursor, false, 20) // col, after, desc, limit

// p.Items      → []Post for the current page
// p.NextCursor → pass this into PaginateAfter on the next call
// p.HasNext    → true if there's a next page

col needs to be unique and orderable (typically the PK or an autonow column); PaginateAfter adds col > ? (or col < ? with desc: true) and orders by col — don't combine it with your own OrderBy on the same query, since PaginateAfter replaces the ordering with col. Unlike Paginate, there's no Total/TotalPages/page numbers — just "is there a next page or not."

Relations: filter with Join, load with Prefetch

Declare the FK on the model with kyrux:"fk:table" (generates REFERENCES + index in the migration). To filter by the related table, use Join — the SELECT uses base_table.*, so the result is still []T with no column conflicts (qualify the Where columns):

// Posts from active users:
posts, _ := orm.FromDB[Post](db).
    Join("users", "users.id = posts.user_id").
    Where("users.is_active = ?", true).
    All()

To load related records, use Prefetch — the explicit equivalent of Django's prefetch_related (1 query + in-memory grouping, no N+1):

posts, _ := orm.FromDB[Post](db).All()
ids := make([]int64, len(posts))
for i, p := range posts { ids[i] = p.ID }

// 1 query: SELECT * FROM comentarios WHERE post_id IN (...)
porPost, _ := orm.Prefetch[Comentario](db, "post_id", ids,
    func(c *Comentario) int64 { return c.PostID })

for _, p := range posts {
    comentarios := porPost[p.ID] // []Comentario for the post
}

Update/Delete don't accept Join (non-portable syntax across databases) — use a subquery in Where: Where("user_id IN (SELECT id FROM users WHERE ...)").

In the template:

{{ range .page.Items }}
    <article>{{ .Titulo }}</article>
{{ end }}

{{ if .page.HasPrev }}
    <a href="?page={{ sub .page.Page 1 }}">← Previous</a>
{{ end }}
{{ if .page.HasNext }}
    <a href="?page={{ add .page.Page 1 }}">Next →</a>
{{ end }}

Multi-tenant with schema

// Tenant middleware sets the schema
db := fw.DB.Use().WithSchema("tenant_" + tenantID)

// All queries use the schema automatically
posts, _ := orm.FromDB[Post](db).Where("publicado = ?", true).All()
// → SELECT * FROM tenant_abc.posts WHERE publicado = ?

post := Post{Titulo: "New"}
orm.Create(db, &post)
// → INSERT INTO tenant_abc.posts (titulo, ...) VALUES (?)

Driver compatibility

The ORM detects the driver automatically. You always write ? — for PostgreSQL the placeholders are rewritten to $1, $2, ... internally.

Driver	Generated placeholder
`postgres`, `pgx`	`$1, $2, ...`
`mysql`, `sqlite`	`?`

Usage in models

Recommended pattern — functions in the models package that receive *database.DB:

// apps/blog/models/models.go
package models

import (
    "kyrux/core/database"
    "kyrux/core/orm"
)

type Post struct {
    ID        int64  `kyrux:"pk"`
    Titulo    string
    Publicado bool
}

func ListarPublicados(db *database.DB) ([]Post, error) {
    return orm.FromDB[Post](db).
        Where("publicado = ?", true).
        OrderBy("id DESC").
        All()
}

func BuscarPorID(db *database.DB, id int64) (*Post, error) {
    return orm.FromDB[Post](db).
        Where("id = ?", id).
        First()
}

func Criar(db *database.DB, post *Post) error {
    return orm.Create(db, post)
}

// apps/blog/views/views.go
func ListaView(ctx *router.Context) {
    posts, err := models.ListarPublicados(fw.DB.Use())
    if err != nil {
        ctx.Error(500)
        return
    }
    render.For("blog").Render(ctx, "lista.html", map[string]any{
        "posts": posts,
    })
}

ORM