Developer Community

Send contract address messages from Alchemy to a PostgreSQL database

OCTOBER 14, 2022 • 4 MIN READ
Written by
Esther Agbaje

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, do not worry, you can get started for free.

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 the 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 Postgres by running `npm install knex --save` and `npm install pg`.

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


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

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


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.


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:


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.