FQL v4 will be decommissioned on June 30, 2025. Ensure that you complete your migration from FQL v4 to FQL v10 by that date.

For more details, see the v4 EOL announcement and migration guide. Contact support@fauna.com with any questions.

"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:

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)
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)
}
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}");
}
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:

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'}}
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]
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)))
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.

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!