Introduction to SQLite3 - a Simple Database Engine

by Adam Bell

Ever wanted to create a little database as part of a script? Ever agonized over setting up associations in an array of records or needed a huge collection of properties in your script? Ever used external text files or complex .pref files to store your script’s persistent data? If you’re running OS X 10.4 (Tiger) then SQLite might be just what you need. This tutorial is an introduction to using SQLite from an AppleScript; it is not a tutorial on Standard Query Language (SQL).

Introduction

In Matt Neuburg’s AppleScript: The definitive Guide, Second Edition” he discusses a new background-only scriptable tool in Tiger’s CoreServices called “Database Events.app”. It is intended for creating and manipulating small databases - a scriptable background-only link to SQLite3. SQLite3 in turn, is a small, open-source database engine introduced in and part of Tiger.

Although the AppleScript Dictionary for Database Events.app is viewable from AppleScript editors, it provides only a subset of what SQLite can do and if there is any documentation for Database Events on Apple’s website, I didn’t find it. Dr. Neuburg says in his book that using Database Events for the creation and manipulation of databases is “nowhere near as powerful, coherent, and complete as the sqlite3 command line tool”, so it’s best in his view to “disregard Database Events as a bad job, and use the command line instead.” After some experiments, I heartily concur. Database Events doesn’t even have a way for scripting the location of the database SQLite creates. This tutorial is therefore an introduction to using SQLite directly from the command line in a ‘do shell script “…”’ command; there is no further mention of Database Events here.

SQLite

What is and isn’t SQLite? it is a mini, single-user open-source unix database engine that understands a simple subset of SQL (Standard Query Language). SQLite is small, fast, reliable, and best of all, simple to use, and simple to learn. If you have never used a database before, SQLite is a good introduction. It should work well for low and medium traffic websites and kiosks; as a replacement for ad hoc textual data files created from your script with “open for access blah with write permission” and later read back (because it is so easy to search a database, but you will have to write a handler to search your own data file), and it is great for scripts that have a lot of data that must be changed, sifted and sorted in diverse ways.

SQLite is not a replacement for a preference file created with a shell “defaults write” statement, but it’s an excellent way to store a bunch of property constants or a complex dictionary of terms required for your script without rolling your own array of records. It is not for multiple simultaneous users; if that’s your situation, then one of the Client/Server databases (like mySQL) is the only way to go. Finally, it is not fast over the internet: it is meant to be used on the same machine as the script calling it.

Getting Started

NOTE: It’s really useful to refer to the SQLite language guide in a separate window (or tab) as you follow along. Most of the instructions are fairly straight-forward.

First, we create and then populate a database. Since SQLite instructions can be concatinated strings of statements separated by semicolons and spaces, we can build up a whole string of commands and then execute them in a single do shell script. Here’s an example:

set loc to space & "~/desktop/TestDB.db" & space
set head to "sqlite3" & loc & quote
set tail to quote
-- "head" tells SQLite where to put our db if it doesn't exist, identifies it if it does.
-- "head" is the opening statement of every future command to our db.
-- "tail" ends every query started with "head".

-- Next, we set up a table and give the columns labels (there can be several).
-- Note the space between the semicolon (which ends every line) and the quote.
set tblName to "mods"
set newTbl to "create table " & tblName & "(firstname, lastname, country); "

-- Now we set up the data to be entered in the table
set d1 to "insert into mods values('Ray', 'Barber', 'USA'); "
set d2 to "insert into mods values('jj', 'Sancho', 'Spain'); "
set d3 to "insert into mods values('Adam', 'Bell', 'Canada'); "
set d4 to "insert into mods values('Bruce', 'Phillips', 'USA'); "
set d5 to "insert into mods values('Kim', 'Hunter', 'USA'); "
set d6 to "insert into mods values('Kevin', 'Bradley', 'USA'); "

-- And finally, build the SQLite query and execute it
do shell script head & newTbl & d1 & d2 & d3 & d4 & d5 & d6 & tail
-- a new db called TestDB.db should appear on your desktop!

Checking the db Table

Now to see what we just did, we can use one of several approaches. If the db is not too large (and ours is certainly not) then we can view it in a dialog:

-- First, the same path, head and tail we used before:
set loc to space & "~/desktop/TestDB.db" & space
set head to "sqlite3 -line" & loc & quote -- the "-line" option outputs the column data and heading one line at a time - useful for parsing the output for particular data items.
set tail to quote
set All to "select * from mods; " -- the "*" means get all rows. columns are separated by pipes ("|") in the result.
set modsTable to do shell script head & All & tail
set title to "So far, the table contents are: " & return & return
display dialog title & modsTable buttons {"Done"} default button 1 with icon 1

If your db is getting too long for a dialog box, you could have put it in the clipboard and pasted it into any text processor or to a new document in the script editor to check it:

set head to "sqlite3 -line ~/desktop/TestDB.db " & quote
set getAll to "select * from mods; "
set modsTable to do shell script head & getAll & quote
set title to "(*" & return & "Your 'mods' Table" & return & return
set the clipboard to title & modsTable & return & "*)"
tell application "Script Editor" to make new document with properties {contents:the clipboard}

Or, for aficionados of one-liners:

tell application "Script Editor" to make new document with properties {name:"Your 'mods' table", contents:"set db to \"" & return & (do shell script "sqlite3 -line  ~/desktop/TestDB.db 'select * from mods; '") & "\""}

Querying Your Table

When we want some particular piece (or pieces of data back from our db, we construct a query like this:

-- Combining the path into the head:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- "-column" displays results in table-like form using whitespace to separate the columns. In a monofont like courier, they will be lined up; not so in a variable width font.
-- Now the query:
set Spain to "select firstname, lastname from mods where country = 'Spain'; "
-- Get the answer (which will appear in your script as a list, so we'll name the parts
set {firstname, lastName} to words of (do shell script head & Spain & quote)
--> {"jj", "Sancho"}
set tLast to "select lastname from mods where firstname = 'Bruce'; "
set surname to do shell script head & tLast & quote --> "Phillips"

Of course, if we had searched for country = ‘USA’, the list for this table would contain eight items: {“Ray”, “Barber”, “Bruce”, “Phillips”, “Kim”, “Hunter”, “Kevin”, “Bradley”} and looking for {firstname, lastname} would only get the first pair of them. If you suspect multiple answers are possible count them and extract them from their list in a repeat block.

Query Conditions Can Be Compounded with AND or OR:

set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- example of a query with an "OR" condition (Spain or Canada):
set getNames to "select firstname, lastname from mods where country = 'Spain' or country = 'Canada';  "
set N to words of (do shell script head & getNames & quote)
set tNames to {}
repeat with k from 1 to (count N) - 1 by 2
	tell N to set end of tNames to item (k + 1) & "," & space & item k
end repeat
tNames --> {"Sancho, jj", "Bell, Adam"}

Adding to, Editing, and Altering a DB Table

Now that we have created a db, populated it, looked at it, and queried it, the obvious question is “Can we make changes?” – Yes and no. Yes, we can easily add more data or change the date in our table(s), but no, we can’t alter the structure of a table by adding a column, for example. Note: Newer versions of SQLite3 (3.2+) have two commands for altering the layout of a table: "alter table table_name rename to new_table_name; ", and “alter table table_name add new_col”, but these are not available in the Tiger version (3.1.3). Bear in mind, however, that even with the latest versions, you cannot delete a column once it is created, you can only delete rows.

Adding to the Database

Adding new data is straight-forward. To add Nigel Garvey and Kai Edwards who both reside in the UK, for example:

set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
set newData to "insert into mods values ('Nigel', 'Garvey', 'UK'); "
set moreData to "insert into mods values ('Kai', 'Edwards', 'UK'); "
-- and then to check the result in the same command, add this line:
set myResult to "select * from mods; " -- the check is optional, of course.
display dialog (do shell script head & newData & moreData & myResult & quote)

Removing a Row from a Table

If we want to remove a row of a table, we use the command “delete from” like this:

set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- identify the row with a condition unique to it:
set trashME to "delete from mods where firstname = 'Adam'; " -- there's only one.
-- check the result - always optional
set myResult to "select * from mods; "
display dialog (do shell script head & trashME & myResult & quote)

A word of caution: the command “delete from mods;” by itself (with no identifying “where” clause) will delete all of the data in the table. Also, deleting a row or rows from a table does not reduce the size of the data base - the blank line is left in place. To compress the table after alterations, it is useful to include the line: "vacuum mods; ", which will compress the table, and we could have included that instruction immediately following the deletion. To delete the whole table, we would run the instruction: "drop table mods; "

Editing a Row of a Table

SQLite3 supports the “update” statement. This statement is used to change the value of the data in columns of selected rows of a table (Not the column headings - they are cast in stone when the table is created). Selection is by means of a where clause to single out those rows that fit. For example to change “UK” to “England” as the country for Nigel and Kai, we would do this:

set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
set changes to "update mods set country = 'England' where country='UK'; "
-- note that all such matches are changed.
-- and then to check the result in the same command set, add this line:
set myResult to "select * from mods; " -- optional in a serious script, of course.
display dialog (do shell script head & changes & myResult & quote)

Adding a Second Table to a Database

Since the Tiger version of SQLite3 won’t let us add columns, one way to add data is to add a new table; but so they can be jointly searched, make sure all the new column titles are different from the first. The script below creates a new table in our TestDB.db file that includes the lastname from the first table under the column heading “surName” and a new column “aNum” (in this case a made-up number as text) The script also adds my name and data back to ‘mods’ (remember that we deleted it earlier), so it will now appear at the end of the mods table.

set head to "sqlite3 -line" & " ~/desktop/TestDB.db " & quote
-- get my name back on the original list (mods)
set MeAgain to "insert into mods values ('Adam', 'Bell', 'Canada'); "
-- Make the new table with column titles that are unique to it.
set newTable to "create table " & "mods2" & "(surName, aNum); "
-- populate the new list (see comment below)
set d1 to "insert into mods2 values ('Barber', '1'); "
set d2 to "insert into mods2 values ('Sancho', '23'); "
set d3 to "insert into mods2 values ('Bell', '45'); "
set d4 to "insert into mods2 values ('Phillips', '67'); "
set d5 to "insert into mods2 values ('Hunter', '89'); "
set d6 to "insert into mods2 values ('Bradley', '101'); "
set d7 to "insert into mods2 values ('Garvey', '102'); "
set d8 to "insert into mods2 values ('Edwards', '103'); "
-- collect stuff
set All to MeAgain & newTable & d1 & d2 & d3 & d4 & d6 & d7 & d8
-- do it
do shell script head & All & quote

(* Clearly, we could have extracted the last names as a list from mods and then constructed the entries for the new table (mods2) by adding the relevant data in a second list and iterating through the two to form the text for the inserts. Here, for the sake of simplicity, I've just added the data in a series of "insert" statements.*)

If you want to check the new db, use this one-liner:

display dialog (do shell script "sqlite3 -line ~/desktop/TestDB.db  'select * from mods2;'") -- easy enough?

To get a specific person’s number, try this:

set head to "sqlite3" & " ~/desktop/TestDB.db " & quote
set getNames to "select surName from mods2; "
set theWho to words of (do shell script head & getNames & quote)
set who to (choose from list theWho with prompt "Choose a Name to Get a Number") as text
set getIt to "select aNum from mods2 where surName ='" & who & "'; "
set gotIt to (do shell script head & getIt & quote)
display dialog who & " is number " & gotIt

Combining Data from Both Tables

Suppose we wanted to discover something with parts in two tables; for example we might want both first name and number for the person living in Spain. First name and country are in one table (mods), but number is in the other (mods2). We can search both if those tables have a data column in common but those columns have different column labels so we can equate one to the other meaningfully (saying where myCol = myCol doesn’t do it).

Our tables have one column’s data in common: mods:lastname holds the same data as mods2:surname - so that’s our connection. We construct our query as follows with a where clause that specifies both the common items and our specific targets (firstname, aNum):

set head to "sqlite3" & " ~/desktop/TestDB.db " & quote
-- note we include both tables in the "from"
-- and that we ask for a match between the data in common
-- and finally [b]AND[/b] on our condition for an individual name
set Q to "select firstName, aNum from mods, mods2 where lastname=surName and country='Spain'; "
set ans to (do shell script head & Q & quote) --> "jj|23"
display dialog word 1 of ans & " is number " & word 3 of ans

There’s much more can be done, of course, but lest this tutorial grow to groaning proportions, we’ll stop here for now, perhaps doing more in a later article. Happy database scripting.

1 Like

Adam, this was so useful. Many thanks.
I thought I’d adapt your first few examples to using Applescript 2.3 library support and write a small class that did some of the repetitive work … just to play and become familiar with AppleScripting SQLite.

Firstly, here’s the library file “/Users/iainhouston/Library/Script Libraries/IHDatabase”


property name : "IHDatabase"
property version : "1.0"

--IHDatabase class
on IHDatabase at dbname
	script IHDB
		property loc : missing value -- tells SQLite where to put our db if it doesn't exist, identifies it if it does.
		property head : missing value -- the opening statement of every future command to our db.
		property tail : missing value --ends every query started with "head".
		property sql_stmt_list : missing value -- we build up a SQL program here
		
		on init(dbname)
			set loc to space & dbname & space
			set head to "sqlite3 -column" & loc & quote
			set tail to quote
			set sql_stmt_list to {}
			return me
		end init
		
		on add_sql(stmt)
			set stmt_space to stmt & space
			set sql_stmt_list to sql_stmt_list & stmt_space
		end add_sql
		
		on run_sql()
			set rows to do shell script head & sql_stmt_list & tail
			set sql_stmt_list to {}
			return rows
		end run_sql
		
	end script
	return IHDB's init(dbname)
end IHDatabase

Next the library’s client script:


use scripting additions -- without this `display dialog` means nothing!
use myLib : script "IHDatabase"

set db to IHDatabase of myLib at "~/Desktop/TestDB.db" -- IHDatabase handler returns an IHDB script object
tell db
	add_sql("drop table if exists testtable;")
	add_sql("create table testtable (firstname, lastname, country);")
	add_sql("insert into testtable values('Ray', 'Barber', 'USA');")
	add_sql("insert into testtable values('jj', 'Sancho', 'Spain');")
	add_sql("insert into testtable values('Adam', 'Bell', 'Canada');")
	add_sql("insert into testtable values('Bruce', 'Phillips', 'USA');")
	add_sql("insert into testtable values('Kim', 'Hunter', 'USA');")
	add_sql("insert into testtable values('Kevin', 'Bradley', 'USA');")
	add_sql("select * from testtable;") 
	set testtable_result to run_sql()
	log testtable_result & return & return
	set title to "So far, the table contents are: " & return & return
	display dialog title & testtable_result buttons {"Done"} default button 1 with icon 1
end tell

@Adam Bell: Thank you very much for this tutorial. I was just what I needed, and you provided excellent instructions.

Also, I want to confirm that everything about sqlite3 is still working fine in Yosemite (10.10.5) as of 2016-03-16.

@iainH: Many thanks to you as well for sharing your nice library handler. It works quite well.

I would like to add the following, which I discovered as a result of Adam’s tutorial:

(1) Be sure to checkout the SQLite Web Site. It is full of great info like: Command Line Shell For SQLite

(2) Looks like SQLite can handle lots of simultaneous readers.
From Appropriate Uses For SQLite

(3) SQLite seems to be very fast.
As an example from my proof-of-concept script, I as able to get selected records from an Evernote SQLite DB of over 15,000+ rows with ~20 columns in 0.061 seconds.

(4) For AppleScript Developers/Users, it looks like it is a great way to store all kinds of data easily, and have the built in tools to query/filter/sort results.

When creating a document based application in Cocoa I can choose to use CoreData as my storage. CoreData figures out how to serialize and store objects in an SQLite database. There are different kind of ways to store your file from simple plain ASCII files to complex binary blocks. All kind of files have their pros and cons and therefore certain types are used for specific situations.

A typical binary file have headers and blocks of binaries (meta)data in it. It can read and write parts to the file without needing to overwrite or read the entire file. Even if it uses up much more space than flat binary files (binary streams) they are much more efficient being much faster in real world situations. SQLite stores their databases this way for these performance reasons so after each update, insert or delete you don’t need to write the entire database to disk, only the changes. So if SQLite prefers the “ad hoc data files” why should primitive file type doesn’t do the same?

If you’re interested in this you should definitely take a look at object/document oriented databases. MongoDB and CouchDB (just as example) are exceptionally useful for application back-end storage and I think they will succeed SQLite in the future.

@DJ, thanks for the info, but using SQLite is about as far as I want to go, at least at this time.
It is very easy to setup and use, and is very fast. And a big bonus for me is that it use SQL, which I am very familiar with from by SQL database developer days.

Now that I know how easy it is to use SQLite DB with AppleScript, it opens up a whole new broad category of use cases for me. When I combine that with Shane Stanley’s Myriad Tables, I have a very powerful data and presentation tool.

Then never mind what I said :cool:

Actually I appreciate your (and all other) suggestions, even if they are way out of the box. I have picked up on numerous ideas/solutions from just such suggestions.

Thanks again.

I’ve been playing with SQLite. It’s quite usable from AppleScript via the command line tool sqlite3 and do shell script, but it seems to me that this approach has several drawbacks from AppleScript.

The biggest issue is that everything is reduced to text. So you can end up doing a lot of text manipulation just to get stuff in and out. This is especially the case where there are a lot of results. And while it’s not slow, it could be faster.

There is a third-party framework, FMDB, that puts an Objective-C wrapper around SQLite, so it can be called from AppleScriptObjC. Unfortunately some of it is lower level and more complicated than would be ideal for AppleScript.

So I’ve written a new framework, FMDBAS. This is the FMDB framework wrapped in a more AppleScript-friendly face (but maintaining access to the more advanced stuff for those who need it). Initial tests show it’s faster – in some cases considerably so. It’s main advantages are:

  • Easy use of SQL ? placeholders, so you can optionally pass parameters as lists containing differing classes. You can handle classes such as dates and images much more easily.

  • Query results are returned in either arrays/lists or dictionaries/records for each row, so there’s no need to massage a string to extract the results.

  • Query results are returned initially as Cocoa arrays. You can coerce them to AppleScript lists (a simple “as list”), but in the case of large results it’s often quicker to deal with the raw arrays. And because arrays are returned as a simple pointer, large result sets are returned much, much quicker.

  • Support for transactions. You can batch updates together for greater efficiency.

  • Lower overhead than shell, and combined with transactions this means there’s less need to build long statements to perform things like multiple insertions.

  • Better error handling.

There are a few cons:

  • You need to add the framework to your system. I may wrap it in a library at some stage.

  • You need at least a basic understanding of AppleScriptObjC syntax to use it.

  • It won’t work under versions earlier than 10.10 at this stage.

It’s still at the early testing stage, and I’m looking for interested volunteers. If that’s you, please email me.

As a taster, here are the reading and writing examples from Adam’s first post here, rewritten to use the new framework:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDBAS"
use scripting additions

-- create and open db
set thePath to current application's NSString's stringWithString:"~/desktop/TestDB.db"
set theDb to current application's FMDatabase's databaseWithPath:(thePath's stringByExpandingTildeInPath())
theDb's openWithFlagsAS:(current application's FMDBASOpenReadWriteCreate)

-- create table, clearing if necessary it
set tableName to "mods"
theDb's executeUpdateASBool:("drop table if exists " & tableName)
theDb's executeUpdateAS:("create table " & tableName & " (firstname, lastname, country)")

-- enter data
theDb's beginTransactionAS()
repeat with aList in {{"Ray", "Barber", "USA"}, {"jj", "Sancho", "Spain"}, {"Adam", "Bell", "Canada"}, {"Bruce", "Phillips", "USA"}, {"Kim", "Hunter", "USA"}, {"Kevin", "Bradley", "USA"}}
	(theDb's executeUpdateAS:"insert into mods values(?, ?, ?)" withArguments:aList)
end repeat
theDb's commitAS()
-- close database
theDb's closeAS()

And reading:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDBAS"
use scripting additions

-- create and open db
set thePath to current application's NSString's stringWithString:"~/desktop/TestDB.db"
set theDb to current application's FMDatabase's databaseWithPath:(thePath's stringByExpandingTildeInPath())
theDb's openWithFlagsAS:(current application's FMDBASOpenReadOnly)

-- query it
set theResult to theDb's executeQueryAS:("select firstname, lastname from mods where country = 'Spain'")
-- *or*:
-- set otherResult to theDb's executeQueryAS:("select firstname, lastname from mods where country = ?") withArguments:{"Spain"}
set surname to theDb's executeQueryAS:("select lastname from mods where firstname = ?") withArguments:{"Bruce"}
theDb's closeAS()
return {theResult as list, surname as list}

Shane, this looks totally awesome! Count me in! I’m happy to be one of your beta testers.
I’ll send you a separate email as requested.

So this project morphed into SQLite Lib, a free scripting library, which is now available.

SQLite Lib offers a more efficient and faster alternative than using the command-line tool sqlite3 via do shell script. It requires OS X 10.10 or later.

You can read about it and download it here: www.macosxautomation.com/applescript/apps/SQLite_Lib.html