SQLite Crash Course for Android Developers

Android applications can store application data in SQLite databases. In this tutorial, you learn how SQLite databases are designed and manipulated.

Here we begin by designing and using a simple SQLite database to manage chess tournament scores. This tutorial is meant as a brief overview of how SQLite databases work. This knowledge will then be used in future development tutorials to implement database-driven Android applications.

Getting Started

Android applications can create and manipulate their own private SQLite relational databases. Developers can also inspect and modify databases on a given Android emulator or device using the sqlite3 command-line tool provided as part of the Android SDK tool called Android Debug Bridge (adb).

In this tutorial, we assume that you have some understanding of relational databases, in theory, but require a bit of a refresher course before you use them within your Android applications. This particular tutorial does not require any tools; it’s more a theoretical exercise.

However, if you are planning to develop Android applications which rely upon SQLite databases, you will need to install the tools necessary for Android development, such as the Android SDK and the Eclipse IDE. Check out the many other helpful Android tutorials available here on Mobiletuts+ to help you work through these tasks.

What is SQLite?

SQLite is a lightweight relational database engine. SQLite is fast and has a small footprint, making it perfect for Android devices. Instead of the heavyweight server-based databases like Oracle and Microsoft SQL Server, each SQLite database is stored within a single file on disk. Android applications can choose to store private application data in a SQLite database.

Note: If you’re familiar with SQL, then SQLite will be very easy to pick up. SQLite is basically a stripped-down SQL database engine for embedded devices. For specific information about SQLite and its features, functionality, and limitations, see the SQLite online documentation.

A Quick Review of Database Fundamentals

A database is simply a structured way of storing data in a persistent fashion. Data is stored in tables. A table has columns with different datatypes. Each row in a table represents a data record. You may find it helpful to think of a table like an Excel spreadsheet. For an object oriented programming perspective, each table in a database often represents an object (represented by a class). Each table column represents a class attribute. Each record in a table represents a specific instance of that object.

Let’s look at a quick example. Let’s say you have a company database with a table called Employee. The Employee table might have five typed columns: EmployeeID (number), FirstName (string), LastName (string), Title (string) and Salary (number). You could then add a record to the data base for an employee named John Doe and a separate record for an employee named Anne Droid.

Data within a database is meant to be inspected and manipulated. Data within a table can be:

  • Added (using the INSERT command)
  • Modified (using the UPDATE command)
  • Removed (using the DELETE command)

You can search for specific data within a database using what is called a query. A query (using the SELECT command) may involve one table, or multiple tables. To create a query, you must specify the tables, data columns, and data values of interest using SQL command language. Each SQL command is terminated with a semicolon (;).

The Chess Tournament Database

The best way to truly understand how SQLite databases function is to work through a simple example, so let’s do so. Let’s pretend that we have an application that keeps track of player scores from a casual chess tournament. Player scores are recorded and then, at the end of a series of matches, the winner is determined. Each player’s overall tournament score is calculated from their performance on:

  • Four warm-up heats (weight: 10% of overall score each)
  • One semi-final (weight: 25% of overall score)
  • One final (weight: 35% of overall score)

Note: For the tournament, player scores could be based upon a formula that factors in the time it took to win the game and the type and number of pieces left on the board at the end of the game. This way, a strong player will receive a high score for losing few powerful pieces and winning the game quickly. Perhaps style and attitude are included by the judges to encourage fun, light play. How scores are calculated is really not important to how we define our database; we just store them in the database. For simplicity, we will assume that scores are based on a scale of 0–100.

Designing the Database Schema

A database schema is simply the definition of the structure of the database in terms of tables, data columns and such. The schema for our tournament database is quite simple:

The TournamentScores database schema has three tables:

  • The Players table contains player information.
  • The Games table contains information about each game and how much it counts toward the player’s overall tournament score.
  • The GameResults table contains all players’ game scores.

SQLite3 has support for the following common datatypes for columns:

  • INTEGER (signed integers)
  • REAL (floating point values)
  • TEXT (UTF-8 or UTF-16 string; encoded using database encoding)
  • BLOB (data chunk)

Once you’ve determined which columns are necessary for each table, you’re ready to create some tables within your database schema.

Working with Tables

Let’s begin by creating the Players table. This table requires a unique player id to reference each player. We can make this the primary key (to uniquely identify a record in this table) and set its autoincrement attribute. Autoincrement means that each time a new player record is added, the record will get a new, unique player id. We also want to store the first and last name of each player-no nulls allowed.
Here we can use the CREATE TABLE SQL statement to generate the Players table:

The Games table is very similar. We need a unique game id to reference each game. We also want a friendly name for each game and a weight value for how much the game counts towards the player’s final tournament score (as a percentage). Here’s the SQL statement to create the Games table:

  • Each player’s full name is displayed as the first result column (calculated from their first and last name)
  • Each player’s tournament score is displayed as the second result column (calculated from the weighted scores of all games played by a given player)
  • The result records are listed in descending order based upon the tournament scores (first place, second place, etc.)

The results from this query are shown below:


This concludes our exploration of a simple SQLite database example: a chess tournament database. Hopefully you’ve reacquainted yourself with relational database concepts like tables, records, and queries and familiarized yourself with many of the commonly used SQLite commands. Finally, you’ve walked through the design and usage of an example database. Now that you’ve got a handle on how SQLite databases work, you’re ready to use them within your Android applications (the topic of our next tutorial in this series).

About the Authors

Mobile developers Lauren Darcey and Shane Conder have coauthored several books on Android development: an in-depth programming book entitled Android Wireless Application Development and Sams TeachYourself Android Application Development in 24 Hours. When not writing, they spend their time developing mobile software at their company and providing consulting services. They can be reached at via email to, via their blog at, and on Twitter @androidwireless.

Need More Help Writing Android Apps? Check out our Latest Books and Resources!

Buy Android Wireless Application Development, 2nd Edition  Buy Sam's Teach Yourself Android Application Development in 24 Hours  Mamlambo code at Code Canyon