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

![banner](https://articles.aux4.blog/data/posts/aux4/turn-csv-into-searchable-sqlite-table/banner.png)

## 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](https://hub.aux4.io/r/public/packages/aux4/adapter) to transform CSV into JSON (streaming),
- [aux4/db-sqlite](https://hub.aux4.io/r/public/packages/aux4/db-sqlite) to insert rows into SQLite (streaming, transactional),
- [aux4/2table](https://hub.aux4.io/r/public/packages/aux4/2table) to print a tidy ASCII table from the DB query.

## Install the packages

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

## Prepare a CSV and adapter mapping

Create a CSV file called `content.csv`:

```bash
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`:

```yaml
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:

```bash
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.

```bash
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:

```json
{"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):

```json
{"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:

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

Expected output:

```text
┌────┬────────────┬─────┬───────────────────┐
│ 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`:

```bash
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:

```bash
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.

## See Also

- [aux4/db-sqlite](https://hub.aux4.io/r/public/packages/aux4/db-sqlite)
- [aux4/adapter](https://hub.aux4.io/r/public/packages/aux4/adapter)
- [aux4/config](https://hub.aux4.io/r/public/packages/aux4/config)
- [aux4/2table](https://hub.aux4.io/r/public/packages/aux4/2table)