Send contract address messages from Alchemy to a PostgreSQL database
Tags: web3, database, node, eventdriven
Published: 2022-10-14 12:51:37

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](https://hub.buildable.dev/use-cases/alchemy-to-postgresql) and hit **Connect**.

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


![Creating an Alchemy connection on Buildable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1t28f2t0im21rhvu24t2.gif)

> If you do not have a Buildable account yet, [get started for free here](https://messages.buildable.dev/).
> 

## 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.


![Setting up your Buildable Secret Key](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6sdm1vv36ni42pyvvy5i.gif)

## 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.


![Adding environmental variables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ooyhjrbz1w12ge710qv3.png)

## 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



![Creating a table in our PostgreSQL database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tmgi9f82p2q2fklrtsgn.gif)


## 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:

```jsx
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

```jsx
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.

```jsx
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.

```jsx
// 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 🎉


![Records in a PostgreSQL database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/evy033o2iy55ih7f6jyi.png)

Here's the final code for reference:

```jsx
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](https://twitter.com/BuildableHQ) or join our [Discord](https://discord.com/invite/47AJ42Wzys) community.