Determining if an SQLite statement is readonly

16 Mar 2026

While optimizing my Go code for dealing with SQLite databases, I needed to predetermine if a statement is a read or a write statement. During my research, I learned that SQLite has a sqlite3_stmt_readonly() function.

This means, I could do something like this:

// IsReadOnly checks if a SQL statement is read-only using SQLite's built-in API
func IsReadOnly(db *sql.DB, query string) (bool, error) {
    // Get the underlying sqlite3 connection
    conn, err := db.Conn(context.Background())
    if err != nil {
        return false, err
    }
    defer conn.Close()

    var isReadOnly bool
    err = conn.Raw(func(c interface{}) error {
        sqliteConn, ok := c.(*sqlite3.SQLiteConn)
        if !ok {
            return fmt.Errorf("not a sqlite3 connection")
        }

        // Prepare the statement to inspect it
        stmt, err := sqliteConn.Prepare(query)
        if err != nil {
            return err
        }
        defer stmt.Close()

        // Cast to access SQLite-specific methods
        sqliteStmt, ok := stmt.(*sqlite3.SQLiteStmt)
        if !ok {
            return fmt.Errorf("not a sqlite3 statement")
        }

        isReadOnly = sqliteStmt.Readonly()
        return nil
    })

    return isReadOnly, err
}

The only problem is the performance penalty of running this function for every statement.

To mitigate this, I decided on a quick test to determine if the statement was definitely a write, which allows me to bypass the more expensive SQLite API call.

The quick test checks whether the statement starts with a particular keyword indicating a write:

s := strings.TrimLeft(query, " \t\n\r")
n := min(8, len(s))
prefix := strings.ToUpper(s[:n])
switch {
case strings.HasPrefix(prefix, "INSERT"),
    strings.HasPrefix(prefix, "UPDATE"),
    strings.HasPrefix(prefix, "DELETE"),
    strings.HasPrefix(prefix, "CREATE"),
    strings.HasPrefix(prefix, "DROP"),
    strings.HasPrefix(prefix, "ALTER"),
    strings.HasPrefix(prefix, "REPLACE"):
    return true
}

This code runs on the order of nanoseconds compared to the sqlite3_stmt_readonly(), which is on the order of microseconds, saving a good bit of overhead, when dealing with writes.

Tagged with: