# Sqlc

> Add SQL queries, run code generation, and configure type overrides for the hive SQLite data layer. Use when writing new queries, adding tables, or mapping domain types to generated code.

- Skill: `colonyops/sqlc` (Agent Skill)
- Install (CLI): `npx skillmds@latest add colonyops/sqlc`
- Raw SKILL.md: https://api.skillmd.com/api/skills/colonyops/sqlc/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: colonyops (https://skillmd.com/u/colonyops)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/colonyops/sqlc

---


# sqlc in Hive

Hive uses [sqlc](https://sqlc.dev) to generate type-safe Go from SQL queries. The generated files are committed to the repo — never edit them manually.

## File Layout

```
internal/data/db/
├── queries/
│   ├── queries.sql          # Core session/message queries
│   └── queries_hc.sql       # Honeycomb queries (separate file)
├── migrations/
│   └── NNNN_name.up.sql     # Schema migrations (source of truth for sqlc)
├── queries.sql.go           # Generated — do not edit
├── queries_hc.sql.go        # Generated — do not edit
└── models.go                # Generated — do not edit
```

`sqlc.yaml` at the repo root lists both query files under `sql[0].queries`.

## Running Code Generation

After any change to `.sql` query files or `sqlc.yaml`:

```bash
mise run generate    # runs sqlc generate + go-enum generation
# or directly:
sqlc generate
```

Always commit the generated `*.sql.go` and `models.go` alongside the SQL changes.

## Adding a Query

1. Write the annotated query in the appropriate `.sql` file:

```sql
-- name: GetHCItem :one
SELECT id, repo_key, epic_id, parent_id, session_id, title, "desc",
       type, status, depth, created_at, updated_at
FROM hc_items WHERE id = ?;

-- name: ListHCItems :many
SELECT ... FROM hc_items WHERE repo_key = ? ORDER BY created_at DESC;

-- name: InsertHCItem :exec
INSERT INTO hc_items (...) VALUES (...);
```

Annotations: `:one` (returns single row), `:many` (returns slice), `:exec` (no rows returned).

2. Run `mise run generate`.

3. The generated function appears in `queries_hc.sql.go` (or `queries.sql.go`) under the `db` package.

4. Call it via `s.db.Queries().GetHCItem(ctx, id)`.

## Type Overrides (Domain Types in Generated Code)

When a column stores a domain enum, add an override in `sqlc.yaml` so the generated params/returns use the Go type directly instead of `string`:

```yaml
overrides:
  - column: "hc_items.status"
    go_type:
      import: "github.com/colonyops/hive/internal/core/hc"
      type: "Status"
  - column: "hc_items.type"
    go_type:
      import: "github.com/colonyops/hive/internal/core/hc"
      type: "ItemType"
```

The domain type must implement `driver.Valuer` and `sql.Scanner` (or use text marshaling). go-enum generated types satisfy this via `MarshalText`/`UnmarshalText`, which SQLite handles as text.

## Schema Source of Truth

sqlc derives the schema from `internal/data/db/migrations/*.up.sql`. When you add a migration, run `mise run generate` to regenerate models. The generated `models.go` is always overwritten — do not add hand-written code there.

## Separate Query Files

HC queries live in `queries_hc.sql` to keep the diff surface small. Add new feature query files by listing them under `sql[0].queries` in `sqlc.yaml`. All query files share the same `gen.go` output directory and package.

## Common Patterns

**Nullable / optional columns:** Use `sql.NullString`, `sql.NullInt64`, etc. Override with `nullable: true` in `sqlc.yaml` if needed.

**Timestamps:** Stored as `INTEGER NOT NULL` (Unix seconds). The generated code uses `int64`. Conversion to/from `time.Time` happens in the store layer, not in generated code.

**Transactions:** Use `s.db.WithTx(ctx, func(q *db.Queries) error { ... })` — the `db.DB` wrapper provides this. Queries inside the closure use the transactional `Queries` instance.