Prisma-client-js: Support atomic operations on update

Created on 13 Jul 2020 · 5Comments · Source: prisma/prisma-client-js

Problem

Whenever in need to perform an update on a field based on its current value, Prisma requires to first fetch the record, and then perform the update call.
This creates room for race conditions if the value gets changed in the data source between the fetching and updating calls.
The alternative is to rely on raw SQL queries, which defeats the value of using Prisma.

Context

SQL offers to perform a certain set of operations on fields in an UPDATE query:

Arithmetic operations: add (+), subtract (-), multiply (*), divide (/) or modulo (%)
String operations: concat (field = otherField + ' ')
Functions (type-dependent): e.g. CONCAT, LOWER, UPPER for strings, ABS, CEIL, ROUND, etc. for numbers

Ideal solution

An ideal solution would offer a way to express operations, and allow to incrementally add support for more and more functions over time, while only supporting a limited set in the first iteration.

Proposed first iteration

The most important set of operations to support are the arithmetic operations on numbers, provided that they are the most commonly used.
Type-dependent functions supports could be considered separately.

Most helpful comment

Implementation of step 1 is scheduled this sprint and is tracked in https://github.com/prisma/prisma-client-js/issues/844

janpio on 21 Aug 2020

🎉5 👍5

All 5 comments

Proposal

This proposal aims to solve atomic update operations while giving us the flexibility to add more operations and allowing us to introduce syntactic sugar later.

I'll first provide an example that will be used throughout, then describe the thought process, then suggest a solution.

Example

The examples below build up from the same expressions.

age: add(age, 10)
age: add(10) // increment 10
age: subtract(age, 10)
age: subtract(10) // decrement 10
age: multiply(age, 10)
age: divide(age, 10)
age: modulo(age, 10)

age: add(age, add(otherField, 10))
age: subtract(age, subtract(otherField, 10))
age: multiply(age, multiply(otherField, 10))
age: divide(age, divide(otherField, 10))
age: modulo(age, modulo(otherField, 10))

full_name: concat(first_name, last_name)
checksum: md5(contents)
contents_b64: base64(contents)
email: lower(email)
amount: abs(amount)
amount: ceil(10.5)
amount: round(10.5)

Process

The following was my process for working through this problem:

I started with S-expressions since they are the most "pure" expression-syntax.
I turned those S-expressions into Javascript via functions.
Those functions morphed into the AST representation.
I simplified the AST into a more domain-specific object notation.
Build up helper functions from the AST.

1. S-expressions

Initial exploration using S-expressions. Here, everything is a list.

age: (+ age 10)
age: (- age 10)
age: (* age 10)
age: (/ age 10)
age: (% age 10)

age: (+ age (+ otherField 10))
age: (- age (- otherField 10))
age: (* age (* otherField 10))
age: (/ age (/ otherField 10))
age: (% age (% otherField 10))

full_name: (concat first_name last_name)
checksum: (md5 contents)
contents_b64: (base64 contents)
email: (lower email)
amount: (abs amount)
amount: (ceil 10.5)
amount: (round 10.5)

A direct translation, doesn't lend itself very well to Javascript ["+", Symbol("age"), ["+", Symbol("otherField"), 10]].

2. Functions

This form uses functions:

age: add(age, 10)
age: subtract(age, 10)
age: multiply(age, 10)
age: divide(age, 10)
age: modulo(age, 10)

age: add(age, add(otherField, 10))
age: subtract(age, subtract(otherField, 10))
age: multiply(age, multiply(otherField, 10))
age: divide(age, divide(otherField, 10))
age: modulo(age, modulo(otherField, 10))

full_name: concat(first_name, last_name)
checksum: md5(contents)
contents_b64: base64(contents)
email: lower(email)
amount: abs(amount)
amount: ceil(10.5)
amount: round(10.5)

I feel like this would be the ideal solution if these functions were magically global.

3. AST

Using https://astexplorer.net/, we could turn these functions into proper AST objects:

age: {
  type: "CallExpression",
  callee: {
    type: "Identifier",
    name: "add"
  },
  arguments: [
    {
      type: "Identifier",
      name: "age"
    },
    {
      type: "Literal",
      value: 10
    },
  ]
}

age: {
  type: "CallExpression",
  callee: {
    type: "Identifier",
    name: "add"
  },
  arguments: [
    {
      type: "Identifier",
      name: "age"
    },
    {
      type: "CallExpression",
      callee: {
        type: "Identifier",
        name: "add"
      },
      arguments: [
        {
          type: "Identifier",
          name: "otherField"
        },
        {
          type: "Literal",
          value: 10
        }
      ]
    }
  ]
}


age: {
  type: "CallExpression",
  callee: {
    type: "Identifier",
    name: "round"
  },
  arguments: [
    {
      type: "Literal",
      value: 10
    }
  ]
}

This would work, but it'd be a bit verbose for people to write. It's also somewhat unnecessary, we're not trying to offer full JS support inside the Prisma Query Engine.

4. Objects

Since we don't need the full expressiveness of the Javascript AST, we can simplify our AST. Everything is a CallExpression:

age: { fn: "add", args: [ Symbol("age"), 10 ] }
age: { fn: "add", args: [ 10 ] }
age: { fn: "subtract", args: [ Symbol("age"), 10 ] }
age: { fn: "subtract", args: [ 10 ] }
age: { fn: "multiply", args: [ Symbol("age"), 10 ] }
age: { fn: "divide", args: [ Symbol("age"), 10 ] }
age: { fn: "modulo", args: [ Symbol("age"), 10 ] }

age: { fn: "add", args: [ Symbol("age"), { fn: "add", args: [ Symbol("otherField"), 10 ] } ] }
age: { fn: "subtract", args: [ Symbol("age"), { fn: "subtract", args: [ Symbol("otherField"), 10 ] } ] }
age: { fn: "multiply", args: [ Symbol("age"), { fn: "multiply", args: [ Symbol("otherField"), 10 ] } ] }
age: { fn: "divide", args: [ Symbol("age"), { fn: "divide", args: [ Symbol("otherField"), 10 ] } ] }
age: { fn: "modulo", args: [ Symbol("age"), { fn: "modulo", args: [ Symbol("otherField"), 10 ] } ] }

full_name: { fn: "concat", args: [ Symbol("first_name"), Symbol("last_name") ] }
checksum: { fn: "md5", args: [ Symbol("contents") ] }
amount: { fn: "round", args: [ 10.5 ] }

A bit verbose, but it does give us the flexibility to add more operations and will allow us to introduce Helper Functions later.

Note on Symbols: These allow you to create your own custom identifier. I'm not sure if we should use them here, but the application does feel appropriate: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Symbol. These would probably be generated by Prisma.

_Open Questions:_

Can we make this type-safe? Filters would depend on the column type,
- e.g. age supports number operations
- e.g. full_name supports string operations
Should we use symbols?
Should increment/decrement be separate operations?

_Alternatives_

op or type instead of fn.

5. Helper Functions

We could eventually do this later.

age: (x, f) => x.add(f.age, 10)
age: (x) => x.add(10)
age: (x, f) => x.subtract(f.age, 10)
age: (x) => x.subtract(10)
age: (x, f) => x.multiply(f.age, 10)
age: (x, f) => x.divide(f.age, 10)
age: (x, f) => x.modulo(f.age, 10)

age: (x, f) => x.add(f.age, x.add(f.otherField, 10))
age: (x, f) => x.subtract(f.age, x.subtract(f.otherField, 10))
age: (x, f) => x.multiply(f.age, x.multiply(f.otherField, 10))
age: (x, f) => x.divide(f.age, x.divide(f.otherField, 10))
age: (x, f) => x.modulo(f.age, x.modulo(f.otherField, 10))

age: (x, f) => x.concat(f.first_name, f.last_name)
checksum: (x, f) => x.md5(f.contents)
amount: (x) => x.round(10.5)

_Alternatives_

x.age.add(10) form instead

Suggested Solution

I propose the following:

Go with 4. Object notation, unless it's easier to go straight to the 5. Helper Function syntax.
Start with numeric operations for V1.
Support operations across columns is optional for V1.

matthewmueller on 11 Aug 2020

After some additional internal discussion, we're leaning towards starting with a simpler API and introduce expressions later.

Step 1: Limited object syntax

age: {
    set: 18, // this just sets the value, is equivalent to `data: { age: 18 }`
    increment: 1, // age = age + 1
    decrement: 2, // age = age - 2
    multiply: 3, // age = age * 3
    divide: 4, // age = age / 4
}

Start with something simple. A limited, but first-step solution to atomic updates for numeric operations.

Step 2: Add functional syntax

{ 
   age: u => u.age.inc(1)
}

The aim here is to provide higher-level syntax-sugar. The underlying query that this generates may be equivalent to the object expression above, but it doesn't have to be. It could be a more generic expression syntax.

Step 3: Extend Functional Syntax

{
  age: u => u.age.add(u.otherField.add(10))
}

This could allow us to extend this syntax to support more arbitrary expressions. The underlying query that this generates will likely need to be more generic than the object syntax above to accommodate this.

Right now we're planning on starting with Step 1.

Step 2 and Step 3 are currently unplanned and the syntax may change.

Open Questions

Can we squeeze in some string operations? It might be nice to stress test our solution. I think concat would be the most useful, but that may also bring complexity. Two other options could be append and prepend.
Are there any other must-have operations in Step 1 that we're missing?
What will the protocol look like between client and query engine?

matthewmueller on 14 Aug 2020

👍4 ❤2

Implementation of step 1 is scheduled this sprint and is tracked in https://github.com/prisma/prisma-client-js/issues/844

janpio on 21 Aug 2020

🎉5 👍5

All the beauty of prisma.upsert is gone when this feature is missing. It is fine when same data can be used for create and update structure in this command, so I suggest it works that way, i.e increment from zero in create

const itemData = {
    name: args.name,
// ...20 more properties
    quantity: add(quantity, args.quantity),
}
ctx.prisma.saleitem.upsert ({
  create: itemData,
  update: itemData,
// 20 lines saved
  where: {id}
})

Without an error message "Cannot add to undefined" :D