Working with Cardano DB Sync

The purpose of this document is to explain how Cardano users can retrieve blockchain data from the database. Here, we look into the ways of working with the PostgreSQL database and explore its various queries.

How to retrieve data from the DB Sync PostgreSQL database

Installation Prerequisites

  • PostgreSQL library. This can be installed on Linux using e.g., apt install libpq-dev.
  • PostgreSQL server. This can be installed on Linux using e.g., apt install postgresql.
  • Сardano-node package. This provides the core Cardano node functionality and runs as a passive node.
  • Сardano-db-sync package. This acts as a library for database support and connects to the Cardano node.

Alternatively, you can run these components using Docker: the cardano-db-sync repository contains a docker-compose.yml file, which lets you start the node, Postgres, and db-sync with a single command:

docker-compose up

How to set up and access the database

  1. You will first need to install PostgreSQL.
  2. Create the database and manage schemas.
  3. Build and run the cardano-node and cardano-db-sync.

Useful PostgreSQL tutorials:

How to get data from the tables

On-chain data is stored in tables the mapping of which is structured (normalized) in a way that avoids data duplication. However, this also means that to extract certain data (like transaction information, for example), you will need to join multiple tables in a query.

To show all tables, run this command:

\dt

To show details about a specific table:

\d+ TABLE_NAME

Use the queries from the section below to get the blockchain data you are looking for.

Using DB management tools

Another convenient way to explore database tables is to use a DB management tool like https://www.pgadmin.org/.

This tool allows browsing the tables and their definitions (like foreign key dependencies) or developing and executing complex queries.

Follow the getting started instructions to set everything up. Now you can use the queries listed below to get the blockchain data you are looking for.

Useful queries

You can find a list of useful queries here.

© IOHK 2015 - 2021


Cardano Logo

Cardano is an open-source project.

Cardano is a software platform ONLY and does not conduct any independent diligence on, or substantive review of, any blockchain asset, digital currency, cryptocurrency or associated funds. You are fully and solely responsible for evaluating your investments, for determining whether you will exchange blockchain assets based on your own judgement, and for all your decisions as to whether to exchange blockchain assets with Cardano. In many cases, blockchain assets you exchange on the basis of your research may not increase in value, and may decrease in value. Similarly, blockchain assets you exchange on the basis of your research may fall or rise in value after your exchange.

Past performance is not indicative of future results. Any investment in blockchain assets involves the risk of loss of part or all of your investment. The value of the blockchain assets you exchange is subject to market and other investment risks