Posted on by

In the previously published JSON tutorial, I mentioned that JSON is good for storing similar types of information easily, since a decoded JSON string in Corona comes back as a Lua table.

So when would you use JSON over databases, and vice-versa? Both are powerful features, sure, but they have their differences. They should be leveraged depending on the needs of your app—Let the best tool for the job win.

The basic rule of thumb I use when deciding which one to use is that if it’s a large amount of data (and especially if the data needs to be searched or sorted in any way within my app), then I’d choose a database every time, hands down. For smaller sets of data, such as tables that store configuration data or cases where I simply need to save a Lua table into a file to read back in later, I choose JSON because of its simplicity.

Or, get the best of both worlds…

Many times, when dealing with an app that will potentially store lots of data, I end up using both SQLite and JSON together. Since JSON objects are just strings (e.g. “blobs”), you can store JSON blobs in a database, load them later into a Lua variable, and then use the JSON library to convert that string into a table. In other words, JSON takes care of the smaller data chunks, and the database organizes all the JSON blobs within a larger data structure.

With JSON, you also have the advantage of having your strings automatically escaped (because things like quote characters can be problematic when inserting data into an SQL table)—more on that later.

In this tutorial, I’m going to walk you through creating a database, saving it to a file, storing information, as well as retrieving that data for use in a Corona app.

Creating a database

There are two ways you can go about creating a database. You can create a database in-memory, in which the life of your database only extends as far as the runtime of your app, or you can create a database file so it can be loaded at any time. Since you most-likely want to keep your data, this tutorial is not going to cover in-memory databases.

The example below is how you would open a saved database file, and/or create a database file (that does not yet exist):


require "sqlite3"

local path = system.pathForFile( “data.db”, system.DocumentsDirectory )
local db = sqlite3.open( path )

NOTE: The recommended location for creating a database is system.DocumentsDirectory (as shown in the example). This will ensure that you are able to read/write to your database, and that it is in a safe location. Your project resource directory is not writable, and temp/cache directories are periodically wiped clean by the OS.

Going back to the example, the first line makes SQLite functions availiable via the sqlite3 namespace. The last line is where we use sqlite3.open() to load the database file at a specific path (or create it there if it does not exist). From that point on, the database is accessible via the db object that was created.

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. Tables can be thought of as “Categories” of data. I mentioned earlier that in a notes app, you might have separate tables to hold app settings, user settings, and user-created notes.

Each SQL table can have multiple columns, which can be thought of as properties (e.g. id, name, description, website, etc. are some examples). The individual “items” that 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 have rows, we must set up a table with specific columns.


local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY autoincrement, name, description, website);]]
db:exec( tablesetup )

The high-level overview of the above code is that tablesetup is a string that represents an “SQL Query” (basically, a command that tells our database what do to). Then, we “execute” that query on the database object we created (db). In this case, we created a table (called “test”) with four columns.

  • id (primary key that will auto-increment upon inserting new rows)
  • name
  • description
  • website

NOTE 1: The first column in a table is commonly an ‘id’ column that is set to be the primary key (all tables should have a primary key). The autoincrement flag simply tells the table to automatically increment to the next id number when a new row is incremented (so you don’t have to specify an id manually whenever inserting a new row).

NOTE 2: Notice how the query string is wrapped in double brackets (‘[[' and ']]‘) instead of quotes. This is due to the fact that it is very much possible to use both single and double quotes in an SQL query, so using brackets is the safest option.

Now, if we were to insert individual rows into the above SQL table, all rows would have an id, name, description, and website property (which can all have different values). As I mentioned a moment ago, columns can be thought of as the individual properties of the rows in a table.

Inserting new rows into a table

Creating new rows is accomplished via the “INSERT” statement. First, I’ll show you the basic usage, then I’ll go over a more dynamic example.


local insertQuery = [[INSERT INTO test VALUES (NULL, 'John Doe','This is an unknown person.', 'http://www.example.com/'); ]]
db:exec( insertQuery )

The example is pretty straight-forward, but allow me to go over it just to make sure you understand exactly what’s going on. In the previous example, we created a table called “test”. In the above example, we are “inserting into” the test table we created.

Notice how the values (in the parenthesis) correspond exactly (and in the same order) to the columns we specified when we created the table—this is very important.

  • id → NULL
  • name → John Doe
  • description → This is an unknown person.
  • website → http://www.example.com/

Remember how I mentioned that id will auto-increment to the next number because of the autoincrement flag we specified? This is why we’re able to pass NULL instead of an actual id number when creating the row. Since the column “auto”-increments, you don’t have to worry about it.

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


local people =
{
{
name = "John Doe",
description = "This is an unknown person.",
website = "http://www.example.com/"
},
{
name = "Jane Doe",
description = "The wife of an unknown person.",
website = "http://www.example2.com/"
},
{
name = "Oscar",
description = "Green guy lives in trash can.",
website = "http://www.example3.com/"
}
}

for i=1,#people do
local q = [[INSERT INTO test VALUES (NULL, ']] .. people[i].name .. [[',']] .. people[i].description .. [[', ']] .. people[i].website .. [['); ]]
db:exec( q )
end

NOTE: If the strings you are inserting into row columns contain quotes, you should “escape” them by placing backslashes before the quote character before inserting them into the columns to prevent errors from occurring.

Updating existing rows

You won’t always need to create a new row, in fact, most of the time you’ll probably be updating rows that already exist. In the following example, we’re going to assume we have the three rows (from the previous example) already inserted into our table (test):


local q = [[UPDATE test SET name='Big Bird' WHERE id=3;]]
db:exec( q )

What the above query does is find the row that matches ‘id’ (in this case, 3), and changes the ‘name’ value to “Big Bird”. In our example, we changed the third row (which had a name value of “Oscar”) to “Big Bird”. You don’t have to use the ‘id’ column to find the row, but it is often the easiest way to narrow down a specific row (since it will always be unique).

Deleting a row from an SQL table

The SQL query for deleting rows looks very similar to what we used to update rows, the primary difference being the use of “DELETE” (rather than “UPDATE”). The following example removes the “John Doe” row from the test table:


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

Retrieving data with “SELECT”

There are several ways to retrieve data from an SQL database. Sometimes you want a single, specific row. Or, you might want all the rows in a specific table. Or to narrow it even further, you might want only a subset of rows in a certain table that all match a specific criteria. All this (and more) is possible with SQLite.

I won’t be able to go over everything, but I’ll show you a complete example of how to load an existing database from a file and populate a Lua array from the rows of a specific query. The following example assumes data.db has our 3-person table (test) included within it:


require "sqlite3"

local path = system.pathForFile( “data.db”, system.DocumentsDirectory )
local db = sqlite3.open( path )

local people = {} — starts off emtpy

for row in db:nrows(“SELECT * FROM test”) do
print( “Row ” .. row.id )

– create table at next available array index
people[#people+1] =
{
name = row.name,
description = row.description,
website = row.website
}
end

The most important line is 8, where we execute a “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 the rows that were found and do something with them. In the example, we simply copied the data over to an array that we can use later in our app.

Read this article for more information on “SELECT”, including much more ways to narrow down specific data.

Closing the database

When you’re finished with you’re database, it’s important to close the “connection” to it by using the close() method (of your database object, so it would something like: db:close()). You can do this at any time (as long as you don’t foresee yourself needing the database anymore), but I recommend calling this function in conjunction with an “applicationExit” system event. This will ensure the database connection is closed when your app is closed.


local function onSystemEvent( event )
if event.type == "applicationExit" then
if db and db:isopen() then
db:close()
end
end
end
Runtime:addEventListener( "system", onSystemEvent )

The above code can be anywhere in your app, but must be within the scope of the database object you are closing (the db object, in the example above).

Further Reading

And that’s the gist of database access in Corona! Keep in mind, however, that I only barely scratched the surface of what’s possible with SQLite and Corona, so I encourage you to explore on your own. There are tons of SQLite + Lua resources on the internet, so I recommend you do more digging to see how you can make the most of this feature in your app.

To start, you should become familiar with the different types of “queries” you can execute. There are far too many possibilities to go over in a single tutorial, and what I covered here is good enough to get you started, but you should definitely learn more.

For a more in-depth tutorial on SQLite and the different types of queries you can execute, see the SQLite tutorial at zetcode.com.


Posted by . Thanks for reading...

21 Responses to “Database Access Using SQLite”

  1. John

    Jon, Fantastic! Now I need to make some mods to take advantage of this JSON and SQlite combo.

    Reply
  2. David

    I’ve been a web developer for a long time, so I appreciate DB integration.. lol

    Speaking of which, there’s a third-party module called “Ice” that’s a great accompaniment to this topic.

    Also, in the spirit of Corona (making things super easy for non-programmers) it might be cool to see built-in functionality for data storage. Things like “system.saveState()”, “system.thawState()”, “system.storeTable(tablename)”, etc – things like that. It’d be especially practical for occasions where the user is pausing the game, or if the user hits the device home button in the middle of a level.

    Just a thought. Keep up the great work, all. =)

    Reply
  3. KS

    This is great! Thanks for sharing this information. Echoing @David’s comments and taking it even further… I hope Corona will add data awareness to its current set of UI elements to the point where we can consider it the VB of Mobile App development!

    Reply
  4. David

    @KS: The idea that people would consider this “the VB of Mobile App development” is probably unflattering and might make the creators of Corona cringe. lol

    Reply
  5. Greimer

    I’m trying to use the count() but it always returns a nil value. Not sure if this function works in corona. I have the following codes:

    local path = system.pathForFile( “data.db”, system.DocumentsDirectory )
    local db = sqlite3.open( path )

    local tablesetup = [[CREATE TABLE IF NOT EXISTS playerscore (id INTEGER PRIMARY KEY autoincrement, name, highscore);]]
    db:exec(tablesetup)

    db:exec[[
    SELECT COUNT(highscore) AS scoreCount FROM playerscore
    ]]

    print(scoreCount)

    I also tried this code for the count() but still didn’t work.

    local scoreCount = [[SELECT COUNT(highscore) FROM playerscore ; ]]
    db:exec(scoreCount)

    print(scoreCount).

    The table has existing 482 records.

    Please advise. Thanks.

    Reply
  6. Jonathan Beebe

    @Greimer: Try this:


    local row = db:first_row( "SELECT count(highscore) AS count FROM playerscore" )
    local scoreCount = row.count

    print( scoreCount )

    For future reference, db:exec() only returns whether the query execution was successful or not.

    Reply
    • Jerry Palmroos

      For the record, if the table has no records, this code will generate an error:

      attempt to call method ‘first_row’ (a nil value)

      I’ve looked everywhere and can’t find a simple way to check if a record exists or not.

      Regards,
      Jerry Palmroos
      Charlotte

      Reply
      • tangoeurop

        local rowCnt = 0
        for row in db:nrows( “SELECT count(id) AS cnt FROM tbl_job_head group by id” ) do
        rowCnt = rowCnt+1
        end;

        if (rowCnt == 0) then
        print( “>>>>>> Table is empty” )
        else
        print( “>>>>>> Table have”.. rowCnt ..” rows” )
        end;

        Reply
  7. sXc

    First off this is tutorial is great! I was wonder if it would be possible to update the SQLite DB using a textField or textBox input. Say I wanted to store my first and last name, age, weight, height, and so on and then simply click a save button. Would this be difficult?

    Reply
  8. Damir

    I need to open the database created in my Corona game with an app created in native Java.

    Managed to open it with:

    SQLiteDatabase myDataBase = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS | SQLiteDatabase.OPEN_READWRITE );

    but can’t update a table.

    The error is:

    05-01 23:04:31.890: E/AndroidRuntime(5257): Caused by: android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database

    Why is the database readonly ?

    This is all happening on an Android tablet.

    Reply
  9. Michael Dilday

    Do you have a manual in PDF format that includes all of the documentation in one place?

    Reply
  10. Laimonas Turauskas

    Thank you for this great article, but that link for in-depth tutorial was the most important thing. I was trying to figure out how to insert a new column into already existing table and answer was right in there! Thanks again you saved hours of internet browsing!

    Reply
  11. Jose

    Can I create a database in a server? The idea if the use of a worldwide database for the ranking or best results of game.

    Thanks

    Reply
  12. on

    I am new to corona and development. I am want to my application to use the data already populated in the database.

    How can I create a new database or access an existing one to edit the data in it.
    How do I access system.DocumentsDirectory and the data.db in it for editing

    Thanks in advance.

    Reply
  13. Simran

    Hello,

    Thanks for the tutorial.Can you please help me with this.I am fairly new to Lua.
    I have it connected to Sql server management studio and I want to check the value of a column in my if statement like

    if PatientAccount.Type = ‘Inpatient’ –PatientAccount-Table, Type is a column in Table and Inpatient is the value
    But I am not allowed to use PatientAccount(says call to a nil value ).

    What should I do?Please help!

    Thank you

    Reply

Leave a Reply

  • (Will Not Be Published)