FQL v4 will be decommissioned on June 30, 2025. Ensure that you complete your migration from FQL v4 to FQL v10 by that date. Fauna accounts created after August 21, 2024 must use FQL v10. These accounts will not be able to run FQL v4 queries or access the v4 Dashboard. For more details, see the v4 EOL announcement and migration guide. Contact support@fauna.com with any questions. |
E-commerce
Fauna 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, Fauna guarantees strong consistency in a multi-region environment — a core tenet since inception.
The current tutorial showcases Fauna’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 Fauna, see Isolation levels.
This tutorial requires a Fauna account. If you don’t have a Fauna account, visit https://dashboard.fauna.com/register to register. |
You can also complete this tutorial with the Fauna Shell. All the commands work the same way in the Dashboard Shell and the command-line Fauna Shell. |
Setup
Create the database
-
Open the Fauna Dashboard
Log in to the Fauna Dashboard.
-
Create a new database
Click the NEW DATABASE link near the upper-left corner of the Dashboard. Name the new database
ecommerce-tutorial
and click the SAVE button. -
Open the Dashboard Shell
Click SHELL in the left sidebar to display the Dashboard Shell.
Create the schema
The data model represents a simple E-commerce database through the following collections:
-
customers
-
orders
-
products
-
stores
A collection is a container for documents within a database. If you are familiar with traditional databases, you can think of a collection as a table without a structured schema. Documents within a collection usually have a common structure, but this is not required. Fauna 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, copy the following query and paste it into the Shell, then press RUN QUERY to run it:
CreateCollection({name: "customers"});
CreateCollection({name: "products"});
CreateCollection({name: "orders"});
CreateCollection({name: "stores"});
With the collections 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 Fauna database. If you’re familiar with other database systems, you can think of a document as a row, or record.
-
Create the
stores
documentsCopy the following query and paste it into the Shell, and press RUN QUERY to run it:
Do( // Create Store 1 document Create( Ref(Collection("stores"), "301"), { data: { "name": "DC Fruits", "address": { "street": "13 Pierstorff Drive", "city": "Washington", "state": "DC", "zipCode": "20220" } } } ), // Create Store 2 document Create( Ref(Collection("stores"), "302"), { data: { "name": "Party Supplies", "address": { "street": "7529 Capitalsaurus Court", "city": "Washington", "state": "DC", "zipCode": "20002" } }, } ), // Create Store 3 document Create( Ref(Collection("stores"), "303"), { data: { "name": "Foggy Bottom Market", "address": { "street":"4 Floride Ave", "city": "Washington", "state": "DC", "zipCode": "20037" }, } } ) )
Optional: run the following query to list the new
stores
documents:Map( Paginate(Documents(Collection("stores"))), Lambda("storeRef", Get(Var("storeRef"))) );
-
Create the
products
documentsCopy the following query and paste it into the Shell, and press RUN QUERY to run it:
Do( // Create Product 1 document Create( Ref(Collection("products"), "201"), { data: { "name": "cups", "description": "Translucent 9 Oz, 100 ct", "price": 6.98, "quantity": 100, "store": Ref(Collection("stores"), "302"), "backorderLimit": 5, "backordered": false } } ), // Create Product 2 document Create( Ref(Collection("products"), "202"), { data: { "name": "pinata", "description": "Original Class Donkey Pinata", "price": 24.99, "quantity": 20, "store": Ref(Collection("stores"), "303"), "backorderLimit": 10, "backordered": false } } ), // Create Product 3 document Create( Ref(Collection("products"), "203"), { data: { "name": "pizza", "description": "Frozen Cheese", "price": 4.99, "quantity": 100, "store": Ref(Collection("stores"), "303"), "backorderLimit": 15, "backordered": false } } ), // Create Product 4 document Create( Ref(Collection("products"), "204"), { data: { "name": "avocados", "description": "Conventional Hass, 4ct bag", "price": 3.99, "quantity": 1000, "store": Ref(Collection("stores"), "301"), "backorderLimit": 15, "backordered": false } } ), // Create Product 5 document Create( Ref(Collection("products"), "205"), { data: { "name": "limes", "description": "Conventional, 1 ct", "price": 0.35, "quantity": 1000, "store": Ref(Collection("stores"), "301"), "backorderLimit": 15, "backordered": false } } ), // Create Product 6 document Create( Ref(Collection("products"), "206"), { data: { "name": "limes", "description": "Organic, 16 oz bag", "price": 3.49, "quantity": 50, "store": Ref(Collection("stores"), "301"), "backorderLimit": 15, "backordered": false } } ), // Create Product 7 document Create( Ref(Collection("products"), "207"), { data: { "name": "limes", "description": "Conventional, 16 oz bag", "price": 2.99, "quantity": 30, "store": Ref(Collection("stores"), "303"), "backorderLimit": 15, "backordered": false } } ), // Create Product 8 document Create( Ref(Collection("products"), "208"), { data: { "name": "cilantro", "description": "Organic, 1 bunch", "price": 1.49, "quantity": 100, "store": Ref(Collection("stores"), "301"), "backorderLimit": 15, "backordered": false } } ), // Create Product 9 document Create( Ref(Collection("products"), "209"), { data: { "name": "pinata", "description": "Giant Taco Pinata", "price": 23.99, "quantity": 10, "store": Ref(Collection("stores"), "302"), "backorderLimit": 10, "backordered": false } } ) );
Optional: run the following query to list the new
products
documents:Map( Paginate(Documents(Collection("products"))), Lambda("productRef", Get(Var("productRef"))) );
-
Create the
customers
documentsCopy the following query and paste it into the Shell, and press RUN QUERY to run it:
Do( // Create Customer 1 document Create( Ref(Collection("customers"), "101"), { data: { "firstName": "Alice", "lastName": "Appleseed", "address": { "street": "87856 Mendota Court", "city": "Washington", "state": "DC", "zipCode": "20220" }, "telephone": "208-346-0715", "creditCard": { "network": "Visa", "number": "4556781272473393" } } } ), // Create Customer 2 document Create( Ref(Collection("customers"), "102"), { data: { "firstName": "Bob", "lastName": "Brown", "address": { "street": "72 Waxwing Terrace", "city": "Washington", "state": "DC", "zipCode": "20002" }, "telephone": "719-872-8799", "creditCard": { "network": "Visa", "number": "4916112310613672" } } } ), // Create Customer 3 document Create( Ref(Collection("customers"), "103"), { data: { "firstName": "Carol", "lastName": "Clark", "address": { "street": "5 Troy Trail", "city": "Washington", "state": "DC", "zipCode": "20220", }, "telephone": "907-949-4470", "creditCard": { "network": "Visa", "number": "4532636730015542" } } } ) )
Optional: run the following query to list the new
customers
documents:Map( Paginate(Documents(Collection("customers"))), Lambda("customerRef", Get(Var("customerRef"))) );
The orders
collection remains empty for now, its documents are going
to be created when we go through the use cases, coming next.
A word on document IDs
It’s important to notice that, in the queries above, we are using hard-coded document IDs when creating the documents. This is being done to easily establish the relationships between the different documents in the example.
Normally, a document ID is created automatically when you call
Create
and just specify the Collection
as the first
parameter (instead of providing the Ref
call). However, these
document IDs are not predictable. To properly form the required
relationships between documents, you might have to run queries to create
new documents, gather their References, and then run additional queries
to specify the relationships.
Alternatively, you can use the NewId
function to generate a
unique, valid document ID for yet-to-be-created documents. Then you can
use that document ID to create a document and specify its relationship
in other documents.
A Reference, or ref for short, is a pointer to a document within the database. A ref is a composite value that includes a reference to the collection that the document belongs to, and the document’s document ID which is a string-encoded 64-bit integer. |
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 an FQL expression (one or more FQL functions), 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
document. All of these operations are executed in a transactional
fashion.
To create the submit_order
function, copy the following query and
paste it into the Shell, the press RUN QUERY to run it:
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 FQL 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 Fauna.
Use cases
1. Submit a simple order
First, we start by submitting an order for products with enough stock. Copy the following query and paste it into the Shell, then press RUN QUERY to run it:
Call(
Function("submit_order"),
"101",
[
{
"productId": "204",
"quantity": 10
},
{
"productId": "206",
"quantity": 5
},
{
"productId": "208",
"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"), "294711005527671296"),
ts: 1617317166760000,
data: {
customer: Ref(Collection("customers"), "101"),
line: [
{
product: Ref(Collection("products"), "204"),
quantity: 10,
price: 3.99
},
{
product: Ref(Collection("products"), "206"),
quantity: 5,
price: 3.49
},
{
product: Ref(Collection("products"), "208"),
quantity: 20,
price: 1.49
}
],
status: 'processing',
creationDate: Time("2021-04-01T22:46:06.604Z"),
shipAddress: {
street: '87856 Mendota Court',
city: 'Washington',
state: 'DC',
zipCode: '20220'
},
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 running the following query:
Map(
Paginate(Documents(Collection("products"))),
Lambda("productRef", Get(Var("productRef")))
);
We should see that the quantities have been modified:
{
data: [
{
ref: Ref(Collection("products"), "201"),
ts: 1617311744380000,
data: {
name: 'cups',
description: 'Translucent 9 Oz, 100 ct',
price: 6.98,
quantity: 100,
store: Ref(Collection("stores"), "302"),
backorderLimit: 5,
backordered: false
}
},
{
ref: Ref(Collection("products"), "202"),
ts: 1617311744380000,
data: {
name: 'pinata',
description: 'Original Class Donkey Pinata',
price: 24.99,
quantity: 20,
store: Ref(Collection("stores"), "303"),
backorderLimit: 10,
backordered: false
}
},
{
ref: Ref(Collection("products"), "203"),
ts: 1617311744380000,
data: {
name: 'pizza',
description: 'Frozen Cheese',
price: 4.99,
quantity: 100,
store: Ref(Collection("stores"), "303"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "204"),
ts: 1617317166760000,
data: {
name: 'avocados',
description: 'Conventional Hass, 4ct bag',
price: 3.99,
quantity: 990,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "205"),
ts: 1617311744380000,
data: {
name: 'limes',
description: 'Conventional, 1 ct',
price: 0.35,
quantity: 1000,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "206"),
ts: 1617317166760000,
data: {
name: 'limes',
description: 'Organic, 16 oz bag',
price: 3.49,
quantity: 45,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "207"),
ts: 1617311744380000,
data: {
name: 'limes',
description: 'Conventional, 16 oz bag',
price: 2.99,
quantity: 30,
store: Ref(Collection("stores"), "303"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "208"),
ts: 1617317166760000,
data: {
name: 'cilantro',
description: 'Organic, 1 bunch',
price: 1.49,
quantity: 80,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "209"),
ts: 1617311744380000,
data: {
name: 'pinata',
description: 'Giant Taco Pinata',
price: 23.99,
quantity: 10,
store: Ref(Collection("stores"), "302"),
backorderLimit: 10,
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 the following query in the shell to try this case:
Call(
Function("submit_order"),
"103",
[
{
"productId": "206",
"quantity": 40
},
{
"productId": "203",
"quantity": 90
}
]
);
As a result, the order should be created successfully:
{
ref: Ref(Collection("orders"), "294711919406744064"),
ts: 1617318038310000,
data: {
customer: Ref(Collection("customers"), "103"),
line: [
{
product: Ref(Collection("products"), "206"),
quantity: 40,
price: 3.49
},
{
product: Ref(Collection("products"), "203"),
quantity: 90,
price: 4.99
}
],
status: 'processing',
creationDate: Time("2021-04-01T23:00:38.243Z"),
shipAddress: {
street: '5 Troy Trail',
city: 'Washington',
state: 'DC',
zipCode: '20220'
},
creditCard: { network: 'Visa', number: '4532636730015542' }
}
}
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"), "201"),
ts: 1617317952130000,
data: {
name: 'cups',
description: 'Translucent 9 Oz, 100 ct',
price: 6.98,
quantity: 100,
store: Ref(Collection("stores"), "302"),
backorderLimit: 5,
backordered: false
}
},
{
ref: Ref(Collection("products"), "202"),
ts: 1617317952130000,
data: {
name: 'pinata',
description: 'Original Class Donkey Pinata',
price: 24.99,
quantity: 20,
store: Ref(Collection("stores"), "303"),
backorderLimit: 10,
backordered: false
}
},
{
ref: Ref(Collection("products"), "203"),
ts: 1617318038310000,
data: {
name: 'pizza',
description: 'Frozen Cheese',
price: 4.99,
quantity: 10,
store: Ref(Collection("stores"), "303"),
backorderLimit: 15,
backordered: true
}
},
{
ref: Ref(Collection("products"), "204"),
ts: 1617317977150000,
data: {
name: 'avocados',
description: 'Conventional Hass, 4ct bag',
price: 3.99,
quantity: 990,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "205"),
ts: 1617317952130000,
data: {
name: 'limes',
description: 'Conventional, 1 ct',
price: 0.35,
quantity: 1000,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "206"),
ts: 1617318038310000,
data: {
name: 'limes',
description: 'Organic, 16 oz bag',
price: 3.49,
quantity: 5,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: true
}
},
{
ref: Ref(Collection("products"), "207"),
ts: 1617317952130000,
data: {
name: 'limes',
description: 'Conventional, 16 oz bag',
price: 2.99,
quantity: 30,
store: Ref(Collection("stores"), "303"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "208"),
ts: 1617317977150000,
data: {
name: 'cilantro',
description: 'Organic, 1 bunch',
price: 1.49,
quantity: 80,
store: Ref(Collection("stores"), "301"),
backorderLimit: 15,
backordered: false
}
},
{
ref: Ref(Collection("products"), "209"),
ts: 1617317952130000,
data: {
name: 'pinata',
description: 'Giant Taco Pinata',
price: 23.99,
quantity: 10,
store: Ref(Collection("stores"), "302"),
backorderLimit: 10,
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"),
"102",
[
{
"productId": "203",
"quantity": 15
},
{
"productId": "202",
"quantity": 45
}
]
);
As there isn’t enough stock quantity for the requested product, we should see an error result containing the following message:
'Stock quantity for Product [203] not enough – requested at [2021-04-01T23:04:46.246Z]'
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 running 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"),
"101",
[
Object({
"productId": "201",
"quantity": 70
})
]
);
And in another terminal, prepare the following query:
Call(
Function("submit_order"),
"102",
[
Object({
"productId": "201",
"quantity": 50
})
]
);
Then run 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"), "294712378764820992"),
ts: 1617318476370000,
data: {
customer: Ref(Collection("customers"), "101"),
line: [
{
product: Ref(Collection("products"), "201"),
quantity: 70,
price: 6.98
}
],
status: 'processing',
creationDate: Time("2021-04-01T23:07:56.323Z"),
shipAddress: {
street: '87856 Mendota Court',
city: 'Washington',
state: 'DC',
zipCode: '20220'
},
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 [201] not enough – requested at [2021-04-01T23:08:27.331Z]
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 run 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 Fauna keeps data correctness among simultaneous transactions in a distributed environment. This, combined with the power of the FQL, 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.
Is this article helpful?
Tell Fauna how the article can be improved:
Visit Fauna's forums
or email docs@fauna.com
Thank you for your feedback!