​
Example request:
POST /$sql?_format=yaml​SELECT count(*) FROM patient​# Response## - {count: 7}
POST /$sql?_format=yaml​["SELECT count(*) FROM patient where resource->'status' = ?", true]​# Response## - {count: 2}
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-patientssql: |update patient set resource = resource - 'extension'- id: create-policy-helpersql: |create function patient_for_user(u jsonb) returns jsonbas $$select resource || jsonb_build_object('id', id)from patientwhere id = u#>>'{data,patient_id}'$$ language sql​-- first run response- id: remove-extensions-from-patientssql: ...- id: create-policy-helpersql: ...-- 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-patientsts: <timestamp>sql: ...- id: create-policy-helperts: <timestamp>sql: ...​