Ethers - get historical block.timestamp in batch mode

Question

I am trying to build a simple database table containing block.number and block.timestamp starting at a given block number.

This could be easily done with the following JS code using ethers.js library:

const loadEthBlocks = async (fromBlock, toBlock) => {
    for (let i = fromBlock; i <= toBlock; i++) {
        const block = await provider()
           .getBlock(i)
        // store record into database
    }
}

However, this will generate a request per block (when using Alchemy or Infura, this is a handicap). After some tests, it takes 1 second to store 2 blocks into a PostgreSQL DB, so it's not feasible for a few millions of records.

I am wondering if there is a way to retrieve a range of blocks in bulk mode containing at least the block number and block timestamp and then doing a bulk data load (instead of one record per request).

It would be great if we could define a starting block in the below function and retrieve also the timestamp, but this listener only returns the block number starting from the latest block:

provider.on("block", (blockNumber) => {
    // Emitted on every block change
})

Anyone facing the same issue with a scalable solution?

Answer 1

Ethers simply uses the JSON RPC API exposed by ethereum nodes. Since you need block.timestamp, the only two methods that allow this are eth_getBlockByHash and eth_getBlockByNumber (both used in provider.getBlock under the hood). This is definitely very slow for your use case.

You can run your own node to speed up. If you don't want to do that, you can use some tricks that come to my mind right now:

• If the chain is Ethereum mainnet or xDai, Dune Analytics already have the entire blockchain indexed on SQL and you can write query to extract what you want. But to export the data in csv they charge subscription.
• There is a very hacky way otherwise, you might utilize TheGraph's indexer. You can create a very simple subgraph that just stores a Block that contains a block number and timestamp. Once indexer is done indexing, you can query the data using GraphQL and write to your DB.