TLDR: I led a Blockchain at Berkeley project that made on-chain Cosmos data SQL-queryable via an intuitive frontend that served the data as both tables and graphs. This makes the data more accessible, as it removes the need for web3-specific technical knowledge.
Glossary:
Blockchain at Berkeley | the world’s leading student-run blockchain research, education, and consulting organization |
Cosmos | a decentralized network of independent & interoperable blockchains |
Tendermint | infrastructure for securely and consistently replicating an application on many machines that Cosmos is built on |
Osmosis | the largest decentralized token exchange on Cosmos |
Dune Analytics | an analytics platform that enables access to on-chain data via SQL queries that filter it into convenient charts & graphs |
ETH SF | a hackathon bringing together some of the top minds and experts in Ethereum |
During the research phase of the project, we explored desired features of various dApps and chains, and we discovered that Osmosis was looking for a Dune Analytics integration. Dune Analytics enables the querying and analysis of on-chain data with SQL queries. Upon further investigation and discussion with the Dune team at ETHSF, we learned that this integration isn’t currently possible as Dune’s tooling isn’t compatible with the Cosmos ecosystem. Dune is widely used across the Ethereum / EVM ecosystem, but Cosmos has no such tool.
This roadblock inspired us to create a new tool that fills this gap in the market. We set out to build a Dune analytics-like tool that would enable SQL querying of on-chain data for Cosmos chains, starting with Osmosis.
To get started, we first needed to find a source of on-chain data. After deeply delving into the Tendermint documentation, we realized that we could run our own node and modify its internal indexer to use a Postgres database that we could further customize for our needs.
Next, we built out an API that could take in SQL queries and run them on our customized database, while also ensuring the queries aren’t malicious. With a working backend in place, we then focused on building a user-friendly frontend that allows users to write SQL queries, preview them, and view the resulting output as both a SQL table and an interactive graph.
The end result is a tool that empowers developers and businesses to easily access and analyze Cosmos chain data in a way that was previously not possible. With the ability to query data using SQL, the barrier to analyzing on-chain data is reduced, as the need for web3-specific technical knowledge is removed.
At this time, we have only integrated support for the Osmosis chain, but our backend was built with modularity in mind, allowing us to easily add support for new chains by simply running a validator for that chain and connecting its database. We’re excited to see how this tool will be used and look forward to continuing its development in the future.
For example, the following query in our interface:
produces this graph after being processed by our backend:
landing page design:
Tech Stack
Frontend: Next.js, React + Tailwind, Chart.js
Backend:
DB: Ran a customized Postgres indexer in our own self-hosted Cosmos node
API: Used Express initially, then switched to using Next API Routes with an LRU cache