Corona SDK SQLiteThe topic of databases is far-reaching in the world of programming (especially for web developers), and for good reason. Databases are great for storing tons of information. Not only that, when it comes to retrieving that data for later use, there’s hardly a better option.

For instance, if you were writing a notes app, you might store the user’s individually created notes in a database. Sure, you *could* just store each note as a separate text file, but then you’d have tons of files on your hands (rather than just a single .db file), and things such as sorting and searching (both commonplace in the world of databases) is out of the question.

What’s more, is that in that same database file, notes could be stored in an SQL table (more on that in a little bit), while other things—such as user settings—can be stored in a separate table.

But wait, haven’t we seen this before somewhere else? Storing data, retrieving it later… all sounds familiar right?

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 = 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 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.', ''); ]]
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 →

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 = ""
name = "Jane Doe",
description = "The wife of an unknown person.",
website = ""
name = "Oscar",
description = "Green guy lives in trash can.",
website = ""

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

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 = path )

local people = {} — starts off emtpy

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

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

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

  1. 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. =)

  2. 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!

  3. @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

  4. 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 = path )

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

    SELECT COUNT(highscore) AS scoreCount FROM playerscore


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

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


    The table has existing 482 records.

    Please advise. Thanks.

  5. Jonathan Beebe says:

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

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

      Jerry Palmroos

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

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

  6. 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?

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

  8. 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!

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

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

  11. Is the WHERE constraint on nrows supported in the version of sqlite3 that Corona uses? I keep getting an error when I use it that says:

    “no such column: ‘quick list'”

    I’m kind of new to sqlite3 so I may just be doing it wrong but the syntax seems pretty straight forward so I’m guessing it isn’t supported. I would just like confirmation.

    The line that is producing the error looks like this:

    for rows in db:nrows([[SELECT*FROM ]] .. myTable .. [[ WHERE name = ]] .. myName .. [[;]]) do

  12. My mistake, it is supported.

    The syntax needed to be:
    for rows in db:nrows([[SELECT*FROM ]] .. myTable .. [[ WHERE name = ‘]] .. myName .. [[‘;]]) do

    I left out the single quotes around the string.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>