Database Query Code Example Generation Prompt

This prompt is for documentation engineers and DevRel teams who need to generate database query code examples that are secure, parameterized, and production-ready. The primary job-to-be-done is producing code snippets for developer documentation that prevent SQL injection, avoid connection leaks, and demonstrate realistic data access patterns. The ideal user is a technical writer or developer advocate who understands the target language and database driver but needs a reliable, repeatable way to generate safe examples that won't introduce security anti-patterns into public-facing documentation.

Use this prompt when you need examples that cover the full lifecycle of a database operation: establishing a connection with proper credentials management, executing parameterized queries, handling transactions with commit and rollback paths, mapping results to native data structures, and ensuring resources are cleaned up in finally blocks or using statements. It is particularly effective when your documentation must demonstrate patterns for PostgreSQL, MySQL, or similar relational databases using native drivers like psycopg2, mysql-connector-python, or node-postgres. The prompt expects you to provide a target language, a database driver, and a specific query intent—such as a filtered SELECT with joins, an INSERT with a RETURNING clause, or a transactional UPDATE across multiple tables.

Do not use this prompt for generating raw SQL strings without parameterization, as that would defeat the core security purpose. It is also unsuitable for ORM-specific configuration that requires runtime context (like Django model definitions or Hibernate XML mappings), for database schema design documentation (DDL generation has different safety requirements), or for NoSQL query patterns where parameterization semantics differ. If your documentation needs a quick, insecure snippet for conceptual illustration, this prompt will over-engineer the output. Instead, use a simpler code generation prompt and add a prominent security warning. The next section provides the exact prompt template you can copy and adapt for your own documentation pipeline.

This prompt template generates production-quality database query code examples for documentation. It forces the model to produce parameterized queries, proper resource cleanup, and transaction handling rather than naive inline SQL. Replace each square-bracket placeholder with your specific context before sending to the model. The template is designed to work across languages and database drivers while enforcing security and reliability constraints that documentation readers need to see.

codeCopy
You are a senior backend engineer writing documentation code examples for database access patterns.

Generate a complete, runnable code example that demonstrates the following database operation:

[DATABASE_OPERATION_DESCRIPTION]

## Required Context
- Language and version: [LANGUAGE_AND_VERSION]
- Database driver or ORM: [DATABASE_DRIVER_OR_ORM]
- Database type: [DATABASE_TYPE]
- Target table(s) and schema: [TABLE_SCHEMA_DEFINITION]
- Query type: [QUERY_TYPE]

## Output Requirements
Generate a single code block containing:
1. Import statements and dependency declarations with version pins
2. Connection or session setup with configurable connection parameters (use environment variables or config objects, never hardcode credentials)
3. Parameterized query construction (use placeholders, never string interpolation)
4. Query execution with proper error handling for connection failures, query timeouts, and constraint violations
5. Result mapping to typed objects or structures
6. Resource cleanup in a finally block, using context managers, or equivalent idiomatic pattern
7. If the operation modifies data, include explicit transaction boundaries with rollback on failure

## Constraints
- [CONSTRAINTS]
- Never use string concatenation or interpolation to build SQL queries
- Never hardcode credentials, connection strings, or secrets
- Always close connections, cursors, and result sets
- Handle at least these error categories: connection failure, query timeout, unique constraint violation, and foreign key violation
- Use the most idiomatic connection pooling pattern for the specified driver
- Include comments that explain why, not what

## Output Schema
Return ONLY the code block with surrounding explanation in this structure:

### Setup
[Installation and configuration steps]

### Code Example
```[LANGUAGE]
[COMPLETE_RUNNABLE_CODE]

Expected Output

[DESCRIPTION_OF_EXPECTED_RESULT]

Error Scenarios

Risk Level

[RISK_LEVEL]

Additional Examples for Reference

[EXAMPLES]

Adaptation guidance: Replace [DATABASE_OPERATION_DESCRIPTION] with a specific operation like 'Insert a new user record and return the generated ID with conflict handling.' The [CONSTRAINTS] field is where you add domain-specific rules such as 'Must use connection pooling with a minimum pool size of 2' or 'Must log query duration for observability.' For [RISK_LEVEL], use 'low' for read-only examples, 'medium' for single-table writes, and 'high' for multi-table transactions or operations on production-sensitive tables. The [EXAMPLES] field should contain 1-2 existing code examples from your codebase that demonstrate the preferred style and patterns. If the operation is high-risk, add a note in [CONSTRAINTS] requiring a comment block that flags the need for human review before production use.

What to do next: Copy this template into your prompt library. For each new database documentation page, fill in the placeholders and run the prompt. Validate the output by executing the generated code against a test database instance. If the code fails to compile, leaks connections, or uses string interpolation, add those failure patterns to your evaluation harness and iterate on the constraints. For high-risk operations, route the generated example through a security linting step before publication.

This prompt is designed to be integrated into a documentation generation pipeline, not used as a one-off chat interaction. The harness must enforce strict validation because database code examples carry high security risk: a single SQL injection vector or connection leak in published documentation can propagate into production systems. The implementation should treat the prompt output as untrusted code until it passes automated checks and, for high-risk patterns, human review.

Wire the prompt into a pipeline that accepts a structured input payload containing the [DATABASE_TYPE], [QUERY_PATTERN], [LANGUAGE], [ORM_OR_DRIVER], and [SECURITY_LEVEL] fields. Before calling the model, assemble a system message that includes the full prompt template with these values substituted. After receiving the model response, extract the code block and run it through a multi-stage validation harness: (1) Static analysis using language-specific linters and security scanners (e.g., Bandit for Python, ESLint with security plugins for JavaScript, or SQLFluff for raw SQL) to flag SQL injection, hardcoded credentials, and unsafe string formatting; (2) Compilation or parse check to verify the code is syntactically valid; (3) Resource cleanup audit using pattern matching to confirm every connection, cursor, and transaction object has a corresponding close, dispose, or context-manager exit path; (4) Parameterization verification using AST analysis to ensure user-supplied values flow through parameterized queries or ORM bind variables rather than string concatenation. If any stage fails, route the output to a repair prompt with the specific failure message and the original context, allowing up to two retry attempts before escalating to a human reviewer.

For model choice, prefer a model with strong code generation capabilities and a large context window to hold the full schema context and security constraints. If using a model that supports structured outputs, constrain the response to a JSON schema with fields for code, language, dependencies, explanation, and security_notes to simplify extraction. Log every generation attempt, validation result, and retry to an observability system with the prompt version, model identifier, and input parameters for traceability. For high-risk query patterns such as dynamic table names, raw SQL with user input, or multi-tenant data isolation, require a human approval step before the example enters the documentation repository. The harness should also maintain a golden dataset of known-safe query examples to use as few-shot demonstrations and regression tests when the prompt template is updated.