| 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() }
}
}
