Over a million developers have joined DZone.

Building a Healthcare App With Auto-Generated APIs From Legacy and Modern Databases

DreamFactory can be used to integrate several types of databases at the same time, pulling their data and generating APIs.

Learn how API management supports better integration in Achieving Enterprise Agility with Microservices and API Management, brought to you in partnership with 3scale

DreamFactory can be used to turn an existing database into a REST API as well as it can be used for a new project with a 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 the 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 patients details as a response.

carsten-postman.gif


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're just creating an API endpoint to illustrate the use of the two databases. We're not going to make the actual app.

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
  • Table: side_effect
  • Table: drug_sideeffect
    • Columns: id, drug_id, sideeffect_id

MongoDB

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

carsten-df.gif


Creating the Script Service

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

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.

carsten-df-pt2.gif


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);


carsten-cli.gif


The variable patient will contain an object with patient information. Get the patient's 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.

Unleash the power of your APIs with future-proof API management - Create your account and start your free trial today, brought to you in partnership with 3scale.

Topics:
mysql database ,mysql ,mongodb

Published at DZone with permission of Carsten Jacobsen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}