Turn CSV into a searchable SQLite table (and a nice ASCII report)

Introduction

You have a CSV dump and you want a repeatable pipeline that:

normalizes and cleans the rows,
inserts them into a SQLite database,
and produces a readable ASCII table for quick inspection.

We'll build a tiny, composable pipeline using aux4 packages:

aux4/adapter to transform CSV into JSON (streaming),
aux4/db-sqlite to insert rows into SQLite (streaming, transactional),
aux4/2table to print a tidy ASCII table from the DB query.

Install the packages

aux4 aux4 pkger install aux4/adapter aux4/db-sqlite aux4/2table

Prepare a CSV and adapter mapping

Create a CSV file called content.csv:

cat > content.csv <<'CSV'
name,age,email
John Doe,28,john@example.com
Jane Smith,34,jane@acme.io
CSV

Create a simple adapter config (config.yaml) that tells aux4/adapter how to parse the CSV and map fields. We call the mapping profile simple:

config:
  simple:
    format: csv
    mapping:
      name: $.name
      age: $.age
      email: $.email

This mapping is minimal: it reads CSV columns and emits JSON objects with name, age, and email.

Create the SQLite table

Create the SQLite database and table. We'll use users.db in the working directory:

aux4 db sqlite execute \
  --database users.db \
  --query "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT)"

This returns a JSON array (empty result for CREATE TABLE), but you can ignore it — the table is created.

Transform CSV, stream JSON, and insert into SQLite

Now stream the CSV through aux4/adapter (streaming NDJSON output), and pipe that into aux4/db-sqlite stream to insert rows. We'll use a transactional insert so either the whole batch commits or nothing does.

cat content.csv \
  | aux4 adapter map --config simple --stream \
  | aux4 db sqlite stream --database users.db \
      --query "INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning id,name,age,email" \
      --inputStream --tx

What happens here:

aux4 adapter map --stream emits one JSON object per line (NDJSON).
aux4 db sqlite stream --inputStream reads that NDJSON stream and executes the INSERT for each row. --tx wraps the whole batch in a transaction.

Sample NDJSON emitted by the adapter:

{"name":"John Doe","age":"28","email":"john@example.com"}
{"name":"Jane Smith","age":"34","email":"jane@acme.io"}

Sample NDJSON produced by the INSERT (one JSON object per inserted row, with assigned id):

{"id":1,"name":"John Doe","age":28,"email":"john@example.com"}
{"id":2,"name":"Jane Smith","age":34,"email":"jane@acme.io"}

If you prefer a single JSON array result instead of NDJSON, you can use aux4 db sqlite execute with --inputStream (it will read a JSON array from stdin), but for streaming pipelines NDJSON with stream is more convenient and memory-light.

Query the table and pretty-print as ASCII

Now query the table and pipe the JSON array to aux4/2table to get a readable ASCII table:

aux4 db sqlite execute \
  --database users.db \
  --query "SELECT id, name, age, email FROM users" \
  | aux4 2table id,name,age,email

Expected output:

┌────┬────────────┬─────┬───────────────────┐
│ id │ name       │ age │ email             │
├────┼────────────┼─────┼───────────────────┤
│ 1  │ John Doe   │ 28  │ john@example.com  │
│ 2  │ Jane Smith │ 34  │ jane@acme.io      │
└────┴────────────┴─────┴───────────────────┘

If you prefer Markdown tables (great for PRs or docs), use --format md:

aux4 db sqlite execute \
  --database users.db \
  --query "SELECT id, name, age, email FROM users" \
  | aux4 2table --format md id,name,age,email

You can also rename columns for better readability:

aux4 db sqlite execute \
  --database users.db \
  --query "SELECT id, name, age, email FROM users" \
  | aux4 2table id:"ID",name:"Name",age:"Age",email:"Email"

Notes & tips

Use --tx with batch inserts when you need all-or-nothing semantics.
Use aux4 adapter map --stream to keep memory usage low on large CSVs.
The adapter mapping can include transformers (trim, uppercase, lowercase) — add them in the config to clean fields before insertion.
You can override values at insert time with named params in the SQL query (:name, :age); that's handy to add default values or tags.
If you want NDJSON output from the DB insert step (useful for piping into other processors) use aux4 db sqlite stream; if you want a JSON array, use aux4 db sqlite execute.

Conclusion

With a few aux4 commands you get a robust, repeatable CSV to SQLite ingestion pipeline — and a quick ASCII summary to inspect results. The workflow composes cleanly: adapter maps and cleans rows, db-sqlite streams inserts (optionally transactional), and 2table formats query output for humans.