Best Practices

Overview

Follow these best practices to build secure, production-ready applications with the SQL API.

Prioritize indexed fields for filtering

To maximize query speed, always filter your data using indexed fields. Filtering on non-indexed fields forces full table scans, which are resource-intensive. Key indexed fields for filtering (use these in your WHERE clauses):

Base: event_signature (prefer over event_name), address, block_timestamp
Solana transfers: mint, block_time (and source_owner / destination_owner for wallet filters)
Solana instructions: executing_account, instruction_name, block_time

-- Good: Uses indexed fields for fast lookup on Base
SELECT address, block_timestamp, transaction_hash
FROM base.events
WHERE block_timestamp='2025-10-04T23:04:09.000Z'
AND event_signature = 'Transfer(address,address,uint256)'
AND address = '0x456...def';

-- Good: Uses indexed fields for fast lookup on Solana
SELECT mint, source_owner, destination_owner, amount, block_time
FROM solana.transfers
WHERE mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
  AND block_time >= now() - INTERVAL 5 MINUTE;

Select only necessary fields

Avoid using SELECT *. Selecting unnecessary fields consumes more RAM and accesses more disk space, which increases the likelihood of your query reaching resource limits.

-- Good: Only selects the necessary fields
SELECT transaction_hash, log_index
FROM base.events
WHERE address = '0x456...def';

Leverage caching with `maxAgeMs`

Developers should take advantage of SQL API’s cache controls when executing high frequency queries. Ensure you’re using the maximum maxAgeMs that your queries can reasonably tolerate. This allows the API to return a cached response, which limits direct database calls.

curl --request POST \
  --url https://api.cdp.coinbase.com/platform/v2/data/query/run \
  --header 'Authorization: Bearer <token>' \
  --header 'Content-Type: application/json' \
  --data '{
  "sql": "SELECT block_number, transaction_hash FROM base.events WHERE address = '0x456...def'",
  "cache": {
    "maxAgeMs": 5000
  }
}'

Separate dev and prod project IDs

Always use a different project ID for your development (dev) and production (prod) integrations. Clear separation ensures that testing and debugging activities do not impact the performance or rate limits of your live production environment.

Account for Solana token accounts vs wallets

On Solana, SPL transfers move balances between token accounts, not directly between owner wallets. When you need holder-level activity:

Prefer source_owner / destination_owner on solana.transfers
Or resolve owners via account_owners on solana.instructions

Filter on mint when tracking a specific token—mint is the leading index for solana.transfers. See Query Solana tokens.

GET STARTED

WALLETS

PAYMENTS

STABLECOINS

TRADING

ONCHAIN TOOLS

CUSTOMERS

WEBHOOKS

CONSUMER APIS

BUSINESS APIS

INSTITUTIONAL APIS

Overview

Prioritize indexed fields for filtering

Select only necessary fields

Leverage caching with `maxAgeMs`

Separate dev and prod project IDs

Account for Solana token accounts vs wallets

​Overview

​Prioritize indexed fields for filtering

​Select only necessary fields

​Leverage caching with maxAgeMs

​Separate dev and prod project IDs

​Account for Solana token accounts vs wallets

Overview

Prioritize indexed fields for filtering

Select only necessary fields

Leverage caching with `maxAgeMs`

Separate dev and prod project IDs

Account for Solana token accounts vs wallets