Overview

SQLite is the built-in database engine. You access it using the SQLiteDatabase class. SQLite is an open-source, public-domain embedded database that is used by all types of software. It is lightweight, fast and easy to use. It works great for desktop, web and iOS apps.

Unlike most databases, SQLite does not have a strictly typed columns. You can put data of any type in any column, regardless of the type the column has defined. You can learn more about SQLite by visiting their web site: www.SQLite.org

The Database property

When working with a SQLite database you will want to have a database property available so you can easily use it throughout your app. For desktop and iOS projects you'll likely want this property to be on the App object or in a module. For web projects you usually want it to be on the Session object.

DB As SQLiteDatabase

If that is on the App object you'd refer to it like this:

App.DB

In a module you'd access it like this:

Module1.DB ' If property is protected
DB ' if property is global

And on the Session object in a web project like this:

Session.DB

Creating a database

SQLite databases are single files that typically exist on the same computer as the running app. For desktop apps, the SQLite database is usually in the Application Data folder for the operating system. For web apps, the database often resides alongside the web app itself on the server. In iOS apps, the database is often placed in the Documentation or Caches folder. To work with SQLite databases you use the SQLiteDatabase class.

This desktop code creates a new SQLite database in the Application Data folder and could be on the App.Opening event:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

' DB As SQLiteDatabase is a property on the App object
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
  ' Use the database
End If

If the database already exists, then CreateDatabaseFile will connect to the existing database instead.

Do not place your database file alongside the desktop app itself. The app location is often read-only (especially for non-admin users) which will prevent you from being able to save any changes to the database.

Creating a table

As covered in Database Concepts, tables are used to store data in the database. The following SQL command creates the Team table in the SQLite database:

CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));

SQL commands such as CREATE TABLE are sent to SQLite using the ExecuteSQL method of the SQLiteDatabase class. This code can be used to send the above SQL to SQLite:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
  ' Create the table
  Var SQL As String
  SQL = "CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
  Try
    App.DB.ExecuteSQL(SQL)
  Catch error As DatabaseException
    MessageBox("Error: " + error.Message)
  End Try
End If

Connecting to a database

If you are connecting to a database that already exists, you can instead call the Connect method:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

If dbFile.Exists Then
  App.DB = New SQLiteDatabase
  App.DB.DatabaseFile = dbFile
  If App.DB.Connect Then
    ' Use the database
  End If
End if

Retrieving, adding, changing and deleting data

For information on how to retrieve, add, change or delete data, refer to the Adding, updating and deleting rows topic.

Transactions

As covered in Transactions, a Transaction is a block of processing. With SQLite, by default each command operates as its own implicit transaction. There may be times when you instead want to group many commands into a single transaction. This can be for data integrity purposes, but it also has the benefit of improving performance as well.

For example, if you update 1,000 rows each of which are in their own transaction then the database has to do more work as it has to create and commit a transaction for each row. Instead you can manually start a single transaction, do your 1,000 row updates and then commit once at the end. This is much faster as the database only has to create and update the single transaction. To start a transaction in SQLite, you use the "BEGIN TRANSACTION" SQL command, which you can send using the ExecuteSQL method:

App.DB.ExecuteSQL("BEGIN TRANSACTION;")

Then when you are done, call the Commit method:

App.DB.Commit

Auto-incrementing primary keys

With SQLite, if a table has a single column specified as the INTEGER primary key, then that column auto-increments when a row is added to the table. This column is said to map to the internal rowid column that is on all SQLite tables.

However, just because SQLite has an internal rowid column, you should not rely on it as your primary key. Rowid values can be changed behind the scenes by SQLite and this could possibly corrupt any relationships in your database. Always create a separate primary key for your tables. When you INSERT data into a table with a primary key, you omit the primary key from the INSERT SQL:

INSERT INTO Team (Name)
VALUES ('Seagulls');

The above SQL is sent to SQLite using this code:

' App.DB refers to a connected SQLiteDatabase
App.DB.ExecuteSQL("INSERT INTO Team (Name) VALUES ('Seagulls');)"

After adding a row to the database, you can get the value of the last primary key value by calling the LastRowID method:

Var lastValue As Integer
lastValue = App.DB.LastRowID

Encryption

SQLite databases can be encrypted. An encrypted database cannot be viewed at all unless you know the encryption key.

Encrypting a database

To encrypt a new database, specify a value for the EncryptionKey property before you call CreateDatabaseFile or before you call Connect. This creates a new encrypted database:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
App.DB.EncryptionKey = "MySecretKey123!"
If App.DB.CreateDatabaseFile Then
  ' Use the database
End If

To encrypt an existing database, call the Encrypt method, supplying the encryption key as a parameter:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
  ' Encrypt the database
  App.DB.Encrypt("MySecretKey123!")
End If

You can also use the Encrypt method to change the encryption key of an encrypted database.

Decrypting a database

Remember, you do not need to decrypt a database in order to use it -- just connect using the encryption key as shown above.

To decrypt an encrypted database, call the Decrypt method after you have connected to the database:

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")

App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
App.DB.EncryptionKey = "MySecretKey123!"
If App.DB.CreateDatabaseFile Then
  App.DB.Decrypt ' Decrypt the database
End If

Once you have decrypted the database, you no longer need to use the encryption key to access it.

Multiple user support

SQLite is not technically a multiple user database. But by enabling a feature called Write-Ahead Logging (WAL), you can improve performance when multiple users are accessing the database from the same app.

This is most useful with web apps because they can easily have multiple users connected to the web app, each of which may be connecting to the database.

The SQLite developers do not recommend that you use SQLite on a shared network drive with separate apps sharing it. This can result in data corruption.

Large objects (BLOB)

Large objects in a database are also called BLOBs (Binary Large Objects). You can add large objects to a database using the DatabaseRecord class, but there is a limitation on the amount of available memory.

If you need to store large objects in a database, but want to be able to read the data from the database sequentially, you use the SQLiteBLOB class in conjunction with the CreateBlob and OpenBlob methods on the SQLiteDatabase class:

Var blob As SQLiteBlob
blob = App.DB.OpenBlob("Team", "Logo", 1, True)
If blob <> Nil Then
  ' Read BLOB
  Var data As String
  While Not blob.EOF
    ' Read 1000 bytes at a time
    data = blob.Read(1000)
  Wend

  ' Do something with the data
End If

Attaching other SQLite databases

Normally when you connect to a SQLite database, you are connecting to a single file. With SQLite it is possible to connect to multiple SQLite database files using one connection. You do this by "attaching" the additional databases.

The AttachDatabase method attaches the specified database file and lets you assign a prefix to use for all the tables in the attached database. The DetachDatabase method is to remove the attached database.

dbFile = New FolderItem("ExtraDB.sqlite")
If App.DB.AttachDatabase(dbFile, "extra") Then
  Var rs As RecordSet
  rs = App.DB.SQLSelect("SELECT * FROM extra.Table")
  ' Process results...
End If

Determining the SQLite version

It can sometimes be helpful to know exactly which version of SQLite is being used by your app. You can check this using the LibraryVersion property.

MessageBox(App.DB.LibraryVersion)

Videos

These videos also cover SQLite and its features:

• Using SQLite

• SQLite Features

• Advanced SQLite

• Connecting to Databases

• Database Development

See also

SQLiteDatabase, SQLiteBLOB classes; Database Basics for beginners topic