What causes "SQL statement is not read-only" in siyuan-note/siyuan?

Remove any DML (INSERT/UPDATE/DELETE) from inside the WITH/SELECT so the statement is genuinely read-only.

SQL statement is not read-only

Error message

SQL statement is not read-only

What it means

Returned by checkReadonlyStatement inside conn.Raw after the statement is prepared and sst.Readonly() (the SQLite sqlite3_stmt_readonly C API) reports false. The first-line keyword filter (isReadonlyQueryStatement) already passed — the statement starts with SELECT or WITH — but SQLite itself judges that executing it could modify the database. The classic case is a WITH clause whose body contains INSERT/UPDATE/DELETE.

Source

Thrown at kernel/sql/stmt_validate.go:219

	defer conn.Close()

	return conn.Raw(func(dc any) error {
		sqliteConn, ok := dc.(*sqlite3.SQLiteConn)
		if !ok {
			return fmt.Errorf("SQL driver connection type is unexpected: %T", dc)
		}
		ds, err := sqliteConn.Prepare(stmt)
		if err != nil {
			return err
		}
		defer ds.Close()

		sst, ok := ds.(*sqlite3.SQLiteStmt)
		if !ok {
			return fmt.Errorf("SQL driver statement type is unexpected: %T", ds)
		}
		if !sst.Readonly() {
			return errors.New("SQL statement is not read-only")
		}
		return nil
	})
}

// isReadonlyQueryStatement 仅允许查询语句进入 SQLite prepare，提前拒绝会被 sqlite3_stmt_readonly
// 视为只读的 ATTACH、DETACH 和事务控制语句。WITH 中的写操作仍由 sqlite3_stmt_readonly 拒绝。
func isReadonlyQueryStatement(stmt string) bool {
	stmt = strings.TrimSpace(stmt)
	for "" != stmt {
		switch {
		case strings.HasPrefix(stmt, "--"):
			if lineEnd := strings.IndexByte(stmt, '\n'); 0 <= lineEnd {
				stmt = strings.TrimSpace(stmt[lineEnd+1:])
				continue
			}
			return false
		case strings.HasPrefix(stmt, "/*"):

View on GitHub (pinned to 251596fc0d)

Solutions

Remove any DML (INSERT/UPDATE/DELETE) from inside the WITH/SELECT so the statement is genuinely read-only.
Move the write operation to a proper write API endpoint; the readonly SQL surface will never accept it.
Run `EXPLAIN` locally in a sqlite3 shell to confirm sqlite3_stmt_readonly returns true before submitting.

Example fix

// before
stmt := "WITH moved AS (DELETE FROM blocks RETURNING *) SELECT * FROM moved"
err := sql.CheckReadonlyStatement(stmt) // -> "SQL statement is not read-only"

// after: keep the query side read-only; perform writes through the write API
stmt := "SELECT id, content FROM blocks WHERE box = ?"
err := sql.CheckReadonlyStatement(stmt)

Defensive patterns

Strategy: try-catch

Try / catch

err := sql.CheckReadonlyStatement(stmt)
if err != nil {
    if strings.Contains(err.Error(), "not read-only") {
        // statement prepared but SQLite judged it mutative (e.g. WITH containing DML)
        return fmt.Errorf("%q is not a readonly query even though it starts with SELECT/WITH; rewrite without DML", stmt)
    }
    return err
}

Prevention

Never embed INSERT/UPDATE/DELETE inside a WITH clause intended for the readonly API.
Test novel CTEs against sqlite3 locally and confirm sqlite3_stmt_readonly returns true.
Treat any 'not read-only' error as authoritative — do not retry with minor variations.

When it happens

Trigger: Submitting a statement that begins with WITH (or SELECT) but whose prepared form is non-readonly according to sqlite3_stmt_readonly. The most common shape is a CTE-written write: `WITH x AS (...) INSERT INTO ...` or a WITH ... UPDATE/DELETE. Also reachable through /api/query, /api/search, the MCP sql tool, the CLI sql command, and asset-content query helpers.

Common situations: A plugin author tries to smuggle a write past the keyword filter by prefixing `WITH`; using data-modifying CTEs; relying on the fact that the keyword check only inspects the first token.

Related errors

AI-assisted analysis of siyuan-note/siyuan@251596fc0d (2026-08-12). Data as JSON: /api/errors/78130773fe6f2e33. Report an issue: GitHub.