Android Data, Networking & APIs

Local Databases with SQLite

18 min Lesson 2 of 12

Local Databases with SQLite

Every Android device ships with a full SQLite engine baked into the OS. When your app needs to store structured, relational data — think a list of tasks with categories, a message history with timestamps, or a product catalogue with prices — a local SQLite database is the right tool. SharedPreferences is for key-value settings; SQLite is for anything that looks like a table.

This lesson teaches you how to manage an SQLite database in Android using the SQLiteOpenHelper class and the raw android.database.sqlite API. In the next lesson you will layer the Room abstraction on top; understanding what Room hides is what lets you debug it when it misbehaves.

How Android Manages the Database File

Android stores each app's SQLite database as a file under the app's private data directory: /data/data/<package>/databases/<name>.db. Other apps cannot read it. The file is created the first time you open the database — you never create it manually.

The lifecycle entry point is SQLiteOpenHelper. You subclass it, declare your schema version, and override two methods:

onCreate(SQLiteDatabase db) — called once, the very first time the database file is created. Write your CREATE TABLE statements here.
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) — called whenever you bump the version number in the constructor. Migrate the schema here; never drop tables without offering data preservation.

Version numbers matter. The integer you pass to the super() constructor is persisted inside the database file. If you deploy a new APK with a higher version number, Android calls onUpgrade automatically on the user's device. If you change the schema without incrementing the version, existing devices never see the change.

Creating the Helper Class

Below is a complete helper for a simple task-management database. It has one table, tasks, with an auto-increment primary key, a title, a priority level, and a boolean completion flag.

package com.example.taskapp.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TaskDbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "tasks.db";
    private static final int    DATABASE_VERSION = 1;

    // Column name constants — define once, use everywhere
    public static final String TABLE_TASKS   = "tasks";
    public static final String COL_ID        = "_id";
    public static final String COL_TITLE     = "title";
    public static final String COL_PRIORITY  = "priority";
    public static final String COL_DONE      = "done";

    private static final String SQL_CREATE =
        "CREATE TABLE " + TABLE_TASKS + " (" +
            COL_ID       + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL_TITLE    + " TEXT NOT NULL, " +
            COL_PRIORITY + " INTEGER DEFAULT 1, " +
            COL_DONE     + " INTEGER DEFAULT 0" +   // SQLite has no BOOLEAN; 0 = false, 1 = true
        ")";

    private static final String SQL_DROP =
        "DROP TABLE IF EXISTS " + TABLE_TASKS;

    public TaskDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Simple destructive upgrade — acceptable during development only.
        // In production: ALTER TABLE or copy-and-rename strategy.
        db.execSQL(SQL_DROP);
        onCreate(db);
    }
}

Define column names as public constants. Accessing TaskDbHelper.COL_TITLE throughout your code means a typo is a compile error, not a runtime crash. Never write column names as raw string literals in multiple places.

Inserting Data

To write a row you obtain a writable database handle, populate a ContentValues map, and call insert(). The return value is the new row's _id, or -1 on failure.

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;

public class TaskRepository {

    private final TaskDbHelper helper;

    public TaskRepository(Context context) {
        this.helper = new TaskDbHelper(context);
    }

    public long addTask(String title, int priority) {
        // getWritableDatabase() creates or opens the file; safe to call repeatedly
        SQLiteDatabase db = helper.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(TaskDbHelper.COL_TITLE,    title);
        values.put(TaskDbHelper.COL_PRIORITY, priority);
        values.put(TaskDbHelper.COL_DONE,     0);

        long newId = db.insert(TaskDbHelper.TABLE_TASKS, null, values);
        // Do NOT close db here — see note below
        return newId;
    }
}

Do not call db.close() after every operation. SQLiteOpenHelper caches the database handle internally. Closing it after each call forces an expensive reopen on the next one. Instead, let the helper live as a long-lived singleton (Application scope or a dependency-injected repository) and only close it in Application.onTerminate() — which on real devices is almost never called. The OS reclaims the file handle when your process dies.

Querying Data

db.query() is a structured helper method that builds a SELECT statement for you. It returns a Cursor — a result-set pointer that you iterate row by row. Always close the cursor in a finally block or try-with-resources.

import android.database.Cursor;
import java.util.ArrayList;
import java.util.List;

public List<String> getPendingTaskTitles() {
    SQLiteDatabase db = helper.getReadableDatabase();

    // query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
    Cursor cursor = db.query(
        TaskDbHelper.TABLE_TASKS,
        new String[]{ TaskDbHelper.COL_ID, TaskDbHelper.COL_TITLE },
        TaskDbHelper.COL_DONE + " = ?",   // WHERE done = ?
        new String[]{ "0" },              // ? = 0  (always pass as String)
        null,                             // GROUP BY
        null,                             // HAVING
        TaskDbHelper.COL_PRIORITY + " DESC"  // ORDER BY priority DESC
    );

    List<String> titles = new ArrayList<>();
    try {
        while (cursor.moveToNext()) {
            int titleIndex = cursor.getColumnIndexOrThrow(TaskDbHelper.COL_TITLE);
            titles.add(cursor.getString(titleIndex));
        }
    } finally {
        cursor.close();
    }
    return titles;
}

Never build WHERE clauses by string concatenation. Always use the ? placeholder and pass values in selectionArgs. Concatenating user input directly into a selection string opens your app to SQL injection — even in a local database, malformed input can corrupt or delete data.

Updating and Deleting Rows

Both operations follow the same pattern as insert: get a writable handle, describe what to change, and pass selection criteria via ? placeholders.

// Mark a task as done
public int markDone(long taskId) {
    SQLiteDatabase db = helper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(TaskDbHelper.COL_DONE, 1);

    return db.update(
        TaskDbHelper.TABLE_TASKS,
        values,
        TaskDbHelper.COL_ID + " = ?",
        new String[]{ String.valueOf(taskId) }
    );
    // returns the number of rows affected
}

// Delete a task
public int deleteTask(long taskId) {
    SQLiteDatabase db = helper.getWritableDatabase();
    return db.delete(
        TaskDbHelper.TABLE_TASKS,
        TaskDbHelper.COL_ID + " = ?",
        new String[]{ String.valueOf(taskId) }
    );
}

Running Raw SQL

For complex queries — multi-table joins, subqueries, aggregates — use db.rawQuery(). It still accepts ? placeholders for safety and returns a Cursor like query().

Cursor cursor = db.rawQuery(
    "SELECT priority, COUNT(*) AS cnt FROM tasks WHERE done = ? GROUP BY priority",
    new String[]{ "0" }
);

Schema Migrations in Practice

Suppose version 2 of your app adds a due_date column. You bump the version to 2 and handle both upgrade paths:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
        // ALTER TABLE is safe; it adds a nullable column to existing rows
        db.execSQL("ALTER TABLE " + TaskDbHelper.TABLE_TASKS +
                   " ADD COLUMN due_date INTEGER DEFAULT 0");
    }
    // Future: if (oldVersion < 3) { ... }
}

Use a chained if (oldVersion < N) pattern, not switch. A user upgrading from version 1 to version 3 must pass through all intermediate migrations. Each if block is additive, so they all execute in sequence for that user.

Threading Considerations

getWritableDatabase() and getReadableDatabase() are thread-safe on their own, but the returned SQLiteDatabase object is not thread-safe if you share it across threads without synchronisation. The safest rule for raw SQLite: run all database operations off the main thread. Use AsyncTask (deprecated but still functional), an Executor, or — better — the LiveData / coroutine patterns you will meet when you learn Room. Blocking the main thread with a database call causes ANR (Application Not Responding) errors under load.

Summary

SQLite on Android gives you a full relational database in a single file. Subclass SQLiteOpenHelper, define your schema in onCreate, migrate it in onUpgrade, and perform CRUD through the ContentValues + Cursor API. Always use ? placeholders, always close cursors, and always move database work off the main thread. In the next lesson you will see how Room wraps this same engine with type safety, compile-time query verification, and LiveData integration.