Tutorial: Database access using SQLite

Share on Facebook0Share on Google+7Tweet about this on TwitterShare on LinkedIn0

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:

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

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.

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.
  • websitehttp://www.example.com/

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

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

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:

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:

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.

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.

Share on Facebook0Share on Google+7Tweet about this on TwitterShare on LinkedIn0

Brent Sorrentino serves as a full-time Developer Evangelist for Corona Labs, assisting developers in the forums, maintaining documentation/guides, and creating samples which highlight core features of Corona SDK.

This entry has 23 replies

  1. Mo says:

    LOVE IT!

    Thanks Jon.


  2. John says:

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

  3. GreenCastle says:

    Nice to see a tutorial on this. I’d like to recommend SQLite Database Browser – http://sqlitebrowser.sourceforge.net/ – as a good tool for opening/manually altering those .db files in your workspace. The UI is not very modern and takes some getting used to, but it does the job.

  4. David says:

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

  5. Jamie says:

    Tons of info here! Definitely over my head, but I will pass it on. One of our developers wrote kind of an info to SQLite http://blog.inspiringapps.com/sqlite-database-engine/

  6. KS says:

    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!

  7. David says:

    @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

  8. Greimer says:

    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);]]

    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.

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

      • tangoeurop says:

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

  10. Greimer says:

    Got it! Thanks

  11. sXc says:

    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?

  12. Paul Harrold says:

    Plenty of food for thought here…I’ve been thinking of branching out! Thanks for this info.

    Paul Harrold Microsoft Access database developer

  13. Damir says:

    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.

  14. Michael Dilday says:

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

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

  16. hangn says:

    Can I use a .db database that i already created? I mean not to set it up from LUA

  17. Jose says:

    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.


  18. on says:

    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.

  19. Simran says:


    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

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

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