sms.db: iMessage and SMS Database on iOS
Where iOS stores iMessage, SMS and MMS in sms.db, which backup reaches it, how to decode attributedBody and nanosecond Cocoa time, and edits and deletes.
- Location
- /private/var/mobile/Library/SMS/sms.db (backup: HomeDomain, Library/SMS/sms.db, fileID 3d0d7e5fb2ce288813306e4d4636395e047a3d28)
- Evidence area
- Proves
- Who exchanged which messages and attachments with the device owner, over which service (iMessage, SMS, and RCS on iOS 18+), and when they were sent, delivered and read
- Timestamps
- Cocoa time in nanoseconds (divide by 1,000,000,000, then add 978307200 for Unix time), UTC
- Acquisition
- Unencrypted backup is enough (HomeDomain). A full file system extraction adds the -wal file, freelist pages and the Attachments tree without backup filtering
- Retention
- Until deleted by the user or by the Keep Messages setting (30 days, 1 year or forever). Since iOS 16, deleted messages sit in Recently Deleted for about 30 days
- Collection
- idevicebackup2, iMazing, Full file system extraction
Tools
Compare all toolsWhat it is
sms.db is the SQLite database behind the Messages app. It stores iMessage conversations, carrier SMS and MMS, and on iOS 18 and later RCS messages, together with group chats, reactions, delivery and read receipts, and pointers to attachments on disk. It is usually the first database opened in an iPhone case because it reaches a standard backup and answers the core question: who talked to whom, and when.
The same schema is used by chat.db on macOS, so tooling and SQL carry over between the two, but the paths, the backup layout and some columns differ.
Where it lives
| Source | Path | Notes |
|---|---|---|
| Full file system | /private/var/mobile/Library/SMS/sms.db (+ -wal, -shm) | Collect all three files together |
| Attachments | /private/var/mobile/Library/SMS/Attachments/xx/yy/<GUID>/ | Paths referenced by attachment.filename with a ~/ prefix |
| iTunes / Finder backup | HomeDomain, Library/SMS/sms.db | Stored as 3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28 |
| Backup attachments | MediaDomain, Library/SMS/Attachments/... | One hashed file per attachment, resolved through Manifest.db |
When Messages in iCloud is enabled, the local database can be a partial cache: older threads may only exist in iCloud, and deletions on one device propagate to the others. Record the setting (it appears in the device's Apple Account data and in the user's statement) before interpreting gaps.
What it proves
- Sent and received messages per conversation, with the sender handle (phone number or email address) and the service used.
- Group membership and group names, including renames over time.
- Delivery and read state, where the sender's device received receipts.
- Attachments: original file name, MIME type, size and on-disk path.
- Tapbacks, replies and, from iOS 16, edited and unsent messages (see Key fields).
It does not prove who was holding the phone. A message "from me" was sent from this Apple Account, which may be signed in on a Mac or iPad as well. Check the account and service columns and the owner's other devices.
Key fields
| Table / column | Meaning |
|---|---|
message.text | Plain text body. Often NULL on recent iOS, see attributedBody |
message.attributedBody | Typedstream (NSArchiver) blob holding the formatted text; the body lives here when text is empty |
message.handle_id | Foreign key to handle.ROWID (the other party) |
message.is_from_me | 1 = sent by the device owner |
message.service | iMessage, SMS; RCS observed on iOS 18+ |
message.date, date_read, date_delivered | Event times (see Timestamps) |
message.associated_message_guid, associated_message_type | Tapbacks and replies point at another message GUID |
message.date_edited, date_retracted | Added with edit and unsend support (iOS 16); verify the exact columns on your build |
message.message_summary_info | Blob that can hold edit history on iOS 16+ |
handle.id | Phone number (E.164) or email address |
chat.chat_identifier, display_name | Conversation ID and group name |
chat_message_join, chat_handle_join | Link messages and participants to chats |
attachment.filename, mime_type, transfer_name | Attachment path and original name |
Columns are added with most major releases. Run .schema message on your copy before trusting a canned query.
Timestamps
message.date and the other date columns hold Cocoa time in nanoseconds on every release this site covers (older iOS versions used seconds). Convert to Unix seconds by dividing by 10^9 and adding 978307200:
SELECT m.ROWID,
datetime(m.date / 1000000000 + 978307200, 'unixepoch') AS sent_utc,
CASE WHEN m.date_read > 0
THEN datetime(m.date_read / 1000000000 + 978307200, 'unixepoch') END AS read_utc,
h.id AS handle, m.service, m.is_from_me, m.text
FROM message m
LEFT JOIN handle h ON m.handle_id = h.ROWID
ORDER BY m.date;
All values are UTC. A date_read of 0 means "not read" or "no receipt", not 1 January 2001.
Retention
Messages stay until the user deletes them or the Keep Messages setting (30 days, 1 year, forever) purges them. From iOS 16, a deleted message moves to Recently Deleted for about 30 days before final removal, tracked through tables such as chat_recoverable_message_join. After that, SQLite freelist pages and the -wal file can still hold fragments, which is one reason a full file system extraction beats a backup for deleted content.
Collection
- Backup: an unencrypted iTunes or Finder backup contains
sms.dband the attachments. With libimobiledevice:idevicebackup2 backup --full ./case/backup. Then resolve the file throughManifest.dbor copy3d/3d0d7e5f...directly and rename it. - Full file system: collect
sms.db,sms.db-wal,sms.db-shmand theAttachmentstree. Never open the original: SQLite may checkpoint the WAL and change the evidence. - Hash the files immediately after extraction and record the extraction method and the iOS version.
Parsing
- Messaging Forensics reads
sms.dbin the browser, including bodies that only exist inattributedBody, and merges it with WhatsAppChatStorage.sqliteinto one timeline. - iLEAPP parses messages and attachments from a backup or a full file system extraction.
- imessage-exporter exports conversations to HTML or text and decodes typedstream bodies.
- For raw SQL, join
messagetochatthroughchat_message_join, and treatattributedBodyas binary: extracting its string requires a typedstream decoder, not aCAST.
Investigator tips
- An empty
textcolumn is normal on current iOS. Do not report "no content" before decodingattributedBody. - Match handles to names with AddressBook after normalising phone numbers to E.164.
- Correlate with CallHistory for FaceTime and phone calls to the same handle, and with KnowledgeC and Biome to see whether Messages was actually in focus when a message was read.
- A message edited or unsent on iOS 16+ can keep traces of the original in
message_summary_infoand in the WAL. Report what is recoverable, and say which extraction level you had. - SMS sent while the device had no service can show a
datelong beforedate_delivered: read both before building a sequence.