Writing an update conditional on both ref and index value

Help
1

Hi,

I’m trying to write a query which will only update a record if both the ref and the user’s email address match the record.

I could look up the record first, but I would prefer to do it one query.

This is the query I’ve written and it appears to explode.

queryAction = q.Update(
            q.Update(
                q.All([
                    q.Ref(q.Collection('timer'), data.ref),
                    q.Match(q.Index('timers_by_email'), user.email),
                ]),
                {
                    data: {
                        isRunning: true,
                    },
                }
            )
        );

If I was using SQL Server, I would use AND to join multiple conditions together. What’s the Fauna equivalent? Is it even possible with Fauna?

2

If this isn’t possible, how do you update a subset of documents in a collection?

e.g.
UPDATE table
SET foo = bar
WHERE email = ‘test@email.com’ AND thing LIKE ‘%stuff%’

3

Are you storing references within your documents? If not, this part of your request doesn’t make much sense as-is:

if both the ref and the user’s email address match the record

Do you mean that you want to update documents containing a specific email address?

These kinds of queries are certainly possible in Fauna, but you have to be a lot more deliberate because Fauna doesn’t support ad hoc indexing. You have to create indexes for your documents that specify terms (fields for searching) and values (fields for output).

If you just want to match email addresses, it looks like you might already have an appropriate index, timers_by_email. Processing groups of documents is best done by calling Map on the results of the Paginate function.

q.Map(
  q.Paginate(q.Match(q.Index('timers_by_email'), user.email)),
  q.Lambda(
    'timerRef',
    q.Update(q.Var('timerRef'), { data: { isRunning: true }})
  )
)

If there are more than 64 timer documents, you need to increase the default page size:

  q.Paginate(
    q.Match(q.Index('timers_by_email'), user.email),
    { size: 1000 }
  ),
4

Thanks so much for replying! Sorry, it was late and night and I probably didn’t explain well enough.

I have a Netlify function which updates a Fauna document. I want to only update that document if both the ref passed to the function matches the document’s ref and the email address in the document matches the user’s email address.

I’m using Netlify functions so the email address is provided and I’m sending the document’s ref in the request body.

I could do this in two stages:

  1. Get the document from Fauna and then check the email address matches that provided by the function.
  2. Only run the update if the email addresses match.

I wondered if this was possible to do in a single query. The two stage process would require extra code in every single function that modifies data just to prove that the user has permission to do so.

It sounds like the Map might do what I’m after, but would it be more sensible/performant to just do the two stage process described above?

With regards to the default page size, I’m only looking to update a single document at a time so could I just ignore it in this scenario?

5

Yes, it is less work to update a single document instead of searching for some number of documents to update.

Note that a reference is a unique identifier for a document in a database. There cannot be two documents sharing a reference. If you can provide a reference for a document that should be updated, then you can update it directly.

I’m guessing that you want to prevent users from guessing email addresses where content updates could be made (although that would be very weak security). You can certainly create a UDF that performs the update only when the email address matches.

Here is what I tried:

> CreateCollection({ name: "timer" })
{
  ref: Collection("timer"),
  ts: 1614712822980000,
  history_days: 30,
  name: 'timer'
}

> Create(Collection("timer"), { data: { email: 'me@me', score: 5 }})
{
  ref: Ref(Collection("timer"), "291980201289581056"),
  ts: 1614712868880000,
  data: { email: 'me@me', score: 5 }
}
> Create(Collection("timer"), { data: { email: 'you@you', score: 11 }})
{
  ref: Ref(Collection("timer"), "291980212836499968"),
  ts: 1614712879890000,
  data: { email: 'you@you', score: 11 }
}

> CreateFunction({
  name: "updateTimer",
  body: Query(
    Lambda(
      ["timerRef", "email", "score"],
      If(
        Equals(
          Var("email"),
          Select(["data", "email"], Get(Var("timerRef")))
        ),
        Update(Var("timerRef"), { data: { score: Var("score") }}),
        Abort("Score update not permitted!")
      )
    )
  )
})
{
  ref: Function("updateTimer"),
  ts: 1614713186500000,
  name: 'updateTimer',
  body: Query(Lambda(["timerRef", "email", "score"], If(Equals(Var("email"), Select(["data", "email"], Get(Var("timerRef")))), Update(Var("timerRef"), {data: {score: Var("score")}}), Abort("Score update not permitted!"))))
}

> Call(Function("updateTimer"), Ref(Collection("timer"), "291980201289581056"), "me@me", 47)
{
  ref: Ref(Collection("timer"), "291980201289581056"),
  ts: 1614713240880000,
  data: { email: 'me@me', score: 47 }
}
> Call(Function("updateTimer"), Ref(Collection("timer"), "291980201289581056"), "me@you", 47)
Error: call error
{
  errors: [
    {
      position: [],
      code: 'call error',
      description: 'Calling the function resulted in an error.',
      cause: [
        {
          position: [
            'expr',
            'else'
          ],
          code: 'transaction aborted',
          description: 'Score update not permitted!'
        }
      ]
    }
  ]
}
6

Oh interesting thanks! I’ll give it a try.

On the security side of things, Netlify takes care of proving the logged in user’s email address, but there’s nothing to stop the user posting any old document ref. So I’m making sure the logged in user owns the document in question before permitting the update.

7

Woo! I got it working. I didn’t go down the function route right away because I wanted to learn more about the JavaScript syntax.

This is what I’ve got for now. Just need to plug in some more variables to adjust the seconds properly when I stop and start my timer.

queryAction = q.Lambda(
	q.If(
		q.Equals(
			user.email,
			q.Select(['data', 'email'], q.Get(q.Ref(q.Collection('timer'), data.ref)))
		),
		q.Update(q.Ref(q.Collection('timer'), data.ref), {
			data: {
				seconds: 999,
				isRunning: true,
			},
		}),
		q.Abort('User does not own this timer!')
	)
);
1 Like