(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.
