Cover photo

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.


Introduction

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:

  1. Run your own indexing infra (expensive and brittle).

  2. 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)

Play Video

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:

Play Video

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:

  1. When a transaction first appears → action = 1 (credit).

  2. 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 Architecture

Data Flow:

Developer → SQL API → Blockchain Tables → JSON Result → App Logic
  • SQL 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 Setup

mkdir cdp-sql-tutorial && cd cdp-sql-tutorial
npm init -y
npm install axios dotenv

Update package.json to mark the project as ESM:

"type": "module"

Create a .env file:

CDP_CLIENT_API_KEY=your-client-api-key

Make sure .env is in your .gitignore.

Step 2: First Query — Latest Block

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.

Step 3: Whale Watcher — Top USDC Transfers

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.

Step 4: Working with Limits

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.


Conclusion

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 🚀