PostgreSQL query standards and postgres_runner usage. Covers schema discovery via migrations, query execution (CLI and programmatic), output formats (table/JSON/CSV), parameterized queries, and safety rules. Use when querying, exploring, or working with the database.
Standards for querying the PostgreSQL database in this repository.
| Action | Command |
|---|---|
| Run query | uv run python -m src.postgres_runner.run_query "SELECT ..." |
| JSON output | ... -f json |
| CSV output | ... -f csv |
| Verbose | ... -v |
Before writing any query, examine the database schema by reading migration files:
infra/postgres/migrations/
Migrations are the source of truth for tables, columns, types, and constraints.
Key tables (verify against migrations):
books – book metadataspans – text segments with embeddingscraft_examples – writing craft examplesanalysis_sections – analysis resultscharacter_* tables – character data# Basic query (table output)
uv run python -m src.postgres_runner.run_query "SELECT * FROM books LIMIT 5"
# JSON output
uv run python -m src.postgres_runner.run_query "SELECT COUNT(*) FROM craft_examples" -f json
# CSV output with verbose mode
uv run python -m src.postgres_runner.run_query "SELECT title, author FROM books" -f csv -v
from src.postgres_runner.queries import run_select_query
# Returns list of dicts
results = run_select_query("SELECT * FROM books WHERE id = %s", (book_id,))
Database connection is configured via environment variables in .env:
BOOK_ANALYZER_PG_HOST=...
BOOK_ANALYZER_PG_PORT=...
BOOK_ANALYZER_PG_DATABASE=...
BOOK_ANALYZER_PG_USER=...
BOOK_ANALYZER_PG_PASSWORD=...
SELECT COUNT(*) FROM craft_examples;
SELECT COUNT(*) FROM books WHERE author IS NOT NULL;
-- Sample rows
SELECT * FROM spans LIMIT 10;
-- Column inspection
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'books';
-- Distinct values
SELECT DISTINCT category FROM craft_examples;
Always use parameterized queries in code:
# GOOD - parameterized
run_select_query("SELECT * FROM books WHERE id = %s", (book_id,))
# BAD - string interpolation (SQL injection risk)
run_select_query(f"SELECT * FROM books WHERE id = {book_id}")
SELECT b.title, s.content
FROM books b
JOIN spans s ON s.book_id = b.id
WHERE b.id = 1
LIMIT 5;
LIMIT for exploration queries.env| Issue | Solution |
|---|---|
| Connection refused | Check .env variables, ensure Postgres is running |
| Table not found | Read migrations, check table name spelling |
| Permission denied | Verify BOOK_ANALYZER_PG_USER has SELECT rights |
| Slow query | Add LIMIT, check for missing indexes in migrations |
python skill for type hints and error handlinginfra/postgres/migrations/src/postgres_runner/