Room Rawquery Readonly Vacuum Agent Skill

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 vacuum() {
        useWriterConnection { it.execSQL("VACUUM") }
    }
}

And at the call site, checkpoint first, then compact, and swallow the failure:

// adapted
runCatching {
    localDataSource.checkpoint()   // pragma wal_checkpoint(full)
    localDataSource.vacuum()
}.onFailure { Logger.e(TAG, "compaction after clearing history failed: ${it.message}") }

Traps

A working raw checkpoint is not evidence that raw statements can write. PRAGMA wal_checkpoint

is accepted on a read-only connection. So a codebase can carry a raw-query checkpoint helper that

works perfectly for years, and everyone reasonably concludes the escape hatch writes fine — until the

first statement that genuinely needs a writer lands next to it and fails. If you keep the checkpoint

helper on the DAO, leave a comment beside it saying why compaction deliberately does *not* live

there; otherwise someone will move it back.

Compaction cannot run inside a transaction. SQLite refuses VACUUM in one — plainly, with

cannot VACUUM from within a transaction. The trap is not the message but the transaction you

cannot see: ORM helpers open one for you, so wrapping the call in withTransaction { } — or

running it through any generated path that begins its own transaction — turns a working statement

into a failing one whose error points at a BEGIN that appears nowhere in your code. Use an exec

that prepares and steps the statement without opening a transaction (execSQL).

Compact last, outside the deletes. Anything that leaves a transaction open around the call

reintroduces the trap above, and compaction on a database still mid-sweep has less to reclaim.

Skip the checkpoint and most of the space stays gone. The pages a bulk delete frees are sitting

in the write-ahead log; the checkpoint folds them back into the main file so compaction has something

to reclaim. Without it the operation "succeeds" and the file barely shrinks — which reads to the user

as the feature not working.

A compaction failure is not a failure of the operation. Every delete has already committed by the

time compaction runs. Letting it throw surfaces an error toast for work that actually succeeded, and

the user will retry the whole expensive sweep for nothing. Log it and move on: the only cost is disk

space the next run reclaims.

Compaction rewrites the entire file. It needs room for a second copy and it is not instant on a

large database. Run it where a user expects a long operation (an explicit "clear cache" action), not

on a hot path or at startup.

Verifying it

• Read the generated code, do not reason about the generator. Open the generated DAO

implementation for your method and look at the flag the call site passes — the read-only decision is

written there in plain sight, for every method, and it is the only source that stays correct across

library versions.

• Ask the connection what it is. SELECT * FROM pragma_query_only; executed through the same path

as your failing statement answers "am I on a reader?" directly, without guessing from the error.

• Measure the file, not the row counts. Record the database file size before the sweep, after the

deletes, and after compaction. The middle number barely moves even on a successful sweep — that gap

is exactly what compaction is for, and it is the only way to tell a working compaction from a

silently swallowed one.

• Check the log path is reachable. Because the failure is deliberately swallowed, the log line is

the entire signal. Force it once (wrap the call in a transaction on purpose) and confirm the message

actually appears.