Query Onchain

IMPORTANT: Always single-quote the -d JSON string to prevent bash variable expansion.

Input Validation

Before constructing the command, validate inputs to prevent shell injection:

• SQL query: Always embed the query inside a single-quoted JSON string (-d '{"sql": "..."}'). Never use double quotes for the outer -d wrapper, as this enables shell expansion of $ and backticks within the query.
• Addresses: Must be valid 0x hex addresses (^0x[0-9a-fA-F]{40}$). Reject any value containing shell metacharacters.

Do not pass unvalidated user input into the command.

CRITICAL: Indexed Fields

Queries against base.events MUST filter on indexed fields to avoid full table scans. The indexed fields are:

Always include at least one indexed field in your WHERE clause. Combining all three gives the best performance.

CoinbaseQL Syntax

CoinbaseQL is a SQL dialect based on ClickHouse. Supported features:

• Clauses: SELECT (DISTINCT), FROM, WHERE, GROUP BY, ORDER BY (ASC/DESC), LIMIT, WITH (CTEs), UNION (ALL/DISTINCT)
• Joins: INNER, LEFT, RIGHT, FULL with ON
• Operators: =, !=, <>, <, >, <=, >=, +, -, *, /, %, AND, OR, NOT, BETWEEN, IN, IS NULL, LIKE
• Expressions: CASE/WHEN/THEN/ELSE, CAST (both CAST() and :: syntax), subqueries, array/map indexing with [], dot notation
• Literals: Array [...], Map {...}, Tuple (...)
• Functions: Standard SQL functions, lambda functions with -> syntax

Available Tables

base.events

Decoded event logs from smart contract interactions. This is the primary table for most queries.

base.transactions

Complete transaction data.

base.blocks

Block-level metadata.

Example Queries

Get recent USDC Transfer events with decoded parameters

Get transactions from a specific address

Count events by type for a contract in the last hour

Get latest block info

Common Contract Addresses (Base)

Best Practices

1. Always filter on indexed fields (event_signature, address, block_timestamp) in base.events queries.
2. Never use SELECT * - specify only the columns you need.
3. Always include a LIMIT clause to bound result size.
4. Use event_signature instead of event_name for filtering - it is indexed and much faster.
5. Use time-bounded queries with block_timestamp to narrow the scan range.
6. Always wrap address values in lower() - the database stores lowercase addresses but users may provide checksummed (mixed-case) addresses. Use address = lower('0xAbC...') not address = '0xAbC...'.
7. Common event signatures: Transfer(address,address,uint256), Approval(address,address,uint256), Swap(address,uint256,uint256,uint256,uint256,address).

Prerequisites

• Must be authenticated (npx awal@2.12.1 status to check; see references/auth.md)
• Wallet must have sufficient USDC balance (npx awal@2.12.1 balance to check; see references/fund.md)
• Each query costs $0.10 (100000 USDC atomic units)

Error Handling

• “Not authenticated” - See references/auth.md
• “Insufficient balance” - See references/fund.md
• Query timeout or error - Ensure you are filtering on indexed fields and using a LIMIT