Introduction
Copying a slice of a production MySQL table into a local SQLite file — cleaned up and reshaped to a different schema — is a chore you want to script once and rerun forever. This post builds that pipeline from four aux4 packages: validate and normalize the rows, reshape them into the target table with computed columns, and load them into SQLite. Then it bottles everything into a single command, aux4 sync.
Install the packages
aux4 aux4 pkger install aux4/db-mysql aux4/validator aux4/adapter aux4/db-sqlite aux4/2table
Step 1: query MySQL
aux4 db mysql execute runs a query and returns the rows as a JSON array:
aux4 db mysql execute --host 127.0.0.1 --port 3306 --user root --password secret --database shop \
--query "SELECT id, first_name, last_name, email_address, country_code, signup_date FROM customers"
[
{
"id": 1,
"first_name": "Ada",
"last_name": "Lovelace",
"email_address": "ada@example.com",
"country_code": "GB",
"signup_date": "2024-01-15T08:00:00.000Z"
},
{
"id": 4,
"first_name": "Katherine",
"last_name": "Johnson",
"email_address": "not-an-email",
"country_code": "US",
"signup_date": "2024-03-01T08:00:00.000Z"
}
]
Snake_case columns and an invalid email on row 4 — raw source data you don't want to copy as-is.
Step 2: validate and normalize in one pass
aux4 validator validate checks each record against a rule set. The trick: each rule has a path (where to read the value) and a rule (how to check it), so the same block both renames fields and validates them. Define it in config.yaml:
config:
source:
mapping:
id:
path: $.id
rule: required|integer
firstName:
path: $.first_name
rule: required
lastName:
path: $.last_name
rule: required
email:
path: $.email_address
rule: required|email
country:
path: $.country_code
rule: required
joined:
path: $.signup_date
rule: required
The validator emits one field per rule, keyed by the rule's name and read from its path — so first_name comes out as firstName. Pipe the query through it; --onlyValid drops the rows that fail:
... | aux4 validator validate --configFile config.yaml --config source --rules mapping --onlyValid --ignore
[
{
"id": 1,
"firstName": "Ada",
"lastName": "Lovelace",
"email": "ada@example.com",
"country": "GB",
"joined": "2024-01-15T08:00:00.000Z"
},
{
"id": 5,
"firstName": "Linus",
"lastName": "Torvalds",
"email": "linus@kernel.org",
"country": "FI",
"joined": "2024-03-12T07:00:00.000Z"
}
]
Row 4 (not-an-email) is gone, and the columns are now a clean camelCase model. --ignore keeps the exit code at 0 so the pipeline flows on.
Step 3: reshape into the SQLite schema
The destination table has its own column names and two timestamp columns the source doesn't carry. aux4 adapter map builds that shape — and its expr directive computes fields with JSONata or built-in functions like utc(). Add a second section to config.yaml:
record:
format: json
mapping:
customer_id: $.id
full_name:
expr: "firstName & ' ' & lastName"
email: $.email
region: $.country
created_at:
expr: "utc()"
updated_at:
expr: "utc()"
full_name joins two fields, and created_at/updated_at get the current UTC timestamp. Pipe the validated rows through it:
... | aux4 adapter map --configFile config.yaml --config record
[
{
"customer_id": 1,
"full_name": "Ada Lovelace",
"email": "ada@example.com",
"region": "GB",
"created_at": "2026-02-11T09:30:12Z",
"updated_at": "2026-02-11T09:30:12Z"
}
]
Besides utc(), expr also supports now() (local-offset timestamp), time() (Unix seconds), and uuid() (a sortable v7 UUID) — handy for surrogate keys.
Step 4: load into SQLite
Create the destination table, then run the whole pipeline into it. aux4 db sqlite fills :name placeholders from each record and --inputStream reads them from stdin:
aux4 db sqlite execute --database local.db \
--query "CREATE TABLE IF NOT EXISTS customers (customer_id INTEGER PRIMARY KEY, full_name TEXT, email TEXT, region TEXT, created_at TEXT, updated_at TEXT)"
aux4 db mysql execute --host 127.0.0.1 --port 3306 --user root --password secret --database shop \
--query "SELECT id, first_name, last_name, email_address, country_code, signup_date FROM customers" \
| aux4 validator validate --configFile config.yaml --config source --rules mapping --onlyValid --ignore \
| aux4 adapter map --configFile config.yaml --config record \
| aux4 db sqlite execute --database local.db \
--query "INSERT OR REPLACE INTO customers (customer_id, full_name, email, region, created_at, updated_at) VALUES (:customer_id, :full_name, :email, :region, :created_at, :updated_at) returning customer_id" \
--inputStream --tx
[
{ "customer_id": 1 },
{ "customer_id": 2 },
{ "customer_id": 3 },
{ "customer_id": 5 }
]
INSERT OR REPLACE makes the sync idempotent. Check the result:
aux4 db sqlite execute --database local.db --query "SELECT customer_id, full_name, region, created_at FROM customers" \
| aux4 2table customer_id,full_name,region,created_at
customer_id full_name region created_at
1 Ada Lovelace GB 2026-02-11T09:30:24Z
2 Alan Turing GB 2026-02-11T09:30:24Z
3 Grace Hopper US 2026-02-11T09:30:24Z
5 Linus Torvalds FI 2026-02-11T09:30:24Z
Step 5: bottle it into aux4 sync
Wrap the whole flow in an .aux4 file so it becomes one command with the connection details as variables. Create .aux4:
{
"profiles": [
{
"name": "main",
"commands": [
{
"name": "sync",
"execute": [
"nout:aux4 db sqlite execute --database ${target} --query \"CREATE TABLE IF NOT EXISTS customers (customer_id INTEGER PRIMARY KEY, full_name TEXT, email TEXT, region TEXT, created_at TEXT, updated_at TEXT)\"",
"aux4 db mysql execute --host ${host} --port ${port} --user ${user} --password ${password} --database ${database} --query \"${query}\" | aux4 validator validate --configFile ${configFile} --config source --rules mapping --onlyValid --ignore | aux4 adapter map --configFile ${configFile} --config record | aux4 db sqlite execute --database ${target} --query \"INSERT OR REPLACE INTO customers (customer_id, full_name, email, region, created_at, updated_at) VALUES (:customer_id, :full_name, :email, :region, :created_at, :updated_at) returning customer_id\" --inputStream --tx"
],
"help": {
"text": "Sync customers from MySQL into a local SQLite table",
"variables": [
{ "name": "host", "text": "MySQL host", "default": "127.0.0.1" },
{ "name": "port", "text": "MySQL port", "default": "3306" },
{ "name": "user", "text": "MySQL user", "default": "root" },
{ "name": "password", "text": "MySQL password", "default": "" },
{ "name": "database", "text": "MySQL database", "default": "shop" },
{ "name": "query", "text": "Source query", "default": "SELECT id, first_name, last_name, email_address, country_code, signup_date FROM customers" },
{ "name": "configFile", "text": "Mapping + rules config", "default": "config.yaml" },
{ "name": "target", "text": "Target SQLite database", "default": "local.db" }
]
}
}
]
}
]
}
aux4 picks up the .aux4 in your working directory, so the command is ready:
aux4 sync --password secret --target local.db
[
{ "customer_id": 1 },
{ "customer_id": 2 },
{ "customer_id": 3 },
{ "customer_id": 5 }
]
Every parameter has a default you can override — point it at another host, a different query, or a new target file. Install it as a package to run aux4 sync from anywhere.
Conclusion
You built aux4 sync out of four packages: db-mysql reads the source, validator validates and renames the rows in one pass, adapter reshapes them into the destination schema with computed columns and timestamps, and db-sqlite loads the result. The rules, mapping, and wiring live in config.yaml and one .aux4 file — the whole cross-database sync is now a single idempotent command.