I'm using Controller Layer/Service Layer/Repository Layer in my API with Postgres database (I'm using node-postgres).

In a given service, before inserting certain information A, I need to insert other information in other tables in the database. However, if there is a problem with one of the inserts, I would like to roll back the transaction. In node-postgres, rollbacks are done as follows:

const { Pool } = require('pg')
const pool = new Pool()
;(async () => {
  // note: we don't try/catch this because if connecting throws an exception
  // we don't need to dispose of the client (it will be undefined)
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
    const res = await client.query(queryText, ['brianc'])
    const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
    const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
    await client.query(insertPhotoText, insertPhotoValues)
    await client.query('COMMIT')
  } catch (e) {
    await client.query('ROLLBACK')
    throw e
  } finally {
    client.release()
  }
})().catch(e => console.error(e.stack))

The database connection is called on the Repository Layer. However, the rollback situation will only happen on the Service Layer. How do I solve this situation, since for architectural reasons, I can't call the database connection directly in the Service Layer? Is there a problem with my architecture?

CodePudding user response：

The easiest way to accomplish this is to put all the related transactions into a single method in your repository layer. This is generally "OK" because they are all fundamentally a single transaction.

If you need to support distributed transactions, you are better off using a unit of work pattern to implement holding onto the various transactions, and rolling back on the whole unit of work.