FHIR Storage

Fhirbase Console Overview

Each Aidbox box includes separate PostgreSQL database with installed Fhirbase. Fhirbase is an open source PostgreSQL extension for storing FHIR resources in PostgreSQL. You can get direct connection to it from Box Administration Interface.

Fhirbase Console is a way to interact with Fhirbase, which is built Aidbox.

Fhirbase itself is an open source storage based on the FHIR standard.

Interaction with Fhirbase Console is performed via writing and executing PostgreSQL functions. This is due to the fact that Fhirbase is an extension of PostgreSQL.

Back To Top

Fhirbase Console UI Guide

How to work with the Fhirbase Console

First, you need to create a table of patients.

1. Open Fhirbase Console
2. Copy and paste the following query:

SELECT fhir_create_storage('{"resourceType": "Patient"}'::json);

3. Click the 'Execute' button clojure.lang.LazySeq@4380bb9a 4. Check the result clojure.lang.LazySeq@4380bb9a

Then you can add a new patient:

1. Copy and paste the following query:

SELECT fhir_create_resource(' {"resource": {"resourceType": "Patient"}} ');

2. Click the 'Execute' button

Let's check that the patient has been added:

  1. Copy and paste the following query:
SELECT * FROM patient LIMIT 5;

2. Click the 'Execute' button clojure.lang.LazySeq@4380bb9a 3. Check the result clojure.lang.LazySeq@4380bb9a

Back To Top

Fhirbase API

Conformance

fhir_conformance('{}'::json)

Arguments

Takes an argument JSON

Returns

Returns an JSON

Back To Top

Create storage

fhir_create_storage('{}'::json)

Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns an JSON

Example

Create patient table

SELECT fhir_create_storage('{"resourceType": "Patient"}'::json);

Response:

{
  "message": "Table patient was created",
  "status": "ok"
}

Back To Top

Create all storages

fhir_create_all_storages()

Arguments

Function has no arguments

Returns

Returns an string

Back To Top

Drop storage

fhir_drop_storage('{}'::json)

Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Drop all storages

fhir_drop_all_storages('{}'::json)

Arguments

Function has no arguments

Returns

Returns an JSON

Back To Top

Describe storage

fhir_describe_storage('{}'::json)

n### Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Truncate storage

fhir_truncate_storage('{}'::json)

Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Create resource

fhir_create_resource('{}'::json)

Arguments

Takes an argument JSON with properties:

  • allowId boolean
  • resource JSON with properties:
    • resourceType string (mandatory)

Returns

Returns Resource JSON

Example

Create patient

SELECT fhir_create_resource('
  {
    "resource": {
      "name": [
        {
          "given": [
            "Smith"
          ]
        }
      ],
      "resourceType": "Patient"
    }
  }
');

Response:

{
  "meta": {
    "extension": [
      {
        "valueString": "POST",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ],
    "lastUpdated": "2016-05-05T16:57:26.202Z",
    "versionId": "0a75f4b1-1e15-4628-b1ad-ae8a8730af00"
  },
  "id": "c6258cdc-1c80-4dda-80d4-6027c2a23ffd",
  "name": [
    {
      "given": [
        "Smith"
      ]
    }
  ],
  "resourceType": "Patient"
}

Create patient with specific id

SELECT fhir_create_resource('
  {
    "resource": {
      "id": "smith",
      "resourceType": "Patient"
    },
    "allowId": true
  }
');

Response:

{
  "meta": {
    "extension": [
      {
        "valueString": "POST",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ],
    "lastUpdated": "2016-05-06T13:38:11.910Z",
    "versionId": "c112dd87-1f5b-4c2a-a3c0-783e371cc721"
  },
  "id": "smith2",
  "resourceType": "Patient"
}

Back To Top

Read resource

fhir_read_resource('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • resourceType string (mandatory)

Returns

Returns Resource JSON

Example

Show patient by id

SELECT fhir_read_resource('{"resourceType": "Patient", "id": "smith"}');

Response:

{
  "resourceType": "Patient",
  "meta": {
    "lastUpdated": "2016-05-06T13:18:20.996Z",
    "versionId": "c8da6a21-018f-4646-afbf-dc2e36baaef1",
    "extension": [
      {
        "valueString": "POST",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ]
  },
  "id": "smith"
}

Back To Top

Vread resource

fhir_vread_resource('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • versionId string (mandatory)

Returns

Returns Resource JSON

Back To Top

Update resource

fhir_update_resource('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • resourceType string (mandatory)

Returns

Returns Resource JSON

Example

Update patient by id

SELECT fhir_update_resource('
  {
    "resource": {
      "name": [
        {
          "family": [
            "Smith"
          ],
          "given": [
            "John"
          ]
        }
      ],
      "id": "smith",
      "resourceType": "Patient"
    }
  }
');

Response:

{
  "meta": {
    "extension": [
      {
        "valueString": "PUT",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ],
    "lastUpdated": "2016-05-06T13:46:09.571Z",
    "versionId": "946b91ac-b285-4751-9f6f-c6462d0e7465"
  },
  "name": [
    {
      "family": [
        "Smith"
      ],
      "given": [
        "John"
      ]
    }
  ],
  "id": "smith",
  "resourceType": "Patient"
}

Back To Top

Patch resource

fhir_patch_resource('{}'::json)

Arguments

Takes an argument JSON with properties:

  • patch array (mandatory) contains objects with properties:
    • op string (mandatory)
    • path string (mandatory)
    • value string (mandatory)
  • resource JSON (mandatory) with properties:
    • id string (mandatory)
    • resourceType string (mandatory)

Returns

Returns Resource JSON

Example

Patch patient by id

SELECT fhir_patch_resource('
  {
    "patch": [
      {
        "value": "Jane",
        "path": "\/name\/0\/given\/0",
        "op": "replace"
      }
    ],
    "resource": {
      "id": "johndoe",
      "resourceType": "Patient"
    }
  }
');

Response:

{
  "resourceType": "Patient",
  "name": [
    {
      "family": [
        "Doe"
      ],
      "given": [
        "Jane"
      ],
      "text": "John Doe"
    }
  ],
  "meta": {
    "lastUpdated": "2016-05-11T16:12:50.377Z",
    "versionId": "3541fd2e-4ca9-4790-9382-41f98c8b9ad7",
    "extension": [
      {
        "valueString": "PUT",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ]
  },
  "id": "johndoe"
}

Back To Top

Delete resource

fhir_delete_resource('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • resourceType string (mandatory)

Returns

Returns Resource JSON

Example

Mark patient as deleted (i.e. keep history)

SELECT fhir_delete_resource('{"resourceType": "Patient", "id": "smith"}');

Response:

{
  "resourceType": "Patient",
  "meta": {
    "lastUpdated": "2016-05-06T14:10:50.841Z",
    "versionId": "a307ff6c-d35d-4cc4-9784-a91865dfa933",
    "extension": [
      {
        "valueString": "DELETE",
        "url": "fhir-request-method"
      },
      {
        "valueUri": "Patient",
        "url": "fhir-request-uri"
      }
    ]
  },
  "id": "smith"
}

Back To Top

Terminate resource

fhir_terminate_resource('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Example

Completely delete patient and its history

SELECT fhir_terminate_resource('{"resourceType": "Patient", "id": "smith"}');

Response:


[
  "{\"id\": \"smith\", \"meta\": {\"extension\": [{\"url\": \"fhir-request-method\", \"valueString\": \"POST\"}, {\"url\": \"fhir-request-uri\", \"valueUri\": \"Patient\"}], \"versionId\": \"3074d414-2318-42e4-aebe-f521d0ef1aff\", \"lastUpdated\": \"2016-05-06T14:10:43.186Z\"}, \"resourceType\": \"Patient\"}",
  "{\"id\": \"smith\", \"meta\": {\"extension\": [{\"url\": \"fhir-request-method\", \"valueString\": \"DELETE\"}, {\"url\": \"fhir-request-uri\", \"valueUri\": \"Patient\"}], \"versionId\": \"a307ff6c-d35d-4cc4-9784-a91865dfa933\", \"lastUpdated\": \"2016-05-06T14:10:50.841Z\"}, \"resourceType\": \"Patient\"}"
]

Back To Top

Resource history

fhir_resource_history('{}'::json)

Arguments

Takes Resource JSON with properties:

  • id string (mandatory)
  • resourceType string (mandatory)

Returns

Returns Bundle JSON

Example

Show patient's history

SELECT fhir_resource_history('{"resourceType": "Patient", "id": "smith"}');

Response:

{
  "entry": [
    {
      "resource": {
        "resourceType": "Patient",
        "meta": {
          "lastUpdated": "2016-05-06T13:18:20.996Z",
          "versionId": "c8da6a21-018f-4646-afbf-dc2e36baaef1",
          "extension": [
            {
              "valueString": "POST",
              "url": "fhir-request-method"
            },
            {
              "valueUri": "Patient",
              "url": "fhir-request-uri"
            }
          ]
        },
        "id": "smith"
      },
      "request": {
        "url": "Patient",
        "method": "POST"
      }
    }
  ],
  "type": "history",
  "meta": {
    "lastUpdated": "2016-05-06T13:46:48.278Z"
  },
  "total": 1,
  "resourceType": "Bundle"
}

Back To Top

Resource type history

fhir_resource_type_history('{}'::json)

Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns Bundle JSON

Back To Top

fhir_search('{}'::json)

Arguments

Takes an argument JSON with properties:

  • queryString string (mandatory)
  • resourceType string (mandatory)

Returns

Returns Bundle JSON

Example

Patient search

SELECT fhir_search('{"resourceType": "Patient", "queryString": "name=smith"}');

Response:

{
  "entry": [
    {
      "resource": {
        "deceasedBoolean": "N",
        "resourceType": "Patient",
        "identifier": [
          {
            "system": "urn:oid:2.16.840.1.113883.4.3.42",
            "value": "010107112",
            "type": {
              "text": "External ID"
            }
          },
          {
            "system": "urn:oid:2.16.840.1.113883.4.3.45",
            "value": "160923",
            "type": {
              "text": "Internal ID"
            }
          },
          {
            "system": "urn:oid:2.16.840.1.113883.4.3.46",
            "value": "160923",
            "type": {
              "text": "Alternate ID"
            }
          },
          {
            "value": "5435435435",
            "type": {
              "text": "Account number"
            }
          },
          {
            "system": "http:\/\/hl7.org\/fhir\/sid\/us-ssn",
            "value": "123-22-1111",
            "type": {
              "text": "Social Security Number"
            }
          }
        ],
        "birthDate": "1996-02-16T04:17+03",
        "telecom": [
          {
            "system": "phone",
            "value": "090.293.5104 x4116",
            "use": "home"
          }
        ],
        "contact": [
          {
            "address": {
              "use": "Mailing Address"
            },
            "gender": "unknown",
            "name": {
              "middle": [
                "Ms."
              ],
              "family": [
                "Balistreri"
              ],
              "given": [
                "Bartholome"
              ],
              "text": "JEAN SANDY JACK",
              "use": "official"
            }
          }
        ],
        "address": [
          {
            "postalCode": "08928",
            "country": "USA",
            "state": "MI",
            "line": [
              "45713 Glennie Loaf",
              "Schumm Village"
            ],
            "city": "Rodriguezville"
          }
        ],
        "gender": "female",
        "name": [
          {
            "middle": [
              "Mrs."
            ],
            "family": [
              "Smitham"
            ],
            "given": [
              "Rudy"
            ],
            "text": "JEAN SANDY JACK"
          }
        ],
        "id": "97af0e81-29fc-4a97-935d-a7909da2c17d"
      }
    }
  ],
  "link": [
    {
      "url": "Patient\/search?name=smith&_page=0",
      "relation": "self"
    }
  ],
  "total": 1,
  "type": "searchset",
  "resourceType": "Bundle"
}

Get search SQL

fhir_search_sql('{}'::json)

Arguments

Takes an argument JSON with properties:

  • queryString string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Example

See generated SQL

SELECT fhir_search_sql('
  {
    "queryString": "name=smith",
    "resourceType": "Patient"
  }
');

Response:

[
  "SELECT * FROM \"patient\" tbl1
     WHERE fhir_extract_as_string(
             ( \"tbl1\".\"resource\" )::json,
             ( '[\"Patient\",\"name\"]' )::json,
             'HumanName'
           ) ilike $1
     LIMIT $2",
  "%^^smith%",
  10
]

Back To Top

Expand valueset

fhir_expand_valueset('{}'::json)

Arguments

Takes an argument JSON with properties:

  • id string (mandatory)
  • filter string

Returns

Returns ValueSet JSON

Back To Top

Analyze storage

fhir_analyze_storage('{}'::json)

Analyze on resource tables to update PostgreSQL statistic

Arguments

Takes Resource JSON with properties:

  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Index parameter

fhir_index_parameter('{}'::json)

Create index for parameter

Arguments

Takes an argument JSON with properties:

  • name string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Index order

fhir_index_order('{}'::json)

Creates index for parameter sort

Arguments

Takes an argument JSON with properties:

  • name string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Unindex parameter

fhir_unindex_parameter('{}'::json)

Arguments

Takes an argument JSON with properties:

  • name string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

Unindex order

fhir_unindex_order('{}'::json)

Arguments

Takes an argument JSON with properties:

  • name string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an JSON

Back To Top

fhir_explain_search('{}'::json)

Arguments

Takes an argument JSON with properties:

  • queryString string (mandatory)
  • resourceType string (mandatory)

Returns

Returns an integer

Back To Top

Transaction

fhir_transaction('{}'::json)

Arguments

Takes Bundle JSON with properties:

  • resourceType string (mandatory)
  • entry array (mandatory) contains objects with properties:
    • resource JSON (mandatory) with properties:
      • resourceType string (mandatory)
    • request JSON (mandatory) with properties:
      • method string (mandatory)
      • url string (mandatory)

Returns

Returns Bundle JSON

Example

Create practitioner and associated patient

Patient search

SELECT fhir_transaction('
  {
    "resourceType": "Bundle",
    "id": "bundle-transaction-id",
    "type": "transaction",
    "entry": [
      {
        "resource": {
          "resourceType": "Practitioner"
        },
        "request": {
          "method": "PUT",
          "url": "/Practitioner/created-practitioner-id"
        }
      },
      {
        "resource": {
          "resourceType": "Patient",
          "careProvider": [
            {
              "reference": "/Practitioner/created-practitioner-id"
            }
          ]
        },
        "request": {
          "method": "PUT",
          "url": "/Patient/created-patient-id"
        }
      },
      {
        "request": {
          "method": "GET",
          "url": "/Patient?_id=created-patient-id&_include=careprovider"
        }
      }
    ]
  }
');

Response:

{
  "resourceType": "Bundle",
  "type": "transaction-response",
  "entry": [
    {
      "resourceType": "Practitioner",
      "id": "created-practitioner-id",
      "meta": {
        "versionId": "b6de0892-0aa5-48f3-a645-b833fc4bf0c3",
        "lastUpdated": "2016-05-12T12:29:22.412Z",
        "extension": [
          {
            "url": "fhir-request-method",
            "valueString": "PUT"
          },
          {
            "url": "fhir-request-uri",
            "valueUri": "Practitioner"
          }
        ]
      }
    },
    {
      "resourceType": "Patient",
      "careProvider": [
        {
          "reference": "/Practitioner/created-practitioner-id"
        }
      ],
      "id": "created-patient-id",
      "meta": {
        "versionId": "ffc1c306-c2bd-472e-916f-d75ba2ce133d",
        "lastUpdated": "2016-05-12T12:29:22.414Z",
        "extension": [
          {
            "url": "fhir-request-method",
            "valueString": "POST"
          },
          {
            "url": "fhir-request-uri",
            "valueUri": "Patient"
          }
        ]
      }
    },
    {
      "resourceType": "Bundle",
      "type": "searchset",
      "total": 1,
      "link": [
        {
          "relation": "self",
          "url": "/Patient?_id=created-patient-id&_include=careprovider&_page=0"
        }
      ],
      "entry": [
        {
          "resource": {
            "id": "created-patient-id",
            "meta": {
              "extension": [
                {
                  "url": "fhir-request-method",
                  "valueString": "POST"
                },
                {
                  "url": "fhir-request-uri",
                  "valueUri": "Patient"
                }
              ],
              "versionId": "ffc1c306-c2bd-472e-916f-d75ba2ce133d",
              "lastUpdated": "2016-05-12T12:29:22.414Z"
            },
            "careProvider": [
              {
                "reference": "/Practitioner/created-practitioner-id"
              }
            ],
            "resourceType": "Patient"
          }
        },
        {
          "resource": {
            "id": "created-practitioner-id",
            "meta": {
              "extension": [
                {
                  "url": "fhir-request-method",
                  "valueString": "PUT"
                },
                {
                  "url": "fhir-request-uri",
                  "valueUri": "Practitioner"
                }
              ],
              "versionId": "b6de0892-0aa5-48f3-a645-b833fc4bf0c3",
              "lastUpdated": "2016-05-12T12:29:22.412Z"
            },
            "resourceType": "Practitioner"
          }
        }
      ]
    }
  ]
}

Back To Top

Valueset after changed

fhir_valueset_after_changed('{}'::json)

Arguments

Takes ValueSet JSON with properties:

  • id string (mandatory)

Returns

Returns an JSON

Back To Top

Valueset after deleted

fhir_valueset_after_deleted('{}'::json)

Arguments

Takes ValueSet JSON with properties:

  • id string (mandatory)

Returns

Returns an JSON

Back To Top

Disk usage

fhirbase_disk_usage_top('{}'::json)

Arguments

Takes an argument JSON

Returns

Returns an array

Back To Top

Benchmark

fhir_benchmark('{}'::json)

Arguments

Takes an argument JSON

Returns

Returns an array

Back To Top

Fhir version

fhir_version()

Arguments

Function has no arguments

Returns

Returns version string

Example

SELECT fhir_version();

Response:

1.3.0

Back To Top

Fhirbase version

fhirbase_version()

Arguments

Function has no arguments

Returns

Returns version string

Example

SELECT fhirbase_version();

Response:

1.3.0.13

Back To Top

Fhirbase release date

fhirbase_release_date()

Arguments

Function has no arguments

Returns

Returns datetime string

Example

SELECT fhirbase_release_date();

Response:

1970-12-31T23:59:59Z

Back To Top