macOS Automation with AppleScript and SQLite - macOS Only

Apple provides system hooks to automate macOS desktop applications through Apple Events. The command-line program osascript runs AppleScript code and JavaScript for Automation (JXA) scripts from shell sessions and sub-processes. Common Lisp programs can call osascript through UIOP to control native macOS applications, including Mail, Calendar, Messages, Reminders, and Safari. In addition, Common Lisp can read historical chat records directly by querying the local SQLite database that stores iMessage data.

This chapter presents the :mac system. It shows how to automate macOS applications and read system data without external dependencies beyond UIOP.

Note 1: This code runs only on macOS.

Note 2: This code requires SBCL and UIOP.

Security and System Permissions (TCC)

macOS enforces a privacy framework called Transparency, Consent, and Control (TCC). TCC protects sensitive user data such as mailboxes, calendars, contacts, and message logs. Before you run this code, you must grant the appropriate permissions to the terminal emulator or text editor hosting your Common Lisp environment.

1. Automation Permissions

When a Lisp process controls another application through osascript, macOS prompts the user for consent. If you do not grant permission, or if macOS suppresses the prompt, Apple Events fail with permission errors.

To check or update these permissions:

  1. Open System Settings > Privacy & Security > Automation.
  2. Locate the host application running your Common Lisp image (such as Terminal, iTerm2, or Emacs).
  3. Confirm that toggle switches for Mail, Calendar, Messages, Reminders, and Safari are active.

2. Full Disk Access for Message History

The Messages application stores chat history in an SQLite database located at ~/Library/Messages/chat.db. Because this database contains personal communication records, macOS blocks regular file access even if the user owns the file.

To allow Common Lisp to read this database:

  1. Open System Settings > Privacy & Security > Full Disk Access.
  2. Add your terminal emulator or editor to the permitted applications list.
  3. Enable the toggle switch.

If you omit this step, queries against chat.db fail with an unable to open database file error.

3. Safari Apple Events Permission

Safari blocks script execution from Apple Events by default. If a script attempts to run JavaScript inside Safari without this setting, macOS returns error -10004 (privilege violation).

To enable JavaScript execution in Safari:

  1. Launch Safari.
  2. Open Settings > Advanced and check Show features for web developers (or Show Develop menu in menu bar on older macOS versions).
  3. Select the Develop menu in the menu bar.
  4. Check Allow JavaScript from Apple Events.

You can also set this preference from the terminal:

1 defaults write -app Safari AllowJavaScriptFromAppleEvents 1

System Definition and Package Structure

The system definition lives in mac.asd. It depends only on #:uiop and loads four files in sequence:

 1 ;;;; mac.asd -- ASDF system for the :mac package.
 2 
 3 (asdf:defsystem #:mac
 4   :description "macOS automation via osascript (Mail, Calendar, Messages, Reminders, Safari)."
 5   :depends-on (#:uiop)
 6   :serial t
 7   :components ((:file "package")
 8                (:file "osascript")
 9                (:file "read-recent-imessages")
10                (:file "safari-login")))

The package definition lives in package.lisp. It exports the public functions and condition symbols:

 1 ;;;; package.lisp -- the :mac package shared by all files in this directory.
 2 ;;;; Load this file first.
 3 
 4 (defpackage :mac
 5   (:use :cl)
 6   (:export
 7    ;; osascript.lisp
 8    #:osascript-error
 9    #:osascript-error-code
10    #:osascript-error-message
11    #:osascript-error-script
12    #:run-osascript
13    #:mail-unread-count
14    #:get-todays-events
15    #:send-imessage
16    #:add-reminder
17    ;; read-recent-imessages.lisp
18    #:read-recent-imessages
19    ;; safari-login.lisp
20    #:safari-login))

The AppleScript Interface (osascript.lisp)

The file osascript.lisp provides the execution core and standard application handlers.

Capturing Subprocess Errors

When osascript fails, it prints diagnostics to standard error (such as syntax errors or missing permissions) and exits with status 1. Standard calls to uiop:run-program drop standard error on failures and signal a generic uiop:subprocess-error.

To preserve the error text, osascript.lisp defines a dedicated condition type osascript-error:

 1 (define-condition osascript-error (error)
 2   ((code    :initarg :code    :reader osascript-error-code)
 3    (message :initarg :message :reader osascript-error-message)
 4    (script  :initarg :script  :reader osascript-error-script))
 5   (:report (lambda (condition stream)
 6              (format stream "osascript failed (exit ~D): ~A"
 7                      (osascript-error-code condition)
 8                      (osascript-error-message condition))))
 9   (:documentation "Signalled when osascript exits non-zero.  The MESSAGE
10 slot holds the real AppleScript diagnostic from stderr."))

The function run-osascript runs the script with osascript -e, captures standard error, and inspects the return code:

 1 (defun run-osascript (script)
 2   "Run AppleScript source SCRIPT with `osascript -e', returning trimmed
 3 stdout.  Signals OSASCRIPT-ERROR (with osascript's own message) on failure."
 4   (multiple-value-bind (stdout stderr exit-code)
 5       (uiop:run-program (list "osascript" "-e" script)
 6                         :output :string
 7                         :error-output :string   ; <-- the real error text
 8                         :ignore-error-status t) ; <-- inspect the code ourselves
 9     (let ((out (string-trim '(#\Space #\Newline #\Tab) (or stdout "")))
10           (err (string-trim '(#\Space #\Newline #\Tab) (or stderr ""))))
11       (if (zerop exit-code)
12           out
13           (error 'osascript-error
14                  :code exit-code
15                  :script script
16                  :message (if (plusp (length err))
17                               err
18                               (if (plusp (length out)) out "no diagnostic on stderr")))))))

Reading Unread Mail

The function mail-unread-count asks Mail for the unread message count of the inbox:

1 (defun mail-unread-count ()
2   (parse-integer (run-osascript "tell application \"Mail\" to return unread count of inbox")))

osascript returns the number as a string on standard output. parse-integer parses this output into a Lisp integer.

Fetching Calendar Events

The function get-todays-events queries Calendar for events scheduled between midnight of the current day and midnight of the following day:

 1 (defun get-todays-events ()
 2   (let ((script "tell application \"Calendar\"
 3   set todayStart to (current date)
 4   set time of todayStart to 0
 5   set todayEnd to todayStart + 1 * days
 6   set eventList to {}
 7 
 8   repeat with c in calendars
 9     set calendarEvents to (summary of (every event of c whose start date >= todayStart and start date < todayEnd))
10     repeat with e in calendarEvents
11       set end of eventList to e
12     end repeat
13   end repeat
14 
15   set AppleScript's text item delimiters to \"|\"
16   return eventList as string
17 end tell"))
18     ;; Split the pipe-delimited string directly into a Common Lisp list
19     (uiop:split-string (run-osascript script) :separator '(#\|))))

AppleScript uses the global property AppleScript's text item delimiters to join list items when casting a list to a string. Setting the delimiter to "|" allows Lisp to convert the output into a list of strings using uiop:split-string.

Sending Messages and Creating Reminders

send-imessage targets the Messages service to deliver text to a recipient:

1 (defun send-imessage (phone-number message)
2   ;; ~S wraps the Lisp strings in the double quotes AppleScript needs.
3   (let ((script (format nil "tell application \"Messages\"
4   set targetService to 1st service whose service type = iMessage
5   set targetBuddy to buddy ~S of targetService
6   send ~S to targetBuddy
7 end tell" phone-number message)))
8     (run-osascript script)))

The format directive ~S places quotes around the phone number and message text, ensuring valid AppleScript syntax.

add-reminder creates a new entry in Reminders and returns the unique reminder identifier:

1 (defun add-reminder (task-name)
2   (let ((script (format nil "tell application \"Reminders\"
3   set newReminder to make new reminder with properties {name:~S}
4   return id of newReminder
5 end tell" task-name)))
6     (run-osascript script)))

Reading iMessage History via SQLite (read-recent-imessages.lisp)

AppleScript does not provide a fast, searchable interface for reading message histories. The Messages application stores message data in an SQLite database at ~/Library/Messages/chat.db. Reading this file directly with the command-line utility sqlite3 provides fast access to message records.

The function read-recent-imessages joins the message table with the handle table:

 1 (defun read-recent-imessages (&optional (limit 5))
 2   (let* ((db-path (namestring (merge-pathnames "Library/Messages/chat.db" 
 3                                                (user-homedir-pathname))))
 4          ;; Join the message table with the handle table to get the sender's phone/email
 5          (query (format nil "SELECT m.is_from_me, h.id AS sender, m.text 
 6                              FROM message m 
 7                              LEFT JOIN handle h ON m.handle_id = h.ROWID 
 8                              WHERE m.text IS NOT NULL 
 9                              ORDER BY m.date DESC LIMIT ~A;" 
10                         limit))
11          ;; The -json flag outputs a cleanly formatted JSON array of objects
12          (command (list "sqlite3" "-json" db-path query)))
13     
14     ;; Returns a JSON string suitable for parsing with shasht, jonathan, or cl-json
15     (uiop:run-program command :output :string)))

The SQL query extracts three fields:

  • m.is_from_me: an integer (1 if sent by the user, 0 if received).
  • h.id: the identifier of the sender (phone number or Apple Account email).
  • m.text: the text body of the message.

The -json argument instructs sqlite3 to output a JSON array of objects. Common Lisp programs can pass this string directly to a JSON parser such as cl-json or shasht.

Browser Automation with Safari (safari-login.lisp)

The file safari-login.lisp provides browser automation without external browser drivers. It controls Safari through AppleScript’s do JavaScript command.

The file is self-contained. It can load alongside osascript.lisp or independently.

Escaping JavaScript Values

The function safari-js-string formats strings for JavaScript string literals. The format control ~S escapes internal double quotes:

1 (defun safari-js-string (value)
2   "VALUE as a double-quoted JavaScript string literal.  ~S yields \\\" for
3 embedded quotes, which is valid in JavaScript *and* in the AppleScript
4 string literal that will carry it."
5   (format nil "~S" (string value)))

The function safari-js-single-quoted produces single-quoted literals for HTML element identifiers:

 1 (defun safari-js-single-quoted (value)
 2   "VALUE as a single-quoted JavaScript string literal (for element ids)."
 3   (with-output-to-string (out)
 4     (write-char #\' out)
 5     (loop for character across (string value)
 6           do (case character
 7                (#\' (write-string "\\'" out))
 8                (#\\ (write-string "\\\\" out))
 9                (#\Newline (write-string "\\n" out))
10                (t (write-char character out))))
11     (write-char #\' out)))

Form Submission and Polling

The function safari-login opens a target URL, waits for the web page to finish loading, and injects JavaScript to fill and submit the form:

 1 (defun safari-login (url username password
 2                      &key (username-id "username")
 3                           (password-id "password")
 4                           (form-id "login-form")
 5                           (timeout 30))
 6   "Open URL in Safari, wait for the page, then fill and submit the login
 7 form.  USERNAME-ID / PASSWORD-ID / FORM-ID must match the target site's
 8 HTML.  TIMEOUT is the maximum number of seconds to wait for the page."
 9   (let* ((javascript
10            (format nil "document.getElementById(~A).value = ~A; document.getElementById(~A).value = ~A; document.getElementById(~A).submit();"
11                    (safari-js-single-quoted username-id) (safari-js-string username)
12                    (safari-js-single-quoted password-id) (safari-js-string password)
13                    (safari-js-single-quoted form-id)))
14          ;; One AppleScript string literal carrying the whole JavaScript
15          ;; program.  Do NOT build the JavaScript by concatenating AppleScript
16          ;; strings ("... = " & "value"): that drops the JavaScript quotes and
17          ;; yields invalid JavaScript such as
18          ;;   document.getElementById('username').value = markw@example.com
19          (script
20            (format nil "set targetURL to ~S
21 set pageLoaded to false
22 
23 tell application \"Safari\"
24   set targetDoc to make new document with properties {URL:targetURL}
25 
26   -- Poll for the page, but with a hard cap so we can never hang forever.
27   set waited to 0.0
28   repeat while waited < ~,1F
29     delay 0.5
30     set waited to waited + 0.5
31     if (do JavaScript \"document.readyState\" in targetDoc) is \"complete\" then
32       set pageLoaded to true
33       exit repeat
34     end if
35   end repeat
36 
37   if pageLoaded then do JavaScript ~S in targetDoc
38 end tell
39 
40 if not pageLoaded then error \"Safari page did not reach readyState 'complete' within ~,1F seconds\""
41                    url timeout javascript timeout)))
42     (safari-run-osascript script)))

The script polls document.readyState every 0.5 seconds until it returns "complete", with a default timeout limit of 30 seconds (timeout = 30). Once the document loads, the script sets form field values and invokes the form’s submit() method.

Running the Code in the REPL

Start SBCL in the src/macOS_osascript/ directory:

1 cd src/macOS_osascript
2 sbcl

Load the system using ASDF:

1 (require :asdf)
2 (asdf:load-asd (truename "mac.asd"))
3 (asdf:load-system :mac)

Alternatively, load the individual files directly:

1 (load "package.lisp")
2 (load "osascript.lisp")
3 (load "read-recent-imessages.lisp")
4 (load "safari-login.lisp")

Enter the :mac package or call functions with the mac: package prefix.

Querying Unread Mail

1 (mac:mail-unread-count)
2 ;; => 3

Listing Today’s Calendar Events

1 (mac:get-todays-events)
2 ;; => ("Team Sync" "Project Review" "Dentist Appointment")

Adding a Reminder

1 (mac:add-reminder "Buy groceries")
2 ;; => "x-apple-reminder://..."

Sending an iMessage

1 (mac:send-imessage "+15551234567" "Hello from Common Lisp!")
2 ;; => ""

Inspecting Recent Messages

Read the last three messages from the local SQLite store:

1 (mac:read-recent-imessages 3)
2 ;; => "[{\"is_from_me\":0,\"sender\":\"+15551234567\",\"text\":\"See you at lunch\"},...]"

Automating Safari Login

1 (mac:safari-login "https://example.com/login"
2                   "user@example.com"
3                   "secret-pass"
4                   :username-id "user-field"
5                   :password-id "pass-field"
6                   :form-id "login-form")

Summary of Error Codes and Troubleshooting

Error Symptom Cause Solution
-10004 privilege violation in Safari Safari blocks Apple Events from executing JavaScript. Enable Safari > Develop > Allow JavaScript from Apple Events.
Not authorized to send Apple events to System Events / App TCC blocks Apple Events between the terminal/editor and the target application. Enable the host terminal or editor in System Settings > Privacy & Security > Automation.
unable to open database file on chat.db TCC blocks access to ~/Library/Messages/chat.db. Grant Full Disk Access to your terminal or editor in System Settings > Privacy & Security > Full Disk Access.
Safari page did not reach readyState 'complete' Page load timed out before DOM completed loading. Increase the :timeout key argument, or check network connectivity.
null is not an object in Safari JavaScript Element identifier does not exist on the target web page. Check HTML source and verify :username-id, :password-id, and :form-id values.

Previous

WebKit Applications - macOS Only

Up next

Book Wrapup