返回提示词目录第 1170 页

使用 sqlc 编写数据库访问代码

依据现有表结构编写查询和调用代码,处理空值、事务与错误,并通过真实数据库测试验证。

完整提示词

<context>
sqlc turns SQL files into typed Go functions, so the queries are real SQL checked against the schema at generate time, and the Go side has no ORM magic. The common mistakes sit around it. sqlc reads the schema from the migration files listed in `sqlc.yaml`, so a query written against a column that only exists in production fails to generate, which is the point. Each query needs the right annotation: `:one` returns `pgx.ErrNoRows` (or `sql.ErrNoRows`) when nothing matches, which callers must map to "not found" rather than a 500; `:many` returns an empty slice; `:exec` and `:execrows` for writes (`:execrows` tells you whether an update touched anything). Nullable columns become `pgtype.Text` or `sql.NullString` unless overrides map them to pointers, and `sqlc.narg` marks optional parameters. Dynamic filters are awkward in sqlc; `sqlc.narg` with `COALESCE` or `IS NULL OR` patterns usually beats string-built SQL. Transactions use the generated `WithTx` on a `Queries` value, with `defer tx.Rollback(ctx)` and an explicit commit. Every call should take the request `context.Context` so a cancelled request stops its query, and a pool (`pgxpool`) needs sane limits. `rows.Close` and `rows.Err` are handled for you in generated code but not in hand-written queries.
</context>

<task>
Data access for: {{feature}}
{{#setup}}Setup: {{setup}}{{/setup}}

1. Read `sqlc.yaml`, the schema or migration files it points at, existing query files and how the `Queries` value and pool are created and passed around. Follow the package layout already in use.
2. Write the SQL queries in the queries file with names and annotations (`:one`, `:many`, `:exec`, `:execrows`, `:copyfrom` for bulk inserts on pgx), named parameters with `sqlc.arg` and `sqlc.narg`, and `RETURNING` where the caller needs the row.
3. Add any missing migration for new columns or indexes with the project's migration tool, in a new file.
4. Run `sqlc generate` (and `sqlc vet` if configured) and fix errors in the SQL, not in the generated code. Never edit generated files.
5. Write the Go code that calls the queries: context passed through, `ErrNoRows` mapped to the domain's not-found error, NULL types converted at the boundary, and transactions with `WithTx`, deferred rollback and explicit commit for multi-statement writes.
6. Write tests against a real database (a test container or the project's test database setup), covering not-found, NULL values and a transaction that rolls back on error. Run `go vet` and the tests and report the results.
</task>

<constraints>
- Never edit generated sqlc files; change the SQL or `sqlc.yaml` and regenerate.
- Do not build SQL strings from input; use parameters and `sqlc.narg` patterns for optional filters.
- Do not run migrations against a shared or production database yourself.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>

<output_format>
## Queries
The SQL query file changes and any migration, in fenced blocks with paths.
## Generated code use
The Go code that calls the queries, with error and NULL handling.
## Transactions
Which operations run in a transaction and how rollback is guaranteed, or "None needed".
## Check
`sqlc generate`, `go vet` and test results.
## Open questions
Numbered, or "None".
</output_format>

可替换内容

  • {{feature}}
  • {{#setup}}
  • {{setup}}
  • {{/setup}}
  • {{> guardrails/scope-discipline}}
  • {{> guardrails/verify-before-done}}

分类与标签

编程开发、数据库访问、类型安全查询、事务处理、数据库迁移、集成测试

前往 AI 图片创作