The Complete Production SOP: How to Deploy and Run SQLx Pure Rust Async SQL with Compile-Time Verification in Autonomous AI Agents
Production SOP to install and execute SQLx compile-time query verification in Codex, Claude Code, and Cursor. Fix database offline caching and PgPool errors.
Technical Overview & Architectural Context
Autonomous coding agents frequently generate brittle database access layers in Rust. When tasked with writing async SQL using raw drivers or high-level ORMs, agents commonly hallucinate column data types, map nullable database columns to non-optional Rust primitives, write syntactically invalid SQL that compiles cleanly, or fail to establish optimal async connection pooling configurations. In large codebases, agents also struggle to manage compile-time validation pipelines, regularly breaking CI/CD pipelines because they forget to generate or refresh offline query metadata cache files. The 'SQLx Pure Rust Async SQL with Compile-Time Verification' skill enforces strict procedural boundaries across LLM execution contexts. By explicitly mandating the use of the `query!` and `query_as!` macros alongside `sqlx::PgPoolOptions`, the agent is guided through strict static validation against the target schema. Crucially, the SOP provides deterministic rules for running `cargo sqlx prepare` to generate `.sqlx/` metadata directories, allowing compile-time verification without requiring an active database instance during CI builds or offline sandboxed development.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules into your agent runtime configuration.
Agent runtimes such as Claude Code, Codex, Cursor, and Windsurf index skill manifests from designated project or global directories (`.agents/skills/` or `.cursor/rules/`). Mounting the SKILL.md registers the declarative triggers, bounded filesystem permissions, and procedural steps so that any request involving async SQL, connection pooling, or compile-time verification invokes the standardized SQLx SOP.
mkdir -p .agents/skills/sqlx-rust-async-compile-time-checked && curl -fsSL https://askills.dev/api/skills/sqlx-rust-async-compile-time-checked/raw -o .agents/skills/sqlx-rust-async-compile-time-checked/SKILL.md---
name: "sqlx-rust-async-compile-time-checked"
description: "Execute raw SQL queries in Rust with compile-time SQL syntax and type checking via query! macro."
metadata:
author: "LaunchBadge"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "query! macro sqlx rust"
- "cargo sqlx prepare offline"
- "sqlx connection pool pgpool"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---
Place the SKILL.md directly in `.agents/skills/` within your root repository so both CLI-based agents (Claude Code, Codex) and IDE-based extensions (Cursor, Windsurf) discover the identical trigger context without duplicating tokens.
The skill frontmatter sets `net_access: false` and `shell_exec: false`. The agent must not attempt arbitrary background shell processes; provide schema files locally or supply pre-configured environment flags.
Environment Secrets & Dependency Validation
Configure DATABASE_URL and SQLX_OFFLINE variables securely in workspace configuration without exposing live credentials.
SQLx requires a live database connection string via `DATABASE_URL` during local development builds, or the `SQLX_OFFLINE=true` environment flag when reading queries from the `.sqlx` metadata directory. Add the required dependencies to `Cargo.toml` and configure the development database connection inside a root `.env` file that is strictly tracked in `.gitignore`.
cargo add sqlx --features runtime-tokio-rustls,postgres,macros,chrono,uuid && echo "DATABASE_URL=postgres://postgres:postgres@localhost:5432/app_db" > .env && echo ".env" >> .gitignore[dependencies]
tokio = { version = "1.38", features = ["full"] }
sqlx = { version = "0.8", features = [
"runtime-tokio-rustls",
"postgres",
"macros",
"chrono",
"uuid"
] }
serde = { version = "1.0", features = ["derive"] }Set `SQLX_OFFLINE=true` in local CI workflows and provide a verified `sqlx-data.json` or `.sqlx` directory so the agent does not attempt to access external network sockets during containerized builds.
Never expose actual production connection strings in prompt queries or inline code; ensure `.env` is explicitly ignored in git before allowing the agent to run cargo builds.
Intent Matching & Trigger Invocation
Prompt the agent using explicit SQLx triggers to enforce compile-time checked macro usage and PgPool management.
When asking the AI agent to implement database repositories or persistence layers, include exact match phrases like `query! macro sqlx rust` and `sqlx connection pool pgpool`. This ensures the runtime injects the SOP rules into the system prompt, preventing the agent from falling back to untyped `sqlx::query` strings or unmanaged connection instantiation.
use sqlx::postgres::{PgPool, PgPoolOptions};
use std::time::Duration;
pub async fn create_connection_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
PgPoolOptions::new()
.max_connections(10)
.min_connections(2)
.acquire_timeout(Duration::from_secs(5))
.idle_timeout(Duration::from_secs(600))
.connect(database_url)
.await
}Instruct the agent to implement mapping structs with `#[derive(sqlx::FromRow, Debug)]` whenever dynamic multi-table joins are needed, keeping type enforcement deterministic.
The `query!` and `query_as!` macros check SQL syntax and types at compile time. If your agent writes a migration, it must apply migrations before calling the query macro.
Verification, Linting & Output Quality Gate
Run offline metadata preparation and cargo type-checks to guarantee reproducible offline CI validation.
Execute `cargo sqlx prepare` to freeze the compile-time query metadata into a committed `.sqlx` folder. Next, execute `cargo check` with `SQLX_OFFLINE=true` to guarantee that compile-time verification passes without a network connection or live PostgreSQL instance.
cargo sqlx prepare -- --all-targets && SQLX_OFFLINE=true cargo check --all-targets{
"db": "PostgreSQL",
"query": "SELECT id, name, email FROM users WHERE id = $1",
"describe": {
"columns": [
{"name": "id", "ordinal": 0, "type_info": "Uuid"},
{"name": "name", "ordinal": 1, "type_info": "Text"},
{"name": "email", "ordinal": 2, "type_info": "Text"}
],
"parameters": {"Left": ["Uuid"]},
"nullable": [false, false, false]
}
}Ensure `.sqlx` is tracked in version control (git add .sqlx) so CI runners do not require database provisioning during pure compile checks.
If you modify a database migration or column type, you must run `cargo sqlx prepare -- --all-targets` again, otherwise `cargo build` in offline mode will throw a cached query hash mismatch error.
Real-World Scenario: Implement a high-throughput async User Repository in Rust with compile-time verified queries
“Using the sqlx-rust-async-compile-time-checked skill, create a UserRepository module in Rust using sqlx connection pool pgpool and the query! macro sqlx rust. Implement functions to create a user and fetch a user by ID. Prepare the project for offline builds using cargo sqlx prepare offline.”
Runtime Troubleshooting & Failure Modes
Root Cause: The `query!` macro attempted compile-time validation against the database specified in `DATABASE_URL`, but pending migrations have not been applied to the local database.
cargo sqlx migrate run && cargo checkRoot Cause: The compile step was run without a defined `DATABASE_URL` environment variable, and the project lacks a valid `.sqlx` offline query cache directory.
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/app_db" && cargo sqlx prepare -- --all-targetsFrequently Asked Questions (SQLx Pure Rust Async SQL with Compile-Time Verification SOP)
Q:Can I use SQLx Pure Rust Async SQL with Compile-Time Verification across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification supported across all modern agent runtimes. Placing the file in your project's `.agents/skills/` directory allows Codex, Claude Code CLI, Cursor, and Windsurf to dynamically parse triggers and adhere to the same compile-time query standards.
Q:How does SQLx Pure Rust Async SQL with Compile-Time Verification prevent context window overflow during long agent sessions?
By establishing concise procedural boundaries and deterministic validation gates rather than polluting system prompts with redundant rules. The agent receives exact code patterns for `PgPool` initialization and macro querying without needing repeated roundtrips to resolve Rust type mismatches.
Q:Does this skill require escalated root or container permissions?
No. It is audited with a security score of 98/100 and runs safely within workspace-scoped sandboxes. The skill operates entirely within the project repository directory and requires only standard cargo CLI toolchain permissions.
Ready to equip your autonomous agent with SQLx Pure Rust Async SQL with Compile-Time Verification?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.