Multi-document upsert?

I’ve managed to create an upsert for a single document using this answer.

How would I convert this to upsert multiple documents in one query? I’d like to avoid looping and hitting fauna with a request for each document and dealing with a mess of error handling.

    const productId = 'abc567';
    const data = {
      foo: 'all new!',
      productId
    };

    client
      .query(
        q.If(
          q.Exists(q.Match(q.Index('productId'), productId)),
          q.Replace(
            q.Select('ref', q.Get(q.Match(q.Index('productId'), productId))),
            {
              data
            }
          ),
          q.Create(q.Collection('products'), {
            data
          })
        )
      )
      .then((ret) => console.log(ret.data))
      .catch((err) => console.error(err));

You could use Do() to put multiple hard-coded statements in there.

Or you could put your ‘to be updated’ data in an array.

const mydata = [ {
    data: {
        foo: 'all new!',
        productId: 'abc567'
     } 
   } ,

 .. others ...

]


And write your query as a Map

q.Map(mydata, q.Lambda(['d'], myUpsertFunction(Var('d')))

This assumes you wrote a function to encapsulate your upsert logic. For example:

function myUpsertFunction(fqlVar) {
 return q.If(
      q.Exists(q.Match(q.Index('productId'), Select(['data', 'productId'], fqlVar))),
      q.Replace(
        q.Select('ref', q.Get(q.Match(q.Index('productId'), Select(['data', 'productId'], fqlVar)))),
        {
          Select(['data'], fqlVar)
        }
      ),
      q.Create(q.Collection('products'), {
        Select(['data'], fqlVar)
      })
    )
}

Make sure to verify the code, I didn’t run it.

Do note that there is a request limit as well (± 1Mb) so don’t make those arrays too big :wink:

The data will be variable length, and I like the idea of it all as one transaction as Pier suggested, to simplify error handling.

However, I’m just trying to get the most basic version working first. Ran into errors in the above sample code and tried tweaking.

Can you see what I’m doing wrong? I don’t understand what the d property is doing; I also tried ‘productId’ there, without success. This code inserts two empty objects currently.

const arrData = [
  {
    data: {
      productId: 'abc123',
      foo: 'bar'
    }
  },
  {
    data: {
      productId: 'abc789',
      foo: 'baz'
    }
  }
];

function myUpsertFunction(fqlVar) {
  return q.If(
    q.Exists(
      q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
    ),
    q.Replace(
      q.Select(
        'ref',
        q.Get(
          q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
        )
      ),
      q.Select(['data'], fqlVar)
    ),
    q.Create(q.Collection('products'), q.Select(['data'], fqlVar))
  );
}

(async () => {
  let res;
  try {
    res = await client.query(
      q.Map(arrData, q.Lambda(['d'], myUpsertFunction(q.Var('d'))))
    );
    console.log(res.data);
  } catch (err) {
    console.error(err);
  }
})();

Actually forget what I just said in my previous post :slight_smile:

yep, but no worries, everything query is a transaction in FaunaDB, you don’t need Do (which is why the post was removed, there was some confusion :wink: )

I can’t help without an error :).

Fwiw, I just made a product collection, added the productId index you are querying and threw this in the shell:

var arrData = [
  {
    data: {
      productId: 'abc123',
      foo: 'bar'
    }
  },
  {
    data: {
      productId: 'abc789',
      foo: 'baz'
    }
  }
]

function myUpsertFunction(fqlVar) {
  return q.If(
    q.Exists(
      q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
    ),
    q.Replace(
      q.Select(
        'ref',
        q.Get(
          q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
        )
      ),
      q.Select(['data'], fqlVar)
    ),
    q.Create(q.Collection('products'), q.Select(['data'], fqlVar))
  )
}

q.Map(arrData, q.Lambda(['d'], myUpsertFunction(q.Var('d'))))

which returned the created documents:

[
  {
    ref: Ref(Collection("products"), "273765789117448711"),
    ts: 1597342251990000
  },
  {
    ref: Ref(Collection("products"), "273765789115351559"),
    ts: 1597342251990000
  }
]

The ‘d’ property is a variable.
just like when you would write a map in javascript:

[  ].map((d => .. do something ... )

However, since FQL is based on functions and we can’t just put plain javascript variables in there, the syntax for Map takes a Lambda() function and the first parameter of that Lambda function is an array of the variables (in string format) that you will use, in my case. [‘d’].

Since these are just strings, how do we get the variable? That’s done with the Var() function, in our case, Var(‘d’). You have to realisz that this is code that is not running in javascript but will be executed in FaunaDB as a query, hence the rather strange syntax :).

Hence the equivalent of a JavaScript map that does nothing (simply returns the variable again)
[ ].map((d => d )

would be

q.Map([], q.Lambda(['d'], Var('d') )

If you apply that on you map. Var(‘d’) will be equal to one of your objects in the array, for example:

 {
    data: {
      productId: 'abc123',
      foo: 'bar'
    }
  }

PS: you do not have to abstract the FQL away in that function. You could just place the whole upsert function in the Map directly but I wanted to show you what you could do to keep it clean :). E.g.

q.Map(arrData, q.Lambda(['d'], 
  q.If(
      q.Exists(
        q.Match(q.Index('productId'), q.Select(['data', 'productId'], Var('d')))
      ),
      q.Replace(
        q.Select(
          'ref',
          q.Get(
            q.Match(q.Index('productId'), q.Select(['data', 'productId'], Var('d')))
          )
        ),
        q.Select(['data'], Var('d'))
      ),
      q.Create(q.Collection('products'), q.Select(['data'], Var('d')))
    )
))

Sorry. There were too many. It was one after another. So I tried posting some code that got closer instead.

Thanks for the explanation.

I receive a similar successful-looking response after updating my console log from console.log(ret.data) to console.log(ret), given it’s an array response now.

However it’s still storing empty objects.

Latest code (stores objects, but their data property is empty in the Fauna admin):


const arrData = [
  {
    data: {
      productId: 'abc123',
      foo: 'bar'
    }
  },
  {
    data: {
      productId: 'abc789',
      foo: 'baz'
    }
  }
];


function myUpsertFunction(fqlVar) {
  return q.If(
    q.Exists(
      q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
    ),
    q.Replace(
      q.Select(
        'ref',
        q.Get(
          q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
        )
      ),
      q.Select(['data'], fqlVar)
    ),
    q.Create(q.Collection('products'), q.Select(['data'], fqlVar))
  );
}

(async () => {
  let res;
  try {
    // Replace, if already exists exists; create, if not.
    res = await client.query(
      q.Map(arrData, q.Lambda(['d'], myUpsertFunction(q.Var('d'))))
    );
    console.log(res);
  } catch (err) {
    console.error(err);
  }
})();

@isle, you’re on the right track using q.Map.

Create and Replace expect the params object to also contain the data field. So do not Select it. Pass in fqlVar directly.

You want it to resolve into something like:

q.Create(q.Collection(‘products’), { data: { … } })

Does this work better?

function myUpsertFunction(fqlVar) {
Let(
  {
    match: q.Match(q.Index('productId'), q.Select(['data', 'productId'], fqlVar))
  },
  q.If(
    q.Exists(q.Var('match')),
    q.Replace(
      q.Select('ref', q.Get(q.Var('match'))),
      fqlVar
    ),
    q.Create(q.Collection('products'), fqlVar)
  )
)
}

Also

If for some reason you need to reduce read ops while doing this (like frequently upserting thousands at a time)

Jup, sorry. Didn’t test int that much detail since it was the end of my day. What Paul said is correct.

Or in one query:

q.Map(arrData, q.Lambda(['d'], 
  q.If(
      q.Exists(
        q.Match(q.Index('productId'), q.Select(['data', 'productId'], Var('d')))
      ),
      q.Replace(
        q.Select(
          'ref',
          q.Get(
            q.Match(q.Index('productId'), q.Select(['data', 'productId'], Var('d')))
          )
        ),
        Var('d')
      ),
      q.Create(q.Collection('products'), Var('d'))
    )
))

lol I need to refresh the page more often. SO much happened in 15 minutes! :slight_smile:

1 Like

Thank you guys, to everyone.

The last one works after changing Var() to q.Var().

I got this working:

const arrData = [
  {
    data: {
      productId: 'abc123',
      foo: 'bar!'
    }
  },
  {
    data: {
      productId: 'abc789',
      foo: 'baz!'
    }
  }
];

function multiUpsert(arrData) {
  return q.Map(
    arrData,
    q.Lambda(
      ['d'],
      q.If(
        q.Exists(
          q.Match(
            q.Index('productId'),
            q.Select(['data', 'productId'], q.Var('d'))
          )
        ),
        q.Replace(
          q.Select(
            'ref',
            q.Get(
              q.Match(
                q.Index('productId'),
                q.Select(['data', 'productId'], q.Var('d'))
              )
            )
          ),
          q.Var('d')
        ),
        q.Create(q.Collection('products'), q.Var('d'))
      )
    )
  );
}

(async () => {
  let res;
  try {
    res = await client.query(multiUpsert(arrData));
    console.log(res);
  } catch (err) {
    console.error(err);
  }
})();

Run it once to insert. Change values of foo props and run it again and it replaces existing items.

I’m going to select this post as the answer so others can find it.

I do find this quite verbose compared to the alternatives:

Hopefully this will be more concise in a future SQL interface for Fauna, if such an alternative to FQL becomes available. :pray:

Just to be clear towards other users who might read this. There are no immediate plans to support SQL as our roadmap surveys have suggested low interest in SQL. This is a question that often comes back due to an announcement that was released a while back. The roadmap surveys came right after that and showed low interest, hence SQL has been deprioritized.

That said, there are reasons that FQL is what it is. FaunaDB is a massive scalable database that’s also pay-as-you-go. I personally had experience with NoSQL databases being queried by an inexperienced person (e.g. for analytics purposes) which resulted in crashes of nodes. The ex-twitter engineers who designed FQL had similar experiences and FQL was built to avoid specific pain points. For the people who are interested why it’s so different I’ll quickly write down a few points.

It’s not declarative, due to the following reasons:

  • Query is the query plan: a declarative query has to be interpreted and an optimal query plan has to be constructed. In data heavy applications, it often happens that your query plan looks good at a certain time and when your data grows, the query plan changes and you suddenly get a performance bottleneck. In traditional databases you declare what data you want to see and the query planner will attempt to come up with an intelligent query plan. That intelligent plan might not be so intelligent. In FQL you define exactly how your data should be fetched so there are no surprises.
  • Mandatory indexes: Indexes are mandatory to avoid shooting yourself in the foot. That is much easier to support when you explicitly define the query plan (e.g. in FQL the query is the query plan).
  • One language for querying/security/functions In SQL you typically have stored procedures which are a different language (e.g. PL/SQL) than SQL in order to support conditional logic etc more easily, a language which is not super easy either. In FaunaDB, FQL can do everything in the same language, User Defined Functions, Security Roles (ABAC) are all written in the same language.
  • FQL is meant to fetch or do everything in one query: the same principle as GraphQL, instead of doing multiple queries, FQL is designed to do everything in one query, things that are sometimes feasible in SQL yet become very difficult if your query becomes more complex. Especially if a part of that query is to update something depending on conditional logic.
  • FQL is meant to support business logic: having business logic close to the data makes sense since you can then benefit from the strong consistency of your data. FQL Is meant to be able to express business logic as well.
  • FQL is meant to be composable: the functional nature is probably the most differentiating thing compared to other languages. The idea behind this is that it’s meant to be composable, everything is just a JavaScript function that takes values (or other FQL statements) and returns an FQL statement. This made it quite easy to create a GraphQL layer for FaunaDB where the GraphQL is translated to an FQL query one to one which is notoriously difficult in SQL (I’ve worked on complex SQL generation engines in the past, I’d choose FQL if I would have had the choice personally). This makes it easy to create your libraries (e.g. https://github.com/shiftx/faunadb-fql-lib or https://github.com/gahabeen/biota) and it wouldn’t be surprising that we have a solid community-driven higher level library for FaunaDB at a certain moment. That said, we are planning on providing more high-level functions in FQL in the future and even separate high-level from low-level functions to make it easier to get started.

As we have shown above you can easily create your own Upsert function, and once someone has done that and placed it in a library, you can’t even see the difference (given that we follow the same naming conventions) between FQL and that new function. It’s arguably not very difficult to then write the query:

     myUpsertFunction( < some data > )

or apply it on a list:

      Map(< some data in array > , Lambda(['d'], myUpsertFunction(Var('d'))))

Right?

To be clear, I respect your points of view and we get it, FQL has to become less verbose in the future and more high-level. I just want to make sure that you can see the potential and good parts of it as well :wink:

1 Like