The 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 rarely a better option.

For instance, if you were writing a “notes” app, you might store the user’s individual 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 tasks such as sorting and searching (both commonplace in the world of databases) would be out of the question.

What about JSON?

In the saving and loading Lua tables with JSON tutorial, we outline how JSON is great 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 tools, but they have their differences and they should be leveraged depending on the needs of your app — in other words, let the best tool for the job win.

A basic rule of thumb 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), a database is the clear winner every time. However, for smaller sets of data such as tables that store configuration data, JSON is the winner because of its simplicity.

In this tutorial, we’ll walk through creating a database, saving it to a file, storing information, and 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 probably want to keep your data, this tutorial will only discuss the latter method.

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

local sqlite3 = require( "sqlite3" )

local path = system.pathForFile( "data.db", system.DocumentsDirectory )
local db = path )

Note that the recommended location for creating a database is system.DocumentsDirectory, as shown in this example. Your project resource directory is not writable and the temporary/cache directories are periodically wiped clean by the OS, so using the documents directory will ensure that you’re able to read and write to your database, and that it resides in a “safe” (persistent) location.

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. 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 “id,” “name,” “description,” “website,” etc.). Finally, the individual “records” 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 )

In the above code, 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


  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.
  2. The query string is wrapped in double brackets ([[ and ]]) instead of quotes. This is because it is both possible and common to use both single and double quotes in an SQL query, so using brackets is a safer option.

At this point, a new empty table named test is created in the database. If we were to insert new rows into this table, all rows would have an id, name, description, and website property, and of course each row (record) can have different values for these properties.

Inserting new rows into a table

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 Doe','This is an unknown person.', '');]]
db:exec( insertQuery )

This example is rather straightforward. In the previous code, we created a table called test. Here, we are “inserting into” that test table.

Note how the values in the parentheses (()) correspond exactly — and in the same order — to the columns we specified when we created the table. This is very important!

  • idNULL
  • nameJohn Doe
  • descriptionThis is an unknown person.
  • website

Remember that id will auto-increment to the next number because of the autoincrement flag. This is why we’re able to pass NULL instead of an actual number when creating the row.

Lua table to SQL table

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 (this assumes that you’ve already created the database and the test 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 )

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='Grouch' WHERE id=3;]]
db:exec( q )

Essentially, this query finds the row where id equals 3 and changes the name value to Grouch. While you don’t necessarily have to use the id column to find the row, it is often the easiest way to locate a specific row because it will always be unique.

Deleting a row

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

There are several ways to retrieve data from an SQL database. Sometimes you’ll want a single specific row. 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 that match a 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:

local sqlite3 = require( "sqlite3" )

local path = system.pathForFile( "data.db", system.DocumentsDirectory )
local db = path )

local people = {}  -- Begins empty

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

    -- Create table at the next available array index
    people[#people+1] =
        name =,
        description = row.description,
        website =

The most important line is line 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. In this example, we simply copy the data over to the people array so we can use it later in the app.

For more information on using the SELECT command to narrow down data, read this article.

Closing the database

When you’re finished accessing your database, it’s important to close the “connection” to it by using the close() method of your database object, for example db:close(). You can do this at any time, but we recommend calling this in conjunction with an "applicationExit" system event to 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 )

Of course this code must exist in the scope of the database object you are closing (the db object in the example above) so that Lua understands which database you are referring to.


And that covers the essentials of database access in Corona. Keep in mind, however, that we only scratched the surface of what’s possible with SQLite and Corona, so we encourage you to explore further to discover what’s possible.

  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>