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