Using SQLite

(The implementation of SQLite was changed by Google in 2024. See Support for SQLite for the latest information.)

SQLite is a fast, popular and easy to use database. Its speed and easy of use make it a practical choice for use when you need an indexed file system for an app. Databases you create can be accessed, without change, on any platform SQLite supports. It is built into mobile devices that AppStudio supports.

If you just need to save some simple data, without needing an index, have a look at using localStorage.

SQLite is a zero-configuration, in-process, SQL database engine. But don't let this description scare you: SQLite is an easy way for your app to store and retrieve data.

Your app can create, update and delete SQLite database tables. The databases are kept in your app's private directory. Generally, an app is not allowed to access databases belonging to other apps for security reasons. However, apps deployed from the same server can share databases. This allows you to create a family of apps which share data, so long as they all come from the same server. The security protects apps from changing or stealing information from other app's databases.

Databases cannot be directly imported or exported, once again, for security reasons. However, it is possible to include an SQLite database with your application. Add the name of the database to the manifest in Properties Window and it will be automatically converted in to a format that can be exported from the server. The app will then import the database automatically when the application starts for the first time. To do this, the SQLExport and SQLImport methods are called internally. These methods are also available for use at any time.

The database is created inside the browser's private storage area. It is not part of the normal file system. This prevents apps from interfering with each other's databases.

There are two sets of commands: the ones AppStudio uses to call SQLite and the SQLite commands themselves. Documentation on AppStudio's commands are in this Tech Note; documentation on SQLite itself is in the SQLite Reference.

Platforms Supported

SQLite is supported on all iOS and Android devices. It is in Chrome, Safari and Brave. It is not supported by other browsers, such as Internet Explorer, Edge or Firefox.

To include the SQLite libraries in web apps, enable SQLite WASM in your [libraries]. If you're making a native app, use the Cordova plugin for SQLite.

Persisting Updates to the Database

If you have an already defined database that you want to include with your application, to ensure that updates to your database are persisted, one needs to ensure that the "manifest" property of the project is properly set. When the database is specified in the manifest property, its uploaded to the application when the application is first run just like the SQLImport statement.

The SQLImport function uses this statement for example, SQLImport(JSON, [DB], [callback], [overwrite]), and your "manifest" property should be in line with this for your updates to be considered when your application is run, an example would be 'mydb.db,dbObj,callBackMethod,NSB.overwriteIfVersionDifferent'.

NB: If on this property only the database name is specified that should be included with the application, any changes made during the application run, whilst they will be reflected in your application, the problem is that the next time you start the application, all your updates will not exist.

Asynchronous Results

The results of calls to the database are returned asynchronously. That means that after a statement involving the database is executed, the next statement in the program is executed - even though the database operation may not be complete. The database operation continues on its own. When it finishes, it will call a function you specify in your program, depending on the success or failure of the operation. In the meantime, you can do other processing: update controls on the screen or do calculations.

Consider the following code:

   MsgBox "Before doing call"
   sqlList="CREATE TABLE myData('name', 'age')"
   sql(DB, [sqlList, Success_Function, Fail_Function])
   Msgbox "After doing call"

   Function Success_Function(transaction, result)
     Msgbox "Success - result received"
   End function

   Function Fail_Function(transaction, result)
     Msgbox "Fail"
   End function

The result will be 3 messages, in this order:

   "Before doing call"
   "After doing call"
   "Success - result received"

Transactions

When doing multiple operations on a database, for example, adding a number of new records, it is much faster to group them into a transaction. A transaction is a list of database operations that are performed as a set: each of the operations on the list has to successful. If any one step fails, the entire transaction can be aborted without change to the database.

A transaction is implemented as an array, with one operation in each element of the array.

Example: Add 3 new records into customerData

   sqlList = []
   sqlList[0]="INSERT INTO customerData (name,age,sales) VALUES ('Haley',16,121)"
   sqlList[1]="INSERT INTO customerData (name,age,sales) VALUES ('Alex',12,80)"
   sqlList[2]="INSERT INTO customerData (name,age,sales) VALUES ('Luke',7,65)"

Each operation in a transaction can have optional success and fail callbacks. In this case, the element in transaction array is itself an array:

[operation, success, fail] , where operation is a string. success and fail are functions in the program. You are required to have at least a success function defined in your program.

Example:

  sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function)

Parameters

Parameters allow you to simplify your coding by putting the arguments into a separate array. It's also a more secure way of handling data from users: they will not be able to do an SQL Injection attack on your database.

To use parameters, put a question mark (?) for each item in the Values clause. Then, add a new item to your SQL operation which contains an array of the values.

    args = ['Haley',16,121]
    sqlList[0]=[ "INSERT INTO customerData (name,age,sales) VALUES (?,?,?)", args]

You will find this method is easier to use, requires less code and is easier to read than putting the values inline.

Opening the database file using SqlOpenDatabase()

SQLite keeps the entire database in a single physical file. Use the SqlOpenDatabase method to open the file. If it does not exist, it is created:

   DB = SqlOpenDatabase("customers.db")
   If DB.version<>"" Then Print "Database Opened"

The return value, DB, will be used to identify the database in subsequent calls. If the database does not exist, it is created. Here is the full syntax of SqlOpenDatabase:

res = SqlOpenDatabase(filename)

where

• filename is the actual name of the file

Create a table in the database

After step 1 creates a new database file, it is empty. A database is made up of tables. A table is simply a collection of records with a defined format. Let's create the table, but first, let's get rid of any existing table with the same name.

  sqlList=[]
  sqlList[0]=["DROP TABLE customerData;",,skipError]
  Sql(DB, sqlList)

  sqlList=[]
  sqlList[0]=["CREATE TABLE customerData('name', 'age', 'sales', PRIMARY KEY('name') );"]
  Sql(DB, sqlList)

We're creating a table with three columns, indexed by the first name: each record therefore has 3 fields. The Sql statement is used to send a transaction (a list of SQL commands) to SQLite. The syntax of Sql is:

 Sql(db, sqlList)

where

• db is the reference to the database returned by SqlOpenDatabase
• sqlList is an SQLite command or an array of SQLite commands.

Add records to the database and select them

  For j = 0 to databaseSize-1
    args=[cust, j, j*10]
    sqlList[j]=["INSERT INTO customerData (name, age, sales) VALUES (?,?,?);", args]
  Next
  sqlList[databaseSize]=["SELECT * from customerData ORDER BY name;", dataHandler]

  Sql(DB, sqlList)

The SELECT command is very powerful - you can use it to collect all kinds of different sets of records. Read more about it in the [SQLite.htm SQL Documentation].

In the SELECT command, we're specifying that we want to run a special function when the SELECT completes. The function datahandler will be called. It gives the result of the SELECT:

  Function dataHandler(transaction, results)
    // Called on completion of SQL command
    DBRecords = results
    message.innerHTML = "Records created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & " milliseconds."
  End Function

In dataHandler, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.

Replace a record in the table

  cmd="INSERT OR REPLACE INTO ""customerData"" VALUES (""" & tbFirstName.text & """,""" & _
    tbLastName.text &""",""" & tbAge.text & """)"
  Sql(DB, cmd)

Once again, all the quotes get resolved in the command. The actual command sent to SQLite is:

  INSERT OR REPLACE INTO "customerData" VALUES("John","Kemeny","80")

Using the last name as the key, the record is added or replaced. Once again, there are many more options on this command: we're just trying to get you started here. Once you have added the record, you'll need to do your selection again. It won't be automatically added to the existing selection.

Update a record

Let's start with the simplest case:

Function HeaderBar1_onclick(button)
  s=Array(["UPDATE Customers SET CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid])
  Sql(DB,s)
End Function

You need to extend it in two ways: update more fields, and update more rows.

To update more fields, add more to the SET clause:

s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])

To update more rows, add a new array element for each row:

s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])
s.push(["UPDATE Customers SET CompanyName='NewCo2', City='New York' WHERE CustomerID='ANATR';", loadGrid])

Delete a record

This is pretty easy. Do not use success or fail routine with DELETE.

  cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """"
  Sql(DB, cmd)

The command sent to SQLite resolves to:

  DELETE FROM customerData WHERE lastname = "Kemeny"

Closing the Database

When you are done using a database, you can close it.

  DB.close

Complete Sample

var databaseSize; //number of records in the database
var DB; //the database itself
var startTime; //used for timer
var lastSQL; //the last SQL statement. Useful for debugging.
var total; //use to total sales from customers
var DBRecords; //the last set of records selected from DB
var iRec; //record number of record being processed, starting with 0
var nTables; //number of tables in the database (when it works)

databaseSize = 22;
btnCreate.value = "Create /" + "\n" + "Open DB";

btnCreate.onclick = function () {
  total = 0;
  initDB();
};

btnCount.onclick = function () {
  total = 0;
  startTime = SysInfo(10);
  for (i = 0; i <= DBRecords.rows.length - 1; i++) {
    total = total + DBRecords.rows.item(i)["sales"];
  }
  txtMessage.value =
    "Total sales of " +
    DBRecords.rows.length +
    " recs is " +
    total +
    " in " +
    (SysInfo(10) - startTime) +
    " milliseconds.";
};

btnRandom.onclick = function () {
  var sqlList;
  startTime = SysInfo(10);
  total = 0;
  sqlList = [];
  for (j = 0; j <= databaseSize - 1; j++) {
    sqlList[j] = [
      "SELECT * FROM customerData WHERE name=?",
      "cust" + jsFix(Rnd() * databaseSize),
      Random_Total,
    ];
  }

  sqlList[databaseSize - 1][2] = Random_Complete;
  Sql(DB, sqlList);
};

function Random_Total(transaction, results) {
  total = total + results.rows.item(0)["sales"];
}

function Random_Complete(transaction, results) {
  txtMessage.value =
    "Total of " +
    databaseSize +
    " random recs is " +
    total +
    " in " +
    (SysInfo(10) - startTime) +
    " milliseconds.";
}

function initDB() {
  var sqlList;
  DB = SqlOpenDatabase("localStorage", "1.0", "My Customer Database");
  if (DB != 0) {
    startTime = SysInfo(10);
    nTables = -1;
    sqlList = [];
    sqlList[0] = [
      "SELECT COUNT(*) AS tabCount FROM sqlite_master WHERE type='table' AND name='customerData';",
      tableExistCheck,
      masterErr,
    ];
    Sql(DB, sqlList);
  }
}

function createDB() {
  // checks if table "customerData" exists
  // if not, drops (just in case), re-creates, and fills the table with data
  var sqlList;
  if (nTables < 1) {
    NSB.MsgBox("Table customerData does not exist");
    sqlList = [];
    sqlList[0] = ["DROP TABLE IF EXISTS customerData;"];
    sqlList[1] = [
      "CREATE TABLE IF NOT EXISTS customerData('name', 'age', 'sales', PRIMARY KEY('name') );",
    ];
    for (j = 0; j <= databaseSize - 1; j++) {
      args = ["cust" + j, j, j * 10];
      sqlList[j + 2] = [
        "INSERT INTO customerData (name, age, sales) VALUES ( ?,?,?);",
        args,
      ];
    }
    // now execute to create the table
    Sql(DB, sqlList);
  } else {
    NSB.MsgBox(
      "DB with table customerData already exists with " +
        nTables +
        " Tables named " +
        '"' +
        "customerData" +
        '"' +
        " "
    );
  }
  // select the data for further processing
  sqlList = [];
  sqlList[0] = ["SELECT * FROM customerData ORDER BY name;", dataHandler];
  Sql(DB, sqlList);
}

function tableCount(transaction, results) {
  var rtc;
  var i, j;
  DBRecords = results;
  nTables = DBRecords.rows.length;
  txtMessage.value = "The database currently has " + nTables + " Tables";
  grMaster.rows = nTables + 1;
  if (nTables > 4) {
    nTables = 4;
  } // because of fixed grid size
  //remove old grid values
  for (i = 1; i <= 4; i++) {
    for (j = 0; j <= 4; j++) {
      grMaster.setValue(i, j, "");
    }
  }

  for (j = 0; j <= nTables - 1; j++) {
    i = j + 1;
    grMaster.setValue(i, 0, DBRecords.rows.item(j)["type"]);
    grMaster.setValue(i, 1, DBRecords.rows.item(j)["name"]);
    grMaster.setValue(i, 2, DBRecords.rows.item(j)["tbl_name"]);
    grMaster.setValue(i, 3, DBRecords.rows.item(j)["rootpage"]);
    grMaster.setValue(i, 4, DBRecords.rows.item(j)["sql"]);
  }
  nsbDOMAttr(form1, "style.display", "none");
  nsbDOMAttr(form2, "style.display", "block");
}

function tableExistCheck(transaction, results) {
  var rtc;
  NSB.MsgBox("tableExistCheck");
  DBRecords = results;
  nTables = DBRecords.rows.item(0)["tabCount"];
  txtMessage.value =
    "The database contains " + nTables + " tables with the name specified";
  rtc = createDB();
}

function masterErr(transaction, results) {
  // called in Case of Error
  NSB.MsgBox("Error: " + results.message);
  debugger;
}

function dataHandler(transaction, results) {
  // Called On completion of Sql command
  DBRecords = results;
  txtMessage.value =
    "Recs in DB created/read: " +
    DBRecords.rows.length +
    " in " +
    (SysInfo(10) - startTime) +
    " milliseconds.";
}

function skipError(transaction, results) {
  // Called On failure of Sql command
  NSB.MsgBox("skipError");
  NSB.MsgBox("results = " + results);
}

btnRead.onclick = function () {
  var sqlList;
  var i, j;
  for (i = 1; i <= 5; i++) {
    for (j = 0; j <= 3; j++) {
      Grid1.setValue(i, j, CStr(i) + ":" + CStr(j));
    }
  }
  sqlList = [];
  sqlList[0] = [
    "SELECT name, age, sales from customerData ORDER BY name;",
    [],
    resultToGrid,
  ];
  iGridStart = 1;
  iRec = 0;
  DBRecords = [];
  NSB.WaitCursor(true);
  Sql(DB, sqlList);
};

function resultToGrid(transaction, results) {
  console.log("result");
  // Called On completion of Sql command in Function btnRead_onclick
  DBRecords = results;
  NSB.WaitCursor(false);
  iRec = 0;
  btnShowNext.onclick();
}

btnShowNext.onclick = function () {
  var i, j;

  //Clear grid before reading Next group of records
  for (i = 1; i <= 5; i++) {
    for (j = 0; j <= 3; j++) {
      Grid1.setValue(i, j, "");
    }
  }
  //fill grid With Next group of 5 records
  for (i = 1; i <= 5; i++) {
    if (iRec < DBRecords.rows.length) {
      Grid1.setValue(i, 0, iRec);
      Grid1.setValue(i, 1, DBRecords.rows.item(iRec)["name"]);
      Grid1.setValue(i, 2, DBRecords.rows.item(iRec)["age"]);
      Grid1.setValue(i, 3, DBRecords.rows.item(iRec)["sales"]);
      iRec = iRec + 1;
    }
  }
};

btnTotalSales.onclick = function () {
  startTime = SysInfo(10);
  sqlList = [];
  sqlList[0] = [
    "SELECT SUM(sales) AS TotalSales, AVG(sales) AS AvgSales FROM customerData;",
    sumSales,
  ];
  Sql(DB, sqlList);
};

function sumSales(transaction, results) {
  //Called On completion of Sql command in Function btnTotalSales_onclick
  txtMessage.value =
    "Total Sales are " +
    results.rows.item(0)["TotalSales"] +
    " , Average: " +
    results.rows.item(0)["AvgSales"] +
    " in " +
    (SysInfo(10) - startTime) +
    " ms.";
}

btnDrop.onclick = function () {
  var sqlList;
  NSB.MsgBox("Table customerData will be dropped");
  sqlList = [];
  sqlList[0] = ["DROP TABLE customerData;", dropExec, dropError];
  Sql(DB, sqlList);
};

function dropExec(transaction, result) {
  DBRecords = [];
  NSB.MsgBox("Table customerData dropped");
}

function dropError() {
  NSB.MsgBox("error in drop statement");
}

btnOpen.onclick = function () {
  total = 0;
  DB = SqlOpenDatabase("customerdb.db", "1.0", "My Customer Database");
};

Dim databaseSize  'number of records in the database
Dim DB            'the database itself
Dim startTime     'used for timer
Dim lastSQL       'the last SQL statement. Useful for debugging.
Dim total         'use to total sales from customers   
Dim DBRecords     'the last set of records selected from DB
Dim iRec          'record number of record being processed, starting with 0
Dim nTables       'number of tables in the database (when it works)


databaseSize=22
btnCreate.value = "Create /" & vbCRLF & "Open DB"

Function btnCreate_onclick()
  total=0
  initDB
End Function

Function btnCount_onclick()
  total=0
  startTime=SysInfo(10)
  For i=0 To DBRecords.rows.length-1
    total=total + DBRecords.rows.item(i)["sales"]
  Next
  txtMessage.value = "Total sales of " & DBRecords.rows.length & " recs is " & total & " in " & (SysInfo(10)-startTime) & " milliseconds."
End Function

Function btnRandom_onclick()
  Dim sqlList
  startTime=SysInfo(10)
  total=0
  sqlList = []
  For j=0 To databaseSize-1
    sqlList[j]=["SELECT * FROM customerData WHERE name=?", "cust" & Fix(Rnd * databaseSize), Random_Total]
  Next
  
  sqlList[databaseSize-1][2]=Random_Complete
  Sql(DB,sqlList)
End Function

Function Random_Total(transaction, results)
  total = total + results.rows.item(0)["sales"]
End Function

Function Random_Complete(transaction, results)
  txtMessage.value =  "Total of " & databaseSize & " random recs is " & total & " in " & (SysInfo(10)-startTime) & " milliseconds."
End Function

Function initDB()
  Dim sqlList
  DB = SqlOpenDatabase("localStorage","1.0","My Customer Database")
  If DB<>0 Then
    startTime=SysInfo(10)
    nTables = -1
    sqlList=[]
    sqlList[0]=["SELECT COUNT(*) AS tabCount FROM sqlite_master WHERE type='table' AND name='customerData';",tableExistCheck,masterErr]
    Sql(DB, sqlList)
  End If
End Function

Function createDB()
  ' checks if table "customerData" exists
  ' if not, drops (just in case), re-creates, and fills the table with data
  Dim sqlList
  If nTables < 1 Then
    MsgBox "Table customerData does not exist"
    sqlList=[]
    sqlList[0]=["DROP TABLE IF EXISTS customerData;"]
    sqlList[1]=["CREATE TABLE IF NOT EXISTS " & "customerData('name', 'age', 'sales', PRIMARY KEY('name') );"]
    For j = 0 To databaseSize-1
      args=["cust" & j, j, j*10]
      sqlList[j+2]=["INSERT INTO customerData (name, age, sales) VALUES ( ?,?,?);", args]
    Next
    ' now execute to create the table
    Sql(DB, sqlList)
  Else
    MsgBox "DB with table customerData already exists with " & nTables & " Tables named ""customerData"" "
  End If
  ' select the data for further processing
  sqlList=[]
  sqlList[0]=["SELECT * FROM customerData ORDER BY name;", dataHandler]
  Sql(DB, sqlList)  
End Function

Function tableCount(transaction, results)
  Dim rtc
  Dim i,j
  DBRecords = results
  nTables = DBRecords.rows.length
  txtMessage.value = "The database currently has " & nTables & " Tables"
  grMaster.rows = nTables + 1
  If nTables > 4 Then nTables = 4       ' because of fixed grid size
  Rem remove old grid values
  For i=1 To 4
    For j=0 To 4
      grMaster.setValue(i,j,"")
    Next j
  Next i
  
  For j=0 To nTables - 1
    i = j + 1
    grMaster.setValue(i,0,DBRecords.rows.item(j)["type"])
    grMaster.setValue(i,1,DBRecords.rows.item(j)["name"])
    grMaster.setValue(i,2,DBRecords.rows.item(j)["tbl_name"])
    grMaster.setValue(i,3,DBRecords.rows.item(j)["rootpage"])
    grMaster.setValue(i,4,DBRecords.rows.item(j)["sql"])
  Next j
  form1.style.display = "none"
  form2.style.display = "block"
End Function

Function tableExistCheck(transaction, results)
  Dim rtc
  MsgBox "tableExistCheck"
  DBRecords = results
  nTables = DBRecords.rows.item(0)["tabCount"]
  txtMessage.value = "The database contains " & nTables & " tables with the name specified"
  rtc = createDB()
End Function

Function masterErr(transaction, results)
  ' called in Case of Error
  MsgBox "Error: " & results.message
  debugger
End Function

Function dataHandler(transaction, results)
  ' Called On completion of Sql command
  DBRecords = results
  txtMessage.value =  "Recs in DB created/read: " & DBRecords.rows.length & " in " & (SysInfo(10)-startTime) & " milliseconds."
End Function

Function skipError(transaction, results)
  ' Called On failure of Sql command
  MsgBox "skipError"
  MsgBox "results = " & results
End Function 

Function btnRead_onclick()
  Dim sqlList
  Dim i, j
  For i=1 To 5
    For j=0 To 3
      Grid1.setValue(i,j,CStr(i) & ":" & CStr(j))
    Next j
  Next i
  sqlList=[]
  sqlList[0]=["SELECT name, age, sales from customerData ORDER BY name;", [], resultToGrid]
  iGridStart = 1
  iRec = 0
  DBRecords=[]
  WaitCursor True
  Sql(DB, sqlList)
End Function

Function resultToGrid(transaction, results)
  console.log("result")
  ' Called On completion of Sql command in Function btnRead_onclick
  DBRecords = results
  WaitCursor False
  iRec=0
  btnShowNext_onclick()
End Function

Function btnShowNext_onclick()
  Dim i, j
  
  Rem Clear grid before reading Next group of records
  For i=1 To 5
    For j=0 To 3
      Grid1.setValue(i,j,"")
    Next j
  Next i
  Rem fill grid With Next group of 5 records
  For i=1 To 5
    If iRec<DBRecords.rows.length Then
      Grid1.setValue(i,0,iRec)
      Grid1.setValue(i,1,DBRecords.rows.item(iRec)["name"])
      Grid1.setValue(i,2,DBRecords.rows.item(iRec)["age"])
      Grid1.setValue(i,3,DBRecords.rows.item(iRec)["sales"])
      iRec = iRec + 1
    End If
  Next i
End Function

Function btnTotalSales_onclick()
  startTime = SysInfo(10)
  sqlList=[]
  sqlList[0]=["SELECT SUM(sales) AS TotalSales, AVG(sales) AS AvgSales FROM customerData;", sumSales]
  Sql(DB, sqlList)
End Function

Function sumSales(transaction, results)
  'Called On completion of Sql command in Function btnTotalSales_onclick
  txtMessage.value = "Total Sales are " & results.rows.item(0)["TotalSales"] & " , Average: " & results.rows.item(0)["AvgSales"] & " in " & (SysInfo(10)-startTime) & " ms."
End Function

Function btnDrop_onclick()
  Dim sqlList
  MsgBox "Table customerData will be dropped"
  sqlList=[]
  sqlList[0]=["DROP TABLE customerData;",dropExec,dropError]
  Sql(DB, sqlList)
End Function

Function dropExec(transaction, result)
  DBRecords=[]
  MsgBox "Table customerData dropped"
End Function

Function dropError
  MsgBox "error in drop statement"
End Function

Function btnOpen_onclick()
  total=0
  DB = SqlOpenDatabase("customerdb.db","1.0","My Customer Database")
End Function

</pre>

Debugging

Use the SQLDump command in the Chrome Console to see the contents of a database table.