Table

info

Table models have all the capabilities of view models. This page will only focus on the additional capabilities.

In addition to EVERYTHING a view model is capable of, a table model makes it easy to make changes to a database table.

To create a view, use the NewTable() function. This takes 2 type parameters:

1. The first should match the general structure of the table.
2. The second is used as the "setter". The setter is expected to have "Optional" fields used to know which values are being inserted/updated.

type User struct {
    ID        int     `db:",pk"` // needed to know the primary key when updating
    VehicleID int
    Name      string
    Email     string
}

// An interface to implement to make the model a table
// this returns the values of the primary keys
func (u User) PrimaryKeyVals() bob.Expression {
    return psql.Arg(u.ID)
}

// UserSetter must implement orm.Setter
type UserSetter struct {
    ID        omit.Val[int]
    VehicleID omit.Val[int]
    Name      omit.Val[string]
    Email     omit.Val[string]
}

var userTable = psql.NewTable[User, UserSetter]("public", "users")

tip

The NewTablex() function takes an extra type parameter to determine how slices are returned.

Why do we need a setter?

A setter is necessary because if we run userTable.Insert(User{}), due to Go's zero values it will be difficult to know which fields we purposefully set.

Typically, we can leave out fields that we never intend to manually set, such as auto increment or generated columns.

userTable.Insert(ctx, db, UserSetter{
    Name: omit.From("Stephen"), // we know the name was set and not the email
}) 

tip

If the Setter methods feel tedious to implement, they can be fully generated from your database.
See Code Generation for more information.

Queries

Like a View the Query() method starts a SELECT query on the model's database view/table. It can accept query mods to modify the final query.

In addition, a Table also has InsertQ, UpdateQ and DeleteQ which begin insert, update and delete queries on the table. As you may expect, they can also accept query mods to modify the final query.

// UPDATE "users" SET "kind" = $1
updateQ := userTable.UpdateQ(
    ctx, db, 
    um.Set("kind").ToArg("Dramatic"),
    um.Returning("*"),
)

The query can then be executed with the Exec() method which returns the rows affected and an error. If the dialect supports the RETURNING clause, One(), All() and Cursor() methods are also included.

rowsAffected, _ := updateQ.Exec()
user, _ := updateQ.One()
users, _ := updateQ.All()
userCursor, _ := updateQ.Cursor()

Insert

// INSERT INTO "users" ("id") VALUES (100)
user, err := models.UsersTable.Insert(ctx, db, &UserSetter{
    ID: omit.From(100),
    // add other columns
})

InsertMany

Bulk insert models

// INSERT INTO "users" ("id") VALUES (100), (101), (102)
users, err := models.UsersTable.InsertMany(ctx, db,
    &UserSetter{ID: omit.From(100)},
    &UserSetter{ID: omit.From(101)},
    &UserSetter{ID: omit.From(102)},
)

Update

// UPDATE "users"
// SET "vehicle_id" = 200
// WHERE "users"."id" IN (1, 2)
err := models.UsersTable.Update(ctx, db, &UserSetter{VehicleID: omit.From(200)}, user1, user2)

Upsert

info

The method signature for this varies by dialect.

// INSERT INTO "users" ("id") VALUES (100) ON CONFLICT DO UPDATE SET "id" = EXCLUDED."id"
user, err := models.UsersTable.Upsert(ctx, db, true, nil, nil, &UserSetter{
    ID: omit.From(100),
    // add other columns
})

Delete

// DELETE FROM "users" WHERE "id" = 100
err := models.UsersTable.Delete(ctx, db, user)