Writing data

The query builder writes rows two ways: a typed Repo[T] for the common single-row CRUD keyed by primary key, and Update[T] / Delete[T] builders for writing many rows by condition. This page uses the Product model from the hub.

For exhaustive API detail, see the reference at pkg.go.dev/liteorm.org/query.

query.NewRepo[T](sess) is a typed repository wrapping the common write paths and primary-key lookups. It requires a primary key on T for the keyed operations.

repo := query.NewRepo[Product](db)

// Insert one; the generated primary key is read back into v in place.
p := Product{Name: "Desk Lamp", Category: "home", Price: 24, Stock: 18, Active: true}
err := repo.Insert(ctx, &p)
fmt.Println(p.ID) // populated

// Lookup by primary key (→ liteorm.ErrNoRows when absent).
got, err := repo.Get(ctx, p.ID)

// Find by predicates (same predicates as the builder).
cheapBooks, err := repo.Find(ctx,
  query.Col[string]("category").Eq("books"),
  query.Col[float64]("price").Lt(40),
)

// Update non-key columns of the row identified by its primary key.
p.Price = 19.99
err = repo.Update(ctx, &p)

// Delete by primary key.
err = repo.Delete(ctx, p.ID)

Insert reads the generated key back via RETURNING where the dialect has it, else LastInsertId. Get and Delete also take one value per key column for a composite key, in declaration order — repo.Get(ctx, tenantID, code).

InsertMany(ctx, vs) inserts a slice efficiently — using the backend’s native bulk path when available (Postgres CopyFrom), otherwise chunked multi-row VALUES. It does not read primary keys back, so use Insert per row when you need each generated id.

err := repo.InsertMany(ctx, []Product{
  {Name: "Laptop Pro", Category: "electronics", Price: 1899, Stock: 7, Active: true},
  {Name: "USB Cable", Category: "electronics", Price: 9.99, Stock: 230, Active: true},
})

Upsert(ctx, v, query.OnConflict("col")) inserts v or, on a conflict with the named columns, updates the row. By default every non-conflict column is overwritten; chain .DoUpdate(...) to overwrite only specific columns:

restock := Product{Name: "USB Cable", Category: "electronics", Price: 8.49, Stock: 500, Active: true}
err := repo.Upsert(ctx, &restock, query.OnConflict("name").DoUpdate("stock", "price"))

To ignore a conflicting row instead of updating it — the typed form of INSERT OR IGNORE, and the canonical SQL ON CONFLICT DO NOTHING — chain .DoNothing(). It is portable: a no-op ON DUPLICATE KEY UPDATE on MySQL, a MERGE with no matched arm on SQL Server. A skipped insert returns no generated key.

err := repo.Upsert(ctx, &seen, query.OnConflict("url").DoNothing()) // first writer wins; dups skipped

The Repo writes one row by primary key; query.Update[T] and query.Delete[T] are the builders for writing many rows by condition. Set/SetExpr assign columns, Where/Filter scope the statement, and Exec returns the number of rows affected. A WHERE-less write is refused (add Where("1 = 1") to affect every row on purpose).

deactivated, err := query.Update[Product](db).
  Set("active", false).
  Filter(query.Col[int64]("stock").Eq(0)).
  Exec(ctx) // rows affected

discontinued, err := query.Delete[Product](db).
  Filter(query.Col[string]("category").Eq("legacy")).
  Exec(ctx)

For the common atomic read-modify-write, Inc/Dec are typed sugar over SetExpr — the increment happens in the database, with the column quoted for the dialect:

_, err := query.Update[Product](db).
  Inc("view_count", 1). // view_count = view_count + 1, atomically
  Filter(query.Col[int64]("id").Eq(id)).
  Exec(ctx)

For a correlated DELETE, scope it with a subquery predicate (InQuery / Exists) — portable across every dialect. See joins & subqueries.

From(source) adds a correlated UPDATE … FROM — set columns from another table (or a VALUES list), which is also how you set many rows to different values in one statement. SetExpr assigns a raw expression rather than a bound value. Gated by FeatUpdateFrom (Postgres / SQLite / SQL Server; MySQL, which uses UPDATE … JOIN, raises a clear build error):

// age += adjustments.delta, joined per row
_, err := query.Update[Person](db).
  SetExpr("age", "age + adj.delta").
  From("adjustments AS adj").
  Where("people.id = adj.person_id").
  Exec(ctx)

Returning(ctx) runs the write and scans the changed rows back as []T — via RETURNING (Postgres/SQLite) or OUTPUT (SQL Server); it errors on MySQL, which has neither:

restocked, err := query.Update[Product](db).
  SetExpr("stock", "stock + ?", 100). // a raw expression, not just a value
  Filter(query.Col[string]("category").Eq("electronics")).
  Returning(ctx) // []Product, the updated rows

Delete[T].Returning(ctx) returns the deleted rows the same way.

• Reading data — the predicates that scope these writes.
• Joins & subqueries — correlated UPDATE/DELETE via subquery predicates.
• Transactions — grouping writes atomically.
• Errors — the normalized constraint/conflict errors these writes return.
• The orm front-end — the declarative repository with hooks and associations.