PostgreSQL Database Integration

This package provides components required to sync indexed data to a PostgreSQL instance in Flair indexer.

Installation

1. Make sure you have created your indexing cluster as described in Getting Started.

2. Install the PostgreSQL solution package:

pnpm install @flair-sdk/solution-database-postgresql

3. Define your schemas based on your entities. For example, if you have a Swap entity, you can define a schema as follows:

# ./src/schemas/Swap.yml
---
Swap:
  entityId: STRING
  entityUpdatedAt: INT64
  chainId: INT64
  poolAddress: STRING
  from: STRING
  to: STRING
  amount: INT256
  amountUSD: DOUBLE
  someObjectOrArray: STRING

3. Add the PostgreSQL solution to your manifest.yml.mustache usually created from starter-boilerplate repository:

# ./manifest.yml.mustache
# ...
solutions:
  - source: '@flair-sdk/solution-database-postgresql'
    config:
      schema:
        - src/schemas/*.yml
      connectionUri: 'jdbc:postgresql://PUT_DB_HOST_HERE/PUT_DB_NAME_HERE'
      username: 'PUT_DB_USERNAME_HERE'
      password: '{{{ postgresqlPassword }}}'
      tableNamePrefix: entity_

The "postgresqlPassword" here is a variable that is replaced by mustache locally. Refer to boilerplate repo for more details.

4. Add postgresqlPassword to your config.json usually created from starter-boilerplate repository:

// ./config.prod.json
{
  "cluster": "prod",
  "namespace": "my-project",
  // ...
  "postgresqlPassword": "{{ secret(\"postgresql.password\") }}"
}

The "postgresql.password" is an actual secret value set using flair secret command, so that it is not exposed in the repository.

5. Deploy your cluster and check the status of real-time live syncing.

pnpm generate-and-deploy
pnpm flair logs -w -t component=enricher

6. (OPTIONAL) If there are any historical previously indexed data, you can sync them using:

pnpm flair script database-manual-full-sync