How to populate Android Room database table on first run?

Question

In SQLiteOpenHelper there is a onCreate(SQLiteDatabase ...) method which i used to populate database tables with some initial data.

Is there a way to insert some data into Room database table on first app run?

Twoply

You could provide a pre-populated database. And work on that one. – Glori

There's a good article here: medium.com/google-developers/7-pro-tips-for-room-fbadea4bfbd1 – Vexed

Answer 1

Updated

You can do this in 3 ways: important check this for migration details

1- Populate your database from exported asset schema

Room.databaseBuilder(appContext, AppDatabase.class, "Sample.db")
    .createFromAsset("database/myapp.db")
    .build();

2- Populate your database from file

Room.databaseBuilder(appContext, AppDatabase.class, "Sample.db")
    .createFromFile(new File("mypath"))
    .build();

3- You can run scripts after database is created or run every time database is opened using RoomDatabase.Callback, this class is available in the latest version of the Room library.

You need to implement onCreate and onOpen method of RoomDatabase.Callback and add it to RoomDatabase.Builder as shown below.

yourDatabase = Room.databaseBuilder(context, YourDatabase.class, "your db")
    .addCallback(rdc)
    .build();

RoomDatabase.Callback rdc = new RoomDatabase.Callback() {
    public void onCreate (SupportSQLiteDatabase db) {
        // do something after database has been created
        
    }
    public void onOpen (SupportSQLiteDatabase db) {
        // do something every time database is open
     
    }
};

Reference

You can use Room DAO itself in the RoomDatabase.Callback methods to fill the database. For complete examples see Pagination and Room example

   RoomDatabase.Callback dbCallback = new RoomDatabase.Callback() {
        public void onCreate(SupportSQLiteDatabase db) {
            Executors.newSingleThreadScheduledExecutor().execute(new Runnable() {
                @Override
                public void run() {
                   getYourDB(ctx).yourDAO().insertData(yourDataList);
                }
            });
        }
    };

Combustion

This one should be flagged as the correct answer. Thanks! – Biddle

I faced with an issue that callbacks not triggered on .build(). only on first real read/write operation as described here – Buyse

I have the same problem as @Maxim, the onCreate is only triggered when the first read/write operation is called, not after the build() call. So far the only solution is the proposed by Maxim on the link – Bui

I'm trying this approach but I get a: java.lang.IllegalStateException: getDatabase called recursively – Repose

@EduardoCorona you have probably added the getYourDb part to the onOpen() method.Naturally, this will result in endless recursive calls. – Toenail

I'd love it if someone could help me figure out how to do this with Hilt and not cause memory leaks because the DB isn't closed. – Mcmorris

@Arnav Rao What the file "mypath" looks like? And what about the myapp.db? – Curcio

You need to be careful with the callback approach as the data is being added on a new thread (and needs to be if using the Dao). The new database will be immediately accessible and can be used while this is happening. To avoid race condition access to the unpopulated database either use execSQL to populate it on the same thread (see answer by @nAkmedov) or populate it in the getter for the singleton instance (see answer by @N1234). – Bergmann

Answer 2

I tried to use the RoomDatabase.Callback as suggested by Arnav Rao, but to use a callback you cannot use the DAO as the callback is created before the database has been built. You could use db.insert and content values, but I didn't think that would have been correct. So after looking into it a bit more - took me ages lol - but I actually found the answer when going through the samples provided by Google.

https://github.com/googlesamples/android-architecture-components/blob/master/PersistenceContentProviderSample/app/src/main/java/com/example/android/contentprovidersample/data/SampleDatabase.java

See line 52 and the method on line 71 - In there you can see after the build of the database instance, the next line calls a method which checks if there are any records in the database (using the DAO) and then if it’s empty it inserts the initial data (again using the DAO).

Hope this helps anyone else who was stuck :)

Daylong

thank you very much for sharing. this looks like the correct way to do this. – Hagiographa

Answer 3

You can populate tables after creating the database, make sure the operation is running on a separate thread. You can follow the classes bellow to pre-populate tables on the first time.

AppDatabase.kt

@Database(entities = [User::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {

    abstract fun userDao(): UserDao

    companion object {

        // For Singleton instantiation
        @Volatile private var instance: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return instance ?: synchronized(this) {
                instance ?: buildDatabase(context).also { instance = it }
            }
        }

        private fun buildDatabase(context: Context): AppDatabase {
            return Room.databaseBuilder(context, AppDatabase::class.java, DATABASE_NAME)
                    .addCallback(object : RoomDatabase.Callback() {
                        override fun onCreate(db: SupportSQLiteDatabase) {
                            super.onCreate(db)
                            //pre-populate data
                            Executors.newSingleThreadExecutor().execute {
                                instance?.let {
                                    it.userDao().insertUsers(DataGenerator.getUsers())
                                }
                            }
                        }
                    })
                    .build()
        }
    }
}

DataGenerator.kt

class DataGenerator {

    companion object {
        fun getUsers(): List<User>{
            return listOf(
                User(1, "Noman"),
                User(2, "Aayan"),
                User(3, "Tariqul")
            )
        }
    }

}

Hardden

You need to be careful with the callback approach as the data is being added on a new thread (and needs to be if using the Dao). The new database will be immediately accessible and can be used while this is happening. To avoid race condition access to the unpopulated database either use execSQL to populate it on the same thread (see answer by @nAkmedov) or populate it in the getter for the singleton instance (see answer by @N1234). – Bergmann

Answer 4

I tried a number of ways to do this, each to no available.

First, I tried adding a Migration implementation to Room using the 'addMigrations' method, but found that it only runs during a database upgrade, but not on creation.

Then, I tried passing a SQLiteOpenHelper implementation to Room using the 'openHelperFactory' method. But after creating a bunch of classes in order to get around Room's package-level access modifiers, I abandoned the effort. I also tried subclassing Room's FrameworkSQLiteOpenHelperFactory but, again, the package-level access modifier of its constructor didn't support this.

Finally, I created a IntentService to populate the data and invoked it from the onCreate method of my Application subclass. The approach works but a better solution should be the upcoming fix to the tracker issue mentioned by Sinigami elsewhere on this page.

Darryl

[Added July 19, 2017]

The issue looks as though it's resolved in Room 1.0.0. Alpha 5. This release added a callback to RoomDatabase that lets you execute code when the database is first created. Take a look at:

https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase.Callback.html

Abran

Answer 5

@Provides
@Singleton
LocalDatabase provideLocalDatabase(@DatabaseInfo String dbName, Context context) {
    return Room.databaseBuilder(context, LocalDatabase.class, dbName)
            .addCallback(new RoomDatabase.Callback() {
                @Override
                public void onCreate(@NonNull SupportSQLiteDatabase db) {
                    super.onCreate(db);
                    db.execSQL("INSERT INTO id_generator VALUES(1, 1, 1);");
                }
            })
//                .addMigrations(LocalDatabase.MIGRATION_1_2)
            .build();
}

Nympho

Answer 6

There are 3 ways of prepopulating of db
The first 2 is coping from assets and file which is described here
The third way is programmatical after db creation

Room.databaseBuilder(context, Database::class.java, "app.db")
    // ...
    // 1
    .createFromAsset(...)
    // 2
    .createFromFile(...)
    // 3
    .addCallback(DatabaseCallback())
    .build()

Here is the manual filling

class DatabaseCallback : RoomDatabase.Callback() {

    override fun onCreate(db: SupportSQLiteDatabase) = db.run {
        // Notice non-ui thread is here
        beginTransaction()
        try {
            execSQL(...)
            insert(...)
            update(...)
            delete(...)
            setTransactionSuccessful()
        } finally {
            endTransaction()
        }
    }
}

Rimola

Answer 7

I was struggling with this topic too and this solution worked for me:

// build.gradle

    def room_version = "2.2.5"

    // Room
    implementation "androidx.room:room-runtime:$room_version"
    kapt "androidx.room:room-compiler:$room_version"
    implementation "androidx.room:room-ktx:$room_version"

In your App class:

// virtually create the db
        val db = Room.databaseBuilder(
            appContext, AppDatabase::class.java,
            Res.getString(R.string.dbname)
        ).createFromAsset(Res.getString(R.string.source_db_name)).build()

        // first call to db really creates the db on filesystem
        db.query("SELECT * FROM " + Room.MASTER_TABLE_NAME, null)

Doodlebug