# Build a MySQL-to-SQLite Sync Command with Validation ![banner](https://articles.aux4.blog/data/posts/aux4/sync-mysql-to-sqlite-with-validation/banner.png) ## 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 ```bash 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: ```bash 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" ``` ```json [ { "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`: ```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: ```bash ... | aux4 validator validate --configFile config.yaml --config source --rules mapping --onlyValid --ignore ``` ```json [ { "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](https://jsonata.org) or built-in functions like `utc()`. Add a second section to `config.yaml`: ```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: ```bash ... | aux4 adapter map --configFile config.yaml --config record ``` ```json [ { "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: ```bash 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 ``` ```json [ { "customer_id": 1 }, { "customer_id": 2 }, { "customer_id": 3 }, { "customer_id": 5 } ] ``` `INSERT OR REPLACE` makes the sync idempotent. Check the result: ```bash 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 ``` ```text 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`: ```json { "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: ```bash aux4 sync --password secret --target local.db ``` ```json [ { "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. ## See Also - [aux4/db-mysql](https://hub.aux4.io/r/public/packages/aux4/db-mysql) - [aux4/validator](https://hub.aux4.io/r/public/packages/aux4/validator) - [aux4/adapter](https://hub.aux4.io/r/public/packages/aux4/adapter) - [aux4/db-sqlite](https://hub.aux4.io/r/public/packages/aux4/db-sqlite) - [aux4/2table](https://hub.aux4.io/r/public/packages/aux4/2table)