DEV Community: Oleksandr Holyshevskyi

Mastering SQL Transactions: The Power of COMMIT and ROLLBACK in Database Management

Oleksandr Holyshevskyi — Fri, 29 Dec 2023 13:01:07 +0000

Introduction

SQL transactions are a fundamental aspect of database management that play a crucial role in maintaining data integrity.
Among the key components of transactions, the COMMIT statement stands out as a linchpin for ensuring that changes made to a database are permanent.
In this blog post, we'll explore what SQL transactions and the COMMIT statement are, the problems they solve, and how to use SQL queries with and without transactions.

What is a SQL Transaction?
In the database world, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work.
The primary purpose of transactions is to maintain the consistency and integrity of data.
A transaction typically involves multiple steps, such as reading data, making changes, and saving those changes to the database.

The COMMIT Statement

The COMMIT statement is a critical part of the SQL transaction model. When a COMMIT statement is executed,
it marks the end of a transaction, confirming that all changes made during the transaction are permanent and should be saved to the database.
In other words, COMMIT is like saying, "I'm done with my changes; please make them permanent."

Problems Solved by COMMIT

Data Consistency: COMMIT ensures that a transaction is completed successfully before making changes permanent. If an error occurs during the transaction, the COMMIT is not executed, and the changes are rolled back, maintaining the consistency of the data.
Concurrency Control: In multi-user environments, transactions help manage concurrent access to data. COMMIT ensures that one transaction's changes do not interfere with another's until explicitly instructed to do so.

Use COMMIT

How to Use SQL Queries with and without Transactions (COMMIT):

Without Transactions (Auto-Commit mode)

In many database systems, SQL statements are executed in auto-commit mode by default. Each statement is treated as a separate transaction, and changes are automatically committed to the database.

-- Auto-commit mode (default behavior)
INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000);
-- The above statement is automatically committed

With Transactions (Manual COMMIT)

Consider a scenario where you need to transfer funds between two bank accounts.
Using transactions ensures the atomicity of the operation, meaning either the entire transaction succeeds, or it fails entirely with no partial changes committed.

-- Start a transaction
BEGIN TRANSACTION;

-- Deduct funds from Account A
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A';

-- Add funds to Account B
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B';

-- Commit the transaction
COMMIT;
-- or Rollback the transaction in case of errors
-- ROLLBACK;

Benefits of Using Transactions:

Atomicity:
Ensures that a series of SQL statements are treated as a single, indivisible unit of work.
Either all changes are committed, or none are.
Consistency:
Maintains the integrity and consistency of data, as the changes are applied as a cohesive whole.
Isolation:
Provides a level of isolation between transactions, preventing interference between concurrent operations.
Durability:
Guarantees that committed transactions are permanent and survive system failures.

Conclusion

Understanding SQL transactions and the role of COMMIT is crucial for maintaining data integrity and consistency.
Whether you're working in auto-commit mode or manually managing transactions, being mindful of these concepts ensures that your database operations are robust and reliable.

Originally posted on https://oholsyhevskyi.com/blog/commit-and-rollback-in-db

🌟 Functional Programming in Automation Testing with TypeScript and Playwright 🤖🚀 Part II

Oleksandr Holyshevskyi — Wed, 25 Oct 2023 07:17:43 +0000

Welcome to the second part of our exploration into functional programming in automation testing. In this continuation, we will delve deeper into the practical implementation of the concepts we've
discussed so far.
Here's what you can expect in this part:

Installing Required Libraries
- Step 1: Install TypeScript
- Step 2: Install Playwright
- Step 3: Install fp-ts (Functional Programming in TypeScript)

Installing Required Libraries

Here are step-by-step instructions for installing TypeScript, Playwright, and fp-ts. We'll also include code snippets and commands to help you through the installation process.
Please note that you need Node.js installed before proceeding with these installations. If you haven't already installed Node.js, you can download it from the official website: Node.js

Step 1: Install TypeScript

Open your command-line interface (CLI) or terminal.
Use Node Package Manager (npm) to install TypeScript globally:

npm install -g typescript

Verify the TypeScript installation by running the following command:

tsc --version

This command should display the installed TypeScript version.

Step 2: Install Playwright

Open your CLI or terminal.
Create a new directory for your Playwright project, if you haven't already:

mkdir my-playwright-project-fp
cd my-playwright-project-fp

Initialize a new Node.js project in your directory:

npm init -y

Install Playwright by running the following command:

npm init playwright@latest

Once Playwright is installed, you can use it with JavaScript or TypeScript for your automation testing. TypeScript is recommended for type safety.

Step 3: Install fp-ts (Functional Programming in TypeScript)

Open your CLI or terminal.
Navigate to your project directory (e.g., my-playwright-project-fp).
Install fp-ts as a dependency for your project:

npm install fp-ts

You can now start using fp-ts in your TypeScript code. Make sure to import the necessary modules as needed in your TypeScript files.

🌟 Functional Programming in Automation Testing with TypeScript and Playwright 🤖🚀 Part I

Oleksandr Holyshevskyi — Sun, 22 Oct 2023 20:02:02 +0000

Introduction
- Functional programming in automation testing
- Why TypeScript and Playwright?

Introduction

Functional programming in automation testing

Functional programming in automation testing is a paradigm that leverages the principles of functional programming to design and write test scripts.
Unlike traditional imperative testing approaches, functional programming focuses on creating test cases as pure functions, emphasizing immutability and composability.
By using technologies like TypeScript and Playwright, testers can build robust, maintainable, and highly readable test frameworks.
This approach offers numerous advantages, including improved code quality, enhanced test coverage, and increased test automation efficiency.
In this post, we will delve deeper into the world of functional programming for automation testing, exploring its benefits and practical implementation.

Why TypeScript and Playwright?

Using TypeScript and Playwright for creating a test framework holds significant advantages in the context of automation testing:

TypeScript's Strong Typing: TypeScript provides static typing, which means that you catch errors at compile-time rather than runtime. This reduces the chances of introducing bugs in your test code and enhances code quality. It also offers code completion and better code documentation, making it easier to write and maintain test scripts.
Maintainability: TypeScript enforces strict coding standards and best practices, which results in more maintainable code. As automation tests are often long-lived, this is crucial for the longevity of your test framework.