Watermelondb: Create or update record (batch)

[diegolmello]

Hey, guys.

What is the proper way of create or updating records in one statement?
I fetch the updated data and I need to save into the database.
I understand all validation must be made on javascript and that makes us to do try/catch everywhere.

await database.action(async () => {
  const postsCollection = database.collections.get('posts')
  try {
    // try to find post by id
    const post = await postsCollection.find('abcdef')
    // if it exists, update it
    await somePost.update(post => {
      post.title = 'Updated title'
    })
  } catch (error) {
    // if post was not found, create it
    const newPost = await postsCollection.create(post => {
      post.title = 'New post'
      post.body = 'Lorem ipsum...'
    })
  }
})

A bit verbose, but it works.
This is for one record, but the server returns 20 records, so it's not optimal.
We need batch then.

let statements = [
  database.action(async () => {
    const postsCollection = database.collections.get('posts')
    try {
      // try to find post by id
      const post = await postsCollection.find('abcdef')
      // if it exists, update it
      somePost.prepareUpdate(post => {
        post.title = 'Updated title'
      })
    } catch (error) {
      // if post was not found, create it
      const newPost = postsCollection.prepareCreate(post => {
        post.title = 'New post'
        post.body = 'Lorem ipsum...'
      })
    }
  })
]
database.batch(statements);

The code above raises an error.
I'm not sure why 🤔

Note:
In Realm, this can be achieved this way:

realm.write(() => {
  // just pass `true` as third argument
  realm.create('Post', {id: 1, title: 'New post', body: 'Lorem ispum'}, true);
});

https://realm.io/docs/javascript/latest/#creating-and-updating-objects-with-primary-keys

cc @radex

[diegolmello]

Just updating this issue...
I found in docs that batch only works for prepareCreate and prepareUpdate methods.

Also, it's possible to group several actions as we would call a batch, but resolve as Promises instead (this is nice when fetching new data and finding out what to do in the local db).

const postsCollection = database.collections.get('posts')
const actions = [
  database.action(async () => {
    try {
      // try to find post by id
      const post = await postsCollection.find('abcdef')
      // if it exists, update it
      await post.update(post => {
        post.title = 'Updated title'
      })
    } catch (error) {
      // if post was not found, create it
      const newPost = await postsCollection.create(post => {
        post.title = 'New post'
        post.body = 'Lorem ipsum...'
      })
    }
  })
  // more actions...
]
await Promise.all(actions);

It's not very nice, because it runs in a queue.

Even more, we can group actions, resolve them as promises and group only updates and deletes as batch:

const postsCollection = database.collections.get('posts')
const operations = []
const actions = [
  database.action(async () => {
    try {
      // try to find post by id
      const post = await postsCollection.find('abcdef')
      // if it exists, update it
      operations.push(post.prepareUpdate(post => {
        post.title = 'Updated title'
      }))
    } catch (error) {
      // if post was not found, create it
      operations.push(postsCollection.prepareCreate(post => {
        post.title = 'New post'
        post.body = 'Lorem ipsum...'
      }))
    }
  })
  // more actions...
]
await Promise.all(actions);
await database.batch(...operations);

Yeah... it makes several queries in line and send create/update in batch.
It works and it's pretty messy and untraceable, but it cleans up the bridge a little.

Now, what if we want to create/update a post and add a few comments in the same action using the last approach?
We can't.
We need to do it the first way... in a queue.
A query, a create/update and then another create... in a loop through the bridge.

@radex Is it possible to add actions into a batch?
Is it hard to implement?
Thanks.

[diegolmello]

It's sad, but I had to stop our migration from Realm because of the lack of a better batch.
The gif below shows a chats list just after logging in.
The app requests all rooms from an user and list them.
Of course this is not an easy task, but the inserts/updates shouldn't be executed in a queue.

[radex]

It's sad, but I had to stop our migration from Realm because of the lack of a better batch.

Hold on, you can do it with batch — the only thing you can't is deletes (YET), I just didn't have time to respond yet ;)

[diegolmello]

@radex Can I do a find followed by a create/update in a batch?
I don't think so.
That needs an action and batch only supports prepareCreate and prepareUpdate.

[radex]

Can I do a find followed by a create/update in a batch?
I don't think so.

I do think so :) Unless I really badly misunderstood your question — let me get back to you tomorrow

[diegolmello]

@radex We have several API routes to update local data.
Examples:

• rooms.get: returns all rooms since a date, but a room has roles too
• channels.history: returns all messages since a date, but a message has reactions and attachments too

Record by record, we need to ensure it's up-to-date. It may be outdated and we need to update it or it may never exist yet, so we need to create it. We need to guarantee the same for their children associations.
As I said above, every record must be an action and it needs to be executed at the same time in a batch.
Unfortunately, batch only accepts prepareCreate and prepareUpdate.

I'm eager to see your solution here 😰

[radex]

This:

const actions = [
  database.action(async () => {
     // ...
  })
]
await Promise.all(actions);
await database.batch(...operations);

is misguided. batch must be called within an Action — but you don't have to do one Action per operation — doesn't really make sense. An Action ought to encompass all related operations, and your finds, created, and updates are related.

Here's how you create or update multiple records in one go efficiently (yes, it's not the prettiest code and could be a bit easier, but should work unless I misunderstand your problem):

database.action(async () => {
  // check which posts already exist
  const existingPosts = await database.collections.get('posts').query(Q.where('id', Q.oneOf(['abcdef', 'dasdasd', 'asdasd'])))

  const postsToCreate = IDs that are not contained in existingPosts
  const poststoUpdate = Posts that are contained in existing Posts

 await database.batch(
   ...postsToUpdate.map(post => post.prepareUpdate(() => {
     post.title = 'Updated title'
   })),
   ... postsToCreate.map(postData => collection.prepareCreate(post => {
    post.title = 'New title'
  }))
 )
})

Does this make sense to you?

[radex]

Clarification: The confusing part (but currently necessary to enable 🍉's asynchronous nature while giving you safety of batch) is that all stuff passed to batch must be prepared operations — that is, synchronous stuff. But you can still do asynchronous stuff with it, you just have to prepare all the data to create or update beforehand, and then only pass to batch preparedCreates and preparedUpdates in one go

[diegolmello]

@radex That's a nice idea. One query to check all ids.
I'll try this and get back to you. Thanks!

[lakhman]

Deletes in a batch would be very useful, when I fetch new data I delete the existing rows in the database, this prints 100's of log debug statements like so:

[DB] Executed batch of 1 operations (first: destroyAllPermanently on events) in 727.5750000000116ms

If we could delete all the rows in a table in a single batch operation (with 1 log statement) like prepareCreate and prepareUpdate - that would be an awesome addition.

It's sad, but I had to stop our migration from Realm because of the lack of a better batch.

Hold on, you can do it with batch — the only thing you can't is deletes (YET), I just didn't have time to respond yet ;)

Any ideas when a delete feature could land?

[diegolmello]

In order to avoid calling destroyAllPermanently on a table, which can be very slow, I'm planning to take another approach as workaround until prepareDelete is implemented.
_I'm working in something else right now and didn't test this idea, but I'll write as it may be useful to someone else._

Instead of deleting everything from a table every time new data is fetched, having a json column in parent table may be faster.
For example, a rooms table has a child called rooms_roles.
Roles are [admin, owner, leader, user].

Instead of cleaning rooms_roles every time, we can store a json column roles in rooms with:

['admin', 'owner']

Please don't be angry with me. I know this is far away from best practices 😂

[radex]

Hold on, you can do it with batch — the only thing you can't is deletes (YET), I just didn't have time to respond yet ;)

Any ideas when a delete feature could land?

No earlier than May, June — and maybe later. Unless there's interest from the community to help out with implementation, in which case we might be able to get this done in April?

[radex]

Instead of cleaning rooms_roles every time, we can store a json column roles in rooms with:

@diegolmello It's completely fine, as long as you're aware of the downsides of using JSON (less flexibility in querying, and potential issues in resolving sync conflicts — but you're not using 🍉 sync so that doesn't apply to you)

[radex]

@diegolmello Hey, how's progress? Did this solution work?

[diegolmello]

@radex I spent too much time on this, so we'll stick to Realm for now.
I'll try 🍉again soon.

[radex]

@diegolmello I'm sad to hear that, but I completely understand! Switching a database is hard, and Realm has more resources for documentation. I'll close this issue for now, but I definitely encourage you to ask questions if you'd like to try 🍉 again in the future.