Best way to set up and query a simple database on Mac

Hi all- I’m looking for advice on the best way to build a simple database out of information generated every day from about half a dozen iOS and MacOS apps, mainly about time use. I want to generate a dashboard and trigger some actions based on the data.

I know AppleScript and Keyboard Maestro, and I can use them to do almost everything I want. I can collect the data I need as CSV or JSON files and load it into lists of records (with dictionaries). As far as I can tell, neither Applescript nor KM make it easy to query those records without a lot of repeat loops.

Essentially all I want is a statement like this:

Set DashboardValue to
[sum, average, min, max, or count] of Field A for records
where Field B = X and Date is between Y and Z

Is there an easy way to do that in AppleScript or KM? Or is there a tool I can learn just enough of to solve my problem:

  • SQL
  • A scripting language (JavaScript, Python, etc.)
  • A webapp (Airtable, FireBase, etc.)
  • A spreadsheet (Numbers or Excel) or other Mac app.

Other than spreadsheets, I don’t have any experience with these other languages or platforms. All of these could solve my problem. Does anyone have a view as to what the most painless path to take?

Thanks, Saul

You should check out Shane’s SQL libraries. Best thing about them is if you have any questions you can come here for answers!

SQLite Lib 1.0.0 and SQLite Lib2 1.1.0

SQLite Lib2 and SQLite Lib are libraries for fast and efficient SQLite programming from AppleScript, based on the well-known open-source FMDB Framework. If you ever need to access SQLite databases, or need a simple database for your scripts, one of these is the answer. Functionally similar, SQLite Lib2 requires macOS 10.11 or later and includes a terminology dictionary, whereas SQLite Lib uses traditional handlers, and works under macOS 10.10 and later. Note that scripts using SQLite Lib and SQLit Lib2 cannot be edited in Script Editor in Mojave or later because of security settings. You need to use Script Debugger. See Catalina Security and Script Libraries for Catalina installation details. (Updated July 17, 2020.)

1 Like

Hi, I use Filemaker Pro for all my db needs. It’s very Applescriptable and has a large ecosystem supporting it.

Filemaker Pro is very scriptable but as of the last few years the emphasis there is more corporate clients and app development and server. It is more difficult and expensive to buy a single-user license these days. So if you are looking for a cheap way to do this, forget about FMPro.