Introduction
A single bad row — an invalid email, a missing name, a negative amount — is enough to blow up an import halfway through and leave your table half-loaded. This post puts a gatekeeper in front of the load: aux4/validator checks every incoming record against a rule set, drops the ones that fail, tells you exactly why, and lets only clean data reach the database.
Install the packages
aux4 aux4 pkger install aux4/validator aux4/db-sqlite
Step 1: some messy input
Real batches are never clean. Here's signups.json — two good records and five each broken a different way:
[
{ "name": "Sally Chen", "email": "sally.chen@example.com", "age": 34, "amount": 49.90, "signup_date": "2026-07-01" },
{ "name": "Marco Ruiz", "email": "not-an-email", "age": 41, "amount": 19.90, "signup_date": "2026-07-03" },
{ "name": "Priya Nair", "age": 29, "amount": 99.00, "signup_date": "2026-07-05" },
{ "name": "Chen Wei", "email": "chen.wei@example.com", "age": 52, "amount": -12.50, "signup_date": "2026-07-06" },
{ "name": "Aisha Bello", "email": "aisha.bello@example.com", "age": 38, "amount": 29.90, "signup_date": "2026-13-40" },
{ "name": "Kenji Tanaka", "email": "kenji.tanaka@example.com", "age": 45, "amount": 149.00, "signup_date": "2026-07-09" },
{ "name": "Lucia Moretti", "email": "lucia.moretti@example.com", "age": 16, "amount": 9.90, "signup_date": "2026-07-10" }
]
Marco's email is invalid, Priya has no email at all, Chen's amount is negative, Aisha's date is impossible, and Lucia is under 18.
Step 2: write the rules
Rules live in a config.yaml. Each field has a path (where to read it) and a rule (how to check it):
config:
data:
signup:
name:
path: $.name
rule: required
email:
path: $.email
rule: required|email
age:
path: $.age
rule: required|integer|min:18
amount:
path: $.amount
rule: required|numeric|min:0
signup_date:
path: $.signup_date
rule: required|date
Step 3: keep only the clean rows
Pipe the input through the validator. --onlyValid keeps the records that pass; --ignore keeps the exit code at 0 so the pipeline flows on:
cat signups.json | aux4 validator validate --configFile config.yaml --config data --rules signup --onlyValid --ignore
[
{ "name": "Sally Chen", "email": "sally.chen@example.com", "age": 34, "amount": 49.9, "signup_date": "2026-07-01" },
{ "name": "Kenji Tanaka", "email": "kenji.tanaka@example.com", "age": 45, "amount": 149, "signup_date": "2026-07-09" }
]
Five bad records gone, two clean ones through. (The validator emits only the fields that have rules — so it doubles as a whitelist, quietly stripping any unexpected extra fields.)
Step 4: see exactly what failed
Swap --onlyValid for --onlyInvalid and the validator hands back the rejected records with the reason — perfect for a report or an alert:
cat signups.json | aux4 validator validate --configFile config.yaml --config data --rules signup --onlyInvalid --ignore
[
{ "item": { "name": "Marco Ruiz", "email": "not-an-email", "age": 41, "amount": 19.9, "signup_date": "2026-07-03" },
"errors": { "email": ["The email format is invalid."] } },
{ "item": { "name": "Priya Nair", "age": 29, "amount": 99, "signup_date": "2026-07-05" },
"errors": { "email": ["The email field is required."] } },
{ "item": { "name": "Chen Wei", "email": "chen.wei@example.com", "age": 52, "amount": -12.5, "signup_date": "2026-07-06" },
"errors": { "amount": ["The amount must be at least 0."] } },
{ "item": { "name": "Aisha Bello", "email": "aisha.bello@example.com", "age": 38, "amount": 29.9, "signup_date": "2026-13-40" },
"errors": { "signup_date": ["The signup date is not a valid date format."] } },
{ "item": { "name": "Lucia Moretti", "email": "lucia.moretti@example.com", "age": 16, "amount": 9.9, "signup_date": "2026-07-10" },
"errors": { "age": ["The age must be at least 18."] } }
]
Every rejection names the field and the rule it broke.
Step 5: load only the clean data
Now put the gatekeeper in front of the database. Create the table, then pipe input → validator → SQLite. Only records that survive validation are ever inserted:
aux4 db sqlite execute --database signups.db \
--query "CREATE TABLE IF NOT EXISTS signups (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER, amount REAL, signup_date TEXT)"
cat signups.json \
| aux4 validator validate --configFile config.yaml --config data --rules signup --onlyValid --ignore \
| aux4 db sqlite execute --database signups.db \
--query "INSERT INTO signups (name, email, age, amount, signup_date)
VALUES (:name, :email, :age, :amount, :signup_date) returning *" --inputStream
[
{ "id": 1, "name": "Sally Chen", "email": "sally.chen@example.com", "age": 34, "amount": 49.9, "signup_date": "2026-07-01" },
{ "id": 2, "name": "Kenji Tanaka", "email": "kenji.tanaka@example.com", "age": 45, "amount": 149, "signup_date": "2026-07-09" }
]
The :field placeholders bind straight from each validated record — no loop, no glue. Two rows in the table, and not one bad record made it through.
Choosing the contract: filter or fail
The --ignore flag is the switch between two behaviors:
- With
--ignore— exit0no matter how many records fail. "Filter out the bad ones and keep going." (What we used above.) - Without
--ignore— if any record is invalid, exit non-zero (40). "Fail the whole job if the batch is dirty."
Drop --ignore when a dirty batch should stop the pipeline and page someone; keep it when you'd rather quietly load what's good.
Conclusion
aux4/validator is a gatekeeper you drop into a pipe: it checks every record against your rules, keeps only the clean ones, and reports precisely what failed — so bad data is caught at the door instead of halfway through a load. Put it in front of any aux4/db-sqlite (or repository, or API) write, and your destination only ever sees data you trust.