Query Builder

Hand-written column lists are tedious to maintain and tend to drift out of sync with your models. The csql/qb package derives SQL fragments from your models’ db: tags, so your queries stay correct as the struct changes. Throughout this page, let’s assume we have the following model:

type Rocket struct {
	ID        string    `db:"id,readonly"`
	CreatedAt time.Time `db:"created_at,readonly"`
	Name      string    `db:"name"`
	Fuel      int64     `db:"fuel"`
}

Inserts

qb.Columns renders the column list, qb.ValuePlaceholders the matching ?s, and qb.Values the arguments:

func (q *Queries) InsertRocket(ctx context.Context, rocket Rocket) error {
	query := fmt.Sprintf("INSERT INTO rockets (%s) VALUES (%s)",
		qb.Columns(rocket), qb.ValuePlaceholders(rocket))

	_, err := q.querier.Exec(ctx, query, qb.Values(rocket)...)
	return err
}

This renders INSERT INTO rockets (id, created_at, name, fuel) VALUES (?, ?, ?, ?).

Selects

qb.Columns also works on a zero value, which is handy for SELECT statements:

func (q *Queries) GetRocket(ctx context.Context, id int64) (*Rocket, error) {
	var rocket Rocket

	query := fmt.Sprintf("SELECT %s FROM rockets WHERE id = ?", qb.Columns(Rocket{}))

	err := q.querier.Get(ctx, &rocket, query, id)
	if err != nil {
		return nil, err
	}

	return &rocket, nil
}

For joined queries, you may pass a table alias; qb.Columns(Rocket{}, "r") renders r.id, r.created_at, r.name, r.fuel.

Updates and `readonly`

qb.SetColumns and qb.SetValues render SET clauses, and this is where the ,readonly tag modifier earns its keep. Columns marked readonly, such as id and created_at, are included in inserts but excluded from updates:

func (q *Queries) UpdateRocket(ctx context.Context, rocket Rocket) error {
	query := fmt.Sprintf("UPDATE rockets SET %s WHERE id = ?", qb.SetColumns(rocket))

	_, err := q.querier.Exec(ctx, query, append(qb.SetValues(rocket), rocket.ID)...)
	return err
}

This renders UPDATE rockets SET name = ?, fuel = ? WHERE id = ?. The readonly columns never appear.

Upserts

qb.ValuesAndSetValues returns the insert values followed by the update values, which lines up exactly with an upsert:

query := fmt.Sprintf(
	"INSERT INTO rockets (%s) VALUES (%s) ON CONFLICT (id) DO UPDATE SET %s",
	qb.Columns(rocket), qb.ValuePlaceholders(rocket), qb.SetColumns(rocket))

_, err := q.querier.Exec(ctx, query, qb.ValuesAndSetValues(rocket)...)

All of the functions above follow the same tag rules: untagged, db:"-", and unexported fields are skipped, while embedded structs are flattened recursively. To learn more, see Models.

Getting Started

The Basics

Architecture

HTTP

Database

Frontend

Digging Deeper

Reference

Inserts

Selects

Updates and `readonly`

Upserts

​Inserts

​Selects

​Updates and readonly

​Upserts

Inserts

Selects

Updates and `readonly`

Upserts