Building a healthcare app with auto-generated APIs from legacy and modern databases

DreamFactory can be used to turn an existing database into a REST API, as well as for a new project with fresh database. But, you can also use it to integrate multiple database types, in tandem.

Let’s say we have a legacy MySQL database, which has been maintained and added to over years, and we want to use it in a new application. We don’t want to store application specific data in the legacy database, which might also be used for other purposes. The solution is to use two databases.

In this example we want to develop an app API, where patients can keep track of the drugs they are taking, and show possible side effects.

For this purpose we have the legacy MySQL database with a drug directory and relationship table, which holds information about which side effects the different drugs may have. The patients and the drugs they take are stored in a new MongoDB database, created just for the app.

Our goal is to make an API call with the patient’s ID, and get the patient’s details as a response.

To accomplish that, follow these steps:

1. Create a MySQL service, and hook up the MySQL database
2. Create a MongoDB service and hook up the MongoDB database
3. Create a Script service, which generates an API endpoint, and combines data from the two databases

In this blog we just create an API endpoint to illustrate the use of the two databases, and not the actual app.

The first two steps are covered in other blog posts, see this blog post for instructions for setting up a service for MySQL, and this blog post for instructions for setting up a service for MongoDB.

The third step is where the magic happens, this is where we pull data from two different databases, and combine the data to the response showed above.

Databases

For this example three tables have been created in the MySQL database, and one collection has been created in the MongoDB database.

MySQL

• Table: drugs
  • Columns: id, name

• Table: side_effect
  • Columns: id, name

• Table: drug_sideeffect
  • Columns: id, drug_id, sideeffect_id

MongoDB

• Collection: patient
  • Keys: id, first_name, last_name, drugs

Creating the Script Service

In this example we create a V8js Script service, but DreamFactory support other scripting languages like PHP, Python etc. too.

Go to the Services tab, and click Create.

Fill out the Info form with Name, Label and Description. The name will be the API endpoint, if you enter patient, the API endpoint will be api/v2/patient.

Click the Config tab, and this is where the script goes. Use the code editor, or upload a script file if you prefer to write the code in a code editor of your choice.

Writing the script

Ok, let’s write some code. First, we get a patient document from the MongoDB database, based on patient ID. The patient API is called with a parameter, which is the ID of the patient:

api/v2/patient?filter=id%3D0  (patient id=0)

// Get patient document from MongoDB database

var patient = platform.api.get("mongodb/_table/patient", event.request.parameters);

The variable patient will contain an object with patient information. Get the patients name and drug array from this object:

// Extract patient ID, first name and last name

var patient_id = patient.content.resource[0].id;

var patient_firstname = patient.content.resource[0].first_name;

var patient_lastname = patient.content.resource[0].last_name;
// Get IDs of the drugs the patient is taking

var patient_drug_ids = [];
for(var i = 0; i < patient.content.resource[0].drugs.length; i++) {

    patient_drug_ids.push("(id%20%3D%20" + patient.content.resource[0].drugs[i] + ")");

}

Now, get the drugs the patient is taking. Note the use of filters:

// Get the drugs the patient is taking, from the legacy MySQL database

var drugs = platform.api.get("db/_table/drugs?filter=" + patient_drug_ids.join("%20or%20"));

Now we loop through the drugs, to get the drug name and side effects:

// Extract drug ID, name and side effects

var drug_details = [];

for(var i = 0; i < drugs.content.resource.length; i++) {

    var drug = drugs.content.resource[i];

    drug['side_effects'] = getSideeffects(drug.id);

    drug_details.push(drug);

}

The side effects are retrieved with the function getSideeffects():

// Function for getting side effects for the drug ID

function getSideeffects(id) {

    // Get all side effect IDs for the drug by drug ID

    var drug_sideeffects = platform.api.get("db/_table/drug_sideeffect?filter=drug_id%3D" + id);

    // Create query for getting side effect names

    var sideeffect_query = [];

    for(var i = 0; i < drug_sideeffects.content.resource.length; i++) {

         sideeffect_query.push("(id%20%3D%20" + drug_sideeffects.content.resource[i].sideeffect_id + ")");
    }

    // Get side effects based on query, and return names only

    var sideeffects = platform.api.get("db/_table/side_effect?fields=name&filter=" + sideeffect_query.join("%20or%20"));

    var sideeffect_array = sideeffects.content.resource.map(function(a) {return a.name;});

    return sideeffect_array;

}

Now we have everything we need to return the patient object we want:

// Create the result object

var result = {

    id: patient_id,

    first_name: patient_firstname,

    last_name: patient_lastname,

    drug_details: drug_details,

};

return result;

Here’s the script as a single block:

// Get patient document from MongoDB database

var patient = platform.api.get("mongodb/_table/patient", event.request.parameters);

// Extract patient ID, first name and last name

var patient_id = patient.content.resource[0].id;

var patient_firstname = patient.content.resource[0].first_name;

var patient_lastname = patient.content.resource[0].last_name;
// Get IDs of the drugs the patient is taking

var patient_drug_ids = [];

for(var i = 0; i < patient.content.resource[0].drugs.length; i++) {

    patient_drug_ids.push("(id%20%3D%20" + patient.content.resource[0].drugs[i] + ")");

}

// Get the drugs the patient is taking, from the legacy MySQL database

var drugs = platform.api.get("db/_table/drugs?filter=" + patient_drug_ids.join("%20or%20"));

// Extract drug ID, name and side effects

var drug_details = [];

for(var i = 0; i < drugs.content.resource.length; i++) {

     var drug = drugs.content.resource[i];

     drug['side_effects'] = getSideeffects(drug.id);

     drug_details.push(drug);

}

// Create the result object

var result = {

     id: patient_id,

     first_name: patient_firstname,

     last_name: patient_lastname,

     drug_details: drug_details,

};

return result;

// Function for getting side effects for the drug ID

function getSideeffects(id) {

    // Get all side effect IDs for the drug by drug ID

    var drug_sideeffects = platform.api.get("db/_table/drug_sideeffect?filter=drug_id%3D" + id);

    // Create query for getting side effect names

    var sideeffect_query = [];

    for(var i = 0; i < drug_sideeffects.content.resource.length; i++) {

      sideeffect_query.push("(id%20%3D%20" + drug_sideeffects.content.resource[i].sideeffect_id + ")");

    }

    // Get side effects based on query, and return names only

    var sideeffects = platform.api.get("db/_table/side_effect?fields=name&filter=" + sideeffect_query.join("%20or%20"));

    var sideeffect_array = sideeffects.content.resource.map(function(a) {return a.name;});
    return sideeffect_array;

}

This small example shows how a Script Service easily can be used to work with multiple databases at the same time. The benefit of using a script service is that your application doesn’t have to pull data from two databases to work with the data. With Script Services this can be done server side, providing your application with a simple API call.