E-commerce

FaunaDB is a modern database that combines the capability of maintaining data integrity, typical of relational databases, with the scale and flexibility of non-relational systems. Inspired by the Calvin protocol, FaunaDB guarantees strong consistency in a multi-region environment — a core tenet since inception.

The current tutorial showcases FaunaDB’s correctness guarantees upon the execution of concurrent distributed transactions, in the context of an E-commerce scenario.

For further information on the multiple consistency levels supported by FaunaDB, see Isolation levels.

Requirements

In order to run the following query snippets, you need to setup a FaunaDB account and install the FaunaDB Shell on your computer. You can create a free FaunaDB account in seconds by signing up. The FaunaDB Shell can be installed using NPM or alternatively, if you are using a Mac, you can use Homebrew.

Set up

Create the database

The first step is to create a database where all of the queries are going to be executed. In order to do so, login to your FaunaDB account using the FaunaDB Shell:

fauna cloud-login

You are prompted for your FaunaDB credentials, where you need to enter the email you used for signing up, and your password:

Email: email@example.com
Password: **********

Once you are logged in, issue the following command for creating a database called ecommerce-tutorial:

fauna create-database ecommerce-tutorial

Next, start a shell session for the new ecommerce-tutorial database with following command:

fauna shell ecommerce-tutorial

All of the query snippets going forward should be execute within a shell session.

Create the schema

The data model represents a simple E-commerce database through following collections: customers, products, warehouses, and orders.

The data model:

The E-commerce data model"

A collection is a group of data categorized within a database. If you are familiar with traditional databases, you can think of a collection as a table without a structured schema. Data within a collection usually has a common structure, but this is not required.

FaunaDB implements a schema-free paradigm. This means that no structure is provided when creating a collection, but just a name for identifying it.

To create the collections for the E-commerce database, execute the following queries in the shell:

CreateCollection({name: "warehouses"});
CreateCollection({name: "products"});
CreateCollection({name: "customers"});
CreateCollection({name: "orders"});

In order to access the data within a collection, we need to create a collection index. An index is a database entity that allows the retrieval of documents by attributes other than their refs. A collection index, in this case, returns all documents for a given collection.

A reference, or ref for short, is a pointer to a document within the database. A ref encodes the collection that it belongs to, along with the target document’s unique id.

Run the following queries in the shell to create the collection indexes:

CreateIndex({"name": "all_warehouses", "source": Collection("warehouses")});
CreateIndex({"name": "all_products", "source": Collection("products")});
CreateIndex({"name": "all_customers", "source": Collection("customers")});
CreateIndex({"name": "all_orders", "source": Collection("orders")});

With the collections and their corresponding indexes in place, we are now able to insert and retrieve data from them.

Insert initial data

With the schema ready it’s time to insert the initial data into the database. The data is inserted into collections into the form of documents.

Documents are single, changeable records within a FaunaDB database. If you’re familiar with other database systems, you can think of a document as a row, or record.

For creating the initial documents for the warehouses collection, execute following query in the shell:

Do(
  // Create Warehouse 1 document
  Create(
    Ref(Collection("warehouses"), "1"), {
      data: {
        "name": "East",
        "address": {
          "street": "13 Pierstorff Drive",
          "city": "Washington",
          "state": "DC",
          "zipCode": "20220"
        }
      }
    }
  ),
  // Create Warehouse 2 document
  Create(
    Ref(Collection("warehouses"), "2"), {
      data: {
        "name": "Central",
        "address": {
          "street": "7529 Melrose Circle",
          "city": "Dallas",
          "zipCode": "75205",
          "state": "TX"
        }
      },
    }
  ),
  // Create Warehouse 3 document
  Create(
    Ref(Collection("warehouses"), "3"), {
      data: {
        "name": "Central",
        "address": {
          "street":"4 Elka Drive",
          "city": "Tacoma",
          "zipCode": "98424",
          "state":"WA"
        },
      }
    }
  )
)

Issue the following query in order to see the data that was inserted above:

Map(
  Paginate(Match(Index("all_warehouses"))),
  Lambda("nextRef", Get(Var("nextRef")))
);

Next, execute the following query for populating the products collection:

Do(
  // Create Product 1 document
  Create(
    Ref(Collection("products"), "1"), {
      data: {
        "name": "Cup",
        "description": "Translucent 9 Oz",
        "price": 6.90,
        "quantity": 100,
        "warehouse": Ref(Collection("warehouses"), "2"),
        "backorderLimit": 5,
        "backordered": false
      }
    }
  ),
  // Create Product 2 document
  Create(
    Ref(Collection("products"), "2"), {
      data: {
        "name": "Beef Cheek",
        "description": "Fresh",
        "price": 5.28,
        "quantity": 100,
        "warehouse": Ref(Collection("warehouses"), "3"),
        "backorderLimit": 10,
        "backordered": false
      }
    }
  ),
  // Create Product 3 document
  Create(
    Ref(Collection("products"), "3"), {
      data: {
        "name": "Pizza",
        "description": "Frozen Cheese",
        "price": 4.07,
        "quantity": 100,
        "warehouse": Ref(Collection("warehouses"), "1"),
        "backorderLimit": 15,
        "backordered": false
      }
    }
  )
);

List recently created products with the following query:

Map(
  Paginate(Match(Index("all_products"))),
  Lambda("nextRef", Get(Var("nextRef")))
);

Last, issue the following query for creating the initial customers documents:

Do(
  // Create Customer 1 document
  Create(
    Ref(Collection("customers"), "1"), {
      data: {
        "firstName": "Auria",
        "lastName": "Osgardby",
        "address": {
          "street": "87856 Mendota Court",
          "city": "Idaho Falls",
          "state": "ID",
          "zipCode": "83405"
        },
        "telephone": "208-346-0715",
        "creditCard": {
          "network": "Visa",
          "number": "4556781272473393"
        }
      }
    }
  ),
  // Create Customer 2 document
  Create(
    Ref(Collection("customers"), "2"), {
      data: {
        "firstName": "Skipper",
        "lastName": "Scanes",
        "address": {
          "street": "72 Waxwing Terrace",
          "city": "Colorado Springs",
          "state": "CO",
          "zipCode": "80925"
        },
        "telephone": "719-872-8799",
        "creditCard": {
          "network": "Visa",
          "number": "4916112310613672"
        }
      }
    }
  ),
  // Create Customer 3 document
  Create(
    Ref(Collection("customers"), "3"), {
      data: {
        "firstName": "Ardith",
        "lastName": "Probert",
        "address": {
          "street": "5 Troy Trail",
          "city": "Fairbanks",
          "state": "AK",
          "zipCode": "99790",
        },
        "telephone": "907-949-4470",
        "creditCard": {
          "network": "Visa",
          "number": "4532636730015542"
        }
      }
    }
  )
)

And retrieve all customers documents with the following query:

Map(
  Paginate(Match(Index("all_customers"))),
  Lambda("nextRef", Get(Var("nextRef")))
);

The orders collection remains empty for now, its documents are going to be created when going through the use cases, coming next.

A word on Id usage

It’s important to notice that, in the queries above, we are providing a hard-coded Id when creating the documents. This is being done to easily establish the relationships between the different documents in the example. When working in a real scenario, make sure to use the NewId function instead. The NewId function generates a unique, valid Id across the entire cluster.

It is also possible to let the Create function automatically generate a valid Id for you. This can be done by providing only the Collection part of the Ref as the first argument instead.

For further information, see the reference for the Create and NewId functions.

Create `submit_order` function

As a last step, let’s implement a user-defined function for submitting orders.

A user-defined function accepts a set of arguments, executes a series of queries and outputs a result.

The submit_order function ensures that there is enough stock for the requested products, decreases the stock quantity if appropriate, updates their backordered status if necessary, and creates a new order. All of these operations are executed in a transactional fashion.

Run the following query in the shell for creating the submit_order function:

CreateFunction(
  {
    "name": "submit_order",
    "body": Query(
      Lambda(["customerId", "products"],
        // 1- Get Customer and Products
        // The first step is to make sure that documents exist within the
        // database for the given parameters. Therefore, we try to get
        // the Customer and all of the Products for the given Ids. If
        // they exist, we bind them to variables using the Let function
        // in order to make them available within the scope of the
        // function.
        Let(
          {
            "customer": Get(Ref(Collection("customers"), Var("customerId"))),
            "products":
              Map(
                Var("products"),
                Lambda("requestedProduct",
                  Let(
                    {
                      "product": Get(Ref(
                        Collection("products"),
                        Select(
                          "productId",
                          Var("requestedProduct")
                        )
                      ))
                    },
                    // Build up a new temporal product object containing
                    // the data given as parameter together with the
                    // data retrieved from the database.
                    {
                      "ref": Select("ref", Var("product")),
                      "price": Select(["data", "price"], Var("product")),
                      "currentQuantity": Select(
                        ["data", "quantity"],
                        Var("product")
                      ),
                      "requestedQuantity": Select(
                        ["quantity"],
                        Var("requestedProduct")
                      ),
                      "backorderLimit": Select(
                        ["data", "backorderLimit"],
                        Var("product")
                      )
                    }
                  )
                )
              )
          },
          Do(
            // 2- Check if there's enough stock
            // Next, we need to verify if there is enough stock for the
            // requested products. To do so, we evaluate all of the
            // requested products and compare their requested quantity
            // value against the current quantity value. When there is
            // not enough stock for any of the products, we print a
            // message and cancel the whole transaction with the Abort
            // function.
            Foreach(Var("products"),
              Lambda("product",
                If(
                  LTE(
                    Select("requestedQuantity", Var("product")),
                    Select("currentQuantity", Var("product"))
                  ),
                  Var("product"),
                  Abort(Concat([
                    "Stock quantity for Product [",
                    Select(["ref", "id"], Var("product")),
                    "] not enough – requested at [",
                    ToString(Time("now")),
                    "]"
                  ]))
                )
              )
            ),
            // 3- Update products stock
            // Then, we need to update the product stock quantity
            // accordingly. To do this, we update each product document
            // through the Update function subtracting the requested
            // quantity from its current quantity.
            Foreach(Var("products"),
              Lambda("product",
                Update(
                  Select("ref", Var("product")), {
                    data: {
                      "quantity": Subtract(
                        Select("currentQuantity", Var("product")),
                        Select("requestedQuantity", Var("product"))
                      )
                    }
                  }
                )
              )
            ),
            // 4- Update backordered status
            // Moving forward, we verify if the backordered status needs
            // to be updated. For that, we check if the updated stock
            // quantity is lower than the backorderLimit threshold and
            // set the backordered flag to true if so.
            Foreach(Var("products"),
              Lambda("product",
                If(
                  LTE(
                    Subtract(
                      Select("currentQuantity", Var("product")),
                      Select("requestedQuantity", Var("product"))
                    ),
                    Select("backorderLimit", Var("product"))
                  ),
                  Update(
                    Select("ref", Var("product")), {
                      data: {
                        "backordered": true
                      }
                    }
                  ),
                  Var("product")
                )
              )
            ),
            // 5- Create Order
            // Last, we create a new Order document with the provided
            // and retrieved data. As this is the last query to be
            // executed, the function will output the newly created
            // Order as result.
            Let(
              {
                "productsLine":
                  // Build up the Order products line object from the
                  // products variable.
                  Map(
                    Var("products"),
                    Lambda("product",
                      {
                        "product": Select("ref", Var("product")),
                        "quantity": Select(
                          "requestedQuantity", Var("product")
                        ),
                        "price": Select("price", Var("product"))
                      }
                    )
                  )
              },
              Create(
                Collection("orders"), {
                  data: {
                    "customer": Select("ref", Var("customer")),
                    "line": Var("productsLine"),
                    "status": "processing",
                    "creationDate": Time("now"),
                    "shipDate": null,
                    "shipAddress": Select(
                      ["data", "address"],
                      Var("customer")
                    ),
                    "creditCard": Select(
                      ["data", "creditCard"],
                      Var("customer")
                    )
                  }
                }
              )
            )
          )
        )
      )
    )
  }
);

The submit_order function leverages many of the Fauna Query Language (FQL) features. In order to get a deeper understanding of how this function is built, see the Fauna Query Language reference.

While not a general-purpose programming language, FQL provides much of the functionality expected from one. It allows for complex, precise manipulation and retrieval of data stored within FaunaDB.

Use cases

1. Submit a simple order

First, we start by submitting an order for products with enough stock. In order to do so, let’s execute the following query in the shell:

Call(
  Function("submit_order"),
    "1",
    [
      Object({
        "productId": "1",
        "quantity": 10
      }),
      Object({
        "productId": "2",
        "quantity": 5
      }),
      Object({
        "productId": "3",
        "quantity": 20
      })
    ]
);

As all of the requested products had enough stock, the query should create a new order and we should see output similar to the following:

{
  ref: Ref(Collection("orders"), "226581909370569219"),
  ts: 1552344197540000,
  data: {
    customer: Ref(Collection("customers"), "1"),
    line: [
      {
        product: Ref(Collection("products"), "1"),
        quantity: 10,
        price: 6.90
      },
      {
        product: Ref(Collection("products"), "2"),
        quantity: 5,
        price: 5.28
      },
      {
        product: Ref(Collection("products"), "3"),
        quantity: 20,
        price: 4.07
      }
    ],
    status: 'processing',
    creationDate: Time("2019-03-11T22:43:17.420997Z"),
    shipAddress: {
      street: '87856 Mendota Court',
      city: 'Idaho Falls',
      state: 'ID',
      zipCode: '83405'
    },
    creditCard: {
      network: 'Visa',
      number: '4556781272473393'
    }
  }
}

Now if we query the products collection index, we should also see that the products' quantities have been decreased accordingly. Let’s check it out by executing the following query:

Map(
  Paginate(Match(Index("all_products"))),
  Lambda("nextRef", Get(Var("nextRef")))
);

We should see that the quantities have been modified:

{
  data: [
    {
      ref: Ref(Collection("products"), "1"),
      ts: 1552344197540000,
      data: { name: 'Cup',
        description: 'Translucent 9 Oz',
        price: 6.90,
        quantity: 90,
        warehouse: Ref(Collection("warehouses"), "2"),
        backorderLimit: 5,
        backordered: false
      }
    },
    {
      ref: Ref(Collection("products"), "2"),
      ts: 1552344197540000,
      data: {
        name: 'Beef Cheek',
        description: 'Fresh',
        price: 5.28,
        quantity: 95,
        warehouse: Ref(Collection("warehouses"), "3"),
        backorderLimit: 10,
        backordered: false
      }
    },
    {
      ref: Ref(Collection("products"), "3"),
      ts: 1552344197540000,
      data: {
        name: 'Pizza',
        description: 'Frozen Cheese',
        price: 4.07,
        quantity: 80,
        warehouse: Ref(Collection("warehouses"), "1"),
        backorderLimit: 0,
        backordered: false
      }
    }
  ]
}

2. Submit an order which affects `backordered` status

Next, we try to submit a new order which should affect the backordered status of the requested product. Every product has a backorderLimit property, if after submitting an order the product’s resulting stock quantity is below that threshold, then the backordered status in the product should be set to true. This means that the product is about to be out of stock and new items should be purchased.

Run following query in the shell for trying this case:

Call(
  Function("submit_order"),
    "1",
    [
      Object({
        "productId": "2",
        "quantity": 90
      })
    ]
);

As a result, the order should be created successfully:

{
  ref: Ref(Collection("orders"), "226581909370569219"),
  ts: 1552344197540000,
  data: {
    customer: Ref(Collection("customers"), "1"),
    line: [
      {
        product: Ref(Collection("products"), "2"),
        quantity: 90,
        price: 6.90
      }
    ],
    status: 'processing',
    creationDate: Time("2019-03-11T22:43:17.420997Z"),
    shipAddress: {
      street: '87856 Mendota Court',
      city: 'Idaho Falls',
      state: 'ID',
      zipCode: '83405'
    },
    creditCard: {
      network: 'Visa',
      number: '4556781272473393'
    }
  }
}

And then, if we query the products once again, we should see that the requested product is now in backordered status, since its current stock quantity is below the backorderedLimit threshold:

{
  data: [
    {
      ref: Ref(Collection("products"), "1"),
      ts: 1552344197540000,
      data: { name: 'Cup',
        description: 'Translucent 9 Oz',
        price: 6.90,
        quantity: 90,
        warehouse: Ref(Collection("warehouses"), "2"),
        backorderLimit: 5,
        backordered: false
      }
    },
    {
      ref: Ref(Collection("products"), "2"),
      ts: 1552344197540000,
      data: {
        name: 'Beef Cheek',
        description: 'Fresh',
        price: 5.28,
        quantity: 5,
        warehouse: Ref(Collection("warehouses"), "3"),
        backorderLimit: 10,
        backordered: true
      }
    },
    {
      ref: Ref(Collection("products"), "3"),
      ts: 1552344197540000,
      data: {
        name: 'Pizza',
        description: 'Frozen Cheese',
        price: 4.07,
        quantity: 80,
        warehouse: Ref(Collection("warehouses"), "1"),
        backorderLimit: 0,
        backordered: false
      }
    }
  ]
}

3. Submit an order with insufficient stock

Now let’s try to submit an order for a product which exceeds its current stock. Execute the following query:

Call(
  Function("submit_order"),
    "1",
    [
      Object({
        "productId": "1",
        "quantity": 150
      })
    ]
);

As there isn’t enough stock quantity for the requested product, we should see an error result containing following message:

'Stock quantity for Product [1] not enough – requested at [2019-03-11T23:04:18.066135Z]'

4. Submit two orders with insufficient stock, at the same time

Last, let’s try to submit two orders for the same product at the same time, which together exceed the current stock. There is only enough stock only for one of the two orders. This means one of them should succeed and the other should fail.

In order to go through this use case in the most realistic way possible, you need to be able to simulate two different users running a query at the same time. This could be done by executing the corresponding queries simultaneously in two different terminal windows in one single computer. Please check the documentation of your terminal of choice on how to run two commands in different windows at the same time.

In one terminal window, prepare the following query:

Call(
  Function("submit_order"),
    "1",
    [
      Object({
        "productId": "1",
        "quantity": 70
      })
    ]
);

And in another terminal, prepare the following query:

Call(
  Function("submit_order"),
    "2",
    [
      Object({
        "productId": "1",
        "quantity": 50
      })
    ]
);

Then execute both of them at the same time.

You should see in one of the terminals that the order has been created successfully:

{
  ref: Ref(Collection("orders"), "226581909370569219"),
  ts: 1552344197540000,
  data: {
    customer: Ref(Collection("customers"), "1"),
    line: [
      {
        product: Ref(Collection("products"), "1"),
        quantity: 70,
        price: 6.90
      }
    ],
    status: 'processing',
    creationDate: Time("2019-03-11T22:43:17.420997Z"),
    shipAddress: {
      street: '87856 Mendota Court',
      city: 'Idaho Falls',
      state: 'ID',
      zipCode: '83405'
    },
    creditCard: {
      network: 'Visa',
      number: '4556781272473393'
    }
  }
}

And in the other terminal, you should see an error message indicating there isn’t enough stock for performing the operation:

'Stock quantity for Product [1] not enough – requested at [2019-03-11T22:43:17.066135Z]'

If you look closer at the order creationDate and the time prompted in the error message, you find that both time values differ only by fractions of a second. This demonstrates that, despite being executed at the same time, the two queries have been effectively processed in a serialized way and only one of them has managed to modify the records stored in the database.

Conclusions

In this tutorial, we have seen how FaunaDB keeps data correctness among simultaneous transactions in a distributed environment. This, combined with the power of the Fauna Query Language, allows you to safely build comprehensive use cases directly at the database level, which otherwise would end up being implemented — with most databases — at the application level through multiple queries and locks.

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve! documentation@fauna.com

Thank you for your feedback!

E-commerce

Overview

Requirements

Set up

Create the database

Create the schema

Insert initial data

A word on Id usage

Create submit_order function

Use cases

1. Submit a simple order

2. Submit an order which affects backordered status

3. Submit an order with insufficient stock

4. Submit two orders with insufficient stock, at the same time

Conclusions

Create `submit_order` function

2. Submit an order which affects `backordered` status