Aave's GraphQL database is a trove of information. It also has its own intricacies. In this post, I'll show how I parsed its information into a friendly format in order to chart it in Vega. This is a continuation of my post on how to query a dApp's GraphQL data in React and TypeScript.

Basic GraphQL query

This query provides the data I need to get started:

{
  borrows(first: 5) {
    timestamp
    amount
    borrowRate
    reserve {
      name
      symbol
      decimals
      liquidityRate
      price {
        priceInEth
      }
    }
  }
}

Just like on the Aave subgraph Playground, the borrowRate is in a format which I don't know how to parse. I'll have to figure that out later. It's actually not absolutely required for the questions I was trying to answer. Same for liquidityRate, so I'll remove them for now. We can come back to them later.

Now my query in my React code looks like this:

const BORROWS = gql`
  query GetBorrows {
    borrows(first: 5) {
      timestamp
      amount
      reserve {
        name
        symbol
        decimals
        price {
          priceInEth
        }
      }
    }
  }
`;

And my rendering code looks like this:


function ExchangeRates() {
  const { loading, error, data } = useQuery(BORROWS);

  if (loading) return <p>Loading...</p>;
  if (error) return <p>Error :(</p>;

  console.log(data.borrows);
  return data.borrows.map(({ timestamp, amount, reserve } : GetBorrows_borrows) => (
    <div key={timestamp}>
      <p>
        {timestamp}: {Number(amount) / Math.pow(10, reserve.decimals) } {reserve.symbol}
      </p>
    </div>
  ));
}

My data and Aave Watch seem to differ though.

Let's convert the timestamps to actual dates. With a bit of trial and error, I figured that you can parse the date like this:

new Date(timestamp * 1000).toDateString()

And here's the resulting data:

Fri Dec 10 2021: 5000 USDC
Mon Oct 04 2021: 15 WETH
Tue May 18 2021: 41501.629937448706 DAI
Tue Jun 01 2021: 7000000 USDT
Fri Apr 30 2021: 15000 USDC

Now it makes sense. The borrow info I get is not sorted.

To figure out how to sort the borrow information, I need to check Aave subgraph's schema. It is not exposed in the pre-processed schema at https://github.com/aave/protocol-v2-subgraph/blob/main/schema.graphql. But the fully generated schema shows it:

enum OrderDirection {
  asc
  desc
}

enum Borrow_orderBy {
  id
  pool
  user
  onBehalfOf
  reserve
  userReserve
  amount
  borrowRate
  borrowRateMode
  referrer
  timestamp
  stableTokenDebt
  variableTokenDebt
}

input Borrow_filter {
  id: ID
  id_not: ID
  id_gt: ID
  id_lt: ID
  id_gte: ID
  id_lte: ID
  id_in: [ID!]
  id_not_in: [ID!]
  pool: String
  pool_not: String
  pool_gt: String
  pool_lt: String
  pool_gte: String
  pool_lte: String
  pool_in: [String!]
  pool_not_in: [String!]
  pool_contains: String
  pool_not_contains: String
  pool_starts_with: String
  pool_not_starts_with: String
  pool_ends_with: String
  pool_not_ends_with: String
  user: String
  user_not: String
  user_gt: String
  user_lt: String
  user_gte: String
  user_lte: String
  user_in: [String!]
  user_not_in: [String!]
  user_contains: String
  user_not_contains: String
  user_starts_with: String
  user_not_starts_with: String
  user_ends_with: String
  user_not_ends_with: String
  onBehalfOf: String
  onBehalfOf_not: String
  onBehalfOf_gt: String
  onBehalfOf_lt: String
  onBehalfOf_gte: String
  onBehalfOf_lte: String
  onBehalfOf_in: [String!]
  onBehalfOf_not_in: [String!]
  onBehalfOf_contains: String
  onBehalfOf_not_contains: String
  onBehalfOf_starts_with: String
  onBehalfOf_not_starts_with: String
  onBehalfOf_ends_with: String
  onBehalfOf_not_ends_with: String
  reserve: String
  reserve_not: String
  reserve_gt: String
  reserve_lt: String
  reserve_gte: String
  reserve_lte: String
  reserve_in: [String!]
  reserve_not_in: [String!]
  reserve_contains: String
  reserve_not_contains: String
  reserve_starts_with: String
  reserve_not_starts_with: String
  reserve_ends_with: String
  reserve_not_ends_with: String
  userReserve: String
  userReserve_not: String
  userReserve_gt: String
  userReserve_lt: String
  userReserve_gte: String
  userReserve_lte: String
  userReserve_in: [String!]
  userReserve_not_in: [String!]
  userReserve_contains: String
  userReserve_not_contains: String
  userReserve_starts_with: String
  userReserve_not_starts_with: String
  userReserve_ends_with: String
  userReserve_not_ends_with: String
  amount: BigInt
  amount_not: BigInt
  amount_gt: BigInt
  amount_lt: BigInt
  amount_gte: BigInt
  amount_lte: BigInt
  amount_in: [BigInt!]
  amount_not_in: [BigInt!]
  borrowRate: BigInt
  borrowRate_not: BigInt
  borrowRate_gt: BigInt
  borrowRate_lt: BigInt
  borrowRate_gte: BigInt
  borrowRate_lte: BigInt
  borrowRate_in: [BigInt!]
  borrowRate_not_in: [BigInt!]
  borrowRateMode: BorrowRateMode
  borrowRateMode_not: BorrowRateMode
  borrowRateMode_in: [BorrowRateMode!]
  borrowRateMode_not_in: [BorrowRateMode!]
  referrer: String
  referrer_not: String
  referrer_gt: String
  referrer_lt: String
  referrer_gte: String
  referrer_lte: String
  referrer_in: [String!]
  referrer_not_in: [String!]
  referrer_contains: String
  referrer_not_contains: String
  referrer_starts_with: String
  referrer_not_starts_with: String
  referrer_ends_with: String
  referrer_not_ends_with: String
  timestamp: Int
  timestamp_not: Int
  timestamp_gt: Int
  timestamp_lt: Int
  timestamp_gte: Int
  timestamp_lte: Int
  timestamp_in: [Int!]
  timestamp_not_in: [Int!]
  stableTokenDebt: BigInt
  stableTokenDebt_not: BigInt
  stableTokenDebt_gt: BigInt
  stableTokenDebt_lt: BigInt
  stableTokenDebt_gte: BigInt
  stableTokenDebt_lte: BigInt
  stableTokenDebt_in: [BigInt!]
  stableTokenDebt_not_in: [BigInt!]
  variableTokenDebt: BigInt
  variableTokenDebt_not: BigInt
  variableTokenDebt_gt: BigInt
  variableTokenDebt_lt: BigInt
  variableTokenDebt_gte: BigInt
  variableTokenDebt_lte: BigInt
  variableTokenDebt_in: [BigInt!]
  variableTokenDebt_not_in: [BigInt!]
}

Query {
  ...
  borrows(skip: Int = 0, first: Int = 100, orderBy: Borrow_orderBy, orderDirection: OrderDirection, where: Borrow_filter, ...
  ...
}

If you want to know how I generated it, take a look at my previous post, more specifically the part where I compiled the tool with Cargo and ran graph-graphql.

If I add the sorting info, the query looks like this:

const BORROWS = gql`
  query GetBorrows {
    borrows(first: 5, orderBy: timestamp) {
      timestamp
      amount
      reserve {
        name
        symbol
        decimals
        price {
          priceInEth
        }
      }
    }
  }
`;

Nearly there! Looks like it is querying it in ascending order, but we want it in descending order. Tweaked the query accordingly:

const BORROWS = gql`
  query GetBorrows {
    borrows(first: 5, orderBy: timestamp, orderDirection: desc) {

And now I am seeing the same data as Aave Watch!

I am now seeing the same data as Aave Watch!

The next step is to convert the amounts to Ether, then to USD.

Converting BigInt decimals to number

According to some websites online, a USD Coin today is worth 0.00025512 ETH. Also, according to Aave Watch, Aave's Oracle says that one ETH is worth $3,919.151.

If I query the info on the GraphQL subgraph:

{
  priceOracles {
    usdPriceEth
  }
}

I get:

{
  "data": {
    "priceOracles": [
      {
        "usdPriceEth": "255157297889700"
      }
    ]
  }
}

How do you convert 255157297889700 to $3,919.151?

According to this project, one can convert BigInt to a DecimalBigInt like this (see code), DEFAULT_DECIMALS being 18 (see code):

  export function fromBigInt(value: BigInt, decimals: i32 = DEFAULT_DECIMALS): BigDecimal {
    let precision = BigInt.fromI32(10)
      .pow(<u8>decimals)
      .toBigDecimal()

    return value.divDecimal(precision)
  }

So just divide by a power of 10? I must be missing something.

Let's try with some more data. This time I'll check the borrow rates for USD Coin. The query looks like this:

query {
  reserves {
    name
    variableBorrowRate
    stableBorrowRate
  }
}

The result is:

...
    "reserves": [
      ...
      {
        "name": "Maker",
        "stableBorrowRate": "30667806888971627742233250",
        "variableBorrowRate": "467464822280139419563276"
      },
      {
        "name": "USD Coin",
        "stableBorrowRate": "108510146912845866609424453",
        "variableBorrowRate": "37020293825691733218848905"
      },
      {
        "name": "USD Coin",
        "stableBorrowRate": "109143193722328678434486054",
        "variableBorrowRate": "38286387444657356868972108"
      },
      ...
    ]
  }
}

Meanwhile Aave Watch shows:

In this case it does indeed seem like the number was divided by a power of 10. In this case, they were divided by 10 to the power of 27: 1.09e26 = 0.109e27 = 10.9%e27 and 3.8e25 = .038e27.

... Oh!! I was looking at the USD/Ether conversion the wrong way. The Oracle returns how many Ether a USD is worth. Which is a tiny amount. So if an Ether is worth $3,919.151, one USD should be worth 0.000255157303202 Ether. How did the Oracle return 255157297889700?

0.000255157303202
= 000255157303202e-15
= 255157303202e-15
= 255157303202000e-18
~ 255157297889700e-18

This time it looks like the power of 10 is 18. It'd be nice to check the source code of Aave later. For now I can go with these constants.

Ah and I forgot. The priceInEth of USDC is 254200000000000. Online they say that one USDC is worth 0.00024569492 Ether.

254200000000000
= .254200000000000e-15
= .000254200000000000e-18

The constant is e18 again. Now, the subgraph-toolkit I stumbled on earlier is starting to make sense. Also they seem to be handling BigInts like experts. I'm probably at risk of losing precision by using Numbers. Eventually I should try and reuse that library.

Here's the final code after all conversions are done. Thanks to MDN's article on exponentiation, I learned of the ** operator, which is equivalent to the old Math.pow function:

const ethToUsd = 1 / (data.priceOracles[0].usdPriceEth * 10 ** -18);
return data.borrows.map(({ timestamp, amount, reserve } : GetBorrows_borrows) => {
  const amountInCoin = Number(amount) / 10 ** reserve.decimals;
  const coinInEth = Number(reserve.price.priceInEth) / 10 ** 18;
  return (
    <div key={timestamp}>
      <p>
        {new Date(timestamp * 1000).toDateString()}:
          {amountInCoin} {reserve.symbol}, ie ${amountInCoin * coinInEth * ethToUsd}
      </p>
    </div>
  );
});

And the corresponding output is:

Tue Dec 28 2021:20100 USDC, ie $19952.245447426398
Tue Dec 28 2021:20000 USDT, ie $19939.922528031355
Tue Dec 28 2021:200000 USDT, ie $199399.22528031355
Tue Dec 28 2021:50000 USDC, ie $49632.4513617572
Tue Dec 28 2021:5000000 USDT, ie $4984980.632007839

Generating a JSON

Now let's convert this to JSON so that we can feed it to Vega.

interface BorrowRow {
    date: Date
    coinSymbol: string
    amountCoin: number
    amountEth: number
    amountUsd: number
}

function toRows(borrows: GetBorrows): BorrowRow[] {
  const ethToUsd =
    1 / (Number(borrows.priceOracles[0].usdPriceEth) * 10 ** -18);
  return borrows.borrows.map(
    ({ timestamp, amount, reserve }: GetBorrows_borrows) => {
      const amountInCoin = Number(amount) / 10 ** reserve.decimals;
      const coinInEth = Number(reserve.price.priceInEth) / 10 ** 18;
      return {
        date: new Date(timestamp * 1000),
        coinSymbol: reserve.symbol,
        amountCoin: amountInCoin,
        amountEth: amountInCoin * coinInEth,
        amountUsd: amountInCoin * coinInEth * ethToUsd,
      };
    }
  );
}

After running this code on the latest 1000 loans, I get the following JSON.