Thursday, March 28, 2024
HomeJavaScriptAPI with NestJS #76. Working with transactions utilizing uncooked SQL queries

API with NestJS #76. Working with transactions utilizing uncooked SQL queries


One of many challenges when working with databases is protecting the integrity of the info. On this article, we discover ways to take care of it utilizing transactions.

A transaction can include a number of totally different directions. The essential factor a few transaction is that it both runs solely or doesn’t run in any respect. Let’s revisit the commonest instance to grasp the necessity for transactions.

Transferring cash from one checking account to a different include two steps:

  1. withdrawing cash from the primary account,
  2. including the identical amount of cash to the second account.

The entire operation failing means the integrity of the info remains to be intact. The amount of cash on each of the accounts stays intact. The worst state of affairs occurs when simply half of the above steps run efficiently. Think about the next scenario:

  1. withdrawing the cash reduces the amount of cash within the first account,
  2. including the cash to the second account fails as a result of the account was not too long ago closed.

The above state of affairs causes us to lose the integrity of our information. A particular sum of the cash disappeared from the financial institution and is in neither of the accounts.

ACID properties

Thankfully, we will take care of the above situation utilizing transactions. It ensures us the next properties:

Atomicity

The operations within the transaction kind a single unit. Subsequently, it both solely succeeds or fails utterly.

Consistency

A transaction progresses the database from one legitimate state to a different.

Isolation

A couple of transaction may happen in our database on the similar time with out having an invalid state of the info. For instance, one other transaction ought to detect the cash in a single checking account, however not in neither nor each.

Sturdiness

Once we commit the adjustments from the transaction, they should persist completely.

Writing transactions with PostgreSQL

At any time when we run a single question, PostgreSQL wraps it in a transaction that ensures the entire ACID properties. Moreover that, we will run a number of queries in a transaction. To try this, we will use the and  statements.

With the assertion, we provoke the transaction block. PostgreSQL executes all queries after the assertion in a single transaction. Once we run the assertion, PostgreSQL shops our adjustments within the database.

Within the above code, we first disconnect all posts from a given class. Then, we delete the class.

If deleting the class fails for any purpose, the posts should not faraway from the class. When that occurs, we should always discard the transaction utilizing the assertion. However, after all, we will additionally try this anytime we wish to abort the present transaction.

Utilizing transactions with node-postgres

We’ve used the node-postgres library on this sequence of articles to create a connection pool. Which means that we’ve a pool of a number of shoppers related to our database.

Utilizing the identical shopper occasion for all of our queries inside a transaction is essential. To try this, we have to modify our class.

database.service.ts

When operating , our question runs on any accessible shopper within the pool. That is positive if we don’t write transactions. To run a set of operations utilizing a specific shopper, we have to get it utilizing perform.

Let’s use the above data to delete rows from each and tables.

classes.repository.ts

A big factor above is that we name the methodology once we don’t want a specific shopper anymore. Because of that, it returns to the pool and turns into accessible once more.

Passing the shopper occasion between strategies

As our logic will get extra complicated, our transactions may occupy a couple of methodology. To take care of this, we will go the shopper occasion as an argument.

Within the earlier article, we realized how you can work with many-to-many relationships. When creating posts, we despatched the next information via the API:

Let’s write a technique that enables us to change the above put up. For instance, think about sending the next PUT request:

After analyzing the above payload, we will discover the next variations:

  • we have to take away the put up from the and classes,
  • we’ve so as to add the put up to the class with id .
posts.repository.ts

Within the above perform, we use the methodology that modifies the connection between the put up and the classes.

posts.repository.ts

A couple of vital issues are taking place above:

  • we decide what classes we have to hyperlink and unlink from a put up utilizing the methodology,
  • we take away and add classes related to the put up,
  • we return the record of classes associated to the put up after the above operations.

First, let’s check out the methodology.

getDifferenceBetweenArrays.ts

Above, we return the weather current within the first array however absent from the second.

The final half we have to analyze is how we take away and add classes to the put up. Eradicating classes could be very simple and entails a easy SQL question.

posts.repository.ts

Then again, including classes to a put up has a catch. If the person tries to make use of the id of a class that doesn’t exist, PostgreSQL throws the overseas key violation error. On this instance, we catch this error and assume that the person supplied the incorrect id.

posts.repository.ts

If you wish to see the entire , test it out on GitHub.

To catch the above situation elegantly, we’ve added the suitable error code to our enum.

postgresErrorCode.enum.ts

Because of the entire above, we will do the next actions in a single transaction:

  • replace the title and content material of the put up
  • examine the classes at present tied to the put up,
  • take away and add classes to the put up if mandatory,
  • return the modified put up along with the up to date record of classes.

Abstract

On this article, we’ve gone via the thought of transactions and realized why we would want them. We’ve additionally realized that we have to use a specific shopper from the connection pool for all queries in a specific transaction. To observe that, we first carried out a easy instance. Then, we went via a extra sophisticated transaction that concerned passing the shopper between a number of strategies. The above data is essential when caring in regards to the integrity of our database.

Sequence Navigation

<< API with NestJS #75. Many-to-many relationships utilizing uncooked SQL queries

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments