apple-mail-search

name	apple-mail-search
description	Fast Apple Mail search via SQLite on macOS. Search emails by subject, sender, date, attachments - results in ~50ms vs 8+ minutes with AppleScript. Use when asked to find, search, or list emails.

Apple Mail Search

Search Apple Mail.app emails instantly via SQLite. ~50ms vs 8+ minutes with AppleScript.

Installation

# Copy mail-search to your PATH
cp scripts/mail-search /usr/local/bin/
chmod +x /usr/local/bin/mail-search

Or use the inline SQL queries with sqlite3 (already installed on macOS).

Quick SQL Search

For immediate use without the script, query the Mail database directly:

# Find the Mail database
MAIL_DB=$(find ~/Library/Mail -name "Envelope Index" -type f 2>/dev/null | head -1)

# Search by subject
sqlite3 -header -column "$MAIL_DB" "
SELECT m.ROWID, s.subject, a.address, datetime(m.date_sent + 978307200, 'unixepoch') as date
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE s.subject LIKE '%your_query%'
ORDER BY m.date_sent DESC
LIMIT 20;
"

# Search by sender
sqlite3 -header -column "$MAIL_DB" "
SELECT m.ROWID, s.subject, a.address, datetime(m.date_sent + 978307200, 'unixepoch') as date
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE a.address LIKE '[e-mail]%'
ORDER BY m.date_sent DESC
LIMIT 20;
"

# List unread emails
sqlite3 -header -column "$MAIL_DB" "
SELECT m.ROWID, s.subject, a.address
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE m.flags & 1 = 0
ORDER BY m.date_sent DESC
LIMIT 20;
"

Usage (with mail-search script)

mail-search subject "invoice"           # Search subjects
mail-search sender "@amazon.com"        # Search by sender email
mail-search from-name "John"            # Search by sender name
mail-search to "[e-mail]"  # Search sent mail
mail-search unread                      # List unread emails
mail-search attachments                 # List emails with attachments
mail-search attachment-type pdf         # Find PDFs
mail-search recent 7                    # Last 7 days
mail-search date-range 2025-01-01 2025-01-31
mail-search open 12345                  # Open email by ID
mail-search stats                       # Database statistics

Options

-n, --limit N    Max results (default: 20)
-j, --json       Output as JSON
-c, --csv        Output as CSV
-q, --quiet      No headers
--db PATH        Override database path

Examples

# Find bank statements from last month
mail-search subject "statement" -n 50

# Get unread emails as JSON for processing
mail-search unread --json | jq '.[] | .subject'

# Find all PDFs from a specific sender
mail-search sender "@bankofamerica.com" -n 100 | grep -i statement

# Export recent emails to CSV
mail-search recent 30 --csv > recent_emails.csv

Why This Exists

Method	Time for 130k emails
AppleScript iteration	8+ minutes
Spotlight/mdfind	Broken since Big Sur
SQLite (this tool)	~50ms

Apple removed the emlx Spotlight importer in macOS Big Sur. This tool queries the Envelope Index SQLite database directly.

Technical Details

Database: ~/Library/Mail/V{9,10,11}/MailData/Envelope Index

Key tables:

• messages - Email metadata (dates, flags, FKs)
• subjects - Subject lines
• addresses - Email addresses and display names
• recipients - TO/CC mappings
• attachments - Attachment filenames

Limitations:

• Read-only (cannot compose/send)
• Metadata only (bodies in .emlx files)
• Mail.app only (not Outlook, etc.)

Finding the Database Path

# Find Mail database automatically
find ~/Library/Mail -name "Envelope Index" -type f 2>/dev/null

# Common paths:
# ~/Library/Mail/V9/MailData/Envelope Index
# ~/Library/Mail/V10/MailData/Envelope Index
# ~/Library/Mail/V11/MailData/Envelope Index

Date Conversion Note

Apple Mail stores dates as Apple epoch (seconds since 2001-01-01). To convert to readable format in SQL:

datetime(m.date_sent + 978307200, 'unixepoch')