title: NodeJS - Which layer should I roll back the transaction with multiple inserts?
body: <p>I'm using Controller Layer/Service Layer/Repository Layer in my API with Postgres database (I'm using node-postgres).</p>
  <p>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:</p>
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>const { Pool } = require('pg')
  const pool = new Pool()
  ;(async () =&gt; {
    // 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 =&gt; console.error(e.stack))</code></pre>
  </div>
  </div>
  </p>
  <p>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?</p>