how to use transactions with updates and create

Looking at the documentation it was not clear to me how I can do to use transactions in such a context:

const trx = await Database.beginTransaction()
    const user = await User.findOrFail(id)

    try {
      await user.merge(data)

      if (roles && roles.length && isAdmin) {
        await user.roles().sync(roles, trx)
      }

      if (permissions && permissions.length && isAdmin) {
        await user.permissions().sync(permissions, trx)
      }

      await user.save(trx)
      await trx.commit()

      await user.loadMany([
        'roles',
        'permissions'
      ])

      return user
    } catch(err) {
      await trx.rollback()
      throw new JsonException(err.message, err.status)
    }

help?

1 Like

Hi @henriqueweiand!

It seems about right, does it throw you some specific error or some specific problem with it?

3 Likes

Hi there, 2 suggestions, (first your code looks pretty good to me), now, you can split your code a little bit, you really don’t need that transaction, (not in that context), or at the end of the day, you can end up doing some real query with the transaction manually.

query = `
START TRANSACTION;
SELECT @userId:=id FROM users WHERE id = ${id};

DELETE FROM roles WHERE user_id = @userId;

${your_INSERT_into_ROLES_generated_query};

DELETE FROM permissions WHERE user_id = @userId;
${your_INSERT_into_PERMISSIONS_query};

UPDATE users SET 
  column_name1 = '${variable1}',
  column_name2 = '${variable2}', ...
WHERE id = @userId;

COMMIT;
`

// make sure to sanitize your query to avoid SQLInjection or use query params …
await Database.schema.raw(query)

Regards.

1 Like