Send contract address messages from Alchemy to a PostgreSQL database

With nearly 100,000 transactions processed on Ethereum every second, you'll likely want to securely store details and activities on your web3 contract address in a database.

That's exactly what you will learn in this article! We'll go over how to send contract or wallet address activity data to a PostgreSQL database using Buildable's Node SDK.

Prerequisites

Before we get started, you'll need the following to complete this tutorial:

• A Buildable account
• An Alchemy account
• A PostgreSQL database
• A contract address

Creating an Alchemy connection

Inside your Buildable account, navigate to the Connection tab 
and hit the + New button. You'll see a list of 3rd party apps, and from this list, select Alchemy.

Fill in your Alchemy credentials and hit Connect.

Next, we subscribe to the required event. For this tutorial, let's subscribe to. "ADDRESS_ACTIVITY" event.

If you do not have a Buildable account yet, get started for free here.

Setting up your Buildable Secret Key

Now, we need to create a Buildable secret key that we will use in our NodeJS project.

To generate a secret key, head over to the Settings page. From the left panel, select Secret Keys and create a new one.

Copy and save this secret key, as we'll be using it shortly.

Adding environmental variables

It's now time to add in our environmental variables. In your NodeJS project, create a .env file and define the environmental variables used in our app.

Do you have the secret key you created earlier in Buildable? Go ahead and paste it in as your BUILDABLE_SECRET_KEY.

You'll also want to add the environmental variables from your PostgreSQL database.

Creating a table in our PostgreSQL database

Since we'll be inserting activity records into a table in our PostgreSQL database, let's go ahead to create this table. Call the table crypto_address_activity.

Each ADDRESS_ACTIVITY event from Alchemy returns the following payload:

1. Network: The network of the activity
2. Activity: An array of objects with address activity

So, right in our table, we add the following columns:

• network: The network of the contract address
• from: The from contract address
• to: The to contract address
• amount: The amount of the crypto asset
• asset: The crypto asset

Connecting the database to your project

In our NodeJS project, we'll connect our PostgreSQL database using Knex.

So, let's install knex and postgress by running npm install knex --save and npm install pg.

Now, create an index.js file and connect using the following code:

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
  }
});

Listening to messages from Alchemy

We need to listen for the messages that Alchemy will emit to Buildable. Of course, to help us achieve this, let's install Buildable's Node SDK into our app by running npm install @buildable/messages.

Now, in our code, we can do the following:

• Create a Buildable client that listens to messages emitted from Alchemy
• Specify the message that should be listened to
• Define the PostgreSQL table to insert this message events into

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 MESSAGE_NAME = "ADDRESS_ACTIVITY"; 

// PostgreSQL table name to insert records into
const POSTGRESQL_TABLE_NAME = "crypto_address_activity";

Finally, we configure a listener that listens to the messages from Alchemy.

const listenerConfig = {
platform: "alchemy",
label: "alchemy-app", // Connection name
txKey: "postgresql.record.created",
};

Inserting records into PostgreSQL database

At this point, we're getting the ADDRESS_ACTIVITY event from Alchemy, and we need to insert these data as records into our PostgreSQL database.

// Listen to address activity message
client.on(MESSAGE_NAME, async ({ payload, event }) => {
  const { network, activity } = payload;

  // 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)

Now, we've completed the setup and you can be confident all your wallet address activity will get sent to your database as below 🎉

Here's the final code for reference:

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 MESSAGE_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(MESSAGE_NAME, async ({ payload, event }) => {
    const { network, activity } = payload;

    // 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);

Simplify your Alchemy streaming process with Buildable

With this tutorial completed, you'll begin to see details of your contract address activity populate in your PostgreSQL database.

If you want to subscribe to multiple events from multiple wallet addresses, that's no problem. Buildable offers you the flexibility to do that as well. Now, go ahead and explore all you want!

Have thoughts or questions? Please reach out via Twitter or join our Discord community.