> ## Documentation Index > Fetch the complete documentation index at: https://docs.moralis.com/llms.txt > Use this file to discover all available pages before exploring further. # Token Balances by Wallet > Sync every ERC-20 token a wallet holds, with its current non-zero balance, into your own database: a portfolio lookup that's a single prefix scan. ### Question it answers > "Give me every token wallet **0x…** holds, with its current non-zero balance." This is the by-wallet portfolio read. It's the same ingest as Token Balances by Token, sorted the other way: this template keys **wallet-first** so one wallet's full token holdings are a contiguous range read, while the sibling keys token-first to list a token's holders. ### What you get The template lands **one balance observation per wallet, per transfer leg**. Each `token_transfer` carries absolute post-transfer balances (`from_post_balance` / `to_post_balance`), so every transfer becomes two observations: one for the sender, one for the receiver. The **latest observation per `(wallet, token)`** is the current balance ("latest-wins"). | Column | Description | | --------------------------- | ----------------------------------------------------------------------------------------------------------------------- | | `chain_id` | Chain identifier | | `wallet_address` | The holder (leading sort key) | | `token_address` | The ERC-20 contract | | `balance` | Absolute post-transfer balance for this wallet, raw `uint256` (the latest per `(wallet, token)` is the current balance) | | `leg` | `from` or `to`: which side of the transfer produced this observation | | `block_number`, `log_index` | On-chain ordering tuple; picks the latest observation | | `event_ts` | Block time | | `vendor_event_id` | Stable per-observation identity (keeps rows unique) | The zero address is excluded, so a token's balance going to `0` on a full transfer-out is a real observation you read as the current state, not a missing row. ### Source The transform reads one per-block array, `tokenTransfers`, and unpivots each transfer into two per-wallet observations: `(from_address, from_post_balance)` and `(to_address, to_post_balance)`. Because the source supplies **absolute** post-balances on each transfer, there's no running-sum reconstruction: the latest observation is the balance. ### Destination | Destination | Table | By-wallet access | | ---------------------------- | ----------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------- | | **ClickHouse** (first-class) | `fact_balances_by_wallet` | Prefix scan on `(chain_id, wallet_address, token_address, …)`; current balance via `argMax` over `FINAL` | | **Postgres** | `token_balances` (materialized view over `token_balance_observations`) | Partial index `(wallet_address, token_address) WHERE balance > 0` | | **MySQL** | `token_balances` (trigger-maintained over `token_balance_observations`) | PK `(wallet_address, token_address)` + `DELETE … WHERE balance = 0` cleanup | ClickHouse uses the collapsing log-table pattern (see the [templates overview](/data-feeds/templates/overview#destinations)) so chain reorganizations self-correct. The fact table's sort key is wallet-first, so a wallet's holdings are a contiguous range read; you collapse to the current balance per token with `argMax` over `FINAL`. ### Full schema Below is the complete read table this template produces. It's a starting point: keep the columns you need and drop the rest (see [Schema & flexibility](/data-feeds/templates/overview#schema--flexibility)). Raw `uint256` balances are stored as text (ClickHouse) or `NUMERIC(76, 0)` (Postgres) so they never overflow. ```sql theme={null} CREATE TABLE recipe_token_balances_by_wallet.fact_balances_by_wallet ( vendor_event_id String, ingested_at DateTime64(3), chain_id UInt32, block_hash String, block_number UInt64, log_index UInt32, event_ts DateTime64(3), token_address String, wallet_address String, balance String, -- absolute post-transfer balance, raw uint256 leg LowCardinality(String), -- from | to sign Int8 ) ENGINE = ReplicatedCollapsingMergeTree( '/clickhouse/tables/{database}/fact_balances_by_wallet', '{replica}', sign) PARTITION BY (chain_id, toYYYYMM(event_ts)) ORDER BY (chain_id, wallet_address, token_address, block_number, log_index, vendor_event_id, leg); ``` The `sign` column drives reorg collapsing; read with `FINAL` then `argMax`, or a sign-aware aggregate, never a bare `WHERE sign = 1`. A single-node setup can use `CollapsingMergeTree(sign)` without the replication path. ```sql theme={null} -- 1. Observations (sink target): one row per wallet, per transfer leg. CREATE TABLE public.token_balance_observations ( position BIGINT NOT NULL, log_index BIGINT NOT NULL, block_number BIGINT NOT NULL, block_timestamp BIGINT NOT NULL, -- unix seconds token_address TEXT NOT NULL, wallet_address TEXT NOT NULL, balance NUMERIC(76, 0) NOT NULL, leg TEXT NOT NULL, vendor_event_id TEXT NOT NULL ); -- Recency index leads with the DISTINCT ON keys so the REFRESH avoids a sort. CREATE INDEX tbo_token_wallet_recency_idx ON public.token_balance_observations (token_address, wallet_address, block_number DESC, log_index DESC); -- 2. Current-balance materialized view: latest observation per (token, wallet). CREATE MATERIALIZED VIEW public.token_balances AS SELECT DISTINCT ON (token_address, wallet_address) token_address, wallet_address, balance, block_number, log_index, block_timestamp FROM public.token_balance_observations ORDER BY token_address, wallet_address, block_number DESC, log_index DESC; CREATE UNIQUE INDEX token_balances_pk ON public.token_balances (token_address, wallet_address); -- This template's primary access path: active balances held by a wallet. CREATE INDEX token_balances_by_wallet_active_idx ON public.token_balances (wallet_address, token_address) WHERE balance > 0; -- Sibling access path (all non-zero holders of a token). CREATE INDEX token_balances_by_token_active_idx ON public.token_balances (token_address, wallet_address) WHERE balance > 0; -- Cleanup index for zeroed positions. CREATE INDEX token_balances_zero_cleanup_idx ON public.token_balances (wallet_address, token_address) WHERE balance = 0; ``` Refresh the view on a schedule: `REFRESH MATERIALIZED VIEW CONCURRENTLY token_balances;`. MySQL is the same shape with a trigger-maintained `token_balances` table and a `DELETE … WHERE balance = 0` cleanup. `position` is the block-level cursor used during backfill. ### Run it Everything runs in **your** infrastructure: the sink writes into a database you own and you query it with plain SQL, no API and no per-call limits. **Prerequisites** * **Docker** and Docker Compose. * A **Data Feeds key**, a `cm_live_…` key with `continuum:read` scope (this is *not* your Web3 API key). Log in to the Moralis admin panel to create your account and generate a `cm_live_…` key. **Start the sink** Generate a **starter pack** for `tokenBalancesByWalletAddress` in the [Moralis Admin Panel](https://admin.moralis.com), pick your chain and destination database, then run it with Docker. The [quickstart](/data-feeds/migration/quickstart) walks through it end to end. Set the backfill window (`MORALIS_HISTORICAL_FROM_BLOCK`) as noted below. **Backfill full history for complete balances.** A balance is *current state* (the latest transfer-derived observation per `(wallet, token)`), and a wallet can still hold a token it last moved years ago. To return a wallet's *complete* holdings, backfill from an early block (`MORALIS_HISTORICAL_FROM_BLOCK=0`, or a token's deploy block if you scope to one token). A recent-only window like `tip-1000` captures only tokens that moved in that window, so balances would be incomplete. Unlike an event feed, a partial window is **not** correct here. Full-chain backfill on a busy chain is large; plan for it. See [History & backfill](/data-feeds/concepts/history-and-backfill). Then run the example reads below to confirm rows are landing. ### Example reads Every token a wallet holds, current non-zero balance only (ClickHouse; `argMax` over `FINAL` picks the latest observation per token): ```sql theme={null} SELECT token_address, argMax(balance, (block_number, log_index)) AS current_balance FROM recipe_token_balances_by_wallet.fact_balances_by_wallet FINAL WHERE chain_id = 1 AND wallet_address = lower('0x...') GROUP BY token_address HAVING current_balance != '0' AND current_balance != '' ORDER BY token_address; ``` Postgres (after `REFRESH MATERIALIZED VIEW CONCURRENTLY token_balances;`): ```sql theme={null} SELECT token_address, balance FROM public.token_balances WHERE wallet_address = lower('0x...') AND balance > 0 ORDER BY balance DESC; ``` MySQL: ```sql theme={null} SELECT token_address, balance FROM token_balances WHERE wallet_address = LOWER('0x...') AND balance > 0 ORDER BY balance DESC; ``` ### Other ways to consume this data The sink is the way to get **this pre-shaped table** (`token_balances`), recommended for a portfolio lookup you own. The *same underlying data* is also available as the **raw decoded feed**, if you'd rather derive balances yourself: * **Your existing message clients**: stream it with **Kafka / AMQP / SQS**. * **REST / Arrow Flight**: pull the decoded block stream directly. * **Your warehouse**: query it via **Iceberg** from Snowflake, BigQuery, Spark, or DuckDB. Those give you the **source array** (`tokenTransfers`, which carries absolute post-transfer balances) to compute balances yourself, not this pre-computed table. All of them read the one data lake into **your** infrastructure. See [What are Data Feeds?](/data-feeds/concepts/what-are-data-feeds). ### Modes Shipped defaults: **ClickHouse `hybrid`** (backfill → realtime), **Postgres / MySQL `historical`** (one-shot backfill). For live/reorg-safe ingestion, use ClickHouse; see the [overview](/data-feeds/templates/overview#modes). The backfill cursor (`position`) is block-level, so realtime/hybrid on Postgres / MySQL is constrained by their single-column `UNIQUE` requirement. Run realtime/hybrid on **ClickHouse**; the Postgres / MySQL configs target `historical` backfill. ### Multichain The template is chain-parametrized: point it at any supported EVM chain or Solana. On Solana, the per-observation identity already folds `(from, to, token, amount)` into `vendor_event_id` so rows stay unique under Solana's repeated `logIndex`; the balances it produces are identical in shape. ### Fidelity gaps * **Raw balances only.** `balance` is the raw `uint256` post-transfer amount; divide by `10^token_decimals` to read human units. Fold in a decimals lookup from a Token Metadata sync if you need it pre-scaled. * **No USD value.** This template lands quantities, not dollar value. Join to a Token Prices sync at read time for portfolio valuation. * **Latest-wins semantics.** A `(wallet, token)` row reflects the most recent transfer-derived post-balance. Direct mints/burns or rebases that emit a transfer are captured; balance changes with no transfer event are not. ## Migrating from the REST API This template replaces the wallet-balance family of Web3 API endpoints. Data Feeds are **not** 1:1 replicas of those responses: you land the underlying data in your own database and reconstruct the response shape with a view. The subsections below cover each REST endpoint: what replaces it, the field mapping (**exact** = straight from the chain, **calculated** = derived from real DEX trades, very close, **add yourself** = off-chain signal not in the feed), and the gotchas specific to that endpoint. ### GET /wallets/:address/tokens The enriched balances-plus-prices endpoint. Replaced by this template's `token_balances` table joined with three sibling templates for native balance, token details, and prices. This endpoint needs **four templates**, not one. Run this template plus [Native Balances](/data-feeds/templates/wallet/native-balances) (`native_balances`), [Token Metadata](/data-feeds/templates/token/token-metadata) (`token_metadata`), and [Token Prices](/data-feeds/templates/token/token-prices) (`token_price_updates`) against the same database, then join them in one view. There is no single pre-joined template; the composition is the supported path. | `/wallets/:address/tokens` | Data Feeds | Fidelity | | --------------------------------------------------------------------------- | ------------------------------------------- | ---------- | | `token_address`, `balance` (raw) | `token_balances.*` | exact | | `balance_formatted` | derive: `balance / 10^decimals` | exact | | `name`, `symbol`, `decimals`, `total_supply(_formatted)` | `token_metadata.*` | exact | | `usd_price` | latest `token_price_updates.usd_price` | calculated | | `usd_value`, `usd_price_24hr_*`, `portfolio_percentage` | derive from balance × price / price history | calculated | | `percentage_relative_to_total_supply` | derive: `balance / total_supply` | exact | | `native_token` + native balance | `native_balances` | exact | | `logo`, `thumbnail`, `possible_spam`, `verified_contract`, `security_score` | add yourself | off-chain | The reconstruction view (Postgres; adapt to your destination): ```sql theme={null} CREATE VIEW wallet_token_balances_price AS WITH latest_price AS ( SELECT DISTINCT ON (token_address) token_address, usd_price FROM token_price_updates ORDER BY token_address, block_number DESC -- newest price per token ) SELECT b.wallet_address, b.token_address, m.symbol, m.name, m.decimals, b.balance AS balance_raw, b.balance / power(10, m.decimals) AS balance_formatted, p.usd_price, (b.balance / power(10, m.decimals)) * p.usd_price AS usd_value, m.total_supply, b.balance / NULLIF(m.total_supply, 0) AS percentage_relative_to_total_supply FROM token_balances b LEFT JOIN token_metadata m ON m.token_address = b.token_address LEFT JOIN latest_price p ON p.token_address = b.token_address WHERE b.balance > 0; ``` Finishing touches: `portfolio_percentage` is `usd_value / SUM(usd_value)` per wallet at query time; `UNION` in `native_balances` priced with the native asset's USD mark; derive 24-hour change from the price \~24h ago in `token_price_updates`. **Gotchas** * USD values come from real DEX trades: very close to the old endpoint, but compare with a tolerance, not bit-for-bit. A thinly-traded token may not have a price yet. * No fixed spam filter: you get every token the wallet holds, then apply your own spam/dust rules. * No wallet-size limit: the old endpoint caps very large wallets; your own dataset has none. * Balances are current-state on Postgres/MySQL; use ClickHouse for real-time/hybrid. ### GET /:address/erc20 The plain ERC-20 balances endpoint (no prices, no portfolio math). Replaced by this template's `token_balances` table joined with [Token Metadata](/data-feeds/templates/token/token-metadata) for name/symbol/decimals/supply: two templates, one view, every onchain value exact. | `/:address/erc20` | Data Feeds | Fidelity | | --------------------------------------------------------------------------- | ------------------------------- | --------- | | `token_address`, `balance` (raw) | `token_balances.*` | exact | | `name`, `symbol`, `decimals`, `total_supply` | `token_metadata.*` | exact | | `total_supply_formatted`, `percentage_relative_to_total_supply` | derive from those values | exact | | `balance_formatted` *(bonus, the REST response omits it)* | derive: `balance / 10^decimals` | exact | | `logo`, `thumbnail`, `possible_spam`, `verified_contract`, `security_score` | add yourself | off-chain | The view is the balances-and-prices view above minus the price join: join `token_balances` to `token_metadata`, keep `balance > 0`, and derive the formatted/supply-relative columns. The old `exclude_spam` / `exclude_unverified_contracts` filters become a `WHERE` against your own list. **Gotchas** * Everything onchain is exact: no pricing engine in the loop, nothing to reconcile with a tolerance. * ERC-20 only, matching the REST endpoint. For native (ETH) balance, add [Native Balances](/data-feeds/templates/wallet/native-balances). * No wallet-size limit: the old endpoint errors on very large wallets ("too many ERC20 token balances"); your dataset doesn't, and you get real keyset pagination on top. ### GET /wallets/:address/net-worth The USD net-worth roll-up. There is no single replacement table; net worth is `SUM(token balance × price) + native balance × native price`, computed over the same tables as `/wallets/:address/tokens`. This endpoint needs **four templates**: this template plus [Native Balances](/data-feeds/templates/wallet/native-balances), [Token Metadata](/data-feeds/templates/token/token-metadata), and [Token Prices](/data-feeds/templates/token/token-prices). Build the balances-and-prices view above first, then aggregate it; net worth is a `SUM` on top of that view. | `/wallets/:address/net-worth` | Data Feeds | Fidelity | | -------------------------------------------- | --------------------------------------- | ---------- | | `native_balance`, `native_balance_formatted` | `native_balances.balance` | exact | | `native_balance_usd` | derive: native balance × native price | calculated | | `token_balance_usd` | `SUM(usd_value)` over the balances view | calculated | | `networth_usd` | derive: native + token USD | calculated | | `total_networth_usd` | derive: sum across chains | calculated | The roll-up view (Postgres; sums the balances view above and adds the native leg): ```sql theme={null} CREATE VIEW wallet_net_worth AS WITH tokens AS ( SELECT wallet_address, sum(usd_value) AS token_balance_usd -- exclude what shouldn't count (see gotchas) FROM wallet_token_balances_price WHERE usd_value IS NOT NULL GROUP BY wallet_address ), native AS ( SELECT n.wallet_address, n.balance / 1e18 AS native_balance_formatted, n.balance / 1e18 * p.usd_price AS native_balance_usd FROM native_balances n LEFT JOIN LATERAL ( SELECT usd_price FROM token_price_updates WHERE token_address = lower('0xC02aaa39b223FE8D0A0e5C4F27eAD9083C756Cc2') -- WETH = native mark ORDER BY block_number DESC LIMIT 1 ) p ON true ) SELECT coalesce(t.wallet_address, n.wallet_address) AS wallet_address, n.native_balance_formatted, n.native_balance_usd, coalesce(t.token_balance_usd, 0) AS token_balance_usd, coalesce(t.token_balance_usd, 0) + coalesce(n.native_balance_usd, 0) AS networth_usd FROM tokens t FULL JOIN native n ON n.wallet_address = t.wallet_address; ``` **Gotchas** * **Curating the token set is the whole game.** One spam or bogus-priced token can dominate the total. The old `exclude_spam` / `exclude_unverified_contracts` / `min_pair_side_liquidity_usd` filters become your own `WHERE` in the `tokens` CTE; here they're essential, not polish. * Stale prices: a token that hasn't traded recently has no fresh mark. Decide whether to value it at zero, carry forward the last mark, or exclude it. Each choice moves the total. * Cross-chain net worth: each chain is its own feed. Compute per chain and sum `networth_usd` into `total_networth_usd`. * Compare with a tolerance: USD comes from real DEX trades, very close to the old figure, not identical. * A missed holding silently changes the total, so full-history backfill (see the note in [Run it](#run-it)) matters even more here. ### Related The sibling: same ingest, keyed token-first to list a token's holders. The use case this balance feed powers.