Links

Alchemy to PostgreSQL

Send Messages from Alchemy to a PostgreSQL database via the Buildable SDK

Overview

In this example, we’re going to use the Buildable SDK to send contract address activity data to a PostgreSQL database in 2 easy steps:
  1. 1.
    Create an Alchemy Connection in Buildable
  2. 2.
    Send address activity to a PostgreSQL database
For this example, you'll need a few things:
  • A Buildable account and Buildable Secret Key
  • An Alchemy account and Authentication keys
  • A PostgreSQL database

Step 1: Create an Alchemy Connection

To grab events from Alchemy, simply create a Connection in Buildable:
  1. 1.
    Click on the Connections tab
  2. 2.
    Click + New
  3. 3.
    Select the Alchemy option
  4. 4.
    Securely add your Alchemy credentials:
    1. 1.
      Notify Auth Token
    2. 2.
      App ID
    3. 3.
      Network: ETH_MAINNET
    4. 4.
      Comma-separated addresses to track
  5. 5.
    Click Connect to establish the connection
  6. 6.
    Select the ADDRESS_ACTIVITY event
  7. 7.
    Click Subscribe

Step 2: Send address activity to PostgreSQL

Connect to PostgreSQL

To connect to your PostgreSQL database, take a look at the Official KnexJS Documentation.
const database = await knex({
client: 'postgresql',
connection: {
host: process.env.POSTGRESQL_HOST,
user: process.env.POSTGRESQL_USERNAME,
password: process.env.POSTGRESQL_PASSWORD,
database: process.env.POSTGRESQL_DATABASE,
port: process.env.POSTGRESQL_PORT
}
});

Listen to Messages from Alchemy

Install the Node SDK
> npm install @buildable/messages
Create an Environment Variable
Add your Buildable Secret Key values in a .env file
Create your Listener
Listen for the messages that Alchemy will emit to Buildable
const EVENT_NAME = "ADDRESS_ACTIVITY";
const listenerConfig = {
platform: "alchemy",
label: "alchemy-app", // Connection name
txKey: "postgresql.record.created",
};
async function main() {
// Listen to address activity message
client.on(EVENT_NAME, async ({ payload, event }) => {
// Send data to a PostgreSQL database...
}, listenerConfig)
}
main().catch(console.error);

Send data to PostgreSQL

Each ADDRESS_ACTIVITY event from Alchemy contains:
  1. 1.
    network - The network of the activity
  2. 2.
    activity - An array of objects with address activity
We’re going to select specific data from the activity records and add it to a PostgreSQL table named crypto_address_activity that contains the following columns:
Column
Description
Datatype
network
Network
string
from
From contract address
string
to
To contract address
string
amount
The amount of the crypto asset
string
asset
The crypto asset
string
// Listen to address activity message
client.on(EVENT_NAME, async ({ payload, event }) => {
const { network, activity } = payload;
console.log(`Sending ${EVENT_NAME} data to PostgreSQL...`);
// Create DB records
const records = activity.map((record) => ({
network,
from: record.fromAddress,
to: record.toAddress,
amount: record.value.toString(),
asset: record.asset
}));
// Insert records into PostgreSQL
await database("crypto_address_activity").insert(records);
return records;
}, listenerConfig)

You're done! Here's the final Code

const knex = require("knex");
const { createClient } = require("@buildable/messages");
// Create Buildable Client
const client = createClient(process.env.BUILDABLE_SECRET_KEY);
// Message name to listen to
const EVENT_NAME = "ADDRESS_ACTIVITY";
// PostgreSQL table name to insert records into
const POSTGRESQL_TABLE_NAME = "crypto_address_activity";
const listenerConfig = {
platform: "alchemy",
label: "alchemy-app", // Connection name
txKey: "postgresql.record.created",
};
async function main() {
const database = await knex({
client: 'postgresql',
connection: {
host: process.env.POSTGRESQL_HOST,
user: process.env.POSTGRESQL_USERNAME,
password: process.env.POSTGRESQL_PASSWORD,
database: process.env.POSTGRESQL_DATABASE,
port: process.env.POSTGRESQL_PORT
}
});
// Listen to address activity message
client.on(EVENT_NAME, async ({ payload, event }) => {
const { network, activity } = payload;
console.log(`Sending ${EVENT_NAME} data to PostgreSQL...`);
// Create DB records
const records = activity.map((record) => ({
network,
from: record.fromAddress,
to: record.toAddress,
amount: record.value.toString(),
asset: record.asset
}));
// Insert records into PostgreSQL
await database(POSTGRESQL_TABLE_NAME).insert(records);
return records;
}, listenerConfig)
}
main().catch(console.error);