Using Ethereum Logic Apps to push ledger data into to a MySQL or PostgreSQL database

Hi welcome to another episode of Block
Talk I’m Chris and today I’m gonna walk you through a couple of demos that I
think highlight some interesting patterns that we see from our partners
and customers oftentimes we get questions from our partners and
customers asking how do I take ledger information how do I take my ledger data
and push that to a back-end system an existing back-end system like maybe a
SQL database in today’s example we’re going to show you two different
variations on the same theme we’re gonna show you how to take ledger data and
push that into a Postgres database or push that data into a MySQL database
their variations on the theme in that the Postgres example is going to look at
contract events itself so amid events from a contract the MySQL version is
going to actually monitor a Service Bus or Event Grid and then based on those events, send the information to a MySQL database let’s go ahead and look
at the Logic App Logic Apps that we put together to create this example so you
see here on the screen I have my Postgres example and it’s super simple
super straightforward I have a theory on Logic App that you can get from the
Logic App marketplace that’s connected to my ledger it’s monitoring that ledger
for smart contract events again these are emit events in my solidity contract
when I get one of those events I’m going to go ahead and go query that contract
get contract state and then I’m going to use an Azure function to take that state
and turn it into a SQL statement to push that into a Postgres database now
let’s look at the my SQL version it’s the exact same flow but again with a
slightly different trigger patterns meaning instead of just looking at
contract and contract emit events I can also look at things like a Service Bus
or an Event Grid this is fairly common use case for back-end systems that just
publish data to a Service Bus and expect other applications to take action on it
in this example I’m monitoring the service bus from Azure Blockchain
Workbench and I’m parsing that service bus message to make
it’s a message I’m interested in again this is all optional from a user
standpoint but once I do find a message I’m interested in we do the exact same
pattern we did with a Postgres example we go query a deployed contract get all
of its current state and then push that information as a SQL statement into a
MySQL database so let’s go ahead and let’s let’s work through that demo just
a little bit so you can see what’s going on here here you have I’m using
Workbench so that it’s sort of easier nicer to see that the UI my interaction
with a contract because I’m using the logic apps I’m not really tied to
Workbench I can use any etherium based RPC endpoint to contract in and monitor
our contact and monitor but let’s go ahead and take a couple of actions on
this contract I have a deployed contract I will take one action here which is
sort of the first contract function I will just go ahead and transfer
responsibilities of an asset now when I hit or when I complete this action here
and I hit take action and submit a number of things are happening right so
right now that contract and those events are being submitted to the blockchain
the event is being mined and added to the block that information comes back
here but then what’s also going on right now in the background is these Logic App
flows are being triggered I’m going through and looking getting notified
when an event happened getting that information and pushing it to Postgres
or MySQL let’s go ahead and look for example now in our Postgres database so
let me just go ahead and select everything from that ledger or from that
account let’s go ahead and query our database and you see here I’ve added a
single road to this database but I’ve added a row based on the contract
information right I have the participating parties I also have state
right so I’ve gone from my initial state into my first state it means I have
transferred responsibility to a new party
let’s go ahead and look now at the my SQL version and it’s gonna look
exactly the same right again the sequels underlying SQL behavior is the same
I’m just using two different databases but you see here again various
counterparties various information and I’ve just entered the same state into
the database now let’s go ahead and take one more action again the scenario here
is I want to record what’s going on in my contract so back-end systems can can
do that let’s go ahead and take one last action let’s finish off this particular
contract yeah now that that’s completed let’s just go ahead and look at the MySQL example because again they’re both stood the same so we’ll just take a
look at one let’s go ahead and reissue the same query command select all from
my table and you see here I have that second entry right so now I have sort of
this running history of states and running record of what’s happening so
that that was it this code and all of the setup and walk through is on our
Blockchain Dev Kit GITHUB page you can go ahead and set all this up and sort of
help build this very simple but I think powerful example thank you for watching
make sure to subscribe or follow us on Twitter at @MSFTblockchain

Leave a Reply

Your email address will not be published. Required fields are marked *