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