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:

1. The Let creates bindings to the variables latest and counter. The variable Counter in a real application would be the counter value of the ledger that is passed by the client into the query.  
2. The Let then executes an ‘If’ query to check that counter and latest are the same value.
3. If counter and latest are the same value, the if statement will evaluate to true and  create the class instance and returns the counter.
4. If counter and latest 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.