Android SQLite Database Tutorial

Overview

SQLite is the native relational database engine embedded in the Android platform. It provides a lightweight, file-based SQL database suitable for structured data storage within Android applications. SQLite supports standard SQL syntax and ACID transactions, making it an efficient choice for local persistence without requiring a separate database server.

This documentation explains how to design, implement, access, and manage SQLite databases in Android using the recommended APIs.

Core Concepts

What Is SQLite?

SQLite is a self-contained SQL database engine that stores data in a single file. Android includes it as part of the OS, and developers interact with SQLite using:

• SQLiteOpenHelper: Helper class for database creation and version management
• SQLiteDatabase: Provides database operations (CRUD)
• Cursor: Represents query results

These components work together to store, query, and update structured data in a performant and thread-safe manner.

Defining Database Schema

Start by specifying your database structure. A typical schema includes:

• Table names
• Column names and types
• Primary and foreign key relationships (if needed)

Example schema:

SQL type mapping:

• INTEGER
• TEXT
• REAL
• BLOB

Creating SQLiteOpenHelper

Extend SQLiteOpenHelper to manage database creation and upgrades.

public class AppDatabaseHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "appdata.db";
    private static final int DB_VERSION = 1;

    public AppDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
            "CREATE TABLE users (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT NOT NULL," +
            "email TEXT UNIQUE NOT NULL)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }
}

• onCreate: Called once on first database access
• onUpgrade: Handles schema changes across versions

Opening and Closing the Database

Instantiate your helper and get a writable database:

AppDatabaseHelper dbHelper = new AppDatabaseHelper(context);
SQLiteDatabase db = dbHelper.getWritableDatabase();

Always close the database when done:

db.close();

This ensures resources are released appropriately.

Performing CRUD Operations

Insert Data

ContentValues cv = new ContentValues();
cv.put("name", "John Doe");
cv.put("email", "john@example.com");

long id = db.insert("users", null, cv);

A positive id indicates successful insertion.

Read Data

Query all users:

Cursor cursor = db.query(
    "users",
    new String[]{"id", "name", "email"},
    null, null, null, null, "name ASC"
);

while (cursor.moveToNext()) {
    int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
    String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
    String email = cursor.getString(cursor.getColumnIndexOrThrow("email"));
}

cursor.close();

Always close the Cursor after use to free resources.

Update Records

ContentValues cvUpdate = new ContentValues();
cvUpdate.put("email", "newemail@example.com");

int count = db.update(
    "users",
    cvUpdate,
    "id = ?",
    new String[]{String.valueOf(userId)}
);

The count indicates how many rows were modified.

Delete Records

int deletedCount = db.delete(
    "users",
    "id = ?",
    new String[]{String.valueOf(userId)}
);

Using Raw SQL

For advanced queries or performance tuning:

db.execSQL("DELETE FROM users WHERE id = ?", new Object[]{userId});

Use parameter binding to protect against SQL injection.

Transactions

Wrap multiple operations in a transaction to ensure atomicity:

db.beginTransaction();
try {
    // multiple insert/update/delete calls
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

This improves performance and data integrity.

Indexing for Performance

Create indexes to speed up queries:

db.execSQL("CREATE INDEX idx_user_email ON users(email)");

Indexes improve query performance for frequent lookup fields.

Migration Strategy

When schema evolves, adjust onUpgrade to handle version changes without data loss — for example:

if (oldVersion < 2) {
    db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT");
}

Use version checks to apply incremental changes.

Best Practices

Use Constant Definitions

Avoid hard-coding table and column names. Define them as constants for maintainability.

Close All Resources

Always close:

• Database instances
• Cursor objects
• Helper references when no longer needed

Handle Errors Gracefully

Use try-catch blocks and log exceptions. Provide fallback logic when possible.

Avoid Long Operations on UI Thread

Perform SQLite operations on background threads using:

• AsyncTask (deprecated, consider alternatives)
• ExecutorService
• Kotlin Coroutines or RxJava

This prevents UI blocking and improves responsiveness.