SQLiteException: no such table: database-notes (code 1 SQLITE_ERROR)

Question

I'm trying to migrate a new database version. The only thing changed is an added column. I always get the following error:

android.database.sqlite.SQLiteException: no such table: database-notes (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE 'database-notes' ADD COLUMN image TEXT

I don't understand why I get this exception, because my table is named database-notes as written in the .build() call.

This is my database class:

@Database(
version = 2,
entities = [Note::class],
exportSchema = true)

abstract class AppDatabase : RoomDatabase() {
abstract fun noteDao(): NoteDAO

companion object {
    fun build(context: Context) = Room.databaseBuilder(context, AppDatabase::class.java, "database-notes")
        .addMigrations(MIGRATION_1_2).build()
    }
}

val MIGRATION_1_2 = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
       database.execSQL("ALTER TABLE 'database-notes' ADD COLUMN image TEXT")
    }
}

The database name was exactly the same in the previous version. I copied it to rule out typos. What have I overlooked here? Thank you in advance!

Answer 1

because my table is named database-notes

It would appear not, due to the failure, and is probably a misunderstanding of the difference between the database name and table name(s).

A Database can have multiple tables. The database name is the name of the file itself (the container of the components such as tables, indexes, views and triggers).

In your code database-notes, as per the 3rd parameter to the Room.databaseBuilder is the name of the database (the file).

With Room the table names are derived from the classes that are both annotated with @Entity and provided, via the entities parameter of the @Database annotation. In your case the Note class.

The name of the table will be Note unless you use the tableName = parameter of the @Entity annotation to provide another name.

Example

If the following were your Note class :-

@Entity // No tableName parameter so the table name is the class name
data class Note(
    @PrimaryKey
    var noteId: Long? = null,
    var noteText: String,
    var image: String
)

Then the table name would be Note (the name of the class)

If the Note class were :-

@Entity(tableName = "notes") //<<<<< specifically names the table
data class Note(
    @PrimaryKey
    var noteId: Long? = null,
    var noteText: String,
    var image: String
)

The the table name would be notes (as specified by the tableName = parameter of the @Entity annotation).