What format should the `.db` file take in a call to Room.createFromAsset("initialData.db")?

user_9387500 0
Viewed 878

Scenario

I am attempting to pre-populate a Room database with a few entries. The documentation on the api call is clear (developer.android docs), and I've read a good blog (Medium article), but I'm unfamiliar with the syntax/format for the actual prepackaged database file (developmentData.db in the example below). I cannot find any documentation anywhere I've searched.

Room
  .databaseBuilder(
      context.applicationContext, 
      MyDatabase::class.java, 
      "my_database")
  .fallbackToDestructiveMigration()
  .createFromAsset("database/developmentData.db")
  .build()

Thoughts

I've followed the documentation for exporting the schema. Here is what came out of that process, for reference. In this case, I'm just creating a dummy object for the sake of asking my question here.

{
  "formatVersion": 1,
  "database": {
    "version": 1,
    "identityHash": "0ad43a6714902eedbb90c1f77ab1ffcb",
    "entities": [
      {
        "tableName": "library_table",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`libraryId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `description` TEXT NOT NULL)",
        "fields": [
          {
            "fieldPath": "libraryId",
            "columnName": "libraryId",
            "affinity": "INTEGER",
            "notNull": true
          },
          {
            "fieldPath": "title",
            "columnName": "title",
            "affinity": "TEXT",
            "notNull": true
          },
          {
            "fieldPath": "description",
            "columnName": "description",
            "affinity": "TEXT",
            "notNull": true
          }
        ],
        "primaryKey": {
          "columnNames": [
            "libraryId"
          ],
          "autoGenerate": true
        },
        "indices": [],
        "foreignKeys": []
      }
    ],
    "setupQueries": [
      "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"0ad43a6714902eedbb90c1f77ab1ffcb\")"
    ]
  }
}

Here are the current contents of my developmentData.db, but it fails validation:

CREATE TABLE IF NOT EXISTS `library_table` (
`libraryId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`title` TEXT NOT NULL,
`description` TEXT NOT NULL
);
INSERT INTO `library_table` (title, description) VALUES ("Library 1", "A whopping amazing library!");
INSERT INTO `library_table` (title, description) VALUES ("Library the Second", "A smashing library indeed!");
INSERT INTO `library_table` (title, description) VALUES ("Thou Third Library", "Quite possibly the most amazing library ever.");
INSERT INTO `library_table` (title, description) VALUES ("Ye Olde Fouthe Librarye", "So many profound thoughts in here.");

Here is the error. It's not encrypted, so this must be an issue with the syntax. E/SQLiteLog: (26) file is encrypted or is not a database

Question

What format should the .db file that is referenced in the call to createFromAsset() take? If there is existing documentation somewhere, I would be glad for the reference.

android-room android
2 Answers

The solution here was to create an actual SQLite database binary, instead of the series of SQL statements I have above, and use that file. The process to create this was not immediately apparent from the documentation, so I'll explain it here in case someone else needs to be pointed in the right direction. @CommonsWare should get credit for this answer, as the one who pointed me in the correct direction.

Export the schema for the Room database in the app.

There is good documentation for this step here. In short, I needed to add this annotation to my RoomDatabase class. Take note of the exportSchema = true part. This causes a Gradle build to drop a .json file in app/schemas directory.

@Database(entities = [Library::class], version = 1, exportSchema = true)
abstract class MyDatabase : RoomDatabase() {

That json file contains tableName and createSql key:value pairs that I used in the next step. It looks like this:

"tableName": "library_table",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`libraryId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `description` TEXT NOT NULL)",

Create the SQLite binary file

I then made use of a free application called DB Browser for SQLite to actually create the .db binary that I needed, with reference to the .json mentioned above. Once I added a few records into it and saved, the data pre-populated without any issue.

Place the binary in the correct directory

One final note, that I didn't find immediately obvious from the documentation: The call to createFromAsset() looks, by default, in the /assets directory. This directory needs to be INSIDE the desired source set. So, for example, I placed my .db binary here:
<project-root>/app/src/main/assets/initialData.db

user_93875000

Unlikely server-side database systems (MySQL, Oracle, SQL Server, etc.), when closed, a SQLite database is a single file. createFromAsset() is expecting that sort of file, not a SQL script.

So, what I have usually done is:

  • Have the app create an empty database on a device using Room's schema, using standard Room stuff
  • Copy that database off the device (e.g., using Device File Explorer in Android Studio)
  • Use SQL Browser or sqlite3 or other tools to add data to my app's tables

That way, I'm guaranteed that the database will have the right structure and Room's metadata.

user_1151450
Related