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!

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.