sqlc Agent Skills & AI Rules

# Claude Code Development Guide for sqlc

This document provides essential information for working with the sqlc codebase, including testing, development workflow, and code structure.

## Quick Start

### Prerequisites

- **Go 1.26.4+** - Required for building and testing
- **Docker & Docker Compose** - Required for integration tests with databases (local development)
- **Git** - For version control

## Database Setup with sqlc-test-setup

The `sqlc-test-setup` tool (`cmd/sqlc-test-setup/`) automates installing and starting PostgreSQL, MySQL, ClickHouse, SQL Server and Spanner Omni for tests. Both commands are idempotent and safe to re-run, and take the databases to act on as arguments (`postgresql`, `mysql`, `clickhouse`, `mssql`, `spanner`); with none named they act on all five.

### Install databases

```bash
go run ./cmd/sqlc-test-setup install
```

This will:
- Configure the apt proxy (if `http_proxy` is set, e.g. in Claude Code remote environments)
- Install PostgreSQL via apt
- Download and install MySQL 26.7 from Oracle's deb bundle
- Download the ClickHouse 25.8 release into the user cache, where goldeneye caches it too
- Install SQL Server 2025 from Microsoft's apt repository and run its setup, accepting the EULA on your behalf (Ubuntu 22.04 and 24.04 only)
- Download the Spanner Omni standalone server release into the user cache (linux/amd64 only)
- Resolve all dependencies automatically
- Skip anything already installed

### Start databases

```bash
go run ./cmd/sqlc-test-setup start
```

This will:
- Start PostgreSQL and configure password auth (`postgres`/`postgres`)
- Start MySQL via `mysqld_safe` and set root password (`mysecretpassword`)
- Start a ClickHouse server in the background on localhost, with the default user's password `mysecretpassword`
- Start SQL Server through systemd, or in the background where there is no systemd, with the sa password `Mysecretpassword1!`
- Start a Spanner Omni single server in the background, serving plaintext gRPC on port 15000
- Verify the connections
- Skip steps that are already done (running services, existing config)

Connection URIs after start:
- PostgreSQL: `postgres://postgres:[email protected]:5432/postgres?sslmode=disable`
- MySQL: `root:mysecretpassword@tcp(127.0.0.1:3306)/mysql`
- ClickHouse: `clickhouse://default:[email protected]:9000` (`CLICKHOUSE_SERVER_URI`)
- SQL Server: `sqlserver://sa:[email protected]:1433?encrypt=disable` (`MSSQL_SERVER_URI`)
- Spanner Omni: `localhost:15000` (`SPANNER_SERVER_URI`)

The examples' tests for ClickHouse, SQL Server and Spanner read those variables and skip when unset. `docker-compose.yml` runs the same databases for anyone who prefers containers.

### Run tests

```bash
# Full test suite (requires databases running)
go test --tags=examples -timeout 20m ./...
```

## Running Tests

### Basic Unit Tests (No Database Required)

```bash
go test ./...
```

### Full Test Suite with Docker (Local Development)

```bash
docker compose up -d
go test --tags=examples -timeout 20m ./...
```

### Full Test Suite without Docker (Remote / CI)

```bash
go run ./cmd/sqlc-test-setup install
go run ./cmd/sqlc-test-setup start
go test --tags=examples -timeout 20m ./...
```

### Running Specific Tests

```bash
# Test a specific package
go test ./internal/config

# Run with verbose output
go test -v ./internal/config

# Run a specific test function
go test -v ./internal/config -run TestConfig

# Run with race detector (recommended for concurrency changes)
go test -race ./internal/config
```

## Testing Strategy

**Cover new work with end-to-end tests, not unit tests.** A change to the
compiler, an engine, the analysis core or codegen is exercised by running sqlc
the way a user does — a schema, a query file and a committed golden output —
so the test says what sqlc produces rather than what an internal function
returns. Internal APIs move around; the SQL that goes in and the output that
comes out is the contract worth pinning down.

Adding coverage means adding a directory under `/internal/endtoend/testdata/`,
not a `*_test.go` next to the code. Reach for a unit test only when the
behavior genuinely cannot be reached through the CLI, and say why in the test.

Some `*_test.go` files predate this and remain; they are not a precedent for
new ones.

### End-to-End Tests

- **Location:** `/internal/endtoend/`
- **Requirements:** `--tags=examples` flag and running databases
- **Tests:**
  - `TestExamples` - Main end-to-end tests
  - `TestReplay` - Replay tests
  - `TestFormat` - Code formatting tests
  - `TestJsonSchema` - JSON schema validation
  - `TestExamplesVet` - Static analysis tests

A case is a directory holding the inputs and the expected output. `exec.json`
names the command and its arguments — omit it and the case runs `generate`,
comparing the generated files against the ones committed alongside; give it
`{"command": "analyze", "args": [...]}` and the case compares the command's
stdout against `stdout.json` (or `stdout.txt` for a command that does not
print JSON). A case that is expected to fail commits its `stderr.txt`. Regenerate a golden by running the command in its directory and
writing the output back over the committed file.

`TestReplay` runs the whole corpus once per *context*. `base` runs each case as
committed and `managed-db` reruns it against a live database, so a context can
change the config a case is generated with and the experiments it is generated
under. A case restricts itself to some of them with `"contexts": [...]` in its
`exec.json`, and commits per-context expected errors as `stderr/<context>.txt`.
There is only one set of committed golden files, so every context is expected
to generate identical code.

The `core` context generates every case through the analysis core
(`SQLCEXPERIMENT=coreanalyzer`). The two paths still disagree, so it is opt-in
and needs no database:

```bash
SQLC_TEST_CORE=1 go test ./internal/endtoend -run 'TestReplay/core'
```

Go aborts a test binary on panic, so a case that panics the core analyzer ends
the run early. Run a subset to get past one (`-run 'TestReplay/core/^select'`).

### Dialect Checks

The dialect seeds under `/internal/engine/<engine>/dialect/` are generated
from a live database by `/internal/goldeneye`, a nested module, and its tests
verify the committed files against one byte for byte. The same module checks
the `analyze_*` cases under `/internal/endtoend/testdata/` against what the
database itself reports for them, so a `fixture.sql` next to a case's schema
gives the queries rows to run against. ClickHouse, DuckDB, MySQL, SQLite,
SQL Server and Spanner have the check today; engines whose database is not
available skip.

```bash
cd internal/goldeneye
go run ./cmd/goldeneye install clickhouse   # download the pinned clickhouse binary once
go run ./cmd/goldeneye install duckdb       # download the current DuckDB 2.0 preview build once
go run ./cmd/goldeneye install sqlite       # build the pinned sqlite3 shells once; needs a C compiler
POSTGRESQL_SERVER_URI="postgres://postgres:[email protected]:5432/postgres?sslmode=disable" \
MYSQL_SERVER_URI="root:mysecretpassword@tcp(127.0.0.1:3306)/mysql" \
MSSQL_SERVER_URI="sqlserver://sa:[email protected]:1433?encrypt=disable" \
SPANNER_SERVER_URI="localhost:15000" go test ./...
go run ./cmd/goldeneye generate postgresql  # rewrite the files after a change
```

The checks are not part of CI. The `gen` workflow
(`.github/workflows/gen.yml`) generates the files on demand and uploads
them as an artifact.

### Example Tests

- **Location:** `/examples/` directory, a Go module of its own so the
  drivers the examples run against are not dependencies of sqlc, and so
  cgo stays on for the DuckDB driver
- **Requirements:** Tagged with "examples", requires live databases; run
  with `cd examples && go test --tags=examples ./...`
- **Databases:** PostgreSQL, MySQL, SQLite, ClickHouse, DuckDB, Spanner and
  SQL Server examples; each skips when its server is not named

## Database Services

The `docker-compose.yml` provides test databases:

- **PostgreSQL 16** - Port 5432
  - User: `postgres`
  - Password: `mysecretpassword`
  - Database: `postgres`

- **MySQL 26.7** - Port 3306
  - User: `root`
  - Password: `mysecretpassword`
  - Database: `dinotest`

## Makefile Targets

```bash
make test              # Basic unit tests only
make test-examples     # Tests with examples tag
make build-endtoend    # Build end-to-end test data
make test-ci           # Full CI suite (examples + endtoend + vet)
make vet               # Run go vet
make start             # Start database containers
```

## CI/CD Configuration

### GitHub Actions Workflow

- **File:** `.github/workflows/ci.yml`
- **Go Version:** 1.26.4
- **Jobs:** `build` cross-compiles sqlc with cgo off; `test` runs sqlc's own tests; `examples` runs the examples module against every database; `vuln_check` runs `govulncheck`
- **Database Setup:** `sqlc-test-setup` (not Docker) installs and starts the databases directly on the runner: PostgreSQL and MySQL for `test`, and all five for `examples`
- **Test Command:** `gotestsum --junitfile junit.xml -- --tags=examples -timeout 20m ./...` in `test`, and `go test --tags=examples ./...` under `examples/` in `examples`
- **Additional Checks:** `govulncheck` for vulnerability scanning

## Development Workflow

### Building Development Versions

```bash
# Build main sqlc binary for development
go build -o ~/go/bin/sqlc-dev ./cmd/sqlc

# Build JSON plugin (required for some tests)
go build -o ~/go/bin/sqlc-gen-json ./cmd/sqlc-gen-json
```

### Environment Variables for Tests

You can override database connections via environment variables:

```bash
POSTGRESQL_SERVER_URI="postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
MYSQL_SERVER_URI="root:mysecretpassword@tcp(127.0.0.1:3306)/mysql?multiStatements=true&parseTime=true"
```

## Code Structure

### Key Directories

- `/cmd/` - Main binaries (sqlc, sqlc-gen-json, sqlc-test-setup)
- `/internal/cmd/` - Command implementations (vet, generate, etc.)
- `/internal/engine/` - Database engine implementations
  - `/postgresql/` - PostgreSQL parser and converter
  - `/dolphin/` - MySQL parser (uses TiDB parser)
  - `/sqlite/` - SQLite parser
  - `/duckdb/` - DuckDB 2.0 parser (uses darkwing, the pure Go port of
    DuckDB's PEG parser)
  - `<engine>/dialect/` - The engine's type system and standard library, as
    JSONL read by `/internal/core/seed`; the generated parts come from
    `/internal/goldeneye`
- `/internal/goldeneye/` - Nested module that generates the dialect seeds
  under `/internal/engine/<engine>/dialect/` from a live database, checks
  the committed ones against it, and checks the analyze cases under
  `/internal/endtoend/testdata/` against what the database reports, one
  package per engine; see its README
- `/internal/core/` - The analysis core: catalog, analyzer and dialect seeds
- `/internal/compiler/` - Query compilation logic
- `/internal/codegen/` - Code generation for different languages
- `/internal/config/` - Configuration file parsing
- `/internal/endtoend/` - End-to-end tests
- `/internal/sqltest/` - Test database setup (Docker, native, local detection)
- `/examples/` - Example projects for testing

### Important Files

- `/Makefile` - Build and test targets
- `/docker-compose.yml` - Database services for testing
- `/.github/workflows/ci.yml` - CI configuration

## Common Issues & Solutions

### Network Connectivity Issues

If you see errors about `storage.googleapis.com`, the Go proxy may be unreachable. Use `GOPROXY=direct go mod download` to fetch modules directly from source.

### Test Timeouts

End-to-end tests can take a while. Use longer timeouts:
```bash
go test -timeout 20m --tags=examples ./...
```

### Race Conditions

Always run tests with the race detector when working on concurrent code:
```bash
go test -race ./...
```

### Database Connection Failures

If using Docker:
```bash
docker compose ps
docker compose up -d
```

If using sqlc-test-setup:
```bash
go run ./cmd/sqlc-test-setup start
```

## Tips for Contributors

1. **Run tests before committing:** `go test --tags=examples -timeout 20m ./...`
2. **Cover new behavior end to end:** Add a case under `/internal/endtoend/testdata/`
3. **Check for race conditions:** Use `-race` flag when testing concurrent code
4. **Iterate on one case:** `go test ./internal/endtoend -run 'TestReplay/base/<case>'`
5. **Read existing cases:** `/internal/endtoend/testdata/` has one per feature

## Git Workflow

### Branch Naming

- Feature branches should start with `claude/` for Claude Code work
- Branch names should be descriptive and end with the session ID

### Committing Changes

```bash
git add <files>
git commit -m "Brief description of changes"
git push -u origin <branch-name>
```

### Rebasing

```bash
git checkout main
git pull origin main
git checkout <feature-branch>
git rebase main
git push --force-with-lease origin <feature-branch>
```

## Resources

- **Main Documentation:** `/docs/`
- **Development Guide:** `/docs/guides/development.md`
- **CI Configuration:** `/.github/workflows/ci.yml`
- **Docker Compose:** `/docker-compose.yml`