Building a system wide snippet database for macOS

Posted by Chris Rosser on Mon 02 January 2017
Hello! This site is archived and no longer maintained. For Chris' main site go to

As listener to the Mac Power Users podcast I've often been curious to try Text Expander. Text Expander is a class of utility that lets you store and retrieve snippets of text and invoke them using a shortcut or trigger.

This is by no means an original concept; many text editors, such as Sublime Text and Gedit, provide this functionality. Where Text Expander shines however is that it's available system wide, meaning it will work across all applications. This is really appealing because you can maintain a single library and use them in applications that do not provide their own snippets feature.

The problem I have with Text Expander is that it now requires at subscription. I have no problem with paying for software but for some reason, I really think twice about entering into a subscription model. It's true that I pay monthly fees for my VPS and extra iCloud storage but these are essentially replaceable. My fear with coming to rely on productivity software that requires this model is if their business fails I'll be up the creek without the proverbial paddle.

My need for this came to a head when I was working in MultiMarkdown Composer and wanted a means to quickly add repeatable sections to the document. Although a great markdown editor, it is not extensible in any way.

My first stumbling block was that I wanted to insert the current date and time. Many editors have this feature built in. Not so MultiMarkdown Composer. Having used the Automator in the past to wrap shell commands and scripts, I decided to take the same route. w Basically all I did was wrap a simple shell script into an Automator workflow that was saved as a service. I gave it a keyboard short in System Preferences and so it's available to me system wide.

Automator Workflow: insert formatted date
Automator Workflow: insert formatted date

This is the behaviour I want, so I figured all I needed to do was wrap a shell utility that will perform a look up based on what is selected and return the snippet to the Service.

In the example above I use a single command, date, in Bash to return the formatted date. Naturally, I need something more sophisticated because I need to query a data source but the Automator logic is the same.

Data Source

The data source is where I store my snippets. Most text editors (including Gedit and Sublime Text) store snippets as XML files. I personally find XML is messy as a persistence format so I elected to use an SQLite database instead. SQLite is built into macOS, is light on resources and I find that querying through SQL is much nicer than using XPath and XQuery.

For the data source, I created a very simple schema with a single table with two columns: trigger and text. The trigger is the text shortcut that will reference the snippet and text contains the actual snippet.

To create new snippets I'm using a simple open-source GUI application, DB Browser for SQLite. It's good enough for my needs and means I don't need to create my own GUI.

Python Script

Querying a database requires a bit more programming than invoking a one-line shell command so I turned to Python to handle the connection (and errors), query the snippet table and return the result.


import sqlite3 as sql
import sys
import re

con = None

    con = sql.connect('/Users/christopher/Documents/projects/db/snippets.db')
    trigger = (sys.argv[1], )
    cur = con.cursor()
    cur.execute("SELECT text FROM snippets WHERE trigger=?", trigger)
    data = cur.fetchone()
    if data == None:
        data = "No record found"
       data = ''.join(data)

    print ( data )

except sql.Error, e:

    print "Error %s:" % e.args[0]


    if con:

As you can see, there's nothing fancy to the script and the functionality is provided by the standard library

The Automator Workflow

The Automator workflow logic is basically the same as the Insert Formatted Date service I created earlier with a few exceptions.

Firstly, I've encapsulated input and output as variables using the Set Value of Variable action. This made it a little easier to debug because I could use those variables in notifications.

Secondly, my shell script is pointing to the Python interpreter and I'm using arguments instead of Stdin to receive input from the previous step. Python handles both so it really doesn't matter.

Automator workflow: Insert snippet
Automator workflow: Insert snippet

Wow you read this far! This site is archived and no longer maintained. For Chris' main site go to