---
name: sqlc
description: Use when writing SQL queries, regenerating sqlc code, or calling generated Querier methods. Covers generating and using sqlc-generated Go code in this repo.
allowed-tools: Bash, Read, Edit
---

# Sqlc in This Repo

This repo uses [sqlc](https://sqlc.dev) to generate type-safe Go from SQL. The config is at `pkg/sqlc.yaml`. All generated files live under `pkg/generated/` and carry `// Code generated by sqlc. DO NOT EDIT.`

## Configuration Summary

`pkg/sqlc.yaml` defines six query sets, all targeting the same `pkg/migrations/` schema:

| Queries dir           | Generated package          |
| --------------------- | -------------------------- |
| `queries/district`    | `generated/districtsql`    |
| `queries/teacher`     | `generated/teachersql`     |
| `queries/testutil`    | `generated/testutilsql`    |
| `queries/meta`        | `generated/metasql`        |
| `queries/metrics`     | `generated/metricssql`     |
| `queries/leaderboard` | `generated/leaderboardsql` |

All blocks use `engine: postgresql`, `sql_package: pgx/v5`, and `emit_interface: true`.

## The Pgx/v5 DBTX Interface

`sql_package: pgx/v5` selects sqlc's pgx-native code generator. The generated `DBTX` interface uses pgx types, not `database/sql`:

```go
// generated by sqlc — do not edit
type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}
```

This interface has no `PrepareContext` method. **No `Prepare()` constructor is generated.** Prepared statements are handled implicitly by pgx's per-connection LRU statement cache (512 entries by default). The first time a query runs on a given connection, pgx prepares it automatically; subsequent calls on that connection reuse the cached statement. No explicit preparation step is needed or possible in this mode.

`*pgxpool.Pool`, `*pgxpool.Conn`, `*pgx.Conn`, and `pgx.Tx` all satisfy this interface.

## Regenerating Code

Run from `pkg/`:

```bash
cd pkg && sqlc generate
```

Install sqlc if not available:

```bash
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
```

Validate queries without generating (fast, no database required):

```bash
cd pkg && sqlc compile
```

## Writing Queries

Add `.sql` files in the appropriate `pkg/queries/<set>/` subdirectory. Each query needs a name annotation immediately above it:

```sql
-- name: QueryName :one|:many|:exec|:execresult
SELECT ...
```

Return-type commands:

| Command       | Go return type               | When to use                     |
| ------------- | ---------------------------- | ------------------------------- |
| `:one`        | `(RowType, error)`           | Query returns exactly one row   |
| `:many`       | `([]RowType, error)`         | Query returns zero or more rows |
| `:exec`       | `error`                      | INSERT/UPDATE/DELETE, no return |
| `:execresult` | `(pgconn.CommandTag, error)` | Need rows-affected count        |

### Named Parameters

Use `@param_name` for named parameters. Always include `::type` to cast to an explicit PostgreSQL type:

```sql
-- name: GetDistrict :one
SELECT id, name, country_code
FROM district
WHERE id = @district_id::uuid;
```

```sql
-- name: UpsertCurrentYear :exec
INSERT INTO current_district_year (district_id, year_start_date, year_end_date)
VALUES (@district_id::uuid, @year_start_date, @year_end_date)
ON CONFLICT ON CONSTRAINT current_district_year_pkey
    DO UPDATE SET
        year_start_date = EXCLUDED.year_start_date,
        year_end_date   = EXCLUDED.year_end_date;
```

For optional filters, the `@param::text = ''` pattern lets callers skip a condition by passing an empty string:

```sql
-- name: ListByCountry :many
SELECT district_id, ka_locale
FROM current_district_year
WHERE (@country_code::text = '' OR country_code = @country_code)
ORDER BY district_id;
```

## Type Overrides

`pkg/sqlc.yaml` declares these global type overrides:

| PostgreSQL type        | Go type                           | Notes                                |
| ---------------------- | --------------------------------- | ------------------------------------ |
| `uuid`                 | `github.com/google/uuid.NullUUID` | Applies to nullable and non-nullable |
| `pg_catalog.timestamp` | `time.Time`                       | Non-nullable timestamps              |
| `pg_catalog.timestamp` | `pgtype.Timestamp` (nullable)     | Nullable timestamps                  |
| `date`                 | `time.Time`                       |                                      |
| `pg_catalog.numeric`   | `string`                          |                                      |

Use `pgtype.Timestamp` when you need to distinguish `NULL` from a real timestamp. Use `time.Time` otherwise.

## Params Helpers

`pkg/sqldb/params.go` provides helpers to convert Go values into the types that generated query params expect:

| Helper                         | Purpose                                      |
| ------------------------------ | -------------------------------------------- |
| `sqldb.ToUUID(s string)`       | `string` → `uuid.NullUUID`                   |
| `sqldb.ToUUIDs(ss []string)`   | `[]string` → `[]uuid.NullUUID`               |
| `sqldb.ToText[S ~string](v S)` | any string type → `pgtype.Text{Valid: true}` |
| `sqldb.ToNullableText(s)`      | `string` → `pgtype.Text` (empty = null)      |
| `sqldb.ToBool(b bool)`         | `bool` → `pgtype.Bool{Valid: true}`          |
| `sqldb.ToFloat8[F](v F)`       | float type → `pgtype.Float8{Valid: true}`    |
| `sqldb.CastStrings[E](vs []E)` | slice of string-based type → `[]string`      |

```go
params := districtsql.GetDistrictParams{
	DistrictID: sqldb.ToUUID(req.DistrictID),
}
district, err := q.GetDistrict(ctx, params)
```

## Using the Querier Interface

Each generated package exposes a `Querier` interface (from `emit_interface: true`). Depend on the interface, not the concrete `*Queries` type:

```go
import "github.com/Khan/districts-jobs/pkg/generated/districtsql"

type DistrictStore struct {
	q districtsql.Querier
}

func NewDistrictStore(db sqldb.DBTX) *DistrictStore {
	return &DistrictStore{q: districtsql.New(db)}
}
```

## The `sqldb.DBTX` Interface

`pkg/sqldb/helpers.go` defines:

```go
type DBTX interface {
	districtsql.DBTX                           // Exec, Query, QueryRow
	Begin(ctx context.Context) (pgx.Tx, error) // start a transaction
}
```

`*pgxpool.Pool` satisfies `sqldb.DBTX`. `pgx.Tx` satisfies `districtsql.DBTX` but **not** `sqldb.DBTX` (no `Begin` method) — pass `tx` directly to `districtsql.New(tx)`, not to a function expecting `sqldb.DBTX`.

Use `sqldb.DBTX` as the dependency type in services that mix queries and transactions.

## Transactions

Use `pool.Begin(ctx)` to get a `pgx.Tx`. This is the only correct approach: `pgx.Tx` carries the OTel tracer attached to the pool's connection config, so query spans remain visible inside transactions. Always defer `Rollback` — it is a no-op after `Commit`:

```go
func (s *DistrictStore) TransferDistrict(ctx context.Context, fromID, toID string) error {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx) // no-op after Commit

	q := districtsql.New(tx)
	if err := q.DeactivateDistrict(ctx, sqldb.ToUUID(fromID)); err != nil {
		return err
	}
	if err := q.ActivateDistrict(ctx, sqldb.ToUUID(toID)); err != nil {
		return err
	}
	return tx.Commit(ctx)
}
```

If you already have a `*Queries` value and need a transaction-scoped copy of it, use the generated `WithTx` method:

```go
q := districtsql.New(pool)
// ... later, inside a transaction:
txQ := q.WithTx(tx)
```

**Do not use `database/sql` transactions (`*sql.Tx`).** `*sql.Tx` does not implement `districtsql.DBTX` (wrong method signatures — `ExecContext` vs `Exec`, `*sql.Rows` vs `pgx.Rows`), and it bypasses the pgx tracer entirely.