Modeling, ORM & Architecture · 07 Queries, Scopes & Raw SQL

Nothing runs until the finisher

Every chained call adds a clause to a builder. Walk it with Next — the SQL only exists on the last line.

→Chain methods build; finishers execute. Where, Joins, Order, Limit and Preload return a builder. Find, First, Count, Scan, Create and Update run it.

?Always use ? placeholders. Where("email = ?", v) parameterises; Where(fmt.Sprintf("email = '%s'", v)) is SQL injection, and GORM cannot tell them apart.

✗A *gorm.DB holding conditions must not be reused. Calling Where on the result of another Where and then reusing the parent leaks clauses into the next query. Start from db.Model(...) or use db.Session(&gorm.Session{}).

✓Scan into a purpose-built struct when the result is not a model — aggregate rows have no id and do not belong in []Appointment.

Scopes: a filter with a name

A Scope is just a function that takes a builder and returns one. That is the entire mechanism — and it is what stops the same WHERE being retyped in nine places.

internal/repository/postgres/scopes.go

package postgres

func Active(db *gorm.DB) *gorm.DB {
    return db.Where("status IN ?", []string{"booked", "attended"})
}

// parameterised scopes return the function instead
func ForDoctor(id uint) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("doctor_id = ?", id)
    }
}

func OnDay(d time.Time) func(*gorm.DB) *gorm.DB {
    start := d.Truncate(24 * time.Hour)
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("starts_at >= ? AND starts_at < ?", start, start.AddDate(0,0,1))
    }
}

// at the call site the query reads like the sentence you were asked for
func (r *AppointmentRepo) DayList(ctx context.Context, doc uint, day time.Time) ([]model.Appointment, error) {
    var out []model.Appointment
    err := r.db.WithContext(ctx).
        Scopes(Active, ForDoctor(doc), OnDay(day)).
        Order("starts_at").
        Find(&out).Error
    return out, err
}

✓The rule lives once. When "active" grows to exclude no_show, you edit one function rather than grepping for every status IN.

→Scopes compose in any order because each one only adds a clause. Scopes(Active, ForDoctor(3)) and the reverse emit the same SQL.

→They also work on updates and deletes, not just reads — the builder does not care which finisher comes last.

→WithContext belongs on every query. It is what lets a cancelled request actually cancel the database call instead of holding a connection until it completes.

Scopes are the repository's vocabulary. By Session 10 these functions are the only place a WHERE clause appears anywhere in the codebase — the service layer asks for Active appointments and never learns what column stores the status.

Raw SQL is not an admission of defeat

The builder covers ordinary queries well. For a handful of shapes it is strictly worse than the SQL you already know how to write.

a window function — expressible in the builder, unreadable there

type GapRow struct {
    DoctorID  uint
    StartsAt  time.Time
    PrevEnd   *time.Time
    GapMin    *int
}

const gapQuery = `
SELECT doctor_id,
       starts_at,
       lag(starts_at + (duration_min || ' minutes')::interval)
           OVER (PARTITION BY doctor_id ORDER BY starts_at)        AS prev_end,
       EXTRACT(EPOCH FROM starts_at - lag(starts_at + (duration_min || ' minutes')::interval)
           OVER (PARTITION BY doctor_id ORDER BY starts_at)) / 60  AS gap_min
  FROM appointments
 WHERE starts_at >= ? AND starts_at < ?
   AND status IN ('booked','attended')
 ORDER BY doctor_id, starts_at`

func (r *AppointmentRepo) Gaps(ctx context.Context, from, to time.Time) ([]GapRow, error) {
    var out []GapRow
    err := r.db.WithContext(ctx).Raw(gapQuery, from, to).Scan(&out).Error
    return out, err
}

✓Reach for Raw when the SQL is the specification — window functions, recursive CTEs, INSERT … ON CONFLICT, bulk upserts, anything where the query is the interesting logic.

→Raw for results, Exec for statements. Raw(...).Scan(&out) reads rows; Exec(...) runs an UPDATE or DELETE and reports RowsAffected.

→Placeholders still apply. Raw parameterises ? exactly like the builder — raw SQL is not a reason to start concatenating strings.

✗Do not use Raw to skip learning the builder. If the query is a WHERE and an ORDER BY, the builder gives you composition, scopes and context for free. Raw throws all three away.

Tip

Raw SQL stays inside the repository package like every other query. The service layer calling Gaps(ctx, from, to) has no idea whether the implementation used the builder, a window function or a stored procedure — which is exactly the boundary Session 10 makes explicit.

TASK

Three queries, three tools

(a) Write "all active appointments for doctor 3 tomorrow, earliest first" twice — once inline, once with Scopes — and confirm from the log that the SQL is identical. (b) Write a query returning each doctor's appointment count for a given day, scanning into a purpose-built struct rather than []Doctor. (c) Express "the busiest three doctors this month" with a window function in Raw, then argue in two sentences whether the builder version would have been better.

Recap

Session 07 in four lines

1Chain methods build a query; only a finisher like Find, Count or Scan emits SQL.

2Always parameterise with ?, and never reuse a *gorm.DB that already carries conditions.

3A Scope is a function from builder to builder — it gives a filter a name, one definition and free composition.

4Use Raw when the SQL is the point, Exec when nothing comes back — and keep both inside the repository.