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 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 value of param is an slice this function format to an IN SQL clause.
SELECT * FROM table WHERE name IN {{inFormat "field1"}};
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 }});