orm

README

orm

kanna-orm writes the database code behind your repository interfaces. You declare the interface, and a scope type for each table it reads; kanna-orm writes the implementation, and a method on the scope type for each way a query can be narrowed, ordered, paged, or made to load what a row refers to. The SQL stays in the generated code as literals, and a column or a type that does not match the schema stops generation, or compilation, rather than a request.

Part of the kanna family of code generators. Requires Go 1.26, and PostgreSQL through pgx or database/sql, or MySQL 8.4 or later through database/sql.

Install

go get -tool github.com/go-kanna/orm/cmd/kanna-orm

The generated code imports github.com/go-kanna/orm, which the line above already requires: a scope type holds an orm.Of, and a row that is not there is orm.ErrNotFound. It depends on nothing outside the standard library; the driver is yours.

Use

kanna-orm reads the schema from a database, not from your migrations. Migrate one, and describe it into a snapshot at the root of your module:

go tool kanna-orm describe -database-url "$DATABASE_URL" ./...

Commit kanna-orm.snapshot.json along with the migration: the change to the schema shows in review, and generation reads the snapshot alone, so it needs no database.

Name the package of your models, and kanna-orm declares a scope type for each of them that a table is named after:

package scopes

//go:generate go tool kanna-orm

//kanna:scopes ../model

and the repository the rest of your code depends on:

package repository

//go:generate go tool kanna-orm -destination ../postgres

//kanna:repository
type OrderRepository interface {
	Get(ctx context.Context, id model.OrderID, s ...scopes.Order) (model.Order, error)
	List(ctx context.Context, s ...scopes.Order) ([]model.Order, error)
	Insert(ctx context.Context, o model.Order) (model.Order, error)
	Delete(ctx context.Context, id model.OrderID) error
}

With the model as your domain has it:

package model

type Order struct {
	ID         OrderID
	CustomerID CustomerID
	Status     OrderStatus
	PlacedAt   time.Time
	Memo       *string `orm:"note"`
	Lines      []OrderLine
}

go generate ./... writes scopes_gen.go next to the scope types:

// Code generated by kanna-orm. DO NOT EDIT.

// Orders returns a scope over all the rows of orders.
func Orders() Order { return Order{} }

// StatusNe narrows to the rows whose status is not v.
func (s Order) StatusNe(v model.OrderStatus) Order {
	return Order{s.q.Where(`status <> `, v)}
}

// PlacedAtDesc orders the rows by placed_at, latest first.
func (s Order) PlacedAtDesc() Order {
	return Order{s.q.OrderBy(`placed_at`, true)}
}

// WithLines loads Lines, from order_lines by order_id.
func (s Order) WithLines(sub ...OrderLine) Order {
	// ...
}

// ...a method for each column and relation, and Limit, Offset, Or, Not, After, Before, Cursor, and AfterCursor.

and the implementation into ../postgres:

// Code generated by kanna-orm. DO NOT EDIT.

// OrderRepository implements repository.OrderRepository on PostgreSQL through pgx.
type OrderRepository struct{ db DBTX }

func NewOrderRepository(db DBTX) OrderRepository { return OrderRepository{db: db} }

func (r OrderRepository) Insert(ctx context.Context, o model.Order) (model.Order, error) {
	const q = `INSERT INTO orders (id, customer_id, status, placed_at, note)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, customer_id, status, placed_at, note`
	var row model.Order
	err := r.db.QueryRow(ctx, q, o.ID, o.CustomerID, o.Status, o.PlacedAt, o.Memo).
		Scan(&row.ID, &row.CustomerID, &row.Status, &row.PlacedAt, &row.Memo)
	if err != nil {
		return model.Order{}, fmt.Errorf("insert into orders: %w", err)
	}
	return row, nil
}

// ...

Callers chain the scope:

orders, err := repo.List(ctx, scopes.Orders().
	StatusNe(model.OrderCanceled).
	PlacedAtGte(lastMonth).
	WithLines().
	PlacedAtDesc().
	Limit(20))

A declaration that does not match the schema stops generation, rather than the first request that meets it:

$ go generate ./...
./scopes.go:6:1: error: Order: the snapshot has no table order
  did you mean orders?
../model/order.go:12:1: error: Order.Memo: orders.note may be NULL, which a string cannot hold
  declare it as *string
./order_repository.go:12:2: error: ListByCustomer: orders has no column for the parameter custmerID
  did you mean customerID (customer_id)?
./order_repository.go:14:2: error: GetByStatus: status is not a unique key of orders, so Get cannot return one row
  use List, or add a unique index on orders (status)

The repository interface names scopes.Order, which does not exist until //kanna:scopes generates it, so the code does not compile before the first run. That does not stop the run: kanna-orm knows the type from that directive, and go generate can reach the two packages in either order. Code may also call a method that is generated before it is: errors saying only that a method the run generates is missing do not stop it.

The snapshot

describe connects to the database and records, for each table, its columns, with their types, whether they may be NULL, and whether the database assigns them itself, as an identity, serial, or AUTO_INCREMENT column does; its primary key; its unique keys; and its foreign keys. It also prepares each hand-written query and scope in the packages it is given, and in the directories they name that hold no package yet, as the destination of the repositories does before their first generation, and records the types of their parameters and of their result columns. Nothing it runs writes.

The database is the one -database-url names, or else $DATABASE_URL:

• postgres://user:password@host:5432/database is PostgreSQL, through pgx. The tables are those of the current schema, which ?search_path= chooses.
• mysql://user:password@host:3306/database is MySQL, through go-sql-driver/mysql, which takes the options after ?. The tables are those of the database the URL names.

The types are what the database itself answers, so kanna-orm has no SQL parser of its own to disagree with it. MySQL does not describe the result of a statement it prepares, so there, describe reads the columns of the statement wrapped as SELECT t.* FROM (...) AS t LIMIT 0, which returns no rows.

The snapshot records what generation needs, and no more, so describe -check tells that the code was generated from the schema there is, not that two schemas are the same:

Recorded	Not recorded
a column's type, with the length or precision it declares: varchar(100), numeric(10,2)	what a default is, only that there is one
whether a column may be NULL, has a default, or is assigned by the database	CHECK constraints
primary keys, unique keys, and foreign keys	the conditions of partial indexes
the types of each hand-written query's parameters and result columns	triggers, collations, and comments

A migration changing only what is not recorded leaves the snapshot as it was. To review those, or to compare two databases, use the tools of your migrations.

The snapshot is JSON with one column to a line, in a fixed order, so that a migration shows as the lines it changes:

     "orders": {
       "columns": [
         {"name": "id", "type": "uuid", "not_null": true},
         {"name": "customer_id", "type": "uuid", "not_null": true},
         {"name": "status", "type": "text", "not_null": true},
-        {"name": "placed_at", "type": "timestamptz", "not_null": true}
+        {"name": "placed_at", "type": "timestamptz", "not_null": true},
+        {"name": "note", "type": "text"}
       ],

A module has one snapshot, kanna-orm.snapshot.json at its root, which generation finds by walking up from the package. A module using two databases describes each into a file of its own, and names it with -snapshot on both commands.

In CI, describe a freshly migrated database with -check, which fails when the snapshot is out of date:

$ go tool kanna-orm describe -check ./...
./kanna-orm.snapshot.json: error: out of date with the database
  orders.note (text) is in the database but not in the snapshot
  run go tool kanna-orm describe to update it

Scope types

A scope type is a struct whose only field is an unexported orm.Of[M], M being the model the table's rows are read into, so that nothing but its methods narrows it. Each table kanna-orm reads or writes has one, and each model belongs to one table: the scope type is the only place where the two are tied together.

//kanna:scopes, followed by packages of the module, by their import paths or by paths relative to the file, as ../model, declares one, in scopes_gen.go, for each struct of those packages that a table of the snapshot is named after: the struct's name in snake case with its last word made plural, as order_lines for OrderLine, categories for Category, and people for Person, or else as it is. A struct no table is named after, such as a value type, gets none, and neither does a table no struct is named after, such as one between two others. Methods of your own are written on the type as on any other, in another file of the package.

A table named otherwise, or a scope type of a name of your own, is declared by hand, and wins over the one //kanna:scopes would imply of its model or its table:

//kanna:scope table=people
type Person struct{ q orm.Of[model.Person] }

A model that a relation reaches but no scope type names stops generation:

../model/order_line.go:9:1: error: OrderLine.Product: no scope type is declared for model.Product
  declare one where products are queried: //kanna:scope table=products

For each scope type, kanna-orm writes:

• the type, when //kanna:scopes implies it;
• a function returning the empty scope, named after the table: Orders() for orders. When the name is the type's own, or is taken otherwise, declare the type by hand and choose another with func=, as in //kanna:scope table=person func=People;
• a method for each column, each relation, and each hand-written scope;
• Limit, Offset, Or, Not, After, Before, Cursor, and AfterCursor.

Every method returns a new scope and leaves its receiver as it was, so a scope built halfway can be shared: base.Limit(10) and base.Limit(20) do not see each other's limit. The zero value, Order{}, is the empty scope too.

Columns and fields

A field holds the column named as the field is, in snake case: CustomerID holds customer_id, and PlacedAt holds placed_at. The conversion is mechanical, so a field named otherwise says so with a tag:

Tag	Meaning
(none)	the column named as the field is, in snake case
orm:"note"	the column note
orm:"note,nullzero"	a nullable string column: read NULL as the zero string and write the zero string as NULL
orm:",default"	omit this column from generated INSERT statements and use its database default
orm:"-"	not a column: kanna-orm neither reads nor writes the field
orm:"fk=order_id"	on a relation, the foreign key to follow when more than one could
orm:"fk=order_lines.order_id"	the same, naming its table too, for when the column alone does not tell

A field with no column, a column that may be NULL held by a field that cannot be nil, and, on a table something inserts into, a column the database requires a value for and the model has no field for, all stop generation. A column the model has no field for is otherwise left alone.

Use nullzero only when a nullable string column represents absence as the field's zero value. It works with named string types, including enums implementing sql.Scanner: generated code uses sql.Null[T], so NULL bypasses the enum's non-null scanner, while non-null values still go through it. It applies to table operations and hand-written query models. Omit the column name, as orm:",nullzero", when the field's snake-case name already matches it.

The PostgreSQL and MySQL examples use this for the optional order memo:

Memo string `orm:"note,nullzero"`

Their readMemos step selects orders with MemoIsNull() and verifies that empty memos were stored as SQL NULL and read back as empty strings, while a non-empty memo remains unchanged. note is explicit here because the field is named Memo; a field named Note would need only orm:",nullzero".

A field's type must be one the driver reads the column's type into: an integer type for bigint, a string for text, time.Time for timestamptz or datetime, []byte for bytea or blob, uuid.UUID of Go 1.27 or a string for uuid, and a slice of those for an array of PostgreSQL, which MySQL has none of. A type with Scan and Value methods converts itself, and fits any column, and a type of the database kanna-orm does not know, such as an enum or json, is taken at the field's word.

A field read from a column must also hold every value the column can: a row holding a value beyond the field fails to scan, and a float32 read from a float8 or double loses precision without a word. So an integer field is at least as wide as the column, int64 for bigint and int32 for integer, signed for a signed column, and unsigned or a bit wider for an unsigned one: uint64 for bigint unsigned, int64 for int unsigned. int and uint are taken to be 64 bits wide, as on amd64. Writing needs no such width, as the database rejects a value beyond its column rather than store another, so a field wider than its column, as an int for an integer, is fine, and so is a parameter of any width. The range of numeric is not recorded, so any number reads it.

Methods for columns

The methods are named after the field, so the column note, held by Memo, gives MemoContains. Each column gets the methods its type allows:

Column	Methods, for a field Status
any	StatusEq, StatusNe, StatusIn, StatusNotIn, StatusAsc, StatusDesc
of an ordered type: number, time, string	also StatusLt, StatusLte, StatusGt, StatusGte, StatusBetween
of a string	also StatusLike; StatusHasPrefix and StatusContains, which escape % and _; StatusILike on PostgreSQL
that may be NULL	also StatusIsNull, StatusIsNotNull

A method takes the field's type: StatusEq takes a model.OrderStatus, and a value type goes to the database through its Value method, as database/sql would send it. The methods matching a pattern take a string, which no value of the type need be, and a column held in a slice other than []byte, an array, has no In or NotIn. The comparisons of a column that may be NULL take the value, not a pointer, so that = NULL, which matches nothing, cannot be written; StatusIsNull says what it would have meant.

Your own scopes

A scope you name is a method you write on the scope type, out of the generated ones. It chains as they do:

// Open narrows to the orders that are not canceled.
func (s Order) Open() Order {
	return s.StatusNe(model.OrderCanceled)
}

// Recent orders by placed_at, latest first, and keeps the first n.
func (s Order) Recent(n int) Order {
	return s.PlacedAtDesc().Limit(n)
}

A search narrows a scope one condition at a time:

s := scopes.Orders().Open().Recent(in.PageSize)
if in.CustomerID != nil {
	s = s.CustomerIDEq(*in.CustomerID)
}
if in.Keyword != "" {
	s = s.MemoContains(in.Keyword)
}
orders, err := repo.List(ctx, s)

A method of yours named as one kanna-orm would generate stops generation instead of being overwritten.

Or and Not

Or matches the rows that any of its scopes match, each scope's own conditions holding together:

// WHERE ... AND ((status = $1) OR (placed_at < $2))
s := scopes.Orders().Or(
	scopes.Orders().StatusEq(model.OrderPending),
	scopes.Orders().PlacedAtLt(cutoff),
)

Not matches the rows its scope does not. A scope given to Or or Not holds conditions only: one with an order, a limit, or a relation to load makes the query return an error. When every alternative is a value of one column, StatusIn says the same more plainly.

Relations

A field holding another model, a pointer to one, or a slice of them is a relation. kanna-orm tells its kind from the snapshot's foreign keys, so nothing more is written for it:

Field	Foreign key	Kind
Lines []OrderLine	order_lines.order_id to orders	has many
Customer *Customer	orders.customer_id to customers	belongs to
Profile *Profile	profiles.user_id to users, a unique key	has one
Tags []Tag	through a table with foreign keys to both, such as user_tags	many to many

When more than one foreign key could serve, generation stops and asks for a tag:

../model/order.go:16:1: error: Order.Lines: order_lines has two foreign keys to orders (order_id, replaced_order_id)
  order_lines.order_id to orders
  order_lines.replaced_order_id to orders
  tag the field with the one to follow: orm:"fk=order_id"

A table with foreign keys to both counts as much as a foreign key of the related table: when refunds refers to orders and to order_lines, Lines could be the lines of an order or those refunded, and asks for a tag too. Where the column alone does not tell, as order_id does not here, the tag names its table as well: orm:"fk=order_lines.order_id".

A relation is loaded only when the scope asks for it with its With method, which takes scopes of the related rows: their conditions, their order, and relations of their own to load.

o, err := repo.Get(ctx, id, scopes.Orders().
	WithCustomer().
	WithLines(scopes.OrderLines().
		QuantityGt(0).
		SKUAsc().
		WithProduct()))

On PostgreSQL, each relation costs one query, however many rows there are: the call above runs four, one each for orders, customers, order lines, and products. A relation through a table between costs two, one for the pairs of keys in that table and one for the related rows. The related rows are read by the keys of the rows already read, each key once, with = ANY($1) on PostgreSQL and IN (?, ...) on MySQL, where the keys go a thousand at a time, each thousand in a query of its own: the rows related to one row come in one query, in the order of the scope given for them.

• A slice that was loaded is never nil: it is empty when nothing is related. A nil slice was not loaded.
• A pointer is nil both when it was not loaded and when nothing is related. Telling the two apart would take a type of kanna-orm's in your model.
• A limit or an offset on the related rows would count all of them, not those of each row, so a scope given to a With method that holds one makes the query return an error. To read the first few of each, write the query by hand.
• Relations are read, never written: Insert and Update write the row's own columns, and a row read back holds them alone.
• A relation by a foreign key of more than one column is read by its columns as a row: (a, b) IN (SELECT * FROM unnest($1, $2)) on PostgreSQL, and (a, b) IN ((?, ?), ...) on MySQL.

Paging

Limit and Offset page as SQL does. For pages that stay put while rows come and go, give After the last row of a page, and it continues from there in the scope's order:

s := scopes.Orders().Open().PlacedAtDesc().Limit(20)

page, err := repo.List(ctx, s)
// ...
next, err := repo.List(ctx, s.After(page[len(page)-1]))
// WHERE status <> $1 AND (placed_at, id) < ($2, $3)
// ORDER BY placed_at DESC, id DESC LIMIT 20

• When the order does not end in the primary key, After and Before add it, so that rows equal in the order still come in one. Without it, a page that ends among equal rows repeats or skips some of them, without a word.
• When every column of the order goes the same way, the condition compares rows, as above. When they do not, and on MySQL, it is spelled out: a < $1 OR (a = $1 AND b < $2).
• Before goes the other way, to the page before the one a row is the first of. The repository reads that page from its end and hands its rows back in the scope's order.
• An order by a column that may be NULL makes a query continuing from a row return an error, and so Cursor, since the databases put NULL in different places.

To hand the position to a client, Cursor encodes it as a string, and AfterCursor decodes it into the scope:

s, err := scopes.Orders().Open().PlacedAtDesc().AfterCursor(req.PageToken)
if err != nil {
	return nil, err
}
page, err := h.orders.List(ctx, s.Limit(20))
// ...
res.NextPageToken, err = s.Cursor(page[len(page)-1])

A cursor holds the order it was made in and the row's values in that order, as JSON; an empty one is the start, and one made in another order is an error. Each value is decoded through its type, so a value type checks its rule. A cursor a client has altered changes where the page starts, and nothing else.

Repositories

An interface marked //kanna:repository declares what the code above the database may do with it. Its package runs kanna-orm -destination <dir>, which writes the implementation into repository_gen.go there: a struct named after the interface when generated into a separate package, its constructor, and the DBTX it runs on, named as sqlc names it.

Same-package implementations

Use -destination . to keep the interface and its implementation in the same package. The interface must be exported; Category generates an unexported category, a newCategory(db DBTX) *category constructor, and pointer receiver methods. It also generates NewCategory(db DBTX) Category unless you already declare NewCategory yourself. Keep scope types in a separate package; their methods go into scopes_gen.go.

Declare Bind(db DBTX) Category to generate transaction binding. The argument may instead be a concrete database or transaction type implementing DBTX, or a type exposing a parameterless DBTX() method that returns one, such as Bind(tx tx.Tx) Category. Bind returns a new implementation using that connection and leaves the original unchanged. The input must match the selected driver's statement methods, and the result must be the repository's own interface. An existing hand-written Bind is preserved.

For dependency injection, -constructor-db chooses a concrete argument type for generated public constructors:

//go:generate go tool kanna-orm -destination . -constructor-db "*example.com/app/db.Reader"

This generates NewCategory(db *db.Reader) Category, while Bind can still use the transaction's DBTX. The flag takes an exported type's full import path and name, optionally prefixed with *, loads that package, and checks that the type implements the selected driver's DBTX. It applies only to same-package generation. You need no constructor or binding wrapper of your own.

Write application-specific methods directly on the generated type. A method without a recognized verb or SQL query, other than Bind is left to your implementation. When you implement a method with a recognized verb or SQL query, kanna-orm generates its database operation under an unexported name: a hand-written Upsert can call r.upsert before translating its error. This helper behavior applies only to same-package generation. The helper has the same signature and follows the interface method's //kanna:conflict and //kanna:lock directives. Its name must not already be declared. Queries belong in the destination's .sql files, including when the destination also declares the interface.

//go:generate go tool kanna-orm -destination .

//kanna:repository table=categories
type Category interface {
	Bind(db DBTX) Category
	Upsert(ctx context.Context, c model.Category) error
}

func (r *category) Upsert(ctx context.Context, c model.Category) error {
	return translateError(r.upsert(ctx, c))
}

Error translation

The PostgreSQL and MySQL examples translate a missing order into model.ErrOrderNotFound. Their repository declares //kanna:repository errors=translateOrderError, and the implementation package defines the function in store/errors.go.

func translateOrderError(op orderRepositoryOperation, err error) error {
    if op == orderRepositoryOperationGet && errors.Is(err, orm.ErrNotFound) {
        return model.ErrOrderNotFound
    }
    return err
}

The generator emits a private orderRepositoryOperation string type and a typed constant for each generated error-returning method, such as orderRepositoryOperationGet and orderRepositoryOperationInsert. Bind is excluded. The examples' main programs verify that Get returns model.ErrOrderNotFound for order 99, while repositories without translation continue returning orm.ErrNotFound.

The function's presence and exact func(orderRepositoryOperation, error) error signature are checked during generation, including the first run before the operation type exists. Generated operation names must not collide with handwritten declarations. Translation runs once for a non-nil error, after adding the generated method's error context, including custom SQL and relation-loading errors. A generated private helper passes its original interface method's operation. Handwritten method bodies are not translated automatically. Without errors=, no operation type or translation code is generated. Return an unrecognized error unchanged to preserve its error chain.

Separate-package implementations

With a separate destination such as -destination ../postgres, kanna-orm generates an exported struct with the interface's name, a value constructor, and value receiver methods. Methods written by hand on that struct are skipped without generating helpers.

// DBTX is what the repositories run on: *pgxpool.Pool, *pgx.Conn, or pgx.Tx.
type DBTX interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

func NewOrderRepository(db DBTX) OrderRepository

A transaction is begun and committed as it would be without kanna-orm: give the constructor the pgx.Tx, or the *sql.Tx.

Methods

What a method does is told by the verb its name starts with; what follows the verb is yours to choose. Its parameters tell the rest: a parameter of a model type is a row to write, the scopes say how to read, and any other parameter is the column of its name, which the rows must equal.

Verb	Declared as	Does
Get	Get(ctx, id model.OrderID, s ...scopes.Order) (model.Order, error)	reads the row whose key the parameters give, or returns orm.ErrNotFound
Find	Find(ctx, id model.OrderID, s ...scopes.Order) (*model.Order, error)	reads an optional row by a unique key; returns nil, nil when absent
First	First(ctx, s ...scopes.Order) (model.Order, error)	reads the first row the scopes match, or returns orm.ErrNotFound
List	List(ctx, s ...scopes.Order) ([]model.Order, error)	reads every row the scopes match
Count	Count(ctx, s ...scopes.Order) (int64, error)	counts the rows the scopes match
Exists	Exists(ctx, s ...scopes.Order) (bool, error)	reports whether any row matches
Insert	Insert(ctx, o model.Order) (model.Order, error), or with error alone	writes a row, or many
Update	Update(ctx, o model.Order) error	writes every column but the key, or returns orm.ErrNotFound when no row has the key
Upsert	Upsert(ctx, o model.Order) error	inserts, or updates the row with the same key, or another unique key
Delete	Delete(ctx, id model.OrderID) error, or Delete(ctx, s ...scopes.Order) (int64, error)	deletes the row with the key, or the rows the scopes match

Get requires the row to exist and returns orm.ErrNotFound when it does not. Find returns a pointer and treats absence as nil, nil; database errors still return nil, err. Both require explicit key parameters covering a primary or unique key. Additional scopes can filter the row and load relations. First selects a row from a potentially larger set instead. Hand-written SQL may implement a Find... returning a pointer to a result struct with the same absence contract.

Use //kanna:pluck customer_id above a repository method such as CustomerIDs(ctx context.Context, s ...scopes.Order) ([]int64, error) to read one column as a typed slice. The method name is yours to choose. Conditions, ordering, limits, offsets, and cursors work as for List; relations are not loaded. Duplicate values are preserved and no matching rows returns an empty slice. Generation checks the column and result type. Nullable columns require nullable elements unless the model field uses nullzero and the element has that field’s type. A pluck directive cannot be combined with hand-written SQL, conflict, or lock.

//kanna:pluck name
Names(ctx context.Context, s ...scopes.Product) ([]string, error)

names, err := products.Names(ctx, scopes.Products().PriceLt(2000).PriceAsc())

Both database examples use this method to read the affordable product names without loading whole products.

// customerID is customer_id: the orders of one customer.
ListByCustomer(ctx context.Context, customerID model.CustomerID, s ...scopes.Order) ([]model.Order, error)

// number is a unique key of orders, so Get reads one row by it.
GetByNumber(ctx context.Context, number model.OrderNumber) (model.Order, error)

// The key of order_lines is (order_id, sku), so Get takes both.
GetLine(ctx context.Context, orderID model.OrderID, sku model.SKU) (model.OrderLine, error)

• The table is the one whose scope type holds the method's model, so one interface may read and write every table of an aggregate.
• Several scopes hold together, as if chained.
• Get stops generation unless its parameters give the primary key or a unique key, every column of it.
• Count and Exists use their conditions only; a scope with an order, a limit, or a relation to load makes them return an error rather than ignore it.
• A Delete given scopes but no condition returns an error rather than delete every row.
• A Delete returning error alone returns orm.ErrNotFound when it deletes no row, as Update does; one returning (int64, error) returns the number of rows it deleted, none among them.
• A hand-written query of the method's name takes the place of its verb; a method with neither stops generation.
• A method you write yourself on the generated struct stays in your file. For same-package implementations, a recognized verb or named query also generates a private helper. Separate-package implementations skip the method.

A method tells its model by its result, its row, or its scopes, or else by a key of a type declared with the models, as model.OrderID is. One that takes nothing but keys of another type, such as uuid.UUID or int64, tells none, and stops generation unless the interface names the table with table=:

//kanna:repository table=todos
type TodoRepository interface {
	Get(ctx context.Context, id uuid.UUID) (model.Todo, error) // todos, by its result
	Delete(ctx context.Context, id uuid.UUID) error            // todos, by table=
}

table= is the table of the methods that tell none, and of no other: a method that tells its own model reads or writes its table, so an interface of an aggregate still reaches each of its tables.

orm.ErrNotFound comes wrapped with what was not found, so test for it with errors.Is, and turn it into your application's error where you call the repository.

Keyed by a uuid

On Go 1.27 or later, a uuid column is held by the uuid.UUID of the standard library, which pgx and database/sql read and write as it is; before, by a string, or by a type of your own with Scan and Value methods. A table keyed by one is declared as any other, but for table=:

type Todo struct {
	ID    uuid.UUID
	Title string
}

//kanna:repository table=todos
type TodoRepository interface {
	Get(ctx context.Context, id uuid.UUID) (model.Todo, error)
	List(ctx context.Context, s ...scopes.Todo) ([]model.Todo, error)
	Upsert(ctx context.Context, t model.Todo) error
	Delete(ctx context.Context, id uuid.UUID) error
}

• uuid.UUID is declared with no model, so a method that takes nothing but one tells no table: Delete takes its table from table=, while Get tells its own by its result.
• A uuid has no order to compare by, so its column gets Eq, Ne, In, NotIn, Asc, and Desc, but neither Lt nor Between. A page still continues from a row by it, and a cursor holds it as its text.
• A column defaulting to uuidv7() is not automatically treated as assigned. Without orm:",default", an insert writes the model's ID, the zero UUID included. Either give each row its ID in Go with uuid.NewV7(), or tag the field with orm:",default" to use the database's default.
• MySQL has no uuid type, and kanna-orm does not take uuid.UUID for a binary(16) or a char(36): hold one in a []byte, a string, or a type of your own with Scan and Value methods.

go-kanna/showcase is a to-do service whose to-dos are keyed by uuid.UUID.

Scalar relations

A relation can load one column of related rows directly into a slice, including a slice of enums. Name the related table and the column to read; no intermediate row model or scope type is needed:

type Device struct {
    ID uuid.UUID
    Topics []Topic `orm:"relation=device_topics,column=topic"`
}

The snapshot must have a foreign key from device_topics to devices. Its referenced columns must be held by the parent model. When multiple foreign keys connect the tables, select one with orm:"relation=device_topics,column=topic,fk=device_id". Generation checks the column's type and nullability against the slice element type.

Composite foreign keys work the same way: a relation from order_tags.(tenant_id, order_id) to orders.(tenant_id, id) matches and groups by the whole pair. The parent model must hold both referenced columns. Related values are loaded in batches using tuple conditions, so rows with the same ID in different tenants remain separate. A parent with a nil pointer in any key component has no related values loaded.

The generated device scope has WithTopics(). A normal generated read loads this relation only when requested, in batches, ordered by the selected column. Each enum value goes through its scanner. An explicitly loaded relation with no rows is an empty slice; an unrequested relation remains nil. Parent inserts and updates leave the relation alone, and custom SQL model mappings exclude it. Write application-specific relation updates separately.

device, err := devices.GetByToken(ctx, platform, token, scopes.Devices().WithTopics())

Writing

A row is written as the model holds it. Database-assigned columns, such as identity, serial and AUTO_INCREMENT, and fields explicitly tagged default are left out of INSERT statements. Otherwise a zero value is written as a zero value: a false for a column defaulting to true stays false. Set created_at and updated_at in your domain, or explicitly use their database defaults.

An Insert returning the model reads the row back as the database stored it; one returning error alone writes and is done. MySQL reads it back by LAST_INSERT_ID() when the database assigns its primary key, and by its primary key otherwise. MySQL counts the rows an Update changes, not those it finds, so when it changes none, kanna-orm asks whether the row is there before returning orm.ErrNotFound.

Database defaults

Tag a field with orm:",default" to leave its column out of generated INSERT statements. A field with a different column name can use orm:"created_at,default". The snapshot must record a database default or database assignment for that column; otherwise generation fails. default and nullzero can be combined.

type Product struct {
    SKU      SKU
    Name     string
    Price    int64
    Currency string `orm:",default"`
}

The PostgreSQL and MySQL examples define products.currency with DEFAULT 'JPY'. Even when the model supplies Currency: "USD", INSERT omits it and the stored value is JPY. The example reads it back and checks this. The option is explicit and unconditional: an untagged zero value is still written as a zero value. It affects generated INSERT statements, including bulk inserts and upserts, but not handwritten SQL.

On PostgreSQL, a method returning the model reads database-generated values with RETURNING. For example, ID uuid.UUID tagged orm:",default" can use a DEFAULT uuidv7() primary key without assigning the ID in Go. A single row with every column omitted uses DEFAULT VALUES. Bulk writes still require at least one input column.

UPDATE keeps writing the model's values for tagged non-key fields. Upsert conflict updates follow the usual keep= and coalesce= rules: an omitted field's incoming value is its database default, not the value supplied in Go. Use keep= when an existing value should always remain unchanged.

MySQL limitation: this option does not add a RETURNING substitute or a new unique-key discovery mechanism. Existing readback through AUTO_INCREMENT, a supplied primary key, or an upsert's explicit conflict key continues to work. If default omits a key needed by that existing readback, generation fails for a model-returning method. An error-only method needs no readback and can still omit the column. MySQL's existing restriction on returning models from bulk writes also remains in place.

MySQL's new row alias also cannot reference an omitted column. An upsert that would update a default field is therefore rejected; list that column in keep= to preserve its stored value. The examples use //kanna:conflict sku keep=currency for the product catalog. Ordinary UPDATE statements can still write Currency.

Ignoring an insert conflict

Use //kanna:insert to generate an insert with a domain-specific method name, such as Use. The directive takes no arguments and cannot be combined with a hand-written query or //kanna:pluck.

On PostgreSQL, add do=nothing to //kanna:conflict on an Insert. The columns must name a primary or unique key. Only conflicts on that key are ignored; other constraint errors are returned.

//kanna:insert
//kanna:conflict token_hash do=nothing
Use(ctx context.Context, token model.UsedIDToken) (int64, error)

The count is the number of rows inserted: one for a new token, zero for an existing one. An Insert returning only error succeeds when the conflict is ignored. One returning a model uses RETURNING and returns orm.ErrNotFound when the conflict inserts no row. Bulk inserts can return the count or the slice of newly inserted rows; existing rows are omitted.

do=nothing cannot be combined with keep= or coalesce=, or used on an Upsert. MySQL rejects this directive at generation time: INSERT IGNORE would also suppress unrelated errors and cannot restrict conflict handling to the declared key. An ordinary Insert may also return (int64, error) for its inserted row count.

Upserting

Upsert meets an existing row by its primary key. To meet it by another unique key, name its columns with //kanna:conflict, and with keep= the columns to leave as they were:

//kanna:repository
type UserRepository interface {
	// UpsertByEmail meets the user of a tenant by their email address.
	//kanna:conflict tenant_id,email keep=created_at
	UpsertByEmail(ctx context.Context, u model.User) (model.User, error)
}

-- PostgreSQL
INSERT INTO users (tenant_id, email, name, created_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (tenant_id, email) DO UPDATE SET name = EXCLUDED.name
RETURNING id, tenant_id, email, name, created_at

-- MySQL, which then reads back by LAST_INSERT_ID() the row inserted or updated
INSERT INTO users (tenant_id, email, name, created_at)
VALUES (?, ?, ?, ?) AS new
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), name = new.name

The columns must be the primary key or a unique key of the table, exactly, and none the database assigns, as an insert never gives those to meet a row by. The row met gets every column but those of the key, the primary key, and those keep= names; with none left, the key is set to itself, so that the row is still read back. MySQL updates on a conflict over any unique key, so a table with unique keys besides the one named gets a warning:

./user_repository.go:14:2: warning: UpsertByEmail: on MySQL, a conflict on (tenant_id, login) also updates the row
  users has unique keys other than (tenant_id, email)

Use coalesce= on an upsert to keep the stored value only when the incoming SQL value is NULL:

//kanna:conflict email keep=created_at coalesce=phone
UpsertByEmail(ctx context.Context, c model.Customer) (model.Customer, error)

The examples' CustomerRepository uses this for Customer.Phone: a non-nil phone replaces the old one, while nil keeps it. keep=created_at always preserves when the customer joined. The two options can appear in either order. Only SQL NULL triggers COALESCE; an empty string or numeric zero does not. A string field's nullzero option explicitly encodes its empty string as NULL. Columns in coalesce= must be columns the upsert actually updates; keys, kept columns, and unknown columns are rejected during generation.

-- PostgreSQL
phone = COALESCE(EXCLUDED.phone, customers.phone)

-- MySQL
phone = COALESCE(new.phone, customers.phone)

Writing many rows

An Insert or an Upsert given a slice writes every row in it:

InsertLines(ctx context.Context, lines []model.OrderLine) error

-- PostgreSQL: one statement, however many rows
INSERT INTO order_lines (order_id, sku, quantity)
SELECT * FROM unnest($1::uuid[], $2::text[], $3::int[])

• On PostgreSQL, a result of ([]model.OrderLine, error) reads the rows back with RETURNING.
• On MySQL, the rows go in VALUES (?, ?, ?), (?, ?, ?), ..., split into several statements when they need more placeholders than one can take. Give the constructor a transaction to have them stand or fall together.
• MySQL has no RETURNING, so there a result other than error alone stops generation.
• PostgreSQL rejects an upsert in which two rows have the same key.
• unnest flattens an array of arrays, so on PostgreSQL a table with an array column stops generation; write its rows one by one.

Locking rows

//kanna:lock has a Get, a Find, a First, or a List lock the rows it reads until the transaction ends, so that what was read still holds when it is written back:

//kanna:repository
type OrderRepository interface {
	//kanna:lock update
	GetForUpdate(ctx context.Context, id model.OrderID) (model.Order, error)

	// FirstToShip passes over the orders other workers hold.
	//kanna:lock update skip-locked
	FirstToShip(ctx context.Context, s ...scopes.Order) (model.Order, error)
}

tx, err := pool.Begin(ctx)
if err != nil {
	return err
}
defer tx.Rollback(ctx)

orders := store.NewOrderRepository(tx)
o, err := orders.GetForUpdate(ctx, id)
if err != nil {
	return err
}
if o.Status != model.OrderOpen {
	return ErrNotOpen
}
o.Status = model.OrderCanceled
if err := orders.Update(ctx, o); err != nil {
	return err
}
return tx.Commit(ctx)

Strength	Clause	PostgreSQL	MySQL
update	FOR UPDATE	yes	yes
no-key-update	FOR NO KEY UPDATE	yes	no
share	FOR SHARE	yes	yes
key-share	FOR KEY SHARE	yes	no

A read waits for the rows other transactions hold. nowait after the strength has it return the database's error at once instead, and skip-locked has it pass those rows over.

• The lock belongs to the method, not to its scopes: they narrow, order, and page the rows as ever, and no caller can leave the lock out or change it.
• A lock lasts as long as its transaction, so a method with one returns an error on anything else. It tells by whether what it runs on has a Commit, so a DBTX of your own wrapping a transaction needs one too.
• A lock not given, by nowait, a timeout, or a deadlock, comes back as the database's error, wrapped, never as orm.ErrNotFound. Retrying is yours to do.
• With skip-locked, orm.ErrNotFound from a First says that no free row matches, not that no row does. A Get or Find would say a held row is not there, so skip-locked on one stops generation; use nowait.
• The relations a scope loads are read without a lock.
• Only rows that exist are sure to be locked, so do not count on a lock to keep a row from being inserted.
• PostgreSQL also locks the rows an Offset skips. InnoDB locks the rows it scans, not those it returns, so a read with no index for its condition locks the whole table.
• //kanna:lock anywhere but right above such a method stops generation, rather than leave the method unlocked. So does one on a method a hand-written query or your own code alone implements: write the lock there. A same-package method delegating to a generated helper keeps its lock on that helper.
• A Get, a Find, a First, or a List named as if it locked, such as GetForUpdate, gets a warning without //kanna:lock.

Reading a projection

A Get..., Find..., First..., or List... method may return a different named struct while taking the original model's scopes. The result type selects the columns; it needs no scope of its own:

type ProductSummary struct {
    Name  string
    Price int64
}

// Inside a //kanna:repository interface:
ListSummaries(ctx context.Context, s ...scopes.Product) ([]ProductSummary, error)

ListSummaries(ctx, scopes.Products().SKUAsc().PriceLt(2000)) selects only name, price from products, while filtering and ordering by the original scope. Fields match the original model's field names or mapped column names; column tags and nullzero handling are inherited. A projection field can use an orm column tag to choose a source column under another Go name. Field types must match the corresponding model field exactly. Missing fields, duplicate source columns, empty projections, and relations in the result are generation errors. Projection methods are read-only; filters, ordering, paging, locks, and the normal absence contracts remain available. A scope requesting relation loading is rejected rather than silently ignored. Aggregates, expressions, and joins can still use hand-written SQL returning a DTO.

Hand-written SQL

What the methods cannot say goes into a .sql file. A query becomes the repository method of its name, and is written where that method is implemented, in the package -destination names. With -destination ., queries sit next to the repository interface. A scope becomes a method of the scope type it names, and is written next to it. A .sql file with neither a -- query: nor a -- scope: line, such as a migration, is none of kanna-orm's.

repository/repository.go   //kanna:repository interfaces, MonthCounts among their methods
postgres/queries.sql       -- query: MonthCounts
scopes/order_scopes.sql    -- scope: Order.PlacedInMonth(at time.Time)

For -destination ., the layout is:

repository/repository.go   //kanna:repository interfaces; //go:generate go tool kanna-orm -destination .
repository/queries.sql     -- query: MonthCounts
repository/repository_gen.go      generated private implementations and DBTX
scopes/order_scopes.sql    -- scope: Order.PlacedInMonth(at time.Time)

-- query: MonthCounts
SELECT date_trunc('month', placed_at) AS month, count(*) AS count
FROM orders
WHERE placed_at >= :since
GROUP BY 1
ORDER BY 1;

MonthCounts(ctx context.Context, since time.Time) ([]MonthCount, error)

type MonthCount struct {
	Month time.Time
	Count int64
}

-- PlacedInMonth narrows to the orders placed in the month of at.
-- scope: Order.PlacedInMonth(at time.Time)
date_trunc('month', placed_at) = date_trunc('month', :at)

The generated code holds each as a literal, its parameters numbered for the database:

func (r OrderRepository) MonthCounts(ctx context.Context, since time.Time) ([]MonthCount, error) {
	const q = `SELECT date_trunc('month', placed_at) AS month, count(*) AS count
FROM orders
WHERE placed_at >= $1
GROUP BY 1
ORDER BY 1`
	// ...
}

• A parameter is : followed by a name, as :since is. A : after another :, a letter, a digit, _, or ] starts none, so a cast such as ::int and a slice such as arr[1:n] are left alone, as is anything in a string, a quoted name, a comment, or a dollar-quoted body. describe prepares what results, so a misreading shows there.
• A query's parameters take their types from the method. A struct argument can provide parameters through its exported fields, by column name (including tags) or lower-camel field name. A name used by SQL must match exactly one argument or struct field; collisions stop generation, including those between an explicit parameter and a field. Use distinct parameter and field names, including orm tags. A field remains addressable by its lower-camel name even when it has a tag. This lets a model be written without a separate parameter DTO. The nullzero option also applies to these field arguments.
• Result columns go directly into model fields by column name and tag. A one-column result can instead be a scalar or enum, and []T can collect those values directly. T returns one row or orm.ErrNotFound; []T returns every row. With no result columns, (int64, error) reports affected rows; with one bigint column, it scans that value. An error-only method returns no columns. Inputs and results are checked against the snapshot.
• Each result column needs a name of its own, since the fields take them by name; MySQL cannot even describe a query whose columns share a name. Give them aliases, as in SELECT o.id, c.id AS customer_id.
• A result column that may be NULL needs a field that can be nil, or a string field tagged nullzero. Other fields that cannot be nil stop generation where the database says its column may be NULL; where the database cannot tell, the field's type is taken at its word. MySQL tells for every column. PostgreSQL tells only for a column read straight from a table, and as the table declares it, even through an outer join that leaves it NULL: hold such a column, as c.email from LEFT JOIN customers c, in a pointer. kanna-orm's own queries load relations with queries of their own, not joins, so this is a matter for hand-written queries only.
• A scope has no method declared for it, so its signature is written on its line, and the lines of comment right above that line are its method's doc. MySQL does not tell the types of a statement's parameters, and this way it need not.
• A statement runs from its line to the next statement's comment or line. The blank lines and the lines of comment it ends in are not part of it, nor is a comment at its very end, so that a scope can end in one.
• On MySQL, a parameter used twice is passed twice, once for each ?.

Drivers and dialects

The snapshot knows which database it came from, so only the driver is chosen, with -driver:

Database	-driver	The generated code uses
PostgreSQL	pgx, the default	pgx.Rows, $1, = ANY($1), RETURNING
PostgreSQL	database/sql, through pgx/stdlib	*sql.Rows, $1, = ANY($1), RETURNING
MySQL 8.4 or later	database/sql, the default, through go-sql-driver/mysql	*sql.Rows, ?, IN (?, ...), LAST_INSERT_ID()

The code differs for each, and none of it hides the database behind an abstraction: where PostgreSQL and MySQL differ, the difference is settled when the code is generated.

	PostgreSQL	MySQL
A name, quoted	"x"	`x`
Parameters	$1, used again where needed	?, one for each use
A list of keys	= ANY($1)	IN (?, ...), split when long
Reading a write back	RETURNING	LAST_INSERT_ID() and a SELECT by the key
Upsert	ON CONFLICT (...) on the key named	ON DUPLICATE KEY UPDATE on any unique key
Many rows	unnest, in one statement	VALUES, split when long
Paging by key	a row comparison, unless the order mixes directions	spelled out

A value type goes to and from the database through its Value and Scan methods; any other type, as the driver converts it. A column that may be NULL is held by a pointer. Through database/sql, an array of PostgreSQL is read into a slice on Go 1.27 or later, where pgx/stdlib scans each column itself.

With the rest of kanna

• kanna-value: a value type is read through Scan, which checks its rule, so a value the database holds but the rule rejects is an error naming its column. A cursor is decoded the same way.
• kanna-di: NewOrderRepository is an ordinary constructor, which a container can provide like any other.
• kanna-fixture: a fixture is a model like any other, so Insert puts it into a test's database.
• kanna-mapper: rows are read into your models directly, so there is no row type to map; map the models to the wire as before.

What kanna-orm does not do

• Write relations. Saving a row along with what it refers to is yours to sequence, in a transaction.
• Track changes, keep an identity map, or load lazily.
• Create or apply migrations. Use goose or any other tool, and describe the result.
• Join, group, or nest a query in a scope. Write those queries by hand.
• Set created_at and updated_at, or take a zero value for the column's default.
• Page by a column that may be NULL.
• COPY many rows through pgx.
• Support MariaDB or SQLite, or run one generated package on both databases.

Flags

kanna-orm [flags] [packages]
kanna-orm describe [flags] [packages]

Generating:

Flag	Meaning
-destination <dir>	write repository implementations into dir; required for repository interfaces; . keeps them in the interface package
-constructor-db <type>	argument type for generated public constructors in the interface package, as *example.com/app/db.Reader (default: DBTX)
-driver <name>	pgx or database/sql (default: pgx on PostgreSQL, database/sql on MySQL)
-snapshot <file>	the snapshot to read (default: kanna-orm.snapshot.json at the root of the module)
-tags <list>	comma-separated build tags
-check	verify the output is up to date instead of writing it, including that no output is left over
-v	list the files written, removed, or left unchanged
-version	print the version

Describing:

Flag	Meaning
-database-url <url>	the database to describe (default: $DATABASE_URL)
-snapshot <file>	the snapshot to write (default: kanna-orm.snapshot.json at the root of the module)
-tags <list>	comma-separated build tags
-check	verify the snapshot is up to date with the database instead of writing it
-v	say whether the snapshot was written or left unchanged

The packages default to the one in the current directory, so //go:generate go tool kanna-orm needs no argument. As with every kanna generator, KANNA_CHECK=1 go generate ./... checks instead of writing. A run that succeeds prints nothing but its warnings; one that finds neither a //kanna:scope type nor a //kanna:repository interface says so. A package whose scope declarations are all gone has its scopes_gen.go removed. A destination whose repository interfaces are all gone has its repository_gen.go removed, unless code in the packages the run loads still calls what it declares; a file of that name without the generated-code header is never touched. The go generate that removes one ends with open ...: no such file or directory, from go generate itself, which lists a package's files before it runs the generators; the file is gone as it should be (golang/go#36068).

Example

example is an order service with customers, products, orders, and their lines, shipments, and tags, generated once for PostgreSQL through pgx, in example/postgres, and once for MySQL through database/sql, in example/mysql: each a module of its own, as a table has one scope type in a module. It has scopes of its own, in Go and in SQL, keyset paging with cursors, each kind of relation, an upsert by a natural key, many rows written at once, queries written by hand, and rows locked in transactions. make example migrates the databases of make db-up, describes them, regenerates the example, and runs it against each; CI does the same, and fails if the output would change.

Development

make test       # unit tests
make db-up      # start the PostgreSQL and the MySQL of compose.yaml, on ports 15432 and 13306
make test-db    # unit tests, and the tests against those databases
make lint       # golangci-lint
make example    # migrate and describe the databases of db-up, regenerate the example, and run it
make bench      # run the benchmarks in a container next to the PostgreSQL of db-up

bench/postgres measures kanna-orm against pgx alone, sqlc, GORM, bun, and ent; its README has the results, and how they were taken.

Correlated selections and per-parent collections

Declare the source of a scalar value on the destination field. For example, an order can read the reviewer of its current revision from approval history:

type Order struct {
    ID OrderID
    Approver *string `orm:"relation=approvals"`
    Approvals []Approval
}

When column is omitted, the source column is inferred from the field name using the usual snake_case rule (Approver → approver). Specify column=approver when the field has a different name, such as CurrentApprover. A missing source column is a generation error.

Declare a scope for the related model, as for other relations. Generation adds typed <Scope>Columns descriptors, EqColumn/NeColumn and ordered column comparisons, and One, First, and Many selectors. A parent descriptor represents a SQL column, not a loaded Go value. Known database columns omitted from the model, such as an order's revision, are available in the descriptor too. Mapped columns retain their Go types; otherwise supported database types determine the descriptor type.

func (s Order) WithCurrentApprover() Order {
    return s.SelectApprover(func(parent OrderColumns) orm.Single[model.Approval] {
        return Approvals().RevisionEqColumn(parent.Revision).RevokedAtIsNull().One()
    })
}

func (s Order) WithLatestApprover() Order {
    return s.SelectApprover(func(_ OrderColumns) orm.Single[model.Approval] {
        return Approvals().RevokedAtIsNull().ApprovedAtDesc().IDDesc().First()
    })
}

func (s Order) WithRecentApprovals() Order {
    return s.PreloadApprovals(func(_ OrderColumns) orm.Many[model.Approval] {
        return Approvals().ApprovedAtDesc().IDDesc().Limit(3).Many()
    })
}

orders, err := repo.List(ctx, scopes.Orders().IDAsc().Limit(20).
    WithCurrentApprover().WithRecentApprovals())

The foreign key supplies the basic parent/child correlation. The callback adds conditions, including comparisons to parent columns. A column's Go type must match the comparison method; references captured from another callback or used outside their parent query fail when rendered. Values are still passed as bound parameters, and identifiers are quoted separately.

• One expects at most one matching row: zero produces SQL NULL and multiple matches produce a database cardinality error. Limits, offsets, and cursors cannot hide extra matches.
• First requires an order and chooses its first row; Offset can skip earlier rows. It supplies its own limit. Use a unique ordering, such as a timestamp followed by an ID, for deterministic results.
• One().Required() and First().Required() reject a missing or NULL value with orm.ErrRequiredRelation. Non-nullable destinations require Required. A nullable destination without a selection stays nil.
• Many reads collections in a batch, restricted to the requested parents. Limit and Offset apply independently to each parent, using ROW_NUMBER() when needed. An order is required for per-parent pagination. Empty collections are empty slices. Collection cursors are rejected; paginate parents with cursors and child collections with offsets.

Select<Field> adds a scalar subquery to the parent's SELECT, preserving its rows and pagination and reading the value in the same statement. Multiple selected fields have independent predicates and correctly numbered parameters. Preload<Field> reads model collections through a separate batched query, supports composite foreign keys and junction tables, and can load the selected children's own relations. A normal repeatable-read transaction provides snapshot consistency across ordinary parent and collection reads; locking and transaction isolation remain explicit caller choices. Existing With<Field> preloads retain their behavior.

Scalar selections are read-only: INSERT and UPDATE leave them out. A hand-written repository query can instead select the value under the field's column name, such as a.approver AS approver, into the same model. This keeps a route to query-specific optimization without changing the domain model. The PostgreSQL and MySQL examples check current/latest reviewers, per-order history limits and offsets, missing/required/multiple matches, and the custom SQL alternative.

Updating selected columns

Each scope generates a typed change set. CustomerChanges() returns an empty CustomerChangeSet; setters add only the columns to write. Setters use the model field's Go type, so a nullable Phone *string accepts nil to store SQL NULL:

changes := scopes.CustomerChanges().
    SetName("Ann Lee").
    SetPhone(nil)

Not calling a setter leaves that column unchanged. Zero values are written, rather than skipped. Setting the same column again replaces its earlier value. Change sets are immutable: a derived set leaves the original unchanged. Fields tagged nullzero retain their usual conversion of the empty string to SQL NULL. Primary keys, database-assigned columns, and relations have no setters. An empty change set is an error.

Declare the unique key separately from the assigned values and add ordinary condition scopes:

UpdatePhone(ctx context.Context, id model.CustomerID,
    c scopes.CustomerChangeSet, s ...scopes.Customer) (model.Customer, error)

updated, err := repo.UpdatePhone(ctx, customerID,
    scopes.CustomerChanges().SetPhone(new("050-1234-5678")),
    scopes.Customers().PhoneIsNull())

On PostgreSQL, a model result generates UPDATE ... RETURNING and reads the stored row in the same statement. A failed condition or missing key returns orm.ErrNotFound. Composite and alternate unique keys are supported. Returning only error updates the row without reading it back. Existing whole-model updates also accept additional condition scopes and may return the updated model. Returned models contain mapped table columns; related values and preloads are not loaded by an update.

For multiple rows, declare (int64, error) for the affected count or ([]model.Customer, error) for the returned rows. The generated method requires at least one WHERE condition and rejects ordering, pagination, and relation-loading scopes. Returned rows have no guaranteed order. MySQL supports change sets with error or an affected count; its count is the number of changed rows. A single-row update that matches unchanged values still succeeds. MySQL declarations returning updated models fail generation because MySQL has no UPDATE RETURNING; no separate readback is performed.

The PostgreSQL and MySQL examples update a customer's phone, clear it with SetPhone(nil), verify untouched fields, and reject a failed condition.

Selecting rows to delete

A scope-based Delete returns the deleted row count. No All or Batch suffix is required; conditions, ordering, and pagination describe the target rows:

Delete(ctx context.Context, s ...scopes.Order) (int64, error)

n, err := repo.Delete(ctx, scopes.Orders().
    Open().
    PlacedAtLt(before).
    PlacedAtAsc().
    IDAsc().
    Limit(batch))

Without pagination, the method deletes all matching rows. With Limit or Offset, it selects the target keys in a subquery and deletes those rows in one statement. Composite primary keys and non-null unique keys are supported, on PostgreSQL and MySQL. A table without either kind of key can still use an ordinary condition-based delete, but cannot use paged deletion.

A WHERE condition is required even when a limit is supplied. Paged deletion requires an explicit order; end it in a unique key, such as IDAsc(), to choose consistently among equal sort values. Existing keyset cursor scopes can select targets too. An order without a limit, offset, or cursor is rejected rather than ignored. Relation-loading scopes are rejected. Limit(0) and no matching rows both return a count of zero without orm.ErrNotFound.

The existing Delete(ctx, id) error form retains its missing-row error behavior. Method shape, rather than an All suffix, distinguishes it from the scoped count-returning form. Both database examples use the scoped Delete to remove the oldest matching order with a limit.

License

MIT

Documentation

Overview

Package orm is what the code kanna-orm generates runs on: the scope a query is narrowed, ordered, and paged by, and the SQL it becomes. Code written by hand needs ErrNotFound alone.

Index

• Variables
• func Batched[C, M any](q Of[M], field string) (Of[C], Parent, bool)
• func CheckSelection[M any](q Of[M], field string, missing bool) error
• func EscapeLike(s string) string
• func List[T any](vs []T) any
• type Batch
• type Changes
  • func (c Changes[M]) RenderUpdate(d Dialect, table string, allowed []string, q Of[M]) (string, []any, error)
  • func (c Changes[M]) Set(column string, value any) Changes[M]
• type Column
  • func NewColumn[T any](parent Parent, name string) Column[T]
• type Dialect
• type Many
• type Of
  • func CompareColumn[M, T any](q Of[M], name, op string, other Column[T]) Of[M]
  • func Join[M any](qs ...Of[M]) Of[M]
  • func Preload[M, C any](q Of[M], relation string, sub Of[C]) Of[M]
  • func PreloadMany[M, C any](q Of[M], field string, parent Parent, m Many[C]) Of[M]
  • func Preloaded[C, M any](q Of[M], relation string) (Of[C], bool)
  • func Select[M, C any](q Of[M], field, table, column string, parent Parent, keys, foreign []string, ...) Of[M]
  • func (q Of[M]) After(key []string, values map[string]any) Of[M]
  • func (q Of[M]) Before(key []string, values map[string]any) Of[M]
  • func (q Of[M]) ConditionsOnly(what string) error
  • func (q Of[M]) Cursor(key []string, values map[string]any) (string, error)
  • func (q Of[M]) DecodeCursor(token string, key []string, dest map[string]any) error
  • func (q Of[M]) First() Single[M]
  • func (q Of[M]) Limit(n int) Of[M]
  • func (q Of[M]) Many() Many[M]
  • func (q Of[M]) Not(s Of[M]) Of[M]
  • func (q Of[M]) Offset(n int) Of[M]
  • func (q Of[M]) One() Single[M]
  • func (q Of[M]) Or(alternatives ...Of[M]) Of[M]
  • func (q Of[M]) OrderBy(column string, desc bool) Of[M]
  • func (q Of[M]) ProjectionOnly(what string) error
  • func (q Of[M]) Render(d Dialect, first int) (Rendered, error)
  • func (q Of[M]) RenderBatch(d Dialect, b Batch, parent Parent, columns []string, keys [][]any) (string, []any, error)
  • func (q Of[M]) RenderDelete(d Dialect, table string, key []string) (string, []any, error)
  • func (q Of[M]) RenderSelect(d Dialect, table string, columns []string, fields []Projection) (string, []any, error)
  • func (q Of[M]) Where(parts ...any) Of[M]
• type Parent
  • func NewParent() Parent
• type Projection
• type Rendered
  • func (r Rendered) And() string
  • func (r Rendered) Args() []any
  • func (r Rendered) Limit() string
  • func (r Rendered) OrderBy() string
  • func (r Rendered) Reversed() bool
  • func (r Rendered) Where() string
• type Single
  • func (s Single[M]) Required() Single[M]

Variables

var ErrNotFound = errors.New("orm: not found")

ErrNotFound is what a repository returns, wrapped, for a row that is not there.

var ErrRequiredRelation = errors.New("orm: required relation has no value")

ErrRequiredRelation means a required selection returned no value.

var MayBeNull any = mayBeNull{}

MayBeNull stands, among the values of a row After and Cursor are given, for that of a column which may be NULL. The databases put NULL in different places in an order, so a page cannot continue by such a column.

Functions

func Batched

func Batched[C, M any](q Of[M], field string) (Of[C], Parent, bool)

func CheckSelection

func CheckSelection[M any](q Of[M], field string, missing bool) error

func EscapeLike

func EscapeLike(s string) string

EscapeLike escapes what a LIKE pattern gives a meaning, % and _, and the escape character itself, !, so that s matches only itself in a pattern that ends in ESCAPE '!'. ! rather than a backslash, since MySQL reads a backslash in a string differently by its NO_BACKSLASH_ESCAPES mode.

func List

func List[T any](vs []T) any

List is an argument standing for each of vs in turn, so that a condition can say IN (...) of it: it renders as a placeholder for each, and as NULL, which matches nothing, when there are none.

Types

type Batch

type Batch struct {
	ParentTable, Table                      string
	ParentKeys, ChildKeys                   []string
	Through                                 string
	ThroughParent, ThroughChild, TargetKeys []string
	Projections                             []Projection
}

Batch describes the join used for a generated collection preload.

type Changes

type Changes[M any] struct {
	// contains filtered or unexported fields
}

Changes holds the explicitly assigned columns of an update. Its zero value assigns nothing.

func (Changes[M]) RenderUpdate

func (c Changes[M]) RenderUpdate(d Dialect, table string, allowed []string, q Of[M]) (string, []any, error)

RenderUpdate renders assignments followed by conditions, rejecting an empty update or an unbounded write. allowed contains the mapped, writable columns supplied by the generated repository.

func (Changes[M]) Set

func (c Changes[M]) Set(column string, value any) Changes[M]

Set returns a new change set with column assigned to value, including nil and zero values. Generated setters supply the column name and enforce its Go type.

type Column

type Column[T any] struct {
	// contains filtered or unexported fields
}

Column is a typed reference to a column of a correlated parent.

func NewColumn

func NewColumn[T any](parent Parent, name string) Column[T]

type Dialect

type Dialect int

Dialect is the database a scope is rendered for.

const (
	Postgres Dialect = iota
	MySQL
)

type Many

type Many[M any] struct {
	// contains filtered or unexported fields
}

type Of

type Of[M any] struct {
	// contains filtered or unexported fields
}

Of is the scope of a query reading Ms: its conditions, the order of its rows, and how many of them it keeps. Every method returns a new scope and leaves its receiver as it was, so a scope built halfway can be shared.

func CompareColumn

func CompareColumn[M, T any](q Of[M], name, op string, other Column[T]) Of[M]

CompareColumn compares a scope column to a typed parent column; generated methods check the Go types.

func Join

func Join[M any](qs ...Of[M]) Of[M]

Join makes one scope of qs, as if each were chained onto the one before: their conditions and orders add up, and a later limit or offset replaces an earlier one.

func Preload

func Preload[M, C any](q Of[M], relation string, sub Of[C]) Of[M]

Preload has the rows of relation loaded along with those of q, read by sub. Preloading a relation again joins the two scopes as Join does. A limit or an offset in sub would count the related rows of every row together, not those of each, so it is an error.

func PreloadMany

func PreloadMany[M, C any](q Of[M], field string, parent Parent, m Many[C]) Of[M]

func Preloaded

func Preloaded[C, M any](q Of[M], relation string) (Of[C], bool)

Preloaded returns the scope the rows of relation are read by, and whether q loads them at all.

func Select

func Select[M, C any](q Of[M], field, table, column string, parent Parent, keys, foreign []string, s Single[C]) Of[M]

Select attaches a correlated scalar projection. It is used by generated Select<Field> methods.

func (Of[M]) After

func (q Of[M]) After(key []string, values map[string]any) Of[M]

After narrows to the rows that come after a row in the order of q, so that a page continues from the last row of the one before. values holds the row's value in each column of its table, and key names the columns of its primary key. When the order does not end in the primary key, After adds it, so that rows equal in the order still come in one.

func (Of[M]) Before

func (q Of[M]) Before(key []string, values map[string]any) Of[M]

Before is After the other way, for the page before the one a row is the first of. The query reads that page in the reverse order, and Rendered.Reversed says so, for its rows to be turned around.

func (Of[M]) ConditionsOnly

func (q Of[M]) ConditionsOnly(what string) error

ConditionsOnly reports why q, given to what, holds more than conditions: an order, a limit, an offset, a row to continue from, or a relation to load, which what would otherwise pass over. A repository's Count and Exists read the conditions alone.

func (Of[M]) Cursor

func (q Of[M]) Cursor(key []string, values map[string]any) (string, error)

Cursor encodes the position of a row in the order of q, for a client to hand back for the page after it. values and key are as After takes them.

func (Of[M]) DecodeCursor

func (q Of[M]) DecodeCursor(token string, key []string, dest map[string]any) error

DecodeCursor decodes the values a cursor holds into dest, which has a pointer for each column of the table, so that the row can be given to After. A cursor made in another order than that of q is an error.

func (Of[M]) First

func (q Of[M]) First() Single[M]

func (Of[M]) Limit

func (q Of[M]) Limit(n int) Of[M]

Limit keeps the first n rows.

func (Of[M]) Many

func (q Of[M]) Many() Many[M]

func (Of[M]) Not

func (q Of[M]) Not(s Of[M]) Of[M]

Not narrows to the rows s, which holds conditions alone, does not match.

func (Of[M]) Offset

func (q Of[M]) Offset(n int) Of[M]

Offset skips the first n rows.

func (Of[M]) One

func (q Of[M]) One() Single[M]

func (Of[M]) Or

func (q Of[M]) Or(alternatives ...Of[M]) Of[M]

Or narrows to the rows any of alternatives matches, each holding conditions alone. Without alternatives, it matches no row.

func (Of[M]) OrderBy

func (q Of[M]) OrderBy(column string, desc bool) Of[M]

OrderBy orders the rows by column, after any order given before.

func (Of[M]) ProjectionOnly

func (q Of[M]) ProjectionOnly(what string) error

ProjectionOnly rejects relation loading in a single-table projection. Filters, ordering, and pagination remain valid.

func (Of[M]) Render

func (q Of[M]) Render(d Dialect, first int) (Rendered, error)

Render writes q as SQL for d, numbering the placeholders of PostgreSQL from first, so that they can follow those of the query it goes into.

func (Of[M]) RenderBatch

func (q Of[M]) RenderBatch(d Dialect, b Batch, parent Parent, columns []string, keys [][]any) (string, []any, error)

RenderBatch returns parent keys followed by model columns, with pagination applied independently to each parent.

func (Of[M]) RenderDelete

func (q Of[M]) RenderDelete(d Dialect, table string, key []string) (string, []any, error)

RenderDelete selects the rows to delete using the scope's conditions and pagination. key names a non-null unique key, needed only when a page of rows is selected.

func (Of[M]) RenderSelect

func (q Of[M]) RenderSelect(d Dialect, table string, columns []string, fields []Projection) (string, []any, error)

RenderSelect renders a model read, including correlated scalar values before the root placeholders.

func (Of[M]) Where

func (q Of[M]) Where(parts ...any) Of[M]

Where narrows to the rows a condition holds for. The condition is SQL with its arguments in between, as in Where("status <> ", v) or Where("date_trunc('month', placed_at) = date_trunc('month', ", at, ")").

type Parent

type Parent struct {
	// contains filtered or unexported fields
}

Parent identifies one correlated scope. Its columns cannot escape into another query.

func NewParent

func NewParent() Parent

type Projection

type Projection struct {
	Field, Column string
	Optional      bool
}

Projection describes one generated result field. Optional says its Go type can hold NULL.

type Rendered

type Rendered struct {
	// contains filtered or unexported fields
}

Rendered is a scope as SQL: the pieces a query is put together from, and the arguments of their placeholders.

func (Rendered) And

func (r Rendered) And() string

And returns the conditions for a WHERE clause the query has of its own, or nothing when there are none.

func (Rendered) Args

func (r Rendered) Args() []any

Args returns the arguments of the placeholders, in order.

func (Rendered) Limit

func (r Rendered) Limit() string

Limit returns the LIMIT and OFFSET clauses, or nothing when the scope keeps every row.

func (Rendered) OrderBy

func (r Rendered) OrderBy() string

OrderBy returns the order as an ORDER BY clause, or nothing when there is none.

func (Rendered) Reversed

func (r Rendered) Reversed() bool

Reversed reports whether the rows come in the reverse of the scope's order, as they do for Before, for the caller to turn them around.

func (Rendered) Where

func (r Rendered) Where() string

Where returns the conditions as a WHERE clause, or nothing when there are none.

type Single

type Single[M any] struct {
	// contains filtered or unexported fields
}

Single selects zero or one related row. One and First differ in how extra matches are handled.

func (Single[M]) Required

func (s Single[M]) Required() Single[M]