Android SQLite & Room Database — Entity, DAO, CRUD, Migration

Database Overview

When your app needs structured, queryable data — contacts, todos, notes, products — a relational database is the right choice. Android ships with SQLite, and Room is Google's recommended abstraction on top of it.

Raw SQLite Path: SQLiteOpenHelper → SQL queries → SQLite file on device Modern Room Path: @Entity → @Dao → @Database → Room generates SQL for you

Approach	Pros	Cons
Raw SQLite	Full control, no dependencies	Verbose, error-prone SQL strings
SQLiteOpenHelper	Standard Android API, version management	Still manual SQL and boilerplate
Room	Compile-time checks, LiveData/Flow, migrations	Learning curve, annotation setup

1SQLite Basics

SQLite is a lightweight, file-based relational database embedded in Android. Data is stored in tables with rows and columns, queried using SQL.

Core SQL concepts

Term	Description	Example
Table	Collection of related records	`contacts`
Column	Field / attribute	`name`, `email`
Row	Single record	One contact entry
Primary Key	Unique row identifier	`id INTEGER PRIMARY KEY`

CREATE TABLE example

SQL — contacts table schemaCREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

Common SQL statements

SQL — Essential queries-- Insert
INSERT INTO contacts (name, email, phone) VALUES ('Nikhil', 'nikhil@example.com', '9876543210');

-- Select all
SELECT * FROM contacts;

-- Select with filter
SELECT name, email FROM contacts WHERE name LIKE 'N%';

-- Update
UPDATE contacts SET phone = '9999999999' WHERE id = 1;

-- Delete
DELETE FROM contacts WHERE id = 1;

-- Order and limit
SELECT * FROM contacts ORDER BY name ASC LIMIT 10;

2SQLiteOpenHelper

SQLiteOpenHelper manages database creation and version upgrades. Subclass it to define schema in onCreate() and handle schema changes in onUpgrade().

Database helper class

Kotlin — ContactDbHelper.ktclass ContactDbHelper(context: Context) : SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {
    companion object {
        const val DATABASE_NAME = "contacts.db"
        const val DATABASE_VERSION = 1
        const val TABLE_CONTACTS = "contacts"
        const val COL_ID = "id"
        const val COL_NAME = "name"
        const val COL_EMAIL = "email"
        const val COL_PHONE = "phone"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = """
            CREATE TABLE $TABLE_CONTACTS (
                $COL_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COL_NAME TEXT NOT NULL,
                $COL_EMAIL TEXT,
                $COL_PHONE TEXT
            )
        """.trimIndent()
        db.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_CONTACTS")
        onCreate(db)
    }
}

Open readable/writable database

Kotlin — Get database instanceval dbHelper = ContactDbHelper(context)

// Read-only (multiple threads OK)
val readDb = dbHelper.readableDatabase

// Read-write (single writer at a time)
val writeDb = dbHelper.writableDatabase

// Always close when done (or use use {} block)
readDb.close()
dbHelper.close()

3CRUD Operations

CRUD — Create, Read, Update, Delete — are the four basic database operations. With raw SQLite, use ContentValues and Cursor to interact with data safely.

Create — insert row

Kotlin — Insert contactfun insertContact(dbHelper: ContactDbHelper, name: String, email: String, phone: String): Long {
    val db = dbHelper.writableDatabase
    val values = ContentValues().apply {
        put(ContactDbHelper.COL_NAME, name)
        put(ContactDbHelper.COL_EMAIL, email)
        put(ContactDbHelper.COL_PHONE, phone)
    }
    return db.insert(ContactDbHelper.TABLE_CONTACTS, null, values)
}

Read — query rows

Kotlin — Read all contactsdata class Contact(val id: Long, val name: String, val email: String?, val phone: String?)

fun getAllContacts(dbHelper: ContactDbHelper): List<Contact> {
    val contacts = mutableListOf<Contact>()
    val db = dbHelper.readableDatabase
    val cursor = db.query(
        ContactDbHelper.TABLE_CONTACTS,
        arrayOf(
            ContactDbHelper.COL_ID,
            ContactDbHelper.COL_NAME,
            ContactDbHelper.COL_EMAIL,
            ContactDbHelper.COL_PHONE
        ),
        null, null, null, null,
        "${ContactDbHelper.COL_NAME} ASC"
    )

    cursor.use {
        while (it.moveToNext()) {
            contacts.add(
                Contact(
                    id = it.getLong(it.getColumnIndexOrThrow(ContactDbHelper.COL_ID)),
                    name = it.getString(it.getColumnIndexOrThrow(ContactDbHelper.COL_NAME)),
                    email = it.getString(it.getColumnIndexOrThrow(ContactDbHelper.COL_EMAIL)),
                    phone = it.getString(it.getColumnIndexOrThrow(ContactDbHelper.COL_PHONE))
                )
            )
        }
    }
    return contacts
}

Update — modify row

Kotlin — Update contactfun updateContact(dbHelper: ContactDbHelper, id: Long, name: String, email: String): Int {
    val db = dbHelper.writableDatabase
    val values = ContentValues().apply {
        put(ContactDbHelper.COL_NAME, name)
        put(ContactDbHelper.COL_EMAIL, email)
    }
    return db.update(
        ContactDbHelper.TABLE_CONTACTS,
        values,
        "${ContactDbHelper.COL_ID} = ?",
        arrayOf(id.toString())
    )
}

Delete — remove row

Kotlin — Delete contactfun deleteContact(dbHelper: ContactDbHelper, id: Long): Int {
    val db = dbHelper.writableDatabase
    return db.delete(
        ContactDbHelper.TABLE_CONTACTS,
        "${ContactDbHelper.COL_ID} = ?",
        arrayOf(id.toString())
    )
}

4Room Database

Room is an abstraction layer over SQLite that provides compile-time SQL verification, coroutine/Flow support, and easier migrations. Google recommends Room for all new database work.

Gradle dependencies

Kotlin — app/build.gradle.ktsplugins {
    id("com.google.devtools.ksp") version "1.9.22-1.0.17"
}

dependencies {
    val roomVersion = "2.6.1"
    implementation("androidx.room:room-runtime:$roomVersion")
    implementation("androidx.room:room-ktx:$roomVersion")
    ksp("androidx.room:room-compiler:$roomVersion")
}

Room architecture

@Entity (Contact.kt) → Defines table structure @Dao (ContactDao.kt) → Defines SQL operations @Database (AppDatabase) → Connects entities + DAOs Repository → Single source of truth for UI

AppDatabase class

Kotlin — AppDatabase.kt@Database(entities = [Contact::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun contactDao(): ContactDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                ).build().also { INSTANCE = it }
            }
        }
    }
}

Use in Activity with coroutines

Kotlin — MainActivity.ktclass MainActivity : AppCompatActivity() {

    private lateinit var contactDao: ContactDao

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        val db = AppDatabase.getInstance(this)
        contactDao = db.contactDao()

        lifecycleScope.launch {
            contactDao.insert(Contact(name = "Nikhil", email = "nikhil@example.com", phone = "9876543210"))
            val contacts = contactDao.getAllContacts()
            Log.d("Room", "Contacts: $contacts")
        }
    }
}

5Entity

An Entity is a Kotlin class annotated with @Entity that maps to a SQLite table. Each property becomes a column unless marked with @Ignore.

Basic entity

Kotlin — Contact.kt@Entity(tableName = "contacts")
data class Contact(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val name: String,
    val email: String?,
    val phone: String?,
    val createdAt: Long = System.currentTimeMillis()
)

Entity with indices and defaults

Kotlin — Task entity with index@Entity(
    tableName = "tasks",
    indices = [Index(value = ["title"], unique = true)]
)
data class Task(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val isDone: Boolean = false,
    val priority: Int = 0
)

Composite primary key

Kotlin — Junction / cross-ref table@Entity(primaryKeys = ["userId", "courseId"])
data class UserCourseCrossRef(
    val userId: Long,
    val courseId: Long,
    val enrolledAt: Long = System.currentTimeMillis()
)

Entity annotations reference

Annotation	Purpose
`@Entity`	Marks class as a database table
`@PrimaryKey`	Primary key column; `autoGenerate = true` for auto-increment
`@ColumnInfo`	Custom column name, default value, type affinity
`@Ignore`	Exclude property from table
`@Embedded`	Flatten nested object into parent table columns
`@Relation`	Define relationship between entities (used in POJO queries)

6DAO

A DAO (Data Access Object) defines database operations as interface methods. Room generates the implementation at compile time — no manual SQL strings in your app code.

ContactDao interface

Kotlin — ContactDao.kt@Dao
interface ContactDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(contact: Contact): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(contacts: List<Contact>)

    @Update
    suspend fun update(contact: Contact)

    @Delete
    suspend fun delete(contact: Contact)

    @Query("DELETE FROM contacts WHERE id = :id")
    suspend fun deleteById(id: Long)

    @Query("SELECT * FROM contacts ORDER BY name ASC")
    suspend fun getAllContacts(): List<Contact>

    @Query("SELECT * FROM contacts WHERE id = :id")
    suspend fun getContactById(id: Long): Contact?

    @Query("SELECT * FROM contacts WHERE name LIKE '%' || :query || '%'")
    suspend fun searchContacts(query: String): List<Contact>

    @Query("SELECT COUNT(*) FROM contacts")
    suspend fun getContactCount(): Int
}

Reactive queries with Flow and LiveData

Kotlin — Observable queries@Dao
interface ContactDao {

    @Query("SELECT * FROM contacts ORDER BY name ASC")
    fun getAllContactsFlow(): Flow<List<Contact>>

    @Query("SELECT * FROM contacts ORDER BY name ASC")
    fun getAllContactsLiveData(): LiveData<List<Contact>>
}

// In ViewModel
val contacts: Flow<List<Contact>> = contactDao.getAllContactsFlow()

Repository wrapping DAO

Kotlin — ContactRepository.ktclass ContactRepository(private val contactDao: ContactDao) {

    val allContacts: Flow<List<Contact>> = contactDao.getAllContactsFlow()

    suspend fun insert(contact: Contact) = contactDao.insert(contact)

    suspend fun update(contact: Contact) = contactDao.update(contact)

    suspend fun delete(contact: Contact) = contactDao.delete(contact)

    suspend fun search(query: String) = contactDao.searchContacts(query)
}

7Database Migration

When you change your schema (add column, new table), increment the database version and provide a Migration. Without it, Room throws an error or wipes user data.

Why migrations matter

Never use fallbackToDestructiveMigration() in production — it deletes all user data on schema change. Always write explicit migrations for app updates.

Add column migration (v1 → v2)

Kotlin — Migration 1 to 2// Entity updated: added `phone` column
// @Database version changed from 1 to 2

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE contacts ADD COLUMN phone TEXT")
    }
}

Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
    .addMigrations(MIGRATION_1_2)
    .build()

Multiple migrations

Kotlin — Migrations 2 to 3val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                title TEXT NOT NULL,
                isDone INTEGER NOT NULL DEFAULT 0,
                priority INTEGER NOT NULL DEFAULT 0
            )
        """.trimIndent())
    }
}

Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Auto-migrations (Room 2.4+)

Kotlin — AutoMigration for simple changes@Database(
    entities = [Contact::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 2, to = 3)
    ],
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() { ... }

Migration checklist

Increment version in @Database
Update Entity classes to match new schema
Write Migration(oldVersion, newVersion) with SQL ALTER/CREATE statements
Register with .addMigrations(...)
Test migration on a device with old DB version before releasing
Enable exportSchema = true and keep schema JSON files in version control

8Hands-On Exercises

1

Create a contacts table with SQLiteOpenHelper. Implement insert and read using ContentValues and Cursor.

2

Complete full CRUD on the contacts table: create, read all, update by id, delete by id.

3

Refactor to Room: define a Contact Entity, ContactDao, and AppDatabase singleton.

4

Display contacts in a RecyclerView using a Flow query from the DAO. List updates automatically when data changes.

5

Add a search feature using @Query with LIKE in the DAO.

6

Add a phone column to Contact. Bump database version to 2 and write a Migration from v1 to v2.

7

Bonus: Create a ContactRepository and ContactViewModel that expose contacts as StateFlow to the UI.

SQLite & Room MCQ Practice

10 Basic MCQs 10 Advanced MCQs

10 Basic SQLite & Room MCQs

1

SQLite on Android is?

ACloud service

BEmbedded relational database

CLayout manager

DNotification API

Explanation: Lightweight SQL database stored locally on device.

2

Room is?

AGPS library

BUI toolkit

CSQLite abstraction layer (ORM) by Google

DGradle plugin only

Explanation: Room provides Entity, DAO, Database with compile-time checks.

3

@Entity annotates?

AMenu XML

BActivity

CService

DDatabase table data class

Explanation: Entity classes map to SQLite tables.

4

DAO stands for?

AData Access Object

BDigital Application Output

CDevice Admin Override

DDynamic Activity Object

Explanation: DAO defines query methods Room implements.

5

@Insert on DAO method?

AStarts Activity

BGenerates INSERT SQL

CSends broadcast

DInflates layout

Explanation: Room generates implementation at compile time.

6

@Query allows?

AOnly GPS

BOnly INSERT

CCustom SQL SELECT/UPDATE/DELETE

DOnly UI binding

Explanation: Write SQL in annotation — validated at compile time.

7

SQLiteOpenHelper used for?

ABluetooth

BPush notifications

CCamera

DManual DB create/upgrade callbacks

Explanation: onCreate/onUpgrade before Room — Room wraps similar logic.

8

Primary key in Room Entity?

A@PrimaryKey on field

B@IntentFilter

C@Service

D@Layout

Explanation: autoGenerate = true for auto-increment integer keys.

9

Room database built with?

AIntent.createChooser

BRoom.databaseBuilder(context, AppDatabase.class, "name").build()

CAlertDialog

DSharedPreferences

Explanation: Singleton pattern common for database instance.

10

CRUD means?

ACache Reset Undo Done

BCompile Run Upload Download

CCreate Read Update Delete

DContext Receiver UI Data

Explanation: Basic database operations on records.

10 Advanced SQLite & Room MCQs

11

Migration in Room?

AAutomatic always without code

BMigration(startVersion, endVersion) { sql exec }

CImpossible

DManifest only

Explanation: Provide migrations or fallbackToDestructiveMigration for dev only.

12

@Transaction on DAO?

ANetwork call

BStarts Activity

CGroups multiple DB ops atomically

DUI thread only

Explanation: Ensures all operations succeed or roll back together.

13

Flow> from @Query?

AReplaces LiveData ban

BOne-shot only

CIllegal in Room

DReactive stream emitting on table changes

Explanation: Room can return Flow observed in coroutine collector.

14

suspend fun in DAO?

ARuns query on background thread automatically

BMust run on main

CReplaces SQL

DDeprecated

Explanation: Room handles dispatcher for suspend DAO functions.

15

@Relation used for?

AForeign keys only in SQL

BEmbedded object relationships in query results

CActivity navigation

DIntent extras

Explanation: POJO with @Embedded and @Relation for nested data.

16

FTS (Full-Text Search) in Room?

ANot supported

BFirebase only

C@Fts4 entity for text search

DLayout attribute

Explanation: Room supports FTS entities for efficient text search.

17

Database inspector in Android Studio?

APair Bluetooth

BCompile SQL to dex

CSign APK

DView/edit Room DB on emulator/device

Explanation: App Inspection tool shows live database contents.

18

onConflict = OnConflictStrategy.REPLACE?

AINSERT OR REPLACE on conflict

BDelete database

CCrash app

DIgnore SQL

Explanation: Strategy for duplicate primary key on insert.

19

TypeConverter in Room?

AChanges Activity

BConverts custom types (Date, List) to DB storable types

CGPS coords

DMenu inflation

Explanation: @TypeConverter between Date and Long timestamp.

20

Why avoid main thread DB access?

AFaster on main

BIllegal on all APIs

CCauses ANR — disk I/O blocks UI

DRequired for Room

Explanation: Always use coroutines, RxJava, or Executor for DB I/O.

Click an option to select, then check answers.

SQLite & Room Interview Q&A

15 topic-focused questions for interviews and revision.

1Why use Room over raw SQLite?easy

Answer: Compile-time SQL validation, less boilerplate, LiveData/Flow integration, migrations, type converters.

2Define a Room Entity.easy

Answer: @Entity data class User(@PrimaryKey val id: Int, val name: String, val email: String)

3Example DAO query.easy

Answer: @Query("SELECT * FROM user WHERE id = :userId") suspend fun getUser(userId: Int): User?

4Build Room database singleton.medium

Answer: Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, "app_db").build()

5Perform CRUD with Room.easy

Answer: @Insert suspend fun insert(user: User); @Update update; @Delete delete; @Query SELECT for read.

6Handle database migration.hard

Answer: Add Migration(1,2){ db.execSQL("ALTER TABLE...") } to builder.addMigrations(migration).

7SQLiteOpenHelper vs Room.medium

Answer: OpenHelper manual SQL and cursors; Room generates code from annotations with type safety.

8Observe database changes in UI.medium

Answer: Return Flow or LiveData from @Query; collect/observe in ViewModel; update RecyclerView.

9Relationship one-to-many in Room.hard

Answer: Parent entity + data class with @Embedded parent and @Relation List in query result POJO.

10TypeConverter example.medium

Answer: @TypeConverter fun fromTimestamp(value: Long?) = value?.let { Date(it) }

11Threading with Room.medium

Answer: Use suspend functions, withContext(Dispatchers.IO), or allowMainThreadQueries only in tests.

12FallbackToDestructiveMigration risk.medium

Answer: Drops and recreates DB on version mismatch — data loss; dev only.

13Index in Room Entity.hard

Answer: @Entity(indices = [Index(value = ["email"], unique = true)]) for faster lookups.

14Test Room database.hard

Answer: Use in-memory Room.databaseBuilder(...).inMemoryDatabaseBuilder() in androidTest.

15When still use raw SQLite?medium

Answer: Legacy code, very custom SQL, or interoperability — Room covers most app cases.

SQLite & Room Database

Table of Contents