Issue Prepopulate Room Database – Database Inspector empty

I am new to Android development and I am trying to develop a very simple app. I want to create a prepopulated database which has one table . The table is about users and it has only three columns id, name , and profession. The only usage of the database will be to search via the name of every user and find their profession. So I only need to prepopulate it with some data.

My issue is that nothing is happening when i run the databse nothing is happening the database is not even been created. cant see anything in the dtabase inspector

As I have read from the documentation of Room database https://developer.android.com/training/data-storage/room/prepopulate I just need to add the following code

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

So I created my small database in SqliteStudio and now I am trying to copy it with the Room database in Android. Bellow you can see a screenshot of my table users_table from sqliteStudio

Dependencies

// Room components
implementation "androidx.room:room-runtime:$rootProject.roomVersion"
implementation 'androidx.wear:wear:1.1.0'
annotationProcessor "androidx.room:room-compiler:$rootProject.roomVersion"
androidTestImplementation "androidx.room:room-testing:$rootProject.roomVersion"

// Lifecycle components
implementation "androidx.lifecycle:lifecycle-viewmodel:$rootProject.lifecycleVersion"
implementation "androidx.lifecycle:lifecycle-livedata:$rootProject.lifecycleVersion"
implementation "androidx.lifecycle:lifecycle-common-java8:$rootProject.lifecycleVersion"

implementation 'androidx.appcompat:appcompat:1.3.1'
implementation 'com.google.android.material:material:1.4.0'
implementation 'androidx.constraintlayout:constraintlayout:2.1.0'
testImplementation 'junit:junit:4.13.2'
androidTestImplementation 'androidx.test.ext:junit:1.1.3'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
compileOnly 'com.google.android.wearable:wearable:2.8.1'
implementation "androidx.lifecycle:lifecycle-extensions:2.2.0"

gradle

ext {
    appCompatVersion = '1.3.0'
    constraintLayoutVersion = '2.0.4'
    coreTestingVersion = '2.1.0'
    lifecycleVersion = '2.3.1'
    materialVersion = '1.3.0'
    roomVersion = '2.3.0'
    // testing
    junitVersion = '4.13.2'
    espressoVersion = '3.1.0'
    androidxJunitVersion = '1.1.2'
}

Bellow you can see also the code from my Dao, DatabaseClass, Entity, Repository, ViewModel

User

@Entity(tableName = "users_table")
public class User {

    @PrimaryKey(autoGenerate = true)
    @NonNull
    private int id;

    @ColumnInfo(name = "name")
    @NonNull
    private String name;

    @ColumnInfo(name = "profession")
    @NonNull
    private String profession;

    public User(int id, @NonNull String name, @NonNull String profession) {
        this.id = id;
        this.name = name;
        this.profession = profession;
    }

all getters and setters also exist

UserDao

@Dao
public interface UserDao {

    @Query("SELECT * FROM users_table")
    LiveData<List<User>> getAll();

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    void insert(User user);

    @Delete
    void delete(User user);

    @Query("DELETE FROM users_table")
    void deleteAll();
}

UserDatabase

@Database(entities = {User.class}, version = 1, exportSchema = false)
public abstract class UserDatabase extends RoomDatabase {
    public abstract UserDao userDao();
    private static volatile UserDatabase INSTANCE;

    private static final int NUM_OF_THREADS = 4;


    public static final ExecutorService databaseWriteExecutor
            = Executors.newFixedThreadPool(NUM_OF_THREADS);

    public static UserDatabase getDatabase(final Context context){
        if (INSTANCE == null){
            synchronized (UserDatabase.class){
                if (INSTANCE == null){
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            UserDatabase.class, "user.db")
                            .createFromAsset("users.db")
                            .build();
                }
            }
        }
        return INSTANCE;
    }
}

UserRepository

public class UserRepository {


    private UserDao userDao;
    private LiveData<List<User>> allUsers;

    public UserRepository(Application application) {
        UserDatabase db = UserDatabase.getDatabase(application);
        userDao = db.userDao();
        allUsers = userDao.getAll();

    }

    public LiveData<List<User>> getAllData() { return allUsers; }

    public void insert(User user){
        UserDatabase.databaseWriteExecutor.execute(() -> {
            userDao.insert(user);
        });
    }
    public void deleteAll(){
        UserDatabase.databaseWriteExecutor.execute(() -> {
            userDao.deleteAll();
        });
    }
}

UserViewModel

public class UserViewModel extends AndroidViewModel {

    public static UserRepository repository;
    public final LiveData<List<User>> allUsers;


    public UserViewModel(@NonNull Application application) {
        super(application);
        repository = new UserRepository(application);
        allUsers = repository.getAllData();
    }

    public LiveData<List<User>> getAllUsers() { return allUsers; }
    public static void insert(User user) { repository.insert(user); }
}

Answer

This is only a guess that you are trying to check to see if the database exists before actually accessing the database. However, it’s a relatively common issue. It also assumes that you are using a compatible device (e.g. must be Android API 26+ for database inspector).

When a database instance is retrieved, in your case, when in an activity/fragment using UserDatabase mydatabase = UserDatabase.getDatabase(); does not actually open the database, rather it is only when an attempt is made to actually extract/insert/update/delete data that the database is opened, and in your case that the users.db file/asset is copied from the package to the final location on the device. As such unless the attempt to access the database is made then neither Database Inspector nor Device File Explorer will show anything.

You can temporarily (or permanently) force an open by adding a line to the getDatabase method in the UserDatabase class. e.g. you could use :-

public abstract class UserDatabase extends RoomDatabase {
    public abstract UserDao userDao();
    private static volatile UserDatabase INSTANCE;

    private static final int NUM_OF_THREADS = 4;


    public static final ExecutorService databaseWriteExecutor
            = Executors.newFixedThreadPool(NUM_OF_THREADS);

    public static UserDatabase getDatabase(final Context context){
        if (INSTANCE == null){
            synchronized (UserDatabase.class){
                if (INSTANCE == null){
                    INSTANCE = Room.databaseBuilder(context,
                            UserDatabase.class, "user.db")
                            .allowMainThreadQueries() //NOTE ADDED for convenience of demo
                            .createFromAsset("users.db")
                            .build();
                    /*<<<<<<<<<< ADDED to FORCE an open of the database >>>>>>>>>>*/
                    SupportSQLiteDatabase sdb = INSTANCE.getOpenHelper().getWritableDatabase();
                }
            }
        }
        return INSTANCE;
    }
}

Demonstration

The following is a demonstration. It uses your code as posted in the question. However with the following changes:-

  • Getters and Setters have been added to the User class.
  • The Room.databaseBuilder’s context uses the passed context rather than using the context to get the context (not an issue as only reason that a context is required is to get the default path to the database).
  • .allowMainThreadQueries has been added to simplify the demo (as proof of the entire process).

First a database was created using SQLite Studio, it was populated with 3 rows, as per :-

  • enter image description here

The Structure being :-

  • enter image description here

The database file was copied (after closing the connection in SQLite Studio, opening the connection again and then closing it again to verify that the database was as it should be) (and renamed to users.db) to the assets folder of the project (after creating the folder), as per :-

enter image description here

An activity MainActivity was used to demonstrate this at first being :-

public class MainActivity extends AppCompatActivity {

    UserDatabase db;
    UserDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = UserDatabase.getDatabase(this); // Does not open the database
        dao = db.userDao(); // Does not open the database
    }
}

Run 1

The above was run and from Android Studio the following was the result (noting the use of both Device File Explorer and Database Inspector):-

  • enter image description here

  • As can be seen nothing is shown in Database Inspector (App Inspection) nor in Device File Explorer after the App was successfully run.

  • NOTE It can be seen that the getWritableDatabase line has been commented out (to demonstrate without).

  • Conclusion just getting an instance of the UserDatabase and getting an instance of the UserDao doe not result in the database being created.

Run 2

This run demonstrates that accessing the database results in the database being created. It uses an non-live data version of the getAll query. In UserDao the following was added :-

@Query("SELECT * FROM users_table")
List<User> getAllUsers();

Additionally MainActivity was changed to include (uncomment) the previously commented out code :-

    for (User user: dao.getAllUsers()) {
        Log.d("USERINFO","User is " + user.getName() + " profession is " + user.getProfession());
    }
  • i.e. actually accessing the data.

Now when run:-

  • enter image description here

and the log contains :-

D/USERINFO: User is Fred profession is Doctor
D/USERINFO: User is Mary profession is Solicitor
D/USERINFO: User is Jane profession is Vet
  • i.e. the data is as per the prepopulated database.

    • Note that there are now 3 files, the -wal and -shm are logging files that SQLite handles.
    • Note you should ensure that they DO NOT exist when creating the asset. If they do repeat the open/close connection until they do not. The createFromAsset will not (I believe) cope with the additional/extra files. If they do exist then you may get unexpected results (e.g. corruption, missing data)

Run 3

To demonstrate forcing the open in the getDatabase method the code that access the database in MainActivity was again commented out as per *Run 1 and in the UserDatabase the commented out //SupportSQLiteDatabase sdb = INSTANCE.getOpenHelper().getWritableDatabase(); was changed to be included an be SupportSQLiteDatabase sdb = INSTANCE.getOpenHelper().getWritableDatabase(); AND IMPORTANTLY the App was uninstalled (once the database file exists it will not be copied from the assets folder again, so deleting the database (which uninstalling the App will do)).

  • could have deleted the database files

  • enter image description here

No Asset File to be copied

After uninstalling the App and renaming the asset file to not_the_users.db then the result is a crash/exception and the log includes :-

2021-08-16 08:12:11.884 26625-26625/a.a.so68791243javaroomnodatabase E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so68791243javaroomnodatabase, PID: 26625
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so68791243javaroomnodatabase/a.a.so68791243javaroomnodatabase.MainActivity}: java.lang.RuntimeException: Unable to copy database file.
  • So this eliminates the no assets file, IF you are accessing the database (again the force open would ensure that such and error is detected early).

Run 4 – Incompatible database

For this run the users_table table was altered by changing the type of the profession column to STRING, copied into the assets folder as users.db and the App uninstalled. The result, is the anticipated Expected/Found mismatch crash/exception as per:-

2021-08-16 08:30:42.523 27239-27239/a.a.so68791243javaroomnodatabase E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so68791243javaroomnodatabase, PID: 27239
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so68791243javaroomnodatabase/a.a.so68791243javaroomnodatabase.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: users_table(a.a.so68791243javaroomnodatabase.User).
     Expected:
    TableInfo{name='users_table', columns={name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, profession=Column{name='profession', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='users_table', columns={profession=Column{name='profession', type='STRING', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='null'}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
  • Again, this eliminates a typical issue with pre-populated database IF you are accessing the database.