# Build a Whale-Watching On-Chain Analytics Tool with the Coinbase SQL API (Node.js Tutorial)

> From concept to code — why the SQL API matters and how to use it to track the biggest transfers on Base.

**Published by:** [HeimLabs](https://paragraph.com/@heimlabs/)
**Published on:** 2025-09-17
**Categories:** web3, coinbase, cdp, base, buildonbase, sql, api
**URL:** https://paragraph.com/@heimlabs/build-a-whale-watching-on-chain-analytics-tool-with-the-coinbase-sql-api-nodejs-tutorial

## Content

IntroductionWhat’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: You’ll Build⚙ 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.Why SQL API Matters (Explainer)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.System ArchitectureData 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.Step 1: Project Setupmkdir 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.Step 2: First Query — Latest BlockCreate 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.Step 3: Whale Watcher — Top USDC TransfersAppend 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. 🐳Step 4: Working with LimitsThe SQL API enforces guardrails:⏱ 30s timeout per query📊 10,000 rows max🔗 Up to 5 JOINsPro tip: paginate by time. Use WHERE block_timestamp BETWEEN ... to fetch daily/weekly chunks.ConclusionYou’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.Resources & LinksCoinbase Developer Docs:Coinbase Developer Docs - Coinbase Developer DocumentationExplore our API & SDK references, demos, and guides for building onchain apps.https://docs.cdp.coinbase.comHeimLabs:HeimLabs | Trusted Blockchain Solutions ProviderRevolutionize your business with HeimLabs' blockchain development solutions. Our expert team offers end-to-end services for smart contracts, DApps & more.https://www.heimlabs.comCDP Discord:Discord - Group Chat That's All Fun & GamesDiscord is great for playing games and chilling with friends, or even building a worldwide community. Customize your own space to talk, play, and hang out.https://discord.comClap 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 🚀

## Publication Information

- [HeimLabs](https://paragraph.com/@heimlabs/): Publication homepage
- [All Posts](https://paragraph.com/@heimlabs/): More posts from this publication
- [RSS Feed](https://api.paragraph.com/blogs/rss/@heimlabs): Subscribe to updates
- [Twitter](https://twitter.com/heimlabs): Follow on Twitter
- [Farcaster](https://farcaster.xyz/heimlabs): Follow on Farcaster