This content originally appeared on DEV Community 👩💻👨💻 and was authored 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, 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:
- Network: The network of the activity
- 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.
This content originally appeared on DEV Community 👩💻👨💻 and was authored by Esther Agbaje
Esther Agbaje | Sciencx (2022-10-14T12:51:37+00:00) Send contract address messages from Alchemy to a PostgreSQL database. Retrieved from https://www.scien.cx/2022/10/14/send-contract-address-messages-from-alchemy-to-a-postgresql-database/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.