View this page as Markdown

Query contract state with SQL

Call an EVM contract function from SQL, pin the read to one block, and use a column as the function argument.

Goal

Query USDC balanceOf(address) from Ethereum mainnet. Keep exact UINT256 values separate from formatted display values.

The notebook starts with a live read, repeats it at a pinned block, then uses an account column as the balanceOf argument.

1. Read balanceOf as UINT256 #

Call USDC balanceOf(address) for one account. The ABI defines the argument and output types, so read_contract returns a UINT256. 

WITH balance AS (
  SELECT read_contract(
    $client,
    '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
    '[{
      "type": "function",
      "name": "balanceOf",
      "stateMutability": "view",
      "inputs": [
        {
          "name": "account",
          "type": "address"
        }
      ],
      "outputs": [
        {
          "name": "balance",
          "type": "uint256"
        }
      ]
    }]'::JSON,
    'balanceOf',
    '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
  ) AS raw_balance
)
SELECT format_units(raw_balance, 6) AS vitalik_usdc
FROM balance;
Loading cached snapshot · 1 rows

format_units(raw_balance, 6) returns display text. The unpinned client reads live state, so the value can change.

2. Pin a block and keep the exact value #

Use pin to resolve Ethereum block 25,601,821 and return a client fixed to that block. 

WITH pinned AS (
  SELECT pin($client, 25601821::UBIGINT) AS client
),
balance AS (
  SELECT read_contract(
    client,
    '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
    '[{
      "type": "function",
      "name": "balanceOf",
      "stateMutability": "view",
      "inputs": [
        {
          "name": "account",
          "type": "address"
        }
      ],
      "outputs": [
        {
          "name": "balance",
          "type": "uint256"
        }
      ]
    }]'::JSON,
    'balanceOf',
    '0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
  ) AS raw_balance
  FROM pinned
)
SELECT
  raw_balance,
  format_units(raw_balance, 6) AS usdc_balance
FROM balance;
Loading cached snapshot · 1 rows

raw_balance remains exact, while usdc_balance is display text. The RPC must retain state for the pinned block. For one historical read, read_contract_at accepts a block number directly.

3. Call balanceOf for each input row #

Pass the account column as the function argument. read_contract evaluates balanceOf once per row. 

WITH holders(account) AS (
  VALUES
    ('0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS),
    ('0x000000000000000000000000000000000000dEaD'::ADDRESS)
),
balances AS (
  SELECT
    account,
    read_contract(
      $client,
      '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
      '[{
        "type": "function",
        "name": "balanceOf",
        "stateMutability": "view",
        "inputs": [
          {
            "name": "account",
            "type": "address"
          }
        ],
        "outputs": [
          {
            "name": "balance",
            "type": "uint256"
          }
        ]
      }]'::JSON,
      'balanceOf',
      account
    ) AS raw_usdc_balance
  FROM holders
)
SELECT
  account,
  raw_usdc_balance,
  format_units(raw_usdc_balance, 6) AS usdc_balance
FROM balances;
Loading cached snapshot · 2 rows

Each result row contains the account as ADDRESS, the raw balance as UINT256, and the formatted balance as VARCHAR. The query makes one RPC-backed call per row. Use read_contract_multicall to batch calls and inspect failures per input.

Functions used in this guide