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 allows you to easily map data to multiple rows in a single Lua table and then perform a bulk insert or update without writing complex and long SQL statements. Data INSERTs and UPDATEs are simplified as it automatically handles 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.
Â