Mapping HL7 to Database

Remember that the code we supply here is intended as a basis for you to adapt, rather than a boilerplate solution.

Please contact support at if you need further help.

Sample Code [top]

You can download the project file (code and six test messages) or copy the code from below.

Code for main():

-- this module contains functions customized for this channel only
-- so we named the module after the channel = unique name (and obvious)

-- change the module name from "Mapping_HL7_to_Database" to match your channel name
local Map = require "Mapping_HL7_to_Database"

function main(Data)
   -- Parse the HL7 message
   local Msg, Name = hl7.parse{vmd = 'example/demo.vmd', data = Data}
   -- Create the database tables target node tree
   local Out       = db.tables{vmd='example/demo.vmd', name=Name}
   -- Map (part of) the message
   -- map patient table
   Map.mapPatient(Out.patient[1], Msg.PID)
   -- map kin table
   Map.mapKin(Out.kin[1], Msg)   
   -- remove empty fields to prevent "accidentally" 
   -- updating fields to NULL when merging with Conn:merge()

   -- remove all empty leaf fields in a node recursively
   -- Save data to database
   local Conn = db.connect{
   Conn:merge{data=Out, live = true}

Tip: You can also remove empty fields without using recursion, or specify the exact fields you wish to remove:

   -- remove all empty fields in a table (without recursion)
   -- remove only chosen fields
   -- you will need to create a separate function for each table
   -- ADVANTAGE: gives you the option to remove nodes with data that you do not
   --            wish to forward (as opposed to removing just empty fields)

Code for the module:

-- this module contains functions customized for this channel only
-- so we named it after the channel we used = unique name (and obvious)

local Mapping = {}

function Mapping.mapPatient(Patient, PID)
   Patient.Id        = PID[3][1][1]
   Patient.LastName  = PID[5][1][1][1]
   Patient.GivenName = PID[5][1][2]
   Patient.Ssn       = PID[19]

function Mapping.mapKin(Kin, Msg)
   for i = 1, #Msg.NK1 do
      Kin[i].PatientId    = Msg.PID[3][1][1]
      Kin[i].LastName     = Msg.NK1[i][2][1][1][1]
      Kin[i].FirstName    = Msg.NK1[i][2][1][2]
      Kin[i].Relationship = Msg.NK1[i][3][1]

-- RECOMMENDED: method to remove all empty nodes
-- uses recursion
function Mapping.rRemoveEmptyNodes(Node)
   -- recursively remove empty nodes 
   for i = #Node, 1, -1 do
      if Node[i]:isLeaf() then
         if Node[i]:isNull() then
      else -- recursive call

-- you can use this method if you are not comfortable with recursion
function Mapping.removeEmptyNodes(Node)
   for i = #Node, 1, -1 do
      if Node[i]:isNull() then

-- only use this method if you need to delete *selected* fields
-- create similar functions for other tables
function Mapping.removePatientNodes(P)
   -- "manually" remove only selected fields
   -- NOTE: Count down from highest field no
   P:remove(13) -- Weight
   P:remove(12) -- Sex
   P:remove(7)  -- Religion
   P:remove(4)  -- Race    

return Mapping

Preparation: Create the “test” database [top]

If you do not already have a SQLite database called “test” in the Iguana install directory, then you can create it by following the instructions in the Create a SQLite DB to match your VMD tutorial.

Using the code [top]

  • This code would usually be used in a To Translator component script
  • Use your own VMD file that contains the table structure for your target database
  • Because the module contains functions customized for this channel only, we recommend indicating it is private by naming the module after the channel:
    • This gives you an unique name that is easy to remember
    • The unique name prevents the module from being “accidentally discovered” and re-used
    • This module should only contain code unique to this module
  • Adapt mappings to your match your target database tables:
    • Change table and field name(s) to match the target
    • Add/remove mappings as needed

      Note: The Kin mapping code copies repeated NK1 segments by using a for loop. You can use this method for other repeating nodes. If you have a repeating node that contains a repeating node (like an HL7 Procedure which can contain multiple Roles), you simply use a loop within a loop (i.e., a loop for Roles within the the loop for Procedures).

  • We recommend removing empty nodes to prevent NULL database updates with Conn:merge(), see the warning below:
    • Recommended: Using rRemoveEmptyNodes() is the simplest because automatically removes all the empty nodes in a tree.
      Note: This function is recursive (but you don’t need to understand how that works)
    • If you prefer not to use recursion you can use the removeEmptyNodes() which simply removes empty sub-nodes in the specified node.
    • If you only need to remove selected nodes (i.e., you don’t want to remove all empty nodes), then use a hardcoded function like removePatientNodes().
      Note: You need a function for each node, i.e., to remove fields from the kin table create a removeKinNodes() function.

Warning: When you are using Conn:merge() you must always use node:Remove() to delete any fields that you don’t want to update in the database. Any empty fields that you do not remove will be set to NULL during the merge process, hence “deleting” the data in those fields (which is probably not what you want).

For more information, see: Use remove() with merge() to prevent “accidental” data loss from NULL updates.

How it works [top]


  • There is a mapping function for each table, mapPatient() and mapKin()
  • For each mapped field there is a line of code with an assignment statement
    • Iguana’s auto-completion feature makes it simple to add and modify mappings
  • The mapKin() code also copies repeated NK1 segments using a for loop

Removing fields:

  • There are three node remove functions, rRemoveEmptyNodes(), removeEmptyNodes() and removePatientNodes()
  • The recursive remove function rRemoveEmptyNodes() makes removal of empty nodes simpler by removing all the nodes in a tree. Hence one call of rRemoveEmptyNodes() replaces multiple calls of removeEmptyNodes() (one for each table).
  • The removeEmptyNodes() loops through a node and removes all empty sub-nodes
    • Use this function if you prefer not to use recursion
  • The removePatientNodes() removes only selected fields, for each field there is a line of code with a remove() statement
    • You need a function for each node, i.e., to remove fields from the kin create a removeKinNodes() function

Note: You always need to count down from the highest field index number when deleting fields. If you count up then the wrong fields will be deleted, and you will get errors that the index is out of range.

If you want to know why:

  • When you delete a field all the higher index numbers are decremented by one
  • If you delete fields 3 then 4, you will actually delete 3 and 5 – because 5 is decremented to 4 (after the first deletion)
  • If you have a total of 13 fields and you delete 10 then 13 you will get an index out of range error – because 13 is decremented to 12 (and field 13 no longer exists)

Tip: The exact behaviour of the node:remove() function is customized to suit the type of node tree. See this FAQ for details: How node:remove() behaviour varies depending on node type.

Best Practices [top]

  • Do your mapping in the mapping in a To Translator destination component :
    • Sample data is available to make coding and debugging easier
      Note: Sample data is not available in a From Translator script
    • Allows you to do (optional) pre-processing in the source or filter component (i.e., in a From LLP script etc)
  • Refactoring code: We used a modular version of the code that we recommend you use. However be aware that there are other options, for example you could combine mapPatient() and mapKin() into a single mapTables() function. You could also create all the module functions as local functions in the main module (with the main() function at the end).

What not to do [top]

  • Don’t forget to use node:Remove() to delete any fields that you don’t want to to update in the database (ok so we already mentioned it in the usage section, but it is important)

Leave A Comment?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.