SQL endpoints
get
<base-url>/$sql
$sql
Example request:
Without jdbc params
With jdbc params
1
POST /$sql?_format=yaml
2
​
3
SELECT count(*) FROM patient
4
​
5
# Response
6
#
7
# - {count: 7}
Copied!
1
POST /$sql?_format=yaml
2
​
3
["SELECT count(*) FROM patient where resource->'status' = ?", true]
4
​
5
# Response
6
#
7
# - {count: 2}
Copied!

SQL migrations

Aidbox provides POST and GET /db/migrations operations to enable SQL migrations, which can be used to migrate/transform data, create helper functions, views etc.
POST /db/migrations accepts array of {id,sql} objects. If the migration with such id wasn't executed, execute it. Execution will be stopped on the first exception. This operation returns only freshly executed migrations. It means that if there are no pending migrations, you will get an empty array in the response body.
1
POST /db/migrations
2
​
3
- id: remove-extensions-from-patients
4
sql: |
5
update patient set resource = resource - 'extension'
6
- id: create-policy-helper
7
sql: |
8
create function patient_for_user(u jsonb) returns jsonb
9
as $
10
select resource || jsonb_build_object('id', id)
11
from patient
12
where id = u#>>'{data,patient_id}'
13
$ language sql
14
​
15
-- first run response
16
- id: remove-extensions-from-patients
17
sql: ...
18
- id: create-policy-helper
19
sql: ...
20
21
-- second run response
22
[]
Copied!
For your application you can keep migrations.yaml file under source control. Add new migrations to the end of this file when this is required. With each deployment you can ensure migrations are applied on your server using a simple script like this:
1
curl -X POST \
2
--data-binary @migrations.yaml \
3
-H "Content-type: text/yaml" \
4
-u $client_id:$client_secret \
5
$box_url/db/migrations
Copied!
By GET /db/migrations you can introspect which migrations were already applied on the server:
1
GET /db/migrations
2
​
3
-- resp
4
- id: remove-extensions-from-patients
5
ts: <timestamp>
6
sql: ...
7
- id: create-policy-helper
8
ts: <timestamp>
9
sql: ...
10
​
Copied!
Last modified 8mo ago
Copy link