How would you create a database for a mobile…

To create a local database on Android, I would use the Room Persistence Library.

This is an abstraction over SQLite that simplifies database work by providing an annotated API for creating schemas, queries, and managing migrations.

Main steps:

Adding dependencies:

// In build.gradle (app level)
dependencies {
    implementation("androidx.room:room-runtime:2.6.1")
    annotationProcessor("androidx.room:room-compiler:2.6.1") // For Java
    kapt("androidx.room:room-compiler:2.6.1") // For Kotlin
    implementation("androidx.room:room-ktx:2.6.1") // For coroutines
}

Creating entities: POJO (Plain Old Java Object) or data classes representing tables in the database. Annotations @Entity, @PrimaryKey, @ColumnInfo are used to define the structure.

@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(name = "first_name") val firstName: String?,
    @ColumnInfo(name = "last_name") val lastName: String?
)

Creating DAO (Data Access Objects): Interfaces defining methods for data access (insertion, deletion, update, queries). Annotations @Dao, @Insert, @Delete, @Update, @Query are used to define operations.

@Dao
interface UserDao {
    @Query("SELECT * FROM users")
    fun getAll(): List<User>

    @Insert
    fun insertAll(vararg users: User)

    @Delete
    fun delete(user: User)
}

Creating the Database class: An abstract class extending RoomDatabase that provides access to DAO and defines the database schema. Annotations @Database specify entities and database version.

@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao

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

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

Working with the database: Obtaining an instance of the database and performing operations via DAO.

val db = AppDatabase.getDatabase(applicationContext)
val userDao = db.userDao()

// Inserting data (usually in a background thread)
GlobalScope.launch {
    userDao.insertAll(User(firstName = "John", lastName = "Doe"))
}

// Retrieving data
GlobalScope.launch {
    val users = userDao.getAll()
}

Advantages of Room:

Compile-time query verification: SQL queries are checked during compilation, reducing runtime errors.
Convenient threading: Integration with coroutines or LiveData simplifies background data handling.
Easier migration: Provides tools for managing schema changes.
Less boilerplate code compared to native SQLite.

If requirements are very specific or small in volume, Shared Preferences can be considered for simple key-value pairs, but for structured data and queries, Room is the preferred choice. For more complex scenarios involving synchronization or access from multiple sources (e.g., web), cloud databases like Firebase Realtime Database or Firestore can be considered, but for local storage, Room is the standard and recommended solution.

How would you create a database for a mobile application?