<100 subscribers


What’s the biggest bottleneck when building data-rich on-chain apps? It’s not your idea — it’s the engineering headache of getting reliable, queryable blockchain data.
For years, developers had two painful choices:
Run your own indexing infra (expensive and brittle).
Rely on rigid REST endpoints that can’t answer your custom questions.
The Coinbase Developer Platform SQL API changes that. It’s a zero-infrastructure, production-grade indexing solution that lets you query live and historical blockchain data with SQL — the universal language of developers.
Today, we’ll explain how it works (and why it’s reorg-proof), then walk through a hands-on Node.js tutorial where you’ll build a “Whale Watcher” that finds the largest USDC transfers on Base.
Watch how it works (and why it’s reorg-proof)
Walk-through a hands-on Node.js tutorial where you’ll build a “Whale Watcher” that finds the largest USDC transfers on Base. Watch here:
⚙ Bootstrap a Node.js project and connect to the CDP SQL API.
⛓ Query blockchain tables (blocks, events) in SQL.
Build a “Whale Watcher” that finds the biggest USDC transfers.
📊 Handle large datasets and API limits like a pro.
Blockchains are living ledgers — sometimes the network disagrees on the “latest” state. These temporary forks are called reorgs.
For developers, reorgs are brutal: you might show a user a confirmed payment, only for it to vanish when the chain reorganizes.
The SQL API solves this elegantly with an action column:
When a transaction first appears → action = 1 (credit).
If it gets dropped in a reorg → a new record with action = -1 (debit).
To get the truth, just SUM(action). If the result is > 0, the transaction is valid. No manual reconciliation needed — the API handles it.
That’s why the SQL API isn’t just another endpoint. It’s your on-chain data warehouse.
Data Flow:
Developer → SQL API → Blockchain Tables → JSON Result → App LogicSQL API: Secure endpoint that accepts custom SQL queries.
Tables: base.blocks, base.events, and more.
Output: JSON with schema, rows, and metadata.
Your App: Processes and visualizes the results.
Think of it as BigQuery or Snowflake — but pointed at the blockchain.
mkdir cdp-sql-tutorial && cd cdp-sql-tutorial
npm init -y
npm install axios dotenvUpdate package.json to mark the project as ESM:
"type": "module"Create a .env file:
CDP_CLIENT_API_KEY=your-client-api-keyMake sure .env is in your .gitignore.
Create queryApi.js:
import axios from 'axios';
import dotenv from 'dotenv';
dotenv.config();
const API_URL = 'https://api.cdp.coinbase.com/platform/v2/data/query/run';
const API_KEY = process.env.CDP_CLIENT_API_KEY;
async function runSqlQuery(sql) {
const headers = { Authorization: `Bearer ${API_KEY}`, 'Content-Type': 'application/json' };
const response = await axios.post(API_URL, { sql }, { headers });
return response.data;
}
async function main() {
const query = `
SELECT block_number, block_hash, timestamp, miner, gas_used, transaction_count
FROM base.blocks
ORDER BY block_number DESC
LIMIT 1
`;
const result = await runSqlQuery(query);
console.log(JSON.stringify(result, null, 2));
}
main();Run it:
node queryApi.js
✅ You just pulled the latest block on Base.
Append this inside your main() function.
const usdcContract = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913';
const whaleQuery = `
SELECT parameters['from']::String AS sender,
parameters['to']::String AS recipient,
parameters['value']::UInt256 AS amount
FROM base.events
WHERE event_signature = 'Transfer(address,address,uint256)'
AND address = '${usdcContract}'
LIMIT 10000
`;
const whaleResult = await runSqlQuery(whaleQuery);
if (whaleResult.result.length > 0) {
const sorted = whaleResult.result.sort((a, b) => BigInt(b.amount) - BigInt(a.amount));
const top5 = sorted.slice(0, 5).map(t => ({
sender: t.sender,
recipient: t.recipient,
usdc: Number(BigInt(t.amount) / BigInt(1e6)).toLocaleString()
}));
console.log('--- Top 5 USDC Transfers ---');
console.log(top5);
}Run again:
node queryApi.js
You’ll see the biggest whales moving USDC on Base.
The SQL API enforces guardrails:
⏱ 30s timeout per query
📊 10,000 rows max
🔗 Up to 5 JOINs
Pro tip: paginate by time. Use WHERE block_timestamp BETWEEN ... to fetch daily/weekly chunks.
You’ve just:
Learned why the SQL API is reorg-proof.
Queried live block data with a few lines of Node.js.
Built a whale-watching analytics tool in under 50 lines of code.
This same approach powers dashboards, bots, and monitoring tools. No infra, no indexers — just SQL.
Coinbase Developer Docs:
HeimLabs:
CDP Discord:
Clap if this saved you hours, and share what you’re building with the CDP stack!
Follow HeimLabs for unapologetically practical Web3 dev content.
Twitter, LinkedIn.
Happy Building 🚀
What’s the biggest bottleneck when building data-rich on-chain apps? It’s not your idea — it’s the engineering headache of getting reliable, queryable blockchain data.
For years, developers had two painful choices:
Run your own indexing infra (expensive and brittle).
Rely on rigid REST endpoints that can’t answer your custom questions.
The Coinbase Developer Platform SQL API changes that. It’s a zero-infrastructure, production-grade indexing solution that lets you query live and historical blockchain data with SQL — the universal language of developers.
Today, we’ll explain how it works (and why it’s reorg-proof), then walk through a hands-on Node.js tutorial where you’ll build a “Whale Watcher” that finds the largest USDC transfers on Base.
Watch how it works (and why it’s reorg-proof)
Walk-through a hands-on Node.js tutorial where you’ll build a “Whale Watcher” that finds the largest USDC transfers on Base. Watch here:
⚙ Bootstrap a Node.js project and connect to the CDP SQL API.
⛓ Query blockchain tables (blocks, events) in SQL.
Build a “Whale Watcher” that finds the biggest USDC transfers.
📊 Handle large datasets and API limits like a pro.
Blockchains are living ledgers — sometimes the network disagrees on the “latest” state. These temporary forks are called reorgs.
For developers, reorgs are brutal: you might show a user a confirmed payment, only for it to vanish when the chain reorganizes.
The SQL API solves this elegantly with an action column:
When a transaction first appears → action = 1 (credit).
If it gets dropped in a reorg → a new record with action = -1 (debit).
To get the truth, just SUM(action). If the result is > 0, the transaction is valid. No manual reconciliation needed — the API handles it.
That’s why the SQL API isn’t just another endpoint. It’s your on-chain data warehouse.
Data Flow:
Developer → SQL API → Blockchain Tables → JSON Result → App LogicSQL API: Secure endpoint that accepts custom SQL queries.
Tables: base.blocks, base.events, and more.
Output: JSON with schema, rows, and metadata.
Your App: Processes and visualizes the results.
Think of it as BigQuery or Snowflake — but pointed at the blockchain.
mkdir cdp-sql-tutorial && cd cdp-sql-tutorial
npm init -y
npm install axios dotenvUpdate package.json to mark the project as ESM:
"type": "module"Create a .env file:
CDP_CLIENT_API_KEY=your-client-api-keyMake sure .env is in your .gitignore.
Create queryApi.js:
import axios from 'axios';
import dotenv from 'dotenv';
dotenv.config();
const API_URL = 'https://api.cdp.coinbase.com/platform/v2/data/query/run';
const API_KEY = process.env.CDP_CLIENT_API_KEY;
async function runSqlQuery(sql) {
const headers = { Authorization: `Bearer ${API_KEY}`, 'Content-Type': 'application/json' };
const response = await axios.post(API_URL, { sql }, { headers });
return response.data;
}
async function main() {
const query = `
SELECT block_number, block_hash, timestamp, miner, gas_used, transaction_count
FROM base.blocks
ORDER BY block_number DESC
LIMIT 1
`;
const result = await runSqlQuery(query);
console.log(JSON.stringify(result, null, 2));
}
main();Run it:
node queryApi.js
✅ You just pulled the latest block on Base.
Append this inside your main() function.
const usdcContract = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913';
const whaleQuery = `
SELECT parameters['from']::String AS sender,
parameters['to']::String AS recipient,
parameters['value']::UInt256 AS amount
FROM base.events
WHERE event_signature = 'Transfer(address,address,uint256)'
AND address = '${usdcContract}'
LIMIT 10000
`;
const whaleResult = await runSqlQuery(whaleQuery);
if (whaleResult.result.length > 0) {
const sorted = whaleResult.result.sort((a, b) => BigInt(b.amount) - BigInt(a.amount));
const top5 = sorted.slice(0, 5).map(t => ({
sender: t.sender,
recipient: t.recipient,
usdc: Number(BigInt(t.amount) / BigInt(1e6)).toLocaleString()
}));
console.log('--- Top 5 USDC Transfers ---');
console.log(top5);
}Run again:
node queryApi.js
You’ll see the biggest whales moving USDC on Base.
The SQL API enforces guardrails:
⏱ 30s timeout per query
📊 10,000 rows max
🔗 Up to 5 JOINs
Pro tip: paginate by time. Use WHERE block_timestamp BETWEEN ... to fetch daily/weekly chunks.
You’ve just:
Learned why the SQL API is reorg-proof.
Queried live block data with a few lines of Node.js.
Built a whale-watching analytics tool in under 50 lines of code.
This same approach powers dashboards, bots, and monitoring tools. No infra, no indexers — just SQL.
Coinbase Developer Docs:
HeimLabs:
CDP Discord:
Clap if this saved you hours, and share what you’re building with the CDP stack!
Follow HeimLabs for unapologetically practical Web3 dev content.
Twitter, LinkedIn.
Happy Building 🚀
Share Dialog
Share Dialog
HeimLabs
HeimLabs
1 comment
🐳 Built a Whale Watcher in Node.js with @coinbasedev’s SQL API. Track the biggest USDC transfers on Base, no infra, just SQL. https://paragraph.com/@heimlabs/build-a-whale-watching-on-chain-analytics-tool-with-the-coinbase-sql-api-nodejs-tutorial?referrer=0x17b840804b56F8A1178727bfFb212EFe700dEaf5