# Generate SQL Route

> Create a pREST custom SQL template route under /_QUERIES. Use when the user needs a parameterized SQL script with curl examples.

- Skill: `prest/generate-sql-route` (Agent Skill)
- Install (CLI): `npx skillmds@latest add prest/generate-sql-route`
- Raw SKILL.md: https://api.skillmd.com/api/skills/prest/generate-sql-route/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: prest (https://skillmd.com/u/prest)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/prest/generate-sql-route

---


# Generate SQL template route

## When to use

- User has a SQL query that should become a REST endpoint
- User asks for a custom report or filtered read via pREST
- User needs verb-specific templates (read/write/update/delete)

## Ask first

1. HTTP verb (default GET / read)
2. Folder and script name (e.g. `reports` / `users`)
3. Query parameters and types
4. Target schema/table(s)
5. Single-DB vs multi-DB alias prefix
6. Path to `queries.location` if already configured

## Instructions

1. Place scripts under the configured queries directory:

```text
queries/
└── <folder>/
    └── <name>.read.sql   # GET
    └── <name>.write.sql  # POST
    └── <name>.update.sql # PUT/PATCH
    └── <name>.delete.sql # DELETE
```

2. Use template params as `{{.field}}`. Prefer helpers when useful (`isSet`, `defaultOrValue`, `inFormat`, `limitOffset`).
3. Ensure `prest.toml` includes:

```toml
[queries]
location = "./queries"
```

4. Document the **real** URL shape (never invent `/reports/users/:id`):

```text
GET /_QUERIES/<folder>/<name>?field=value
GET /_QUERIES/<database>/<folder>/<name>?field=value
```

5. Include curl examples and a sample JSON response.
6. Note that template parameters are sanitized in current pREST mainline — still warn against injecting raw SQL fragments via params.

## Safety notes to include

- Prefer parameterized filters over `SELECT *` without limits.
- Restrict which roles can call write/update/delete scripts.
- Avoid destructive scripts unless explicitly requested.
- Call out that custom queries bypass table ACL patterns differently than CRUD table endpoints — keep them least-privilege.

## Docs

https://docs.prestd.com/api-reference/custom-queries