ERC-721 transfers

This guide streams every ERC-721 Transfer event into your own database, with token IDs intact, so you can track ownership, mints, and burns per token. This guide is part of a series of tutorials on how you can stream transfer data into your data warehouse using Turbo pipelines. Here we will be focusing on ERC-721 Transfers, visit the following guides for other types of transfers:

What you’ll need

A Goldsky account and the CLI installed
Install Goldsky's CLI and log in
1. Install the Goldsky CLI: For macOS/Linux:
  curl https://goldsky.com | sh
  For Windows:
  npm install -g @goldskycom/cli
  Windows users need to have Node.js and npm installed first. Download from nodejs.org if not already installed.
2. Log into your Project by running:
  goldsky login
  This opens your browser to sign in (Google, GitHub, SSO, or email). Once you authenticate, the CLI is logged in automatically — there’s no API key to copy or paste.
  On a headless or remote machine (or in CI), create an API key on your Project Settings page and pass it directly with goldsky login --token <API_KEY>. Use goldsky login --no-browser to print the login URL instead of opening a browser.
3. Now that you are logged in, run goldsky to get started:
  goldsky
A basic understanding of the Turbo product
A destination sink to write your data to. In this example, we will use the PostgreSQL sink

Introduction

In order to stream all the ERC-721 Transfers of a chain there are two potential methods available:

Use the readily available ERC-721 dataset for the chain you are interested in: this is the easiest and quickest method to get you streaming token transfers into your sink of choice with minimum code.
Build the ERC-721 Transfers pipeline from scratch using raw or decoded logs: this method takes more code and time to implement but it’s a great way to learn about how you can use decoding functions in case you want to build more customized pipelines.

Let’s explore both methods below with more detail:

Using the ERC-721 transfers source dataset

Every EVM chain has its own ERC-721 dataset available for you to use as source in your pipelines. You can check this by running the goldsky dataset list command and finding the EVM chain of your choice. For this example, let’s use apex chain and create a simple pipeline definition using its ERC-721 dataset that writes the data into a PostgreSQL instance:

apex-erc721-transfers.yaml

name: apex-erc721-pipeline
resource_size: s

sources:
  apex_erc721_transfers:
    type: dataset
    dataset_name: apex.erc721_transfers
    version: 1.0.0
    start_at: earliest

transforms: {}

sinks:
  # Postgres sink for the apex.erc721_transfers dataset
  postgres_erc721_transfers:
    type: postgres
    from: apex_erc721_transfers
    schema: public
    table: apex_erc721_transfers
    secret_name: <YOUR_SECRET>
    primary_key: id

If you copy and use this configuration file, make sure to update:

Your secret_name. If you already created a secret, you can find it via the CLI command goldsky secret list.
The schema and table you want the data written to, by default it writes to public.apex_erc721_transfers.

You can start the pipeline by running:

goldsky turbo apply apex-erc721-transfers.yaml

That’s it! You should soon start seeing ERC-721 token transfers in your database.

Building ERC-721 transfers from scratch using logs

The ERC-721 datasets used as the source above encapsulate all the decoding logic explained in this section. Read on if you want to see how it’s implemented, or to extend or modify this logic yourself. To build the token transfers pipeline from scratch, use the raw_logs dataset for that chain in combination with decoding functions using the ABI of a specific ERC-721 contract.

Building ERC-721 transfers using decoding functions

In this example, we will stream all the Transfer events of all the ERC-721 tokens for the Scroll chain. To that end, we will dynamically fetch the ABI of the Cosmic Surprise token from the Scrollscan API (available here) and use it to identify all the same events for the tokens in the chain. We have decided to use the ABI of this NFT contract for this example but any other ERC-721 compliant token would also work. We need to differentiate ERC-20 token transfers from ERC-721 (NFT) transfers since they have the same event signature in decoded data: Transfer(address,address,uint256). However, if we look closely at their event definitions we can appreciate that the number of topics differ:

ERC-20: event Transfer(address indexed _from, address indexed _to, uint256 _value)
ERC-721: event Transfer(address indexed _from, address indexed _to, uint256 indexed _tokenId)

ERC-20 Transfer events have three topics (one topic for event signature + 2 topics for the indexed params). NFTs on the other hand have four topics as they have one more indexed param in the event signature. We will use this as a filter in our pipeline transform to only index ERC-721 Transfer events. Let’s now see all these concepts applied in an example pipeline definition:

Pipeline definition

scroll-erc721-transfers.yaml

name: scroll-erc721-transfers
resource_size: s

sources:
  my_scroll_mainnet_raw_logs:
    type: dataset
    dataset_name: scroll_mainnet.raw_logs
    version: 1.0.0
    start_at: earliest

transforms:
  # Fetch the ABI from Scrollscan for the Cosmic Surprise token and decode matching logs
  scroll_decoded:
    type: sql
    primary_key: id
    sql: |
      SELECT
        *,
        _gs_log_decode(
          _gs_fetch_abi('https://api.scrollscan.com/api?module=contract&action=getabi&address=0xcf7f37b4916ac5c530c863f8c8bb26ec1e8d2ccb&apikey=YOUR_KEY', 'etherscan'),
          topics,
          data
        ) AS decoded
      FROM my_scroll_mainnet_raw_logs
      WHERE topics LIKE '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef%'
        AND SPLIT_INDEX(topics, ',', 3) IS NOT NULL

  # Clean up the previous transform, unnest the values from the decoded struct
  scroll_clean:
    type: sql
    primary_key: id
    sql: |
      SELECT
        *,
        decoded.event_params AS event_params,
        decoded.event_signature AS event_name
      FROM scroll_decoded
      WHERE decoded IS NOT NULL
        AND decoded.event_signature = 'Transfer'

  # Select the transfer columns we want in the database
  scroll_721_transfers:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        lower(address) AS contract_address,
        lower(event_params[1]) AS sender,
        lower(event_params[2]) AS recipient,
        COALESCE(TRY_CAST(event_params[3] AS DECIMAL(38, 0)), -999) AS token_id,
        event_name,
        block_number,
        block_hash,
        log_index,
        transaction_hash,
        transaction_index
      FROM scroll_clean

sinks:
  # Postgres sink for ERC-721 transfers
  scroll_721_sink:
    type: postgres
    from: scroll_721_transfers
    schema: public
    table: erc721_transfers
    secret_name: <YOUR_SECRET>
    primary_key: id

If you copy and use this configuration file, make sure to update:

Your secret_name. If you already created a secret, you can find it via the CLI command goldsky secret list.
The schema and table you want the data written to, by default it writes to public.erc721_transfers.

There are 3 transforms in this pipeline definition which we’ll explain how they work:

Transform: scroll_decoded

SELECT
  *,
  _gs_log_decode(
      _gs_fetch_abi('https://api.scrollscan.com/api?module=contract&action=getabi&address=0xcf7f37b4916ac5c530c863f8c8bb26ec1e8d2ccb&apikey=YOUR_KEY', 'etherscan'),
      topics,
      data
  ) AS decoded
  FROM my_scroll_mainnet_raw_logs
  WHERE topics LIKE '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef%'
  AND SPLIT_INDEX(topics, ',', 3) IS NOT NULL

As explained in the decode custom contract events guide we first make use of the _gs_fetch_abi function to get the ABI from Scrollscan and pass it as first argument to the function _gs_log_decode to decode its topics and data. We store the result in a decoded struct which we unnest on the next transform. We include the topic and SPLIT_INDEX filters here to limit decoding only to the relevant events.

topics LIKE '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef%': topics is a comma separated string. Each value in the string is a hash. The first is the hash of the full event_signature (including arguments), in our case Transfer(address,address,uint256) for ERC-721, which is hashed to 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef. We use LIKE to only consider the first signature, with a % at the end, which acts as a wildcard.
SPLIT_INDEX(topics, ',', 3) IS NOT NULL: as mentioned in the introduction, ERC-20 transfers share the same event_signature as ERC-721 transfers. The difference between them is the number of topics associated with the event. ERC-721 transfers have four topics, and ERC-20 transfers have three.

To make the SPLIT_INDEX check more concrete, here’s an example topics string for an ERC-721 transfer:

0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef,0x000000000000000000000000441e1e47a6fa2dbfd3cd9b54291e9ab3a58d7975,0x00000000000000000000000097d2e8eeb59e521f10c2e7716eac3dd805ea9a46,0x0000000000000000000000000000000000000000000000000000000000043321

SPLIT_INDEX splits the string by commas and extracts the element at the given 0-based index, in this case 3, which is the fourth element (here the token ID). An ERC-20 transfer would only have three elements when the topics are split, so SPLIT_INDEX would return NULL. If you want to get into the nitty gritty you may enjoy the Solidity developer documentation for events.

Transform: scroll_clean

SELECT
  *,
  decoded.event_params AS event_params,
  decoded.event_signature AS event_name
  FROM scroll_decoded
  WHERE decoded IS NOT NULL
  AND decoded.event_signature = 'Transfer'

In this second transform, we take the event_params and event_signature from the result of the decoding. We then filter the query on:

decoded IS NOT NULL: to leave out potential null results from the decoder
decoded.event_signature = 'Transfer': the decoder will output the event name as event_signature, excluding its arguments. We use it to filter only for Transfer events.

Transform: scroll_721_transfers

SELECT
  id,
  lower(address) AS contract_address,
  lower(event_params[1]) AS sender,
  lower(event_params[2]) AS recipient,
  COALESCE(TRY_CAST(event_params[3] AS DECIMAL(38, 0)), -999) AS token_id,
  event_name,
  block_number,
  block_hash,
  log_index,
  transaction_hash,
  transaction_index
  FROM scroll_clean

In this last transform we are essentially selecting all the Transfer information we are interested in having in our database. We’ve included a number of columns that you may or may not need, the main columns needed for most purposes are: id, contract_address (if you are syncing multiple contract addresses), sender, recipient and token_id.

id: This is the Goldsky provided id, it is a string composed of the dataset name, block hash, and log index, which is unique per event, here’s an example: log_0x60eaf5a2ab37c73cf1f3bbd32fc17f2709953192b530d75aadc521111f476d6c_18
lower(address) AS contract_address: We use the lower function here to lower-case the address to make using this data simpler downstream, we also rename the column to contract_address to make it more explicit.
lower(event_params[1]) AS sender: Here we continue to lower-case values for consistency. In this case we’re using the first element of the event_params array (using a 1-based index), and renaming it to sender. Each event parameter maps to an argument to the event_signature.
lower(event_params[2]) AS recipient: Like the previous column, we’re pulling the second element in the event_params array and renaming it to recipient.

You can save some space when storing the ID by using md5(id) AS id in your transform. One reason you may want to keep the existing id format is that it makes it easier to order events in the same block without also syncing block hash and log index.

For the token_id we introduce a few SQL functions COALESCE(TRY_CAST(event_params[3] AS DECIMAL(38, 0)), -999) AS token_id. We’ll start from the inside and work our way out.

event_params[3] is the third element of the event_params array, and for ERC-721 this is the token ID. Although not covered in this example, since ERC-20 shares the same signature, this element represents a token balance rather than token ID if you’re decoding ERC-20 transfers.
TRY_CAST(event_params[3] AS DECIMAL(38, 0)) is casting the string element event_params[3] to a fixed-precision decimal. Token IDs can be as large as an unsigned 256 bit integer (up to 78 digits), which exceeds DECIMAL(38, 0) precision; some contracts derive token IDs from hashes, for example. We use TRY_CAST because it will prevent the pipeline from failing in case the cast fails, returning a NULL value instead. If you need the full 256-bit range, keep event_params[3] as a string instead, or use the U256 functions.
COALESCE(TRY_CAST(event_params[3] AS DECIMAL(38, 0)), -999): COALESCE can take an arbitrary number of arguments and returns the first non-NULL value. Since TRY_CAST can return a NULL we’re returning -999 in case it does. This isn’t strictly necessary but is useful to do in case you want to find offending values that were unable to be cast.

Lastly, we are also adding more block metadata to the query to add context to each transaction:

event_name,
block_number,
block_hash,
log_index,
transaction_hash,
transaction_index

These columns are often useful for looking up the transfers in another tool, such as a block explorer like Etherscan. It’s worth mentioning that in this example we are interested in all the ERC-721 Transfer events but if you would like to filter for specific contract addresses you could simply add a WHERE filter to this query with the addresses you are interested in, like: WHERE address IN ('0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D', '0x22c1f6050e56d2876009903609a2cc3fef83b415')

If you plan to combine ERC-721 transfers with ERC-1155 transfers in a single table, add a constant 1 AS amount column to this transform. NFTs don’t have a quantity associated with them when transferred, so normalizing each ERC-721 transfer as a transfer of a single token with an amount of 1 keeps the schemas aligned.

Deploying the pipeline

Our last step is to deploy this pipeline and start sinking ERC-721 transfer data into our database. Assuming we are using the same file name for the pipeline configuration as in this example, we can use the CLI apply command like this:

goldsky turbo apply scroll-erc721-transfers.yaml

After some time, you should see the pipeline start streaming Transfer data into your sink.

Remember that you can always speed up the streaming process by increasing the resource_size in your pipeline YAML and re-applying it with goldsky turbo apply.

Here’s an example transfer record from our sink:

id	contract_address	sender	recipient	token_id	event_name	block_number	block_hash	log_index	transaction_hash	transaction_index
log_0x5e3225c40254dd5b1b709152feafaa8437e505ae54c028b6d433362150f99986_34	0x6e55472109e6abe4054a8e8b8d9edffcb31032c5	0xd2cda3fa01d34878bbe6496c7327b3781d4422bc	0x6e55472109e6abe4054a8e8b8d9edffcb31032c5	38087399	Transfer	4057598	0x5e3225c40254dd5b1b709152feafaa8437e505ae54c028b6d433362150f99986	34	0xf06c42ffd407bb9abba8f00d4a42cb7f1acc1725c604b8895cdb5f785f827967	11

We can find this transaction in Scrollscan. We see that it corresponds to the transfer of MERK token:

This concludes our successful deployment of a Turbo pipeline streaming ERC-721 tokens from Scroll chain into our database using inline decoders. Congrats!

Using the data

With this table in place, you can create views in your database that show you a number of useful pieces of information:

All mints. For ERC-721 (and ERC-1155) a mint is identified by having the zero address 0x0000000000000000000000000000000000000000 as the sender.
All current holders of a token.

For example, here’s a query you can run against the erc721_transfers table in your database to compute current holders. Each transfer credits the recipient with one token and debits the sender with one:

WITH ledger AS (
  SELECT contract_address, recipient AS owner_address, token_id, 1 AS quantity, block_number FROM erc721_transfers
  UNION ALL
  SELECT contract_address, sender AS owner_address, token_id, -1 AS quantity, block_number FROM erc721_transfers
)
SELECT
  contract_address,
  owner_address,
  token_id,
  SUM(quantity) AS balance,
  MIN(block_number) AS first_updated,
  MAX(block_number) AS last_updated
FROM ledger
WHERE owner_address <> '0x0000000000000000000000000000000000000000'
GROUP BY contract_address, owner_address, token_id
HAVING SUM(quantity) > 0;

Conclusion

In this guide, we have learnt how Turbo pipelines simplify streaming NFT Transfer events into your database. We have first looked into the easy way of achieving this, simply by making use of the readily available ERC-721 dataset of the EVM chain and using it as the source to our pipeline. We have also deep dived into the standard decoding method using decoding functions, implementing an example on Scroll chain. With Turbo pipelines, developers gain flexibility and efficiency in integrating blockchain data, opening up new possibilities for applications and insights. Can’t find what you’re looking for? Reach out to us at support@goldsky.com for help.

Turbo

Guides

Reference

Mirror (legacy)

What you’ll need

Introduction

Using the ERC-721 transfers source dataset

Building ERC-721 transfers from scratch using logs

Building ERC-721 transfers using decoding functions

Pipeline definition

Deploying the pipeline

Using the data

Conclusion

​What you’ll need

​Introduction

​Using the ERC-721 transfers source dataset

​Building ERC-721 transfers from scratch using logs

​Building ERC-721 transfers using decoding functions

​Pipeline definition

​Deploying the pipeline

​Using the data

​Conclusion

What you’ll need

Introduction

Using the ERC-721 transfers source dataset

Building ERC-721 transfers from scratch using logs

Building ERC-721 transfers using decoding functions

Pipeline definition

Deploying the pipeline

Using the data

Conclusion