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:
- withdrawing cash from the primary account,
- 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:
- withdrawing the cash reduces the amount of cash within the first account,
- 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 BEGIN and COMMIT statements.
With the BEGIN assertion, we provoke the transaction block. PostgreSQL executes all queries after the BEGIN assertion in a single transaction. Once we run the COMMIT assertion, PostgreSQL shops our adjustments within the database.
BEGIN; –Disconnecting posts from a given class DELETE FROM categories_posts WHERE category_id=1; –Deleting the class from the database DELETE FROM classes WHERE id=1; COMMIT; |
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 ROLLBACK 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 DatabaseService class.
database.service.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import { Inject, Injectable } from ‘@nestjs/frequent’; import { Pool } from ‘pg’; import { CONNECTION_POOL } from ‘./database.module-definition’;
@Injectable() class DatabaseService { constructor(@Inject(CONNECTION_POOL) personal readonly pool: Pool) {}
async runQuery(question: string, params?: unknown[]) { return this.pool.question(question, params); }
async getPoolClient() { return this.pool.join(); } }
export default DatabaseService; |
When operating this.pool.question(), 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 this.pool.join() perform.
Let’s use the above data to delete rows from each classes and posts_categories tables.
classes.repository.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
import { Injectable, NotFoundException } from ‘@nestjs/frequent’; import DatabaseService from ‘../database/database.service’;
@Injectable() class CategoriesRepository { constructor(personal readonly databaseService: DatabaseService) {}
async delete(id: quantity) { const poolClient = await this.databaseService.getPoolClient();
attempt { await poolClient.question(‘BEGIN;’);
// Disconnecting posts from a given class await poolClient.question( ` DELETE FROM categories_posts WHERE category_id=$1; `, [id], );
// Disconnecting posts from a given class const categoriesResponse = await poolClient.question( ` DELETE FROM classes WHERE id=$1; `, [id], );
if (categoriesResponse.rowCount === 0) { throw new NotFoundException(); }
await poolClient.question(` COMMIT; `); } catch (error) { await poolClient.question(` ROLLBACK; `); throw error; } lastly { poolClient.launch(); } }
// … }
export default CategoriesRepository; |
A big factor above is that we name the launch() 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:
{ “title”: “My first put up”, “content material”: “Hi there world!”, “categoryIds”: [1, 2, 3] } |
Let’s write a technique that enables us to change the above put up. For instance, think about sending the next PUT request:
{ “title”: “My modified put up”, “content material”: “Hi there world!”, “categoryIds”: [2, 4] } |
After analyzing the above payload, we will discover the next variations:
- we have to take away the put up from the 1 and 3 classes,
- we’ve so as to add the put up to the class with id 4.
posts.repository.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
import { Injectable, NotFoundException } from ‘@nestjs/frequent’; import DatabaseService from ‘../database/database.service’; import PostDto from ‘./put up.dto’; import PostWithCategoryIdsModel from ‘./postWithCategoryIds.mannequin’;
@Injectable() class PostsRepository { constructor(personal readonly databaseService: DatabaseService) {}
async replace(id: quantity, postData: PostDto) { const shopper = await this.databaseService.getPoolClient();
attempt { await shopper.question(‘BEGIN;’);
const databaseResponse = await shopper.question( ` UPDATE posts SET title = $2, post_content = $3 WHERE id = $1 RETURNING * `, [id, postData.title, postData.content], ); const entity = databaseResponse.rows[0]; if (!entity) { throw new NotFoundException(); }
const newCategoryIds = postData.categoryIds || [];
const categoryIds = await this.updateCategories( shopper, id, newCategoryIds, );
return new PostWithCategoryIdsModel({ ...entity, category_ids: categoryIds, }); } catch (error) { await shopper.question(‘ROLLBACK;’); throw error; } lastly { shopper.launch(); } }
// … }
export default PostsRepository; |
Within the above perform, we use the this.updateCategories methodology that modifies the connection between the put up and the classes.
posts.repository.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
import { Injectable, } from ‘@nestjs/frequent’; import DatabaseService from ‘../database/database.service’; import { PoolClient } from ‘pg’; import getDifferenceBetweenArrays from ‘../utils/getDifferenceBetweenArrays’;
@Injectable() class PostsRepository { constructor(personal readonly databaseService: DatabaseService) {}
personal async getCategoryIdsRelatedToPost( shopper: PoolClient, postId: quantity, ): Promise<quantity[]> { const categoryIdsResponse = await shopper.question( ` SELECT ARRAY( SELECT category_id FROM categories_posts WHERE post_id = $1 ) AS class_ids `, [postId], ); return categoryIdsResponse.rows[0].category_ids; }
personal async updateCategories( shopper: PoolClient, postId: quantity, newCategoryIds: quantity[], ) { const existingCategoryIds = await this.getCategoryIdsRelatedToPost( shopper, postId, );
const categoryIdsToRemove = getDifferenceBetweenArrays( existingCategoryIds, newCategoryIds, );
const categoryIdsToAdd = getDifferenceBetweenArrays( newCategoryIds, existingCategoryIds, );
await this.removeCategoriesFromPost(shopper, postId, categoryIdsToRemove); await this.addCategoriesToPost(shopper, postId, categoryIdsToAdd);
return this.getCategoryIdsRelatedToPost(shopper, postId); }
// … }
export default PostsRepository; |
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 getDifferenceBetweenArrays 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 getDifferenceBetweenArrays methodology.
getDifferenceBetweenArrays.ts
perform getDifferenceBetweenArrays<ListType extends unknown>( firstArray: ListType[], secondArray: unknown[], ): ListType[] { return firstArray.filter((arrayElement) => { return !secondArray.contains(arrayElement); }); }
export default getDifferenceBetweenArrays; |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
import { BadRequestException, Injectable, } from ‘@nestjs/frequent’; import { PoolClient } from ‘pg’; import PostgresErrorCode from ‘../database/postgresErrorCode.enum’; import isRecord from ‘../utils/isRecord’;
@Injectable() class PostsRepository {
personal async removeCategoriesFromPost( shopper: PoolClient, postId: quantity, categoryIdsToRemove: quantity[], ) { if (!categoryIdsToRemove.size) { return; } return shopper.question( ` DELETE FROM categories_posts WHERE post_id = $1 AND category_id = ANY($2::int[]) `, [postId, categoryIdsToRemove], ); }
// … }
export default PostsRepository; |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
import { BadRequestException, Injectable, } from ‘@nestjs/frequent’; import { PoolClient } from ‘pg’; import PostgresErrorCode from ‘../database/postgresErrorCode.enum’; import isRecord from ‘../utils/isRecord’;
@Injectable() class PostsRepository {
personal async addCategoriesToPost( shopper: PoolClient, postId: quantity, categoryIdsToAdd: quantity[], ) { if (!categoryIdsToAdd.size) { return; } attempt { await shopper.question( ` INSERT INTO categories_posts ( post_id, class_id ) SELECT $1 AS post_id, unnest($2::int[]) AS class_id `, [postId, categoryIdsToAdd], ); } catch (error) { if ( isRecord(error) && error.code === PostgresErrorCode.ForeignKeyViolation ) { throw new BadRequestException(‘Class not discovered’); } throw error; } }
// … }
export default PostsRepository; |
If you wish to see the entire PostsRepository, test it out on GitHub.
To catch the above situation elegantly, we’ve added the suitable error code to our PostgresErrorCode enum.
postgresErrorCode.enum.ts
enum PostgresErrorCode { UniqueViolation = ‘23505’, ForeignKeyViolation = ‘23503’, }
export default PostgresErrorCode; |
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.