Android

Sqlite DB

SQLite is an open-source relational database. It is embedded into android platform.

The Room persistence library provides an abstraction layer over SQLite with following benefits:

  • compile-time verification of SQL queries
  • convenience annotations that minimize repetitive and error-prone boilerplate code
  • streamlined database migration paths

It is recommend to use Room instead the SQLite APIs directly.

1. Add the dependencies to your build.gradle file.

Room dependencies

2. Declare entities using annotaions.

@Entity
data class User(
    @PrimaryKey val uid: Int,
    @ColumnInfo(name = "first_name") val firstName: String?,
    @ColumnInfo(name = "last_name") val lastName: String?,
    @Ignore val picture: Bitmap?
)

3. Define DAO interfaces. In the simple case, one interface per entity, in the complex case, one DAO interface can work with multiple entities.

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

    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    fun loadAllByIds(userIds: IntArray): List<User>

    @Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
           "last_name LIKE :last LIMIT 1")
    fun findByName(first: String, last: String): User

    @Insert
    fun insertAll(vararg users: User)

    @Delete
    fun delete(user: User)
}
@Dao
public interface UserDao {
    @Query("SELECT * FROM user")
    List<User> getAll();

    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    List>User< loadAllByIds(int[] userIds);

    @Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
           "last_name LIKE :last LIMIT 1")
    User findByName(String first, String last);

    @Insert
    void insertAll(User... users);

    @Delete
    void delete(User user);
}

4. Create database class.

Database example 
@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}
@Database(entities = {User.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
    public abstract UserDao userDao();
}

5. Create database instance. It is usually a singleton because each RoomDatabase instance is quite expensive. If your app runs in multiple processes, include enableMultiInstanceInvalidation() in your database builder invocation.

Db instance 
val db = Room.databaseBuilder(
            applicationContext,
            AppDatabase::class.java, "database-name"
        ).build()

// usage, invoke in background thread
val userDao = db.userDao()
val users: List<User> = userDao.getAll()

AppDatabase db = Room.databaseBuilder(getApplicationContext(),
        AppDatabase.class, "database-name").build();

// usage, invoke in background thread
UserDao userDao = db.userDao();
List<User> users = userDao.getAll();

embeded object

@Embedded annotation represents an object that you'd like to decompose into its subfields within a table. You can then query the embedded fields just as you would for other individual columns.

data class Address(
    val street: String?,
    val state: String?,
    val city: String?,
    @ColumnInfo(name = "post_code") val postCode: Int
)

@Entity
data class User(
    @PrimaryKey val id: Int,
    val firstName: String?,
    @Embedded val address: Address?
)

relations

Room allows to query related data via intermediate data classes.

The intermediate data classes allow you to avoid writing complex SQL queries, but it can also result in increased code complexity due to the additional data classes that it requires.

A one-to-one relationship is a relationship where each instance of the parent entity corresponds to exactly one instance of the child entity, and vice-versa.

One to one relation example

A one-to-many relationship is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, but each instance of the child entity can only correspond to exactly one instance of the parent entity.

One to many relation example

A many-to-many relationship is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, and vice-versa.

In this case, we need to create an additional entity to hold the parent and child entity pair.

Many to many relation example

You can collect related data programmatically, without intermediate data classes.

One to many relation programmatically

foreigner keys

Foreign keys allows you to specify constraints across Entities such that SQLite will ensure that the relationship is valid when you modify the database.

When a foreign key constraint is specified, SQLite requires the referenced columns to be part of a unique index in the parent table or the primary key of that table. You must create a unique index in the parent entity that covers the referenced columns (Room will verify this at compile time and print an error if it is missing).

Foreigner keys examples

index

Adding an index usually speeds up your SELECT queries but will slow down other queries like INSERT or UPDATE. You should be careful when adding indices to ensure that this additional cost is worth the gain.

There are 2 ways to define an index in an entity:

  • ColumnInfo.index() property to index individual fields
  • Entity.indices() property to define composite indices
Define indices in Room

transactions

The @Transaction annotation indicates that sql operations in a method should be performed atomically. It can be applied to both generated methods and handy methods.

Transaction example

async queries

DAO queries fall into three categories:

  • one-shot write queries that insert, update, or delete data in the database
  • one-shot read queries that read data from your database only once and return a result with the snapshot of the database at that time
  • observable read queries that read data from your database every time the underlying database tables change and emit new values to reflect those changes

To prevent queries from blocking the UI, Room does not allow database access on the main thread. This restriction means that you must make your DAO queries asynchronous.

Room provides integration support for interoperability with specific language features and libraries. Don't forget add a required dependencies.

Live data does not support one-shot queries, you can use Guava library.

One-shot queries example 
@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUsers(vararg users: User)

    @Update
    suspend fun updateUsers(vararg users: User)

    @Delete
    suspend fun deleteUsers(vararg users: User)

    @Query("SELECT * FROM user WHERE id = :id")
    suspend fun loadUserById(id: Int): User

    @Query("SELECT * from user WHERE region IN (:regions)")
    suspend fun loadUsersByRegion(regions: List<String>): List<User>
}
@Dao
public interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public Completable insertUsers(List<User> users);

    @Update
    public Completable updateUsers(List<User> users);

    @Delete
    public Completable deleteUsers(List<User> users);

    @Query("SELECT * FROM user WHERE id = :id")
    public Single<User> loadUserById(int id);

    @Query("SELECT * from user WHERE region IN (:regions)")
    public Single<List<User>> loadUsersByRegion(List<String> regions);
}
@Dao
public interface UserDao {
    // Returns the number of users inserted.
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public ListenableFuture<Integer> insertUsers(List<User> users);

    // Returns the number of users updated.
    @Update
    public ListenableFuture<Integer> updateUsers(List<User> users);

    // Returns the number of users deleted.
    @Delete
    public ListenableFuture<Integer> deleteUsers(List<User> users);

    @Query("SELECT * FROM user WHERE id = :id")
    public ListenableFuture<User> loadUserById(int id);

    @Query("SELECT * from user WHERE region IN (:regions)")
    public ListenableFuture<List<User>> loadUsersByRegion(List<String> regions);
}
Observable queries example 
@Dao
interface UserDao {
    @Query("SELECT * FROM user WHERE id = :id")
    fun loadUserById(id: Int): Flow<User>

    @Query("SELECT * from user WHERE region IN (:regions)")
    fun loadUsersByRegion(regions: List<String>): Flow<List<User>>
}

@Dao
public interface UserDao {
    @Query("SELECT * FROM user WHERE id = :id")
    public Flowable<User> loadUserById(int id);

    @Query("SELECT * from user WHERE region IN (:regions)")
    public Flowable<List<User>> loadUsersByRegion(List<String> regions);
}

@Dao
public interface UserDao {
    @Query("SELECT * FROM user WHERE id = :id")
    public LiveData<User> loadUserById(int id);

    @Query("SELECT * from user WHERE region IN (:regions)")
    public LiveData<List<User>> loadUsersByRegion(List<String> regions);
}

Remember that Room sends a notification whenever there are changes to the corresponding tables, not when the selected data changes.

raw query

The @RawQuery annotation indicates that the method uses raw sql.

The SimpleSQLiteQuery class is used to create a query from a sql string.

Room will generate the code based on the return type of the function and failure to pass a proper query will result in a runtime failure or an undefined result. 

@Dao
internal interface RawDao {
    @RawQuery
    fun getSongViaQuery(query: SupportSQLiteQuery?): Song?
}

// Usage of RawDao
var query: SimpleSQLiteQuery = SimpleSQLiteQuery(
    "SELECT * FROM Song WHERE id = ? LIMIT 1", arrayOf<Any>(songId)
)
var song: Song = rawDao.getSongViaQuery(query)

RawQuery methods can return observable types but you need to specify which tables are accessed in the query.

@Dao
internal interface RawDao {
    @RawQuery(observedEntities = Song::class)
    fun getSongs(query: SupportSQLiteQuery?): LiveData<List<Song?>?>?
}

// Usage of RawDao
var liveSongs: LiveData<List<Song>> = rawDao.getSongs(
    SimpleSQLiteQuery("SELECT * FROM song ORDER BY name DESC")
)

prepackaged database

Sometimes, you might want your app to start with a database that is already loaded with a specific set of data. This is called prepopulating a database. In Room 2.2.0 and higher, you can use API methods to prepopulate a Room database at initialization with contents from a prepackaged database file in the device's file system.

// create db using data from assets/database/myapp.db
Room.databaseBuilder(appContext, AppDatabase.class, "Sample.db")
    .createFromAsset("database/myapp.db")
    .build()
    
// create db using data from the specified file
Room.databaseBuilder(appContext, AppDatabase.class, "Sample.db")
    .createFromFile(File("mypath"))
    .build()

In-memory Room databases don't support prepopulating the database using createFromAsset() or createFromFile().

migration

Sometimes you need to modify your Room entity classes and underlying database tables to reflect these changes. It is important to preserve user data that is already in the on-device database when an app update changes the database schema.

Room supports automated migrations in version 2.4.0+. If your app uses a lower version of Room, you must define your migrations manually.

Setup automatical migration 
// Database class before the version update.
@Database(
  version = 1,
  entities = [User::class]
)
abstract class AppDatabase : RoomDatabase() {
  ...
}

// Database class after the version update.
@Database(
  version = 2,
  entities = [User::class],
  autoMigrations = [
    AutoMigration (from = 1, to = 2)
  ]
)
abstract class AppDatabase : RoomDatabase() {
  ...
}

You can use AutoMigrationSpec to give Room the additional information that it needs to correctly generate migration paths. Define a static class that implements AutoMigrationSpec in your RoomDatabase class and annotate it with one or more of the following annotations:

  • @DeleteTable
  • @RenameTable
  • @DeleteColumn
  • @RenameColumn
AutoMigrationSpec example