Query contract state with SQL

Call an EVM contract function from SQL, pin its state, and apply the same call to relation rows.

Goal

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

The notebook reads live state, pins one read to a block, and applies the same call to relation rows.

1. Turn balanceOf into a typed column #

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

1
WITH balance AS (
2
SELECT read_contract(
3
$client,
4
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
5
'[{
6
"type": "function",
7
"name": "balanceOf",
8
"stateMutability": "view",
9
"inputs": [
10
{
11
"name": "account",
12
"type": "address"
13
}
14
],
15
"outputs": [
16
{
17
"name": "balance",
18
"type": "uint256"
19
}
20
]
21
}]'::JSON,
22
'balanceOf',
23
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
24
) AS raw_balance
25
)
26
SELECT format_units(raw_balance, 6) AS vitalik_usdc
27
FROM balance;

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 #

Pin the client when later reads must use the same block. pin returns a client for Ethereum block 25,601,821.

1
WITH pinned AS (
2
SELECT pin($client, 25601821::UBIGINT) AS client
3
),
4
balance AS (
5
SELECT read_contract(
6
client,
7
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
8
'[{
9
"type": "function",
10
"name": "balanceOf",
11
"stateMutability": "view",
12
"inputs": [
13
{
14
"name": "account",
15
"type": "address"
16
}
17
],
18
"outputs": [
19
{
20
"name": "balance",
21
"type": "uint256"
22
}
23
]
24
}]'::JSON,
25
'balanceOf',
26
'0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS
27
) AS raw_balance
28
FROM pinned
29
)
30
SELECT
31
raw_balance,
32
format_units(raw_balance, 6) AS usdc_balance
33
FROM balance;

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. Scale the same call across rows #

Pass a column as the function argument to evaluate the same balanceOf call for each row.

1
WITH holders(account) AS (
2
VALUES
3
('0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045'::ADDRESS),
4
('0x000000000000000000000000000000000000dEaD'::ADDRESS)
5
),
6
balances AS (
7
SELECT
8
account,
9
read_contract(
10
$client,
11
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
12
'[{
13
"type": "function",
14
"name": "balanceOf",
15
"stateMutability": "view",
16
"inputs": [
17
{
18
"name": "account",
19
"type": "address"
20
}
21
],
22
"outputs": [
23
{
24
"name": "balance",
25
"type": "uint256"
26
}
27
]
28
}]'::JSON,
29
'balanceOf',
30
account
31
) AS raw_usdc_balance
32
FROM holders
33
)
34
SELECT
35
account,
36
raw_usdc_balance,
37
format_units(raw_usdc_balance, 6) AS usdc_balance
38
FROM balances;

Each row keeps its ADDRESS, exact UINT256, and display VARCHAR. This query makes one RPC-backed call per row. Use read_contract_multicall for larger or failure-tolerant batches.

Functions used in this guide