The DataTongs reference for MariaDB — source connection, the Tables array, ShouldCast flags, INSERT ... ON DUPLICATE KEY UPDATE scripts, and ConfigureDataDelivery, which wires reference data into your schema package.
By the SchemaSmith Team · Last reviewed
Lookup tables, seed data, the whole library of reference rows every environment needs — they're a schema-management problem as much as the tables themselves.
DataTongs grips reference data from a live database and produces synchronization scripts that bring it into any target. Point it at a source, list the tables you care about, and it produces one SQL file per table — ready to drop into a schema package's Table Data folder. By default each script reads its rows through a {{<table>.tabledata}} content token that SchemaQuench resolves from the template at deploy; with ShouldCast:TokenizeScripts set to false the rows are embedded instead, and the script runs directly against any compatible instance.
MariaDB has no MERGE statement, so each generated DataTongs script takes a different route. The script stores the rows in a @json_data session variable, and an INSERT ... SELECT reads them back with JSON_TABLE. Sources on MariaDB 10.2 through 10.5 have no JSON_TABLE, so their scripts read the rows with a recursive CTE instead. The statement depends on the configured behavior: INSERT ... ON DUPLICATE KEY UPDATE for insert and update, or INSERT IGNORE for seed only. When delete sync is on, a DELETE ... WHERE NOT EXISTS follows either one. No staging tables, no LOAD DATA, and no external files are read at run time. The configuration shape and the workflow are identical on every supported platform.
Downloads — Get the latest release from the downloads page.
From the directory containing your DataTongs.settings.json:
datatongs
With a custom config file:
datatongs --ConfigFile:/etc/schemasmith/extract-reference-data.json
With a full MariaDB connection string override:
datatongs --ConnectionString:"Server=myserver;Database=AppMain;Uid=appuser"
DataTongs connects to the source database, reads every table listed in the Tables array, serializes each row to JSON in deterministic key order, and writes a sync script plus a sibling .tabledata file for each table. Progress is logged to DataTongs - Progress.log, and errors go to DataTongs - Errors.log, both in the folder that holds the DataTongs executable, unless --LogPath names another folder. See the Log file location section of the Logging page.
A table that cannot be extracted does not stop the run: DataTongs logs it and moves on to the next table. The exit code then tells a pipeline whether every table made it:
| Code | Meaning |
|---|---|
0 | Every configured table was extracted. |
1 | Finished, but something was not fully produced: a table was skipped (it does not exist in the source, has no key columns, or its KeyColumns are malformed); or a script's data token could not be wired. |
2 | At least one table failed to extract. |
Each skipped or failed table is named, with the reason, in the progress log and in DataTongs - Errors.log. The run's summary counts them (Skipped:, Failed:, and Incomplete: for the columns, scripts and tokens that were not fully produced), and its last line before DataTongs completed. names the exit code and why. The full table of codes is in Logging.
DataTongs reads configuration from multiple sources, merged in precedence order (highest priority last):
DataTongs.settings.json in the working directory (or the file specified by --ConfigFile)SmithySettings_ prefixFor the full explanation of configuration loading, environment variable mapping, and CLI switches, see Configuration.
{
"Source": {
"Server": "",
"Port": "3306",
"User": "",
"Password": "",
"Database": "",
"Platform": "MariaDb",
"ConnectionProperties": {}
},
"ContentPath": "",
"ScriptPath": "",
"TemplatePath": "", // blank: discovered from ContentPath
"Tables": [
{ "Name": "Country", "KeyColumns": "CountryCode" },
{ "Name": "Currency", "KeyColumns": "CurrencyCode", "Filter": "IsActive = 1" }
],
"ShouldCast": {
"OutputScripts": true,
"OutputContentFiles": true,
"DisableTriggers": false,
"MergeUpdate": true,
"MergeDelete": false,
"ConfigureDataDelivery": false,
"TokenizeScripts": true,
"DeliveryEncoding": "Json"
}
}
| Key | Type | Default | Description |
|---|---|---|---|
Source:Platform | string | (required, or its Target:Platform twin) | Set to MariaDb to select the MariaDB script generator. |
Source:Server | string | (required, or its Target:Server twin) | Database server hostname or IP. |
Source:Port | string | 3306 | TCP port. |
Source:User | string | (empty) | Login username. |
Source:Password | string | (empty) | Login password. |
Source:Database | string | (required) | Source database (schema) to extract data from. |
Source:ConnectionProperties | object | {} | Arbitrary key-value pairs appended to the connection string. Platform-specific keys — see Configuration. |
Each connection key falls back to its Target: twin when the Source: key is absent — Platform, Server, Port, User, Password, and ConnectionProperties — so one settings file can point SchemaQuench and DataTongs at the same server.
The --ConnectionString switch bypasses all Source settings and passes the provided value directly to the platform-appropriate driver. Source:Platform (or its Target:Platform twin) is still required so the right script generator claims the connection.
A source below the supported engine floor is refused before anything is extracted, naming the detected version.
| Key | Type | Default | Description |
|---|---|---|---|
ScriptPath | string | "." | Directory where the generated Populate <schema>.<table>.sql merge scripts are written. Created automatically if it doesn't exist. |
ContentPath | string | "." | Directory where the sibling .tabledata files (raw JSON row data) are written. When --ConfigureDataDelivery is on, this path is also used to locate the enclosing template — DataTongs walks upward from ContentPath looking for a Template.json. |
TemplatePath | string | (discovered) | The template directory the generated files belong to — where --ConfigureDataDelivery finds the table JSON to update. Blank, DataTongs walks upward from ContentPath to the nearest directory holding a Template.json; the --TemplatePath:<path> switch overrides both. See Template root discovery. |
The typical placement inside a schema package is ScriptPath pointing at Templates/<Name>/Table Data and ContentPath pointing at Templates/<Name>/data. That layout makes the script files visible to SchemaQuench's TableData quench slot and the .tabledata files reachable from each table's DataDelivery block.
| Field | Type | Default | Description |
|---|---|---|---|
Name | string | (required) | Table name. If no schema prefix is given, the connection database is assumed. MariaDB's default schema is the Database on the connection; cross-schema extraction uses schema.table. |
KeyColumns | string | (auto-detected) | Comma-separated column names for the row-matching key. When blank, auto-detected from the table's primary key. On MariaDB only the primary key is detected. Prefix a column with * for NULL-safe comparison on nullable keys. |
SelectColumns | string | (all columns) | Comma-separated column names to extract. When blank, every non-excluded column is extracted. Use to narrow extraction to a specific subset. |
Filter | string | (empty) | SQL WHERE clause (without the WHERE keyword) to filter which rows are extracted. |
MergeType | string | (derived) | Per-table override for the DataDelivery:MergeType default. Values: None, Insert, Insert/Update, Insert/Update/Delete. When blank, derived from global ShouldCast:MergeUpdate + ShouldCast:MergeDelete. |
See Table configuration below for key-column auto-detection, nullable-key handling, and filter semantics. The Tables array itself can't be configured via environment variables — table definitions must live in the JSON configuration file.
Specify tables by name. Every table is read from the connection's Database:
{ "Name": "Country" }
{ "Name": "Products" }
A prefix is ignored: config.feature_flags is read as feature_flags from that database.
DataTongs validates that each table exists in the source database before attempting extraction. Tables that don't exist are skipped with an error message.
Key columns define the row-matching predicate — the unique-key match that drives MariaDB's ON DUPLICATE KEY UPDATE behavior.
Auto-detection. When KeyColumns is blank, DataTongs queries the table's indexes and selects the primary key if one exists. On MariaDB only the primary key is detected. This handles the vast majority of tables without any manual configuration.
Manual override. When you specify KeyColumns, DataTongs uses your list instead of auto-detection. Separate multiple columns with commas:
{ "Name": "OrderLine", "KeyColumns": "OrderID, LineNumber" }
Nullable key columns. If a key column allows NULLs, prefix it with *. This generates NULL-safe matching for the change-detection predicate:
{ "Name": "Mapping", "KeyColumns": "SourceCode, *TargetCode" }
On MariaDB, INSERT ... ON DUPLICATE KEY UPDATE itself matches on the table's declared unique keys, so a column prefixed with * must still participate in a unique index for the merge to work as intended.
No key available. If a table has no key DataTongs can detect (see Auto-detection above) and no KeyColumns configured, DataTongs skips the table with an error message.
The Filter field accepts a SQL WHERE clause (without the WHERE keyword). It controls which rows are extracted from the source database.
{ "Name": "FeatureFlags", "KeyColumns": "FlagName", "Filter": "IsActive = 1" }
This extracts only active flags. The same filter is applied to the delete step when MergeDelete is enabled, so rows outside the filter are never removed (see ShouldCast flags — MergeDelete).
Control how DataTongs generates merge scripts and whether it wires the results into a schema package.
| Flag | Default | Effect |
|---|---|---|
OutputScripts | true | Generate merge scripts. When false, only .tabledata files are written. Also skipped, per table, when --ConfigureDataDelivery actually configured delivery for that table — see Delivery takes precedence over merge scripts. |
DeliveryEncoding | Json | Xml extracts each table's content in the XML delivery encoding (the shape a SQL Server database below compatibility level 130 shreds natively; SchemaQuench also converts a Json delivery there at deploy time), on any source engine, and stamps "ContentEncoding": "Xml" on the configured DataDelivery. Also settable per run with --DeliveryEncoding=Xml. See Content encoding. On MariaDB, a tokenized merge script cannot carry Xml: it reads its .tabledata file with JSON_TABLE when it is deployed, so DataTongs refuses to write one. When every table gets a script (OutputScripts on, ConfigureDataDelivery off), the run is refused before anything is extracted, with a message naming both settings, and exits 2. With --ConfigureDataDelivery only the tables whose delivery could not be configured need a script: each of those fails with that message after its content file is written, and the run exits 2. Delivering through DataDelivery works, and so does setting TokenizeScripts to false. |
OutputContentFiles | true | Write raw data to sibling .tabledata content files. Set to false to skip them — which transitively skips ConfigureDataDelivery, since the configurator needs a content-file path to record. |
DisableTriggers | false | MariaDB cannot disable a trigger, so DisableTriggers has no effect here, and the table's triggers fire during the delivery. When delivery is configured with --ConfigureDataDelivery, DataTongs still records MergeDisableTriggers on the delivery, but that setting has no effect on MariaDB either. |
MergeUpdate | true | Includes the update clause. When true, rows are emitted as INSERT ... ON DUPLICATE KEY UPDATE. When false, seed-only behavior emits INSERT IGNORE and existing rows are left untouched regardless of whether their data differs. |
MergeDelete | false | Opt-in, because this branch deletes data: when enabled, the generated script appends a DELETE WHERE NOT EXISTS step that removes target rows not present in the source data. When both MergeUpdate and MergeDelete are on, full-sync emits INSERT ... ON DUPLICATE KEY UPDATE followed by the delete step. When Filter is configured, the delete step respects the filter so rows outside the filter are never removed. |
ConfigureDataDelivery | false | After extraction, writes a DataDelivery block into each matching table's JSON file inside the enclosing schema package. See --ConfigureDataDelivery. |
TokenizeScripts | true | Embeds each table's extracted row data in the merge script as a {{<table>.tabledata}} content token instead of inline JSON/XML text, matching SchemaTongs' tokenization behavior. DataTongs wires the matching ScriptTokens entry automatically. Set to false to inline the literal row data in generated scripts instead. |
These settings compose into three practical patterns:
| Scenario | MergeUpdate | MergeDelete | MariaDB output |
|---|---|---|---|
| Full sync | true | true | INSERT ... ON DUPLICATE KEY UPDATE followed by DELETE WHERE NOT EXISTS. Target matches source exactly. |
| Add and update, no deletes | true | false | INSERT ... ON DUPLICATE KEY UPDATE. Insert missing, update changed, leave extra rows alone. |
| Seed only | false | false | INSERT IGNORE. Insert missing rows only. Existing rows untouched, nothing deleted. |
MariaDB has no MERGE statement. The rows go into a @json_data session variable, and an INSERT ... SELECT reads them back with JSON_TABLE. MergeUpdate and MergeDelete pick the statement: INSERT ... ON DUPLICATE KEY UPDATE for insert + update, INSERT IGNORE for seed-only, and either one followed by DELETE ... WHERE NOT EXISTS when delete sync is on — no staging tables and no bulk-load dependencies. The samples below are the inline form (TokenizeScripts: false, JSON encoding), with the rows embedded in the script; that form runs directly against any compatible database. By default the rows are read through the table's {{<table>.tabledata}} content token, which SchemaQuench resolves from the template's ScriptTokens at deploy. Tables are named without their database, so a script writes into the database it is deployed to, not the one the data came from.
A script from a MariaDB 10.2–10.5 source reads the rows with a recursive CTE in place of JSON_TABLE, and so does one from a MariaDB patch whose JSON_TABLE misreads strings. When the rows are inlined, a large table from such a source is delivered in chunks. See Engine version compatibility.
SET @ss_saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+00:00';
SET @json_data = '[{"shipper_id":1,"company_name":"Speedy Express","phone":"(503) 555-9831"},
{"shipper_id":2,"company_name":"United Package","phone":"(503) 555-3199"}]';
INSERT INTO `shippers` (`shipper_id`, `company_name`, `phone`)
SELECT `shipper_id`, `company_name`, `phone`
FROM JSON_TABLE(
@json_data,
'$[*]' COLUMNS (
`shipper_id` INT PATH '$.shipper_id',
`company_name` VARCHAR(40) CHARACTER SET utf8mb4 PATH '$.company_name',
`phone` VARCHAR(24) CHARACTER SET utf8mb4 PATH '$.phone'
)
) AS jt
WHERE NOT EXISTS (SELECT 1 FROM `shippers` _ss_live
WHERE <every column of _ss_live equals jt>)
ON DUPLICATE KEY UPDATE
`company_name` = VALUES(`company_name`),
`phone` = VALUES(`phone`);
SET SESSION time_zone = @ss_saved_time_zone;
The ON DUPLICATE KEY UPDATE clause runs whenever an incoming row would collide with an existing unique-key value, and VALUES() carries the incoming row's value into each assignment. The key columns appear in the INSERT list but not in the UPDATE clause, so their values are set on insert and never modified on collision. The script runs in the +00:00 time zone it was extracted in, so TIMESTAMP values arrive unchanged, and puts the session's zone back afterwards.
A row already present exactly as delivered is left out of the upsert altogether. That matters for triggers: INSERT ... ON DUPLICATE KEY UPDATE fires a row's AFTER UPDATE trigger even when it changes nothing (on MariaDB from 10.11.17 and 11.4.11), so without this every redeploy of unchanged data would fire it for every row. Text is compared byte for byte, so a change that only a case-insensitive collation would call equal is still applied. One exception: a table in a foreign-key cycle with another table is delivered through DataDelivery in two passes, which really do update each row twice, so its triggers fire on every delivery. A table that only references itself is not affected.
INSERT IGNORE INTO `shippers` (`shipper_id`, `company_name`, `phone`)
SELECT `shipper_id`, `company_name`, `phone`
FROM JSON_TABLE(@json_data, '$[*]' COLUMNS (<the same columns>)) AS jt;
When MergeUpdate is false, DataTongs emits INSERT IGNORE. Rows that would collide with an existing unique key are silently skipped; only missing rows are inserted. This is the seed-only mode — existing rows are untouched and nothing is deleted, which suits seed data you do not want overwriting local customizations.
When MergeDelete is on, DataTongs appends a delete step after the insert to remove any target rows not present in the source data:
DELETE Target FROM `shippers` Target
WHERE NOT EXISTS (
SELECT 1 FROM JSON_TABLE(@json_data, '$[*]' COLUMNS (<the same columns>)) AS jt
WHERE Target.`shipper_id` = jt.`shipper_id`
);
When a Filter is configured, the delete step is scoped to rows matching the filter, so rows outside the filter are never removed.
Auto-increment columns are handled at the INSERT level — explicit values from the source are carried into the insert so existing auto-increment sequences are preserved. Generated (virtual or stored) columns are auto-excluded from extraction because they cannot be assigned directly.
Each table's delivery is sent to the server in one packet, so delivering a large table can exceed MariaDB's max_allowed_packet and fail:
Error submitting <N>MB packet; ensure 'max_allowed_packet' is greater than <N>MB
Raise the server's max_allowed_packet to accommodate your largest table's row set — for example --max-allowed-packet=64M on the server command line, or the equivalent setting in my.cnf. A MariaDB 10.2–10.5 target, or a patch whose JSON_TABLE misreads strings, takes inlined rows in chunks of 50, but the chunks travel to the server in the same script, so the same limit applies.
DataTongs detects column types and applies the correct extraction and restoration strategy for each. On MariaDB:
INSERT level so explicit values are carried and existing sequences are preserved.VEC_ToText) keeps six significant digits, which would change a stored float32; base64 keeps it exact.FROM_BASE64.When a column is excluded from extraction, DataTongs logs a warning naming the table, the column, and the underlying type. If you need data from these columns, handle them with custom scripts outside of DataTongs.
DataTongs can do more than write scripts — it can wire the extracted data straight into your schema package's declarative delivery pipeline. Pass --ConfigureDataDelivery and DataTongs writes a DataDelivery block into each matching table's JSON file after extracting the data.
datatongs --ConfigureDataDelivery
datatongs --ConfigureDataDelivery --TemplatePath:./Templates/Main
The switch can also be enabled via configuration:
{
"ShouldCast": { "ConfigureDataDelivery": true }
}
For every table that produces output, DataTongs finds the table's JSON file in the template's Tables/ folder — <table>.json first, then that name matched case-insensitively — and writes (or overwrites) a DataDelivery block with:
| Property | Value |
|---|---|
ContentFile | Path to the written .tabledata file, relative to the template root. |
MergeType | Either the per-table MergeType from config, or a default derived from ShouldCast:MergeUpdate + ShouldCast:MergeDelete (see below). |
MatchColumns | The per-table KeyColumns, if one was specified. Otherwise MatchColumns is left out, and SchemaQuench detects the key on the target at deploy, with the same logic that drives the generated script (see Table configuration). |
MergeFilter | The per-table Filter, if one was specified. |
MergeDisableTriggers | Mirrors ShouldCast:DisableTriggers. |
ContentEncoding | Xml when extracting with --DeliveryEncoding=Xml (omitted for the default JSON). |
TimeZone | +00:00: DataTongs extracts TIMESTAMP values in that zone, and delivery reads them back in it, so they arrive unchanged whatever zone either server runs in. Generated merge scripts set the same zone around their data and restore the session's zone after. |
Tables whose JSON file doesn't exist in the template are skipped with a warning. No file is created from scratch — this tool configures existing tables, it doesn't scaffold new ones (use SchemaTongs for that).
Authored arrays of gated deliveries are left alone. If a table's DataDelivery is already an array of two or more deliveries — hand-authored gating or environment variants, see Multiple deliveries — --ConfigureDataDelivery does not touch it. There's no single-delivery identity to reconcile extraction output against, so the array survives untouched and a warning is logged noting it was left as-is. Configure the single-object form first, then hand-edit it into a gated array once you need per-target variants or additive slices.
When a table's config doesn't set MergeType explicitly, the default is derived from the global ShouldCast flags:
| MergeUpdate | MergeDelete | Default MergeType |
|---|---|---|
true | true | Insert/Update/Delete |
true | false | Insert/Update |
false | true | Insert/Update/Delete |
false | false | Insert |
MergeDelete is not subordinate to MergeUpdate. This table used to read “false | any | Insert”, which was wrong in the destructive direction: setting MergeUpdate=false to be conservative does not suppress the delete branch. Turn deletes off with MergeDelete, or set the table's MergeType explicitly — a per-table MergeType always wins over the derived default.
A per-table MergeType on the Tables[] entry always wins over the derived default.
--ConfigureDataDelivery and ShouldCast:OutputScripts both default to producing output, so without this rule a table would get delivered twice — once through the DataDelivery block, once through a generated merge script carrying the same rows. When delivery is actually configured for a table this run, DataTongs does not also emit that table's merge script.
The check is per table, not a global switch: --ConfigureDataDelivery can decline for an individual table (its Tables/<name>.json isn't found, or its DataDelivery is an authored array with no matching VariantName), and a table delivery never covered still gets its script normally. A table whose delivery genuinely was configured — freshly written or already up to date — is the only one that loses its script.
DataTongs logs this once at startup, not per table (the per-table Updated data delivery config for <table> message already covers the detail):
true explicitly alongside --ConfigureDataDelivery, that is contradictory configuration — DataTongs still applies the precedence rule (delivery wins) but logs a warning instead of an info line, since the explicit setting is being overridden.--ConfigureDataDelivery needs to know where your template lives. It uses this precedence:
--TemplatePath:<path> CLI switch.TemplatePath config key.ContentPath looking for the nearest directory containing a Template.json.If nothing is found, DataTongs logs a warning and skips the configuration step — extraction still completes normally. See Data Delivery for the full property reference and the schema validation that catches misconfigurations at authoring time.
DataTongs extracts data using a deterministic, JSON-shaped query. On MariaDB, that query uses JSON_OBJECT aggregation. The principles that apply:
Nobody wants a script that updates every row just because it can. On MariaDB, the generated INSERT ... ON DUPLICATE KEY UPDATE carries a changed-rows filter: a row already present exactly as delivered is left out of the upsert, so only rows that differ reach the engine. The comparison is NULL-safe (<=>), and compares text byte for byte, JSON by value and a vector by its bytes.
Running the generated script twice against unchanged data therefore leaves existing rows alone the second time — the net effect is zero row changes. Diffs are clean. Reviews are honest.
Free schema-as-code for SQL Server, PostgreSQL, MySQL, and MariaDB.
The source is on GitHub for anyone to read.
Get Started on GitHub