SQLite on Android: Room, Setup, and Queries

An Android app that needs to keep structured data on the device needs a place to store it between sessions. SQLite gives the app a local relational database, and Room adds a structured layer for working with it.

In this piece, I’ll explain when to choose Room or Android’s SQLite APIs, then show how to create a table and insert and query rows.

TL;DR: Use Room for a typical Android app

SQLite gives an Android app a local relational database, but Room reduces the low-level SQL and object-mapping work in a production app.

• Use Room for compile-time query checks and a structured data-access layer.
• Use android.database.sqlite directly when you need low-level control or are learning the platform APIs.
• Keep database operations off the main thread and version the schema as it changes.

What Is SQLite on Android?

SQLite is an embedded relational database. An Android app uses the database through its process rather than connecting to a separate database server, and Android stores the app’s database in its private data area by default.

The platform package android.database.sqlite includes SQLiteDatabase and SQLiteOpenHelper. The helper creates a database when first needed and calls your schema callbacks when the database version changes. Android’s documentation points developers toward Room because raw SQL is not checked at compile time and mapping query results into app objects takes additional code.

Room adds annotated entity classes for tables, DAO interfaces for database operations, and a database class that connects them. Its compiler checks query declarations against the schema. Choose direct SQLiteDatabase calls when you need to work with the framework API itself, but avoid building a second database layer without a clear reason.

How to Use SQLite in an Android App

The platform workflow is to define a table, create it in SQLiteOpenHelper.onCreate(), then use SQLiteDatabase methods to insert and query rows. This small Kotlin example keeps the schema and database operations together so the lifecycle is visible.

Step 1: Define the table and helper

SQLiteOpenHelper receives the database name and version. Its onCreate callback runs for a new database. The onUpgrade callback handles an existing file when the version increases, so it should contain deliberate migration steps.

Java uses the same android.database.sqlite classes. Override onCreate(SQLiteDatabase db) and execute the CREATE TABLE statement there, then implement onUpgrade() for each version transition. The platform APIs behave the same regardless of whether the surrounding app code is Kotlin or Java.

class NotesDb(context: Context) : SQLiteOpenHelper(context, "notes.db", null, 1) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE note (_id INTEGER PRIMARY KEY, title TEXT NOT NULL)")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Add a migration for each supported schema change.
    }
}

The table has an integer primary key and a required title. A stable key lets later updates target one row even after its title changes. The example keeps migration work in onUpgrade() rather than discarding user rows.

Step 2: Insert a row with ContentValues

ContentValues holds the columns and data for an insert. Pass it to SQLiteDatabase.insert() with the table name. Avoid building SQL by joining text supplied by a user.

val db = helper.writableDatabase
val values = ContentValues().apply {
    put("title", "SQLite on Android")
}
val rowId = db.insert("note", null, values)
check(rowId != -1L)

Check the row ID that insert() returns. A value of -1 marks a failed insert. Handle that result before telling the caller the note was saved.

Step 3: Query with bound selection arguments

Keep the SQL structure fixed and bind runtime values through selectionArgs. The database API combines the placeholder with those separately supplied values when it runs the query. This separation avoids joining user input into a SQL string.

val cursor = db.query(
    "note",
    arrayOf("_id", "title"),
    "title = ?",
    arrayOf("SQLite on Android"),
    null,
    null,
    "_id ASC"
)
cursor.use {
    while (it.moveToNext()) {
        val id = it.getLong(it.getColumnIndexOrThrow("_id"))
        val title = it.getString(it.getColumnIndexOrThrow("title"))
        println("$id: $title")
    }
}

The query uses bound selection arguments, then closes the Cursor with use. I prefer this shape because Android’s documentation warns that raw SQL has no compile-time verification, and separating values avoids constructing the condition from user input.

Step 4: Update or delete selected rows

SQLiteDatabase.update() and delete() accept the same selection-and-argument form as query(). Keep the condition specific so the operation affects only the intended rows.

val changed = db.update(
    "note",
    ContentValues().apply { put("title", "Revised title") },
    "_id = ?",
    arrayOf(rowId.toString())
)
val removed = db.delete("note", "_id = ?", arrayOf(rowId.toString()))

Each method returns the number of affected rows. In this example, the ID condition limits both operations to one note. Check the return value when the caller needs to know whether a row matched.

To read every note, pass null as the selection and selectionArgs, then iterate through the Cursor with moveToNext(). Choose only the columns the screen needs rather than requesting every column by default. This keeps the query’s returned data aligned with the UI’s task.

Android’s Cursor begins before the first row. Call moveToNext() before reading values, and use getColumnIndexOrThrow() with the column name so a changed projection fails at the point of access instead of returning the wrong field.

SQLite on Android: Storage, Migrations, and Limits

Case	Symptom	Fix
Schema changes	New code expects a column absent from existing databases.	Increase the database version and migrate from oldVersion to newVersion without discarding user rows.
Slow open or query	The screen pauses while SQLite creates, upgrades, or reads data.	Move database work to a background thread.
Inspecting app data	The database file is not visible to other apps.	Use Android Studio App Inspection or the SDK sqlite3 shell during development.
Query results	Cursor resources remain held after reading.	Close the Cursor after mapping rows to app values, for example with Kotlin use.

I prefer an explicit migration over dropping a table because database upgrades should preserve user data. The official Android guide also advises running potentially long database work away from the main thread.

Room is the practical choice for many apps because it adds an entity and DAO layer over SQLite and checks queries during compilation. Direct platform calls remain useful for small low-level components or when an existing app already has a deliberate database layer. If the app shares data with other apps, expose a controlled interface such as a ContentProvider rather than making the database file public.

Android apps usually keep one helper instance for the database’s lifetime rather than opening and closing the underlying connection around every query. The framework can share the helper’s database handle, while callers close their Cursor objects when finished. I keep the helper at the repository or application data layer because that makes database ownership clearer than tying it to a short-lived screen.

For local inspection, Android Studio’s App Inspection can connect to a running app and display its database tables. Android also documents the SDK sqlite3 shell as a development tool. Neither tool changes the app-private access rules in a release build.

A useful split is to let a DAO or repository own database calls and return app-level values to the UI. That keeps Cursor handling and SQL details out of screen code, and makes a later move to Room less disruptive.

For example, an app can keep a note’s ID and title in SQLite while its screen receives a Note object. The database remains responsible for durable rows, while the UI works with the smaller model it needs to render.

Conclusion: Choose the API That Fits the App

SQLite supplies local relational storage, while Android’s platform APIs leave schema management and object mapping to your code. Start with Room for a typical app, and use the platform helper directly when its lower-level control is part of the requirement. For the broader database concepts, see the SQLite tutorial and the sqflite guide for Flutter.

FAQ