generate-sql-route

name	generate-sql-route
description	Create a pREST custom SQL template route under /_QUERIES. Use when the user needs a parameterized SQL script with curl examples.

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:

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:

[queries]
location = "./queries"

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

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