I've been working on a new version of [my iMessage bot](https://github.com/inculi/Sue) recently. Previously it was using Applescript handlers on Sierra, now sqlite calls to iMessage's chat.db (I have to give credit to another bot, [Jared](https://github.com/ZekeSnider/Jared) for help in this area).

I get latest messages with:

```

SELECT handle.id, handle.person_centric_id, message.cache_has_attachments, message.text, message.ROWID, message.cache_roomnames, message.is_from_me, message.date/1000000000 + strftime("%s", "2001-01-01") AS utc_date FROM message INNER JOIN handle ON message.handle_id = handle.ROWID WHERE message.ROWID > #{rowid};

```

and their attachments with:

```

SELECT attachment.ROWID AS a_id, message_attachment_join.message_id AS m_id, attachment.filename, attachment.mime_type, attachment.total_bytes FROM attachment INNER JOIN message_attachment_join ON attachment.ROWID == message_attachment_join.attachment_id WHERE message_attachment_join.message_id >= #{rowid};

```

where rowid is the max rowid from the previous search, and attachments with null mime_types get ignored (YouTube previews, etc). Sending through Applescript still.

Nice job. I'll take a look at your project and see if I can contrib

I did something very similar, extracting the iMessage database. It works by you making an iTunes backup of your phone, and then accessing a specific file in the backup. I ended up with a query that looks like this (only tested with iOS 8, as I sadly lost interest afterwards):

        WITH groupchat_membership AS (
            SELECT cache_roomnames,
            group_concat(id, ', ') AS members
            FROM (
                SELECT DISTINCT message.cache_roomnames, handle.id
                FROM message
                JOIN handle ON message.handle_id=handle.ROWID
                WHERE message.cache_roomnames IS NOT NULL AND message.is_from_me=0
            )
            GROUP BY cache_roomnames
        ),
        unsorted_messages AS (
            SELECT message.ROWID AS rowid,
            CASE WHEN message.cache_roomnames IS NOT NULL THEN groupchat_membership.members ELSE handle.id END AS recipient,
            CASE WHEN message.is_from_me THEN 'Me' ELSE handle.id END AS sender,
            message.date,
            strftime('%d/%m/%Y %H:%M:%S', datetime(message.date + 978307200, 'unixepoch', 'localtime')) AS human_date,
            CASE WHEN message.cache_has_attachments THEN '<Attachment>' || message.text ELSE message.text END AS text
            FROM message
            LEFT JOIN handle ON message.handle_id=handle.ROWID
            NATURAL LEFT JOIN groupchat_membership
        )
        SELECT unsorted_messages.recipient, sender, human_date, text
        FROM unsorted_messages
        NATURAL LEFT JOIN (
            SELECT recipient, max(date) AS latest_date FROM unsorted_messages GROUP BY recipient
        )
        ORDER BY latest_date DESC, date ASC;