bag.watch home
Loading
← Back to notes

Reading the Chain: How to Talk to Smart Contracts in SQL

Learn how to query live Ethereum contracts directly in SQL - fetch token metadata, oracle prices, and Uniswap pool data in your browser with bag.watch. No servers, no scripts, just you and the chain.

TL;DR - Query Live Oracle Data

This page embeds a real bag.watch notebook, so every code block is editable and executable. Click into the cell below, tweak the feed, and hit Run to watch mainnet state refresh right in the note.

1
-- Chainlink ETH/USD price feed
2
SELECT
3
format_units(output0::INT256, 8) AS eth_usd_price
4
FROM read_contract(
5
http_transport('https://ethereum-rpc.publicnode.com'), -- any RPC (even localhost)
6
'0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
7
'[
8
{ "type": "function", "name": "latestAnswer", "stateMutability": "view", "inputs": [], "outputs": [{ "type": "int256" }] }
9
]'::JSON,
10
'latestAnswer',
11
[]
12
);
Notebook ready in readonly mode.

Result

| eth_usd_price |
|---------------|
| 4068.87       |

You’re hitting Chainlink’s ETH/USD oracle straight from your browser—no backend, no API keys. Edit the cell, change transports, or join it with anything else on the page to keep the wow loop going. Yes, you can run and modify every query here.

Reading the Chain: How to Talk to Smart Contracts in SQL

Query Ethereum contracts directly in SQL - fetch live data from Chainlink, Uniswap, or any EVM contract right from your browser.

Every time you hit a blockchain RPC, you get hex. Not insight.

eth_call returns a wall of bytes like:

0x0000000000000000000000000000000000000000000000000000000000000020...

You just wanted to know the token name.

If you’ve used Etherscan, you’ve seen the magic it performs automatically:
it takes that raw output, looks up the ABI, and decodes it into readable fields and values - all behind the scenes.

But what if you could do that yourself - locally, in SQL - and query any contract, live, from your browser?

That’s the idea behind bag.watch - local-first notebooks for onchain ops. Everything runs client-side - no servers, no installs, no API keys, nothing sitting between you and the chain.

bag.watch treats the EVM as a database: contracts feel like tables, functions behave like parameterized queries, and transports are just connections you can swap at will.

With a few lines of SQL, you can:

  • Connect to any EVM RPC (public, private, or local Anvil)
  • Read contract storage and function outputs directly
  • Mix analytics and calldata previews in a single notebook cell

Let’s read the chain.

Connecting to the Chain

In bag.watch, a blockchain connection is just a transport handle:
a JSON object that wraps your RPC endpoint, headers, and chain id.

Use http_transport to create one:

In a notebook cell, run this query exactly as shown - it’s live SQL against the EVM.

1
-- Connect to a local Anvil node
2
SELECT http_transport('http://127.0.0.1:8545', 31337) AS transport;
Notebook ready in readonly mode.

You can also connect to any public endpoint:

1
-- Or connect to a public Ethereum RPC
2
SELECT http_transport('https://ethereum-rpc.publicnode.com') AS transport;
Notebook ready in readonly mode.

This returns a TRANSPORT type you can reuse in later queries, by using query parameters starting with $ like $transport.

Reading a Token’s Metadata

Let’s do something useful: ask a contract who it is.

Take USDC on Ethereum (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48).
With read_contract, you can call its name() and decimals() functions in SQL.

1
-- Query USDC metadata directly from mainnet
2
WITH token_name AS (
3
SELECT output0 AS name
4
FROM read_contract(
5
$transport,
6
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
7
'[
8
{ "type": "function", "name": "name", "stateMutability": "view", "inputs": [], "outputs": [{ "type": "string" }] }
9
]'::JSON,
10
'name',
11
[]
12
)
13
),
14
token_decimals AS (
15
SELECT output0::UINT8 AS decimals
16
FROM read_contract(
17
$transport,
18
'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS,
19
'[
20
{ "type": "function", "name": "decimals", "stateMutability": "view", "inputs": [], "outputs": [{ "type": "uint8" }] }
21
]'::JSON,
22
'decimals',
23
[]
24
)
25
)
26
SELECT name, decimals
27
FROM token_name, token_decimals;
Notebook ready in readonly mode.

Each read_contract call yields a result table. When the ABI output lacks explicit field names (like name() returning a single string), the columns default to output0, output1, and so on - just alias them before composing downstream logic.

Output

| name       | decimals |
|-------------|-----------|
| USD Coin    | 6         |

This is the exact decode path your wallet or block explorer runs for ERC-20 metadata - only now you can blend it with whatever analytics you want.

You just performed a real onchain eth_call - encoded arguments, decoded the output, and surfaced it as clean SQL columns.

No ethers.js, no viem, no scripts, no hex gymnastics.

bag.watch automatically:

  • Encodes your call based on the ABI JSON
  • Sends it via the provided RPC transport
  • Decodes the result into native DuckDB types or custom bag.watch types like uint256

It’s the same decoding logic Etherscan does automatically - but now you control it, locally.

Reading Live Oracle Data

Let’s fetch something dynamic: the ETH/USD price feed from Chainlink.

The feed contract lives at 0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419 and exposes latestAnswer().

Call that function directly and let format_units take care of the 8-decimal scaling:

1
-- Chainlink ETH/USD price feed
2
SELECT
3
format_units(output0, 8) AS eth_usd_price
4
FROM read_contract(
5
$transport,
6
'0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
7
'[
8
{ "type": "function", "name": "latestAnswer", "stateMutability": "view", "inputs": [], "outputs": [{ "type": "int256" }] }
9
]'::JSON,
10
'latestAnswer',
11
[]
12
);
Notebook ready in readonly mode.

Result

| eth_usd_price |
|---------------|
| 4068.87       |

That’s the same live value your DEX or DeFi dashboard would read - now in a SQL cell you can join, aggregate, and chart.

You’re looking at the same price feed that governs most of DeFi’s risk logic - the one Aave, Compound, and EigenLayer monitor before letting collateral move.

Chainlink answers are signed integers scaled by 1e8, so format_units does the decimal math for you. When you’re dealing with raw ETH balances or allowances, reach for format_ether(output0::UINT256) to get clean 18-decimal numbers without manual division.

Comparing Oracle and Pool Prices

Want to cross-check that oracle price against the Uniswap v3 ETH/USDC pool? Another read_contract call pulls the pool’s slot0() struct directly from mainnet, so you’re staring at the exact state Uniswap uses to settle trades—no indexers, subgraphs, or archives in between.

1
-- Uniswap v3 ETH/USDC pool snapshot
2
WITH slot0 AS (
3
SELECT sqrtPriceX96::UINT256 AS sqrt_price_x96
4
FROM read_contract(
5
$transport,
6
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8'::ADDRESS,
7
'[
8
{ "type": "function", "name": "slot0", "stateMutability": "view", "inputs": [], "outputs": [
9
{ "name": "sqrtPriceX96", "type": "uint256" },
10
{ "name": "tick", "type": "int256" },
11
{ "name": "observationIndex", "type": "uint256" },
12
{ "name": "observationCardinality", "type": "uint256" },
13
{ "name": "observationCardinalityNext", "type": "uint256" },
14
{ "name": "feeProtocol", "type": "uint256" },
15
{ "name": "unlocked", "type": "bool" }
16
] }
17
]'::JSON,
18
'slot0',
19
[]
20
)
21
)
22
SELECT
23
(pow((pow(2::DOUBLE, 96) / sqrt_price_x96::DOUBLE), 2) * 1e12)::DECIMAL(38,8) AS uni_eth_usd_price
24
FROM slot0;
Notebook ready in readonly mode.

Result

| uni_eth_usd_price |
|-------------------|
| 4064.44           |

Duplicate the cell, swap the pool address, or change transports and you’ll see the table update instantly. That’s how you verify pool health or slippage in real time, not a day later through someone else’s index.

Computing the Price

The pool’s implied price closely tracks the oracle - both fetched directly over RPC, right inside your notebook.

slot0() returns a tuple of narrow ints (uint160, int24, etc.), but read_contract expects each decoded word to be a full 32-byte slot.

USDC is token0 (6 decimals) and WETH is token1 (18 decimals) in this pool, so the raw sqrtPriceX96 encodes WETH per USDC. To report USDC per ETH, invert that ratio and multiply by 10^(18-6) = 1e12, which is what the pow(2::DOUBLE, 96) / sqrt_price_x96 pattern in the query does.

You can combine them in a single SQL view:

1
WITH chainlink AS (
2
SELECT format_units(output0::INT256, 8) AS chainlink_eth_usd
3
FROM read_contract(
4
$transport,
5
'0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
6
'[{"type":"function","name":"latestAnswer","stateMutability":"view","inputs":[],"outputs":[{"type":"int256"}]}]'::JSON,
7
'latestAnswer',
8
[]
9
)
10
),
11
uniswap AS (
12
SELECT (pow((pow(2::DOUBLE, 96) / (sqrtPriceX96::UINT256)::DOUBLE), 2) * 1e12)::DECIMAL(38,8) AS uniswap_eth_usd
13
FROM read_contract(
14
$transport,
15
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8'::ADDRESS,
16
'[{"type":"function","name":"slot0","stateMutability":"view","inputs":[],"outputs":[{"name":"sqrtPriceX96","type":"uint256"}]}]'::JSON,
17
'slot0',
18
[]
19
)
20
)
21
SELECT
22
chainlink.chainlink_eth_usd,
23
uniswap.uniswap_eth_usd,
24
(
25
(
26
(
27
(uniswap.uniswap_eth_usd::DOUBLE - chainlink.chainlink_eth_usd::DOUBLE)
28
) /
29
chainlink.chainlink_eth_usd::DOUBLE
30
) * 100::DOUBLE
31
)::DECIMAL(9,4) AS pct_diff
32
FROM chainlink, uniswap;
Notebook ready in readonly mode.

Output

| chainlink_eth_usd | uniswap_eth_usd | pct_diff |
|-------------------|-----------------|----------|
| 4068.87           | 4064.44         | -0.01    |

You just joined an oracle feed and a DEX pool like two SQL tables - and the data never left the RPCs you pointed at.

No indexers, no ETL, no delay.

Composing Your Own Queries

Now that you can read contracts on demand, treat them like tables: join oracle feeds, compute deltas, or simulate vault values - all reproducible and client-side.

1
-- How many ETH for $100?
2
WITH prices AS (
3
SELECT format_units(output0::INT256, 8) AS eth_usd
4
FROM read_contract(
5
$transport,
6
'0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS,
7
'[{"type":"function","name":"latestAnswer","stateMutability":"view","inputs":[],"outputs":[{"type":"int256"}]}]'::JSON,
8
'latestAnswer',
9
[]
10
)
11
)
12
SELECT (100::DOUBLE / eth_usd::DOUBLE) AS eth_for_100_usd
13
FROM prices;
Notebook ready in readonly mode.

Output

| eth_for_100_usd |
|-----------------|
| 0.0246          |

Now the oracle feed behaves like any other numeric column inside bag.watch - DuckDB runs underneath to give you OLAP-class SQL right in the browser, so it’s instantly ready for dashboards, alerts, or quick mental math.

Everything here ran locally - no indexer, no backend.

Wrap-Up

Here’s what we just walked through:

  • Connect to any EVM RPC with http_transport
  • Query contracts with read_contract
  • Decode ABI results into clean SQL tables
  • Combine oracle and DEX data for real-time analysis

All of it stays inside the browser - WebAssembly DuckDB on the front-end, live RPCs on the back-end - so “readable chain state” becomes an everyday reality. Contracts feel like tables because they are, once you can query them this way.

What used to require scripts, subgraphs, or bespoke infra now fits inside a single notebook cell. bag.watch makes onchain data composable, reproducible, and frankly fun.

Try a query yourself → bag.watch/app

Next:
👉 Mastering CREATE2 - Predict, Mine & Optimize Ethereum Contract Addresses