SQL endpoints

get
$sql
<base-url>/$sql
Execute SQL in Aidbox
Request
Response
Body Parameters
body
optional
string
JSON: SQL string or jdbc friendly array  [SQL, param, param]
200: OK
​
Example request:
request
POST /$sql?_format=yaml
​
SELECT count(*) FROM patient
response
- {count: 7}
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 migration with such id wasn't executed - execute it;  Execution will be stopped on first exception. Operation returns only freshly executed migrations, which mean if there is no pending migrations you will get empty array in response body.
POST /db/migrations
​
- id: remove-extensions-from-patients
  sql: |
    update patient set resource = resource - 'extension'
- id: create-policy-helper
  sql: |
    create function patient_for_user(u jsonb) returns jsonb 
    as $$
        select resource || jsonb_build_object('id', id)
           from patient
           where id = u#>>'{data,patient_id}'
    $$ language sql
​
-- first run response
- id: remove-extensions-from-patients
  sql: ...
- id: create-policy-helper
  sql: ...
  
-- second run response
[]
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 simple script like this:
curl -X POST \
  --data-binary @migrations.yaml \
  -H "Content-type: text/yaml" \
  -u $client_id:$client_secret \
  $box_url/db/migrations
By GET /db/migrations  you can introspect, which migrations were already applied on server:
GET /db/migrations
​
-- resp
- id: remove-extensions-from-patients
  ts: <timestamp>
  sql: ...
- id: create-policy-helper
  ts: <timestamp>
  sql: ...
​
🎓 Custom Resources
Sequence API
Last updated -3
SQL endpoints

get$sql

SQL migrations

get
$sql
