Raw SQL

Write SQL yourself and scan the results into structs, when the query builder is in the way.

Before you start

Connect to a database.

Steps

1. Scan rows into a struct

// AuthorStats is one row of GET /stats.
type AuthorStats struct {
	Name  string `db:"name" json:"name"`
	Posts int64  `db:"posts" json:"posts"`
	Views int64  `db:"views" json:"views"`
}

func authorStats(ctx context.Context) ([]AuthorStats, error) {
	return db.Raw[AuthorStats](ctx, `
		SELECT a.name, COUNT(p.id) AS posts, COALESCE(SUM(p.views), 0) AS views
		FROM authors a LEFT JOIN posts p ON p.author_id = a.id AND p.deleted_at IS NULL
		GROUP BY a.id, a.name
		ORDER BY views DESC`)
}

(Copied from examples/database, region raw.)

Columns match the struct’s db tags (or snake_case field names). Columns without a field are ignored, and fields without a column keep their zero values, so one struct can serve several queries.

2. Pass parameters

Write ? on every database; it becomes $1, $2… on PostgreSQL:

// illustrative
posts, err := db.Raw[Post](ctx, "SELECT * FROM posts WHERE author_id = ? AND views > ?", id, 100)

Or name them with db.Named:

// illustrative
n, err := db.RawFirst[int64](ctx,
	"SELECT COUNT(*) FROM posts WHERE author_id = :author AND created_at > :since",
	db.Named{"author": id, "since": since})

? inside quotes and comments is left alone. SQL without arguments is sent exactly as written. With arguments on PostgreSQL, write ?? for a literal ?, such as the JSON operators (tags ??| array['go']). A whole query without ? is also sent unchanged, so native $1 placeholders work; SQL fragments in the query builder (db.SQL, WhereRaw) must use ?.

3. Read one row or one value

db.RawFirst[T] returns the first row, or db.ErrNotFound. T can be a single value such as int64, string or time.Time for one-column results.

4. Run statements

// illustrative
res, err := db.Exec(ctx, "UPDATE posts SET views = 0 WHERE author_id = ?", id)
n, _ := res.RowsAffected()

Raw queries and statements join the context’s transaction like any other query.

Complete example

authorStats in examples/database, which the GET /stats handler caches (Cache values).

How it works

db.Raw rewrites placeholders for the dialect, runs the query, and scans with a column-to-field plan cached per struct type and column list. SQL is passed through otherwise: you choose the dialect-specific syntax.

Testing it

Seed an in-memory SQLite database and compare the scanned structs.

Common problems

Next steps

• Query data
• Transactions