What is the difference between Room and SQLite…

SQLite is a lightweight relational database embedded in Android. Room is an abstraction library over SQLite, providing a higher-level API for database operations, simplifying interactions, and reducing the likelihood of errors.

Main differences:

ORM: Room is an ORM (Object-Relational Mapper), allowing working with data as POJO classes rather than directly with tables and columns.
Compile-time checks: Room performs database query checks during compilation, detecting syntax errors and data type mismatches before app launch.
Code simplification: Room generates boilerplate code for database operations (e.g., converting POJO to table rows and vice versa), significantly reducing manual coding.
Support for LiveData and Flow: Room integrates with Android Architecture Components like LiveData and Flow, simplifying asynchronous operations and data change observation.
Migrations: Room provides a convenient mechanism for database schema migrations.

Example of using Room:

// Entity
@Entity
data class User(
    @PrimaryKey val userId: Int,
    val name: String,
    val age: Int
)

// DAO (Data Access Object)
@Dao
interface UserDao {
    @Query("SELECT * FROM user")
    fun getAllUsers(): List<User>

    @Insert
    fun insertUser(user: User)
}

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

Example of direct SQLite usage with SQLiteOpenHelper:

// Database helper
class DbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    override fun onCreate(db: SQLiteDatabase) {
        val createTableQuery = """
            CREATE TABLE users (
                _id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                age INTEGER
            )
        """.trimIndent()
        db.execSQL(createTableQuery)
    }

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

    companion object {
        private const val DATABASE_NAME = "mydatabase.db"
        private const val DATABASE_VERSION = 1
    }
}

// Data insertion example
val dbHelper = DbHelper(context)
val db = dbHelper.writableDatabase
val values = ContentValues().apply {
    put("name", "Alice")
    put("age", 30)
}
db.insert("users", null, values)
db.close()

Characteristic	Room	SQLite (direct)
Type	ORM (abstraction library)	Relational database
Data handling	Objects (POJO)	Rows and columns
Checks	Compile-time	Mainly runtime
Boilerplate code	Automatically generated	Requires manual writing
Integration with AAC	Yes (LiveData, Flow)	No
Migrations	Convenient API	Requires manual implementation
Ease of use	High	Lower, more error-prone

Using Room is preferred in most cases for new development, as it significantly simplifies database work, increases code reliability, and reduces errors. Direct SQLite usage may be justified in very simple cases or when specific requirements that Room cannot satisfy are present.

What is the difference between Room and SQLite?