Comment on page
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:
SELECT * 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/
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 given a folder to 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
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"}}
Return true if the param is set.
SELECT * FROM table
{{if isSet "field1"}}
WHERE name = "{{.field1}}"
{{end}}
;
Return param value or default value.
SELECT * FROM table WHERE name = '{{defaultOrValue "field1" "gopher"}}';
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.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 }});
Assemble
limit offset() string with validation for non-allowed characters parameters must be integer valuesSELECT * 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}}
consultations ready to use prest
Last modified 18d ago