Database Access Using SQLite

Creating a Database

There are two ways you can go about creating a SQLite database:

1. Create an in-memory database with a lifespan that only extends as far as the runtime of the app.
2. Create a database file that can be stored and accessed at any time.

Since you almost certainly want to store data for future access, this tutorial will only discuss the second method.

This example shows how to open a saved database file and/or create a database file that does not yet exist:

-- Require the SQLite library
local sqlite3 = require( "sqlite3" )

-- Create a file path for the database file "data.db"
local path = system.pathForFile( "data.db", system.DocumentsDirectory )

-- Open the database for access
local db = sqlite3.open( path )

Creating a Table

Some common terms you’ll hear when working with SQLite databases include tables (not Lua tables, but SQL tables), columns, and rows. Basically, SQL tables can be thought of as “categories” of data. Each table, in turn, can have multiple columns which can be thought of as “properties” of the table, for example UserID, FirstName, LastName, etc. Finally, the individual “records” which are inserted into tables are known as rows.

Rows — and more specifically their properties — are the actual data you’ll most commonly be working with, but before we can add rows, we must set up a table with specific columns:

local tableSetup = [[CREATE TABLE IF NOT EXISTS test ( UserID INTEGER PRIMARY KEY autoincrement, FirstName, LastName );]]
db:exec( tableSetup )

In the above code, tableSetup is a string that represents an SQL query — basically, a command that tells the database what to do. In this case, we will create a table called test with three columns:

1. UserID
2. FirstName
3. LastName

Following that, we simply “execute” the query on the database object we created above (db).

Creating New Rows

Creating new rows is accomplished via the INSERT statement. First, we’ll illustrate the basic usage, then we’ll go over a more dynamic example.

local insertQuery = [[INSERT INTO test VALUES ( NULL, "John", "Smith" );]]
db:exec( insertQuery )

This example is rather straightforward:

• In the previous code, we created a table called test and now we “insert into” that test table.

• For the row’s values, we list them within parentheses (()) following the VALUES term in the same order as the declaration of columns when we created the table. This consistent ordering is very important!

Lua Table to SQL Table

Now, let’s get creative with a more dynamic example. The following code will insert three rows into the SQL table (test) based on values extracted from a Lua table (this assumes that you’ve already created the database and the test table).

local people = {
    {
        FirstName = "John",
        LastName = "Smith",
    },
    {
        FirstName = "James",
        LastName = "Nelson",
    },
    {
        FirstName = "Tricia",
        LastName = "Cole",
    },
}

for i = 1,#people do
    local q = [[INSERT INTO test VALUES ( NULL, "]] .. people[i].FirstName .. [[","]] .. people[i].LastName .. [[" );]]
    db:exec( q )
end

Updating Existing Rows

You won’t always need to create a new row — in fact, you’ll often need to update a row that already exists. In the following example, we’ll assume that the three rows from the previous example are already inserted into the test table.

local q = [[UPDATE test SET FirstName="Trisha" WHERE UserID=3;]]
db:exec( q )

Essentially, this query finds the row where UserID (the primary key) equals 3 and changes the FirstName value to Trisha. While you don’t necessarily need to use the primary key column to find the row, it’s often the easiest way to locate a specific row since it will always be unique.

Deleting a Row

The SQL query for deleting a row looks very similar to the query we used to update a row, with the primary difference being the use of DELETE FROM rather than UPDATE. The following example removes the John Smith row from the test table:

local q = [[DELETE FROM test WHERE UserID=1;]]
db:exec( q )

Retrieving Data

There are several ways to retrieve data from an SQL database. Sometimes you’ll want a single specific row, while other times you might need all of the rows in a specific table. In other instances, to narrow it down slightly, you might want only a subset of rows in a certain table based on specific criteria. All of this (and more) is possible with SQLite!

The following example illustrates how to load an existing database from a file and populate a Lua array from the rows of a specific query. This assumes that data.db has our 3-person table (test) included within it:

-- Require the SQLite library
local sqlite3 = require( "sqlite3" )

-- Create a file path for the database file "data.db"
local path = system.pathForFile( "data.db", system.DocumentsDirectory )

-- Open the database for access
local db = sqlite3.open( path )

-- Create empty "people" table
local people = {}

-- Loop through database table rows via a SELECT query
for row in db:nrows( "SELECT * FROM test" ) do

    print( "Row:", row.UserID )

    -- Create sub-table at next available index of "people" table
    people[#people+1] =
    {
        FirstName = row.FirstName,
        LastName = row.LastName
    }
end

The most important point of focus is line 14 where we execute a SQL SELECT statement and return an iterator via the nrows() method to be used in conjunction with a for loop, making it easy to go through all of the rows which were found. In this example, we simply copy the data over to the people array so that we can use it later in the app.

Closing the Database

When you’re finished accessing a database, it’s very important that you close the “connection” to it by calling the close() method on the database object, for example db:close():

if ( db and db:isopen() ) then
    db:close()
end

Of course this code must exist in the correct scope of the database object you’re closing — the db object in the example above — so that Lua understands which database it should close.

Conclusion

That wraps up the essentials of database access in Corona. However, we’ve only scratched the surface of what’s possible with SQLite, so we encourage you to explore further to discover what’s possible, starting with our SQLite documentation.