Tutorial: How to Create and Query a Ledger with Fauna
In the previous article, we learned how Fauna provides the ideal platform for event-driven programming by providing mission critical, ACID transactions at a global scale. This article helps you get started using Fauna for such transactions. If you are new to FaunaDB, we’d recommend reading the Fauna CRUD documentation for a background on Fauna query language, but you don’t need it to follow along. We’ll only be covering basic Fauna queries from the command line. (But stay tuned for the next tutorial in this series on how to query Fauna via a full Java application.)
Image by Semantic Scholar (https://bit.ly/2JRwreU)
In this tutorial, we will walk through manually adding ledger entries and then querying the ledger. If you just want the raw code, it’s available at this Event-Sourcing with Fauna gist.
Prerequisites
If you haven’t already, sign up for a free Fauna account.
Then, install the Fauna Shell:
$ npm install -g fauna-shell
Once installed, tell Fauna that you want to login.
$ fauna cloud-login
Enter your Fauna credentials when prompted.
Email: myemail@email.com
Password: **********
Press enter. Once you are logged in on a machine, you don’t need to log in again. Next, create the database where the ledger will live:
$ fauna create-database main_ledger
Now, open the Fauna Shell in the new database:
$ fauna shell main_ledger
We’re ready to go!
Setup the Schema
Our database schema consists of a single class called ledger. This class holds a unique ledger for each client. The following example shows the schema of a ledger entry:
'{"clientId":50,"counter":10,"type":"DEPOSIT","description":
"NEW DEPOSIT", "amount":42.11}'
First, create the ledger class. All data will be stored in a single ledger class for simplicity:
faunadb> CreateClass({ name: "ledger" })
{
"ref": Class("ledger"),
"ts": 1532019955672424,
"history_days": 30,
"name": "ledger"
}
Along with a unique client id is a counter, which is the unique id for just that client’s ledger entry. The combination of
clientId
+ counter
will ensure that every entry in the ledger is unique.Next we need to create two separate indexes. Copy and paste the following into the shell:
faunadb> CreateIndex(
{
name: "UNIQUE_ENTRY_CONSTRAINT",
source: Class("ledger"),
terms: [{ field: ["data", "clientId"] }],
values: [{ field: ["data", "counter"] }],
unique: true,
active: true
})
The first index enforces a uniqueness constraint on the ledger with the term, plus values of
clientId
and counter
. We can not add class reference to the list of values of because it would make the uniqueness constraint clientId
+ counter
+ class reference. This method would allow duplicates of entries with clientId
+ counter
.Next, enter the following in the shell:
faunadb> CreateIndex(
{
name: "ledger_client_id",
source: Class("ledger"),
terms: [{ field: ["data", "clientId"] }],
values: [{ field: ["data", "counter"], reverse:true }, { field: ["ref"] }],
unique: false,
serialized: true,
active: true
})
This index provides the lookup and reference of all the entries for a particular client sorted by the
counter
in reverse order. When sorting the ledger by counter
in reverse order, we can easily find the last entry in the ledger.You can verify the indexes were created properly by running a query to find the index:
faunadb> Get(Index("ledger_client_id"))
{
"ref": Index("ledger_client_id"),
"ts": 1531245138484000,
"active": true,
"partitions": 1,
"name": "ledger_client_id",
"source": Class("ledger"),
"terms": [
{
"field": [
"data",
"clientId"
]
}
],
"values": [
{
"field": [
"data",
"counter"
],
"reverse": true
},
{
"field": [
"ref"
]
}
],
"unique": false,
"serialized": true
}
Adding Entries to the Ledger
Fauna queries are built using one or more nested expressions. Each expression returns an expression so that they can be nested. This example walks through how to build these nested expressions.
Let’s assume the client is issuing a call to insert a ledger entry for
clientId
50 and the last entry in the ledger has a counter
of 20.The core expression to insert a ledger event would be the ‘create class’ expression. Data is the value of the class instance in json format:
Create(Class("ledger"),
{ data:
{"clientId":50,"counter":21,"type":"DEPOSIT","description":
"NEW DEPOSIT", "amount":28.19} })
Add several entries to the ledger as a starting point, updating the counter for each one.
faunadb> Create(Class("ledger"),
{ data: {"clientId":50,"counter":0,"type":
"DEPOSIT","description":"NEW DEPOSIT", "amount":28.19} })
{
"ref": Ref(Class("ledger"), "205271124881179148"),
"ts": 1532020649624717,
"data": {
"clientId": 50,
"counter": 0,
"type": "DEPOSIT",
"description": "NEW DEPOSIT",
"amount": 28.19
}
}
Notice that if we try to enter duplicate entries, the ‘create’ fails:
faunadb> Create(Class("ledger"),
{ data: {"clientId":50,"counter":0,"type":
"DEPOSIT", "description":"NEW DEPOSIT", "amount":28.19} })
Error: instance not unique
Since expressions return a data structure, all Fauna queries can be nested to select the values out of the return expressions. In this case, we want the
counter
that was saved so we can use the select to return only the counter
from the results by using a select. Essentially, the select is saying “select the data element of the array and then, from that array, select out the counter
value”:faunadb> Select(["data", "counter"], Create(Class("ledger"),{
data:
{"clientId":50,"counter":5,"type":"DEPOSIT","description":"NEW
DEPOSIT", "amount":28.19} }))
5
Finding the latest ledger entry
We can build on this core expression to create a query that only inserts the ledger entry if the
counter
is one plus the last entry. That ensures the client has the latest counter
and the events are inserted in order.Now, let’s create a query that gets the last
counter
value out of the index ledger_client_id
. The first part of this query would be to read the first page of entries out of the index with paginate. This returns a nested array of indexed entries along with the reference to the class instances, like this:faunadb> Paginate(Match(Index("ledger_client_id"), 50))
{
"data": [
[
5,
Ref(Class("ledger"), "205271417149719052")
],
[
0,
Ref(Class("ledger"), "205271124881179148")
]
]
}
Notice the entries are returned in reverse order to the
counter
value. This is because the index was created with the flag of reverse set to true. This stores the values sorted in reverse order.Next, select the
counter
from the first entry out of this two-dimensional array. You can do this by referencing the entry you want in the nested array value, similar to how it is done with other programming languages. The last parameter of 0 is the default value:faunadb> Select([0,0],
Paginate(Match(Index("ledger_client_id"), 50)), 0
)
That returns the counter of the last ledger entry or 0, for example:
5
Now, add one to that value and save it in a temporary variable
latest
, which will be used later in the query. This can be done with the Add
and Let
expressions. Let
binds values to variables for reference in later parts of the query:faunadb> Let(
{latest: Add(
Select([0,0],
Paginate(Match(Index("ledger_client_id"), 50)),0
),1)
},
Var("latest")
)
6
The second parameter to
Let
is the expression that is run after binding the variables. In this case, we simply return the variable binding. Running this query only will set the variable latest
, and then return the variable latest
which is 5 because 4 is the last counter
(4+1).Conditional Ledger Entry Creation
When inserting an entry, make sure the
counter
that is being inserted is correct. This can be done by using an ‘if’ expression. A simplified example would be:faunadb> If(Equals(20, 20),
["saved", 7],
["not_saved",9]
)
[ 'saved', 7 ]
This returns a flag indicating whether the entry was saved and the
counter
that was used; or, in an error condition, the counter
that should have been saved.[ 'not_saved', 9 ]
Any value can be returned from this array and, in this case, we want to return the
counter
that was saved:faunadb> If(
Equals(5, 5),
["saved",
Select(["data", "counter"], Create(Class("ledger"),
{ data: {"clientId":50,"counter":5,"type":
"DEPOSIT", "description":"NEW DEPOSIT", "amount":28.19} }))
],
["not_saved",6]
)
If successful, you will see:
[ 'saved', 5 ]
Or, if it failed:
[ 'not_saved', 6 ]
Putting it all together
Finally, let’s pull this all together in a single query. A powerful feature of Fauna is that this can all be combined into a single query that is executed as a single atomic operation. What it does is get the last ledger entry
counter
and check if the counter
we are adding is the expected value. Only then do we do the insert:faunadb> Let(
{latest: Add(
Select([0,0],
Paginate(Match(Index("ledger_client_id"), 50)),0
),1),
counter: 7
},
If(Equals(Var("counter"), Var("latest")),
["saved",
Select(["data", "counter"], Create(Class("ledger"),
{ data: {"clientId":50,"counter":Var("counter"),
"type": "DEPOSIT","description":"NEW DEPOSIT", "amount":28.19} }))
],
["not_saved",Var("latest")]
)
)
[ 'saved', 7]
That is a lot to process, but hang in there and go through it a couple of times! Note that the expression
Var("latest")
is used to access a variable binding. Breaking it down, here is what the major parts are doing:- The
Let
creates bindings to the variableslatest
andcounter
. The variableCounter
in a real application would be the counter value of the ledger that is passed by the client into the query. - The
Let
then executes an ‘If’ query to check thatcounter
andlatest
are the same value. - If
counter
andlatest
are the same value, the if statement will evaluate to true and create the class instance and returns thecounter
. - If
counter
andlatest
are not the same value, it returns an error.
In this example, the entry was saved, so the query returns the flag
saved
and the new counter
value to indicate that the the ledger entry was successfully saved successfully.Summary
Although these Fauna queries seem fairly complicated at first pass, they encapsulate a lot of business logic in a single transaction that would be significantly more complicated in the application tier. Most databases don’t support nested queries that allow combining reading and writing in the same transaction. Thus, performing the same business logic in the application tier would require multiple queries to the database and some type of locking to ensure that the counter is not updated after the value is read.
In this tutorial, we have outlined the core of the event-sourcing model needed to build a complete event-sourcing application with Fauna. In the next tutorial, we will take these values and use them in a complete Java application. For a sneak peak take a look at this repo.
Special thanks to my colleague Ben Edwards for helping out with writing and proofreading of this article.
If you enjoyed our blog, and want to work on systems and challenges related to globally distributed systems, and serverless databases, Fauna is hiring
Subscribe to Fauna's newsletter
Get latest blog posts, development tips & tricks, and latest learning material delivered right to your inbox.