Guide

Load Email into Postgres from the Terminal

When you need to query, join, or analyze email — not just read it — a database beats a mailbox. PostgreSQL handles email well, especially with JSONB for the raw message and typed columns for the fields you filter on. The Nylas CLI gives you each message as JSON; psql loads it. This guide builds an email-to-Postgres loader with an idempotent upsert keyed on the message ID, so re-runs never duplicate rows, across six providers.

Written by Caleb Geene Director, Site Reliability Engineering

Verified — CLI 3.1.16 · Gmail, Outlook · last tested June 8, 2026

Command references used in this guide: nylas email search, nylas email list, and nylas email read.

What schema should you use for email?

Use a hybrid schema: a JSONB column for the full raw message plus typed columns for the few fields you filter and sort on. JSONB keeps every field without forcing a rigid schema, and Postgres can index inside it with a GIN index, per the PostgreSQL JSON documentation. Promote subject, sender, and date to real columns so common queries use a btree index instead of scanning JSON.

Make the message ID the primary key. The CLI's message IDs are stable per message, so a primary key on that column is what makes loading idempotent — a re-run upserts the same row rather than inserting a copy. This one decision is the difference between a loader you can run safely on a schedule and one that doubles your row count every night.

CREATE TABLE IF NOT EXISTS email (
  id        text PRIMARY KEY,
  thread_id text,
  sender    text,
  subject   text,
  received  timestamptz,
  raw       jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS email_received_idx ON email (received DESC);
CREATE INDEX IF NOT EXISTS email_raw_gin ON email USING gin (raw);

How do you load messages with psql?

Load by piping the CLI's JSON into a transform that emits one row per message, then insert with psql. The cleanest path stages the JSON into a temporary table as JSONB, then inserts into the typed table by extracting fields with ->> operators. That keeps the raw message intact while populating the columns you query, in one SQL statement.

Wrap the insert in ON CONFLICT (id) DO UPDATE so a message already present is refreshed rather than rejected. Run it inside a transaction so a malformed batch rolls back cleanly instead of leaving a half-loaded table. The whole loader is a pull, a stage, and an upsert — three steps you can put behind a cron job.

# Pull, stage as JSONB, and upsert into the typed table
nylas email search "newer_than:1d" --json --limit 200 \
  | jq -c '.[]' \
  | psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "
      CREATE TEMP TABLE stage (doc jsonb);
      COPY stage (doc) FROM STDIN;
      INSERT INTO email (id, thread_id, sender, subject, received, raw)
      SELECT doc->>'id', doc->>'thread_id',
             doc#>>'{from,0,email}', doc->>'subject',
             to_timestamp((doc->>'date')::bigint), doc
      FROM stage
      ON CONFLICT (id) DO UPDATE
        SET subject = EXCLUDED.subject, raw = EXCLUDED.raw;
  "

How do you query the loaded email?

Query it like any table: filter on the typed columns for speed, and reach into raw with JSONB operators for fields you didn't promote. Counting messages per sender, finding everything in a date range, or joining email to your application's users becomes plain SQL — the thing a mailbox can't do. A GIN index on raw keeps containment queries fast even on large tables.

This is the payoff over a flat export: a database answers analytical questions a JSON file can't. Top senders this week, average response time, threads with no reply — all are one query away once the data is in Postgres. Keep the loader idempotent and scheduled, and the table stays a current, queryable mirror of the inbox.

-- Top 10 senders in the last 7 days
SELECT sender, count(*) AS n
FROM email
WHERE received > now() - interval '7 days'
GROUP BY sender ORDER BY n DESC LIMIT 10;

Next steps