Relations & transactions
The two places where correct-looking code is wrong: a query set that grows with the number of rows, and a write that was never in the transaction it appears to be in.
Preload, Joins, and the N+1 between them
Preload fetches the related rows with an IN (…); Joins selects the parents by something on the child. Picking the wrong one is not a syntax error — it is a hundred queries per request. Both the wrong and the right shape are here, side by side.
go
package main
import (
"fmt"
core "gitlab.finema.co/finema/idin-core/v2"
"gitlab.finema.co/finema/idin-core/v2/repository"
)
// --- Example 3: relations, and the N+1 that hides in them -------------------
//
// Preload runs one extra query per relation with an IN (…) — that is what makes
// it the cure for N+1 rather than a cause of it. Joins puts the relation in the
// same query, which is what you need when the *parent* rows are chosen by
// something on the child. Picking the wrong one is not a syntax error; it is a
// hundred queries per request that nobody sees until the table grows.
func relationsTour(ctx core.IContext) core.IError {
if err := seedRelations(ctx); err != nil {
return err
}
// ❌ 1 + N queries: one for the users, one more for every user found. It is
// invisible in review and obvious in the log — turn SQL logging on
// (APP_DB_LOG_LEVEL=info) and watch a burst of near-identical statements.
users, err := repository.New[User](ctx).Where("email LIKE ?", "rel-%").FindAll()
if err != nil {
return err
}
for i := range users {
p, err := repository.New[Profile](ctx).FindOne("user_id = ?", users[i].ID)
if err == nil {
users[i].Profile = p
}
}
// ✅ two queries, whatever N is.
users, err = repository.New[User](ctx).
Where("email LIKE ?", "rel-%").
Preload("Profile").
// Preload what the response renders, not what the struct happens to
// contain: each nesting level is another query and potentially a lot of
// rows. A condition on the preload keeps it to the ones that matter.
Preload("Orders", "status = ?", OrderPaid).
Preload("Orders.Items").
FindAll()
if err != nil {
return err
}
ctx.Log().Info("preloaded", "users", len(users))
if err := relationsFilterByChild(ctx); err != nil {
return err
}
return relationsAggregate(ctx)
}
// relationsFilterByChild is the case Preload cannot serve: the parents are
// selected by a column on the child, so the child has to be in the same query.
func relationsFilterByChild(ctx core.IContext) core.IError {
// ❌ pulls every order of every user across the wire in order to throw most
// of them away, and gets slower every month.
all, err := repository.New[User](ctx).Preload("Orders").FindAll()
if err != nil {
return err
}
slow := 0
for _, u := range all {
for _, o := range u.Orders {
if o.TotalSatang > 100_000 {
slow++
break
}
}
}
// ✅ the database decides and sends only the answer. Distinct matters: a
// join to a has-many multiplies the parent, so a user with three large
// orders would otherwise appear three times.
fast, err := repository.New[User](ctx).
Joins("JOIN orders ON orders.user_id = users.id").
Where("orders.total_satang > ?", 100_000).
Distinct("users.*").
FindAll()
if err != nil {
return err
}
ctx.Log().Info("big spenders", "in_go", slow, "in_sql", len(fast))
// For a belongs-to or has-one you also want to display, InnerJoins by
// relation name populates the struct in the same query — no second round
// trip. A has-many still wants Preload; a join would multiply the rows.
orders, err := repository.New[Order](ctx).
InnerJoins("User").
Where(`"User".status = ?`, UserActive).
FindAll()
if err != nil {
return err
}
ctx.Log().Info("orders of active users", "count", len(orders))
return nil
}
// relationsAggregate counts children without loading any of them.
func relationsAggregate(ctx core.IContext) core.IError {
type userRow struct {
ID string
Name string
Orders int64
Spent int64
}
var rows []userRow
// LEFT JOIN keeps users with no paid order; coalesce turns their NULL sum
// into 0. Both are easy to leave out and both change the answer.
if err := repository.New[User](ctx).
Select(`users.id, users.name,
count(orders.id) as orders,
coalesce(sum(orders.total_satang), 0) as spent`).
Joins("LEFT JOIN orders ON orders.user_id = users.id AND orders.status = ?", OrderPaid).
Group("users.id, users.name").
Scan(&rows); err != nil {
return err
}
ctx.Log().Info("spend report", "rows", len(rows))
return nil
}
func seedRelations(ctx core.IContext) core.IError {
users := repository.New[User](ctx)
for i := 0; i < 3; i++ {
u := User{
Email: fmt.Sprintf("rel-%d@example.com", i),
Name: fmt.Sprintf("Rel %d", i),
Status: UserActive,
Profile: &Profile{City: "BKK"},
Orders: []Order{
{Status: OrderPaid, TotalSatang: int64(50_000 * (i + 1)),
Items: []OrderItem{{SKU: "sku-a", Qty: 1, PriceSatang: 50_000}}},
{Status: OrderPending, TotalSatang: 10_000},
},
}
// Create on a parent with populated children inserts the children too,
// in one transaction. Convenient — and easy to trigger by accident:
// loading with Preload and then calling Save rewrites the children as
// well, unless you Omit(clause.Associations).
if err := users.Create(&u); err != nil {
return err
}
}
return nil
}Transactions, locking, and what to keep outside
A repository built with New inside the closure is not in the transaction, and nothing in the logs distinguishes it from one that is. Also: the atomic UPDATE … WHERE that needs no lock at all, consistent lock ordering as the cure for deadlocks, and why the publish happens after the commit.
go
package main
import (
core "gitlab.finema.co/finema/idin-core/v2"
"gitlab.finema.co/finema/idin-core/v2/errmsgs"
"gitlab.finema.co/finema/idin-core/v2/repository"
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
// --- Example 4: transactions ------------------------------------------------
//
// Transaction commits when fn returns nil and rolls back on an error or a
// panic. The rule that causes every bug in this area: the repository the
// transaction was started from is *not* in the transaction. A write made
// through it commits immediately and survives the rollback, with no error, no
// warning and nothing in the log to tell the two lines apart.
//
// The only reliable defence is habit — bind every repository at the top of the
// closure and never call repository.New below that point.
func transactionsTour(ctx core.IContext) core.IError {
users := repository.New[User](ctx)
payer := User{Email: "[email protected]", Name: "Payer", Status: UserActive, CreditsSatang: 100_000}
payee := User{Email: "[email protected]", Name: "Payee", Status: UserActive}
if err := users.Create(&payer); err != nil {
return err
}
if err := users.Create(&payee); err != nil {
return err
}
order, err := placeOrder(ctx, payer.ID, 30_000)
if err != nil {
return err
}
// Publishing belongs *after* the commit: a subscriber that sees this can
// rely on the row existing. Inside the transaction it could receive an event
// for work that then rolled back. (When the message must not be lost even if
// the process dies here, write an outbox row inside the transaction instead
// and let a job deliver it — v2/docs/database-transactions.md.)
if err := ctx.PubSub().Publish("order.created", order.ID); err != nil {
ctx.Log().Warn("publish failed, the order is still committed", "err", err)
}
if err := lockForUpdate(ctx, payer.ID, 1_000); err != nil {
return err
}
return transferCredits(ctx, payer.ID, payee.ID, 5_000)
}
// placeOrder debits the buyer and writes the order as one outcome.
func placeOrder(ctx core.IContext, userID string, totalSatang int64) (*Order, core.IError) {
order := Order{UserID: userID, Status: OrderPending, TotalSatang: totalSatang}
err := repository.New[User](ctx).Transaction(func(tx *gorm.DB) error {
// Everything transactional is bound here, before any business logic, so
// there is nothing non-transactional left in scope below this line.
txUsers := repository.NewWithDB[User](ctx, tx)
txOrders := repository.NewWithDB[Order](ctx, tx)
// The cheapest correct debit: the precondition lives in the WHERE and
// RowsAffected is the answer. No read, no lock, no lost update — and it
// is correct even without the surrounding transaction.
res := txUsers.Where("id = ? AND credits_satang >= ?", userID, totalSatang).
DB().Update("credits_satang", gorm.Expr("credits_satang - ?", totalSatang))
if res.Error != nil {
return res.Error
}
if res.RowsAffected == 0 {
return errmsgs.BadRequest // not enough credit: roll the order back
}
return txOrders.Create(&order)
})
if err != nil {
// Worth knowing: an error returned from the closure comes back wrapped
// as DATABASE_ERROR, so GetCode() no longer says BAD_REQUEST. The cause
// is preserved, so errors.Is still matches — match on that, or decide
// the business case before the transaction starts.
return nil, err
}
return &order, nil
}
// lockForUpdate is the shape to reach for when the decision cannot be expressed
// as a WHERE — several reads that must agree, or a value computed in Go.
//
// SELECT … FOR UPDATE makes the second transaction wait, and the lock lives and
// dies with the transaction, so this only works inside one. It is also the more
// expensive answer: try atomic arithmetic (above) or core.WithLock first.
func lockForUpdate(ctx core.IContext, userID string, spend int64) core.IError {
return repository.New[User](ctx).Transaction(func(tx *gorm.DB) error {
txUsers := repository.NewWithDB[User](ctx, tx)
// sqlite has no FOR UPDATE — it serialises writers instead — so the
// clause is only added on engines that have one. Worth knowing for
// tests: a locking test passes on sqlite whether or not it locks.
if supportsRowLocks(tx) {
txUsers = txUsers.Clauses(clause.Locking{Strength: "UPDATE"})
}
u, err := txUsers.FindOne("id = ?", userID)
if err != nil {
return err
}
if u.CreditsSatang < spend {
return errmsgs.BadRequest
}
return txUsers.Where("id = ?", userID).Update("credits_satang", u.CreditsSatang-spend)
})
}
func supportsRowLocks(tx *gorm.DB) bool {
switch tx.Dialector.Name() {
case "postgres", "mysql", "sqlserver", "oracle":
return true
default:
return false
}
}
// transferCredits shows the deadlock cure. Two transactions that lock the same
// two rows in opposite orders deadlock and the database kills one of them;
// retrying is not the fix, locking in a consistent order is.
func transferCredits(ctx core.IContext, fromID, toID string, amount int64) core.IError {
first, second := fromID, toID
if first > second {
first, second = second, first
}
return repository.New[User](ctx).Transaction(func(tx *gorm.DB) error {
txUsers := repository.NewWithDB[User](ctx, tx)
for _, id := range []string{first, second} {
if _, err := txUsers.FindOne("id = ?", id); err != nil {
return err
}
}
res := txUsers.Where("id = ? AND credits_satang >= ?", fromID, amount).
DB().Update("credits_satang", gorm.Expr("credits_satang - ?", amount))
if res.Error != nil {
return res.Error
}
if res.RowsAffected == 0 {
return errmsgs.BadRequest
}
// Nothing that is not a database statement belongs between here and the
// commit: an HTTP call, an S3 upload or a slow computation turns its own
// latency into lock-hold time, and a transaction holds a pooled
// connection for its whole life.
return txUsers.Where("id = ?", toID).
Update("credits_satang", gorm.Expr("credits_satang + ?", amount))
})
}