I do mainly Objective-C and Cocoa these days (along with SQLite), but here's a simple version of the code I was using to execute arbitrary SQLite commands and selects in AppleScript.
property sqlite3Path : "/usr/bin/sqlite3"
property columnSeparator : character id 3
property rowSeparator : character id 2
on SQLExecute(databasePath, sqlCommand)
set shellCommand to "echo " & (quoted form of sqlCommand) & " | " & sqlite3Path & space & quoted form of databasePath
set resultText to do shell script shellCommand
return resultText
end SQLExecute
on SQLExecuteSelect(databasePath, sqlCommand)
set sqlCommandWithSeparators to "select *, '" & rowSeparator & "' from (" & sqlCommand & ");"
set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " | " & sqlite3Path & space & "-separator " & (quoted form of columnSeparator) & space & quoted form of databasePath
set recordsText to do shell script shellCommand
script speedyObject
property recordList : {}
end script
if recordsText is not "" then
set oldDelimiters to AppleScript's text item delimiters
set AppleScript's text item delimiters to (columnSeparator & rowSeparator & return)
set speedyObject's recordList to text items in recordsText
set last item in speedyObject's recordList to text 1 thru -(1 + (length of (columnSeparator & rowSeparator))) in last item in speedyObject's recordList
set AppleScript's text item delimiters to columnSeparator
set recordCount to (count speedyObject's recordList)
repeat with recordN from 1 to recordCount
set item recordN in speedyObject's recordList to text items in item recordN in speedyObject's recordList
end repeat
set AppleScript's text item delimiters to oldDelimiters
end if
return speedyObject's recordList
end SQLExecuteSelect
on run
set databasePath to "/Users/Shared/Test.sqlite"
SQLExecute(databasePath, "
create table if not exists People
( ID integer primary key
, \"Name First\" text
, \"Name Last\" text
, Company text
, Email text
, DOB date
)
;
insert or ignore into People values (1, 'Tom', 'Brodhurst-Hill', 'BareFeetWare',
'email@hidden', julianday('1968-08-02'))
;
insert or ignore into People values (2, 'Fred', 'Flintstone', 'Bedrock Quarry',
'email@hidden', julianday('1920-06-01'))
;")
set resultList to SQLExecuteSelect(databasePath, "
select \"Name First\" || ' ' || \"Name Last\", Company, DOB from People where DOB > julianday('1925-01-01')")
set firstPerson to item 1 in resultList
set company to item 2 in firstPerson
end run
You can also find info on various GUI tools for creating and accessing SQLite databases here: