Load Email into SQLite from the CLI

Command references used in this guide: nylas email search, nylas email list, and nylas 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.

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

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.

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.

# 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 and Microsoft Graph message resource.

#!/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 — the same pattern into a server database with JSONB
• Load email into MySQL — a JSON column in MySQL instead of SQLite
• Index email in Elasticsearch — full-text search over the same JSON
• Export email to CSV — a flat file instead of a database
• Back up email to JSON — the raw export this pipeline loads
• Extract email data with jq — the JSON-shaping toolkit
• Full command reference — every flag and subcommand documented