indexing all Ethereum fullnode data in a relational database

Question

I want to develop a web app like etherscan. I realized I have to extract and index all datas in a relational database. I have chosen Posturesql. All Datas I want to save are:

• block
• uncle
• transaction receipt
• transaction trace
• smart contract details

And I use geth IPC.

But it's not stable and it's to slow for my purpose.

What is the best practices? Is there another way to get all datas from genesis block to the last one? Can I read directly from the leveldb? How?

Answer 1

Blockscout is an opensource EVM explorer and it uses many technologies for this purpose. It is no less than etherscan. Blockscout uses postgresql as database, Elixir/Erlang programing language for backend, react for frontend and so on (many to name). You can get idea from their database design easily. For your convinience you can look at the opensource code at github.

Answer 2

if you are planning to have a ready made source or application that is running you can use several explorers, like blockscot or evm explorer and other explorers available for free, but if you are planning to have every transaction to be customised as per your interest, here is the sample to get all the transactions after every block is confirmed.

const  WSSURL_BSC= `Alchemylink`;
const { ethers } = require("ethers");
var provider = ethers.getDefaultProvider(WSSURL_BSC);
provider.on("block", (blockNumber) => {

    provider.getBlockWithTransactions(blockNumber).then(block => {
        console.log(`Got txns for block ${blockNumber}`);
        block.transactions.filter(tx => tx.data == '0x').forEach(tx => {
            // raw transactions
        });
        block.transactions.filter(tx => tx.data != '0x').forEach(tx => {
            // token transactions
        });
    });
});

log them and use as per designed handling in your own customised way.