PostgreSQL integration

The PostgreSQL integration is used to mirror onchain data to a PostgreSQL database. Data is automatically inserted as it's produced by the chain, and it's invalidated in case of chain reorganizations.

• This integration can be used to populate SQL tables with data from one or more networks and smart contracts.
• Easily integrate with AI libraries such as Langchain.
• Change how tables are joined or queried without re-indexing.

Installation#

apibara plugins install sink-postgres

Configuration#

• connectionString: string: URL used to connect to your PostgreSQL database.
• tableName: string: table where data will be inserted. The table must exist and it must have a schema compatible with the data returned by the transform step.
• noTls: boolean: disable TLS when connecting to the server.
• tlsCertificate: string: path to the PEM-formatted X509 TLS certificate.
• tlsDisableSystemRoots: boolean: disable system root certificates.
• tlsAcceptInvalidCertificates: boolean: accept invalid TLS certificates.
• tlsAcceptInvalidHostnames: boolean: disable hostname validation.
• tlsUseSni: boolean: use Server Name Identification (SNI).

Table schema#

The target table schema must be compatible with the data returned by the transformation step. Batch data is converted to PostgreSQL records using the json_populate_recordset function. Additionally, the PostgreSQL integration requires a _cursor column in the table to keep track of each batch's cursor, so that data can be invalidated in case of chain reorganizations.

Provider-specific setup#

Supabase#

You have two options:

• disable TLS by adding the --no-tls=true flag when running your indexer. This isn't recommended for production.
• download the SSL certificate from your Supabase dashboard (Settings => Database) and convert it to PEM.

After downloading the .crt certificate from your dashboard, you will have a .crt file in your download folder. This file will be named something like prod-ca-2021.pem. Convert it to PEM using, for example, the openssl CLI tool.

openssl x509 -in prod-ca-2021.crt -out prod-ca-2021.pem -outform PEM

Use the --tls-certificate (or sinkOptions.tlsCertificate in your script) flag to point to the PEM certificate path.

Neon#

Use the provided connection string.