Calling 'insert' in a room database does not complete the transaction

Question

I'm having a problem with a simple @insert operation in a Room database. These are my classes:

The model class

@Entity(tableName = "my_model")
data class MyModel(
    @PrimaryKey @ColumnInfo(name = "id_model") var uid: Int,
    @ColumnInfo(name = "name") var firstName: String,
    @ColumnInfo(name = "last_name") var lastName: String
)

The DAO interface

interface MyModelDAO {
    @Insert
    fun createMyModel(myModel: MyModel)
}

The database

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

    companion object {
        private var INSTANCE: MyDb? = null

        fun getInstance(context: Context): MyDb? {
            if (INSTANCE == null) {
                synchronized(MyDb::class) {
                    INSTANCE = Room.databaseBuilder(context.applicationContext,
                        MyDb::class.java, "mydb.db")
                        .allowMainThreadQueries()//for testing purposes only
                        .build()
                }
            }
            return INSTANCE
        }

        fun destroyInstance() {
            INSTANCE = null
        }
    }


    abstract fun getMyModelDao(): MyModelDAO
}

And this is how I'm trying to insert an object.

val db = MinhaDb.getInstance(this)
db?.getMyModelDao()?.createMyModel(MyModel(111, "john", "doe"))

The thing is, the operation is not persisted in the db file. If I go into the databases folder, there is a mydb file, a wal and a shm file, and no tables are created in mydb. However, if i call db?.close() after the insert operation, the operation happens as its supposed to (the table is created and populated) and the wal and shm files are not there.

What am I missing here? I'm pretty sure I shouldn't have to call close() on the database. I've tried surrounding the insert call with a beginTransaction() and a endTransaction() calls to see if it changed anything, but it didn't.

UPDATE: As @musooff explained in the comments, apparently that's how sqlite dbs work. I queried the database after the insert calls and, indeed, the records where returned, even though the file itself seems empty.

Answer 1

---

TL;DR

Your code seems to be working fine. Don't get confused by the temporary files SQLite creates to operate.

1. The WAL and SHM files are temporary internal files that you shouldn't worry about.
2. If you are checking if the data is present examining the db file directly, the data might not be there yet. Wait until you close the connection.
3. Use a SQLiteBrowser to see if the data is present or not. You can check SQLiteBrowser or Android Debug Database
4. Instead of using a SQLiteBrowser, you can simple check if the data is present from your Android app using a SELECT query.

---

WAL and SHM files

As you have noticed, in your db directory you can find three generated files:

your-database-name
your-database-name-shm
your-database-name-wal

However, the only important for you, where the data really is, is your-database-name.

The wal file is used as a replacement of the Rollback Journal.

Beginning with version 3.7.0 (2010-07-21), SQLite supports a new transaction control mechanism called "write-ahead log" or "WAL". When a database is in WAL mode, all connections to that database must use the WAL. A particular database will use either a rollback journal or a WAL, but not both at the same time. The WAL is always located in the same directory as the database file and has the same name as the database file but with the string "-wal" appended.

What you mention about not being able to see the data in your database file while the wal file is still there, and then you close the connection and the wal file is gone and the data is finally persisted in the database, is the proper behavior while using the wal mechanism.

WAL dissapears

The WAL file exists for as long as any database connection has the database open. Usually, the WAL file is deleted automatically when the last connection to the database closes. (More here)

Transaction not being written immediately to database file

The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file. In the event of a crash or ROLLBACK, the original content contained in the rollback journal is played back into the database file to revert the database file to its original state. The COMMIT occurs when the rollback journal is deleted.

The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL. Multiple transactions can be appended to the end of a single WAL file.

Of course, one wants to eventually transfer all the transactions that are appended in the WAL file back into the original database. Moving the WAL file transactions back into the database is called a "checkpoint".

By default, SQLite does a checkpoint automatically when the WAL file reaches a threshold size of 1000 pages. (The SQLITE_DEFAULT_WAL_AUTOCHECKPOINT compile-time option can be used to specify a different default.) Applications using WAL do not have to do anything in order to for these checkpoints to occur. But if they want to, applications can adjust the automatic checkpoint threshold. Or they can turn off the automatic checkpoints and run checkpoints during idle moments or in a separate thread or process. (More here)

The SHM is just a temporary shared memory file, related to the WAL mechanism, whose only purpose is:

The shared-memory file contains no persistent content. The only purpose of the shared-memory file is to provide a block of shared memory for use by multiple processes all accessing the same database in WAL mode. (More here)