Create Sequel Pro "favorites" from command line?

Question

I'm writing a bootstrapping script to automate some of the setup of a new developer's Mac computer. So far, I've been able to install SP from the command line using brew cask:

brew cask install sequel-pro

Is it possible to then create SP "favorites" from the command line so that, for example, the new dev would already have the connection parameters to a local Vagrant box at his/her fingertips?

Answer 1

Sequel Pro stores its Favorites at ~/Library/Application Support/Sequel Pro/Data/Favorites.plist and passwords are stored in the Keychain. So I had to do 2 things:

1. Write the correct data (essentially in XML format) into that file.
2. Create a Keychain entry for my database password.

By looking at the contents of my current Favorites.plist, I came up with this basic starter file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Favorites Root</key>
    <dict>
        <key>Children</key>
        <array>
            <dict>
                <key>database</key>
                <string>my_db_name</string>
                <key>host</key>
                <string>127.0.0.1</string>
                <key>id</key>
                <integer>-4414284772604805500</integer>
                <key>name</key>
                <string>homestead</string>
                <key>port</key>
                <string>33060</string>
                <key>socket</key>
                <string></string>
                <key>sshHost</key>
                <string></string>
                <key>sshKeyLocation</key>
                <string></string>
                <key>sshKeyLocationEnabled</key>
                <integer>0</integer>
                <key>sshPort</key>
                <string></string>
                <key>sshUser</key>
                <string></string>
                <key>sslCACertFileLocation</key>
                <string></string>
                <key>sslCACertFileLocationEnabled</key>
                <integer>0</integer>
                <key>sslCertificateFileLocation</key>
                <string></string>
                <key>sslCertificateFileLocationEnabled</key>
                <integer>0</integer>
                <key>sslKeyFileLocation</key>
                <string></string>
                <key>sslKeyFileLocationEnabled</key>
                <integer>0</integer>
                <key>type</key>
                <integer>0</integer>
                <key>useSSL</key>
                <integer>0</integer>
                <key>user</key>
                <string>homestead</string>
            </dict>
        </array>
        <key>IsExpanded</key>
        <true/>
        <key>Name</key>
        <string>FAVORITES</string>
    </dict>
</dict>
</plist>

I saved this as Favorites.plist in the same directory as my bootstrapping script (as mentioned in the original question) and added these 3 lines to the script:

mkdir -p ~/Library/Application\ Support/Sequel\ Pro/Data/
cp -f Favorites.plist ~/Library/Application\ Support/Sequel\ Pro/Data/
security add-generic-password -U -T "/Applications/Sequel Pro.app" -s "Sequel Pro : homestead (-4414284772604805500)" -a homestead@127.0.0.1/my_db_name -w secret

• The first line makes sure the directory path exists (I haven't tried it with a totally fresh install of SP yet so I don't know if the whole path will be there or not).
• The second line copies the above template into the right place.
• The third line adds the database password ("secret") to my Keychain.
  • Notice how the different parameters in this command line up with elements from Favorites.plist (name, database, id, etc.)
  • See the doc for OS X's security command.

Viola!