"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', err))
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)
try {
  println(Await.result(
    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"))
              )
            )
          )
        )
      )
    ),
    5.seconds
  ))
} catch {
  case unknown: Throwable => println("Error: " + unknown.getMessage())
}
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', err))
{
  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'}}
try {
  println(Await.result(
    client.query(
      Call(
        Function("upsert"),
        Ref(Collection("posts"), "20211021"),
        Obj(
          "data" -> Obj(
            "title"  -> "My October Post",
            "body"   -> "Lorem ipsum...",
            "author" -> "me"
          )
        )
      )
    ),
    5.seconds
  ))
} catch {
  case unknown: Throwable => println("Error: " + unknown.getMessage())
}
{ref: ref(id = "20211021", collection = ref(id = "posts", collection = ref(id = "collections"))), ts: 1634841193770000, 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.

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve!
Visit Fauna's Discourse forums or email docs@fauna.com

Thank you for your feedback!