Custom Queries

If you need advanced SQL, write script templates and call them over REST. Scripts accept values from the URL (and headers) and run with the same auth stack as other routes.

awesome_folder/example_of_powerful.read.sql:

SELECT * FROM table WHERE name = "{{.field1}}" OR name = "{{.field2}}"

Get result:

GET /_QUERIES/awesome_folder/example_of_powerful?field1=foo&field2=bar

With a database registry, prefix the database alias:

GET /_QUERIES/tenant-a/awesome_folder/example_of_powerful?field1=foo&field2=bar

When the database prefix is omitted, the default database (pg.database) is used.

Interpolated values are screened. A value written into the SQL text — '{{.field1}}' above — becomes part of the statement, so pREST rejects values carrying quotes, --, ::, or (for multi-word values) a SQL keyword. Since v2.4.2 (#1023), a rejected value fails the request with 400 rather than being silently replaced by an empty string.

For anything user-supplied, bind the value with {{sqlVal "field1"}} instead — bound values skip the screen entirely and can never be parsed as SQL.

To activate filesystem scripts, set a location in prest.toml:

[queries]
location = "/path/to/queries/"

Default storage is the filesystem (queries.storage = "filesystem"). Database-backed storage is available in v2.2.0+ — see below.

Scripts templates rules

In your scripts, the fields to replace have to look like: field1 or field2 are examples.

SELECT * FROM table WHERE name = "{{.field1}}" OR name = "{{.field2}}"

Script file must have a suffix based on http verb:

HTTP Verb	Suffix
GET	.read.sql
POST	.write.sql
PUT, PATCH	.update.sql
DELETE	.delete.sql

In queries.location you need to have a folder for your scripts:

queries/
└── foo
    └── some_get.read.sql
    └── some_create.write.sql
    └── some_update.update.sql
    └── some_delete.delete.sql
└── bar
    └── some_get.read.sql
    └── some_create.write.sql
    └── some_update.update.sql
    └── some_delete.delete.sql

URLs to foo folder:

GET    /_QUERIES/foo/some_get?field1=bar
POST   /_QUERIES/foo/some_create?field1=bar
PUT    /_QUERIES/foo/some_update?field1=bar
PATCH  /_QUERIES/foo/some_update?field1=bar
DELETE /_QUERIES/foo/some_delete?field1=bar


URLs to bar folder:

GET    /_QUERIES/bar/some_get?field1=foo
POST   /_QUERIES/bar/some_create?field1=foo
PUT    /_QUERIES/bar/some_update?field1=foo
PATCH  /_QUERIES/bar/some_update?field1=foo
DELETE /_QUERIES/bar/some_delete?field1=foo

Template data

You can access the query parameters of the incoming HTTP request using the . notation.

For instance, the following request:

GET    /_QUERIES/bar/some_get?field1=foo&field2=bar

Makes available the fields field1 and field2 in the script:

{{.field1}}
{{.field2}}

You can also access the query headers of the incoming HTTP requests using the .header notation.

For instance, the following request:

GET    /_QUERIES/bar/some_get
X-UserId: am9obi5kb2VAYW5vbnltb3VzLmNvbQ
X-Application: prest

makes available the headers X-UserId and X-Application in the script:

{{index .header "X-UserId"}}
{{index .header "X-Application"}}

Header values go through the same screen as query parameters. A rejected header is blanked and logged at WARN rather than failing the request — an ordinary User-Agent contains ( and ;, which the character allow-list refuses, so erroring would reject nearly every browser request.

The keys header, _param, and _header are reserved for template data. Query parameters using those names are ignored.

Credential headers (v2.4.2)

Since v2.4.2 (#1023), credential-bearing headers are withheld from templates entirely and render as an empty string:

Authorization · Proxy-Authorization · Cookie · X-Api-Key · X-Auth-Token · X-Access-Token

They are withheld from the bound form too, so {{sqlVal "header.Authorization"}} is also empty — blanking them is about secrecy, and binding must not become a way around it. A bearer token is plain base64url text that passed the value screen untouched, so a template referencing it interpolated the caller's credential into SQL that was then logged.

The request still succeeds; only the value is gone. A script that scoped rows by the caller's token now matches nothing — move that logic to Permissions, or pass a non-credential header such as X-UserId.

Binding values (sqlVal, sqlList, ident) (v2.4.2)

Bind a value instead of interpolating it and the screen does not apply at all. A bound value travels to PostgreSQL out of band as a query parameter, where it can never be parsed as SQL:

-- interpolated: screened, and rejected for values like 'compra do mes'
SELECT * FROM articles WHERE slug = '{{.slug}}'

-- bound: the caller's value arrives verbatim, whatever it contains
SELECT * FROM articles WHERE slug = {{sqlVal "slug"}}

Helper	Use for	Renders
{{sqlVal "key"}}	A single value	$1
{{sqlList "key"}}	A repeated query parameter (?tag=a&tag=b)	($1,$2)
{{ident "key"}}	A table or column name, which cannot be bound	"public"."users"

sqlVal and sqlList also reach headers, using a header. prefix:

SELECT * FROM tenants WHERE app = {{sqlVal "header.X-Application"}}

These helpers have existed since v2.0.0, but before v2.4.2 they bound the screened value — so a rejected value bound as "". Since v2.4.2 (#1023) they resolve the raw value, which is what makes binding a complete alternative to the screen.

Prefer binding for anything user-supplied — search phrases especially. A phrase containing a common word such as do, as, or or is exactly what the interpolation screen refuses.

When a value is rejected, the request fails with 400 and a message naming the parameter. The value itself is never echoed back:

{
  "error": "invalid value for parameter slug: it contains SQL syntax that cannot be interpolated safely; use the sqlVal template helper to bind free-form values"
}

This applies to anything that renders the value into SQL text — {{.slug}}, {{inFormat "slug"}}, {{unEscape .slug}}. sqlVal and sqlList are exempt.

Template functions

isSet

Return true if the param is set.

SELECT * FROM table
{{if isSet "field1"}}
WHERE name = "{{.field1}}"
{{end}}

defaultOrValue

Return param value or default value.

SELECT * FROM table WHERE name = '{{defaultOrValue "field1" "gopher"}}'

inFormat

If you need to format data for usage on a IN ('option1', 'option2') statement. You can use this with the field inside the format. Whenever passing multiple arguments to inFormat function, you must use multiple field1 instances on the URL.

For example: I want to query the field name with options Mary and John.

-- URL will be equal to /_QUERIES/custom_query/query?name=Mary&name=John

SELECT * FROM names WHERE name IN {{inFormat "name"}}

Future updates will include the support of multiple strings split by , on the same instance of the field.

split

Splits a string into substrings separated by a delimiter

SELECT * FROM table WHERE
name IN ({{ range $index,$part := split 'test1,test2,test3' `,` }}{{if gt $index 0 }},{{end}}'{{$part}}'{{ end }})

limitOffset

Assemble limit offset() string with validation for non-allowed characters parameters must be integer values

SELECT * FROM table {{limitOffset "1" "10"}}

generating the query:

SELECT * FROM table LIMIT 10 OFFSET(1 - 1) * 10

We recommend using the default pREST variables _page and _page_size:

{{limitOffset ._page ._page_size}}

unEscape

URL-decodes a string (percent-encoding and +). The result is written into the SQL text, so it is subject to the value screen — bind with sqlVal when the value comes from the caller.

SELECT * FROM table WHERE path = '{{unEscape .path}}'

Database-backed storage (v2.2.0+)

Available in pREST v2.2.0+ (#980). See v2.2.0 release notes.

Set queries.storage = "database" to store scripts in the prest_queries table instead of (or in addition to importing from) .sql files. Execution URLs stay the same (/_QUERIES/{location}/{script}).

[auth]
enabled = true
migrate_on_startup = true

[jwt]
key = "your-secret"
algo = "HS256"

[queries]
storage = "database"          # default: filesystem
schema = "public"
table = "prest_queries"
register_enabled = true
register_admins = ["[e-mail]"]
restrict = true
migrate_on_startup = true     # default true when storage = "database"
import_on_startup = true      # default true when storage = "database"
import_policy = "update"      # skip | update | error
location = "./queries"        # filesystem import source (also PREST_QUERIES_LOCATION)

[[queries.scripts]]
location = "fulltable"
name = "get_all"
permissions = ["read"]

[[queries.users]]
name = "[e-mail]"
[[queries.users.scripts]]
location = "fulltable"
name = "get_all"
permissions = ["read"]

Key	Meaning
storage	filesystem (default) or database
schema / table	Where rows live (default public.prest_queries)
migrate_on_startup	Create the table on API boot when using database storage
import_on_startup	Import .sql files from location into the table
import_policy	skip, update, or error when a script already exists
restrict	Require auth + script ACL for /_QUERIES execution
register_enabled	Enable admin CRUD on /_QUERIES/registry
register_admins	Usernames allowed to use the registry API

Fail-closed: register_enabled is auto-disabled without auth.enabled, jwt.key, and a non-empty register_admins. restrict is auto-disabled without auth.enabled.

CLI migrate

prestd migrate up queries
prestd migrate down queries

Registry API

When register_enabled = true, admins listed in register_admins can manage stored scripts:

Method	Path	Purpose
GET	/_QUERIES/registry	List (?database= / ?location= filters)
POST	/_QUERIES/registry	Create / upsert
GET	/_QUERIES/registry/{location}/{name}	Get one
PUT	/_QUERIES/registry/{location}/{name}	Update
DELETE	/_QUERIES/registry/{location}/{name}	Delete

Optional {database} path segment or query param scopes multi-database aliases.

Create/update JSON body fields include database, location, name, read_sql, write_sql, update_sql, delete_sql, and description (body size capped at 1 MiB).

Full key list: samples/prest.sample.toml. Fixture: testdata/prest_queries.toml.

Ready-made queries

consultations ready to use prest

• Opps CMS

Troubleshooting

Response	Cause
400 — invalid value for parameter <name>: …	An interpolated value was rejected by the screen. Bind it with sqlVal.
400 — invalid identifier in path	The database, folder, or script name contains characters outside the allow-list, including . — so get_all.read cannot be requested directly.
400 — invalid script path: <folder>/<script>	The resolved .sql file lies outside the queries directory. Since v2.4.2 (#1023), .. segments and symlinks escaping the tree are rejected both lexically and after symlink resolution.
400 — could not parse script <folder>/<script>, check your prest logs	A template parse or render error. Since v2.4.2 the detail is logged rather than returned, so read the prestd logs.
Empty value where a header was expected	The header is a credential header and is withheld from templates.

Since v2.4.2, SQL generated from custom query scripts is not written to logs at any level — the statement is caller-influenced. Use PostgreSQL's own statement logging when you need to see it. See Configuring pREST — Logging.

Related

• Configuring pREST

• Multi-database

• Permissions

• v2.4.2 release notes · v2.2.0 release notes

• Acronyms · REST · SQL