Transaction with reserve

[vineboneto]

Hello everyone,

I am currently working on integrating the postgres library into my architecture and have come up with a transaction handling method that I want to ensure is safe and effective. Below is the simplified version of my code:

import postgres from "postgres";

const sql = postgres({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "1234",
  database: "teste",
  max: 20,
});

class Context {
  #tx = null;
  #error = false;

  constructor(error) {
    this.#tx = null;
    this.#error = error;
  }

  async begin(trx) {
    this.#tx = trx;
    await this.#tx`BEGIN`;
  }

  async rollback() {
    await this.#tx`ROLLBACK`;
  }

  async commit() {
    await this.#tx`COMMIT`;
  }

  getError() {
    return this.#error;
  }

  get() {
    if (!this.#tx) return sql;
    return this.#tx;
  }
}

class Repository1 {
  #ctx = null;

  constructor(ctx) {
    this.#ctx = ctx;
  }

  async exec() {
    const sql = await this.#ctx.get();
    await sql`select * from temp`;
  }
}

class Repository2 {
  #ctx = null;

  constructor(ctx) {
    this.#ctx = ctx;
  }

  async exec() {
    const sql = await this.#ctx.get();
    const data =
      await sql`insert into temp (name) values ('Vinicius') returning id`;
    if (this.#ctx.getError()) {
      throw new Error("Error");
    }
  }
}

class Controller {
  #repo1 = null;
  #repo2 = null;

  constructor(repo1, repo2) {
    this.#repo1 = repo1;
    this.#repo2 = repo2;
  }

  async handle() {
    const data = await this.#repo1.exec();
    await this.#repo2.exec();
    return data;
  }
}

class RunDB {
  #controller = null;
  #ctx = null;

  constructor(controller, ctx) {
    this.#controller = controller;
    this.#ctx = ctx;
  }

  async handle() {
    const trx = await sql.reserve();
    try {
      this.#ctx.begin(trx);
      const response = await this.#controller.handle();
      await this.#ctx.commit();
      return response;
    } catch (error) {
      this.#ctx.rollback();
      console.error(error);
    } finally {
      trx.release();
    }
  }
}

async function exec(error = false) {
  const ctx = new Context(error);
  const repo1 = new Repository1(ctx);
  const repo2 = new Repository2(ctx);
  const controller = new Controller(repo1, repo2);
  const decorator = new RunDB(controller, ctx);
  await decorator.handle();
}

const promises = Array(10000)
  .fill()
  .map((_, idx) => exec(idx % 2 === 0));

await Promise.allSettled(promises);

sql.end();

In this setup, I use classes like Context, Repository1, Repository2, Controller, and RunDB to manage database transactions. My main concern is about the safety and efficiency of this approach.

I appreciate any guidance or feedback you can offer. Thank you!

[porsager]

Why not simply use https://github.com/porsager/postgres#transactions ?

[vineboneto]

Thank you for answering. I have concerns about how this approach aligns with the principles of clean architecture in my current system. Adopting this method would mean a tighter integration of each controller and repository with the specifics of managing database transactions. This could lead to a less clean and more interdependent architecture.

In addition, this pattern would necessitate passing the sql instance to every method in each repository involved in a transaction. This would increase complexity and deviate from my preferred architecture, where interactions with the database are clearly separated from business logic. I am seeking a solution that ensures both security and efficiency in transactions while maintaining clear separation between the different layers of the application.

[vineboneto]

Hello again,

I managed to implement a solution following the recommended documentation. I'm not sure if it's the best way for my use case, but it seems to work well. I'd appreciate any suggestions or good recommendations in advance. Here's how the implementation turned out:

import postgres from "postgres";

const sql = postgres({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "1234",
  database: "teste",
  max: 20,
});

class Context {
  #tx = null;
  #error = false;

  constructor(error) {
    this.#tx = null;
    this.#error = error;
  }

  set(trx) {
    this.#tx = trx;
  }

  error() {
    return this.#error;
  }

  get() {
    if (!this.#tx) return sql;
    return this.#tx;
  }
}

class Repository1 {
  #ctx = null;

  constructor(ctx) {
    this.#ctx = ctx;
  }

  async exec() {
    const sql = await this.#ctx.get();
    await sql`insert into temp (name) values ('Vinicius') returning id`;
    const data = await sql`select * from temp`;
    return data;
  }
}

class Repository2 {
  #ctx = null;

  constructor(ctx) {
    this.#ctx = ctx;
  }

  async exec() {
    const sql = await this.#ctx.get();
    const data =
      await sql`insert into temp (name) values ('Vinicius') returning id`;
    if (this.#ctx.error()) {
      throw new Error("Error");
    }
  }
}

class Controller {
  #repo1 = null;
  #repo2 = null;

  constructor(repo1, repo2) {
    this.#repo1 = repo1;
    this.#repo2 = repo2;
  }

  async handle() {
    const data = await this.#repo1.exec();
    await this.#repo2.exec();
    return data;
  }
}

class RunDB {
  #controller = null;
  #ctx = null;

  constructor(controller, ctx) {
    this.#controller = controller;
    this.#ctx = ctx;
  }

  async handle() {
    const result = await sql.begin(async (tx) => {
      this.#ctx.set(tx);
      return this.#controller.handle();
    });

    return result;
  }
}

async function exec(error = false) {
  const ctx = new Context(error);
  const repo1 = new Repository1(ctx);
  const repo2 = new Repository2(ctx);
  const controller = new Controller(repo1, repo2);
  const decorator = new RunDB(controller, ctx);
  const result = await decorator.handle();
}

const promises = Array(1000)
  .fill()
  .map((_, idx) => exec(idx % 2 === 0));

console.time();
await Promise.allSettled(promises);
console.timeEnd();

sql.end();