room-rawquery-readonly-vacuum

name	room-rawquery-readonly-vacuum
description	Why a raw-query DAO method runs on a read-only connection, so database compaction fails there with "attempt to write a readonly database" while a checkpoint on the same connection succeeds and hides the problem. Reach for it when a bulk delete frees no disk space, or when a raw statement reports a readonly database you clearly opened for writing.

Raw queries take a reader connection

An ORM picks the connection for a statement by deciding whether that statement writes. When the statement is a parsed, annotated query the generator can read it and see a DELETE, so it asks for a writer. When the statement arrives as an opaque string through a raw-query escape hatch, the generator cannot know — and it defaults to read-only.

In Room specifically (as of Room 2.8), KSP generates a @RawQuery method as performSuspending(__db, true, false) — positional booleans, in order isReadOnly, inTransaction. Room then takes a reader connection, and readers are opened with PRAGMA query_only = 1. Under that pragma VACUUM fails outright with "attempt to write a readonly database".

Where compaction belongs

On the database class, not the DAO — the DAO has no way to ask for a writer connection at all:

abstract class MusicDatabase : RoomDatabase() {
    abstract fun getDatabaseDao(): DatabaseDao

    suspend fun  {
        useWriterConnection { it.execSQL() }
    }
}