Source: https://cli.nylas.com/guides/email-to-sqlite

# Load Email into SQLite from the CLI

SQLite ships on every Mac, Linux box, and phone — it powers an estimated trillion-plus databases in the wild. That makes it the easiest place to keep a queryable copy of your inbox. The Nylas CLI hands you each message as JSON; SQLite stores it in a table with a real JSON column you can query offline with full SQL. This guide builds the pipeline: search email, shape it with jq, and INSERT rows you can index, join, and group without a single API round-trip.

Written by [Aaron de Mello](https://cli.nylas.com/authors/aaron-de-mello) Senior Engineering Manager

Updated June 9, 2026

> **TL;DR:** Pull messages with `nylas email search --json`, shape each one with `jq`, and `INSERT` a row per message into a SQLite table that keeps the full message in a JSON column. The CLI handles the inbox across six providers; SQLite handles the storage. The payoff comes at the end — one indexed JSON column lets you run sender, date, and full-text queries offline that the email API can't.

Command references used in this guide: [`nylas email search`](https://cli.nylas.com/docs/commands/email-search), [`nylas email list`](https://cli.nylas.com/docs/commands/email-list), and [`nylas email read`](https://cli.nylas.com/docs/commands/email-read).

## How do I load email into SQLite from the CLI?

Loading email into SQLite takes two steps: export messages as JSON, then INSERT each one into a table. The `nylas email search` command returns structured messages with `--json`, and the `sqlite3` shell writes them into a local file. One file holds the whole archive.

SQLite needs no server, no port, and no daemon. The entire database is a single file you can copy, back up, or commit. The CLI authenticates once through OAuth and refreshes tokens automatically every 3,600 seconds, so a scheduled export keeps running without re-login. Install the tool in under 60 seconds with Homebrew; other methods live in the [getting started guide](https://cli.nylas.com/guides/getting-started).

```bash
brew install nylas/nylas-cli/nylas

# Export the messages you want to store, as JSON
nylas email search "*" --from "billing@stripe.com" --after 2026-01-01 --json --limit 200 > messages.json
```

## Why store the message in a SQLite JSON column?

A JSON column stores the full message verbatim while still letting you query inside it. SQLite's JSON1 extension — compiled into the official builds since version 3.38.0 (Feb 2022) — exposes operators like `->>` that read a field straight out of stored JSON, so you keep every header without designing 30 columns up front.

The SQLite docs state the [json1 functions](https://www.sqlite.org/json1.html) are “built into SQLite by default” as of 3.38.0. Define a table with a few promoted columns for the fields you filter on most — sender, date, subject — plus a `raw` column holding the complete JSON. Promoted columns get indexes; the raw column keeps the 40-plus fields the API returns so nothing is lost. This hybrid shape avoids the rigid all-columns schema and the lossy subject-only export at the same time.

```bash
sqlite3 mail.db <<'SQL'
CREATE TABLE IF NOT EXISTS messages (
  id       TEXT PRIMARY KEY,
  sender   TEXT,
  subject  TEXT,
  date     INTEGER,
  raw      TEXT  -- full message JSON
);
CREATE INDEX IF NOT EXISTS idx_sender ON messages(sender);
CREATE INDEX IF NOT EXISTS idx_date   ON messages(date);
SQL
```

## How do I shape email JSON and INSERT it?

You shape each message with `jq` and feed the values to an `INSERT` statement. The `jq` manual documents `@json` for safe string escaping, which matters because subjects routinely contain quotes and commas that would break a naive INSERT. Use `INSERT OR REPLACE` so re-running the export updates rows instead of erroring on the primary key.

The CLI returns an array of message objects; each carries an `id`, a `from` array, a `subject`, and a Unix `date`. The loop below promotes those four fields into columns and stores the entire object in `raw`. Processing 200 messages this way finishes in well under a second on a laptop, since SQLite writes are local disk operations, not network calls.

```bash
jq -c '.[]' messages.json | while read -r msg; do
  id=$(echo "$msg"      | jq -r '.id')
  sender=$(echo "$msg"  | jq -r '.from[0].email // ""')
  subject=$(echo "$msg" | jq -r '.subject // "(no subject)"' | sed "s/'/''/g")
  date=$(echo "$msg"    | jq -r '.date // 0')
  raw=$(echo "$msg"     | jq -c '.' | sed "s/'/''/g")
  sqlite3 mail.db "INSERT OR REPLACE INTO messages (id, sender, subject, date, raw)
    VALUES ('$id', '$sender', '$subject', $date, '$raw');"
done
```

## What can I query once email is in SQLite?

Once the messages are stored, you query them with full SQL offline — no rate limits, no pagination tokens. Promoted columns answer the common questions: top senders, daily volume, unread counts. The JSON column answers everything else through the `->>` operator, which extracts any nested field from the stored `raw` text on demand.

A grouped count over an indexed column scans thousands of rows in single-digit milliseconds, where the same question against the email API needs paginated requests in batches of 200. The query below ranks senders by volume; swap in `raw ->> '$.snippet'` to read a field that was never promoted to its own column. This is the payoff the TL;DR promised: one JSON column turns a flat export into a queryable archive.

```bash
# Top senders by message count
sqlite3 -box mail.db "
  SELECT sender, COUNT(*) AS n
  FROM messages
  GROUP BY sender
  ORDER BY n DESC
  LIMIT 10;"

# Read a field that was never promoted, straight from the JSON column
sqlite3 mail.db "
  SELECT subject, raw ->> '$.snippet' AS preview
  FROM messages
  ORDER BY date DESC
  LIMIT 5;"
```

## How do I keep the SQLite database in sync?

Keep the database current by re-running the export on a schedule and relying on the primary key to de-duplicate. Scope the search to `--after` the last run date and use `INSERT OR REPLACE`, so a daily cron only processes new mail while updating any message that changed. A single day of mail is typically under 150 messages, which loads in roughly one second.

Wrap the export and load in one script and trigger it from cron or a systemd timer. SQLite locks the file during a write, so avoid two importers writing at once; a single scheduled job sidesteps that entirely. Back up by copying `mail.db` — the whole archive is one file. The Gmail and Outlook fields the CLI surfaces follow the providers' documented message resources, such as the [Gmail API](https://developers.google.com/workspace/gmail/api) and [Microsoft Graph message resource](https://learn.microsoft.com/en-us/graph/api/resources/message).

```bash
#!/usr/bin/env bash
set -euo pipefail
SINCE=$(date -v-1d +%Y-%m-%d 2>/dev/null || date -d '1 day ago' +%Y-%m-%d)
nylas email search "*" --after "$SINCE" --json --limit 200 > daily.json
jq -c '.[]' daily.json | while read -r msg; do
  id=$(echo "$msg" | jq -r '.id')
  raw=$(echo "$msg" | jq -c '.' | sed "s/'/''/g")
  sqlite3 mail.db "INSERT OR REPLACE INTO messages (id, raw) VALUES ('$id', '$raw');"
done
```

## Next steps

- [Load email into Postgres](https://cli.nylas.com/guides/email-to-postgres) — the same pattern into a server database with JSONB
- [Load email into MySQL](https://cli.nylas.com/guides/email-to-mysql) — a JSON column in MySQL instead of SQLite
- [Index email in Elasticsearch](https://cli.nylas.com/guides/email-to-elasticsearch) — full-text search over the same JSON
- [Export email to CSV](https://cli.nylas.com/guides/email-to-csv-export) — a flat file instead of a database
- [Back up email to JSON](https://cli.nylas.com/guides/backup-emails-to-json) — the raw export this pipeline loads
- [Extract email data with jq](https://cli.nylas.com/guides/extract-email-data-jq) — the JSON-shaping toolkit
- [Full command reference](https://cli.nylas.com/docs/commands) — every flag and subcommand documented