Merging Records

In addition to executing SQL statements directly, Iguana offers another method for writing to databases using a Database Schema File (*.vdb), db.tables{} and the conn:merge{} function.

This method simplifies data insertion and updating by automatically handling the logic for determining whether to insert new rows or update existing ones, depending on the configuration of key columns in the VDB.

If key columns are defined, Iguana performs a SELECT query to check if a row with matching key values exists. If a matching row is found, an UPDATE operation is performed; otherwise, an INSERT is performed.
If no key columns are defined, new rows are always inserted. This is useful when you don’t want to overwrite existing data.

We see a great example of this in the introductory article: Understanding Databases in Iguana.

STEP 1: Create a Database Schema File

A VDB acts as an intermediary table mirroring your database table structure. It is used to define how data is structured and how it maps to actual database columns. SeeCreating a VDB for building this simple Patients table inside report.vdb.

In this case, we have not defined any keys for the database table - see Key Fields and Examples.

STEP 2: Use db.tables to create an empty set of tables to populate

Now we can use the db.tables to generate an empty set of tables based on the provided VDB and map our data to the database tables.

local T = db.tables{vdb='report.vdb, name ='Patients'} 

-- map vdb table values
T.Patients[1].FirstName = 'John' 
T.Patients[1].LastName = 'Doe' 
T.Patients[1].ID = 1

STEP 3: Use conn:merge{} to insert new rows or update existing rows in your database

A call to conn:merge{} allows you to either insert new rows into a database table or update existing rows depending on the keys configured in the VDB.

-- Insert or update rows based on key column configuration 
conn:merge{data=T, live=true}

With some databases, you can also perform a Bulk Insert on Merge.

conn:merge{} does not support spaces in column names. If your database schema contains spaces in column names, you’ll need to modify the column names to remove or replace spaces before using conn:merge{} or see https://help.interfaceware.com/v6/customize-custom_merge-lua-for-table-and-field-names-with-spaces.

Error Handling Merges

The merge process all happens within a single database transaction. If it fails, the transaction is rolled back and nothing is altered in the database. This behavior can be optionally turned off if you pass in transaction=false as an argument.

Just like with conn:execute{}, you can use pcall() to catch and gracefully handle any errors that arise from conn:merge{} operations:

function doMerge(conn, T) 
  local R = conn:merge{data=T, live=true}
end 

function main()
  -- create empty table
  local T = db.tables{vdb='report.vdb, name ='Patients'} 
  
  -- map vdb table values
  T.Patients[1].FirstName = 'John' 
  T.Patients[1].LastName = 'Doe' 
  T.Patients[1].ID = 1
  
  -- call merge function and handle response
  local Success, R = pcall(doMerge, conn, T) 
  if not Success then 
    iguana.log('Error during merge: ', R.message) 
  end
end