SQLite Database — Persistent Data Storage

For storing structured, relational data in your Android app, SQLite is the built-in, lightweight relational database engine. It's perfect for offline apps, caching, and managing complex data relationships. While you can work with SQLite directly, modern Android development uses Room — an abstraction layer that simplifies database operations and provides compile-time verification of SQL queries.

1. What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It's embedded into Android and requires no setup or administration.

• Serverless: No separate server process — everything runs in the app process.
• Zero-configuration: No installation or configuration needed.
• Transactional: Supports ACID transactions.
• Lightweight: The entire database engine is ~600KB.
• Reliable: Used in millions of apps worldwide.

2. Why Room over Raw SQLite?

Room is a persistence library that provides an abstraction layer over SQLite. Google recommends Room for all new projects because it offers:

• Compile-time SQL validation — Catches SQL errors at compile time.
• Automatic type conversion — Maps SQL types to Java/Kotlin objects.
• LiveData & Flow support — Observes database changes automatically.
• Migration support — Handles database schema changes.
• No boilerplate — Less code compared to raw SQLite.

3. Setting Up Room

Step 1: Add Dependencies

Add the following to your build.gradle (Module) file:

dependencies {
    // Room components
    implementation 'androidx.room:room-runtime:2.6.1'
    kapt 'androidx.room:room-compiler:2.6.1'
    
    // Kotlin Extensions and Coroutines support for Room
    implementation 'androidx.room:room-ktx:2.6.1'
    
    // Optional — Test helpers
    testImplementation 'androidx.room:room-testing:2.6.1'
}

// Apply Kotlin kapt plugin
plugins {
    id 'kotlin-kapt'
}

4. Room Components — The Three Main Parts

5. Step-by-Step Room Implementation

Step 1: Create the Entity

// User.kt — Entity class
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val name: String,
    val email: String,
    val age: Int,
    val isActive: Boolean = true,
    val createdAt: Long = System.currentTimeMillis()
)

Step 2: Create the DAO

// UserDao.kt — Data Access Object
import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

@Dao
interface UserDao {
    
    // Insert
    @Insert
    suspend fun insertUser(user: User): Long
    
    @Insert
    suspend fun insertUsers(users: List<User>)
    
    // Update
    @Update
    suspend fun updateUser(user: User)
    
    // Delete
    @Delete
    suspend fun deleteUser(user: User)
    
    // Query — Get all users
    @Query("SELECT * FROM users ORDER BY name ASC")
    fun getAllUsers(): Flow<List<User>>  // Flow for observing changes
    
    // Query — Get user by ID
    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: Long): User?
    
    // Query — Get users by name (search)
    @Query("SELECT * FROM users WHERE name LIKE '%' || :query || '%'")
    suspend fun searchUsers(query: String): List<User>
    
    // Query — Count users
    @Query("SELECT COUNT(*) FROM users")
    suspend fun getUserCount(): Int
    
    // Query — Delete all
    @Query("DELETE FROM users")
    suspend fun deleteAllUsers()
}

Step 3: Create the Database

// AppDatabase.kt
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import android.content.Context

@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    
    abstract fun userDao(): UserDao
    
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null
        
        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                .fallbackToDestructiveMigration() // For development only!
                .build()
                INSTANCE = instance
                instance
            }
        }
    }
}

Step 4: Using Room in Your Activity/ViewModel

// MainActivity.kt — Using Room
class MainActivity : AppCompatActivity() {

    private lateinit var userDao: UserDao

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

        // Get database instance
        val database = AppDatabase.getInstance(this)
        userDao = database.userDao()

        // Perform operations in background thread
        CoroutineScope(Dispatchers.IO).launch {
            // Insert a user
            val user = User(name = "John Doe", email = "john@example.com", age = 30)
            val userId = userDao.insertUser(user)
            Log.d("MainActivity", "User inserted with ID: $userId")

            // Get all users
            val users = userDao.getAllUsers()
            users.collect { userList ->
                // Update UI with user list (on main thread)
                withContext(Dispatchers.Main) {
                    // Update RecyclerView adapter
                    adapter.submitList(userList)
                }
            }

            // Search users
            val searchResults = userDao.searchUsers("John")
            withContext(Dispatchers.Main) {
                // Update UI with search results
            }

            // Update a user
            user.name = "Johnathan Doe"
            userDao.updateUser(user)

            // Delete a user
            userDao.deleteUser(user)

            // Delete all
            userDao.deleteAllUsers()
        }
    }
}

6. Entity Relationships — One-to-Many, Many-to-Many

One-to-Many Relationship

// Post.kt — Many posts belong to one user
@Entity(tableName = "posts")
data class Post(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val userId: Long,  // Foreign key to User
    val title: String,
    val content: String,
    val createdAt: Long = System.currentTimeMillis()
)

// UserWithPosts.kt — POJO for relationship
data class UserWithPosts(
    @Embedded val user: User,
    @Relation(
        parentColumn = "id",
        entityColumn = "userId"
    )
    val posts: List<Post>
)

// DAO method for one-to-many
@Transaction
@Query("SELECT * FROM users WHERE id = :userId")
suspend fun getUserWithPosts(userId: Long): UserWithPosts?

Many-to-Many Relationship

// Student.kt
@Entity(tableName = "students")
data class Student(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val name: String
)

// Course.kt
@Entity(tableName = "courses")
data class Course(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val title: String
)

// StudentCourseCrossRef.kt — Junction table
@Entity(tableName = "student_course_cross_ref")
data class StudentCourseCrossRef(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val studentId: Long,
    val courseId: Long
)

// StudentWithCourses.kt
data class StudentWithCourses(
    @Embedded val student: Student,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = StudentCourseCrossRef::class,
            parentColumn = "studentId",
            entityColumn = "courseId"
        )
    )
    val courses: List<Course>
)

7. Database Migrations

When you change the database schema (add a column, rename a table, etc.), you need to migrate the existing database to the new version.

// Migration from version 1 to 2 (add column)
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users ADD COLUMN phone TEXT")
    }
}

// Migration from version 2 to 3 (rename column)
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users RENAME COLUMN phone TO phone_number")
    }
}

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

8. Type Converters

Room doesn't support complex data types by default. Use TypeConverters to store objects like Date, List, or custom classes.

// Converters.kt
import androidx.room.TypeConverter
import java.util.Date

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }

    // Convert List<String> to JSON string
    @TypeConverter
    fun fromStringList(value: List<String>): String {
        return value.joinToString(",")
    }

    @TypeConverter
    fun toStringList(value: String): List<String> {
        return if (value.isEmpty()) emptyList() else value.split(",").map { it.trim() }
    }
}

// Add to Database
@Database(
    entities = [User::class],
    version = 2,
    autoMigrations = false
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    // ...
}

9. Testing Room

// UserDaoTest.kt
@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    private lateinit var database: AppDatabase
    private lateinit var userDao: UserDao

    @Before
    fun setup() {
        val context = ApplicationProvider.getApplicationContext<Context>()
        database = Room.inMemoryDatabaseBuilder(
            context,
            AppDatabase::class.java
        ).build()
        userDao = database.userDao()
    }

    @Test
    fun testInsertAndGetUser() = runBlocking {
        val user = User(name = "Test User", email = "test@example.com", age = 25)
        val id = userDao.insertUser(user)
        
        val retrieved = userDao.getUserById(id)
        assertNotNull(retrieved)
        assertEquals("Test User", retrieved?.name)
    }

    @After
    fun tearDown() {
        database.close()
    }
}

10. Common Pitfalls & Solutions

@Entity(
    tableName = "users",
    indices = [Index(value = ["email"]), Index(value = ["name", "age"])]
)
data class User(...)

11. Production-Ready Checklist

• ✅ Use Room — Over raw SQLite for compile-time safety.
• ✅ Run operations on background threads — Use coroutines (Dispatchers.IO).
• ✅ Write migrations — Never use destructive migrations in production.
• ✅ Add indexes — For frequently queried columns.
• ✅ Use transactions — For multiple related operations.
• ✅ Use TypeConverters — For complex data types.
• ✅ Test database operations — Use inMemoryDatabaseBuilder for unit tests.
• ✅ Export schema — Version control your database schema (room.schemaLocation).
• ✅ Use LiveData or Flow — For observable data.
• ✅ Handle database errors — Use try-catch and proper error logging.
• ✅ Use relationships — Properly model one-to-many and many-to-many relationships.
• ✅ Optimize queries — Use indexes and avoid SELECT * when only specific columns are needed.

12. Quick Reference — Room Annotations