Using PostgreSQL

Using PostgreSQL with Dappetizer is really straightforward and can be set up in a few minutes.

Prerequisites

You will need the following for this guide:

• For the purposes of this guide, we will assume you already have an existing Dappetizer project. Check out our Quick Start guide to learn how to set up one.

• Docker (if you are planning on running PostgreSQL in Docker) or a full PostgreSQL instance

Setting up a local database

If you don't have a local PostgreSQL instance running, you can start one using Docker:

Unix/MacOS

docker run -d \
	--name dev-postgres \
	-e POSTGRES_PASSWORD=postgrespassword \
	-v ${HOME}/postgres-data/:/var/lib/postgresql/data \
        -p 5432:5432 \
        postgres

Installing PostgreSQL package

The PostgreSQL client package is required when connecting to the database. To install it, run

npm install pg

Configuring Dappetizer

The configuration should be changed with specific settings for connecting to PostgreSQL. To connect to your local instance, you can replace the existing database section with:

database: {
    type: 'postgres',
    host: 'localhost',
    port: 5432,
    username: 'postgres',
    password: 'postgrespassword',
    database: 'postgres',
    schema: 'indexer',
},

Notice that the username and password here are stored in plaintext. That is fine for local development, but when running Dappetizer on a server, you should use a local configuration file stored on the server (the file should not be in source control) or make use of environment variables. For example, this is how you can use environment variables:

database: {
    type: 'postgres',
    host: process.env['MY_HOST_VARIABLE'],
    port: 5432,
    username: process.env['MY_USERNAME_VARIABLE'],
    password: process.env['MY_PASSWORD_VARIABLE'],
    database: 'postgres',
    schema: 'indexer',
},

Checking the generated data

You can use any tool (like pgAdmin) to connect to the database. In this guide we will simply use Docker to run psql:

docker run -it --rm --network=host postgres psql -h localhost -U postgres

To select what blocks have been saved by the indexer, we will run a short SELECT query:

select * from indexer.block limit 10;

If the indexer has successfully connected to the database and started indexing, you should be able to see the stored blocks so far.