db utils file and transactions

[AmosSzust]

Hi

I have a utils file for all db related operations where I defined db that makes all the queries

  db = pgp(connectionString);
  await db.connect();

I defined generic insert, select and update queries and I call them from the services (route/controller/service), for example

await insertIntoDB({
    tableName: "table_name",
    fields: ["field1", "field2"],
    values: [field1Value, field2Value],
  });

All is fine but now I want to execute several queries inside a transaction. I saw that I should do db.tx(async t => { but I don't have access to db from the service. Also, the generic methods are using db to execute the queries and I need t object to do it.

I'm a bit confused about if and how I can use transactions, does anyone have any pointers for me to look at?
Thanks

[vitaly-t]

Why are you executing await db.connect() there? It just wastes a connection.

For transactions, you can simply pass connection context t into the service. I do not understand the complication.

[AmosSzust]

The await db.connect() happens when the server starts so it happens once, meaning one connection. Unless I'm missing something, there's no connection waste here.

What do you mean by

connection context t

All the db related stuff are happening inside a db related utils file that acts as a black box for the services. The controller is calling the service so how can I pass the connection context to the service?

[vitaly-t]

db.connect() creates and returns a manually-controlled connection. If you do not use one, you are wasting one connection. The library manages connections automatically, it does not need you to manually open connections.

The controller is calling the service so how can I pass the connection context to the service?

You initiate a transaction, and then pass its connection context into the service.

[AmosSzust]

Thanks, I understand that this is enough

  const connectionString = `postgres://...`;
  const pgp = pgPromise();
  db = pgp(connectionString);
  pgp.pg.types.setTypeParser(20, (value) => parseInt(value, 10));

No need to connect

What do you mean by

You initiate a transaction

To start a transaction I should do db.tx(transactionFunction) but db is defined in its own utils file (black box for the service) so how can I pass it the transaction connection context?

[vitaly-t]

Your transaction callback function gets t - connection context, which you should pass into the service, to execute queries against. You cannot have a service fully agnostic of the database logic, which in this case needs to detect the end of all queries and automatically commit or rollback the transaction, plus release the connection. There is a lot going on inside that callback, you cannot just abstract it out, your service needs to be aware of that. See transactions.

[AmosSzust]

I see, I was hoping there is a way to create a transaction instance return db.tx() (in the db utils file) and send it back to the service to do its job and then end it when needed.

[vitaly-t]

Transaction logic requires that its instance is presented by a callback, it cannot be a variable, hence the limitation. And what you get from db.tx() is what's returned from the transaction callback.