# Bob Models & ORM (Layer 2) After running `bobgen` (see `code-generation.md`) you get a typed, database-first ORM. This file covers **using** those generated models. Hand-written models (without codegen) via `orm.NewTable`/ `NewView` are at the end. ## What gets generated (per table `jets`) ```go type Jet struct { // the row type ID int `db:"id,pk" json:"id"` Name string `db:"name"` Color null.Val[string] `db:"color"` // nullable col -> null.Val[T] // ... } type JetSetter struct { // for insert/update; every field optional ID omit.Val[int] `db:"id,pk"` Name omit.Val[string] `db:"name"` Color omitnull.Val[string] `db:"color"` // nullable col -> omitnull.Val[T] } type JetSlice []*Jet // use this instead of []*Jet var Jets = psql.NewTablex[*Jet, JetSlice, *JetSetter]("public", "jets", /* columns */) ``` `models.Jets` is the entrypoint object (the plural table var). It exposes `Query`, `Insert`, `Update`, `Delete`, `NameExpr()`, `Columns`, and the hooks. Alongside it Bob generates the helper namespaces `SelectWhere.Jets`, `SelectJoins.Jets`, `JetColumns`, `FindJet`, `JetExists`, and `JetErrors`. > Some doc pages show a `JetsTable` variable name; in v0.46.0 the generated var is the plural form > (`models.Jets`). Use that. ## Setters: omit.Val and omitnull.Val Setters express "which columns to write." Field types come from `github.com/aarondl/opt`: - `omit.Val[T]` — two states: **set** or **unset**. Unset fields are excluded from the SQL entirely. - `omitnull.Val[T]` — three states: **value**, **NULL**, or **unset**. Used for nullable columns. ```go import "github.com/aarondl/opt/omit" import "github.com/aarondl/opt/omitnull" s := &models.JetSetter{ Name: omit.From("Concorde"), // write this column Color: omitnull.FromPtr(ptr), // nil ptr -> unset; non-nil -> value // ID left zero -> unset -> not in the INSERT/UPDATE } ``` Useful constructors/methods: `omit.From(v)`, `omit.FromPtr(*v)`, `omit.FromCond(v, ok)`; `.Get() (T, bool)`, `.GetOr(fallback)`, `.GetOrZero()`, `.IsValue()`, `.IsUnset()`, `.Set(v)`, `.Unset()`. `omitnull` adds `.IsNull()` and `.Null()`. ## Querying `models.Jets.Query(mods...)` returns a query whose finishers run + scan: ```go jet, err := models.Jets.Query(mods...).One(ctx, db) // T, sql.ErrNoRows if none jets, err := models.Jets.Query(mods...).All(ctx, db) // JetSlice count, err := models.Jets.Query(mods...).Count(ctx, db) // int64 (rewrites to count(1)) exists, err := models.Jets.Query(mods...).Exists(ctx, db) // bool cursor, err := models.Jets.Query(mods...).Cursor(ctx, db) // scan.ICursor[T]; stream large sets // Each(ctx, db) returns a Go 1.23 range-over-func iterator. ``` Shorthands by primary key: ```go jet, err := models.FindJet(ctx, db, 10) // SELECT * ... WHERE id = 10 jet, err := models.FindJet(ctx, db, 10, "id", "cargo") // only those columns has, err := models.JetExists(ctx, db, 10) ``` ### Typed WHERE filters Generated per column; one mod namespace per query type (`SelectWhere`, `UpdateWhere`, `DeleteWhere`): ```go models.Jets.Query(models.SelectWhere.Jets.ID.EQ(100)) // type-checked value models.SelectWhere.Jets.Name.IsNull() models.SelectWhere.Jets.Age.GTE(21) ``` Every generated column exposes the full comparison set (type `WhereMod[Q,C]`): `EQ`, `NE`, `LT`, `LTE`, `GT`, `GTE`, `In(...vals)`, `NotIn(...vals)`, `Like(v)`, `ILike(v)`. **Nullable** columns additionally get `IsNull()` and `IsNotNull()`. Values are type-checked against the column's Go type. Combine with `psql.WhereOr` / `psql.WhereAnd` (nestable): ```go users, err := models.Users.Query( psql.WhereOr( models.SelectWhere.Users.Name.IsNull(), models.SelectWhere.Users.Email.IsNotNull(), psql.WhereAnd( models.SelectWhere.Users.Age.GT(21), models.SelectWhere.Users.Location.IsNotNull(), ), ), ).All(ctx, db) ``` For an aliased table: `models.SelectWhere.Users.AliasedAs("u").Name.IsNull()`. ### Typed JOIN helpers Generated from the table's relationships (`SelectJoins`/`InsertJoins`/`UpdateJoins`/`DeleteJoins`): ```go models.Jets.Query( models.SelectJoins.Jets.InnerJoin.Pilots, models.SelectJoins.Jets.InnerJoin.Airports, ).All(ctx, db) // AliasedAs works here too: SelectJoins.Jets.AliasedAs("j").InnerJoin.Airports.AliasedAs("a") ``` ### Column expressions `models.JetColumns.X` are expressions usable anywhere in a hand-built query (mixing Layer 1 + 2): ```go psql.Select( sm.Columns(models.JetColumns.Name, "count(1)"), sm.From(models.Jets.NameExpr()), sm.Where(models.JetColumns.ID.Between(50, 5000)), sm.OrderBy(models.JetColumns.PilotID), ) ``` ## CRUD ```go // INSERT (RETURNING added automatically -> finish with .One()/.All()) jet, err := models.Jets.Insert(&models.JetSetter{Name: omit.From("x")}).One(ctx, db) jets, err := models.Jets.Insert(s1, s2, s3).All(ctx, db) // bulk jets, err := models.Jets.Insert(bob.ToMods(setterSlice...)).All(ctx, db) // from a []*Setter // UPSERT (PSQL/SQLite) models.Jets.Insert(setter, im.OnConflict("id").DoUpdate(im.SetExcluded("name"))).One(ctx, db) // MySQL: im.OnDuplicateKeyUpdate(im.UpdateWithValues("name")) // UPDATE via the table + typed where jet, err := models.Jets.Update( models.UpdateWhere.Jets.ID.EQ(jetID), setter.UpdateMod(), ).One(ctx, db) // Instance methods on a fetched row / slice err := jet.Update(ctx, db, &models.JetSetter{Name: omit.From("new")}) // by PK err = jets.UpdateAll(ctx, db, models.JetSetter{AirportID: omit.From(100)}) _, err = jet.Delete(ctx, db) _, err = jets.DeleteAll(ctx, db) _, err = jet.Reload(ctx, db) // re-read all columns; jets.ReloadAll for slices ``` ## Error constants (unique-constraint matching) Bob generates `Errors` plus a generic `ErrUniqueConstraint`: ```go pilot, err := models.Pilots.Insert(setter).One(ctx, db) if errors.Is(models.PilotErrors.ErrUniqueFirstNameAndLastName, err) { /* handle */ } if models.ErrUniqueConstraint.Is(err) { /* any unique violation */ } ``` > With `errors.Is`, order matters: the **constant goes first**, the DB error second. Flipping them > silently fails to match. ## Relationships Related rows live in `model.R`; relationship counts (when loaded) live in `model.C`. ```go jet.R.Pilot // *Pilot (to-one) pilot.R.Jets // JetSlice (to-many) ``` **Two eager-loading strategies:** - **`Preload`** — one `LEFT JOIN` in the same SELECT. To-one relationships only. - **`ThenLoad`** — a separate follow-up query. Works for any relationship type, including to-many. ```go // Preload (to-one, single round-trip) jet, err := models.Jets( models.Preload.Jet.Pilot( psql.OnlyColumns("id"), psql.PreloadAs("pilot"), psql.SelectThenLoad.Pilot.Licences(), // nest a further load ), ).One(ctx, db) // ThenLoad (any type; can filter the loaded side) pilots, err := models.Pilots( models.ThenLoad.Pilots.Jets(models.SelectWhere.Jet.AirportID.EQ(100)), ).All(ctx, db) ``` There are query-type variants: `SelectThenLoad`, `InsertThenLoad`, `UpdateThenLoad` (and the `*ThenLoadCount` / `PreloadCount` forms that populate `model.C` without loading rows). **On an existing instance:** ```go err := jet.LoadPilot(ctx, db) // fills jet.R.Pilot err = pilot.LoadJets(ctx, db) // fills pilot.R.Jets err = pilot.LoadCountJets(ctx, db) // fills *pilot.C.Jets ``` **Mutating relationships:** ```go jet.InsertPilot(ctx, db, &models.PilotSetter{...}) // create + link (to-one) pilot.InsertJets(ctx, db, &models.JetSetter{...}) // create + link (to-many) jet.AttachPilot(ctx, db, existingPilot) // link an existing row ``` **Was it loaded?** Each `R` carries a `Loaded` sub-struct (rename via `relation_loaded_name` config): ```go if jet.R.Loaded.Pilot && jet.R.Pilot == nil { // definitively loaded and there is no pilot (null FK) } ``` `Lazy` relationship querying (no preload) is also generated: `jet.Pilots(ctx, db, mods...)` returns a query with `One/All/Count/Exists/...`. ## Hooks Models expose typed hook sets you register onto: ```go // signature: func(ctx, exec, T) (context.Context, error); returned ctx threads forward. models.Jets.BeforeInsertHooks.AppendHooks(func(ctx context.Context, exec bob.Executor, s *models.JetSetter) (context.Context, error) { return ctx, nil }) ``` Hook points on a Table: `BeforeInsertHooks` (receives the **setter**), `AfterInsertHooks`, `Before/AfterUpdateHooks`, `Before/AfterDeleteHooks`, `Before/AfterMergeHooks` (all receive the **slice**), plus `AfterSelectHooks` on the View. There are also query-level hooks (`InsertQueryHooks`, etc.). > **The method is `AppendHooks`, not `Add`.** The hooks doc page is wrong about this. Skip hooks for a single call by threading a marked context: ```go users, err := models.Jets.Query().All(bob.SkipHooks(ctx), db) // bob.SkipModelHooks(ctx) and bob.SkipQueryHooks(ctx) skip only one kind. ``` --- ## Hand-written models (no codegen) You can build the same `View`/`Table` objects by hand. A **View** is read-only; a **Table** embeds a View and adds writes. Construct via the dialect package: ```go import ( "github.com/stephenafamo/bob" "github.com/stephenafamo/bob/dialect/psql" "github.com/stephenafamo/bob/expr" ) type User struct { ID int `db:"id,pk"` Name string `db:"name"` Email string `db:"email"` } type UserSetter struct { // must satisfy orm.Setter ID omit.Val[int] `db:"id,pk"` Name omit.Val[string] `db:"name"` Email omit.Val[string] `db:"email"` } // psql/sqlite: (schema, table, columns). mysql: (table, columns, uniques...) — no schema arg. var userView = psql.NewView[*User, bob.Expression]("public", "users", expr.ColsForStruct[User]("users")) var userTable = psql.NewTable[User, *UserSetter, bob.Expression]("public", "users", expr.ColsForStruct[User]("users")) ``` - `expr.ColsForStruct[T](alias)` reflects `db` tags into the column list. - `NewViewx` / `NewTablex` let you choose the slice type (e.g. a named `[]*User`). - Query/CRUD on these behaves exactly like the generated models: `userTable.Query(...).All(ctx, db)`, `userTable.Insert(&UserSetter{...}).One(ctx, db)`, `user.Update(ctx, db, setter)`, etc. - `Columns` field supports `.Only("a")`, `.Except("b")`, `.WithParent("schema","t")`, `.WithPrefix("t.")` for projection control. - `bob.UseSchema(ctx, "tenant")` overrides the schema at runtime for views built with an empty schema.