Skip to main content

​
Question it answers

“Give me every current owner of NFT collection 0x… — each token_id, the wallet that holds it, and how many (ERC-1155 editions).” Mirrors Moralis GET /nft/{address}/owners.
This is the by-collection sibling of NFTs by Wallet — same ingest, sorted the other way. Each NFT transfer carries the absolute post-transfer holdings of both wallets, so the latest observation per (token_address, token_id, wallet) is the current owner. Storing the resolved owner set in your own database — keyed by collection — is the value.

​
What you get

Each NFT transfer becomes two per-wallet holding observations (leg = from | to), where amount is the absolute count of that token_id held by the wallet after the transfer. The latest observation per (token_address, token_id, wallet_address) wins; keep amount > 0 for the current owner set.
ColumnDescription
token_addressThe NFT collection (contract)
token_idThe specific NFT — uint256, stored as text (256-bit ids overflow numeric types)
wallet_addressThe holder; the EVM zero address is excluded (mint/burn counterparty)
amountAbsolute post-transfer holding of this token_id — 0/1 for ERC-721, an arbitrary count for ERC-1155 editions
contract_typeERC-721 / ERC-1155 discriminator
legfrom or to — which side of the transfer produced this observation
block_number, log_indexRecency tuple — latest wins per holding key
event_ts / block_timestampBlock time

​
Source

The transform reads one per-block array — nftTokenTransfers — and unpivots each event into two observations using its fromPostBalance / toPostBalance fields (the from- and to-wallet’s absolute count of that token_id after the transfer). No price or metadata join is involved.

​
Destination

DestinationTableBy-collection access
ClickHouse (first-class)fact_nft_owners_by_contractPrefix scan on (chain_id, token_address, token_id, wallet_address, …); current owner via argMax over FINAL
Postgresnft_owner_observations → nft_owners (materialized view)Partial index (token_address, token_id, wallet_address) WHERE amount > 0
MySQLnft_owner_observations → nft_owners (trigger-maintained)PK (token_address, token_id, wallet_address) + amount = 0 cleanup
ClickHouse uses the collapsing log-table pattern (see the recipes overview) so chain reorganizations self-correct. The fact table is sorted collection-first, so “every owner of collection X” is a contiguous range read. Ownership is latest-wins: an observation is current state for its holding key, not an append-only event — read canonical state with FINAL then argMax, never a bare WHERE sign = 1.

​
Full schema

Below is the complete read table this recipe produces — one row per holding observation. Keep the columns you need and drop the rest (see Schema & flexibility). token_id and amount are stored as text / wide numeric because full-range uint256 ids and counts overflow standard numeric types. 
CREATE TABLE recipe_nft_owners_by_contract.fact_nft_owners_by_contract
(
    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,
    token_id          String,                   -- uint256, kept as text
    contract_type     LowCardinality(String),   -- ERC721 | ERC1155
    wallet_address    String,
    amount            String,                   -- absolute post-transfer holding of this token_id
    leg               LowCardinality(String),   -- from | to
    sign              Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_nft_owners_by_contract', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, token_id, wallet_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. A single-node setup can use CollapsingMergeTree(sign) without the replication path.
CREATE TABLE public.nft_owner_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,
  token_id         TEXT    NOT NULL,         -- uint256, kept as text
  contract_type    TEXT    NOT NULL,         -- ERC721 | ERC1155
  wallet_address   TEXT    NOT NULL,
  amount           NUMERIC(76, 0) NOT NULL,  -- absolute post-balance of this token_id
  leg              TEXT    NOT NULL,
  vendor_event_id  TEXT    NOT NULL
);

-- Recency index for the DISTINCT ON; leads with the holding key so
-- REFRESH MATERIALIZED VIEW CONCURRENTLY avoids a sort.
CREATE INDEX noo_owner_recency_idx
  ON public.nft_owner_observations
  (token_address, token_id, wallet_address, block_number DESC, log_index DESC);

-- Current-owner surface: latest observation per (token, id, wallet).
CREATE MATERIALIZED VIEW public.nft_owners AS
SELECT DISTINCT ON (token_address, token_id, wallet_address)
  token_address, token_id, wallet_address, contract_type,
  amount, block_number, log_index, block_timestamp
FROM public.nft_owner_observations
ORDER BY token_address, token_id, wallet_address, block_number DESC, log_index DESC;

CREATE UNIQUE INDEX nft_owners_pk
  ON public.nft_owners (token_address, token_id, wallet_address);

-- Primary access path: current owners of a collection.
CREATE INDEX nft_owners_by_contract_active_idx
  ON public.nft_owners (token_address, token_id, wallet_address) WHERE amount > 0;

-- OPTIONAL: sibling by-wallet access path — every NFT held by a wallet.
-- CREATE INDEX nft_owners_by_wallet_active_idx
--   ON public.nft_owners (wallet_address, token_address, token_id) WHERE amount > 0;
MySQL is the same shape with a trigger-maintained nft_owners state table and a DELETE … WHERE amount = 0 cleanup. position is the block-level cursor used during backfill. Refresh the materialized view on a schedule with REFRESH MATERIALIZED VIEW CONCURRENTLY nft_owners;.

​
Example reads

Current owners of a collection — latest non-zero holding per token_id + wallet (ClickHouse): 
SELECT token_id, wallet_address, contract_type, current_amount
FROM (
  SELECT token_id,
         wallet_address,
         argMax(contract_type, (block_number, log_index)) AS contract_type,
         argMax(amount, (block_number, log_index))        AS current_amount
  FROM recipe_nft_owners_by_contract.fact_nft_owners_by_contract FINAL
  WHERE chain_id = 1 AND token_address = lower('0x...')
  GROUP BY token_id, wallet_address
)
WHERE current_amount != '0' AND current_amount != ''
ORDER BY token_id, wallet_address;
Current owners (Postgres, after refreshing the materialized view): 
REFRESH MATERIALIZED VIEW CONCURRENTLY nft_owners;

SELECT token_id, wallet_address, contract_type, amount
FROM public.nft_owners
WHERE token_address = lower('0x...') AND amount > 0
ORDER BY token_id, wallet_address;
Distinct holder count for a collection (Postgres): 
SELECT count(DISTINCT wallet_address)
FROM public.nft_owners
WHERE token_address = lower('0x...') AND amount > 0;

​
Modes

Shipped defaults: ClickHouse hybrid (backfill → realtime), Postgres / MySQL historical (one-shot backfill). For live/reorg-safe ingestion, use ClickHouse — see the overview.
Realtime/hybrid on Postgres / MySQL is constrained because the block-level position cursor requires a single-column unique key. Run realtime/hybrid on ClickHouse; the Postgres / MySQL configs target historical backfill.

​
Multichain

The recipe is chain-parametrized via the chain setting — point it at any supported EVM chain. NFTs on Solana are SPL tokens; if a chain emits NFT transfers under the same nftTokenTransfers struct, set the Solana chain variables. The vendor_event_id already incorporates (token_id, amount) on top of log_index to stay row-unique under Solana’s repeated log indices; the owner surface it produces is identical in shape.

​
Fidelity gaps

This recipe returns on-chain ownership only. Fields a Moralis /nft/{address}/owners response carries that have no onchain source (they need a separate metadata/indexer service) are not populated:
  • name, symbol, token_uri, metadata, normalized_metadata — collection / token metadata (use an NFT Collection Metadata recipe).
  • possible_spam, verified-collection flags — risk / curation signals.
  • block_number_minted and historical mint provenance beyond the backfill window.
amount is the raw on-chain holding count, not a marketplace listing. The core access key — token_address → owners with token_id, amount, contract_type — is fully sourced from nftTokenTransfers.

NFTs by Wallet

The same ingest, sorted by wallet — every NFT a wallet holds.

NFT Transfers

The event-level feed behind these owner observations.

NFT Collection Metadata

The name, symbol, and token URIs this recipe leaves out.

NFT Marketplace

The use case this owner surface powers.