How-To Guide ⏱ ~30 minutes

Working with SQLite Databases

SQLite is a lightweight, file-based database that requires no server and no configuration — perfect for storing structured data in a HyperXTalk app. This guide walks you through everything from opening a database to performing full CRUD operations.

What you'll cover

Opening or creating a SQLite database file
Creating a table with SQL
Inserting, querying, updating, and deleting records
Iterating through query results into an array
Closing the connection cleanly

Opening a Database

Use revOpenDatabase to open a SQLite file. If the file doesn't exist, SQLite creates it automatically. The function returns a database ID — a number you'll use in every subsequent call. If it returns something that isn't a number, the connection failed.

stack script
function openDatabase pFilePath
   local tDatabaseID

   put revOpenDatabase("sqlite", pFilePath) into tDatabaseID

   if tDatabaseID is not a number then
      throw "openDatabase error:" && tDatabaseID
   end if

   return tDatabaseID
end openDatabase

Call it with the full path to your database file:

local tDB, tPath
put specialFolderPath("documents") & "/myapp.db" into tPath
put openDatabase(tPath) into tDB

🟢 Store the database ID in a script-local or global variable so you can reuse it across handlers without reopening the connection each time.

Creating a Table

HyperXTalk has two commands for running SQL. Use revQueryDatabase for SELECT statements — it returns a cursor you iterate through. Use revExecuteSQL for everything else (CREATE, INSERT, UPDATE, DELETE) — it runs the statement and sets the result to an error message if something went wrong, or empty on success.

For a CREATE TABLE, use IF NOT EXISTS so it's safe to run every time the stack opens — it only creates the table if it isn't already there.

stack script
on createTable pDB
   local tSQL, tResult

   put "CREATE TABLE IF NOT EXISTS contacts (" into tSQL
   put tSQL & "id INTEGER PRIMARY KEY AUTOINCREMENT," into tSQL
   put tSQL & "name TEXT NOT NULL," into tSQL
   put tSQL & "email TEXT," into tSQL
   put tSQL & "phone TEXT)" into tSQL

   revExecuteSQL pDB, tSQL

   if the result is not empty then
      throw "createTable error:" && the result
   end if
end createTable

Inserting Records

Build your INSERT statement as a string and pass it to revQueryDatabase. Always close the cursor after a non-SELECT query:

stack script
on insertContact pDB, pName, pEmail, pPhone
   local tSQL, tResult

   put "INSERT INTO contacts (name, email, phone) VALUES ('" into tSQL
   put tSQL & pName & "','" & pEmail & "','" & pPhone & "')" into tSQL

   revExecuteSQL pDB, tSQL

   if the result is not empty then
      throw "insertContact error:" && the result
   end if
end insertContact

⚠️ The example above builds SQL by concatenating strings directly. This is fine for internal apps, but if any value comes from user input, sanitise it first to avoid SQL injection — replace any single quotes in the value with two single quotes: replace "'" with "''" in tValue.

Querying Records

SELECT queries return a cursor ID. You iterate through the results row by row using revQueryIsAtEnd and revMoveToNextRecord, reading each column with revDatabaseColumnNamed. The results are stored in a two-dimensional array indexed by row number and column name:

stack script
function fetchAllContacts pDB
   local tCursorID, tColumnNames, tResults, tRow

   put revQueryDatabase(pDB, "SELECT * FROM contacts ORDER BY name ASC") into tCursorID

   if tCursorID is not a number then
      throw "fetchAllContacts error:" && tCursorID
   end if

   put revDatabaseColumnNames(tCursorID) into tColumnNames
   put 0 into tRow

   repeat until revQueryIsAtEnd(tCursorID)
      add 1 to tRow
      repeat for each item tColumn in tColumnNames
         put revDatabaseColumnNamed(tCursorID, tColumn) into tResults[tRow][tColumn]
      end repeat
      revMoveToNextRecord tCursorID
   end repeat

   revCloseCursor tCursorID
   return tResults
end fetchAllContacts

Access the results like this:

local tContacts, i
put fetchAllContacts(tDB) into tContacts

repeat with i = 1 to the number of elements of tContacts
   put tContacts[i]["name"] & " — " & tContacts[i]["email"] & return after field "Results"
end repeat

Updating Records

stack script
on updateContact pDB, pID, pName, pEmail, pPhone
   local tSQL, tResult

   put "UPDATE contacts SET name='" & pName into tSQL
   put tSQL & "', email='" & pEmail into tSQL
   put tSQL & "', phone='" & pPhone & "' WHERE id=" & pID into tSQL

   revExecuteSQL pDB, tSQL

   if the result is not empty then
      throw "updateContact error:" && the result
   end if
end updateContact

Deleting Records

stack script
on deleteContact pDB, pID

   revExecuteSQL pDB, "DELETE FROM contacts WHERE id=" & pID

   if the result is not empty then
      throw "deleteContact error:" && the result
   end if
end deleteContact

Closing the Database

Always close the database when you're done — typically in a closeStack handler:

stack script
on closeStack
   if sDatabaseID is a number then
      revCloseDatabase sDatabaseID
   end if
end closeStack

💡 Note the use of sDatabaseID — the s prefix is a convention for script-local variables, declared at the top of the stack script with local sDatabaseID. This keeps the database ID available across all handlers without making it global.

Putting It All Together

Here's a complete stack script tying everything together — opening the database when the stack opens, creating the table if needed, and closing cleanly on exit:

stack script
local sDatabaseID

on openStack
   local tPath
   put specialFolderPath("documents") & "/contacts.db" into tPath

   try
      put openDatabase(tPath) into sDatabaseID
      createTable sDatabaseID
   catch tError
      answer "Database error:" && tError
   end try
end openStack

on closeStack
   if sDatabaseID is a number then
      revCloseDatabase sDatabaseID
   end if
end closeStack

-- Open the database
function openDatabase pFilePath
   local tDatabaseID
   put revOpenDatabase("sqlite", pFilePath) into tDatabaseID
   if tDatabaseID is not a number then
      throw "openDatabase error:" && tDatabaseID
   end if
   return tDatabaseID
end openDatabase

-- Create the table
on createTable pDB
   local tSQL, tResult
   put "CREATE TABLE IF NOT EXISTS contacts (" into tSQL
   put tSQL & "id INTEGER PRIMARY KEY AUTOINCREMENT," into tSQL
   put tSQL & "name TEXT NOT NULL, email TEXT, phone TEXT)" into tSQL
   revExecuteSQL pDB, tSQL
   if the result is not empty then
      throw "createTable error:" && the result
   end if
end createTable

-- Insert a contact
on insertContact pDB, pName, pEmail, pPhone
   local tSQL, tResult
   put "INSERT INTO contacts (name, email, phone) VALUES ('" into tSQL
   put tSQL & pName & "','" & pEmail & "','" & pPhone & "')" into tSQL
   revExecuteSQL pDB, tSQL
   if the result is not empty then
      throw "insertContact error:" && the result
   end if
end insertContact

-- Fetch all contacts
function fetchAllContacts pDB
   local tCursorID, tColumnNames, tResults, tRow
   put revQueryDatabase(pDB, "SELECT * FROM contacts ORDER BY name ASC") into tCursorID
   if tCursorID is not a number then
      throw "fetchAllContacts error:" && tCursorID
   end if
   put revDatabaseColumnNames(tCursorID) into tColumnNames
   put 0 into tRow
   repeat until revQueryIsAtEnd(tCursorID)
      add 1 to tRow
      repeat for each item tColumn in tColumnNames
         put revDatabaseColumnNamed(tCursorID, tColumn) into tResults[tRow][tColumn]
      end repeat
      revMoveToNextRecord tCursorID
   end repeat
   revCloseCursor tCursorID
   return tResults
end fetchAllContacts

🟢 The same patterns work for MySQL and PostgreSQL — just change "sqlite" to "mysql" or "postgresql" in revOpenDatabase and provide the host, database name, username, and password instead of a file path.