How to do a UPSERT in FaunaDB FQL

date
Dec 28, 2020
published
slug
how-to-do-a-upsert-in-faunadb-fql
description
FQL is different but just as powerful as SQL. An UPSERT is a great example to show the flexibility of FQL and how you can compose it easily.
In an earlier post I talked about how I was recording site visitors with some home grown custom analytics code and FaunaDB. The basic idea was that I would generate a unique ID for each visitor that would be based on public information. No strange fingerprinting using canvas or any other bad ad tech.
Then I would insert that user into the database. Each of these records would represent a session, in addition to recording the pageview. If the user existed within the session time window, then I would increment the count within that session.
This was the heart of the UPSERT. In traditional SQL, you can tell the database to update an item if it is found, otherwise insert a new record. FaunaDB is exactly the same, in that it can do ACID transactions like this.

Basic Analytics Code for UPSERT

const response = await db.query(Do(
      Let(
        [
          { sid: sid },
          { host: host },
          { match: Match(Index("sessions_by_host_and_sid"), [Var("host"), Var("sid")]) }
        ],
        If(
          Exists(
            Range(
              Var("match"),
              ToMicros(Now()),
              Subtract(ToMicros(Now()), 1800000000)
            )
          ),
          Let(
            { entry: Get(Var("match")) },
            Update(Select("ref", Var("entry")), { data: { count: Add(Select(["data", "count"], Var("entry")), 1) } })
          ),
          Create(Collection("sessions"), { data: { sid: Var("sid"), host: Var("host"), count: 1 } })
        )
      ),
      Create(Collection("pageviews"), { data })
    ));
This works because we can give FQL a basic If statement. In the code above, If checks if Exists determines a record is within the collection. If it does, then it runs Update, otherwise it runs Create.
There is more happening above, like getting data from Var and checking if the entry is within a range. But UPSERTS are possible just like other query languages. It is also worth pointing out, that the Let basically allows you to run multiple queries at once. So the UPSERT is really trying to modify our session table. Every entry also gets recorded as a pageview in the pageview collection as well. This really shows the power of FQL.
And because you are not passing a string of a query, you can compose this the way you compose any javascript. Here is an alternate version of the same query, but broken out to make it easier to read.
const createPageviewCollection = Create(Collection("pageviews"), { data });
const createSessionCollection = Create(Collection("sessions"), { data: { sid, host, count: 1 } })
const updateSessionCollection =  Let(
  { entry: Get(Var("match")) },
  Update(Select("ref", Var("entry")), { data: { count: Add(Select(["data", "count"], Var("entry")), 1) } })
);

const response = await db.query(Do(
      Let(
        [
          { sid: sid },
          { host: host },
          { match: Match(Index("sessions_by_host_and_sid"), [Var("host"), Var("sid")]) }
        ],
        If(
          Exists(
            Range(
              Var("match"),
              ToMicros(Now()),
              Subtract(ToMicros(Now()), 1800000000)
            )
          ),
          updateSessionCollection,
          createSessionCollection,
        )
      ),
      createPageviewCollection,
    ));

See Also

  1. FaunaDB seems so ergonomic with React Hooks