"Upsert" a document

Problem

You need to create a document in a collection within the current database, but if it already exists the document needs to be updated. This is traditionally called an "upsert" operation.

Solution

Fauna doesn’t provide an Upsert function, but you can create one:

try
{
    Value result = await client.Query(
        CreateFunction(
            Obj(
                "name", "upsert",
                "body", Query(
                    Lambda(
                        Arr("ref", "data"),
                        If(
                            Exists(Var("ref")),
                            Update(Var("ref"), Var("data")),
                            Create(Var("ref"), Var("data"))
                        )
                    )
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
result, err := client.Query(
	f.CreateFunction(
		f.Obj{
			"name": "upsert",
			"body": f.Query(
				f.Lambda(
					f.Arr{"ref", "data"},
					f.If(
						f.Exists(f.Var("ref")),
						f.Update(f.Var("ref"), f.Var("data")),
						f.Create(f.Var("ref"), f.Var("data")),
					),
				),
			),
		},
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
System.out.println(
    client.query(
        CreateFunction(
            Obj(
                "name", Value("upsert"),
                "body", Query(
                    Lambda(
                        Arr(Value("ref"), Value("data")),
                        If(
                            Exists(Var("ref")),
                            Update(Var("ref"), Var("data")),
                            Create(Var("ref"), Var("data"))
                        )
                    )
                )
            )
        )
    ).get());
client.query(
  q.CreateFunction({
    name: 'upsert',
    body: q.Query(
      q.Lambda(
        ['ref', 'data'],
        q.If(
          q.Exists(q.Var('ref')),
          q.Update(q.Var('ref'), q.Var('data')),
          q.Create(q.Var('ref'), q.Var('data'))
        )
      )
    ),
  })
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
result = client.query(
  q.create_function({
    "name": "upsert",
    "body": q.query(
      q.lambda_(
        ["ref", "data"],
        q.if_(
          q.exists(q.var("ref")),
          q.update(q.var("ref"), q.var("data")),
          q.create(q.var("ref"), q.var("data"))
        )
      )
    )
  })
)
print(result)
CreateFunction({
  name: "upsert",
  body: Query(
    Lambda(
      ["ref", "data"],
      If(
        Exists(Var("ref")),
        Update(Var("ref"), Var("data")),
        Create(Var("ref"), Var("data"))
      )
    )
  )
})
Query metrics:

  •    bytesIn:  242

  •   bytesOut:  337

  • computeOps:    1

  •    readOps:    0

  •   writeOps:    1

  •  readBytes:   19

  • writeBytes:  449

  •  queryTime: 70ms

  •    retries:    0

Once the UDF has been created, you can then "upsert" a document:

try
{
    Value result = await client.Query(
        Call(
            Function("upsert"),
            Ref(Collection("posts"), "20211021"),
            Obj(
                "data", Obj(
                    "title",  "My October Post",
                    "body",   "Lorem ipsum...",
                    "author", "me"
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
ObjectV(ref: RefV(id = "20211021", collection = RefV(id = "posts", collection = RefV(id = "collections"))),ts: LongV(1634841128670000),data: ObjectV(title: StringV(My October Post),body: StringV(Lorem ipsum...),author: StringV(me)))
result, err := client.Query(
	f.Call(
		f.Function("upsert"),
		f.Ref(f.Collection("posts"), "20211021"),
		f.Obj{
			"data": f.Obj{
				"title":  "My October Post",
				"body":   "Lorem ipsum...",
				"author": "me",
			},
		},
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
map[data:map[author:me body:Lorem ipsum... title:My October Post] ref:{20211021 0xc000109d10 0xc000109d10 <nil>} ts:1634841132390000]
System.out.println(
    client.query(
        Call(
            Function("upsert"),
            Ref(Collection("posts"), "20211021"),
            Obj(
                "data", Obj(
                    "title",  Value("My October Post"),
                    "body",   Value("Lorem ipsum..."),
                    "author", Value("me")
                )
            )
        )
    ).get());
{ref: ref(id = "20211021", collection = ref(id = "posts", collection = ref(id = "collections"))), ts: 1634841145290000, data: {title: "My October Post", body: "Lorem ipsum...", author: "me"}}
client.query(
  q.Call(
    q.Function('upsert'),
    q.Ref(q.Collection('posts'), '20211021'),
    {
      data: {
        title: 'My October post',
        body: 'Lorem ipsum...',
        author: 'me',
      },
    },
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  ref: Ref(Collection("posts"), "20211021"),
  ts: 1634841148190000,
  data: { title: 'My October post', body: 'Lorem ipsum...', author: 'me' }
}
result = client.query(
  q.call(
    q.function("upsert"),
    q.ref(q.collection("posts"), "20211021"),
    {
      "data": {
        "title":  "My October post",
        "body":   "Lorem ipsum...",
        "author": "me",
      }
    }
  )
)
print(result)
{'ref': Ref(id=20211021, collection=Ref(id=posts, collection=Ref(id=collections))), 'ts': 1634841150480000, 'data': {'title': 'My October post', 'body': 'Lorem ipsum...', 'author': 'me'}}
Call(
  Function("upsert"),
  Ref(Collection("posts"), "20211021"),
  {
    data: {
      title:  "My October post",
      body:   "Lorem ipsum...",
      author: "me",
    }
  }
)
{
  ref: Ref(Collection("posts"), "20211021"),
  ts: 1634841198150000,
  data: { title: 'My October post', body: 'Lorem ipsum...', author: 'me' }
}
Query metrics:

  •    bytesIn:  188

  •   bytesOut:  222

  • computeOps:    1

  •    readOps:    1

  •   writeOps:    1

  •  readBytes:   18

  • writeBytes:  241

  •  queryTime: 54ms

  •    retries:    0

Discussion

The UDF takes a document reference, and an object representing the document’s data. The UDF then calls Create or Update depending on the existence of the document reference.

More information

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!