sqlc_skill

Overview

This skill documents how to work with sqlc in the NCPS repository to produce type-safe Go database code for SQLite, PostgreSQL, and MySQL. It describes where SQL sources live, where generated code is written, and the required workflow when queries change. The goal is reliable, consistent database query generation and safe integration with Go code.

How this skill works

sqlc reads SQL query files for each supported engine and generates Go types and query implementations into engine-specific output packages. After generation, a go generate step extracts a common Querier interface and shared domain models, then creates thin wrappers for each backend. The process enforces a single source of truth for queries while keeping application code engine-agnostic.

When to use it

• After making any change to db/query.<engine>.sql files
• When adding new query methods or altering result columns/types
• When switching or supporting an additional database engine
• Before committing changes that affect database behavior
• When updating domain models that depend on query results

Best practices

• Run sqlfluff lint and sqlfluff format on db/query.*.sql before generation
• Always run sqlc generate after editing SQL files to keep generated code in sync
• Run go generate ./pkg/database to regenerate the common Querier and models
• Keep equivalent queries for all supported engines unless intentionally engine-specific
• Never manually edit generated files like pkg/database/querier.go or models.go

Example use cases

• Add a new SELECT query to db/query.postgres.sql and regenerate to expose a new typed method in postgresdb
• Refactor a query that changes returned columns: update SQL, run sqlc generate, then go generate to update shared models
• Introduce a MySQL-specific optimization: add query to db/query.mysql.sql and maintain consistent interfaces via generated wrappers
• Audit SQL formatting and correctness by running sqlfluff then regenerate to ensure consistent codegen