<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Massimo Luraschi on Medium]]></title>
        <description><![CDATA[Stories by Massimo Luraschi on Medium]]></description>
        <link>https://medium.com/@raekwonthethird?source=rss-6f504f633941------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Rn6KjjXnJ8eybio7ryusfg.png</url>
            <title>Stories by Massimo Luraschi on Medium</title>
            <link>https://medium.com/@raekwonthethird?source=rss-6f504f633941------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 01:42:46 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@raekwonthethird/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Build your first DeFi dashboard]]></title>
            <link>https://medium.com/subsquid/build-your-first-defi-dashboard-ad3ce1e9fc73?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/ad3ce1e9fc73</guid>
            <category><![CDATA[smart-contracts]]></category>
            <category><![CDATA[defi]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[dapps]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Thu, 04 May 2023 09:10:33 GMT</pubDate>
            <atom:updated>2023-05-04T09:10:33.517Z</atom:updated>
            <content:encoded><![CDATA[<h3>Build your first DeFi dashboard with Subsquid</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9Yyxv-1QkqBCzswt1hEUEg.png" /></figure><h3>Summary</h3><p>Given the positive feedback received for <a href="https://medium.com/subsquid/how-to-build-a-performant-and-scalable-full-stack-nft-marketplace-63c12466b959">this past article</a> I had in mind for quite a while to create a similar one, with a different subject. I settled for a DeFi dashboard, because, along with NFT marketplace and gallery, they cover a good portion of the most common use cases for an indexing middleware.</p><p>I thought this topic would be a good introduction for Web3 beginners, provide some inspiration to hackathon participants, as well as providing a good introduction to Subsquid for more expert developers, and how this tech can be used in a familiar context.</p><p>Here’s what we’ll be building in this walkthrough:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RNGqvOBiRp7ZKimF8Duktw.png" /><figcaption>Our finished DeFi dashboard</figcaption></figure><h3>Introduction</h3><p>When there&#39;s a part of a project that is necessary, but not the actual core of it, not the primary focus, I am a big fan of leveraging what has already been created and integrating it, building on top of it. This way, I can get close to the objective much faster, it removes part of the complexity, and I genuinely think that this is the true strength of the Open Source community.</p><p>I qualify myself as &quot;below average&quot; (being generous with myself here) in regards to frontend development, especially when it comes to make a web app look good. For this reason, I set out to look out for dashboard examples and tutorials, and that&#39;s when I came across <a href="https://youtu.be/KpGZjrrS3pY">this one</a>.</p><p>I liked the end result and the overall style, I also thought I could make use of pretty much all pages and it was as close as possible to what I wanted to obtain in the end, which meant I had to do minimal changes to turn it into a DeFi dashboard.</p><h3>Project and setup</h3><p>The video linked above does a pretty good job explaining how to get the frontend up and running and how to correctly architecture and style the various components. Because of this, I thought it best to avoid repetition and start exactly where the original tutorial ended. I have created a start branch in the repository for this. The final product of this tutorial can, instead, be found in the main branch.</p><p><a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/tree/start">GitHub - RaekwonIII/subsquid-defi-dashboard at start</a></p><blockquote><strong>Note:</strong> I have coded my project starting from a TypeScript NextJS app, so some things are going to look a bit different and that is why I suggest using my repository as a starting point. This is the repository of the original tutorial:</blockquote><blockquote><a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/tree/main">https://github.com/fireclint/nextjs-dashboard-tailwind</a></blockquote><p>If you want to dive deeper into how this was built and learn a bit more about NextJS, or Tailwind, I advise watching the tutorial, and then come back here to find out how we can <strong>add dynamic on-chain data to it</strong>.</p><p>The original tutorial, in fact, uses hard-coded data and a JSON file to provide numbers and information shown on the page. In this walkthrough we are going to build a Squid project, which we are going to use to Extract, Transform, Load (and Query) data from an actual on-chain DeFi project.</p><p>While preparing this project I was a bit short on ideas regarding the DeFi part, I didn’t know what I could use as an example, or if, due to lack of options, I should develop and deploy a smart contract for this purpose.</p><p>It just so happened that around the same time, I had to index some data for the Moonwell protocol for a different assignment, so in the end, to the occasion presented itself and the choice was made.</p><p>So for some of the data that will eventually be displayed in the dashboard, I took inspiration from <a href="https://moonwell.fi/artemis/GLMR">Moonwell app page</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tnlfB-7KV3sStKkstrmlWg.png" /></figure><h3>Squid ETL</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FKVabPQqdKno%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DKVabPQqdKno&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FKVabPQqdKno%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7ad696770854429b8bb50fc117bfa1b1/href">https://medium.com/media/7ad696770854429b8bb50fc117bfa1b1/href</a></iframe><p>In order to make my chart dynamic and show real data, I created a new squid project. The project created for this article is hosted <a href="https://github.com/RaekwonIII/moonwell-squid">on GitHub in this repository</a>.</p><p><a href="https://github.com/RaekwonIII/moonwell-squid">GitHub - RaekwonIII/moonwell-squid</a></p><blockquote><strong>Note:</strong> the squid project is separate from the React app. Think of it as the backend for the dashboard&#39;s frontend.</blockquote><blockquote>It is hosted in a separate repository, and when writing this tutorial, it has been generated in a separate directory from the dashboard project.</blockquote><p>Because Moonbeam is an EVM chain, just like Ethereum, I chose the evm template.</p><pre>sqd init moonwell-squid -t evm</pre><p>Which should create these files and folders:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/322/1*MN41s3gZigkIj8ihLYIOLQ.png" /><figcaption>Initial structure of the squid ETL project</figcaption></figure><h4>Schema</h4><p>Once I had an idea of what data I wanted to show, I set off to edit the schema.graphql file and configure what queries and fields I needed. Moonwell has multiple liquidity pools; for this project I only focussed on GLMR, but I designed the schema to work if the project is expanded to include more, that&#39;s why I have a Market entity, where its ID is the address of the token used for the liquidity pool.</p><p>I then grouped up every interaction of users with the token itself (be it providing liquidity, asking to morrow some, repaying the lended sum, or being liquidated, …) into a MarketOrder entity, and finally, added the bare minimum information to capture users&#39; Accounts, which I wanted to show in a separate page.</p><p>Here&#39;s the end result, I have left comments to each fields to explain them:</p><pre>&quot;&quot;&quot;<br>Market stores all high level variables for a cToken market<br>&quot;&quot;&quot;<br>type Market @entity {<br>  &quot;CToken address&quot;<br>  id: ID!<br><br>  address: String!<br><br>  &quot;Name of the cToken&quot;<br>  name: String!<br><br>  &quot;CToken symbol&quot;<br>  symbol: String!<br><br>  &quot;Underlying token name&quot;<br>  underlyingName: String!<br><br>  &quot;Underlying token price in USD&quot;<br>  underlyingPriceUSD: BigDecimal!<br><br>  &quot;Underlying token decimal length&quot;<br>  underlyingDecimals: Int!<br><br>  &quot;Borrows in the market&quot;<br>  totalBorrows: BigDecimal!<br><br>  &quot;CToken supply.&quot;<br>  totalSupply: BigDecimal!<br><br>  &quot;Reserves stored in the contract&quot;<br>  reserves: BigDecimal!<br><br>  &quot;The cToken contract balance of ERC20 or ETH&quot;<br>  cash: BigDecimal!<br><br>  &quot;Yearly supply rate.&quot;<br>  supplyRate: BigDecimal!<br><br>  &quot;Yearly borrow rate.&quot;<br>  borrowRate: BigDecimal!<br><br>  &quot;Exchange rate of tokens / cTokens&quot;<br>  exchangeRate: BigDecimal!<br><br>  &quot;The history of the markets borrow index return (Think S&amp;P 500)&quot;<br>  borrowIndex: BigInt!<br><br>  &quot;The factor determining interest that goes to reserves&quot;<br>  reserveFactor: BigInt!<br><br>  &quot;Block the market is updated to&quot;<br>  accrualBlockTimestamp: Int!<br><br>  &quot;Timestamp the market was most recently updated&quot;<br>  blockTimestamp: BigInt!<br>}<br><br>enum MaketOrderType {<br>  BORROW<br>  LIQUIDATE_BORROW<br>  MINT<br>  REDEEM<br>  REPAY_BORROW<br>  TRANSFER<br>}<br><br>type MarketOrder @entity {<br>  &quot;Transaction hash concatenated with log index&quot;<br>  id: ID!<br>  type: MaketOrderType!<br>  &quot;cTokens transferred&quot;<br>  amount: BigDecimal!<br>  &quot;Account that received tokens&quot;<br>  to: String!<br>  &quot;Account that sent tokens&quot;<br>  from: String!<br>  &quot;Block number&quot;<br>  blockNumber: Int!<br>  &quot;Block time&quot;<br>  blockTime: BigInt!<br>  &quot;Symbol of the cToken transferred&quot;<br>  cTokenSymbol: String!<br>  &quot;Symbol of the underlying asset repaid through liquidation&quot;<br>  underlyingSymbol: String!<br>  &quot;Underlying token amount transferred&quot;<br>  underlyingAmount: BigDecimal<br>  &quot;Underlying cToken amount that was repaid by liquidator&quot;<br>  underlyingRepayAmount: BigDecimal<br>}<br><br>type Account @entity {<br>  id: ID!<br><br>  &quot;Timestamp of most recent order&quot;<br>  latestOrder: BigInt!<br><br>  orderType: MaketOrderType!<br>}</pre><p>Thanks to Subsquid’s SDK, it’s possible to automatically generate TypeScript models for these types, and use them in our code. It’s only necessary to run this command in the squid’s root folder:</p><pre>sqd codegen</pre><p>This will create TypeScript files with the necessary classes in the src/model/generated folder, which are, in fact, an abstraction of tables on the database, and will be used to save data to it.</p><h4>Smart contract and ABI code-bindings</h4><p>The next step in Squid development is usually importing the ABI of interested smart contracts into the project, and generate code bindings for them.</p><ul><li>The GLMR liquidity pool has its main logic handled by <a href="https://moonscan.io/address/0x091608f4e4a15335145be0a279483c0f8e4c7955#code">this smart contract</a>, which &quot;wraps&quot; the original GLMR token, creates an mGLMR money market.<br>I copied the ABI from the contract&#39;s page, then pasted it in a file named mGLMR.json in the abi folder.</li><li>Since I wanted the FIAT price of the GLMR token, I needed a price oracle, luckily, Moonwell has deployed a Chainlink oracle on Moonbeam, <a href="https://moonscan.io/address/0xed301cd3eb27217bdb05c4e9b820a8a3c8b665f9">at this addres</a>.<br>I followed the same step as before, copying the ABI and pasting it to a file named chainlinkOracle.json in the same folder.</li><li>Last thing I needed is to know when the GLMR price is updated in that Oracle. For this, <a href="https://moonscan.io/address/0x62ca6b55f0bb1241c635e3dff51883f8b9f49aa4#code">I have found the Feed contract</a> that emits an event whenever this happens.<br>I copied its ABI as well, and pasted it in a file I named feed.json in the same folder as the others.</li></ul><p>Once all ABI JSON files have been created, it&#39;s possible to use Subsquid&#39;s CLI to generate TypeScript bindings for them, using this command:</p><pre>sqd typegen</pre><p>This will generate TypeScript files in the src/abi folder, that make it possible to interact with the contracts (decode event, access topic signature, use ethers.js to interact with contract view functions, …) while indexing their data.</p><h4>Data processing</h4><p>It&#39;s finally time to work with the on-chain data. I wanted to keep this part simple, but it still a little bit verbose. To keep the article short, I am going to list the functionalities that need to be accomplished, and add some code snippets to showcase how they are implemented:</p><ul><li>configure the indexer to extract data generated by the mGLMR and Oracle Feed smart contracts and filter for the events I am interested in: AccrueInterest, Borrow, LiquidateBorrow, Mint, Redeem, RepayBorrow, Transfer (for mGLMR contract), AnswerUpdated (for Oracle Feed)</li></ul><pre><br>const mGLMROracleContract = &quot;0xED301cd3EB27217BDB05C4E9B820a8A3c8B665f9&quot;;<br>const mGLMRPriceFeedContract = &quot;0x62ca6b55f0bb1241c635e3dff51883f8b9f49aa4&quot;;<br>const mGLMRcontractAddress = &quot;0x091608f4e4a15335145be0a279483c0f8e4c7955&quot;;<br><br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    chain: process.env.MOONBEAM_CHAIN_NODE || &quot;wss://wss.api.moonbeam.network&quot;,<br>    archive: lookupArchive(&quot;moonbeam&quot;, { type: &quot;EVM&quot; }),<br>  })<br>  .setBlockRange({ from: 1277865 })<br>  .addLog(mGLMRcontractAddress, {<br>    filter: [<br>      [<br>        mGLRMEvents.AccrueInterest.topic,<br>        mGLRMEvents.Borrow.topic,<br>        mGLRMEvents.LiquidateBorrow.topic,<br>        mGLRMEvents.Mint.topic,<br>        mGLRMEvents.Redeem.topic,<br>        mGLRMEvents.RepayBorrow.topic,<br>        mGLRMEvents.Transfer.topic,<br>      ],<br>    ],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  })<br>  .addLog(mGLMRPriceFeedContract, {<br>    filter: [[feedEvents.AnswerUpdated.topic]],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  });</pre><ul><li>Process the batch of mixed events that Subsquid Archive provides at recurring intervals and sort out the different event kinds, decode them separately, store the decoded data in an array of MarketOrder models. I have added an optimization for AccrueInterest and AnswerUpdated to avoid spamming RPC nodes and only save the latest price update</li></ul><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  let latestPriceUpdateEventCtx:<br>    | LogHandlerContext&lt;<br>        Store,<br>        { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>      &gt;<br>    | undefined = undefined;<br>  let accrueInterestEventCtxArr:<br>    | LogHandlerContext&lt;<br>        Store,<br>        { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>      &gt;[] = [];<br>  let latestIntegerPrice = BigDecimal(0);<br>  let marketOrders: MarketOrder[] = [];<br><br>  // one full loop to get the latest price in this batch<br>  // solves initialization problem for the market updates<br>  for (let block of ctx.blocks) {<br>    for (let i of block.items) {<br>      if (i.address === mGLMRPriceFeedContract &amp;&amp; i.kind === &quot;evmLog&quot;) {<br>        if (i.evmLog.topics[0] === feedEvents.AnswerUpdated.topic) {<br>          latestPriceUpdateEventCtx = {<br>            ...ctx,<br>            block: block.header,<br>            ...i,<br>          };<br>        }<br>      }<br>    }<br>  }<br>  if (latestPriceUpdateEventCtx !== undefined) {<br>    // in reality this should be surrounded with try/catch<br>    latestIntegerPrice = await handleAnswerUpdated(latestPriceUpdateEventCtx)<br>  }<br><br>  // one other loop over the batch to process market updates, with the price established.<br>  for (let block of ctx.blocks) {<br>    for (let i of block.items) {<br>      if (i.address === mGLMRcontractAddress &amp;&amp; i.kind === &quot;evmLog&quot;) {<br>        if (i.evmLog.topics[0] === mGLRMEvents.AccrueInterest.topic) {<br>          // add events to be indexed when they are at least 1 hour apart<br>          accrueInterestEventCtxArr.push({<br>            ...ctx,<br>            block: block.header,<br>            ...i,<br>          })<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.Borrow.topic) {<br>          const { borrower, borrowAmount, totalBorrows, accountBorrows } =<br>            mGLRMEvents.Borrow.decode(i.evmLog);<br>          ctx.log.debug(<br>            `totalBorrows ${totalBorrows}, accountBorrows ${accountBorrows}`<br>          );<br><br>          marketOrders.push(<br>            handleMArketOrder(<br>              mGLMRcontractAddress,<br>              borrower,<br>              borrowAmount,<br>              i.transaction.hash,<br>              i.evmLog.index,<br>              block.header.height,<br>              BigInt(block.header.timestamp),<br>              MaketOrderType.TRANSFER<br>            )<br>          );<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.LiquidateBorrow.topic) {<br>          // similar logic to Borrow event<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.Mint.topic) {<br>          // similar logic to Borrow event<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.Redeem.topic) {<br>          // similar logic to Borrow event<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.RepayBorrow.topic) {<br>           // similar logic to Borrow event<br>        } else if (i.evmLog.topics[0] === mGLRMEvents.Transfer.topic) {<br>          // similar logic to Borrow event<br>        }<br>      }<br>    }<br>  }<br>  // process all market updates found in the batch<br>  // and save all the data<br>});</pre><ul><li>Process the events that updated the money market and fetch data from the on-chain contract, by querying its status.</li></ul><pre>async function updateMarket(<br>  ctx: LogHandlerContext&lt;<br>    Store,<br>    { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>  &gt;,<br>  latestIntegerPrice: BigDecimal<br>): Promise&lt;void&gt; {<br>  const { cashPrior, borrowIndex, interestAccumulated, totalBorrows } =<br>    mGLRMEvents.AccrueInterest.decode(ctx.evmLog);<br>  const market = new Market({<br>    // create a new instance with default values<br>  });<br>  const contractAPI = new mGLMRContract(<br>    ctx,<br>    { height: ctx.block.height },<br>    mGLMRcontractAddress<br>  );<br><br>  const [<br>    accrualBlockTimestamp,<br>    totalSupply,<br>    exchangeRateStored,<br>    totalReserves,<br>    reserveFactorMantissa,<br>    supplyRatePerTimestampResult,<br>    borrowRatePerTimestampResult,<br>  ] = await Promise.all([<br>    contractAPI.accrualBlockTimestamp(),<br>    contractAPI.totalSupply(),<br>    contractAPI.exchangeRateStored(),<br>    contractAPI.totalReserves(),<br>    contractAPI.reserveFactorMantissa(),<br>    contractAPI.supplyRatePerTimestamp(),<br>    contractAPI.borrowRatePerTimestamp(),<br>  ]);<br><br>  // data operations to convert data types and respect decimals<br><br>  await ctx.store.save(market);<br>}</pre><ul><li>Process the various events generated by users interacting with the money market, and save Account data</li></ul><pre>async function saveAccounts(<br>  ctx: BlockHandlerContext&lt;Store&gt;,<br>  marketOrders: MarketOrder[]<br>) {<br>  const accountIds: Set&lt;string&gt; = new Set();<br><br>  for (const marketOrder of marketOrders) {<br>    if (marketOrder.from) accountIds.add(marketOrder.from.toLowerCase());<br>    if (marketOrder.to) accountIds.add(marketOrder.to.toLowerCase());<br>  }<br><br>  const accounts: Map&lt;string, Account&gt; = new Map(<br>    (await ctx.store.findBy(Account, { id: In([...accountIds]) })).map((account) =&gt; [<br>      account.id,<br>      account,<br>    ])<br>  );<br><br>  for (const marketOrder of marketOrders) {<br><br>    let fromAccount = accounts.get(marketOrder.from);<br>    if (fromAccount == null) {<br>      fromAccount = new Account({ <br>        id: marketOrder.from.toLowerCase(),<br>        latestOrder: marketOrder.blockTime,<br>        orderType: marketOrder.type<br>      });<br>      accounts.set(fromAccount.id, fromAccount);<br>    }<br>    if (fromAccount.latestOrder &lt; marketOrder.blockTime) {<br>      fromAccount.latestOrder = marketOrder.blockTime<br>      fromAccount.orderType = marketOrder.type<br>    }<br><br>    let toAccount = accounts.get(marketOrder.to);<br>    if (toAccount == null) {<br>      toAccount = new Account({ <br>        id: marketOrder.to.toLowerCase(),<br>        latestOrder: marketOrder.blockTime,<br>        orderType: marketOrder.type<br>      });<br>      accounts.set(toAccount.id, toAccount);<br>    }<br>    if (toAccount.latestOrder &lt; marketOrder.blockTime) {<br>      toAccount.latestOrder = marketOrder.blockTime<br>      toAccount.orderType = marketOrder.type<br>    }<br>  }<br><br>  ctx.log.info(`Saving ${accounts.size} accounts`)<br><br>  await ctx.store.save([...accounts.values()]);<br>}</pre><p>All the logic is defined in the processor.ts file, under src folder. If you want to take a closer look, I invite you to browse <a href="https://github.com/RaekwonIII/moonwell-squid/blob/main/src/processor.ts">the GitHub repository</a> and inspect the code in detail.</p><h4>Data aggregation</h4><p>So far, the defined data processing logic makes sure that all the historical information is saved on the database. But the dashboard I had in mind does not need to show a data point for every single event that updated the money market. Rather, it needs some <em>aggregated daily data</em> for it.</p><p>This is where the <a href="https://hasura.io/learn/graphql/hasura/custom-business-logic/2-remote-schemas/">custom resolver pattern</a> for GraphQL servers comes in handy. This simply consists in writing a server extension and specify custom queries that the resolver will use to return the data.</p><p>With this in mind, I set out to create new index.ts file under the src/server-extension/resolvers folder, where I implemented daily aggregation of the money market data using relatively simple SQL queries.</p><p>Here&#39;s the content of the index.ts file:</p><pre>import &quot;reflect-metadata&quot;;<br>import type { EntityManager } from &quot;typeorm&quot;;<br>import { Field, ObjectType, Query, Resolver } from &quot;type-graphql&quot;;<br>import { Market } from &quot;../../model&quot;;<br>import { BigDecimal } from &quot;@subsquid/big-decimal&quot;;<br><br>@ObjectType()<br>export class MarketDayData {<br>  @Field(() =&gt; Date, { nullable: false })<br>  day!: Date;<br><br>  @Field(() =&gt; Number, { nullable: true })<br>  totalSupply!: number;<br><br>  @Field(() =&gt; Number, { nullable: true })<br>  totalBorrows!: number;<br><br>  constructor(props: Partial&lt;MarketDayData&gt;) {<br>    Object.assign(this, props);<br>  }<br>}<br><br>@Resolver()<br>export class MarketDayDataResolver {<br>  constructor(private tx: () =&gt; Promise&lt;EntityManager&gt;) {}<br><br>  @Query(() =&gt; [MarketDayData])<br>  async getMarketDayData(): Promise&lt;MarketDayData[]&gt; {<br>    const manager = await this.tx();<br>    const repository = manager.getRepository(Market);<br><br>    const data: {<br>      day: string;<br>      total_supply: number;<br>      total_borrows: number;<br>    }[] = await repository.query(`<br>            SELECT <br>            DATE(to_timestamp(block_timestamp::numeric/1000)) AS day, <br>            AVG(total_supply) as total_supply, <br>            AVG(total_borrows) as total_borrows<br>            FROM market<br>            GROUP BY day<br>            ORDER BY day ASC<br>        `);<br>    return data.map(<br>      (i) =&gt;<br>        new MarketDayData({<br>          day: new Date(i.day),<br>          totalSupply: i.total_supply,<br>          totalBorrows: i.total_borrows<br>        })<br>    );<br>  }<br>}<br><br>@ObjectType()<br>export class PostDayData {<br>  @Field(() =&gt; Date, { nullable: false })<br>  day!: Date;<br><br>  @Field(() =&gt; Number, { nullable: false })<br>  count!: number;<br><br>  constructor(props: Partial&lt;PostDayData&gt;) {<br>    Object.assign(this, props);<br>  }<br>}</pre><p>Because TypeScript works with…well…types, I had to install a new library for this to work properly:</p><pre>npm i type-graphql</pre><p>There are only a couple of steps left to have the indexer up and running. First, launch the database container, with the command:</p><pre>sqd up</pre><p>Then, the SDK handles interactions with the database itself, like creating tables and columns via <em>migrations</em>. Because the template is a fully finished project, it came with its own migration files to apply the correct schema to the database. Because the initial schema has been replaced, the migration files need to be re-created as well. this can be done with these two commands:</p><pre>sqd migration:clean<br>sqd migration</pre><p>Next, it&#39;s time to launch the indexer and start processing blockchain data, with the command:</p><pre>sqd process</pre><p>This will block the terminal, showing indexing logs until either the processor terminates due to an error, or it&#39;s manually stopped.</p><p>The last step is to launch the GraphQL server, so in a different terminal window, run the command:</p><pre>sqd serve</pre><p>The log message says the server is listening to port number <strong>4350</strong>. This means that opening the browser at the URL <a href="http://localhost:4350/graphql">http://localhost:4350/graphql</a>, it&#39;s possible to use the GraphQL playground to test some queries, for example, this one:</p><pre>query MyQuery {<br>  getMarketDayData {<br>    day<br>    totalBorrows<br>    totalSupply<br>  }<br>}</pre><p>Can be used to populate a chart showing the trend of total supply and total borrows for Moonwell protocol, on a daily basis. And it&#39;s exactly what I&#39;m going to explain in the next section.</p><p>I have deployed this project in Aquarium: Subsquid&#39;s hosting service for squids. The API <a href="https://squid.subsquid.io/moonwell-squid/v/v2/graphql">is available at this endpoint</a>, if you want to test it out and compare with what you developed so far.</p><h3>Dynamic data in React</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2F7y_nvzihbEE%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D7y_nvzihbEE&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2F7y_nvzihbEE%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/74b7c3f574e44dec734808d48125d805/href">https://medium.com/media/74b7c3f574e44dec734808d48125d805/href</a></iframe><p>Now that the data indexing is being taken care of, the final touch is to go back to the React app, perform API requests to the squid GraphQL API, and use this data to feed the chart on the page.</p><p>To do so, I installed the axios library first, to be able to perform http requests:</p><pre>npm i axios</pre><h4>Home page — Line chart</h4><p>As I just mentioned, the custom resolver build in the previous section can be used to populate a chart, so this is exactly what I am going to start with. I am going to describe what I have done in a short list, and then provide a code snippet:</p><ul><li>I wanted a Line chart, not a Bar chart, so I renamed the BarChart file to LineChart, as well as the component&#39;s name (if you do this via an IDE, it should take care of the import and usage in index.tsx, otherwise, you&#39;d have to change this manually)</li><li>Because of this the Line component has to be imported and returned by our custom component.</li><li>The configuration and options for Line chart are slightly different from Bar ones. I also took the chance to make it a Multi Axis Line chart, because the two values I am showing have different scales, which would appear on opposite sides of the chart.</li><li>The axios library needed to be imported</li><li>the useState and useEffect calls needed to be changed to fetch data from the API and save it to a React state</li><li>I also added a default message, for when the API fails to fetch data, or it is simply waiting the response</li></ul><p>Here&#39;s the code for LineChart.tsx:</p><pre>import { useState, useEffect } from &quot;react&quot;;<br>import { Line } from &quot;react-chartjs-2&quot;;<br>import { ChartData, ChartOptions } from &quot;chart.js&quot;;<br>import &#39;chart.js/auto&#39;<br>import axios from &quot;axios&quot;;<br><br>export const options = {<br>  responsive: true,<br>  maintainAspectRatio: false,<br>  plugins: {<br>    legend: {<br>      position: &#39;top&#39; as const,<br>    },<br>    title: {<br>      display: true,<br>      text: &#39;Supply and Borrow totals&#39;,<br>    },<br>  },<br>  scales: {<br>    y: {<br>      type: &#39;linear&#39; as const,<br>      display: true,<br>      position: &#39;left&#39; as const,<br>    },<br>    y1: {<br>      type: &#39;linear&#39; as const,<br>      display: true,<br>      position: &#39;right&#39; as const,<br>      grid: {<br>        drawOnChartArea: false,<br>      },<br>    },<br>  },<br>};<br><br>const headers = {<br>  &quot;content-type&quot;: &quot;application/json&quot;,<br>};<br><br>const requestBody = {<br>  query: `query MyQuery {<br>    getMarketDayData {<br>      day<br>      totalBorrows<br>      totalSupply<br>    }<br>  }<br>  `,<br>};<br><br>const graphQLOptions = {<br>  method: &quot;POST&quot;,<br>  url:  process.env.NEXT_PUBLIC_SQUID_URL || &quot;http://localhost:4350/graphql&quot;,<br>  headers,<br>  data: requestBody,<br>};<br><br>export const CHART_COLORS = {<br>  red: &#39;rgb(255, 99, 132)&#39;,<br>  orange: &#39;rgb(255, 159, 64)&#39;,<br>  yellow: &#39;rgb(255, 205, 86)&#39;,<br>  green: &#39;rgb(75, 192, 192)&#39;,<br>  blue: &#39;rgb(54, 162, 235)&#39;,<br>  purple: &#39;rgb(153, 102, 255)&#39;,<br>  grey: &#39;rgb(201, 203, 207)&#39;<br>};<br><br>function LineChart() {<br>  const [chartData, setChartData] = useState&lt;ChartData&lt;&#39;line&#39;&gt; | null&gt;(<br>    null<br>  );<br>  const [chartOptions, setChartOptions] = useState&lt;ChartOptions&lt;&#39;line&#39;&gt; | null&gt;(<br>    null<br>  );<br>  useEffect(() =&gt; {<br>    try {<br>      axios(graphQLOptions).then((response) =&gt; {<br>        setChartData({<br>          labels: [<br>            ...response.data.data.getMarketDayData.map((obj: any) =&gt; new Date(obj.day).toLocaleDateString()),<br>          ],<br>          datasets: [<br>            {<br>              label: &quot;Total Borrows daily values&quot;,<br>              data: [<br>                ...response.data.data.getMarketDayData.map((obj: any) =&gt; obj.totalBorrows),<br>              ],<br>              borderColor: CHART_COLORS.red,<br>              backgroundColor: CHART_COLORS.red.replace(&#39;)&#39;, &#39;, 0.5)&#39;).replace(&#39;rgb&#39;, &#39;rgba&#39;),<br>              yAxisID: &#39;y&#39;,<br>            },<br>            {<br>              label: &quot;Total Supply daily values&quot;,<br>              data: [<br>                ...response.data.data.getMarketDayData.map((obj: any) =&gt; obj.totalSupply),<br>              ],<br>              borderColor: CHART_COLORS.blue,<br>              backgroundColor: CHART_COLORS.blue.replace(&#39;)&#39;, &#39;, 0.5)&#39;).replace(&#39;rgb&#39;, &#39;rgba&#39;),<br>              yAxisID: &#39;y1&#39;,<br>            },<br>          ],<br>        });<br>      });<br>    } catch (err) {<br>      console.log(&quot;ERROR DURING AXIOS REQUEST&quot;, err);<br>    }<br><br>    setChartOptions(options)<br>  }, [])<br>  if (!chartData) return (&lt;div&gt;<br>      &lt;h1&gt;No data 🤷‍♂️!&lt;/h1&gt;<br>      &lt;h2&gt;Try to refresh the page in a short while&lt;/h2&gt;<br>    &lt;/div&gt;);<br><br>  return (<br>    &lt;div className=&quot;w-full md:col-span-2 relative lg:h-[70vh] h-[50vh] m-auto p-4 border rounded-lg bg-white&quot;&gt;<br>      &lt;Line data={chartData} options={options}&gt;&lt;/Line&gt;<br>    &lt;/div&gt;<br>  );<br>}<br><br>export default LineChart;</pre><blockquote><strong>Note:</strong> I am using an environment variable named NEXT_PUBLIC_SQUID_URL for the squid API endpoint. I advise you to create a .env file in the project&#39;s root folder and define this variable, by specifying the URL of the squid running locally, or deployed in Aquarium.</blockquote><h4>Home page — Top Cards</h4><p>I then set out to change the section at the top, so the TopCards.tsx file needed some editing as well:</p><ul><li>In order to show the &quot;+/- X%&quot; bubbles in the cards, I decided to perform two separate queries, two separate API requests.</li><li>I introduced, similarly to the Line chart component, useState and useEffect React function calls, to perform the API request and set the result as a state.</li><li>I changed the returned html code returned to make use of the dynamic data</li><li>I took this chance to change this from 3 cards to return 5 cards, because I found out I had more data points. (In doing this, I think I broke the responsiveness, which I am not sure if I will have the time to fix before wrapping up the article 😝)</li></ul><p>To keep it short, I am only reporting the queries used:</p><pre>query latestMarketQuery {<br>  markets(limit: 1, orderBy: blockTimestamp_DESC, where: {underlyingPriceUSD_not_eq: &quot;0&quot;}) {<br>    totalSupply<br>    totalBorrows<br>    symbol<br>    supplyRate<br>    reserves<br>    name<br>    cash<br>    borrowRate<br>    underlyingPriceUSD<br>    underlyingDecimals<br>    blockTimestamp<br>  }<br>}<br><br>query yesterdayMarketQuery {<br>  markets(limit: 1, orderBy: blockTimestamp_DESC, where: {AND: {blockTimestamp_lt: &quot;1681985229000&quot;, underlyingPriceUSD_not_eq: &quot;0&quot;}}) {<br>      totalSupply<br>      totalBorrows<br>      supplyRate<br>      reserves<br>      cash<br>      borrowRate<br>      underlyingPriceUSD<br>      blockTimestamp<br>  }<br>}</pre><p>The data conversion done for a successful API request:</p><pre>setLatestData({<br>  symbol: obj.symbol,<br>  name: obj.name,<br>  totalSupply: obj.totalSupply,<br>  totalSupplyStr: Number(obj.totalSupply).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }),<br>  totalBorrows: obj.totalBorrows,<br>  totalBorrowsStr: Number(obj.totalBorrows).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }),<br>  supplyRate: obj.supplyRate,<br>  reserves: obj.reserves,<br>  reservesStr: Number(obj.reserves).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }),<br>  cash: obj.cash,<br>  cashStr: Number(obj.cash).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }),<br>  borrowRate: obj.borrowRate,<br>  price: obj.underlyingPriceUSD,<br>  priceStr: Number(obj.underlyingPriceUSD).toLocaleString(undefined, { maximumFractionDigits: 3, minimumFractionDigits: 3 }),<br>  underlyingDecimals: obj.underlyingDecimals,<br>  //   date: new Date(Number(obj.blockTime)).toLocaleDateString(),<br>})</pre><p>How the differences are calculated:</p><pre>const supplyDiff = Number(Number((latestData.totalSupply - yesterdayData.totalSupply) * 100 / latestData.totalSupply).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }))<br>const borrowsDiff = Number(Number((latestData.totalBorrows - yesterdayData.totalBorrows) * 100 / latestData.totalBorrows).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }))<br>const cashDiff = Number(Number((latestData.cash - yesterdayData.cash) * 100 / latestData.cash).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }))<br>const reservesDiff = Number(Number((latestData.reserves - yesterdayData.reserves) * 100 / latestData.reserves).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }))<br>const priceDiff = Number(Number((latestData.price - yesterdayData.price) * 100 / latestData.price).toLocaleString(undefined, { maximumFractionDigits: 2, minimumFractionDigits: 2 }));</pre><p>Lastly, I decided to use the conditional formatting shown in the dashboard video, to change the bubble background and text color, depending on the differential value (red if negative, green if positive). Here&#39;s an example for the daily differential for total supply:</p><pre>return(<br>//...<br>  &lt;p<br>    className={<br>      supplyDiff &lt; 0<br>        ? &quot;bg-red-200 flex justify-center items-center p-2 rounded-lg&quot;<br>        : &quot;bg-green-200 flex justify-center items-center p-2 rounded-lg&quot;<br>    }<br>  &gt;<br>    &lt;span<br>      className={<br>        supplyDiff &lt; 0 ? &quot;text-red-700 text-lg&quot; : &quot;text-green-700 text-lg&quot;<br>      }<br>    &gt;<br>      {supplyDiff}%<br>    &lt;/span&gt;<br>  &lt;/p&gt;<br>//...<br>);</pre><p>If you want to &quot;cheat&quot;, <a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/blob/main/src/components/TopCards.tsx">you can take a peak at the end result on GitHub</a>.</p><h4>Home page — Recent Orders</h4><p>The last component of the home I took care of is the recent orders one. Following a similar pattern to the previous two:</p><ul><li>I imported axios library</li><li>Leveraged useEffect to perform the API request, and set the result as a React state via useState function</li><li>Adapted the returned HTML to include the dynamic data, and fit the information I had available</li></ul><p>Question time: how would you change the component, knowing this is the query I used for it?</p><pre>query MyQuery {<br>  marketOrders(limit: 20, orderBy: blockTime_DESC) {<br>    type<br>    to<br>    from<br>    cTokenSymbol<br>    id<br>    blockTime<br>    amount<br>  }<br>}</pre><blockquote><strong>Hint:</strong> the answer is, once again, <a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/blob/main/src/components/RecentOrders.tsx">on GitHub 😅</a></blockquote><h4>Orders and customers pages</h4><p>This took care of the home page, so the next step was to edit orders.tsx and add dynamic data over there too. The list of changes follows a similar pattern to the previous ones, as a matter of fact, the orders page is a more detailed version of the Recent Orders component.</p><p>I did, however, change the query to use the marketOrdersConnection, because it allows for pagination:</p><pre>query MyQuery {<br>  marketOrdersConnection(orderBy: blockTime_DESC, first: 25) {<br>    totalCount<br>    edges {<br>      cursor<br>      node {<br>        amount<br>        blockTime<br>        cTokenSymbol<br>        from<br>        id<br>        to<br>        type<br>      }<br>    }<br>  }<br>}</pre><p>To keep this tutorial short, I am inviting you to <a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/blob/main/src/pages/orders.tsx">directly check the result on GitHub</a>, but I am letting you know that I ended up not using pagination on this page, so look at the conclusions section for some &quot;homework&quot;. 😉</p><p>As for the customers page, blockchain is all about privacy, so I definitely don&#39;t have name, surname and email available, but I decided to list the address of accounts placing orders, as well as the account&#39;s latest order and the type of order. Similarly to the orders page, I imagined this one to use pagination, so here&#39;s the query:</p><pre>query MyQuery {<br>  accountsConnection(orderBy: id_ASC, first: 20) {<br>    edges {<br>      node {<br>        id<br>        latestOrder<br>        orderType<br>      }<br>      cursor<br>    }<br>    pageInfo {<br>      hasNextPage<br>      hasPreviousPage<br>      endCursor<br>      startCursor<br>    }<br>    totalCount<br>  }<br>}</pre><p>It&#39;s worth noting that the columns could also be made interactive and they could have an effect on the orderBy of this query, whose value could be turned into a variable.</p><p>The customers page code <a href="https://github.com/RaekwonIII/subsquid-defi-dashboard/blob/main/src/pages/customers.tsx">is available here</a>.</p><h4>Testing out the App</h4><p>One of the best parts about React development is that the server launched with npm run dev updates every time you save, thanks to React&#39;s hot reloading. So if you had your browser open on <a href="http://localhost:3000/">http:localhost:3000</a> you should have seen it change while editing the code. Or, you could just open it now.</p><p>At any rate, this is how the home page should look like by now:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RNGqvOBiRp7ZKimF8Duktw.png" /><figcaption>The DeFi dashboard using Moonwell&#39;s on-chain data, powered by Subsquid</figcaption></figure><h3>Conclusions</h3><p>I really enjoyed creating this dashboard. Even though it is not the most sophisticated piece of software I have written, by far, I like the end result. Furthermore, the real focus of this article is to showcase how Subsquid can be integrated in a dashboard such as this one.</p><p>I wanted to provide a mini-guide that would respond to the use case:</p><blockquote>&quot;I need to create a dashboard, that shows key data about a DeFi protocol, but this in formation is stored on-chain.&quot;</blockquote><p>So in this article I have (hopefully successfully) shown how to use Subsquid SDK to source such information, and process it in order to make it easier to consume by a frontend.</p><p>Adding an indexing middleware in a DApp is, in my opinion, a very good way to future-proof it, making sure that the user experience is not degraded by the RPC bottleneck. And sometimes it&#39;s just simply not feasible to directly use data from a smart contract in your frontend.</p><p>At Subsquid is continuously developing, so head over to <a href="https://subsquid.io/">the website</a>, and to <a href="https://docs.subsquid.io/">the docs</a> to know more, and follow Subsquid on social media to stay up to date.</p><h4>What&#39;s next?</h4><p>I have mentioned in the article that even while I was developing the project, I came up with ideas on how it could be improved (like pagination). So here&#39;s a couple of suggestions, if you want to use this as your playground to hone your skills, or simply, like me, you can&#39;t stand having an &quot;incomplete&quot; project in your hands.</p><ul><li>The number of API requests should be reduced. In my opinion, the GraphQL queries could be performed by the page, and the data could be passed down to the components in a page. This will avoid multiple single queries on page load (I think the home page has 3 or 4…)</li><li>I&#39;d like to make the line chart on the home page &quot;draggable&quot;, so that it can start more zoomed-in on the last month for example, but I can move the canvas back to explore past time periods.</li><li>I had set up the queries in the Orders and Customers pages to be paginated, because I eventually want(ed) to add a &quot;next&quot; and &quot;previous&quot; buttons at the bottom of the page, for example, to load different pages of the corresponding data.</li><li>In a similar fashion, I would like to change the columns in the Orders and Customers pages to be interactive, so that clicking on one, would change the orderBy value of the query.</li><li>A cool idea would be to add Web3 integration, so that users could connect to the page with their wallet, and it would remove the hardcoded &quot;Welcome back&quot; message, and display your wallet address in the top right corner, instead.</li></ul><p>This last feature, especially could lead to other interesting ideas, such as being able to show user-specific data, once their wallet is known.</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, <a href="https://www.subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad3ce1e9fc73" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/build-your-first-defi-dashboard-ad3ce1e9fc73">Build your first DeFi dashboard</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Speed up NFT indexing with Subsquid]]></title>
            <link>https://medium.com/subsquid/speed-up-nft-indexing-with-subsquid-d832aa323aef?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/d832aa323aef</guid>
            <category><![CDATA[nft]]></category>
            <category><![CDATA[blockchain-development]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ethereum]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Fri, 31 Mar 2023 07:59:35 GMT</pubDate>
            <atom:updated>2023-03-31T07:59:35.129Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XwO-xyYIiAlq4D9WKGE8kA.png" /></figure><h3>Summary</h3><p>This series of articles is aimed at providing examples and explanation for some of the aspects of features of Subsquid.</p><p>This instalment wants to showcase two things, only one of which is actually inherently part of Subsquid SDK. The second one is still something that the SDK makes possible and easier to achieve. Furthermore, it&#39;s a possibility that many developers might not know, so it&#39;s worth mentioning.</p><p>Although the title of this article specifically talks about NFTs, the subjects here treated can actually be translated to any indexing project where it&#39;s necessary to repeatedly invoke a smart contract function to access its state.</p><p>The topics covered by this article are <strong>Server Extensions</strong> (through custom resolvers) and <strong>Multicall contract</strong><em>.</em></p><p>The project discussed in this article is hosted on a GitHub repository:</p><p><a href="https://github.com/RaekwonIII/bored-ape-yacht-club-indexing">GitHub - RaekwonIII/bored-ape-yacht-club-indexing: Indexing Bored Ape Yacht Club ERC-721 Non Fungible Tokens transfers on Ethereal network with Subsquid SDK</a></p><p>The repository is also configured, so you can run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/bored-ape-yacht-club-indexing.git">Gitpod</a>:</p><figure><a href="https://gitpod.io/#https://github.com/RaekwonIII/bored-ape-yacht-club-indexing.git"><img alt="Open in Gitpod" src="https://cdn-images-1.medium.com/max/160/1*sTh7ly7sGcNYt9m4jnh0Mw.png" /></a></figure><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FkZlHmNGX6t4%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkZlHmNGX6t4&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FkZlHmNGX6t4%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/42a7dad4319a37d61e64efe337e0a05d/href">https://medium.com/media/42a7dad4319a37d61e64efe337e0a05d/href</a></iframe><p>As the series of articles on use cases of Subsquid framework grows, I want to add more and more examples and mention more and more features, including some that the average Subsquid developer might not be aware of.</p><p>This time it&#39;s the turn of Server Extensions. Subsquid documentation <a href="https://docs.subsquid.io/graphql-api/custom-resolvers/">has an extensive documentation page detailing this functionality</a>, so this page is mostly aimed at providing a practical example.</p><p>On top of this, because it&#39;s better to have more examples than less, I am taking this project as a chance to show how to use external API calls to grab the token metadata from the tokenURI, similarly to how it was done <a href="https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db">on the ENS article</a>.</p><p>The Multicall contract is also a very interesting talking point which should become a staples for every developer that needs to repeatedly perform calls to smart contract functions to access the contract&#39;s status. Aggregating these transactions into a single call will contribute to efficiency of your indexer and maintain a good overall performance.</p><h3>Project setup</h3><p>If you haven’t already, you need to install Subsquid CLI first:</p><pre>npm i -g @subsquid/cli@latest</pre><p>As with most squid ETLs, it all starts with creating a project from a template. In a terminal, launch the command:</p><pre>sqd init bored-ape-yacht-club -t evm</pre><p>Here, bored-ape-yacht-club is the name we are going to give to our project, you can change it to anything you like. And -t evm specifies what template should be used and makes sure the setup will create a project from the EVM-indexing template.</p><p>Let’s also install the dependencies, by launching this command:</p><pre>npm i</pre><h4>Contract ABI</h4><p>The next step was importing the ABI into the project and generate bindings for the ABI. I have found the <a href="https://etherscan.io/token/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d">BAYC token contract on Ethereum block explorer</a> and noted the address.</p><p>I then generated TypeScript boilerplate code to interact with it, thanks to Subsquid&#39;s CLI command:</p><pre>sqd typegen 0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d</pre><p>If you take a look at the commands.json file, specifically at the typegen shortcut defined in it, you&#39;ll be able to see it&#39;s adding the --multicall option.</p><p>This means the command will also generate a file named multicall.ts containing specific bindings for the Multicall contract I have discussed in the introduction. This will be useful in the indexing logic.</p><h4>Schema</h4><p>For this project, I didn&#39;t steer much away from <a href="https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db">other similar NFT indexing projects I have shown in previous articles</a>. As usual, I am interest in Transfers, in theTokens, as well as the Owner of each token, and I&#39;m also capturing Contract information.</p><p>I did, however, add the imageUrl field to the Token model, because this is part of what I wanted to achieve by using the Multicall contract. Here&#39;s the schema:</p><pre>type Token @entity {<br>  id: ID!<br>  owner: Owner<br>  uri: String<br>  imageUrl: String<br>  transfers: [Transfer!]! @derivedFrom(field: &quot;token&quot;)<br>  contract: Contract<br>}<br><br>type Owner @entity {<br>  id: ID!<br>  ownedTokens: [Token!] @derivedFrom(field: &quot;owner&quot;)<br>}<br><br>type Contract @entity {<br>  id: ID!<br>  name: String! @index<br>  symbol: String!<br>  totalSupply: BigInt!<br>  tokens: [Token!]! @derivedFrom(field: &quot;contract&quot;)<br>}<br><br>type Transfer @entity {<br>  id: ID!<br>  token: Token!<br>  from: Owner<br>  to: Owner<br>  timestamp: DateTime! @index<br>  block: Int! @index<br>  transactionHash: String!<br>}</pre><p>Let&#39;s save this content in the schema.graphql file and update the TypeScript models, by running the command:</p><pre>sqd codegen</pre><h4>Data indexing</h4><p>For the data processing itself, I didn&#39;t want to create anything too complex, because it&#39;s not the focus of the project. Also, I am not going to provide the full code listing here, I&#39;d recommend instead to <a href="https://github.com/RaekwonIII/bored-ape-yacht-club-indexing/blob/main/src/processor.ts">take a look at the repository on GitHub</a>.</p><p>Instead, I&#39;m going to provide a list of functionalities that the logic accomplishes, and some code snippets to showcase how they are implemented:</p><ul><li>configure the indexer to extract data generated by the BAYC smart contract and filter for the event I am interested in: Transfer</li></ul><pre>const contractAddress = &quot;0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D&quot;.toLowerCase();<br><br>const multicallAddress = &quot;0x5ba1e12693dc8f9c48aad8770482f4739beed696&quot;.toLowerCase();<br><br>const processor = new EvmBatchProcessor()<br>.setDataSource({<br>  chain: process.env.RPC_ENDPOINT,<br>  archive: &quot;https://eth.archive.subsquid.io&quot;,<br>})<br>.addLog(contractAddress, {<br>  filter: [<br>    [<br>      events.Transfer.topic,<br>    ],<br>  ],<br>  data: {<br>    evmLog: {<br>      topics: true,<br>      data: true,<br>    },<br>    transaction: {<br>      hash: true,<br>    },<br>  },<br>});</pre><ul><li>Process the batch of events that Subsquid Archive provides to my processor at recurring intervals and verify the event kind, and use a separate function to extract, save data in a temporary data interface array, then use another function to process this data array and save information on the database</li></ul><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const baycDataArr: BAYCData[] = [];<br>  <br>  for (let c of ctx.blocks) {<br>    for (let i of c.items) {<br>      if (i.address === contractAddress &amp;&amp; i.kind === &quot;evmLog&quot;) {<br>        if (i.evmLog.topics[0] === events.Transfer.topic) {<br>          const baycData = handleTransfer({<br>            ...ctx,<br>            block: c.header,<br>            ...i,<br>          });<br>          baycDataArr.push(baycData);<br>        }<br>      }<br>    }<br>  }<br><br>  await saveBAYCData(<br>    {<br>      ...ctx,<br>      block: ctx.blocks[ctx.blocks.length - 1].header,<br>    },<br>    baycDataArr<br>  );<br>});<br><br>type BAYCData = {<br>  id: string;<br>  from: string;<br>  to: string;<br>  tokenId: bigint;<br>  timestamp: Date;<br>  block: number;<br>  transactionHash: string;<br>};</pre><ul><li>Here is the handleTransfer function, responsible for decoding the Transfer event and save data in a BAYCData interface.</li></ul><pre>function handleTransfer(<br>  ctx: LogHandlerContext&lt;<br>    Store,<br>    { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>  &gt;<br>): BAYCData {<br>  const { evmLog, block, transaction } = ctx;<br><br>  const { from, to, tokenId } = events.Transfer.decode(evmLog);<br><br>  const baycData: BAYCData = {<br>    id: `${transaction.hash}-${evmLog.address}-${tokenId.toBigInt()}-${<br>      evmLog.index<br>    }`,<br>    from,<br>    to,<br>    tokenId: tokenId.toBigInt(),<br>    timestamp: new Date(block.timestamp),<br>    block: block.height,<br>    transactionHash: transaction.hash,<br>  };<br>  return baycData;<br>}</pre><h4>Token URI via Multicall contract</h4><p>At this point, the transferred NFTs have been identified and the transfer information is stored in the baycDataArr array, but what is making sure this information is permanent, is the saveBAYCData function.</p><p>Its logic is acutally very similar to the one <a href="https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db">in the ENS tokens article</a>, so I am not going to repeat it. Furthermore, it&#39;s possible to use the <a href="https://github.com/RaekwonIII/bored-ape-yacht-club-indexing/blob/main/src/processor.ts#L147">code stored in the GitHub repository as a reference</a> (which I advise looking at).</p><p>I am going to instead focus on the parts that make this project worth talking about. Right at the very end of the function, after the loop over the baycDataArr array has ended, and before saving on the database, this code section below is where the Multicall contract is used to fetch the tokenURI, by aggregating multiple calls into a single one.</p><pre>const maxHeight = maxBy(baycDataArr, data =&gt; data.block)!.block;<br>const multicall = new Multicall(ctx, {height: maxHeight}, multicallAddress);<br><br>ctx.log.info(`Calling multicall for ${baycDataArr.length} tokens...`);<br><br>const results = await multicall.tryAggregate(functions.tokenURI, baycDataArr.map(data =&gt; [contractAddress, [BigNumber.from(data.tokenId)]] as [string, BigNumber[]]), 100);<br>results.forEach((res, i) =&gt; {<br>  let t = tokens.get(baycDataArr[i].tokenId.toString());<br>  if (t) {<br>    let uri = &#39;&#39;;<br>    if (res.success) {<br>      uri = &lt;string&gt;res.value;<br>    } else if (res.returnData) {<br>      uri = &lt;string&gt;functions.tokenURI.tryDecodeResult(res.returnData) || &#39;&#39;;<br>    }<br>    t.uri = uri;<br>    if (!tokenIdToImageUrl.has(t.id)) tokensWithNoImage.push(t.id);<br>  }<br>})<br><br>ctx.log.info(`Done`);</pre><p>It&#39;s worth noting that tokenIdToImageUrl is a Map defined at the very top of the file, which act as a &quot;cache&quot; of tokens we have already encountered and whose image we have already fetched.</p><p>Then, right after processing all results from the multicall contract, we can fetch the image url we need with this piece of code (note: api is an instance of the Axios API library, which is also instantiated at the top of the file):</p><pre>await Promise.all(tokensWithNoImage.map( async (id) =&gt; {<br>  const t = tokens.get(id);<br>  if (t &amp;&amp; t.uri) {<br>    try {<br>      const res = await api.get(t.uri);<br>      tokenIdToImageUrl.set(id, res.data.image);<br>      t.imageUrl = res.data.image;<br>    }<br>    catch (error) {<br>      console.log(error);<br>    }<br>  }<br>}))</pre><h4>Custom Resolver</h4><p>The data processing implemented so far made sure I saved all the Transfers, but what if I wanted to do some aggregations? Let&#39;s imagine I wanted to know the daily total transfer, how would I access this kind of data?</p><p>There is a common pattern offered by GraphQL servers, which might be less known by Subsquid developers. This is, to <a href="https://hasura.io/learn/graphql/hasura/custom-business-logic/2-remote-schemas/">write your own custom resolver</a> as a server extension, and specify custom queries.</p><p>I set on to create an index.ts file under the src/server-extension/resolvers and I built my own daily aggregations using simple SQL queries. Here is how to get the daily Transfers of BAYC NFTs:</p><pre>// src/server-extension/resolvers/index.ts<br>import &#39;reflect-metadata&#39;<br>import type { EntityManager } from &#39;typeorm&#39;<br>import { Field, ObjectType, Query, Resolver } from &#39;type-graphql&#39;<br>import { Transfer } from &#39;../../model&#39;<br><br>@ObjectType()<br>export class TransfersDayData {<br>    @Field(() =&gt; Date, { nullable: false })<br>    day!: Date<br><br>    @Field(() =&gt; Number, {nullable: false})<br>    count!: number<br><br>    constructor(props: Partial&lt;TransfersDayData&gt;) {<br>        Object.assign(this, props)<br>    }<br>}<br><br>@Resolver()<br>export class TransfersDayDataResolver {<br>    constructor(private tx: () =&gt; Promise&lt;EntityManager&gt;) {}<br><br>    @Query(()=&gt;[TransfersDayData])<br>    async getTransfersDayData(): Promise&lt;TransfersDayData[]&gt; {<br>        const manager = await this.tx()<br>        const repository = manager.getRepository(Transfer)<br><br>        const data: {<br>            day: string<br>            count: number<br>        }[] = await repository.query(`<br>            SELECT DATE(timestamp) AS day, COUNT(*) as count<br>            FROM transfer<br>            GROUP BY day<br>            ORDER BY day DESC<br>        `)<br>        return data.map(<br>            (i) =&gt; new TransfersDayData({<br>                day: new Date(i.day),<br>                count: i.count<br>            })<br>        )<br>    }<br>}</pre><h4>Start indexing</h4><p>If you have followed along and want to try this at home, at this point you should launch the database Docker container and generate a new migration file (this will also take care of removing any existing migration files, relating to the template project):</p><pre>sqd up<br>sqd migration:generate</pre><p>Launch the processor:</p><pre>sqd process</pre><p>And finally, in a separate terminal window, launch the GraphQL service:</p><pre>sqd serve</pre><p>Let’s open the browser at the address: <a href="http://localhost:4350/graphql">localhost:4350/graphql</a> and test our work so far.</p><p>Let’s find all the tokens that have a value in the uri field, and get some information about them, including the uri itself:</p><pre>query MyQuery {<br>    tokens(limit: 10, where: {uri_isNull: false}) {<br>        id<br>        uri<br>        owner {<br>            id<br>        {<br>    }<br>}</pre><p>But ultimately, thanks to the code section visiting this very same URL, we were able to save the image url, which we can query:</p><pre>query MyQuery {<br>    tokens(limit: 10, where {imageUrl_isNull: false}) {<br>        id<br>        imageUrl<br>        uri<br>    }<br>}</pre><p>Let’s copy the image from the field and paste the URL in another tab, and that’s our bored ape.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*NeG-iRJMAaZ9cwhj.png" /></figure><p>We can also use the server extension we built, and select the day and the count, and when we execute it, we will get a list of days and the related count of transfers for each day. And the information here was retrieved via a custom SQL query.</p><h3>Gitpod</h3><p>Another option, as mentioned at the start of the article, is to run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/bored-ape-yacht-club-indexing.git">Gitpod</a> by clicking on the related link on the <a href="https://github.com/RaekwonIII/bored-ape-yacht-club-indexing/blob/main/README.md">project’s README file</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nuql7MBQFRqWRzoJnhWrmQ.png" /></figure><p>Which will launch the entire project in a cloud-hosted execution environment for you.</p><h3>Conclusion</h3><p>In conclusion, with this article I wanted to prove to be able to</p><ul><li>fully index the Bored Ape Yacht Club NFT collection</li><li>fetch their metadata, including their token URL in a performant way, thanks to Multicall contract</li><li>use API calls to these endpoints to locally save the URLs of the images</li><li>create our own personalized query by extending the GraphQL server and implementing a custom resolver</li></ul><p>So, in this video, we learned that Subsquid SDK gives you the freedom to use external libraries and perform API calls to augment your on-chain data, that Multicall contract helps you keep the performance of your indexer in check, and that custom resolvers can allow developers to introduce custom manipulations and aggregations on top of indexed data.</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, <a href="https://www.subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d832aa323aef" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/speed-up-nft-indexing-with-subsquid-d832aa323aef">Speed up NFT indexing with Subsquid</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Advancing dApp development with Hardhat Indexing: A Game-Changer for Ethereum Devs]]></title>
            <link>https://medium.com/subsquid/advancing-dapp-development-with-hardhat-indexing-a-game-changer-for-ethereum-devs-6106e0196a5?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/6106e0196a5</guid>
            <category><![CDATA[ethereum]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[blockchain-development]]></category>
            <category><![CDATA[smart-contracts]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Fri, 24 Mar 2023 08:50:12 GMT</pubDate>
            <atom:updated>2023-03-24T08:50:12.287Z</atom:updated>
            <content:encoded><![CDATA[<h4>With Subsquid</h4><h3>Summary</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rtuuOjZRnV-r3-qvus3R2A.png" /></figure><p>Blockchain data indexing helps dApp developers to build better, more performant decentralized applications, ultimately improving the user experience.</p><p>One of the most important ways they do this is by decoupling of smart contract development from frontend and, when needed, backend development.</p><p>But traditionally, there has been a lack of solutions to help local development in this sense, <strong>no one thought about indexing a local EVM node…</strong></p><blockquote>But Subsquid did think of it</blockquote><p>And this article explains how.</p><p>Having an indexing middleware from the start of a project, when no contracts have been deployed on chain yet, means developers can build the smart contract locally (just as they do now), and index the local node as well. This enables early development of frontend as well, with reduced coupling to the smart contract, or its blockchain.</p><p>A smoother, better development experience leads to better dApps, which can be tested earlier and better, before deploying the smart contract on-chain (not even on testnet!).</p><h3>Introduction</h3><p>A little over a month ago, I have written <a href="https://medium.com/subsquid/boost-your-dapp-development-productivity-with-local-indexing-3936ba7a8cec">an article on how to index a development Ethereum node, using Subsquid and the Ganache/Truffle suite</a>.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FKDPPJYOfgfc%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DKDPPJYOfgfc&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FKDPPJYOfgfc%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/6fa3b29835f318bb4f80ac010a0588c1/href">https://medium.com/media/6fa3b29835f318bb4f80ac010a0588c1/href</a></iframe><p>I have been positively surprised by the appreciation of the developer community over this very useful feature offered by Subsquid&#39;s framework, and over the article itself.</p><p>The inspiration from the article came from a project I had built a little over a month earlier, <a href="https://medium.com/subsquid/how-to-build-a-performant-and-scalable-full-stack-nft-marketplace-63c12466b959">for which I wrote a different article</a>. In this initial project I was actually using a Hardhat local node for my smart contract development. I decided to switch to <a href="https://trufflesuite.com/">Truffle</a> for the final article, because it had a more intuitive demo contract, and because the command line tool made it easier to create manual transactions.</p><p>In a quest to provide developers with as many options possible, as much material possible and the maximum number of examples, I decided to revisit this material, and write a guide on how to index a Hardhat node.</p><p>The project discussed in this article is available at this repository:</p><p><a href="https://github.com/RaekwonIII/local-evm-indexing/">GitHub - RaekwonIII/local-evm-indexing</a></p><h3>The project</h3><p>If you haven’t already, you need to install Subsquid CLI first:</p><pre>npm i -g @subsquid/cli@latest</pre><p>In order to create a new squid project, simply open a terminal and launch the command:</p><pre>sqd init local-evm-indexing -t evm</pre><p>Where local-evm-indexing is the name we decided to give to our project, you can change it to anything you like. And -t evm specifies what template should be used and makes sure the setup will create a project from the EVM-indexing template.</p><h4>1. Create a Hardhat project</h4><p>In order to start working with Hardhat, it’s necessary to <a href="https://hardhat.org/hardhat-runner/docs/getting-started#installation">install the package</a>.</p><p>In a console window, from the squid project’s root folder, launch:</p><pre>npx hardhat</pre><blockquote><strong><em>Note:</em></strong><em> If you get an error referencing your </em><a href="http://readme.md/"><em>README.md</em></a><em> or other files, delete them and run </em><em>npx hardhat again. Alternatively, choose a different folder, but doing so will slightly diverge from this tutorial.</em></blockquote><p>And follow the guided initialization process. For the purpose of this tutorial, I have chosen a <strong>TypeScript project</strong> and left other choices as default.</p><h4>Configure Hardhat automining (optional)</h4><p>Then, open the hardhat.config.ts (the file extension will be .js if you selected a JavaScript file in the previous step), and add this in the HardhatUserConfig object (or the module.exports for a JavaScript file):</p><pre>const config: HardhatUserConfig = {<br>  defaultNetwork: &quot;hardhat&quot;,<br>  networks: {<br>    hardhat: {<br>      chainId: 1337,<br>      mining: {<br>        auto: false,<br>        interval: [4800, 5200]<br>      }<br>    },<br>  },<br>  solidity: &quot;0.8.17&quot;,<br>};</pre><p>The mining configuration will continuously mine blocks, even if no events or transactions are executed, it can be useful for debugging. You can read more about it on hardhat&#39;s official documentation.</p><p>Finally, launch the node with this command:</p><pre>npx hardhat node</pre><h4>2. Deploy the contract (and generate transactions)</h4><p>Hardhat ships with its own <a href="https://hardhat.org/hardhat-runner/docs/getting-started#compiling-your-contracts">demo contract, called </a><a href="https://hardhat.org/hardhat-runner/docs/getting-started#compiling-your-contracts">Lock</a>, but I found myself more at ease with the MetaCoin contract from Truffle, which part of the reason why the first article uses Ganache and Truffle as an example.</p><p>In order to better compare the setup between the two articles and the two options (Ganache and Hardhat), I am going to continue using the MetaCoin contract in this article.</p><p>My suggestion is to just copy the <a href="https://github.com/RaekwonIII/local-evm-indexing/tree/main/contracts">Solidity source code from the repository</a> and paste the two files in the contracts folder of the Hardhat project.</p><p>The next thing to do is change the deploy.ts (or .js, depending on how you initialized the project) script, located in the scripts folder. It needs to be edited in order to deploy our the ConvertLib library, as well as our ne. MetaCoin contract.</p><p>The <a href="https://github.com/NomicFoundation/hardhat/tree/main/packages/hardhat-ethers">hardhat-ethers plugin</a> adds the ethers.js library to the Hardhat node, making it possible to use its functionalities. It&#39;s possible to use it to deploy the contract, but since our goal is to create transactions, we can also add some code to do that as well.</p><p>It should look like this:</p><pre>import { ethers } from &quot;hardhat&quot;;<br><br>async function main() {<br><br>  const ConvertLib = await ethers.getContractFactory(&quot;ConvertLib&quot;);<br>  const convertLib = await ConvertLib.deploy();<br><br>  await convertLib.deployed();<br><br>  const MetaCoin = await ethers.getContractFactory(&quot;MetaCoin&quot;, {<br>    libraries: {<br>      ConvertLib: convertLib.address,<br>    },<br>  });<br>  const metaCoin = await MetaCoin.deploy();<br><br>  let instance = await metaCoin.deployed();<br><br>  console.log(`MetaCoin  deployed to ${metaCoin.address}`);<br>  let accounts = await ethers.getSigners()<br>  console.log(`There are a total of ${accounts.length} accounts`)<br>  await instance.sendCoin(accounts[1].address, 10, {from: accounts[0].address})<br>  console.log(`Sent 10 coin from ${accounts[0].address} to ${accounts[1].address}`)<br>  await instance.sendCoin(accounts[2].address, 10, {from: accounts[0].address})<br>  console.log(`Sent 10 coin from ${accounts[1].address} to ${accounts[2].address}`)<br>  await instance.sendCoin(accounts[3].address, 10, {from: accounts[0].address})<br>  console.log(`Sent 10 coin from ${accounts[1].address} to ${accounts[3].address}`)<br>  await instance.sendCoin(accounts[4].address, 10, {from: accounts[0].address})<br>  console.log(`Sent 10 coin from ${accounts[1].address} to ${accounts[3].address}`)<br>}<br><br>// We recommend this pattern to be able to use async/await everywhere<br>// and properly handle errors.<br>main().catch((error) =&gt; {<br>  console.error(error);<br>  process.exitCode = 1;<br>});</pre><p>Then open a different terminal (the first one is occupied by the running node), and run this command to deploy the contract:</p><pre>npx hardhat run scripts/deploy.ts --network localhost</pre><blockquote><strong>Note:</strong> please pay attention to the address of the deployedMetaCoin smart contract, as it will be used in the indexer logic section below, and it may vary from the one shown there.</blockquote><p>The next sections about the Subsquid Archive and the development of the Squid indexer, are actually <a href="https://medium.com/subsquid/boost-your-dapp-development-productivity-with-local-indexing-3936ba7a8cec">unchanged from the original article</a>, and this is not only on purpose, but it&#39;s actually the point:</p><blockquote>We have changed the tool running the local Ethereum node, but the indexing part stays the same</blockquote><blockquote>Hardhat offers the ability to <a href="https://hardhat.org/hardhat-runner/docs/guides/hardhat-console">interact with the running blockchain node through a console</a>. If you don’t want to generate transactions on deployment, you can open the console and run the necessary code later on.</blockquote><blockquote>Using it is as easy as running the command:</blockquote><blockquote><em>npx hardhat console</em></blockquote><h3>Archive Docker image</h3><p>In order to index the local Ethereum node, we need to use a pre-built Docker image of Subsquid’s Ethereum Archive. You can do it, by creating a docker-compose.archive.yml file (I have added one in the archive folder) and paste this:</p><pre>version: &quot;3&quot;<br><br>services:<br>  worker:<br>    image: subsquid/eth-archive-worker:latest<br>    environment:<br>      RUST_LOG: &quot;info&quot;<br>    ports:<br>      - 8080:8080<br>    command: [<br>            &quot;/eth/eth-archive-worker&quot;,<br>            &quot;--server-addr&quot;, &quot;0.0.0.0:8080&quot;,<br>            &quot;--db-path&quot;, &quot;/data/db&quot;,<br>            &quot;--data-path&quot;, &quot;/data/parquet/files&quot;,<br>            &quot;--request-timeout-secs&quot;, &quot;300&quot;,<br>            &quot;--connect-timeout-ms&quot;, &quot;1000&quot;,<br>            &quot;--block-batch-size&quot;, &quot;10&quot;,<br>            &quot;--http-req-concurrency&quot;, &quot;10&quot;,<br>            &quot;--best-block-offset&quot;, &quot;10&quot;,<br>            &quot;--rpc-urls&quot;, &quot;http://host.docker.internal:8545&quot;,<br>            &quot;--max-resp-body-size&quot;, &quot;30&quot;,<br>            &quot;--resp-time-limit&quot;, &quot;5000&quot;,<br>    ]<br>    # Uncomment this section on Linux machines.<br>    # The connection to local RPC node will not work otherwise.<br>    # extra_hosts:<br>    #   - &quot;host.docker.internal:host-gateway&quot;<br>    volumes:<br>      - database:/data/db<br>volumes:<br>  database:</pre><p>Now, let’s open the commands.json file, and add archive-up and archive-down to the list of commands. It&#39;s not mandatory, but these shortcuts are quicker to type, and easier to remember! 😅</p><pre>{<br>    &quot;$schema&quot;: &quot;https://cdn.subsquid.io/schemas/commands.json&quot;,<br>    &quot;commands&quot;: {<br>      // ...<br>      &quot;archive-up&quot;: {<br>        &quot;description&quot;: &quot;Start a local EVM Archive&quot;,<br>        &quot;cmd&quot;: [&quot;docker-compose&quot;, &quot;-f&quot;, &quot;archive/docker-compose.archive.yml&quot;, &quot;up&quot;, &quot;-d&quot;]<br>      },<br>      &quot;archive-down&quot;: {<br>        &quot;description&quot;: &quot;Stop a local EVM Archive&quot;,<br>        &quot;cmd&quot;: [&quot;docker-compose&quot;, &quot;-f&quot;, &quot;archive/docker-compose.archive.yml&quot;, &quot;down&quot;]<br>      },<br>      // ...<br>    }<br>  }</pre><p>Then start the service by opening the terminal and launching the command:</p><pre>sqd archive-up</pre><h3>Squid development</h3><p>It’s finally time to index the smart contract events we generated earlier, with Subsquid’s SDK. Using the <strong>contract’s ABI</strong> and contract <strong>address</strong> from previous steps.</p><p>If you want to know more about how to develop your squid ETL, please head over to the <a href="https://docs.subsquid.io/tutorials/create-an-ethereum-processing-squid/">dedicated tutorial</a> on Subsquid’s official documentation.</p><h4>Schema</h4><p>Since we are using MetaCoin contract as a reference, so we&#39;ll build a schema that makes sense with respect to the data it produces.</p><pre>type Transfer @entity {<br>  id: ID!<br>  block: Int!<br>  from: String! @index<br>  to: String! @index<br>  value: BigInt!<br>  txHash: String!<br>  timestamp: BigInt!<br>}</pre><p>And now let’s generate the TypeScript model classes for this schema. In a console window, from the project’s root folder, type this command:</p><pre>sqd codegen</pre><h4>ABI facade</h4><p>We also need to generate TypeScript code to interact with the contract, and for that, we need the contract’s ABI.</p><p>Similarly to Truffle, Hardhat generates that when it compiles the Solidity code, and puts the ABI under artifacts/contracts(while Truffle does the same under build/contracts/). So let&#39;s use Subsquid CLI tools and in a console window, from the project’s root folder, type this command:</p><pre>sqd typegen artifacts/contracts/MetaCoin.sol/MetaCoin.json</pre><h4>Processor configuration</h4><p>Let’s open the processor.ts file, and make sure we import the new Transfer model, as well as the ABI facade. From this, we are only going to import the events object.</p><pre>import { Transfer } from &#39;./model&#39;;<br>import { events } from &#39;./abi/MetaCoin&#39;;</pre><p>Because we want to index our local Archive, the data source of the processor class needs to be set to the local environment.</p><p>I have also created a constant for the address of the contract we deployed, and always remember to toLowerCase() your contract addresses, to avoid issues.</p><blockquote><strong>Note:</strong> This is where you need to use the contract address that has been logged by the deploy.ts script.</blockquote><p>And finally, we want to configure the processor to request data about the Transfer event:</p><pre>// ...<br>const contractAddress = &quot;0x9fE46736679d2D9a65F0992F2272dE9f3c7fa6e0&quot;.toLowerCase()<br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    chain: &quot;http://localhost:8545&quot;,<br>    archive: &quot;http://localhost:8080&quot;,<br>  })<br>  .addLog(contractAddress, {<br>    filter: [[events.Transfer.topic]],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  });<br>// ...</pre><p>You can also use environment variables, just like shown in <a href="https://medium.com/subsquid/how-to-build-a-performant-and-scalable-full-stack-nft-marketplace-63c12466b959">this complete end-to-end project example</a>. This will make sure the project will stay the same, and only configuration files containing environment variables will be changed, depending on the deployment.</p><h4>Process events</h4><p>We now need to change the logic of the processor.run() function, to apply our custom logic.</p><p>In order to process Transfer events, we need to &quot;unbundle&quot; all the events our processor receives from the Archive in a single batch, and decode each one of them.</p><p>The best practice in this scenario, is to use an array as a temporary data storage, and save all the processed transfers at the end, in another batch operation. This will yield better performance, as data storages such as the Postgres database we are using at the moment, are optimized for this. The code should look like this:</p><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const transfers: Transfer[] = []<br>  for (let c of ctx.blocks) {<br>    for (let i of c.items) {<br>      if (i.address === contractAddress &amp;&amp; i.kind === &quot;evmLog&quot;){<br>        if (i.evmLog.topics[0] === events.Transfer.topic) {<br>          const { _from, _to, _value } = events.Transfer.decode(i.evmLog)<br>          transfers.push(new Transfer({<br>            id: `${String(c.header.height).padStart(10, &#39;0&#39;)}-${i.transaction.hash.slice(3,8)}`,<br>            block: c.header.height,<br>            from: _from,<br>            to: _to,<br>            value: _value.toBigInt(),<br>            timestamp: BigInt(c.header.timestamp),<br>            txHash: i.transaction.hash<br>          }))<br>        }<br>      }<br>    }<br>   }<br>   await ctx.store.save(transfers)<br>});</pre><h4>Run and test</h4><p>It’s finally time to perform a full test. Let’s build the project first:</p><pre>sqd build</pre><p>Then, we need to launch the database Docker container, by running the command:</p><pre>sqd up</pre><p>Next, we need to remove the database migration file that comes with the template, and generate a new one, for our new database schema:</p><pre>sqd migration:clean<br>sqd migration:generate</pre><p>And we can finally launch the processor:</p><pre>sqd process</pre><p>In order to test our data, we can launch the GraphQL server as well. In a different console window, run:</p><pre>sqd serve</pre><p>And perform a test query:</p><pre>query MyQuery {<br>  transfers {<br>    block<br>    from<br>    id<br>    timestamp<br>    to<br>    txHash<br>    value<br>  }<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PZD6_viW_y2VFsTGHxDRqg.png" /></figure><h3>Conclusions</h3><p>What I wanted to show with this article is that thanks to Subsquid, it is possible to have an indexing middleware at the same time as developing smart contract(s) for a project.</p><p>Developers can build their dApp by building their smart contract locally, deploying it on a local node, and indexing that local node with a squid. This leads to a development and testing cycle with much tighter iterations.</p><p>Furthermore, since the indexing middleware is present from the start, fewer changes have to be made (just changing the chain, address, and the contract address itself).</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, Subsquid.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6106e0196a5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/advancing-dapp-development-with-hardhat-indexing-a-game-changer-for-ethereum-devs-6106e0196a5">Advancing dApp development with Hardhat Indexing: A Game-Changer for Ethereum Devs</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a fast and scalable Web3 API on Fantom blockchain]]></title>
            <link>https://medium.com/subsquid/building-a-fast-and-scalable-web3-api-on-fantom-blockchain-94c79933b55?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/94c79933b55</guid>
            <category><![CDATA[evm]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[fantom]]></category>
            <category><![CDATA[nft]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Fri, 17 Mar 2023 07:33:05 GMT</pubDate>
            <atom:updated>2023-03-17T10:30:18.648Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_BBb8GPwqWwe3Ojj0iBdxA.png" /></figure><h3>Summary</h3><p>I leveraged Subsquid’s indexing framework to build my very own API <strong>that is able to process data on Fantom network, and map every </strong><strong>tokenId to its corresponding </strong><a href="https://app.rave.domains/"><strong>Rave name</strong></a>, allowing for querying in the opposite direction (get Rave name from tokenId ).</p><p>The project discussed in this article is available at this repository:</p><p><a href="https://github.com/RaekwonIII/fantom-workshop">GitHub - RaekwonIII/fantom-workshop: Indexing Rave names ERC-721 Non Fungible Tokens transfers on Fantom network with Subsquid SDK</a></p><p>The repository is also configured, so you can run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop">Gitpod</a>:</p><figure><a href="https://gitpod.io/#https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop"><img alt="Open in Gitpod" src="https://cdn-images-1.medium.com/max/160/1*sTh7ly7sGcNYt9m4jnh0Mw.png" /></a></figure><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FDovMgPmy5x8%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DDovMgPmy5x8&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FDovMgPmy5x8%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/776d11f6e744584733539fa9e76a51ea/href">https://medium.com/media/776d11f6e744584733539fa9e76a51ea/href</a></iframe><p>In a quest to provide more educational material to devs, I have been creating more and more content, and most of it has been focussed on Ethereum project, or in some cases, Polygon.</p><p>But I wanted to broaden the spectrum and show that Subsquid does not just cover these blockchains, and that it is capable of indexing essentially all EVM blockchains at this point in time.</p><p>A while ago, I have created a sample project to celebrate Subsquid’s partnership announcement with Fantom, so it’s only reasonable I would use that as the topic of this article. While searching for a suitable subject, I came across <a href="https://rave.domains/">Rave names</a>, which is a domain names project building on Fantom, and the tokens generated by their smart contract were perfect for what I had in mind.</p><h3>The project</h3><p>Whenever someone is registering a domain name on the <a href="https://app.rave.domains/">Rave domains app</a>, they are minting a Non-Fungible Token from the <a href="https://ftmscan.com/token/0x14ffd1fa75491595c6fd22de8218738525892101">Rave Names collection</a>.</p><p>This token looks and behaves like a common ERC-721 token so those who have read through previous articles, will have some <em>déjà vu</em>, as there is a partial overlap. At the same time, there is some new information, and some information that is explained in greater detail and with more focussed examples.</p><h3>Let’s get building!</h3><p>A squid project has two basic requirements: First, you need to have <a href="https://nodejs.org/">Node.js</a> installed on your machine. We use version 16 and above for all projects.</p><p>Second, you need to have a <a href="https://www.docker.com/">Docker</a> daemon that is able to expose ports to your host machines.</p><p>Before starting, make sure to install Subsquid CLI to your system, as this will allow you to create new projects, and run useful commands:</p><pre>npm i -g @subsquid/cli@latest</pre><h4>Create new project</h4><p>Thanks to Subsquid&#39;s command line tools, creating a new project is as easy as running these commands (one, actually, the other two simply set the directory and install dependencies 😅):</p><pre>sqd init my-fantom-project -t evm<br>cd my-fantom-project<br>npm i</pre><h4>Modifying the schema</h4><p>Because the objective is to track token creation and transfer, we need to edit the schema.graphql file.</p><p>It&#39;s necessary to remove the existing schema, and add entities that capture the information we need, for example, a Token entity, as well as Owner, Contract, and Transfer. Here is the full schema, the choice of fields in each entity should be pretty intuitive:</p><pre>type Token @entity {<br>  id: ID!<br>  tokenId: BigInt<br>  owner: Owner @index<br>  metadata: String<br>  name: String<br>  transfers: [Transfer!]! @derivedFrom(field: &quot;token&quot;)<br>  contract: Contract<br>}<br> <br>type Owner @entity {<br>  id: ID!<br>  ownedTokens: [Token!] @derivedFrom(field: &quot;owner&quot;)<br>}<br> <br>type Contract @entity {<br>  id: ID!<br>  name: String!<br>  symbol: String!<br>  contractURI: String<br>  totalSupply: BigInt!<br>  mintedTokens: [Token!]! @derivedFrom(field: &quot;contract&quot;)<br>}<br> <br>type Transfer @entity {<br>  id: ID!<br>  token: Token!<br>  from: Owner @index<br>  to: Owner @index<br>  timestamp: BigInt! @index<br>  block: Int! @index<br>  transactionHash: String! @index<br>}</pre><p>Following these changes, we need to generate TypeScript models that corresponds to this schema, and will be useful to save data on the database. So let’s run the command:</p><pre>sqd codegen</pre><h4>Smart contract ABI code bindings</h4><p>The next step was importing the ABI into the project, so <a href="https://ftmscan.com/token/0x14ffd1fa75491595c6fd22de8218738525892101#code">I copied it from the Contract’s page on the block explorer</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*98L1c-f6I_Lpf_FHo7MLMQ.png" /></figure><p>I then pasted the content in a file named rave.json in my project&#39;s root folder, and generated TypeScript boilerplate code to interact with it, thanks to Subsquid&#39;s CLI command:</p><pre>sqd typegen rave.json --multicall</pre><p>The --multicall option will also generate bindings to a specific type of contract, called <a href="https://github.com/makerdao/multicall">Multicall, by MakerDAO</a>. This kind of contract allows to aggregate multiple calls to any contract&#39;s functions into one single call to the Multicall contract.</p><p>We&#39;ll be using this later on to fetch the token&#39;s metadata and batch requests for multiple tokens, instead of invoking the tokenURI function for each token.</p><p>This is a major performance improvement, as in most cases, direct communication to the blockchain through RPC nodes is the bottleneck, so minimizing the number of direct blockchain interactions is key.</p><h4>Processor configuration</h4><p>As mentioned, whenever a domain is registered on the Rave app, a new token is created, and to do so, users invoke the registerName function of the smart contract.</p><p>On the other hand, since each domain is a token, it can be traded, and whenever this happens, the smart contract emits a Transfer event (just like every other ERC-721 token).</p><p>Consequently, to track all the domains, and who owns them, it was sufficient to index and process this transaction and this event.</p><p>To do so, we need to change the processor.ts file and configure the EvmBatchProcessor class instance:</p><pre>const raveAddress = &quot;0x14ffd1fa75491595c6fd22de8218738525892101&quot;;<br>const multicallAddress = &quot;0xD98e3dBE5950Ca8Ce5a4b59630a5652110403E5c&quot;.toLowerCase();<br><br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    chain: process.env.RPC_ENDPOINT,<br>    archive: &#39;https://fantom.archive.subsquid.io&#39;,<br>  })<br>  .addTransaction(raveAddress,{<br>    sighash: functions.registerName.sighash,<br>    data: {<br>      transaction: {<br>        hash: true,<br>        from: true,<br>        input: true,<br>        to: true<br>      }<br>    } as const<br>  })<br>  .addLog(<br>    raveAddress, {<br>      filter: [[events.Transfer.topic]],<br>      data : {<br>        evmLog: {<br>          topics: true,<br>          data: true<br>        },<br>        transaction: {<br>          hash: true,<br>        }<br>      } as const<br>    }<br>  );</pre><p>The section that follows is where the processor is launched (the .run() function call), and where a callback function to process a batch of <em>items</em> (any event or transaction) is defined.</p><p>The first step is to loop through all of the items included in a single batch of data received from the Subsquid Archive for Fantom. For each item, it&#39;s necessary to recognize its type, as contract events and transactions have to be treated separately.</p><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const raveDataArray: RaveData[] = [];<br>  let tokenCounter = await ctx.store.count(Token);<br><br>  for (let b of ctx.blocks) {<br>    for (let i of b.items) {<br>      if (i.address !== raveAddress) continue;<br>      switch (i.kind) {<br>        case &#39;evmLog&#39;:<br>          if (i.evmLog.topics[0] === events.Transfer.topic) {<br>            raveDataArray.push(handleTransfer({<br>              ...ctx,<br>              block: b.header,<br>              ...i<br>            }))<br>          }<br>          break;<br>        case &#39;transaction&#39;:<br>          if (i.transaction.input.slice(0, 10) === functions.registerName.sighash) {<br>              raveDataArray.push(handleRegistered({<br>                ...ctx,<br>                block: b.header,<br>                ...i<br>              }, tokenCounter))<br>              // increment token counter<br>              tokenCounter += 1;<br>          }<br>          break;<br>      }<br>    }<br>  }<br><br>  await saveRaveData(<br>    {<br>      ...ctx,<br>      block: ctx.blocks[ctx.blocks.length - 1].header,<br>    },<br>    raveDataArray<br>  );<br>});</pre><p>Notice the two different handle* functions. They are not too different from each other, but it&#39;s still important to separate the logic.</p><p>To keep this article reasonably short, I will avoid posting the content of the entire file. I will instead describe the logic, and I recommend <a href="https://github.com/RaekwonIII/fantom-workshop/blob/main/src/processor.ts">looking at the repository</a> for reference, instead.</p><p>Data extracted from events and transactions is accumulated in an array, which is then processed at the end of the function, outside the loop. This is another performance improvement, as saving data in batch is much more efficient.</p><p>The schema defines a Contract entity, in which we are saving the collection name, the symbol and the total supply. Because this project is only indexing one contract, it&#39;s possible to handle that with a function named getOrCreateContractEntity, that uses a singleton instance to &quot;cache&quot; this information. We can make this function resilient to the processor being restarted, by adding a lookup to the database that will check if an instance of the contract has been saved before.</p><p>The last piece of logic in this file is the saveRaveData function. This receives the array of data extracted from each event and transaction contained in the batch and saves it on the correct database models ( Owner, Token, Transfer).</p><p>It is important to highlight that this is where getOrCreateContractEntity is used, to link each Token with their related Contract.</p><p>Also, at the very end, the Multicall contract is used to aggregate calls to the tokenURI function for each token encountered in the processed batch.</p><blockquote><strong>Note:</strong> the address of the Multicall contract was obtained from <a href="https://github.com/makerdao/multicall/pull/33/files">this pull Request</a> to the official MakerDAO repository. A <a href="https://github.com/makerdao/multicall/pull/33/files">second pull request for a second</a> deployed contract also exists.</blockquote><h3>Launch the indexer</h3><p>If you have followed along and want to try this at home, you should launch the database container first:</p><pre>sqd up</pre><p>Then generate a new migration file (this will also delete any previously existing migration files):</p><pre>sqd migration:generate</pre><p>Launch the processor:</p><pre>sqd process</pre><blockquote><strong>Note:</strong> at the moment of writing the article, the Multicall contract seems to be having issues: <em>tryAggregate</em> requests error due to lack of gas, although these should be gas-less transactions.</blockquote><blockquote>The project has been successfully tested in the past, and what is shown here has been proven to work. Independently from the (hopefully momentary) issue with Multicall contract on Fantom network, the concept illustrated in this article still stands.</blockquote><blockquote>Furthermore, it is also possible to obtain tokens&#39; metadata with multiple single calls to the <em>tokenURI</em> function of the Rave contract, <a href="https://github.com/RaekwonIII/fantom-workshop/blob/no-multicall/src/processor.ts#L262">as shown here</a>. This will be much slower and inefficient, but it will, however, work.</blockquote><p>And finally, in a separate terminal window, launch the GraphQL service:</p><pre>sqd serve</pre><p>We can, for example, test out our initial question: “what is the name of the first registered rave domain (i.e.: token #1 in the collection)?&quot; with this query:</p><pre>query MyQuery {<br>  tokens(where: {id_eq: &quot;1&quot;}) {<br>    tokenId<br>    metadata<br>    name<br>    owner {<br>      id<br>    }<br>  }<br>}</pre><h3>Conclusions</h3><p>At Subsquid, we are convinced that the mass adoption of Web3 will be driven by dApps with complex data models and high volumes of data written on- and off-chain. We want to make storage and indexing of this data inexpensive and incredibly fast.</p><p><strong>Archives make sure that different developers don’t have to extract the same information multiple times, avoiding duplicated efforts of data ingestion.</strong></p><p>At this point it&#39;s the vast majority agrees that the future is multi-chain, and this agrees with Subsquid&#39;s vision. That&#39;s why in the recent period we have deployed so many new Archives, ingesting data for many new networks.</p><p>What I tried to show in this article is how to use Subsquid to build your own indexer for Fantom network. This indexer is able to map every tokenId generated by the Rave names project to its corresponding Rave domain, allowing for querying in the opposite direction (get Rave name from the tokenId).</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, Subsquid.</p><p><strong>Subsquid socials:<br></strong><a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=94c79933b55" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/building-a-fast-and-scalable-web3-api-on-fantom-blockchain-94c79933b55">Building a fast and scalable Web3 API on Fantom blockchain</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Move your data analysis to the Cloud]]></title>
            <link>https://medium.com/subsquid/move-your-data-analysis-to-the-cloud-a67765b866d7?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/a67765b866d7</guid>
            <category><![CDATA[decentralization]]></category>
            <category><![CDATA[ethereum]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[polygon]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Fri, 03 Mar 2023 17:48:18 GMT</pubDate>
            <atom:updated>2023-03-03T17:48:18.414Z</atom:updated>
            <content:encoded><![CDATA[<h3>Decentralized storage for data analysis</h3><p>with Subsquid and Filebase</p><h3>Summary</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XPeVLTmgWw6fuFXX8TCkPA.png" /></figure><p>The last three published articles were focussed on blockchain data analysis, and in the latest one, <a href="https://medium.com/subsquid/how-to-scale-blockchain-data-science-for-large-datasets-b49d078c15eb">I discussed the use of parquet files</a>.</p><p>In this new installment of this short series, I want to demonstrate how the Subsquid SDK can also be used to easily save indexed data on the Cloud, using S3-compatible APIs, such as the ones offered by <a href="https://filebase.com/">Filebase</a>.</p><p><a href="https://filebase.com/">Filebase</a> is a solution for storing data on centralized networks, removing unnecessary complexity and using familiar tools.</p><p>The project discussed in this article is hosted on a GitHub repository:</p><p><a href="https://github.com/RaekwonIII/s3-store-example">GitHub - RaekwonIII/s3-store-example</a></p><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FoRSMD7JqYfQ&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DoRSMD7JqYfQ&amp;image=http%3A%2F%2Fi.ytimg.com%2Fvi%2FoRSMD7JqYfQ%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/131ae5dc189d0a2ec2a8cae9efbc105f/href">https://medium.com/media/131ae5dc189d0a2ec2a8cae9efbc105f/href</a></iframe><p>Many data analysts have grown accustomed to using Cloud storage for their data. After all, it’s so convenient, it’s reliable, and gives users the flexibility to expand, should they need to, and only pay for what they consume.</p><p>But the convenience of Centralized cloud solutions comes at the cost of putting your data at risk. These services are susceptible to data breaches, and they can be unilaterally shut down or interrupted at any moment in time.</p><p>Tools such as Filebase help with the global adoption of decentralized solutions, by bridging the gap between the new technologies being built, such as IPFS, and the current industry standards that developers are used to, like S3 buckets, and S3 APIs for saving files on remote locations.</p><p>They offer a storage solution that removes complexity and allows developers to use tools they already know, like S3-compatible APIs, on top of unlimited service and predictable pricing.</p><p>The motivation for this project is two-fold. The first is to demonstrate the ability of Subsquid SDK, and show that it covers the need to connect to remote storage solutions. Then, in yet another step towards decentralizing the industry, I wanted to showcase how it’s possible to keep your data safe and maintain full control over it, while still using familiar tools, thanks to Filebase.</p><h3>Project setup</h3><p>For this article, I actually chose to fork the project treated in <a href="https://medium.com/subsquid/how-you-can-supercharge-blockchain-data-analysis-with-local-indexing-88b713e7948e">this article, about indexing to local CSV files</a>. Everything described and developed in the article and in the project is valid and useful, and this article represents an extension to it, should anyone be needing to move their data to remote storage.</p><p>For this reason, I recommend either following the setup steps described in the article or forking the original repository, which we are going to use as the base, from this point forward.</p><h3>Remote storage library</h3><p>The first thing to do, at this point, is to install a library dedicated to connecting with S3-compatible APIs for remote storage. From the project’s root folder, open a terminal, then type and launch this command:</p><pre>npm i @subsquid/file-store-s3</pre><h3>Filebase account and credentials</h3><p>In order to use Filebase service, <a href="https://console.filebase.com/signup">it’s necessary to signup first</a>, but the good news is it’s really easy to get started with a free account, and only pay when the data needs become substantial (&gt;5GB, &gt;1000 pinned files).</p><p>Once signed up, make sure to head over to the console dashboard, select Buckets from the menu on the left, and create a new bucket. Take note of the bucket’s name, as you’ll be needing this later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IH0vm7G5QTfC_3nU.png" /></figure><p>Next, head over to the Access Keys section, which you need to access, to obtain the API credentials, labeled “Key”, and “Secret”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*c_YkxRS0zd3yAKV-.png" /></figure><p>Take note of their values, or have them handy for the next paragraph.</p><blockquote><strong><em>Note:</em></strong><em> the S3 API Endpoint is important to make sure you are connecting to Filebase, and not the default AWS S3 service, but it’s the same for everyone. Just know that it can be found on this screen, should you need it.</em></blockquote><h3>Database class configuration</h3><p>In the article dedicated to local CSV files, I showed how to create a db.ts file that configured a Database class, responsible for interfacing with the processor, and orchestrating writing to CSV files (abstracted as &quot;Tables&quot;).</p><p>It’s then necessary to open this file, located under the src folder, and change dest configuration parameter, to use a new remote location named S3Dest. Here&#39;s the content of the file, after the changes:</p><pre>import { Database, LocalDest, Store } from &quot;@subsquid/file-store&quot;;<br>import { S3Dest } from &quot;@subsquid/file-store-s3&quot;;<br>import { Transfers } from &quot;./tables&quot;;<br><br>export const db = new Database({<br>  tables: {<br>    Transfers,<br>  },<br>  dest:<br>    process.env.DEST === &quot;S3&quot;<br>      ? new S3Dest(process.env.FOLDER_NAME, process.env.BUCKET_NAME, {<br>          region: process.env.S3_REGION,<br>          endpoint: process.env.S3_ENDPOINT,<br>          credentials: {<br>            secretAccessKey: process.env.S3_SECRET_ACCESS_KEY || &quot;&quot;,<br>            accessKeyId: process.env.S3_ACCESS_KEY_ID || &quot;&quot;,<br>          },<br>        })<br>      : new LocalDest(&quot;./data&quot;),<br>  chunkSizeMb: 100,<br>  syncIntervalBlocks: 10000,<br>});<br>export type Store_ = typeof db extends Database&lt;infer R, any&gt;<br>  ? Store&lt;R&gt;<br>  : never;</pre><p>This code uses environment variables to configure the file storage to the Cloud. Here’s an example of how to change the .env file accordingly, and make sure the environment variables are set:</p><pre>DB_NAME=squid<br>DB_PORT=23798<br>GQL_PORT=4350<br># JSON-RPC node endpoint, both wss and https endpoints are accepted<br>RPC_ENDPOINT=&quot;https://rpc.ankr.com/eth&quot;<br>DEST=S3<br>S3_REGION=us-east-1<br>S3_ENDPOINT=https://s3.filebase.com<br>S3_SECRET_ACCESS_KEY=&lt;YOUR_SECRET_ACCESS_KEY&gt;<br>S3_ACCESS_KEY_ID=&lt;YOUR_KEY_ID&gt;<br>BUCKET_NAME=&lt;YOUR_BUCKET_NAME&gt;<br>FOLDER_NAME=&lt;YOUR_FOLDER_NAME&gt;</pre><blockquote><strong><em>Note:</em></strong><em> pay attention to the last four variables, where you’ll have to insert the values obtained in the previous paragraph.</em></blockquote><h3>Data indexing</h3><p>The indexing logic defined in processor.ts is unchanged from the local CSV files project, so please use <a href="https://medium.com/subsquid/how-you-can-supercharge-blockchain-data-analysis-with-local-indexing-88b713e7948e">the related article as a reference</a>, should you need it.</p><p>To launch the project, simply open a terminal and run this command:</p><pre>sqd process</pre><p>And in a few minutes, you should have a few sub-folders (whose names are the block ranges where the data is coming from) in the bucket and folder you have chosen, each containing a transfer.csv file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*doxy4W7j-OMZCXMV.png" /></figure><h3>Python data analysis</h3><p>The local CSV files article already had a simple Python script to showcase how to process the indexed data. For this article, the focus is to demonstrate how the same script can be modified to connect to remote storage, and still accomplish the same goal.</p><p>While doing this, I had a bit of fun refactoring the script to define multiple functions, so it may look quite different from the original, but it’s actually the same, the only real difference is included in the fetch_and_read_csv function, which connects to the S3 API, using AWS&#39; official Python SDK named boto3. Here&#39;s the content of the entire file:</p><pre>import pandas as pd<br>import matplotlib.pyplot as plt<br>import boto3<br>import os<br>from dotenv import load_dotenv<br><br>def get_client():<br>    load_dotenv(dotenv_path=&quot;../.env&quot;)<br>    S3_REGION = os.getenv(&#39;S3_REGION&#39;)<br>    S3_ENDPOINT = os.getenv(&#39;S3_ENDPOINT&#39;)<br>    S3_ACCESS_KEY_ID = os.getenv(&#39;S3_ACCESS_KEY_ID&#39;)<br>    S3_SECRET_ACCESS_KEY = os.getenv(&#39;S3_SECRET_ACCESS_KEY&#39;)<br>    client = boto3.client(&#39;s3&#39;,<br>                          region_name=S3_REGION,<br>                          endpoint_url=S3_ENDPOINT,<br>                          aws_access_key_id=S3_ACCESS_KEY_ID,<br>                          aws_secret_access_key=S3_SECRET_ACCESS_KEY,)<br>    return client<br>def get_csv_files(client):<br>    response = client.list_objects(<br>        Bucket=&#39;csv-store&#39;,<br>        Prefix=&#39;matic-data&#39;<br>    )<br>    csv_files = [obj.get(&quot;Key&quot;) for obj in response.get(<br>        &quot;Contents&quot;) if obj.get(&quot;Key&quot;).endswith(&quot;.csv&quot;)]<br>    return csv_files<br>def fetch_and_read_csv(client, key):<br>    res = client.get_object(Bucket=&quot;csv-store&quot;, Key=key)<br>    try:<br>        df = pd.read_csv(res.get(&quot;Body&quot;), index_col=None, header=None, names=[<br>            &quot;block number&quot;, &quot;timestamp&quot;, &quot;contract address&quot;, &quot;from&quot;, &quot;to&quot;, &quot;value&quot;])<br>    except Exception as ex:<br>        template = &quot;An exception of type {0} occurred. Arguments:\n{1!r}&quot;<br>        message = template.format(type(ex).__name__, ex.args)<br>        print(message)<br>    else:<br>        return df<br>def get_dataframe():<br>    client = get_client()<br>    csv_files = get_csv_files(client=client)<br>    if len(csv_files) == 0:<br>        print(&quot;No CSV file in remote bucket&quot;)<br>        return<br>    try:<br>        df = pd.concat(map(lambda csv_file: fetch_and_read_csv(<br>            client=client, key=csv_file), csv_files), axis=0, ignore_index=True)<br>    except Exception as ex:<br>        template = &quot;An exception of type {0} occurred. Arguments:\n{1!r}&quot;<br>        message = template.format(type(ex).__name__, ex.args)<br>        print(message)<br>    else:<br>        df[&#39;datetime&#39;] = pd.to_datetime(df[&#39;timestamp&#39;])<br>        df.sort_values([&quot;timestamp&quot;, &quot;from&quot;, &quot;value&quot;], inplace=True)<br>        return df<br>def main():<br>    df = get_dataframe()<br>    if df is None:<br>        return 1<br>    sumDf = df.groupby(df.datetime.dt.date)[&#39;value&#39;].sum()<br>    sumDf.plot(kind=&#39;bar&#39;,<br>               x=0,<br>               y=1,<br>               color=&#39;green&#39;,<br>               title=&#39;Daily Total transfers&#39;,<br>               )<br><br>    plt.show()<br>    return 0<br>if __name__ == &quot;__main__&quot;:<br>    exit(main())</pre><p>And the same image is generated, at the end:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*leWGbXurdFm596KK.png" /></figure><h3>Conclusions</h3><p>This project is the latest, in a short series on <strong>how to use </strong><a href="https://subsquid.io/"><strong>Subsquid’s indexing framework</strong></a><strong> for data analytics prototyping</strong>. This article focuses on showing how Subsquid allows its users to choose where they want to store their indexed data. Most importantly, in a quest to give power to our users, I wanted to advise using a decentralized storage solution, to effectively own your data, and rest assured your data analysis can be “unstoppable”.</p><p>I chose to re-use the project shown in the article about indexing to local CSV files because saving to S3 should be seen as simply a change of destination. I thought the best way to explain this was by editing an existing project, to show that, effectively, nothing changed, if not where the files were stored.</p><p>This meant I had to edit the Python script to import the remotely-stored CSVs into a <a href="http://pandas.pydata.org/">Pandas</a> DataFrame, and perform aggregation operations on this data, but this is such a common scenario, that there is a <a href="https://s3fs.readthedocs.io/en/latest/">file system abstraction library</a> to do this. I ended up not using it because I didn’t know how many folders would be created, or their names, a priori, so boto3 was a better option for this scenario.</p><p>This series of articles also wants to collect feedback on these new tools that Subsquid has made available for the developer community, so if you want to express an opinion, or have suggestions, feel free to reach out.</p><p>I want to see more projects like this one, <a href="https://linktr.ee/raekwoniii">follow me on social media</a>, and most importantly, <a href="https://subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a67765b866d7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/move-your-data-analysis-to-the-cloud-a67765b866d7">Move your data analysis to the Cloud</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to scale blockchain data science for large datasets]]></title>
            <link>https://medium.com/subsquid/how-to-scale-blockchain-data-science-for-large-datasets-b49d078c15eb?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/b49d078c15eb</guid>
            <category><![CDATA[uniswap]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[ethereum]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Wed, 22 Feb 2023 17:12:00 GMT</pubDate>
            <atom:updated>2023-02-22T17:12:00.827Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YEMPv9lyLkmUOnr742PJIQ.png" /></figure><h3>Summary</h3><p>In the <a href="https://link.medium.com/Q7jNtWjqzxb">last article I published, I discussed how to use Subsquid&#39;s SDK for data analysis, by saving processed data to local CSV files</a>, instead of a database.</p><p>For this article, I want to talk about the natural follow-up in a &quot;data analysis focussed&quot; mini-series, which is writing data to <a href="https://parquet.apache.org/">Parquet files</a>. The subject of this project is the Uniswap V3 smart contract, namely the data from its pools and positions held by investors.</p><p>The project discussed in this article is hosted on a GitHub repository:</p><p><a href="https://github.com/subsquid-labs/uniswap-squid/tree/parquet">GitHub - subsquid-labs/uniswap-squid at parquet</a></p><p>Just be careful to select the parquet branch.</p><blockquote><strong>Note</strong>: the project was actually developed by <a href="https://github.com/belopash">one of Subsquid&#39;s core developers</a>.</blockquote><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fm3kxCimRnQA%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dm3kxCimRnQA&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2Fm3kxCimRnQA%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/b8ac15e7d86a8ac38279810e6b092a79/href">https://medium.com/media/b8ac15e7d86a8ac38279810e6b092a79/href</a></iframe><p>CSV files are great for prototyping because they are easily inspectable by a human. They are super convenient, and, for smaller datasets, they are very portable. But this format has some drawbacks.</p><p>One of the most notorious ones is that in CSVs, the schema is not defined, and there are no data types included (only column names), but Subsquid’s library already manages this, by using strong typings offered by TypeScript.</p><p>Apart from this, CSV files start to become an issue for large datasets, because they tend to be a bit slow to import and parse and occupy significant space on disk.</p><p>When analyzing very large datasets, it&#39;s common to use distributed systems such as Hadoop, or <a href="https://spark.apache.org/">Spark</a>, and the workers in a cluster usually &quot;divide and conquer&quot; multiple files during the import process, but this cannot be done with CSV files.</p><p>This is where Parquet comes in handy:</p><blockquote>Apache Parquet is an Open Source, column-oriented data file format designed for efficient data storage and retrieval. It provides efficient data compression and encoding schemes with enhanced performance to handle complex data in bulk.</blockquote><p>The Parquet format is designed to make data accessible in chunks, allowing efficient read/write operations without processing the entire file. Finally, Parquet files automatically include schema information and handle data encoding.</p><p>The choice of the project&#39;s subject fell on Uniswap, because the protocol generates a very large amount of information, and we have been using it for a while as a benchmark for our performance tests. Ultimately, this helps to better show how to leverage a more performance-oriented format.</p><h3>Project setup</h3><p>As with most squid ETLs, it all starts with creating a project from a template. If you haven’t already, you need to <a href="https://docs.subsquid.io/squid-cli/">install Subsquid CLI</a>, then, in a terminal, launch the command:</p><pre>sqd init local-parquet-indexing -t evm</pre><p>Here, local-parquet-indexing is the name we are going to give to our project, you can change it to anything you like. And -t evm specifies what template should be used and makes sure the setup will create a project from the EVM-indexing template.</p><blockquote><strong><em>Note:</em></strong><em> The template actually has more than what we need for this project. In the repository used for this article, I went on and removed unnecessary packages, </em><a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/package.json"><em>so you can use this package.json</em></a><em>.</em></blockquote><blockquote><em>The </em><a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/commands.json"><em>list of available commands</em></a><em> has also been shortened, and </em><em>docker-compose.yml, </em><em>schema.graphql, and </em><em>squid.yaml files have been removed because there&#39;s no need for them for this project.</em></blockquote><p>Let&#39;s also install the dependencies, by launching this command:</p><pre>npm i</pre><h4>Contracts&#39; ABI</h4><p>Because the project is not as trivial as indexing the transfers of a single token, some sections will be more complex and most of the code will be explained, rather than directly listed.</p><p>This paragraph about Abstract Binary Interface is no exception since this project indexes data from three contracts, one of which is a factory and uses a Multicall contract as well.</p><p>For this project, you will need:</p><ul><li>The <a href="https://etherscan.io/address/0x1f98431c8ad98523631ae4a59f267346ea31f984#code">Uniswap V3 Factory</a> smart contract address and ABI</li><li>The ABI of <a href="https://etherscan.io/address/0x390a4d096ba2cc450e73b3113f562be949127ceb#code">any of the Uniswap V3 Pool</a> smart contracts deployed by the Factory</li><li>The <a href="https://etherscan.io/address/0xc36442b4a4522e871399cd717abdd847ab11fe88">Uniswap V3 Positions</a> smart contract address, and ABI</li><li>The address of any deployed Maker DAO&#39;s Multicall smart contract. Luckily, <a href="https://etherscan.io/address/0x5ba1e12693dc8f9c48aad8770482f4739beed696">Uniswap have their own</a></li><li>The ABI of an ERC-20 token (can be compiled from <a href="https://github.com/OpenZeppelin/openzeppelin-contracts/blob/master/contracts/token/ERC20/ERC20.sol">OpenZeppelin repository</a>, or downloaded, for example from <a href="https://gist.github.com/veox/8800debbf56e24718f9f483e1e40c35c">here</a>, or from <a href="https://github.com/subsquid-labs/uniswap-squid/tree/parquet/abi">the repository of this article&#39;s project</a>). Save it as abi/ERC20.json.</li></ul><blockquote><strong>Note:</strong> The project also uses <a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/abi/ERC20NameBytes.json">ERC20NameBytes</a> and <a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/abi/ERC20SymbolBytes.json">ERC20SymbolBytes</a> ABIs, which OpenZeppelin defines in <a href="https://github.com/OpenZeppelin/openzeppelin-contracts/blob/master/contracts/token/ERC20/extensions/IERC20Metadata.sol">IERC20Metadata.sol</a> which is included in <a href="https://github.com/OpenZeppelin/openzeppelin-contracts/blob/master/contracts/token/ERC20/ERC20.sol">ERC20.sol</a>, so if you use this, you might have to change some imports in mappings or utils.</blockquote><blockquote><strong>Note:</strong> for the pool ABI, you can inspect the internal transactions of one of the <a href="https://etherscan.io/tx/0xd9e0fc1d737479e940d9807a535c75a31a2c8458792e2c66e2cc33f19f88e546#internal">Create Pool transactions of the Factory</a>, and look at the destination address, which will be the pool itself.</blockquote><p>This means you&#39;ll need to generate TypeScript code for them, with multiple commands.</p><pre>sqd typegen 0x1f98431c8ad98523631ae4a59f267346ea31f984#factory<br>sqd typegen 0xc36442b4a4522e871399cd717abdd847ab11fe88#NonfungiblePositionManager<br>sqd typegen 0x390a4d096ba2cc450e73b3113f562be949127ceb#pool<br>sqd typegen --multicall<br>sqd typegen &lt;PATH_TO_ERC20_ABI&gt;.json</pre><p>This should have created a few files in the src/abi folder for you.</p><h4>&quot;Tables&quot;, &quot;Database&quot; and parquet files interface</h4><p>The @subsquid/file-store library defines the Table and Database classes. The first one refers to the schema and types used for the different files, while the latter is the configuration of the interface with the processor that coordinates writing to these files.</p><p>These terms were chosen, because they maintain the same interfaces as classes used to write to actual databases, and the change is transparent for the indexer’s processor.</p><p>There are two main differences with the CSV article and the first one is that for this project, you&#39;ll need a library that was developed specifically for parquet files, so let&#39;s install it:</p><pre>npm i @subsquid/file-store-parquet</pre><p>The other one is that this project is more involved and is, in fact, using ten different <em>tables</em> instead of one.</p><p>It&#39;s advised to define these tables in a separate file, the original project has them under src/tables.ts. The syntax is pretty much the same as the CSV article, but the Table, Column, and Types classes are imported from the @subsquid/file-store-parquet library, rather than the CSV one. A special note to the newCompression class, which, as the name says, configures the parquet file&#39;s compression.</p><p>Here&#39;s an example:</p><pre>import {Table, Column, Compression, Types} from &#39;@subsquid/file-store-parquet&#39;<br><br>export const Tokens = new Table(<br>    &#39;tokens.parquet&#39;,<br>    {<br>        blockNumber: Column(Types.Uint32()),<br>        timestamp: Column(Types.Timestamp()),<br>        contractAddress: Column(Types.String()),<br>        symbol: Column(Types.String()),<br>        name: Column(Types.String()),<br>        totalSupply: Column(Types.Uint64()),<br>        decimals: Column(Types.Uint16()),<br>    },<br>    {<br>        compression: Compression.ZSTD,<br>    }<br>)</pre><p>The rest of the <em>tables</em> definitions can be found <a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/src/tables.ts">here</a>.</p><p>Similarly, a src/db.ts file should be created to configure the Database class, which acts as the interface to the EvmBatchProcessor. By specifying the tables used, as well as the destination, and the size of the chunks in which the data is going to be split. Here are the contents of this file in full:</p><pre>import assert from &#39;assert&#39;<br>import {Database, LocalDest, Store} from &#39;@subsquid/file-store&#39;<br>import {<br>    FactoryPairCreated,<br>    PoolBurn,<br>    PoolInitialize,<br>    PoolMint,<br>    PoolSwap,<br>    PositionCollect,<br>    PositionDecreaseLiquidity,<br>    PositionIncreaseLiquidity,<br>    PositionTransfer,<br>    Tokens,<br>} from &#39;./tables&#39;<br>import {PoolsRegistry} from &#39;./utils&#39;<br>import {S3Dest} from &#39;@subsquid/file-store-s3&#39;<br><br>type Metadata = {<br>    height: number<br>    pools: string[]<br>}<br><br>export const db = new Database({<br>    tables: {<br>        Tokens,<br>        FactoryPairCreated,<br>        PoolInitialize,<br>        PoolMint,<br>        PoolBurn,<br>        PoolSwap,<br>        PositionCollect,<br>        PositionDecreaseLiquidity,<br>        PositionIncreaseLiquidity,<br>        PositionTransfer,<br>    },<br>    dest: process.env.DEST === &#39;S3&#39; ? new S3Dest(&#39;./uniswap&#39;, &#39;csv-store&#39;) : new LocalDest(&#39;./data&#39;),<br>    hooks: {<br>        async onConnect(dest) {<br>            if (await dest.exists(&#39;status.json&#39;)) {<br>                let {height, pools}: Metadata = await dest.readFile(&#39;status.json&#39;).then(JSON.parse)<br>                assert(Number.isSafeInteger(height))<br><br>                let registry = PoolsRegistry.getRegistry()<br>                for (let pool of pools) {<br>                    registry.add(pool)<br>                }<br><br>                return height<br>            } else {<br>                return -1<br>            }<br>        },<br>        async onFlush(dest, range) {<br>            let metadata: Metadata = {<br>                height: range.to,<br>                pools: PoolsRegistry.getRegistry().values(),<br>            }<br>            await dest.writeFile(&#39;status.json&#39;, JSON.stringify(metadata))<br>        },<br>    },<br>    chunkSizeMb: 50,<br>})<br><br>export type Store_ = typeof db extends Database&lt;infer R, any&gt; ? Store&lt;R&gt; : never</pre><blockquote><strong>Note: </strong>the chunkSizeMb configuration defines the size (in MB) of a parquet file before it&#39;s saved on disk, and a new one is created.</blockquote><h4>Data indexing</h4><p>The orchestration of the indexing logic is defined in the file named src/processor.ts:</p><pre>import {EvmBatchProcessor} from &#39;@subsquid/evm-processor&#39;<br>import * as positionsAbi from &#39;./abi/NonfungiblePositionManager&#39;<br>import * as factoryAbi from &#39;./abi/factory&#39;<br>import * as poolAbi from &#39;./abi/pool&#39;<br>import {db} from &#39;./db&#39;<br>import {processFactory} from &#39;./mappings/factory&#39;<br>import {processPools} from &#39;./mappings/pools&#39;<br>import {FACTORY_ADDRESS, POSITIONS_ADDRESS} from &#39;./utils/constants&#39;<br>import {processPositions} from &#39;./mappings/positions&#39;<br><br>let processor = new EvmBatchProcessor()<br>    .setBlockRange({from: 12369621})<br>    .setDataSource({<br>        archive: &#39;https://eth.archive.subsquid.io&#39;,<br>        chain: process.env.ETH_CHAIN_NODE,<br>    })<br>    .addLog(FACTORY_ADDRESS, {<br>        filter: [[factoryAbi.events.PoolCreated.topic]],<br>        data: {<br>            evmLog: {<br>                topics: true,<br>                data: true,<br>            },<br>        } as const,<br>    })<br>    .addLog([], {<br>        filter: [<br>            [<br>                poolAbi.events.Burn.topic,<br>                poolAbi.events.Mint.topic,<br>                poolAbi.events.Initialize.topic,<br>                poolAbi.events.Swap.topic,<br>            ],<br>        ],<br>        data: {<br>            evmLog: {<br>                topics: true,<br>                data: true,<br>            },<br>        } as const,<br>    })<br>    .addLog(POSITIONS_ADDRESS, {<br>        filter: [<br>            [<br>                positionsAbi.events.IncreaseLiquidity.topic,<br>                positionsAbi.events.DecreaseLiquidity.topic,<br>                positionsAbi.events.Collect.topic,<br>                positionsAbi.events.Transfer.topic,<br>            ],<br>        ],<br>        data: {<br>            evmLog: {<br>                topics: true,<br>                data: true,<br>            },<br>        } as const,<br>    })<br><br>processor.run(db, async (ctx) =&gt; {<br>    await processFactory(ctx)<br>    await processPools(ctx)<br>    await processPositions(ctx)<br>})</pre><p>Here&#39;s a brief explanation of the code above:</p><ul><li>The EvmBatchProcessor class is instantiated and parameters are set to connect to the Ethereum archive, as well as a blockchain node, requesting data after a certain block (make sure to add a node URL to ETH_CHAIN_NODE variable in the .env file)</li><li>It is also configured to request data for EVM logs generated by the Factory and Positions smart contracts, filtering for certain events (PoolCreated and IncreaseLiquidity, DecreaseLiquidity, Collect, Transfer, respectively)</li><li>EVM logs data for <strong>any address</strong>, that complies with the topics of the Pool smart contract&#39;s events: Burn, Mint, Initialize, Swap is also added to the configuration. This will guarantee that every time a new pool is created, the events it generates will be indexed.</li><li>Finally, the processor is launched, and data is processed in batches, by functions defined in src/mappings</li></ul><p>For a brief explanation of what<a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/src/mappings/factory.ts">processFactory</a>, <a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/src/mappings/pools.ts">processPools</a>, <a href="https://github.com/subsquid-labs/uniswap-squid/blob/parquet/src/mappings/positions.ts">processPositions</a> do, let&#39;s take the processPositions functions as an example:</p><ul><li>it &quot;unbundles&quot; the batch of items received</li><li>for each item found it checks that it belongs to one of the pool addresses generated by the factory</li><li>verifies that it&#39;s an EVM log</li><li>compares the EVM log topic against the topics of the Events of position NFT contract registered in processor.ts</li><li>uses the corresponding Event TypeScript class to decode the EVM log</li><li>writes the decoded information to the corresponding table (parquet file)</li></ul><p>To better understand how data is transformed, and how the other functions are defined as well, it&#39;s advised to <a href="https://github.com/subsquid-labs/uniswap-squid/tree/parquet/src/mappings">browse the repository</a> and inspect the code. Be sure to check the <a href="https://github.com/subsquid-labs/uniswap-squid/tree/parquet/src/utils">utils</a> folder as well, as there are some auxiliary files and functions used in the mapping logic.</p><h4>Start indexing</h4><p>When the logic is fully implemented, to launch the project and start indexing, open a terminal and run these two commands:</p><pre>sqd build<br>sqd process</pre><p>The indexer should be able to catch up with the Ethereum blockchain, and <strong>reach the chain&#39;s head in less than an hour</strong> (took ~45 minutes while testing for this article). Bear in mind that this may vary a lot, depending on the Ethereum node used (<a href="https://rpc.ankr.com/eth">Ankr public node</a> in this case) and on your hardware.</p><p>The process will generate a series of sub-folders in the data folder, labelled after the block ranges where the data is coming from, and in each one of these folders there should be one *.parquet file for each of the <em>tables</em> we defined.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*5qj5dP6A-SD57LKDa4nFxw.png" /></figure><h4>Data analysis</h4><p>Since parquet files were essentially created to respond to the demands of data scientists, they are &quot;native&quot; to Pandas, and as such, they are imported as easily as CSVs (if not more).</p><p>To provide a quick demonstration of how to handle the data generated by the indexer, <a href="https://gist.github.com/RaekwonIII/f92c8c4a3de8220752f1ac4c322e1311#file-analysis-py">here&#39;s a Python script</a> that aggregates Swap events from all the parquet files in the data folder into a single <a href="https://pandas.pydata.org/docs/reference/frame.html">DataFrame</a>, converts the timestamp field to a DateTime type, groups by day, and counts the number of swaps that occurred each day, then plots a bar chart, like this one:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8VLAL3nFJVRpiIwJnFib1A.png" /></figure><p>It&#39;s worth noting that the data traces back to May 2021, so the chart is quite squished, but it can be zoomed in using the toolbar at the bottom. Thanks to this, we can zoom in on the biggest spike in swaps, and find out that it occurred on November 9th, 2022, surrounded by two other days of very high numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jr8UdaVqKCrX3ia9wPF01g.png" /></figure><p>Funnily enough, the second highest peak, apart from the three days we just mentioned, occurred on Christmas day 2021:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rck-STXZPjMR18YIIicUQQ.png" /></figure><h3>Conclusions</h3><p>For this article, the focus shifted from CSV to <a href="https://parquet.apache.org/">Parquet files</a>. In contrast to CSV files, Parquet is a binary file format and can only be read with the proper tools. Python and Pandas are among these tools, and they are part of the average Data Analyst’s toolbox.</p><p>Each one of the two has its pros and cons, and they have different uses, so Subsquid wants to guarantee access to both, and let Data Analysts choose which format to use for their project.</p><p>To justify the use of Parquet, and test the performance, Uniswap V3 smart contracts were chosen as the subject of this project, indexing the data from its pools and positions held by investors.</p><p>To summarize: the project here described was able to index the entirety of Uniswap Pool events, across all the pools created by the Factory contract, as well as the Positions held by investors, in less than an hour (~45 minutes).</p><blockquote><strong>Note:</strong> Indexing time may vary, depending on the Ethereum node used (<a href="https://rpc.ankr.com/eth">Ankr public node</a> in this case) and on the hardware used.</blockquote><p>Subsquids has been carefully listening to users’ requests for the past year, and the voices of blockchain data analysts have been heard. This is what motivated the development of libraries that allow saving to local files, and the research put into saving to file format specific to data-oriented disciplines.</p><p>This is why we want to continue collecting feedback on this new tool that Subsquid has made available for the developer community, so if you want to express an opinion, or have suggestions, feel free to reach out.</p><p>I want to see more projects like this one, <a href="https://linktr.ee/raekwoniii">follow me on social media</a>, and most importantly, <a href="https://subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b49d078c15eb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/how-to-scale-blockchain-data-science-for-large-datasets-b49d078c15eb">How to scale blockchain data science for large datasets</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How you can upgrade blockchain data analysis]]></title>
            <link>https://medium.com/subsquid/how-you-can-supercharge-blockchain-data-analysis-with-local-indexing-88b713e7948e?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/88b713e7948e</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[token]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Fri, 17 Feb 2023 15:14:42 GMT</pubDate>
            <atom:updated>2023-03-02T14:44:00.355Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to power-up blockchain data analysis with file store indexing</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ofUw5oEHA3rZg9c2auRjKg.png" /></figure><h3>Summary</h3><p>In this article, I describe <strong>how to use </strong><a href="https://subsquid.io/"><strong>Subsquid’s indexing framework</strong></a><strong> for data analytics prototyping</strong>.</p><p>I have built an indexer that processes MATIC transactions on Ethereum mainnet, and dumps them on local CSV files.</p><p>I have then developed a simple Python script to demonstrate how to import these CSVs into a <a href="http://pandas.pydata.org/">Pandas</a> DataFrame, and perform aggregation operations on this data.</p><p>The project discussed in this article is available at this repository:</p><p><a href="https://github.com/RaekwonIII/local-csv-indexing"><strong>github.com/RaekwonIII/local-csv-indexing</strong></a></p><p>The repository is also configured, so you can run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/lens-analytics-squid.git">Gitpod</a>:</p><figure><a href="https://gitpod.io/#https://github.com/RaekwonIII/local-csv-indexing.git"><img alt="" src="https://cdn-images-1.medium.com/max/160/0*f7SxiLmKnGu1u-c4.png" /></a></figure><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fgxbx9kur18s&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dgxbx9kur18s&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=google" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/41402dc622f73a29afb55b4fe7eb34ad/href">https://medium.com/media/41402dc622f73a29afb55b4fe7eb34ad/href</a></iframe><p>Both Python and CSV files are the bread and butter of data analysts, especially in the early prototyping stages, when trying to validate a hypothesis or get some inspiration from raw data.</p><p>This is why it’s so important to have a blockchain data framework with the flexibility to allow data analysts to save data to CSV files, in order to play with this data with the tools they are already familiar with.</p><p>Luckily, Subsquid has <a href="https://www.npmjs.com/package/@subsquid/file-store">recently released a package</a> to do just that.</p><p>While working on a different project, I stumbled across the necessity to index transfers of an ERC20 token, and I thought this was the perfect opportunity to showcase this new feature of the SDK.</p><h3>Project setup</h3><p>I started by creating a new squid ETL (if you haven’t already, you need to <a href="https://docs.subsquid.io/squid-cli/">install Subsquid CLI first</a>), so in a terminal, I launched the command:</p><pre>sqd init local-csv-indexing -t evm</pre><p>Where local-csv-indexing is the name I decided to give to my project, you can change it to anything you like. And -t evm specifies what template should be used and makes sure the setup will create a project from the EVM-indexing template.</p><blockquote><strong>Note:</strong> The template actually has more than what we need for this project. In the repository used for this article, I went on and removed unnecessary packages, <a href="https://github.com/RaekwonIII/local-csv-indexing/blob/main/package.json">so you can use this package.json</a>.</blockquote><blockquote>I have also shortened the <a href="https://github.com/RaekwonIII/local-csv-indexing/blob/main/commands.json">list of available commands</a> and removed docker-compoes.yml, schema.graphql, and squid.yaml, because we will not be using them.</blockquote><h4>ERC-20 token ABI</h4><p>To be able to index the transfers of a token, I needed the token&#39;s address, as well as the smart contract&#39;s ABI (Abstract Binary Interface), which defines the contract&#39;s functions, the events, as well as their inputs and outputs, and their types.</p><p>Luckily, both of these can be found on block explorers like Etherscan, so I searched for MATIC&#39;s token on the website, and <a href="https://etherscan.io/token/0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0">landed on its page</a>.</p><p>The indexer needs the ABI, because that&#39;s how it can decode the contract&#39;s events or functions inputs (and outputs), and provide the information they carry. The SDK has a handy command to generate some boilerplate TypeScript code to achieve this.</p><pre>sqd typegen 0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0#matic</pre><p>To be fair, because the contract is deployed on Ethereum, and available through Etherscan API, the SDK is able to generate the necessary code with just the smart contract address.</p><p>For all other cases, the same command needs the path to a JSON file containing the smart contract&#39;s ABI, instead of its address.</p><p>This will generate some files under the src/abi folder, the most interesting of which is matic.ts.</p><h4>CSV, Tables and Databases</h4><p>I then installed the file-store-csv package, because my goal was to save my data on CSV files locally, and this package is not available by default:</p><pre>npm i @subsquid/file-store-csv</pre><blockquote>Equivalent packages for parquet files, and for uploading files using S3-compatible APIs are also available</blockquote><p>Next, I created a new file, which I named tables.ts, in case I wanted to expand to multiple files. This is where I provided a filename for the CSV files, and configured their data structure, the same way as if they were a database table (the class name is no coincidence):</p><pre>import {Table, Column, Types} from &#39;@subsquid/file-store-csv&#39;<br><br>export const Transfers = new Table(<br>    &#39;transfers.csv&#39;,<br>    {<br>        blockNumber: Column(Types.Integer()),<br>        timestamp: Column(Types.DateTime()),<br>        contractAddress: Column(Types.String()),<br>        from: Column(Types.String()),<br>        to: Column(Types.String()),<br>        amount: Column(Types.Decimal()),<br>    },<br>    {<br>        header: false,<br>    }<br>)</pre><p>I then created another file, which I named db.ts, to configure my data abstraction layer. This class is called Database, and it&#39;s on purpose because it provides a transparent interface to the processor, which is consistent with how the ETL usually saves data on PostgreSQL, or any future data storage.</p><pre>import {Database, LocalDest, Store} from &#39;@subsquid/file-store&#39;<br>import { Transfers } from &#39;./tables&#39;<br><br><br>export const db = new Database({<br>    tables: {<br>        Transfers,<br>    },<br>    dest: new LocalDest(&#39;./data&#39;),<br>    chunkSizeMb: 100,<br>    syncIntervalBlocks: 10000<br>})</pre><blockquote><strong>Note: </strong>the chunkSizeMb configuration defines the size (in MB) of a CSV file before it&#39;s saved on disk, and a new one is created.</blockquote><blockquote>Similarly, the syncIntervalBlocks configuration defines how many blocks have to be ingested, before creating a new CSV file, when the indexing process has reached the blockchain&#39;s head and is in sync with it.</blockquote><h4>Data indexing</h4><p>All the indexing logic is defined in the file named processor.ts, so I opened it, and edited the EvmBatchProcessor class configuration, to request data for the right smart contract, and the right EVM log:</p><pre>export const contractAddress =<br>  &quot;0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0&quot;.toLowerCase();<br><br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    archive: lookupArchive(&quot;eth-mainnet&quot;),<br>    chain: process.env.RPC_ENDPOINT,<br>  })<br>  .addLog(contractAddress, {<br>    filter: [[events.Transfer.topic]],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>    } as const,<br>  });</pre><blockquote><strong>Note</strong>: I used the RPC_ENDPOINT environment variable, make sure to edit the .env file and use a valid url of an Ethereum RPC node.</blockquote><p>I then defined the logic to process a batch of EVM log data, and save it in the CSV files.</p><p>I run a double loop to explore all the blocks in each batch, and the items in each block. In the innermost loop, I check that the items are actually EVM logs (this check favors TypeScript typings, as different kinds of items have access to different fields), that they have been generated by the right address and that they have the right topic signature.</p><p>I then decode the event and prepare an object with the right data structure, which I add to an array.</p><p>Outside both loops, I perform a single operation to save all the objects in the array I have prepared. This batch operation helps by increasing the overall performance.</p><p>Here is a short summary of the logic:</p><pre>processor.run(db, async (ctx) =&gt; {<br>  const transfers: Record[] = [];<br>  let decimals = 18;<br>  for (let block of ctx.blocks) {<br>    for (let item of block.items) {<br>      if (item.address !== contractAddress) continue;<br>      if (item.kind !== &quot;evmLog&quot;) continue;<br>      if (item.evmLog.topics[0] !== events.Transfer.topic) continue;<br><br>      const { from, to, value } = events.Transfer.decode(item.evmLog);<br>      transfers.push({<br>        blockNumber: block.header.height,<br>        timestamp: new Date(block.header.timestamp),<br>        contractAddress: item.address,<br>        from: from.toLowerCase(),<br>        to: to.toLowerCase(),<br>        amount: BigDecimal(value.toBigInt(), decimals).toNumber(),<br>      });<br>    }<br>  }<br><br>  ctx.store.Transfers.writeMany(transfers);<br>});</pre><p>But I suggest taking a look at the file on the GitHub repository, as there&#39;s some added logic to obtain the number of decimals for the token, using Subsquid&#39;s SDK to interact with the smart contract deployed on chain (this is where the RPC_ENDPOINT variable is being used).</p><p>To launch the project, simply open a terminal and run this command:</p><pre>sqd process</pre><p>And in a few minutes, you should have a few sub-folders (whose names are the block ranges where the data is coming from) under the data directory, each containing a transfer.csv file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/1*zSrl6Nv_Cp6hlbl2lb7URA.png" /></figure><h4>Data analysis with Python</h4><p>Python is a very intuitive and idiomatic language and as I mentioned at the beginning of the article, <a href="http://pandas.pydata.org/">Pandas</a> is pretty much the preferred choice for data analysts.</p><p>I didn&#39;t want to turn this article into a &quot;Pandas for data analytics&quot; tutorial, so I kept this part voluntarily short and simple.</p><blockquote>The purpose is simply to demonstrate that locally analyzing blockchain data, with Pandas and CSV files is possible, and briefly show how.</blockquote><p>This being said, I set on to ingest the data from the various CSVs into a single DataFrame, perform some data operations, like converting the string timestamp column into a datetime column, and sort the DataFrame (this was purely for some manual data inspection and exploration).</p><p>Then I wanted to play around with the data, with some basic aggregation. I thought it would be interesting to calculate the total amount of tokens transferred each day, for example.</p><p>Finally, I plotted this data, because visualizing it is always useful. Here is the code I used:</p><pre>import pandas as pd<br>from pathlib import Path<br>import matplotlib.pyplot as plt<br><br>df = pd.concat(map(lambda csv_file: pd.read_csv(csv_file, index_col=None, header=None, names=[<br>               &quot;block number&quot;, &quot;timestamp&quot;, &quot;contract address&quot;, &quot;from&quot;, &quot;to&quot;, &quot;value&quot;]), Path(r&#39;./&#39;).rglob(&#39;*.csv&#39;)), axis=0, ignore_index=True)<br><br>df[&#39;datetime&#39;] = pd.to_datetime(df[&#39;timestamp&#39;])<br><br>df.sort_values([&quot;timestamp&quot;, &quot;from&quot;, &quot;value&quot;], inplace=True)<br><br>sumDf = df.groupby(df.datetime.dt.date)[&#39;value&#39;].sum()<br><br>plt.figure().set_figwidth(20)<br>plt.figure().set_figheight(10)<br>sumDf.plot(kind = &#39;bar&#39;,<br>        x = 0,<br>        y = 1,<br>        color = &#39;green&#39;,<br>        title=&#39;Daily Total transfers&#39;,<br>        ylim=(0,2200000000.0)<br>        )<br><br># show the plot<br>plt.show()</pre><p>You can find this code in the <a href="https://github.com/RaekwonIII/local-csv-indexing/blob/main/data/analysis.py">analysis.py file in the GitHub repository</a>. I have also created a <a href="https://github.com/RaekwonIII/local-csv-indexing/blob/main/data/analysis-notebook.ipynb">Jupyter Notebook</a>, for those who prefer using it (the script&#39;s virtual environment and dependencies are managed <a href="https://python-poetry.org/">using Poetry</a>).</p><p>Here is an example of the result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*di99imu1_2ke-s4S-LO6sg.png" /></figure><h3>Conclusions</h3><p>The purpose of this project was to demonstrate <strong>how to use </strong><a href="https://subsquid.io/"><strong>Subsquid’s indexing framework</strong></a><strong> for data analytics prototyping</strong>.</p><p>I chose to index MATIC transactions on Ethereum mainnet, because it provides enough data to be significant, both in terms of performance and from a data analysis standpoint.</p><p>The indexer was able to ingest all this data, process it, and dump it on local CSV files in roughly 20 minutes.</p><p>I have then developed a simple Python script to demonstrate how to import these CSVs into a <a href="http://pandas.pydata.org/">Pandas</a> DataFrame, and perform aggregation operations on this data.</p><p>The final idea behind this kind of article was to collect feedback on this new tool that Subsquid has made available for the developer community, so if you want to express an opinion, or have suggestions, feel free to reach out.</p><p>I want to see more projects like this one, <a href="https://linktr.ee/raekwoniii">follow me on social media</a>, and most importantly, <a href="https://subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=88b713e7948e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/how-you-can-supercharge-blockchain-data-analysis-with-local-indexing-88b713e7948e">How you can upgrade blockchain data analysis</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to analyze Lens Protocol activity data with Subsquid]]></title>
            <link>https://medium.com/subsquid/how-to-analyze-lens-protocol-activity-data-with-subsquid-e1ee3b7b43fa?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1ee3b7b43fa</guid>
            <category><![CDATA[polygon]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[evm]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[social-media]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Wed, 08 Feb 2023 16:25:08 GMT</pubDate>
            <atom:updated>2023-02-08T16:25:08.242Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-0RvVNs7agpHzaLiSViD6Q.png" /></figure><h3>Summary</h3><p>I am showing how to quickly build <strong>an analytics web page for Lens Protocol</strong>, using <a href="https://subsquid.io/">Subsquid&#39;s indexing framework</a>, and React.</p><p>Lens is a very cool project, and social media are known for generating a lot of data, which is why having an SDK to create your own custom analytics for on-chain data is super important.</p><p>The project discussed in this article is available at this repository:</p><p><a href="https://github.com/RaekwonIII/lens-analytics-squid"><strong>github.com/RaekwonIII/lens-analytics-squid/</strong></a></p><p>The repository is also configured, so you can run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/lens-analytics-squid.git">Gitpod</a>:</p><figure><a href="https://gitpod.io/#https://github.com/RaekwonIII/lens-analytics-squid.git"><img alt="" src="https://cdn-images-1.medium.com/max/160/0*8cCt4eQ-Qet_FyA9.png" /></a></figure><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2F6xAEEwlNm4E&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D6xAEEwlNm4E&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=google" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/d6fd6cc4ff8af35f49b628e06a73718e/href">https://medium.com/media/d6fd6cc4ff8af35f49b628e06a73718e/href</a></iframe><p>Recently I have started publishing tutorials, <a href="https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db">using projects that I find interesting </a>as the subject. This walkthrough is following (social media pun <strong>intended 🤓</strong>) the trend, and I chose to look into <a href="https://www.lens.xyz/">Lens Protocol, a composable and decentralized social graph for building blockchain-based social media</a>.</p><p>This also came in handy, because Lens is deployed on Polygon, so this meant I could build a tutorial for this blockchain as well.</p><h3>Lens Protocol smart contract(s)</h3><p>Lens utilizes NFTs to define all interactions (Profiles, Posts, Comments, even Follows), so it was familiar territory. I started looking into the <a href="https://polygonscan.com/token/0xdb46d1dc155634fbc732f92e853b10b288ad5a1d">Lens Protocol Profiles token on a block explorer</a>, but the smart contract architecture get fairly complex. The related contract is actually a Proxy <a href="https://polygonscan.com/address/0x20f4d7ddee23029048c53b42dc73a02de19f1c9e">for this other contract</a> (which we are going to use later), which is called LensHub.</p><p>Luckily for me, the team <a href="https://github.com/lens-protocol/core">Open Sourced their contracts&#39; code in a GitHub repository</a>, and they also structured it nicely, so I went to study it.</p><p>I&#39;m not going to go into too much detail about this is structured (I&#39;m not going to risk getting called out for something incorrect by the team! 😆). What was important for me is to get a general overview of how the interactions worked, and for this, the <a href="https://github.com/lens-protocol/core/blob/main/contracts/libraries/InteractionLogic.sol">InteractionLogic.sol library</a> was very useful. I also wanted to be able to browse all the events generated by LensHub, which I have found in the <a href="https://github.com/lens-protocol/core/blob/main/contracts/libraries/Events.sol">Events.sol library</a>.</p><h3>The project</h3><p>The entire project is made of two main components:</p><ul><li>a React app, to show the analytics data</li><li>a squid ETL, with a GraphQL API: the data source for the React app</li></ul><h4>React app</h4><p>I started building my project, by creating a new React app, and installing the libraries I needed to render the charts I had in mind.</p><pre>npx create-react-app lens-analytics-squid - template typescript<br>cd lens-analytics-squid<br>npm install - save react-chartjs-2 chart.js</pre><p>For my demonstration, I wanted to build a few bar charts, showing daily statistics about profiles created, as well posts, and comments. Since I am no React expert (or frontend, for what matters), I took the <a href="https://react-chartjs-2.js.org/examples/vertical-bar-chart">react-chartjs-2 example as reference</a>.</p><p>I went on to change the App.tsx file to look like this:</p><pre>import React from &#39;react&#39;;<br>import {<br>  Chart as ChartJS,<br>  CategoryScale,<br>  LinearScale,<br>  BarElement,<br>  Title,<br>  Tooltip,<br>  Legend,<br>} from &#39;chart.js&#39;;<br>import { Bar } from &#39;react-chartjs-2&#39;;<br><br>ChartJS.register(<br>  CategoryScale,<br>  LinearScale,<br>  BarElement,<br>  Title,<br>  Tooltip,<br>  Legend<br>);<br><br>export const options = {<br>  responsive: true,<br>  plugins: {<br>    legend: {<br>      position: &#39;top&#39; as const,<br>    },<br>    title: {<br>      display: true,<br>      text: &#39;Chart.js Bar Chart&#39;,<br>    },<br>  },<br>};<br><br>const labels = [&#39;January&#39;, &#39;February&#39;, &#39;March&#39;, &#39;April&#39;, &#39;May&#39;, &#39;June&#39;, &#39;July&#39;];<br><br>export const data = {<br>  labels,<br>  datasets: [<br>    {<br>      label: &#39;Dataset 1&#39;,<br>      data: [1552, 1319, 613, 1400, 3452, 1233, 1959,],<br>      backgroundColor: &#39;rgba(255, 99, 132, 0.5)&#39;,<br>    },<br>    {<br>      label: &#39;Dataset 2&#39;,<br>      data: [76, 934, 1003, 128, 2384, 2757, 1986, ],<br>      backgroundColor: &#39;rgba(53, 162, 235, 0.5)&#39;,<br>    },<br>  ],<br>};<br><br>export function App() {<br>  return &lt;Bar options={options} data={data} /&gt;;<br>}</pre><p>Then started the server:</p><pre>npm start</pre><p>And here is my static bar chart:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SaKR3bZuxXgl2C7LCpVvNA.jpeg" /></figure><h4>Squid ETL</h4><p>Next, in order to make my chart dynamic and show real data, I created a new squid project. Because Polygon is an EVM chain, just like Ethereum, I chose the evm template.</p><blockquote><strong>Note:</strong> I chose to create this as a sub-folder of the React app, out of convenience, to have everything in one single repository, but this is not mandatory.</blockquote><pre>sqd init lens-squid -t evm</pre><p>Our project should look like this by now (except for .gitpod.yml, I just added this at the end, to allow everyone to launch the project on Gitpod):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/626/1*7eNFqTY0W60zTa5kgVCvIA.png" /></figure><h4>Schema</h4><p>I wanted to index Profiles, Posts (which Lens calls publications), Comments on the protocol. So I chose this schema, for my database and my GraphQL API:</p><pre>type Profile @entity {<br>  id: ID!<br>  address: String @index<br>  handle: String @index<br>  imageURI: String<br>  profileId: Int!<br>  posts: [Post!] @derivedFrom(field: &quot;creatorProfile&quot;)<br>  comments: [Comment!] @derivedFrom(field: &quot;profile&quot;)<br>  mirrors: [Mirror!] @derivedFrom(field: &quot;profile&quot;)<br>  timestamp: DateTime! @index<br>}<br><br>type Post @entity {<br>  id: ID!<br>  contentURI: String<br>  postId: Int!<br>  profileId: Int!<br>  mirrors: [Mirror!] @derivedFrom(field: &quot;originalPost&quot;)<br>  creatorProfile: Profile! @index<br>  timestamp: DateTime! @index<br>}<br><br>type Comment @entity {<br>  id: ID!<br>  contentURI: String!<br>  commentId: Int!<br>  profileId: Int!<br>  profile: Profile! @index<br>  originalPostId: Int!<br>  originalPost: Post!<br>  originalProfileId: Int!<br>  originalProfile: Profile! @index<br>  timestamp: DateTime! @index<br>}</pre><blockquote><strong>Note:</strong> I also wanted to experiment with the equivalent of &quot;re-post&quot; and &quot;like&quot; actions, which are called Mirror and Collect, respectively, even though, for the moment I am not using these.<br>If I end up adding these to the project, I&#39;ll update the article.</blockquote><p>Thanks to Subsquid&#39;s SDK, it&#39;s possible to automatically generate TypeScript models for these types, and use them in our code. It&#39;s only necessary to run this command in the squid&#39;s root folder:</p><pre>sqd codegen</pre><h4>ABI code-bindings</h4><p>The next step was importing the ABI into the project, so I copied it from the<a href="https://polygonscan.com/address/0x20f4d7ddee23029048c53b42dc73a02de19f1c9e#code"> Contract&#39;s page on the block explorer</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EmIEqxjBS-50qs-BurPBOw.png" /></figure><p>I then pasted the content in a file named lens-abi.json in my lens-squid folder, and generated TypeScript boilerplate code to interact with it, thanks to Subsquid&#39;s CLI command:</p><pre>sqd typegen lens-abi.json</pre><p>Here comes the tricky part: I wanted to do the same thing for the Events.sol library, but I did not have its ABI, so I cloned the repo, installed the dependencies, and used truffle to compile it for me.</p><p>You can avoid doing that, by directly downloading the Events.json file from the repository of this project.</p><p>If you really want to do it by yourself or are just simply curious, here are the commands I ran:</p><pre>git clone git@github.com:lens-protocol/core.git<br>npm i <br>npm i @openzeppelin/contracts<br>truffle compile</pre><p>If you can&#39;t do it, maybe you need to add a truffle.config.js file, with the configuration for the right Solidity compiler version:</p><pre>module.exports = {<br>  networks: {<br>    development: {<br>      host: &#39;127.0.0.1&#39;,<br>      port: 8545,<br>      network_id: &#39;1337&#39;,<br>    },<br>  },<br>  mocha: {<br>    // timeout: 100000<br>  },<br>  compilers: {<br>    solc: {<br>      version: &#39;0.8.10&#39;, <br>    },<br>  },<br>};</pre><p>With the Events.json ABI in my project, I was able to run this command, and generate TypeScript wrappers for <strong>all the events</strong> related to Lens Protocol;</p><pre>sqd typegen Events.json</pre><h4>Data processing</h4><p>For my data processing part, I tried to keep it relatively simple. Here is a list of functionalities that need to be accomplished, and some code snippets to showcase how they are implemented:</p><ul><li>configure the indexer to extract data generated by the LensHub smart contract and filter for the three events I am interested in: ProfileCreated, PostCreated, CommentCreated</li></ul><pre>const lensContractAddress =<br>  &quot;0xDb46d1Dc155634FbC732f92E853b10B288AD5a1d&quot;.toLowerCase();<br><br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    archive: lookupArchive(&quot;polygon&quot;),<br>  })<br>  .addLog(lensContractAddress, {<br>    filter: [<br>      [<br>        events.ProfileCreated.topic,<br>        events.PostCreated.topic,<br>        events.CommentCreated.topic,<br>      ],<br>    ],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  });</pre><ul><li>Process the batch of mixed events that Subsquid Archive provides to my processor at recurring intervals and sort out the different event kinds, decode them separately, and store the decoded data in temporary data interface arrays</li></ul><pre>type ProfileCreated = {<br>  handle: string;<br>  imageURI: string;<br>  profileId: number;<br>  to: string;<br>  timestamp: Date;<br>};<br><br>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const profiles: ProfileCreated[] = [];<br><br>  for (let c of ctx.blocks) {<br>    for (let i of c.items) {<br>      if (i.address === lensContractAddress &amp;&amp; i.kind === &quot;evmLog&quot;) {<br>        if (i.evmLog.topics[0] === events.ProfileCreated.topic) {<br>          const { handle, imageURI, profileId, to, timestamp } =<br>            events.ProfileCreated.decode(i.evmLog);<br>          profiles.push({<br>            handle,<br>            imageURI,<br>            profileId: profileId.toNumber(),<br>            to,<br>            timestamp: new Date(timestamp.toNumber() * 1000),<br>          });<br>        }<br>        // ... similar thing for PostCreated and CommentCreated events<br>      }<br>    }<br>  }<br>  // need to save the data here<br>});</pre><ul><li>Process the temporary data interface and do some cross-checking on the data (e.g.: check that when a Comment is being created, the Post it refers to already exists…)</li></ul><pre>async function saveLensData(<br>  ctx: BlockHandlerContext&lt;Store&gt;,<br>  profiles: ProfileCreated[],<br>  posts: PostCreated[],<br>  comments: CommentCreated[],<br>) {<br>  const profileIds: Set&lt;number&gt; = new Set();<br><br>  for (const profile of profiles) {<br>    profileIds.add(profile.profileId);<br>  }<br><br>  const profileModels: Map&lt;string, Profile&gt; = new Map(<br>    (await ctx.store.findBy(Profile, { id: In([...profileIds]) })).map(<br>      (profile) =&gt; [profile.profileId.toString(), profile]<br>    )<br>  );<br><br>  for (const profile of profiles) {<br>    const { handle, imageURI, profileId, timestamp, to } = profile;<br>    let profileModel = profileModels.get(profileId.toString());<br>    if (profileModel == null) {<br>      profileModel = new Profile({<br>        id: profileId.toString(),<br>        profileId,<br>        timestamp,<br>      });<br>      profileModel.address = to;<br>      profileModel.handle = handle;<br>      profileModel.imageURI = imageURI;<br>      profileModels.set(profileModel.profileId.toString(), profileModel);<br>    }<br>  }<br><br>  // same kind of processing for Post and Comment, only slightly more comples<br>  // because when processing a Post, we need to verify existence of Profile<br><br>  // similarly, for comments, the existence of related Post and Profile(s) <br>  // need to be verified as well<br><br>  await ctx.store.save([...profileModels.values()]);<br>}</pre><p>All the logic is defined in the processor.ts file, under lens-squid/src and you can inspect it <a href="https://github.com/RaekwonIII/lens-analytics-squid/blob/main/lens-squid/src/processor.ts">in the GitHub repository</a>.</p><h4>Data aggregation</h4><p>The data processing implemented so far made sure I saved all the Profiles, Posts, Comments, but what if I wanted to do some aggregations? I set my goal on creating daily totals for these type of activities, so I need this kind of data.</p><p>Luckily, it&#39;s a common pattern offered by GraphQL servers, to <a href="https://hasura.io/learn/graphql/hasura/custom-business-logic/2-remote-schemas/">write your own custom resolver</a> as a server extension, and specify custom queries. I set on to create an index.ts file under the src/server-extension/resolvers and I built my own daily aggregations using simple SQL queries.</p><p>Here is an example for the number of Profiles created on a daily on Lens Protocol</p><pre>//lens-squid/src/server-extension/resolvers/index.ts<br>import &#39;reflect-metadata&#39;<br>import type { EntityManager } from &#39;typeorm&#39;<br>import { Field, ObjectType, Query, Resolver } from &#39;type-graphql&#39;<br>import { Profile, Post, Comment } from &#39;../../model&#39;<br><br>@ObjectType()<br>export class ProfilesDayData {<br>    @Field(() =&gt; Date, { nullable: false })<br>    day!: Date<br><br>    @Field(() =&gt; Number, {nullable: false})<br>    count!: number<br><br>    constructor(props: Partial&lt;ProfilesDayData&gt;) {<br>        Object.assign(this, props)<br>    }<br>}<br><br>@Resolver()<br>export class ProfilesDayDataResolver {<br>    constructor(private tx: () =&gt; Promise&lt;EntityManager&gt;) {}<br><br>    @Query(()=&gt;[ProfilesDayData])<br>    async getProfilesDayData(): Promise&lt;ProfilesDayData[]&gt; {<br>        const manager = await this.tx()<br>        const repository = manager.getRepository(Profile)<br><br>        const data: {<br>            day: string<br>            count: number<br>        }[] = await repository.query(`<br>            SELECT DATE(timestamp) AS day, COUNT(*) as count<br>            FROM profile<br>            GROUP BY day<br>            ORDER BY day ASC<br>        `)<br>        return data.map(<br>            (i) =&gt; new ProfilesDayData({<br>                day: new Date(i.day),<br>                count: i.count<br>            })<br>        )<br>    }<br>}</pre><p>I simply repeated the same pattern for Post(s) and Comment(s), if you want to inspect the code, <a href="https://github.com/RaekwonIII/lens-analytics-squid/blob/main/lens-squid/src/server-extension/resolvers/index.ts">it&#39;s available on GitHub</a>.</p><p>Bear in mind, TypeScript being TypeScript, and needing types, I had to install a new library for this to work properly:</p><pre>npm i type-graphql</pre><h4>Dynamic data in React charts</h4><p>The final touch was going back to my React app, perform API requests to the squid GraphQL API, and use this data to feed the chart on the page.</p><ul><li>I used React&#39;s useState and useEffect to, respectively, set the bar chart data as my App&#39;s state, and to change it, by performing this GraphQL query.</li></ul><pre>query MyQuery {<br>    profilesConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    postsConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    commentsConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    getCommentDayData {<br>      count<br>      day<br>    }<br>    getPostDayData {<br>      count<br>      day<br>    }<br>    getProfilesDayData {<br>      count<br>      day<br>    }<br>  }</pre><ul><li>Made sure the App returns a placeholder title, if the state is not set (e.g.: the API fails, for some reason)</li><li>Because I have three different time series, and I wanted to test how all three of them looked in a single bar chart (spoiler: not very good 🙁), I had to write a bit of code to &quot;synchronize&quot; the different datasets.</li><li>Finally, I have changed App&#39;s return statement to display four different bar charts, with different datasets, instead of one.</li></ul><p>Here&#39;s the end result:</p><pre>// src/App.tsx<br>import React from &quot;react&quot;;<br>import {<br>  Chart as ChartJS,<br>  CategoryScale,<br>  LinearScale,<br>  BarElement,<br>  Title,<br>  Tooltip,<br>  Legend,<br>} from &quot;chart.js&quot;;<br>import { Bar } from &quot;react-chartjs-2&quot;;<br>import axios from &quot;axios&quot;;<br><br>ChartJS.register(<br>  CategoryScale,<br>  LinearScale,<br>  BarElement,<br>  Title,<br>  Tooltip,<br>  Legend<br>);<br><br>export const barChartOptions = {<br>  responsive: true,<br>  plugins: {<br>    legend: {<br>      position: &quot;top&quot; as const,<br>    },<br>    title: {<br>      display: false,<br>      text: &quot;Daily Analytics&quot;,<br>    },<br>  },<br>};<br><br>const headers = {<br>  &quot;content-type&quot;: &quot;application/json&quot;,<br>};<br>const requestBody = {<br>  query: `query MyQuery {<br>    profilesConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    postsConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    commentsConnection(orderBy: id_ASC) {<br>      totalCount<br>    }<br>    getCommentDayData {<br>      count<br>      day<br>    }<br>    getPostDayData {<br>      count<br>      day<br>    }<br>    getProfilesDayData {<br>      count<br>      day<br>    }<br>  }<br>  `,<br>};<br>const graphQLOptions = {<br>  method: &quot;POST&quot;,<br>  url: &quot;http://localhost:4350/graphql&quot;,<br>  headers,<br>  data: requestBody,<br>};<br><br>type DataInterface = {<br>  labels: string[];<br>  datasets: {<br>    label: string;<br>    data: number[];<br>    backgroundColor: string;<br>  }[];<br>};<br><br>export function App() {<br>  const [barChartData, setBarChartData] = React.useState&lt;DataInterface | null&gt;(<br>    null<br>  );<br>  React.useEffect(() =&gt; {<br>    try {<br>      axios(graphQLOptions).then((response) =&gt; {<br>        let labelsSet: Set&lt;string&gt; = new Set([<br>          ...response.data.data.getProfilesDayData.map((obj: any) =&gt; obj.day),<br>          ...response.data.data.getPostDayData.map((obj: any) =&gt; obj.day),<br>          ...response.data.data.getCommentDayData.map((obj: any) =&gt; obj.day),<br>        ]);<br><br>        let profileDataMap: Map&lt;string, number&gt; = new Map&lt;string, number&gt;();<br>        let postDataMap: Map&lt;string, number&gt; = new Map&lt;string, number&gt;();<br>        let commentDataMap: Map&lt;string, number&gt; = new Map&lt;string, number&gt;();<br>        for (const date of labelsSet) {<br>          profileDataMap.set(<br>            date,<br>            response.data.data.getProfilesDayData.find(<br>              (obj: any) =&gt; obj.day === date<br>            )?.count || 0<br>          );<br>          postDataMap.set(<br>            date,<br>            response.data.data.getPostDayData.find(<br>              (obj: any) =&gt; obj.day === date<br>            )?.count || 0<br>          );<br>          commentDataMap.set(<br>            date,<br>            response.data.data.getCommentDayData.find(<br>              (obj: any) =&gt; obj.day === date<br>            )?.count || 0<br>          );<br>        }<br>        console.log([...profileDataMap.values()]);<br>        setBarChartData({<br>          labels: [...labelsSet].map((dateString) =&gt;<br>            new Date(dateString).toLocaleDateString()<br>          ),<br>          datasets: [<br>            {<br>              label: &quot;Daily Profiles created&quot;,<br>              data: [...profileDataMap.values()],<br>              backgroundColor: &quot;rgba(255, 99, 132, 0.5)&quot;,<br>            },<br>            {<br>              label: &quot;Daily Posts created&quot;,<br>              data: [...postDataMap.values()],<br>              backgroundColor: &quot;rgba(53, 162, 235, 0.5)&quot;,<br>            },<br>            {<br>              label: &quot;Daily Comments created&quot;,<br>              data: [...commentDataMap.values()],<br>              backgroundColor: &quot;rgba(20, 255, 235, 0.5)&quot;,<br>            },<br>          ],<br>        });<br>      });<br>    } catch (err) {<br>      console.log(&quot;ERROR DURING AXIOS REQUEST&quot;, err);<br>    }<br>  }, []);<br><br>  if (!barChartData) return &lt;h1&gt;&quot;No data 🤷‍♂️!&quot;&lt;/h1&gt;;<br>  const profileBarChartData = {<br>    labels: barChartData.labels,<br>    datasets: [barChartData.datasets[0]],<br>  };<br>  const postsBarChartData = {<br>    labels: barChartData.labels,<br>    datasets: [barChartData.datasets[1]],<br>  };<br>  const commentsBarChartData = {<br>    labels: barChartData.labels,<br>    datasets: [barChartData.datasets[2]],<br>  };<br>  return (<br>    &lt;div&gt;<br>      &lt;Bar options={barChartOptions} data={profileBarChartData} /&gt;<br>      &lt;Bar options={barChartOptions} data={postsBarChartData} /&gt;<br>      &lt;Bar options={barChartOptions} data={commentsBarChartData} /&gt;<br>      &lt;Bar options={barChartOptions} data={barChartData} /&gt;<br>    &lt;/div&gt;<br>  );<br>}</pre><h3>Testing it out</h3><p>I had previously started the local server, and thanks to React&#39;s hot reloading, the changes in the paragraph above are almost instantly taken into account, so the browser page of http://localhost:3000 should now look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hczZ9t-2YYAL29UuH064ig.png" /></figure><p>And scrolling down, there should be a similar chart for Daily Posts and Daily Comments as well. The last bar chart on the page contains data for all three datasets</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eMvHbgl8Csn89Diekyznlw.png" /></figure><p>As I previously mentioned, I was expecting it to be more informative… 😅</p><blockquote><strong>Note: </strong>I have also added queries for totals for Profiles, Posts, Comments, even though I have not added them to the page yet.<br>Once again, when I&#39;ll eventually add these to the project, this part of the article will be edited…or you can come up with your own suggestions for this and create a Pull Request to the repository <em>😉</em></blockquote><h3>Conclusions</h3><p>Like I said in the introduction of the article, I think that Lens Protocol is a super interesting project, one I hope will prove to be successful.</p><p>Social media generate immense amounts of data and especially given the relative complexity of Lens&#39; smart contract, I found out pretty quickly that cookie-cutter data solutions might not be enough.</p><p>In my opinion, considering we are so early in the blockchain industry and complex applications like Lens are only getting started, this is why having an SDK to create your own custom analytics for on-chain data is super important.</p><p>Furthermore, given how easy it has been to create my own mini-project, especially for someone with a very limited experience with React, I wanted to let web3 developers know that this is a possibility, and most importantly, provide them with a walkthrough on how to quickly build <strong>an analytics web page for Lens Protocol</strong>, using <a href="https://subsquid.io/">Subsquid’s indexing framework</a>, and React.</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, <a href="https://www.subsquid.io/">Subsquid</a>.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1ee3b7b43fa" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/how-to-analyze-lens-protocol-activity-data-with-subsquid-e1ee3b7b43fa">How to analyze Lens Protocol activity data with Subsquid</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Boost your dApp development productivity with local indexing]]></title>
            <link>https://medium.com/subsquid/boost-your-dapp-development-productivity-with-local-indexing-3936ba7a8cec?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/3936ba7a8cec</guid>
            <category><![CDATA[smart-contracts]]></category>
            <category><![CDATA[ethereum]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[evm]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Thu, 02 Feb 2023 15:43:46 GMT</pubDate>
            <atom:updated>2023-03-14T16:21:27.745Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k4p8dKJGrs5xmg2E2MtMjw.png" /></figure><h3>Summary</h3><p>Blockchain data indexing is a performance enhancer for dApps, but it’s a pain to develop your application locally, because, somehow, <strong>no one thought about indexing a local EVM node…</strong> 😡😤</p><blockquote><em>…Until </em><a href="https://subsquid.io/"><em>Subsquid</em></a><em> decided to put an end to this.</em></blockquote><p>By working with an indexing middleware from the very early stages of a project, developers can build their dApp locally, deploying it on a local node, and index that local node with a squid.</p><p>This generates a much smoother developer experience, safer applications that can be thoroughly tested prior to the smart contract being deployed, and a much tighter development cycle, which leads to faster and more efficient deliveries.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FWuZ141ysPQ0&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DWuZ141ysPQ0&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=google" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/0d143f2b414d921c94fd2c11426103f7/href">https://medium.com/media/0d143f2b414d921c94fd2c11426103f7/href</a></iframe><p>The project discussed in this article is available at this repository:</p><p><a href="http://github.com/RaekwonIII/local-evm-indexing/"><strong>github.com/RaekwonIII/local-evm-indexing/</strong></a></p><p>For the development of the squid indexer, Ganache is going to be taken as a reference, because of these factors:</p><ul><li>the provided MetaContract is simple, yet &quot;interesting&quot; enough from an indexing point of view, to create an example</li><li>the truffle console allows to easily create transactions from the command line and emulate smart contract actions</li></ul><p>But the same actions can be done with Hardhat and Anvil (and very likely, there will be future articles about them).</p><h3>Introduction</h3><p>I have been dipping my toes in smart contract development for a little while now, and recently I have published an article detailing a full walkthrough on <a href="https://link.medium.com/Qz3cxPt20wb">how to improve the performance of a dApp (an NFT marketplace, in this case), using blockchain indexing</a>.</p><p>While developing this project, I really put myself in the shoes of developers that are using <a href="https://subsquid.io/">Subsquid</a>’s SDK on a daily basis, and I started to realize <strong>one of the most annoying facts that might hinder indexer’s adoption, dApp development, and ultimately, the user experience</strong>.</p><p>The blockchain industry has recognized pretty quickly, that smart contract development needs local development environment. And this has brought to the creation of tools like <a href="https://trufflesuite.com/">Truffle</a>, then <a href="https://hardhat.org/">Hardhat</a>, and (as I recently discovered) <a href="https://www.alchemy.com/dapps/foundry-anvil">Anvil</a>.</p><p>These tools allow developers to launch a minimal EVM node, to which developers can deploy, run, test the smart contract(s) they are developing. But a decentralized application is not just the smart contract itself: <strong>what about the frontend?</strong></p><h3>Developing dApp frontend locally</h3><p>I wanted to show that connecting the frontend of a dApp to an indexing middleware leads to better performance, compared to direct calls to the blockchain. But while developing this project, I needed to index my local Ethereum node, running on Hardhat.</p><p>This was a sudden realization for me, and a breakthrough-moment.</p><blockquote><em>dApp frontend local development is being treated as an after-thought</em></blockquote><p>Without the ability to index their local node, a typical developer journey looks pretty much like this:</p><ul><li>build the application with connections to smart contract to work locally</li><li>deploy on a testnet</li><li>change project to use indexing middleware</li><li>finish testing in this configuration</li><li>deploy to mainnet/production</li></ul><p>But this is not the best developer experience, nor the safest and most efficient way to work.</p><p>Luckily, Subsquid put an end to this, by publishing Docker images for their Ethereum Archive (<a href="https://hub.docker.com/r/subsquid/eth-archive-worker">which you can find on DockerHub</a>). By launching this container, an Archive service will be running on your machine, ingesting the local node’s blocks.</p><p>This allows developers to build a squid indexer, based on the local node data, exactly the same way they would do if the contract was deployed on testnet.</p><h3>Let’s build it!</h3><p>As mentioned at the start of the article, I chose to use Truffle suite and Ganache for an and-to-end project.</p><p>As previously explained, this choice was made out of convenience, but the same result could be achieved on Hardhat, using ethers library, <a href="https://hardhat.org/tutorial/testing-contracts">as it&#39;s shown in their documentation</a> (as a matter of fact, I might be writing a future article about this, stay tuned for that 😉).</p><p>The following section will cover the setup, a sample smart contract from Ganache, its deployment to the local node, and the generation of some transactions.</p><p>For this tutorial, I also chose to start with a squid project, and we are going to use it to manage Ganache configuration. This is not strictly necessary, it is only useful to work on one single folder and keep this tutorial as simple as possible.</p><h3>Squid project setup</h3><p>If you haven’t already, you need to install Subsquid CLI first:</p><pre>npm i -g @subsquid/cli@latest</pre><p>In order to create a new squid project, simply open a terminal and launch the command:</p><pre>sqd init local-evm-indexing -t evm</pre><p>Where local-evm-indexing is the name we decided to give to our project, you can change it to anything you like. And -t evm specifies what template should be used and makes sure the setup will create a project from the EVM-indexing template.</p><p>The next section contains instructions dedicated to one of the options for local Ethereum development: <a href="https://trufflesuite.com/docs/ganache/quickstart/#1-install-ganache">Ganache</a>. For other similar tools (such as Hardhat) the configuration should still be very similar.</p><h3>Ganache setup</h3><p>In order to start working with Ganache node, it’s necessary to Install <a href="https://trufflesuite.com/docs/truffle/how-to/install/">Truffle</a> and <a href="https://trufflesuite.com/docs/ganache/quickstart/#1-install-ganache">Ganache packages</a>.</p><h4>1. Truffle project, sample contract</h4><p>Let’s create a new truffle project, for example by downloading a sample contract. In a console window, from the project’s main folder, launch this command:</p><pre>truffle unbox metacoin</pre><p>To compile the contracts, launch this command in the same console window:</p><pre>truffle compile</pre><p>You should find the contract’s ABI at the location: build/contracts/MetaCoin.json, <strong>it will be useful for indexing</strong>.</p><h4>2. Create a workspace</h4><p>First of all, make sure the development configuration, in the networks section of truffle-config.js is uncommented, and has these values.</p><pre>development: {<br>  host: &quot;127.0.0.1&quot;,     // Localhost (default: none)<br>  port: 8545,            // Standard Ethereum port (default: none)<br>  network_id: &quot;1337&quot;,       // Any network (default: none)<br>},</pre><blockquote><strong><em>Note:</em></strong><em> It is not mandatory to change </em><em>port or </em><em>network_id, but I initially set them to the same value that Hardhat uses, out of convenience. It&#39;s important that they are the same as the Ganache server, though.</em></blockquote><blockquote><em>If you decide not to change them, please be aware of the values, and correctly apply these in the Archive image parameters.</em></blockquote><p>Launch the Ganache tool and select the New Workspace (Ethereum option).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MPqzSTqHtrow6kXS.png" /></figure><p>In the following window, provide a name for the workspace, and link the Truffle project we just created, by clicking on <strong>Add project</strong> button and selecting the truffle-config.js file in the project&#39;s root folder. Finally, select the Server tab at the top.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8ezdAnjTG4OTl0Lz.png" /></figure><p>In this window, change the server configuration to the exact values reported in this image.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*--ESSzgFUHoKV0Uw.png" /></figure><p>If you encounter issues with these settings, try and change the HOSTNAME value to 0.0.0.0 — all interfaces.</p><blockquote><strong><em>Note:</em></strong><em> It is not mandatory to change </em><em>PORT NUMBER or </em><em>NETWORK ID, but they are set to the same value as Hardhat, so the rest of the Tutorial will look the same.</em></blockquote><blockquote><em>The </em><em>AUTOMINE option is disabled, same as in the </em>Hardhat<em> section of this tutorial, for debugging purposes. For more information, consult the </em><a href="https://trufflesuite.com/docs/ganache/reference/cli-options/#miner"><em>official documentation</em></a><em>.</em></blockquote><h4>3. Deploy the contract</h4><p>With the Truffle suite, you can simply deploy any contract in the contracts directory by running</p><pre>truffle migrate</pre><p>You should, a this point, see a log similar to this:</p><pre>Compiling your contracts...<br>===========================<br>&gt; Everything is up to date, there is nothing to compile.<br><br>Starting migrations...<br>======================<br>&gt; Network name:    &#39;development&#39;<br>&gt; Network id:      1337<br>&gt; Block gas limit: 6721975 (0x6691b7)<br>1_deploy_contracts.js<br>=====================<br>   Replacing &#39;ConvertLib&#39;<br>   ----------------------<br>   &gt; transaction hash:    0x268debaaefe5ed181d0f7eb41f8c0a12b378a0c076910507b654fa6818889c49<br>   &gt; Blocks: 0            Seconds: 0<br>   &gt; contract address:    0x65cb02943D325A5aaC103eAEea6dB52B561Ee347<br>   &gt; block number:        13<br>   &gt; block timestamp:     1675251575<br>   &gt; account:             0x62B9eB74708c9B728F41a2D6d946E4C736f1e038<br>   &gt; balance:             99.99684864<br>   &gt; gas used:            157568 (0x26780)<br>   &gt; gas price:           20 gwei<br>   &gt; value sent:          0 ETH<br>   &gt; total cost:          0.00315136 ETH<br>   Linking<br>   -------<br>   * Contract: MetaCoin &lt;--&gt; Library: ConvertLib (at address: 0x65cb02943D325A5aaC103eAEea6dB52B561Ee347)<br>   Replacing &#39;MetaCoin&#39;<br>   --------------------<br>   &gt; transaction hash:    0x35a35781d5b4947285caf8d9f901d9be7a675821b9c4ec934bc2be2d7e9e85cb<br>   &gt; Blocks: 0            Seconds: 4<br>   &gt; contract address:    0xc0Bb32fc688C4cac4417B8E732F26836C594C258<br>   &gt; block number:        14<br>   &gt; block timestamp:     1675251580<br>   &gt; account:             0x62B9eB74708c9B728F41a2D6d946E4C736f1e038<br>   &gt; balance:             99.98855876<br>   &gt; gas used:            414494 (0x6531e)<br>   &gt; gas price:           20 gwei<br>   &gt; value sent:          0 ETH<br>   &gt; total cost:          0.00828988 ETH<br>   &gt; Saving artifacts<br>   -------------------------------------<br>   &gt; Total cost:          0.01144124 ETH<br>Summary<br>=======<br>&gt; Total deployments:   2<br>&gt; Final cost:          0.01144124 ETH</pre><p>Make sure to take note of the contract address, we&#39;ll be using this later, while indexing. In the case reported here, it&#39;s 0xc0Bb32fc688C4cac4417B8E732F26836C594C258.</p><h4>4. Generate test transactions</h4><p>Then, I ended up following <a href="https://trufflesuite.com/docs/truffle/how-to/contracts/interact-with-your-contracts/">the steps described in this Truffle documentation page</a> to generate a few transactions on the MetaCoin contract I had previously deployed.</p><p>I launched the console first:</p><pre>truffle console</pre><p>Your prompt should have changed to this:</p><pre>truffle(development)&gt;</pre><p>Then obtained an instance of the contract, and used it to trigger the sendCoin function, and send coin to 4 other accounts.</p><pre>let instance = await MetaCoin.deployed()<br>let accounts = await web3.eth.getAccounts()<br>instance.sendCoin(accounts[1], 10, {from: accounts[0]})<br>instance.sendCoin(accounts[2], 10, {from: accounts[0]})<br>instance.sendCoin(accounts[3], 10, {from: accounts[0]})<br>instance.sendCoin(accounts[4], 10, {from: accounts[0]})</pre><p>I can also verify that the first account now has 40 “coins” less than the initial 10000 allotted by Ganache:</p><pre>truffle(development)&gt; let balance = await instance.getBalance(accounts[0])<br>truffle(development)&gt; balance.toNumber()<br>9960</pre><h3>Archive Docker image</h3><p>In order to index the local Ethereum node, we need to use a pre-built Docker image of Subsquid’s Ethereum Archive. You can do it, by creating a docker-compose.archive.yml file (I have added one in the archive folder) and paste this:</p><pre>version: &quot;3&quot;<br><br>services:<br>  worker:<br>    image: subsquid/eth-archive-worker:latest<br>    environment:<br>      RUST_LOG: &quot;info&quot;<br>    ports:<br>      - 8080:8080<br>    command: [<br>            &quot;/eth/eth-archive-worker&quot;,<br>            &quot;--server-addr&quot;, &quot;0.0.0.0:8080&quot;,<br>            &quot;--db-path&quot;, &quot;/data/db&quot;,<br>            &quot;--data-path&quot;, &quot;/data/parquet/files&quot;,<br>            &quot;--request-timeout-secs&quot;, &quot;300&quot;,<br>            &quot;--connect-timeout-ms&quot;, &quot;1000&quot;,<br>            &quot;--block-batch-size&quot;, &quot;10&quot;,<br>            &quot;--http-req-concurrency&quot;, &quot;10&quot;,<br>            &quot;--best-block-offset&quot;, &quot;10&quot;,<br>            &quot;--rpc-urls&quot;, &quot;http://host.docker.internal:8545/&quot;,<br>            &quot;--max-resp-body-size&quot;, &quot;30&quot;,<br>            &quot;--resp-time-limit&quot;, &quot;5000&quot;,<br>            &quot;--query-concurrency&quot;, &quot;16&quot;,<br>    ]<br>    # Uncomment this section on Linux machines.<br>    # The connection to local RPC node will not work otherwise.<br>    # extra_hosts:<br>    #   - &quot;host.docker.internal:host-gateway&quot;<br>    volumes:<br>      - database:/data/db<br><br>volumes:<br>  database:</pre><p>Now, let&#39;s open the commands.json file, and add archive-up and archive-down to the list of commands. It&#39;s not mandatory, but these shortcuts are quicker to type, and easier to remember! 😅</p><pre>{<br>    &quot;$schema&quot;: &quot;https://cdn.subsquid.io/schemas/commands.json&quot;,<br>    &quot;commands&quot;: {<br>      // ...<br>      &quot;archive-up&quot;: {<br>        &quot;description&quot;: &quot;Start a local EVM Archive&quot;,<br>        &quot;cmd&quot;: [&quot;docker-compose&quot;, &quot;-f&quot;, &quot;archive/docker-compose.archive.yml&quot;, &quot;up&quot;, &quot;-d&quot;]<br>      },<br>      &quot;archive-down&quot;: {<br>        &quot;description&quot;: &quot;Stop a local EVM Archive&quot;,<br>        &quot;cmd&quot;: [&quot;docker-compose&quot;, &quot;-f&quot;, &quot;archive/docker-compose.archive.yml&quot;, &quot;down&quot;]<br>      },<br>      // ...<br>    }<br>  }</pre><p>Then start the service by opening the terminal and launching the command:</p><pre>sqd archive-up</pre><h3>Squid development</h3><p>It&#39;s finally time to index the smart contract events we generated earlier, with Subsquid’s SDK. Using the <strong>contract’s ABI</strong> and contract <strong>address</strong> from previous steps.</p><p>If you want to know more about how to develop your squid ETL, please head over to the <a href="https://docs.subsquid.io/tutorials/create-an-ethereum-processing-squid/">dedicated tutorial</a> on Subsquid’s official documentation.</p><h4>Schema</h4><p>Since we are using MetaCoin contract as a reference, so we&#39;ll build a schema that makes sense with respect to the data it produces.</p><pre>type Transfer @entity {<br>  id: ID!<br>  block: Int!<br>  from: String! @index<br>  to: String! @index<br>  value: BigInt!<br>  txHash: String!<br>  timestamp: BigInt!<br>}</pre><p>And now let&#39;s generate the TypeScript model classes for this schema. In a console window, from the project’s root folder, type this command:</p><pre>sqd codegen</pre><h4>ABI facade</h4><p>We also need to generate TypeScript code to interact with the contract, and for that, we need the contract&#39;s ABI.</p><p>Luckily, Truffle generates that when it compiles the Solidity code, and puts the ABI under build/contracts(and Hardhat does the same under artifacts/contracts/). So let&#39;s use Subsquid CLI tools and in a console window, from the project’s root folder, type this command:</p><pre>sqd typegen build/contracts/MetaCoin.json</pre><h4>Processor configuration</h4><p>Let&#39;s open the processor.ts file, and make sure we import the new Transfer model, as well as the ABI facade. From this, we are only going to import the events object.</p><pre>import { Transfer } from &#39;./model&#39;;<br>import { events } from &#39;./abi/MetaCoin&#39;;</pre><p>Because we want to index our local Archive, the data source of the processor class needs to be set to the local environment.</p><p>I have also createad a constant for the address of the contract we deployed, and always remember to toLowerCase() your contract addresses, to avoid issues.</p><p>And finally, we want to configure the processor to request data about the Transfer event:</p><pre>// ...<br>const contractAddress = &quot;0xc0bb32fc688c4cac4417b8e732f26836c594c258&quot;.toLowerCase()<br>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    chain: &quot;http://localhost:8545&quot;,<br>    archive: &quot;http://localhost:8080&quot;,<br>  })<br>  .addLog(contractAddress, {<br>    filter: [[events.Transfer.topic]],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  });<br>// ...</pre><p>You can also use environment variables, just like shown in <a href="https://medium.com/subsquid/how-to-build-a-performant-and-scalable-full-stack-nft-marketplace-63c12466b959">this complete end-to-end project example</a>. This will make sure the project will stay the same, and only configuration files containing environment variables will be changed, depending on the deployment.</p><h4>Process events</h4><p>We now need to change the logic of the processor.run() function, to apply our custom logic.</p><p>In order to process Transfer events, we need to &quot;unbundle&quot; all the events our processor receives from the Archive in a single batch, and decode each one of them.</p><p>The best practice in this scenario, is to use an array as a temporary data storage, and save all the processed transfers at the end, in another batch operation. This will yield better performance, as data storages such as the Postgres database we are using at the moment, are optimized for this. The code should look like this:</p><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const transfers: Transfer[] = []<br>  for (let c of ctx.blocks) {<br>    for (let i of c.items) {<br><br>      if (i.address === contractAddress &amp;&amp; i.kind === &quot;evmLog&quot;){<br>        if (i.evmLog.topics[0] === events.Transfer.topic) {<br><br>          const { _from, _to, _value } = events.Transfer.decode(i.evmLog)<br><br>          transfers.push(new Transfer({<br>            id: `${String(c.header.height).padStart(10, &#39;0&#39;)}-${i.transaction.hash.slice(3,8)}`,<br>            block: c.header.height,<br>            from: _from,<br>            to: _to,<br>            value: _value.toBigInt(),<br>            timestamp: BigInt(c.header.timestamp),<br>            txHash: i.transaction.hash<br>          }))<br>        }<br>      }<br>    }<br>   }<br>   await ctx.store.save(transfers)<br>});</pre><h4>Run and test</h4><p>It&#39;s finally time to perform a full test. Let&#39;s build the project first:</p><pre>sqd build</pre><p>Then, we need to launch the database Docker container, by running the command:</p><pre>sqd up</pre><p>Next, we need to remove the database migration file that comes with the template, and generate a new one, for our new database schema:</p><pre>sqd migration:clean<br>sqd migration:generate</pre><p>And we can finally launch the processor:</p><pre>sqd process</pre><p>In order to test our data, we can launch the GraphQL server as well. In a different console window, run:</p><pre>sqd serve</pre><p>And perform a test query:</p><pre>query MyQuery {<br>  transfers {<br>    amount<br>    block<br>    from<br>    id<br>    timestamp<br>    to<br>    transactionHash<br>  }<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PZD6_viW_y2VFsTGHxDRqg.png" /></figure><h3>Conclusions</h3><p>What I showed you here is that thanks to Subsquid, it is possible to start working with an indexing middleware from the very early stages of a project.</p><p>Developers can build their dApp by building their smart contract locally, deploying it on a local node, and indexing that local node with a squid. This leads to a development and testing cycle with much tighter iterations.</p><p>Furthermore, since the indexing middleware is present from the start, fewer changes have to be made (just changing the chain, address, and the contract address itself).</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, Subsquid.</p><p><strong>Subsquid socials:<br></strong><a href="https://subsquid.io/">Website</a> | <a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3936ba7a8cec" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/boost-your-dapp-development-productivity-with-local-indexing-3936ba7a8cec">Boost your dApp development productivity with local indexing</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Don’t trust, verify: Indexing ENS Domains with Subsquid]]></title>
            <link>https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db?source=rss-6f504f633941------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1fa267923db</guid>
            <category><![CDATA[evm]]></category>
            <category><![CDATA[ethereum]]></category>
            <category><![CDATA[blockchain-technology]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[ethereum-name-service]]></category>
            <dc:creator><![CDATA[Massimo Luraschi]]></dc:creator>
            <pubDate>Tue, 31 Jan 2023 18:10:46 GMT</pubDate>
            <atom:updated>2023-01-31T22:39:57.521Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vDfor7fuV7QQSmM52tWx2w.png" /></figure><h3>Summary</h3><p>I leveraged Subsquid&#39;s indexing framework to build my very own API <strong>that is able to map every </strong><strong>tokenId to its corresponding ENS name</strong>, allowing for querying in the opposite direction (get ENS name from tokenId ).</p><p>Why this is needed or important? Read below 😉</p><p>The project discussed in this article is available at this repository, on the ens-workshop branch:</p><p><a href="https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop"><strong>github.com/RaekwonIII/ethereum-name-service-indexing/</strong></a></p><p>The repository is also configured, so you can run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop">Gitpod</a>:</p><figure><a href="https://gitpod.io/#https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop"><img alt="Open in Gitpod" src="https://cdn-images-1.medium.com/max/160/1*sTh7ly7sGcNYt9m4jnh0Mw.png" /></a></figure><h3>Introduction</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FkfKBNy79zxA%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkfKBNy79zxA&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FkfKBNy79zxA%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/b3e3796fdd17166a1990490a2e073d68/href">https://medium.com/media/b3e3796fdd17166a1990490a2e073d68/href</a></iframe><p>I have recently published a series of videos and articles to educate about Blockchain Data Indexing.</p><p>It specifically discusses Ethereum, although it&#39;s applicable to all the EVM networks that Subsquid is available for, plus Substrate networks, with a couple of adaptations I&#39;ll probably discuss in a future article. It&#39;s a full course consisting of multiple <a href="https://medium.com/subsquid/blockchain-data-indexing-a-full-course-lesson-1-cc0356528a57?source=your_stories_page-------------------------------------">&quot;lessons&quot;</a>, introducing <a href="https://subsquid.io/">Subsquid</a>, the basic concepts, and a few practical tutorials to practice using the SDK.</p><p>While creating these tutorials, I choose <a href="https://ens.domains/">Ethereum Name Service</a> as an example, because it&#39;s a famous project, and quite frankly, also because I take these chances to study some subjects I am interested in (sue me! 😛).</p><p>What I didn&#39;t realize was that, in doing so, I ended up discovering not one, but two peculiar aspects of the NFTs created by the project, and consequently, two strong cases in favor of blockchain data indexing, and more specifically, two great examples of Subsquid&#39;s superiority in this aspect.</p><h3>ENS Domains are NFTs…but they are odd</h3><p>First of all, this needs to be clarified: whenever someone is registering a domain name on the <a href="https://app.ens.domains/">Ethereum Name Service app</a>, they are minting a Non-Fungible Token from the ENS collection.</p><p>This token looks and behaves like a common ERC-721 token: as a matter of fact, the project migrated the registrar in 2019, and since then, it&#39;s a contract that is compliant to this standard.</p><p>It does, however, have its peculiarities.</p><h4>Token ID calculation</h4><p>Unlike usual NFT collections, the tokenId of ENS name is the uint256representation of the hash of the label (vitalik for vitalik.eth). If someone wanted to derive the tokenId for a given ENS name (in this case: vitalik, which computes to 79233663829379634837589865448569342784712482819484549289560981379859480642508), they would have to do it like this:</p><pre>const ethers = require(&#39;ethers&#39;)<br>const BigNumber = ethers.BigNumber<br>const utils = ethers.utils<br>const name = &#39;vitalik&#39;<br>const labelHash = utils.keccak256(utils.toUtf8Bytes(name))<br>const tokenId = BigNumber.from(labelHash).toString()</pre><p>On the flip-side, deriving the ENS name from the tokenId is not as easy. The reason for this was that the fixed-length hash allows to register infinite length of names, but the trade-off is that, because the name is hashed, this is a one-way operation: you cannot obtain ENS names from a tokenId.</p><p>All of this is explained in greater detail on <a href="https://docs.ens.domains/dapp-developer-guide/ens-as-nft">ENS documentation page</a>.</p><h4>Token URL and metadata</h4><p>Simply put, the ENS contract does not have tokenURI. The project has, instead, created a <a href="https://metadata.ens.domains/docs">separate metadata service</a> which NFT marketplaces like OpenSea can fetch metadata for ENS such as registration data, expiration date, name length, etc.</p><p>The problem with this service? It’s using the tokenId to obtain the metadata.</p><h4>Downsides of these oddities</h4><p>Let’s say I am monitoring Vitalik’s wallet on-chain activity, and he’s minted an NFT token.<br>At this point, I only have the token ID, what if I want to know the name, and the eventual image he’s associated with the domain?</p><p>If it’s just one token I could retrieve its metadata from ENS API, but what if I’m monitoring multiple wallets? Or the entire network? I would need a more scalable solution.</p><p>So this is exactly the adventure I embarked on.</p><h3>Let&#39;s get building!</h3><p>The project in <a href="https://link.medium.com/HuACn2CU0wb">Lesson 2 of the Blockchain Data Indexing course</a> (you can find the project on <a href="https://github.com/RaekwonIII/ethereum-name-service-indexing">this GitHub page</a>) was already indexing ENS tokens, but in order to keep it simple and don&#39;t introduce too many variables to the learning process, it stopped at tracking transfers of tokens.</p><pre>const processor = new EvmBatchProcessor()<br>.setDataSource({<br>  chain: process.env.RPC_ENDPOINT,<br>  archive: &quot;https://eth.archive.subsquid.io&quot;,<br>})<br>.addLog(contractAddress, {<br>  filter: [<br>    [<br>      events.Transfer.topic,<br>    ],<br>  ],<br>  data: {<br>    evmLog: {<br>      topics: true,<br>      data: true,<br>    },<br>    transaction: {<br>      hash: true,<br>    },<br>  },<br>});</pre><h4>Modifying the schema</h4><p>Because the objective is to improve the current project and save additional data, we need to change the schema first. I have added new fields, such as name, imageURI, uri and the registrar expiration date as expires :</p><pre>type Token @entity {<br>  id: ID!<br>  owner: Owner<br>  name: String<br>  imageURI: String<br>  uri: String<br>  expires: BigInt,<br>  transfers: [Transfer!]! @derivedFrom(field: &quot;token&quot;)<br>  contract: Contract<br>}<br><br>type Owner @entity {<br>  id: ID!<br>  ownedTokens: [Token!] @derivedFrom(field: &quot;owner&quot;)<br>}<br><br>type Contract @entity {<br>  id: ID!<br>  name: String! @index<br>  symbol: String!<br>  totalSupply: BigInt!<br>  tokens: [Token!]! @derivedFrom(field: &quot;contract&quot;)<br>}<br><br>type Transfer @entity {<br>  id: ID!<br>  token: Token!<br>  from: Owner<br>  to: Owner<br>  timestamp: BigInt!<br>  block: Int! @index<br>  transactionHash: String!<br>}</pre><blockquote>After the publishing of Lesson 2, Subsquid published a new version of our CLI, with <strong>improved commands</strong>, make sure to install it with</blockquote><blockquote><em>npm i -g @subsquid/cli@latest</em></blockquote><blockquote>And if you are working on the same project from the lesson, make sure to add this <a href="https://github.com/RaekwonIII/ethereum-name-service-indexing/blob/ens-workshop/commands.json"><em>commands.json</em> file</a> to your root folder.</blockquote><p>Following these changes, we need to update the TypeScript models, so let&#39;s run the command:</p><pre>sqd codegen</pre><h4>Processor configuration</h4><p>What is truly interesting about the ENS project, is the ability to index the NameRegisteredand NameRenewed events as well. This way, we are able to capture every time a name is registered, renewed and when it is transferred to a different owner.</p><p>For this, we need to change the part where EvmBatchProcessor class is instantiated and configured:</p><pre>const processor = new EvmBatchProcessor()<br>  .setDataSource({<br>    chain: process.env.RPC_ENDPOINT,<br>    archive: &quot;https://eth.archive.subsquid.io&quot;,<br>  })<br>  .addLog(contractAddress, {<br>    filter: [<br>      [<br>        events.NameRegistered.topic,<br>        events.NameRenewed.topic,<br>        events.Transfer.topic,<br>      ],<br>    ],<br>    data: {<br>      evmLog: {<br>        topics: true,<br>        data: true,<br>      },<br>      transaction: {<br>        hash: true,<br>      },<br>    },<br>  });</pre><p>Of course, we also need to adjust how we process each item, because we will have logs generated by three different events, with three different topics, and they have to be treated separately:</p><pre>processor.run(new TypeormDatabase(), async (ctx) =&gt; {<br>  const ensDataArr: ENSData[] = [];<br>  for (let c of ctx.blocks) {<br>    for (let i of c.items) {<br>      if (i.address === contractAddress &amp;&amp; i.kind === &quot;evmLog&quot;) {<br>        if (i.evmLog.topics[0] === events.NameRegistered.topic) {<br>          const ensData = handleNameRegistered({<br>            ...ctx,<br>            block: c.header,<br>            ...i,<br>          });<br>          ensDataArr.push(ensData);<br>        }<br>        if (i.evmLog.topics[0] === events.NameRenewed.topic) {<br>          const ensData = handleNameRenewed({<br>            ...ctx,<br>            block: c.header,<br>            ...i,<br>          });<br>          ensDataArr.push(ensData);<br>        }<br>        if (i.evmLog.topics[0] === events.Transfer.topic) {<br>          const ensData = handleTransfer({<br>            ...ctx,<br>            block: c.header,<br>            ...i,<br>          });<br>          ensDataArr.push(ensData);<br>        }<br>      }<br>    }<br>  }<br><br>  await saveENSData(<br>    {<br>      ...ctx,<br>      block: ctx.blocks[ctx.blocks.length - 1].header,<br>    },<br>    ensDataArr<br>  );<br>});</pre><p>Notice the three different handle* functions. They are not too different from each other, but I&#39;d argue they are still different enough to deserve to be separate:</p><pre>function handleNameRegistered(<br>  ctx: LogHandlerContext&lt;<br>    Store,<br>    { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>  &gt;<br>): ENSData {<br>  const { evmLog, block, transaction } = ctx;<br><br>  const { id, owner, expires } = events.NameRegistered.decode(evmLog);<br><br>  const ensData: ENSData = {<br>    id: `${transaction.hash}-${evmLog.address}-${id.toBigInt()}-${<br>      evmLog.index<br>    }`,<br>    to: owner,<br>    tokenId: id.toBigInt(),<br>    timestamp: BigInt(block.timestamp),<br>    expires: expires.toNumber(),<br>    block: block.height,<br>    transactionHash: transaction.hash,<br>  };<br><br>  return ensData;<br>}<br><br>function handleNameRenewed(<br>  ctx: LogHandlerContext&lt;<br>    Store,<br>    { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>  &gt;<br>): ENSData {<br>  const { evmLog, block, transaction } = ctx;<br><br>  const { id, expires } = events.NameRenewed.decode(evmLog);<br><br>  const ensData: ENSData = {<br>    id: `${transaction.hash}-${evmLog.address}-${id.toBigInt()}-${<br>      evmLog.index<br>    }`,<br>    tokenId: id.toBigInt(),<br>    timestamp: BigInt(block.timestamp),<br>    expires: expires.toNumber(),<br>    block: block.height,<br>    transactionHash: transaction.hash,<br>  };<br><br>  return ensData;<br>}<br><br>function handleTransfer(<br>  ctx: LogHandlerContext&lt;<br>    Store,<br>    { evmLog: { topics: true; data: true }; transaction: { hash: true } }<br>  &gt;<br>): ENSData {<br>  const { evmLog, block, transaction } = ctx;<br><br>  const { from, to, tokenId } = events.Transfer.decode(evmLog);<br><br>  const ensData: ENSData = {<br>    id: `${transaction.hash}-${evmLog.address}-${tokenId.toBigInt()}-${<br>      evmLog.index<br>    }`,<br>    from,<br>    to,<br>    tokenId: tokenId.toBigInt(),<br>    timestamp: BigInt(block.timestamp),<br>    block: block.height,<br>    transactionHash: transaction.hash,<br>  };<br>  return ensData;<br>}</pre><h4>Augment on-chain data with external APIs</h4><p>We are getting to the juicy bit here.</p><p>Finally, I have explored the tokenId and tokenURI issues mentioned above, and changed the saveENSData function to:</p><ul><li>Fetch the token metadata from <a href="https://metadata.ens.domains/docs">ENS metadata service</a></li><li>Process this information and extract the name of a given token (establishing the tokenId ↔️ name relation) and the URL of the image</li><li>Save this data in the Token entity, on the database</li></ul><pre>async function saveENSData(<br>  ctx: BlockHandlerContext&lt;Store&gt;,<br>  ensDataArr: ENSData[]<br>) {<br>  const tokensIds: Set&lt;string&gt; = new Set();<br>  const ownersIds: Set&lt;string&gt; = new Set();<br><br>  for (const ensData of ensDataArr) {<br>    tokensIds.add(ensData.tokenId.toString());<br>    if (ensData.from) ownersIds.add(ensData.from.toLowerCase());<br>    if (ensData.to) ownersIds.add(ensData.to.toLowerCase());<br>  }<br><br>  const transfers: Set&lt;Transfer&gt; = new Set();<br><br>  const tokens: Map&lt;string, Token&gt; = new Map(<br>    (await ctx.store.findBy(Token, { id: In([...tokensIds]) })).map((token) =&gt; [<br>      token.id,<br>      token,<br>    ])<br>  );<br><br>  const owners: Map&lt;string, Owner&gt; = new Map(<br>    (await ctx.store.findBy(Owner, { id: In([...ownersIds]) })).map((owner) =&gt; [<br>      owner.id,<br>      owner,<br>    ])<br>  );<br><br>  for (const ensData of ensDataArr) {<br>    const {<br>      id,<br>      tokenId,<br>      from,<br>      to,<br>      block,<br>      expires,<br>      transactionHash,<br>      timestamp,<br>    } = ensData;<br><br>    // the &quot;&quot; case handles absence of sender, which means it&#39;s not an actual transaction<br>    // it&#39;s the name being registered<br>    let fromOwner = owners.get(from || &quot;&quot;);<br>    if (from &amp;&amp; fromOwner == null) {<br>      fromOwner = new Owner({ id: from.toLowerCase() });<br>      owners.set(fromOwner.id, fromOwner);<br>    }<br><br>    // the &quot;&quot; case handles absence of receiver, which means it&#39;s not an actual transaction<br>    // it&#39;s the name being renewed<br>    let toOwner = owners.get(to || &quot;&quot;);<br>    if (to &amp;&amp; toOwner == null) {<br>      toOwner = new Owner({ id: to.toLowerCase() });<br>      owners.set(toOwner.id, toOwner);<br>    }<br><br>    const tokenIdString = tokenId.toString();<br>    let token = tokens.get(tokenIdString);<br>    if (token == null) {<br>      token = new Token({<br>        id: tokenIdString,<br>        contract: await getOrCreateContractEntity(ctx.store),<br>      });<br>      tokens.set(token.id, token);<br>    }<br>    token.owner = toOwner;<br><br>    if (expires) {<br>      token.expires = BigInt(expires);<br>      if (expires * 1000 &lt; Date.now()) {<br>        // ctx.log.info(`Token ${tokenId} has expired`);<br>        continue;<br>      }<br>      let name = &quot;&quot;,<br>        uri = &quot;&quot;,<br>        imageURI = &quot;&quot;;<br>      try {<br>        // https://metadata.ens.domains/docs<br>        let tokenURI = `https://metadata.ens.domains/mainnet/${contractAddress}/${tokenId}`;<br>        const meta = await axios.get(tokenURI);<br><br>        imageURI = meta.data.image;<br>        name = meta.data.name;<br>        uri = meta.data.url;<br>      } catch (e) {<br>        ctx.log.warn(`[API] Error during fetch token ${tokenId} metadata`);<br>        if (e instanceof Error) ctx.log.warn(`${e.message}`);<br>      } finally {<br>        token.name = name;<br>        token.uri = uri;<br>        token.imageURI = imageURI;<br>      }<br>    }<br><br>    if (toOwner &amp;&amp; fromOwner) {<br>      const transfer = new Transfer({<br>        id,<br>        block,<br>        timestamp,<br>        transactionHash,<br>        from: fromOwner,<br>        to: toOwner,<br>        token,<br>      });<br><br>      transfers.add(transfer);<br>    }<br>  }<br><br>  await ctx.store.save([...owners.values()]);<br>  await ctx.store.save([...tokens.values()]);<br>  await ctx.store.save([...transfers]);<br>}</pre><p>The important part is where the metadata service API is called, using axios library:</p><pre>let tokenURI = `https://metadata.ens.domains/mainnet/${contractAddress}/${tokenId}`;<br>const meta = await axios.get(tokenURI);</pre><p>I want to take a moment to emphasize the ability to call arbitrary external services to obtain additional information, and add to the data we extract from the blockchain.</p><p>This way, we are building a service, providing data that would not be possible to produce by purely relying on the blockchain.</p><h3>Don&#39;t trust, verify</h3><p>The <a href="https://docs.ens.domains/dapp-developer-guide/ens-as-nft#deriving-ens-name-from-tokenid">ENS documentation acknowledges the shortcomings of their architectural choice</a>, and provides an alternative, which is using a third party service, or downloading this service&#39;s dataset.</p><p>One might brush this off as non-consequential, but if you truly subscribe to the motto <em>“Don’t Trust, Verify”</em>, being able to obtain this information independently, in a decentralized way, and without relying on third party services is paramount.</p><h4>Local indexing</h4><p>If you have followed along and want to try this at home, you should delete the previous migration file:</p><pre>rm -rf db/migrations/*js</pre><p>Launch the database Docker container and generate a new migration file:</p><pre>sqd up<br>sqd migration</pre><p>Launch the processor:</p><pre>sqd process</pre><p>And finally, in a separate terminal window, launch the GraphQL service:</p><pre>sqd serve</pre><p>We can, for example, test out our initial question: &quot;what is the image linked to vitalik.eht ENS name?&quot; with this query:</p><pre>query MyQuery {<br>  tokens(where: {name_contains: &quot;vitalik&quot;}) {<br>    expires<br>    id<br>    imageURI<br>    name<br>    uri<br>    owner {<br>      id<br>    }<br>  }<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*l0MiKwvXmqucIFf1x6RSow.png" /></figure><p>And if we copy paste the imageURI in our browser, we&#39;ll eventually land on vitalik&#39;s Nyan cat animated image:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/270/1*o2hZOgamCR4xv_P1lHCBtA.png" /></figure><h4>Gitpod</h4><p>Another option, as mentioned at the start of the article, is to run the project on <a href="https://gitpod.io/#https://github.com/RaekwonIII/ethereum-name-service-indexing/tree/ens-workshop">Gitpod</a> by clicking on the related link on the <a href="https://github.com/RaekwonIII/ethereum-name-service-indexing/blob/ens-workshop/README.md">project&#39;s README file</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nuql7MBQFRqWRzoJnhWrmQ.png" /></figure><p>Which will launch the entire project in a cloud-hosted execution environment for us.</p><h4>Conclusions</h4><p>What I tried to show here is that, thanks to Subsquid, <strong>you can build your own indexer, that is able to map every </strong><strong>tokenId to its corresponding ENS name</strong>, allowing for querying in the opposite direction (get ENS name from tokenId ).</p><p>If you found this article interesting, and you want to read more, please <a href="https://linktr.ee/raekwoniii">follow me</a> and most importantly, Subsquid.</p><p><strong>Subsquid socials:<br></strong><a href="https://www.twitter.com/subsquid">Twitter</a> | <a href="https://discord.gg/subsquid">Discord</a> | <a href="https://www.linkedin.com/company/subsquid">LinkedIn</a> | <a href="https://t.me/subsquid">Telegram</a> | <a href="https://github.com/subsquid">GitHub</a> | <a href="https://www.youtube.com/@subsquid">YouTube</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1fa267923db" width="1" height="1" alt=""><hr><p><a href="https://medium.com/subsquid/dont-trust-verify-indexing-ens-domains-with-subsquid-e1fa267923db">Don’t trust, verify: Indexing ENS Domains with Subsquid</a> was originally published in <a href="https://medium.com/subsquid">SQD Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>