> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Solana Functions

Status: Experimental.

Solana programs store their instructions' arguments and their events as bytes, in a format each program defines. These
functions turn those bytes into columns: `borsh_decode` decodes bytes in [Borsh](https://borsh.io), the format most
programs use, from a layout you describe, and `anchor_decode_event` decodes the events of programs built with
[Anchor](https://www.anchor-lang.com), the most common framework, from the program's IDL.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* Nothing else for the first examples, which decode values copied from a Solana mainnet transaction. To decode chain
  data, a Solana [raw dataset](/datasets/raw-datasets), synced through a [Solana provider](/providers/solana). The
  examples on chain data use the one [Solana](/providers/solana#create-a-dataset) creates,
  `acme/solana_mainnet`, which holds slots 456,192,000 to 456,192,099.

## What to decode

In a [Solana dataset](/datasets/tables/solana), two columns hold program data:

| Column                      | Type          | Holds                                                                                               | Decode it with                                       |
| --------------------------- | ------------- | --------------------------------------------------------------------------------------------------- | ---------------------------------------------------- |
| `instructions.data`         | `List(UInt8)` | Each instruction's arguments, and the events Anchor programs emit with `emit_cpi!`                  | `borsh_decode`, and `anchor_decode_event` for events |
| `transactions.log_messages` | `List(Utf8)`  | Each transaction's log, with the events Anchor programs emit with `emit!`, as `Program data:` lines | `anchor_decode_event`, after unnesting the list      |

Bytes don't say what they hold, so you give their layout: a descriptor, or an Anchor IDL, the JSON file Anchor generates
to describe a program. It's a string literal, so Amp knows the result's type before it reads any rows: every row
decodes to that type, and a row whose bytes don't fit the layout returns `NULL`.

## Functions

### `borsh_decode`

Decodes Borsh bytes with the layout a [descriptor](/querying/functions/solana#borsh-descriptors) gives.

```sql
borsh_decode(data, descriptor)
```

| Argument     | Type                      | Description                                                                  |
| ------------ | ------------------------- | ---------------------------------------------------------------------------- |
| `data`       | `Binary` or `List(UInt8)` | The bytes, like an instruction's `data`.                                     |
| `descriptor` | string literal            | Their layout, like `'{ amount: u64, memo: Option<string> }'`. Can't be NULL. |

It returns the [type the descriptor maps to](/querying/functions/solana#types), like a struct for `{ … }`, or `NULL` when the bytes don't match.

Here, the data of a `swap_base_input` instruction to Raydium's CPMM program, which swaps tokens. Like every Anchor
instruction, it starts with an 8-byte discriminator that identifies the instruction, followed by its arguments:

```sql
SELECT borsh_decode(
  X'8fbe5adac41e33de1f0c9057570400000000000000000000',
  '{ discriminator: [u8; 8], amount_in: u64, minimum_amount_out: u64 }'
) AS swap
```

```text [Output]
+------------------------------------------------------------------------------------+
| swap                                                                               |
+------------------------------------------------------------------------------------+
| {discriminator: 8fbe5adac41e33de, amount_in: 4773177723935, minimum_amount_out: 0} |
+------------------------------------------------------------------------------------+
```

* Read a field with a dot, like `swap.amount_in`, or brackets, like `swap['amount_in']`.
* Name the result with `AS`. Otherwise, its column is named `borsh_decode_` followed by a hash.

**Bad input.** A row returns `NULL` when its data is `NULL`, or doesn't match the descriptor exactly: too few bytes,
bytes left over, a `bool` other than 0 or 1, a `string` that isn't valid UTF-8, an `Option` tag other than 0 or 1, a
`NaN` float, or a `List(UInt8)` with a `NULL` element. So does data over 1 MiB, or a row over the [limits](/querying/functions/solana#limits):

```sql
SELECT
  borsh_decode(X'2a', 'u8') AS exact,
  borsh_decode(X'2a00', 'u8') AS too_long,
  borsh_decode(X'2a', 'u16') AS too_short
```

```text [Output]
+-------+----------+-----------+
| exact | too_long | too_short |
+-------+----------+-----------+
| 42    |          |           |
+-------+----------+-----------+
```

An `Option` that's `None` is `NULL` too, so for a descriptor that's an `Option` itself, `NULL` doesn't tell you which.

The query fails before it runs when:

| Cause                                                   | Message                                                                    |
| ------------------------------------------------------- | -------------------------------------------------------------------------- |
| The descriptor isn't a string literal                   | `borsh_decode: expected a non-null string literal for descriptor argument` |
| The descriptor has a type it doesn't support            | `invalid Borsh descriptor: unsupported Borsh descriptor 'u256'`            |
| The descriptor's syntax is wrong                        | `invalid Borsh descriptor: expected '>' at byte 6`                         |
| The descriptor repeats a field name, or nests `Option`s | `invalid Borsh descriptor: invalid Borsh schema`                           |
| `data` is another type                                  | `borsh_decode: expected Binary or List(UInt8) input, got Utf8`             |

On a Solana dataset, decode the `data` of `instructions`. Bytes of other instructions can happen to fit the layout, so
keep only the rows with the instruction's discriminator:

```sql
SELECT slot, tx_index, swap.amount_in AS amount_in
FROM (
  SELECT slot, tx_index,
    borsh_decode(data, '{ discriminator: [u8; 8], amount_in: u64, minimum_amount_out: u64 }') AS swap
  FROM "acme/solana_mainnet".instructions
)
WHERE swap.discriminator = X'8fbe5adac41e33de'
ORDER BY slot, tx_index
LIMIT 5
```

```text [Output]
+-----------+----------+-------------------+
| slot      | tx_index | amount_in         |
+-----------+----------+-------------------+
| 456192000 | 1264     | 44821530389306299 |
| 456192001 | 36       | 3552820384128     |
| 456192002 | 12       | 19883665          |
| 456192002 | 19       | 89861237291       |
| 456192004 | 834      | 11935305849       |
+-----------+----------+-------------------+
```

Anchor derives an instruction's discriminator from its name, so instructions of the same name in other programs have it
too. To keep one program's, join the instruction to its transaction's account list, where `program_id_index` points:
the [`messages`](/datasets/tables/solana#messages) table's `account_keys`, followed by the
[`transactions`](/datasets/tables/solana#transactions) table's `loaded_addresses_writable` and
`loaded_addresses_readonly`. Programs that inner instructions call can be in the last two:

```sql
SELECT
  array_concat(m.account_keys, t.loaded_addresses_writable, t.loaded_addresses_readonly)[i.program_id_index + 1] AS program,
  COUNT(*) AS swaps
FROM "acme/solana_mainnet".instructions i
JOIN "acme/solana_mainnet".messages m ON i.slot = m.slot AND i.tx_index = m.tx_index
JOIN "acme/solana_mainnet".transactions t ON i.slot = t.slot AND i.tx_index = t.tx_index
WHERE i.data[1:8] = [143, 190, 90, 218, 196, 30, 51, 222]
GROUP BY 1
```

```text [Output]
+----------------------------------------------+-------+
| program                                      | swaps |
+----------------------------------------------+-------+
| CPMMoo8L3F4NbTegBCKVNunggL7H1ZpdTHKxQB5qKP1C | 62    |
+----------------------------------------------+-------+
```

Lists count from 1 in SQL, hence the `+ 1`. Here, all 62 are Raydium's, but 27 of them call it from an account loaded
from a lookup table, which `account_keys` alone misses.

### `anchor_decode_event`

Decodes an event an Anchor program emitted, with the layout the program's [IDL](/querying/functions/solana#anchor-idls) gives.

```sql
anchor_decode_event(data, idl, event)
```

| Argument | Type                               | Description                                                                                                |
| -------- | ---------------------------------- | ---------------------------------------------------------------------------------------------------------- |
| `data`   | `Utf8`, `Binary`, or `List(UInt8)` | The event: a whole `Program data:` log line as `Utf8`, or a whole `emit_cpi!` instruction's data as bytes. |
| `idl`    | string literal                     | The program's Anchor IDL, in JSON. Can't be NULL.                                                          |
| `event`  | string literal                     | The event's name in the IDL, like `'SwapEvent'`. Can't be NULL.                                            |

It returns a struct with the event's fields, named and typed as the IDL says (see [types](/querying/functions/solana#types)), or `NULL` when the
data isn't that event.

Anchor programs emit events in one of two ways, and the type of `data` tells the function which one to expect:

* **`emit!`** writes the event to the transaction's log, as a line that starts with `Program data: `, followed by the
  event in base64. Pass the whole line, as `Utf8`.
* **`emit_cpi!`** has the program call itself with the event as the instruction's data, after 8 bytes that mark it as
  an event: `e445a52e51cb9a1d`. Pass the instruction's whole data, as `Binary` or `List(UInt8)`.

Either way, the event starts with its discriminator, from the IDL, followed by its fields in Borsh.

Here, the event Raydium's CPMM program logged for the swap above, with the parts of its IDL the function reads:

```sql
SELECT swap.input_amount AS input_amount, swap.output_amount AS output_amount, swap.trade_fee AS trade_fee
FROM (
  SELECT anchor_decode_event(
    'Program data: QMbN6CYIceJgb8AWGHxmvVXFUZ1DZ8iIAgoi28NU4yLqUa2eNcMxyR20MgxN/gAAFsFzqAEAAABH7YY6TAQAADVp9wYAAAAA2B4JHQsAAAAAAAAAAAAAAAHR/MNBLUIjwEE+cK6B3Vxs3LtsYMi1UPfH+ZhcGQP6B1cbompK7Ha6TeETMeh/mcbXPNMXU2yU9m9RpuBWah+BE3UlwAIAAACnAxIAAAAAAAA=',
    '{
      "metadata": { "spec": "0.1.0" },
      "events": [{ "name": "SwapEvent", "discriminator": [64, 198, 205, 232, 38, 8, 113, 226] }],
      "types": [{
        "name": "SwapEvent",
        "type": {
          "kind": "struct",
          "fields": [
            { "name": "pool_id", "type": "pubkey" },
            { "name": "input_vault_before", "type": "u64" },
            { "name": "output_vault_before", "type": "u64" },
            { "name": "input_amount", "type": "u64" },
            { "name": "output_amount", "type": "u64" },
            { "name": "input_transfer_fee", "type": "u64" },
            { "name": "output_transfer_fee", "type": "u64" },
            { "name": "base_input", "type": "bool" },
            { "name": "input_mint", "type": "pubkey" },
            { "name": "output_mint", "type": "pubkey" },
            { "name": "trade_fee", "type": "u64" },
            { "name": "creator_fee", "type": "u64" },
            { "name": "creator_fee_on_input", "type": "bool" }
          ]
        }
      }]
    }',
    'SwapEvent'
  ) AS swap
)
```

```text [Output]
+---------------+---------------+-------------+
| input_amount  | output_amount | trade_fee   |
+---------------+---------------+-------------+
| 4725445946695 | 116877621     | 11813614867 |
+---------------+---------------+-------------+
```

And the event Meteora's DLMM program emitted with `emit_cpi!`, in the same transaction:

```sql
SELECT swap.amount_in AS amount_in, swap.amount_out AS amount_out, swap.fee AS fee, swap.fee_bps AS fee_bps
FROM (
  SELECT anchor_decode_event(
    X'e445a52e51cb9a1d516ce3becdd00ac4aac654ed95b2b8abef6cb665280dea154f42f9568509c02cb8b55886159235a1d35d812dc3b74e3659ec29e0a4ee781e3e3652f57c57fd2ac5e7a062ea6fc61e7d0300007d0300003569f7060000000021229b400000000001aa7904000000000091720000000000003f4a26000000000000000000000000000000000000000000',
    '{
      "metadata": { "spec": "0.1.0" },
      "events": [{ "name": "Swap", "discriminator": [81, 108, 227, 190, 205, 208, 10, 196] }],
      "types": [{
        "name": "Swap",
        "type": {
          "kind": "struct",
          "fields": [
            { "name": "lb_pair", "type": "pubkey" },
            { "name": "from", "type": "pubkey" },
            { "name": "start_bin_id", "type": "i32" },
            { "name": "end_bin_id", "type": "i32" },
            { "name": "amount_in", "type": "u64" },
            { "name": "amount_out", "type": "u64" },
            { "name": "swap_for_y", "type": "bool" },
            { "name": "fee", "type": "u64" },
            { "name": "protocol_fee", "type": "u64" },
            { "name": "fee_bps", "type": "u128" },
            { "name": "host_fee", "type": "u64" }
          ]
        }
      }]
    }',
    'Swap'
  ) AS swap
)
```

```text [Output]
+-----------+------------+--------+---------+
| amount_in | amount_out | fee    | fee_bps |
+-----------+------------+--------+---------+
| 116877621 | 1083908641 | 293290 | 2509375 |
+-----------+------------+--------+---------+
```

**Bad input.** A row returns `NULL` when its data is `NULL`, or isn't the event:

* A log line that doesn't start with `Program data: `, or whose rest isn't valid base64.
* Instruction data that doesn't start with the `emit_cpi!` marker. To decode an event's bytes without it, use
  `borsh_decode`, with an `[u8; N]` field for the discriminator.
* A discriminator that isn't the event's: another event, or another program's data.
* Event fields that don't match the IDL, for any of the reasons [`borsh_decode`](/querying/functions/solana#borsh_decode) returns `NULL`.

The query fails before it runs when:

| Cause                                                | Message                                                                                                  |
| ---------------------------------------------------- | -------------------------------------------------------------------------------------------------------- |
| The IDL or event isn't a string literal              | `anchor_decode_event: expected a non-null string literal for event name argument`                        |
| The IDL isn't JSON                                   | `invalid Anchor event schema: invalid IDL JSON`                                                          |
| The IDL is an older one, without `metadata`          | `invalid Anchor event schema: IDL metadata must be an object`                                            |
| The IDL is an older one, without `metadata.spec`     | `invalid Anchor event schema: IDL metadata must contain string 'spec'`                                   |
| The IDL's `metadata.spec` isn't `0.1.0`              | `invalid Anchor event schema: unsupported Anchor IDL metadata spec '0.0.1'; expected 0.1.0`              |
| The IDL has no event, or several, with that name     | `invalid Anchor event schema: expected exactly one event named 'Swap', found 0`                          |
| The event uses a type the function doesn't support   | `invalid Anchor event schema: reachable enum type 'Trade' is unsupported`                                |
| Another event's discriminator starts like this one's | `invalid Anchor event schema: selected event discriminator has a prefix collision with event at index 1` |
| `data` is another type                               | `anchor_decode_event: expected Utf8, Binary, or List(UInt8) input, got Int64`                            |

On a Solana dataset, unnest `transactions.log_messages` to get one log line per row, and keep the rows that are the
event. Here with the IDL from the example in place of `'…'`:

```sql
SELECT slot, tx_index, swap.input_amount AS input_amount, swap.output_amount AS output_amount
FROM (
  SELECT slot, tx_index, anchor_decode_event(log_message, '…', 'SwapEvent') AS swap
  FROM (
    SELECT slot, tx_index, UNNEST(log_messages) AS log_message FROM "acme/solana_mainnet".transactions
  ) AS logs
)
WHERE swap IS NOT NULL
ORDER BY slot, tx_index
LIMIT 5
```

```text [Output]
+-----------+----------+-------------------+---------------+
| slot      | tx_index | input_amount      | output_amount |
+-----------+----------+-------------------+---------------+
| 456192000 | 1264     | 44821530389306299 | 93695471      |
| 456192001 | 36       | 3552820384128     | 90368369      |
| 456192002 | 12       | 19883665          | 93481476523   |
| 456192002 | 19       | 88962624918       | 53040513288   |
| 456192004 | 834      | 11935305849       | 143332487749  |
+-----------+----------+-------------------+---------------+
```

They're the events of the swaps the `borsh_decode` example found.

* A `Program data:` line doesn't say which program emitted it: the `Program … invoke` lines before it do. Anchor
  derives an event's discriminator from its name, so events with the same name in different programs share it. Check
  the program when that matters. In these slots, 935 lines start with `SwapEvent`'s discriminator, but only 62 decode:
  the others come from programs whose `SwapEvent` has other fields.
* RPC endpoints truncate long logs, ending them with a `Log truncated` line: the events cut from them can't be decoded.
  The archive has fewer of them, and a fallback endpoint can fill them in (see
  [Truncated logs](/providers/solana#truncated-logs)).

Events emitted with `emit_cpi!` are in `instructions.data` instead. Here with the IDL from the Meteora example in place
of `'…'`:

```sql
SELECT slot, tx_index, swap.amount_in AS amount_in, swap.amount_out AS amount_out, swap.fee AS fee
FROM (
  SELECT slot, tx_index, anchor_decode_event(data, '…', 'Swap') AS swap
  FROM "acme/solana_mainnet".instructions
)
WHERE swap IS NOT NULL
ORDER BY slot, tx_index
LIMIT 5
```

```text [Output]
+-----------+----------+-------------+-------------+-----------+
| slot      | tx_index | amount_in   | amount_out  | fee       |
+-----------+----------+-------------+-------------+-----------+
| 456192000 | 1170     | 27636841597 | 3611805497  | 138184236 |
| 456192000 | 1274     | 1366328803  | 32227857279 | 13816209  |
| 456192000 | 1292     | 1010415593  | 27855988    | 5066288   |
| 456192000 | 1300     | 199739336   | 25297180158 | 501229    |
| 456192000 | 1315     | 210630850   | 26676600986 | 528560    |
+-----------+----------+-------------+-------------+-----------+
```

## Borsh descriptors

A descriptor is the layout of Borsh bytes, in a syntax Amp defines:

| Descriptor       | Bytes                                                                                            |
| ---------------- | ------------------------------------------------------------------------------------------------ |
| `bool`           | 1 byte: 0 or 1                                                                                   |
| `u8` to `u128`   | An unsigned integer of 1, 2, 4, 8, or 16 bytes, little-endian: `u8`, `u16`, `u32`, `u64`, `u128` |
| `i8` to `i128`   | A signed integer of the same sizes: `i8`, `i16`, `i32`, `i64`, `i128`                            |
| `f32`, `f64`     | A float of 4 or 8 bytes                                                                          |
| `string`         | A 4-byte length, then that many bytes of UTF-8                                                   |
| `pubkey`         | A 32-byte Solana address                                                                         |
| `[u8; N]`        | `N` bytes, like `[u8; 8]`                                                                        |
| `Vec<T>`         | A 4-byte count, then that many `T`s, like `Vec<u64>`                                             |
| `Option<T>`      | 1 byte, 0 for `None` or 1 for `Some`, then a `T` if it's 1                                       |
| `{ name: T, … }` | Each field's bytes, in order, like `{ maker: pubkey, amount: u64 }`                              |

Field names are letters, digits, and underscores, and spaces don't matter. Descriptors can't have fixed arrays of
types other than `u8`, enums, maps, tuples, `u256` or `i256`, an `Option` directly in an `Option`, or names of other
types.

### Types

What each Borsh type decodes to, written in a descriptor or in an Anchor IDL:

| Descriptor       | Anchor IDL                                                          | Arrow type                               |
| ---------------- | ------------------------------------------------------------------- | ---------------------------------------- |
| `bool`           | `"bool"`                                                            | `Boolean`                                |
| `u8` to `u64`    | `"u8"` to `"u64"`                                                   | `UInt8` to `UInt64`                      |
| `i8` to `i64`    | `"i8"` to `"i64"`                                                   | `Int8` to `Int64`                        |
| `u128`, `i128`   | `"u128"`, `"i128"`                                                  | `Utf8`, in base 10, to keep every digit  |
| `f32`, `f64`     | `"f32"`, `"f64"`                                                    | `Float32`, `Float64`                     |
| `string`         | `"string"`                                                          | `Utf8`                                   |
| `pubkey`         | `"pubkey"`                                                          | `Utf8`, in base58, like Solana addresses |
| `Vec<u8>`        | `"bytes"`                                                           | `Binary`                                 |
| `Vec<T>`         | `{ "vec": T }`                                                      | A `List` of `T`'s type                   |
| `Option<T>`      | `{ "option": T }`                                                   | `T`'s type, `NULL` for `None`            |
| `[u8; N]`        | `{ "array": ["u8", N] }`                                            | `FixedSizeBinary(N)`                     |
| Not available    | `{ "array": [T, N] }`                                               | A `FixedSizeList` of `N` of `T`'s type   |
| `{ name: T, … }` | A type of kind `struct`                                             | A `Struct`, with the fields in order     |
| Not available    | `{ "defined": { "name": … } }`, or a type of kind `type` (an alias) | The type it names                        |

## Anchor IDLs

`anchor_decode_event` reads the IDL format Anchor 0.30 and later generate, whose `metadata.spec` is `0.1.0`. It doesn't
read older IDLs, or Codama ones.

* It only reads `metadata.spec`, `events`, and `types`. Pass the program's whole IDL, up to 1 MiB, or only those
  parts, as the examples do.
* `events` must have exactly one entry named after the event, with its discriminator, and `types` one type with the
  same name. No other event's discriminator can be the same, start with this one, or be the start of it.
* The types the event uses, directly or through other types, must be in the [table above](/querying/functions/solana#types). Enums, tuple
  structs, generics, `coption`, `u256`, `i256`, types with a `serialization` other than Borsh, and types that contain
  themselves aren't supported. Types the event doesn't use can be anything.

## Limits

These limits keep a descriptor, an IDL, or bad data from using too much memory. A row over a per-row limit returns
`NULL`. A call over a per-call limit, which covers a whole batch of rows, fails the query.

| What                                                          | Limit     |
| ------------------------------------------------------------- | --------- |
| A descriptor                                                  | 16 KiB    |
| An IDL                                                        | 1 MiB     |
| A name: of the event, a type, or a field                      | 256 bytes |
| Nesting of types                                              | 32 levels |
| Fields in a struct                                            | 256       |
| A row's data, and a `string`, bytes, or `[u8; N]` value in it | 1 MiB     |
| Elements in a `Vec` or array, other than bytes                | 65,536    |
| Values decoded in a row                                       | 65,536    |
| Values decoded in a call                                      | 1,048,576 |
| Data decoded in a call                                        | 64 MiB    |