Screen Shot 2019 03 06 At 10 15 05 Am

Learning FQL, Part 2: Create, Read, Update, and Delete Operations (CRUD)

The most common database queries are used simply to manipulate individual data items. For instance a recipe application allows users to save a new recipe, read an existing recipe, make changes to a recipe, or remove an unwanted recipe. Of course, most applications also include complex queries, such as listing all my favorite recipes, or adding a flag to all the recipes that include ingredients which contain gluten – which will be the focus of other articles in this series.

This article is about basic CRUD operations. There is some setup and context that you need before you can save documents to the database. For instance, you’ll need to define a schema to tell FaunaDB which types of documents you’ll be dealing with. You’ll also need to ensure your code can connect to the database. Those prerequisites are outside the scope of this article, as here we’ll focus on the common runtime operations on documents: create, read, update, and delete. Check out the first article in the series for help setting up your database schema.

Create

Once you’ve setup your FaunaDB client driver and created a database in FaunaDB, you are ready to create a document. In Fauna Shell, your query might look like this:

Create(Class("recipes"), {
    data : {
        title : "Lentil Soup",
        ingredients : ["carrots", "lentils", "tomatoes", "onions", "ginger", "garlic", "olive oil"],
        description : "..."
}})

#=> { 
    ref: Ref(Class("recipes"), "226111113652077056"), 
    ts: 1551895211820000, 
    data: { title : "Lentil Soup" ... } }




This query references the “recipes” class, to tell the database which kind of document to create. Indexes and other schema objects can work on classes of documents. It is common for each application to define several classes. You can learn more about setting up your schema in the FaunaDB documentation.

Read

You can see in the above query that the application data is all contained in the data field. FaunaDB makes system fields available as well, so everything you store will be in the data field. One of the system fields is the ref, which is a reference to the object itself. You can acquire a ref via an index query, or from the result of a CRUD operation. We can use this reference in other queries. Let’s run the query we defined above, and use the reference returned to issue a read.

The FQL we use to fetch a document is simple, in this case we use the ref value returned from the above Create query. Your return value will have a different id.

Get(Ref(Class("recipes"), "226111113652077056"))

In the below JavaScript example, soupRef is a reference obtained from running the above query. This example uses JavaScript in addition to FQL, in order to show you how refs can be reused for future queries in your code:

dbClient.query(makeRecipeQuery).then((createResult) => {
    const soupRef = createResult.ref
    const readSoupQuery = q.Get(soupRef)
    dbClient.query(readSoupQuery).then((soupRecipe) => {
        console.log(soupRecipe.data.title) // “Lentil Soup”
    })
})

The actual read query Get(soupRef) is so simple, that it makes a good opportunity to show how data flows through an asynchronous JavaScript example.

The Get function can also be embedded in more complex queries, for instance it is common to iterate over the results of an index match and use Get to load the associated documents. A future article in this series will discuss patterns for bulk operations.

Update

Updating a document we loaded from database is done by directly manipulating it and saving it back. Extending on the above example:

Update(Ref(Class("recipes"), "226111113652077056"), { 
    data : {
        description: “A flavorful take on the hearty classic.”
}})

In this query, we only have to specify the data field we wish to change. Any unspecified data fields will not be modified. If you want to remove a field, you can replace it with a null value.

Delete

The new description has been added to the soup recipe. But then our uncle Robert calls and insists the recipe is a family secret and can’t be shared! So to delete it, we issue a query using the same reference we used before:

Delete(Ref(Class("recipes"), "226111113652077056"))

You can read the full documentation for Delete here. It is also a common pattern to iterate over an index Match result set and delete all of the documents that it references. Look forward to exploring bulk create, read, update and delete patterns in a future article in this series.

Putting it Together

Composability is a key design goal of FQL. Composable queries are easy to build programmatically, and the patterns you learn can be reused and nested. Compare-and-swap or check-and-set (CAS) is a common pattern built by combining the functions described in this post. Essentially, a CAS update is an update that only succeeds if no other process has changed the data since it was read. This allows the application to alert the user that someone else is editing a document, instead of the users discovering that they have clobbered each other’s work.

To implement CAS, first the application reads a document, then makes changes to it, and saves them back with an instruction to only save if the document hasn’t been touched in the meantime. In FQL it looks like this: (assume 1551895211820000 came from the read the application made to populate the UI, and was submitted with the user as part of a form request.)

If(Equals(1551895211820000, Select("ts", Get(Ref(Class("recipes"), "226111113652077056")))),
    Update(Ref(Class("recipes"), "226111113652077056"), { data : { title : "Best Lentil Soup" } }),
    "Too late, someone else changed the record since you read it.")

This query combines Get, and Update, along with control flow operators like If, logical operators like Equals, and data addressing with Select. If the document’s timestamp has changed since it was loaded by the user, the query fails with an error saying the update is too late. The CRUD operations we are familiar with, and the other operators are designed to be familiar from other languages. Select allows you to pull a particular field from a document. In this case we used the timestamp, so any other change to the document (which automatically updates its timestamp) results in a CAS failure, but if you Select the field you plan to update, you can scope your CAS to the title alone. This allows you to do a conditional update on one field, for instance changing the title even if someone else has changed the ingredients in the meantime.

In the next part of this series we'll cover database Access Keys. Here is a preview of the kinds of patterns you can implement with FaunaDB database access control.