Custom Queries
If need perform an advanced SQL, you can write some scripts SQL and access them by REST. These scripts are templates where you can pass by URL, values to them.
awesome_folder/example_of_powerful.read.sql:
1SELECT * FROM table WHERE name = "{{.field1}}" OR name = "{{.field2}}";
Get result:
GET /_QUERIES/awesome_folder/example_of_powerful?field1=foo&field2=bar
To activate it, you need configure a location to scripts in your prest.toml like:
[queries]
location = /path/to/queries/
Scripts templates rules
In your scripts, the fields to replace have to look like: field1 or field2 are examples
1SELECT * 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 given a folder to your scripts:
1queries/
2└── foo
3 └── some_get.read.sql
4 └── some_create.write.sql
5 └── some_update.update.sql
6 └── some_delete.delete.sql
7└── bar
8 └── some_get.read.sql
9 └── some_create.write.sql
10 └── some_update.update.sql
11 └── some_delete.delete.sql
12
13URLs to foo folder:
14
15GET /_QUERIES/foo/some_get?field1=bar
16POST /_QUERIES/foo/some_create?field1=bar
17PUT /_QUERIES/foo/some_update?field1=bar
18PATCH /_QUERIES/foo/some_update?field1=bar
19DELETE /_QUERIES/foo/some_delete?field1=bar
20
21
22URLs to bar folder:
23
24GET /_QUERIES/bar/some_get?field1=foo
25POST /_QUERIES/bar/some_create?field1=foo
26PUT /_QUERIES/bar/some_update?field1=foo
27PATCH /_QUERIES/bar/some_update?field1=foo
28DELETE /_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"}}
Template functions
isSet
Return true if param is set.
1SELECT * FROM table
2{{if isSet "field1"}}
3WHERE name = "{{.field1}}"
4{{end}}
5;
defaultOrValue
Return param value or default value.
1SELECT * FROM table WHERE name = '{{defaultOrValue "field1" "gopher"}}';
inFormat
If you need to format data for a 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.
In example: I want to query field name
with options Mary
and John
.
1-- URL will be equal to /_QUERIES/custom_query/query?name=Mary&name=John
2
3SELECT * FROM names WHERE name IN {{inFormat "name"}};
Future updates will include the support of multiple strings splited by ,
on the same
instance of the field.
split
Splits a string into substrings separated by a delimiter
1SELECT * FROM table WHERE
2name 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
1SELECT * FROM table {{limitOffset "1" "10"}}
generating the query:
1SELECT * FROM table LIMIT 10 OFFSET(1 - 1) * 10
We recommend using the default pREST variables _page
and _page_size
:
1{{limitOffset ._page ._page_size}}
Ready-made queries
consultations ready to use prest