Atomic Update Support #3657

parisholley · 2022-10-25T17:06:38Z

Is your feature request related to a problem? Please describe.
Most databases include some level of auto incrementing (eg: SERIAL or generators) however these are usually not transaction safe so you need to maintain your own int column and update via a lock (usually done automatically if inside an open transaction).

Describe the solution you'd like
entityManager.atomicUpdate(MyEntity, {id:1}, {field: 'field + 1'})

Check that entityManager is inside a transaction, if not issue a lock command on row (if driver supports), or alternatively always ensist it is in a transaction
Add update to UoW queue
Flush UoW queue
Take result and update in-memory instance (if it exists in cache)

Describe alternatives you've considered
Only way to do this today is use createQueryBuilder to build an update query, execute it against the driver and then update the instance in memory your self (potential downside being that it may execute another query thinking there was a change)

    const qb = this.options.em.createQueryBuilder(clazz);
    const query = qb
      .getKnex()
      .returning(field)
      .update({ [field]: qb.raw(`"${field}" + 1`) })
      .where({ id })
      .toQuery();
    const results = await this.options.em.execute(query);

    const result = results[0] as T;

    if (!result) {
      throw new Error('expected one row to return');
    }

    const number = result[field];

    if (typeof number !== 'number') {
      throw new Error('expected returned value to be a number');
    }

    // TODO: https://github.com/mikro-orm/mikro-orm/issues/3657 - fix typing/better way to update UoW
    // eslint-disable-next-line @typescript-eslint/ban-ts-comment
    // @ts-ignore
    const entity = await this.options.em.findOneOrFail<T>(clazz, id);

    // eslint-disable-next-line @typescript-eslint/ban-ts-comment
    // @ts-ignore
    entity[field] = number;

    return number;

The text was updated successfully, but these errors were encountered:

B4nan · 2023-03-04T00:19:34Z

Re-reading this now, I am not sure I got it right. I would like to add support for a delayed atomic update that will be done via explicit flush. This way it would be always done inside transaction, it is up to you to define explicit boundary if you want to, as well as to flush when appropriate.

Not sure if we need something more generic than increment/decrement support?

const user = await em.findOne(User, id); // or `em.getReference(User, id)`
em.increment(user, 'counter');
await em.flush();

Maybe we could even find a way to do something like this:

const user = await em.findOne(User, id); // or `em.getReference(User, id)`
user.counter = raw('counter + 1');
await em.flush();

This would either use a returning statement or a separate query to load the actual value and hydrate it back to the provided entity instance.

So rather lower level API than what you described, as that feels quite app-specific.

parisholley · 2023-03-04T03:55:00Z

my motivation behind a more high-level approach (in this case, inc/dec) is that it allows that entity reference to be shared across app boundaries safely (in memory) without worrying about the state of a flush. for example, if you are processing a bunch of orders in batch, and want to generate an invoice for each one, you may increment an "invoice number" while looping through each order and then commit a single transaction (with multi-value inserts) at the end.

i suppose with the low-level approach, you could derisk it by throwing if you attempt to "get" a property which has been assigned to a raw, but then you end up littering try/catches everywhere (effectively the same as load/populate without the Loaded type) and then forced to flush earlier than you want. personally, i've always looked to explicit update() commands for advanced cases that require raw-like functionality.

how would something like raw work in this example?:

// generateNewInvoiceNumber()
const user = await em.findOne(User, id); // or `em.getReference(User, id)`
user.invoiceNumber = raw(alias => `${alias}.invoiceNumber + 1`);

// createInvoice()
const invoice = new Invoice();
invoice.number = user.invoiceNumber;

await em.persistAndFlush(invoice);

B4nan · 2023-03-04T09:41:23Z

That's a good point - I was suggesting the raw helper as that is what I wanted to add to v6 anyway, my initial thinking was to have a static helper like expr, that could be used instead of em.raw() in case you don't have the EM instance around - one of the main issues to solve was using custom SQL fragments in custom types. Then I realized this could actually work with the UoW too, hence my proposal of assigning it to the entity property directly. I kinda like that idea, so will probably give it a go and see how it works.

Internally, it would work similarly to how we work with FKs, the UoW automatically maps them to EntityIdentifier instances (basically proxy objects) and those are then assigned with the value once we have it, propagating it to the places where it is needed. So invoice.number = user.invoiceNumber in your example would just reassign the raw value, it would be still the same "raw value instance", so the same value once flushed. The issue with reading the value between you set it to the raw and flushing remains, and we can't really throw on read if we want to allow assigning as in invoice.number = user.invoiceNumber. We could definitely throw on serialization, but maybe ignoring such value would be better?

So while I do understand this can bring inconsistency to the model, it sounds like a very powerful addition - and you can always flush right ahead if you care about this.

I am not entirely sure about the atomicUpdate proposal itself, mainly because I am not sure if people would expect it to do what you described (especially the flushing part), so I would rather allow doing this in a more native way (like with the raw helper), so you could tailor it to your specific needs.

parisholley · 2023-03-04T13:48:03Z

you proposal is effectively opt-in, so no harm in adding it :)

i suppose if the serialization throw would cover cases like adding it into a object then running JSON.stringify() or toString() then it would catch most cases.

i don't think i'd like the runtime risk tho, because I could see folks doing the equivalent of invoice.number += 1 or other cases where i don't think serialization checks will kick in. perhaps adding a wrapper type Expression<number> where you could enforce they treat it like a unloaded collection, and explicitly say .get(), knowing that it may not have flushed yet would then make it more obvious to downstream consumers of the risk.

mainly because I am not sure if people would expect it to do what you described (especially the flushing part)

i'm not sure i follow the "flushing part", the example in the initial post wouldn't flush at all, just issue a raw query (inside a transaction) and update the entity in memory with new value. i could see making the call more generic though, so instead of use-case specific inc/dec, have the user pass in raw("${field}" + 1).

B4nan · 2023-03-04T13:56:40Z

i don't think i'd like the runtime risk tho, because I could see folks doing the equivalent of invoice.number += 1 or other cases where i don't think serialization checks will kick in

It would be effectively a tagged object in place of a scalar, we could define both toJSON and toString methods, and both would throw. Doing + operation with a scalar will convert this object to string and throw - just tried to hack a PoC and it seems to work fine.

console.log({ sql: 'select 1', toString: () => { throw new Error() } } + 1)

i'm not sure i follow the "flushing part",

Your 3. point was "Flush UoW queue", that's what confused me.

the example in the initial post wouldn't flush at all, just issue a raw query (inside a transaction) and update the entity in memory with new value. i could see making the call more generic though, so instead of use-case specific inc/dec, have the user pass in raw("${field}" + 1).

But then it sounds exactly like #2397, or not?

parisholley · 2023-03-04T14:06:11Z

just tried to hack a PoC and it seems to work fine.

👍

point was "Flush UoW queue"

i don't quite remember why i included this, perhaps I was concerned that if the raw query will depend on changes already in the UoW that had not been flushed yet, the atomic update will effectively ignore local state and cause unexpected issues.

But then it sounds exactly like #2397, or not?

don't think so, that issue was about making changes to entities that may or may not be in the UoW already.

this particular ticket for me is important because TypeORM caused me so much heart burn from this exact problem, where one side of a request would increment something but another side would have a older/newer instance of a object, mutate and save and then overwrite the increment i had done somewhere else. so as long as there is some ORM blessed solution to prevent that problem, I am happy :)

B4nan · 2023-03-04T14:23:22Z

don't think so, that issue was about making changes to entities that may or may not be in the UoW already.

Your proposal takes a query and payload - imho it is the same. Which is what I can see achieved via that em.update method proposed there, and for this issue the approach with raw() and explicit find and flush seems like a good fit.

So if you'd like the results to be available right ahead, em.update would be the way to go, it would run the update query directly and update in-memory state if there is something already loaded. Or you could do it explicitly with the raw helper via flushing.

parisholley · 2023-03-04T14:31:31Z

i think part of the motivation for what i proposed in 2397 is to explicitly NOT run a query. it would just mark the entities in memory as dirty, if they don't exist, put the updates in some type of queue, so that if a future object matching that id is hydrated, it would use the queued value. for context, when i use Mikro, my goal is to limit the number of queries per request as much as possible, so if I can deal purely with objects without touching a transaction/flushing.

but yes, you are correct in that if em.update is able to do both then it could address both issues. perhaps another way to think of 2397, is actually making the nativeUpdate-like queries a little smarter.

B4nan · 2023-03-04T14:56:15Z

Hmm looks like I misread the description, I thought it is supposed to fire the update query right ahead, given you defined it as async - but it should be rather a sync method, as it only checks in-memory state and queues the update either way. So that issue is basically the same as #2392 (which is the in-memory matching part) followed by assign call? It all starts to make sense finally.

parisholley · 2023-03-04T16:25:46Z

exactly :)

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan · 2023-03-04T21:40:26Z

So #4094 should help with doing the atomic update via flush.

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

Tlepel · 2023-03-14T15:06:43Z

Is it an option to also allow the use of raw when creating an entity? It seems to me that #4094 only allows it to be used on updates.

A use case I have in mind is the following:

We have an entity, say Child, with the following properties:

name: the name of the Child
parentId: the id of the Parent (1-M relation)
childNumber: the number of this Child of the Parent

childNumber needs to be unique between children of the same parent, but not necessarily across all children. If we could use raw when creating the Child, it would be something like raw(`SELECT MAX(entity_number) + 1 FROM children WHERE parentId = ${parent.id}`)

I tried to find a way to do this currently, and finally found this issue which was related, although I'm not sure if the mentioned methodology would be fitting for this use case. I'd be happy to make a separate issue for this use case if it isn't.

B4nan · 2023-03-14T15:12:18Z

Is it an option to also allow the use of raw when creating an entity? It seems to me that #4094 only allows it to be used on updates.

It should already work with inserts in #4094, I will add some tests around that too. Inserts were already using returning statement so the code you see in that PR is mostly about updates as they were not having this support implemented.

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan · 2023-03-19T20:57:57Z

Internally, it would work similarly to how we work with FKs, the UoW automatically maps them to EntityIdentifier instances (basically proxy objects) and those are then assigned with the value once we have it, propagating it to the places where it is needed. So invoice.number = user.invoiceNumber in your example would just reassign the raw value, it would be still the same "raw value instance", so the same value once flushed.

I ended up disallowing the reuse of the raw statement, it holds an internal counter that is incremented on every assignment and throws it was already used. Propagating the value could be quite challenging as it could potentially require changing the order of queries during flush, and that is already enough of a complex problem. I guess it's better to just throw and let the developer handle it, probably with an explicit flush (or with that future version of em.update() that would run the update query early and map the raw values from database just like flush).

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

When you want to issue an atomic update query via flush, you can use the static `raw()` helper: ```ts const ref = em.getReference(Author, 123); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` The `raw()` helper returns special raw query fragment object. It disallows serialization (via `toJSON`) as well as working with the value (via [`valueOf()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/valueOf)). Only single use of this value is allowed, if you try to reassign it to another entity, an error will be thrown to protect you from mistakes like this: ```ts order.number = raw(`(select max(num) + 1 from orders)`); user.lastOrderNumber = order.number; // throws, it could resolve to a different value JSON.stringify(order); // throws, raw value cannot be serialized ``` Closes #3657

B4nan · 2023-03-19T23:09:57Z

Closing as raw() support is implemented via #4094.

When you want to issue an atomic update query via flush, you can use the static `raw()` helper: ```ts const ref = em.getReference(Author, 123); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` The `raw()` helper returns special raw query fragment object. It disallows serialization (via `toJSON`) as well as working with the value (via [`valueOf()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/valueOf)). Only single use of this value is allowed, if you try to reassign it to another entity, an error will be thrown to protect you from mistakes like this: ```ts order.number = raw(`(select max(num) + 1 from orders)`); user.lastOrderNumber = order.number; // throws, it could resolve to a different value JSON.stringify(order); // throws, raw value cannot be serialized ``` Closes #3657

When you want to issue an atomic update query via flush, you can use the static `raw()` helper: ```ts const ref = em.getReference(Author, 123); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` The `raw()` helper returns special raw query fragment object. It disallows serialization (via `toJSON`) as well as working with the value (via [`valueOf()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/valueOf)). Only single use of this value is allowed, if you try to reassign it to another entity, an error will be thrown to protect you from mistakes like this: ```ts order.number = raw(`(select max(num) + 1 from orders)`); user.lastOrderNumber = order.number; // throws, it could resolve to a different value JSON.stringify(order); // throws, raw value cannot be serialized ``` Closes mikro-orm#3657

When you want to issue an atomic update query via flush, you can use the static `raw()` helper: ```ts const ref = em.getReference(Author, 123); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` The `raw()` helper returns special raw query fragment object. It disallows serialization (via `toJSON`) as well as working with the value (via [`valueOf()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/valueOf)). Only single use of this value is allowed, if you try to reassign it to another entity, an error will be thrown to protect you from mistakes like this: ```ts order.number = raw(`(select max(num) + 1 from orders)`); user.lastOrderNumber = order.number; // throws, it could resolve to a different value JSON.stringify(order); // throws, raw value cannot be serialized ``` Closes #3657

parisholley added the enhancement New feature or request label Oct 25, 2022

B4nan added this to the 6.0 milestone Jan 26, 2023

B4nan added a commit that referenced this issue Mar 4, 2023

feat(core): support atomic updates via raw() helper

6a42ca5

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan mentioned this issue Mar 4, 2023

feat(core): support atomic updates via raw() helper #4094

Merged

B4nan added a commit that referenced this issue Mar 4, 2023

feat(core): support atomic updates via raw() helper

c2e62f1

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan added a commit that referenced this issue Mar 4, 2023

feat(core): support atomic updates via raw() helper

46f457d

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan added a commit that referenced this issue Mar 19, 2023

feat(core): support atomic updates via raw() helper

b5734f5

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan added a commit that referenced this issue Mar 19, 2023

feat(core): support atomic updates via raw() helper

8fdb0bf

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan added a commit that referenced this issue Mar 19, 2023

feat(core): support atomic updates via raw() helper

19e9056

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan added a commit that referenced this issue Mar 19, 2023

feat(core): support atomic updates via raw() helper

e59ac74

```ts const ref = em.getReference(User, 1); ref.age = raw(`age * 2`); await em.flush(); console.log(ref.age); // real value is available after flush ``` Closes #3657

B4nan closed this as completed Mar 19, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Atomic Update Support #3657

Atomic Update Support #3657

parisholley commented Oct 25, 2022 •

edited

Loading

B4nan commented Mar 4, 2023 •

edited

Loading

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023 •

edited

Loading

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

Tlepel commented Mar 14, 2023

B4nan commented Mar 14, 2023

B4nan commented Mar 19, 2023

B4nan commented Mar 19, 2023

Atomic Update Support #3657

Atomic Update Support #3657

Comments

parisholley commented Oct 25, 2022 • edited Loading

B4nan commented Mar 4, 2023 • edited Loading

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023 • edited Loading

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

parisholley commented Mar 4, 2023

B4nan commented Mar 4, 2023

Tlepel commented Mar 14, 2023

B4nan commented Mar 14, 2023

B4nan commented Mar 19, 2023

B4nan commented Mar 19, 2023

parisholley commented Oct 25, 2022 •

edited

Loading

B4nan commented Mar 4, 2023 •

edited

Loading

B4nan commented Mar 4, 2023 •

edited

Loading