Mastering Database Transactions with Prisma: A Step-by-Step Guide for

Section 1:Ensuring Data Integrity through Database Transactions

1.0 Introduction to Database Transaction:

In the ever-evolving landscape of data-driven applications, maintaining the integrity of the underlying databases is paramount. The concept of database transactions stands as a stalwart guardian, offering a systematic approach to handle complex interactions with data, ensuring reliability and consistency.

At its core, a database transaction is a sequence of operations treated as a single, indivisible unit of work. The necessity for transactions arises from the inherent challenges of managing concurrent access to a database by multiple users or processes. As data manipulations become more intricate, the potential for conflicts and inconsistencies grows. This is where the ACID properties come into play.

ACID Properties: The Pillars of Database Reliability

The term "ACID" encapsulates a set of properties that define the characteristics of a robust and reliable database transaction:

Atomicity: Transactions are atomic, meaning they are treated as a single, indivisible unit. Either all operations within the transaction are executed successfully, or none of them are. This ensures that the database is never left in a partially updated or inconsistent state.
Consistency: A transaction brings the database from one valid state to another. It enforces rules and constraints, ensuring that the integrity of the data is preserved throughout the entire process.
Isolation: Transactions are executed in isolation from one another. The results of a transaction are not visible to other transactions until the transaction is committed. This prevents interference and maintains a separation of concerns between concurrent operations.
Durability: Once a transaction is committed, its changes become permanent, surviving subsequent failures, crashes, or power outages. This guarantees that the data is resilient and can be relied upon even in the face of unexpected events.

The Role of Transactions in Ensuring Reliability

Transactions play a pivotal role in guaranteeing the reliability of database operations. Consider a scenario where multiple users attempt to update the same set of records simultaneously. Without transactions, this could lead to a myriad of issues, including lost updates, incomplete operations, or data corruption.

By encapsulating a series of operations within a transaction, developers can create a secure and predictable environment. If any part of the transaction fails, the entire set of changes can be rolled back, preventing partial updates and maintaining a consistent state.

In the subsequent sections of this guide, we delve into the practical implementation of database transactions using Prisma, a powerful and intuitive database toolkit for Node.js. From setting up Prisma to executing transactions and handling errors, this guide aims to empower developers with the knowledge to master the art of managing database transactions effectively.

Example of What we will implement in this tutorial

Let's delve into the hands-on implementation of database transactions using Prisma. In this tutorial scenario, both Wilfred and David initiate fund transfers from their accounts, each commencing with a balance of $100. The system we are about to design ensures that a transfer can only proceed if the sender possesses sufficient funds. In cases where a transfer surpasses the available balance, it is automatically rejected, preserving the overall integrity of the transaction.

In this practical example, Wilfred endeavors two transfers—one for $100 and another exceeding his remaining balance. The anticipated outcome is for the valid transfer to be successfully executed, resulting in Wilfred having $0 and David having $200.

Stay tuned as we walk through the steps of implementing this transactional scenario using Prisma, a powerful database toolkit for Node.js.

Section 2: Using Prisma for Database Transactions

In the realm of Node.js development, Prisma emerges as a game-changing database toolkit, seamlessly integrating with TypeScript and offering a modern approach to database interactions. This section delves into the functionalities of Prisma and walks you through the essential steps to harness its power for effective database transactions.

2.1 Introduction to Prisma

Prisma stands as a beacon in the landscape of database toolkits, designed to simplify and elevate the way developers interact with databases. It goes beyond traditional ORMs (Object-Relational Mappers) by providing a type-safe and auto-generated query builder. By leveraging the benefits of TypeScript, Prisma ensures that your database interactions are not only efficient but also statically typed, catching potential errors at compile-time rather than runtime.

Key Features and Advantages:

Type Safety: Prisma generates TypeScript types based on your database schema, enabling developers to catch errors during development rather than runtime.
Auto-Generated Queries: Prisma creates a query builder based on your database schema, eliminating the need to handwrite complex SQL queries.
Schema Migrations: Effortlessly manage database schema changes with Prisma's migration capabilities.
Performance: Prisma optimizes queries for performance, ensuring that your database interactions are both swift and efficient.

2.2 Setting Up Prisma

Embarking on your Prisma journey begins with a straightforward setup process. Let's guide you through the steps to incorporate Prisma into your Node.js project.

Prerequisites and Project Setup

Before embarking on the journey of mastering database transactions with Prisma, it's essential to ensure that your development environment is appropriately configured. Follow these steps to set up your project and prerequisites:

Prerequisites

In order to successfully complete this guide, you need:

Node.js Installed: Ensure that Node.js is installed on your machine. If not, you can download and install it from nodejs.org.
PostgreSQL Database Server: Have a PostgreSQL database server running. Ensure it meets the system requirements specified for compatibility with Prisma.

Note: If you don't have a database server running and just want to explore Prisma, check out the Quickstart. Additionally, you can refer to my blog post where I provide detailed instructions on connecting to PostgreSQL using Supabase. The blog covers the process of setting up PostgreSQL on Supabase and obtaining the necessary credentials for a seamless integration experience.
Database Connection URL: Make sure you have your database connection URL at hand. This URL is crucial for establishing a connection between your Node.js application and the PostgreSQL database.

Create Project Setup

Let's start by setting up your project directory and configuring the necessary dependencies.

Create a Project Directory:

 $ mkdir database-prisma-transaction 
 $ cd database-prisma-transaction

Initialize a TypeScript Project:
```
 $ npm init -y 
 $ npm install prisma typescript ts-node @types/node --save-dev
```
This establishes the initial setup for your TypeScript application and adds Prisma as a development dependency.
Initialize TypeScript Configuration:
```
 $ npx tsc --init
```
This generates a tsconfig.json file for TypeScript configuration.
Invoke the Prisma CLI:
```
 $ npx prisma
```
Confirm that the Prisma CLI is working by invoking it.
Set Up Your Prisma Project:
```
 $ npx prisma init
```
This command performs two key actions:
- Creates a new directory named prisma containing a file named schema.prisma. This file includes the Prisma schema with your database connection variable and schema models.
- Creates the .env file in the root directory of the project, which is used for defining environment variables such as your database connection.
- Install .env package into your application
  
  Installing the dotenv Package in Your Application:
  
  To integrate environment variable functionality into your application, you'll need to install the dotenv package. This step ensures that your application can seamlessly access and utilize the variables defined in your .env file.
  
  Follow these steps to install the dotenv package:
  1. Open your terminal.
  2. Navigate to your project directory using the cd command:
```
 cd your-project-directory
```
  3. Install the dotenv package using npm (Node Package Manager):
```
 npm install dotenv
```
    This command installs the dotenv package and adds it to your project's dependencies.

With these prerequisites and project setup steps completed, you are now ready to dive into the intricacies of Prisma and explore its capabilities for handling database transactions in your Node.js projects. The subsequent sections will guide you through defining models, migrating the database, and executing transactions with Prisma.

Connecting to Your Database with Prisma:

To establish a connection to your database using Prisma, follow these steps to configure your Prisma schema:

1. Modify Prisma Schema:

Open your prisma/schema.prisma file and update the datasource block, setting the url field to your database connection URL:
```
 // prisma/schema.prisma

 datasource db {
   provider = "postgresql"
   url      = env("DATABASE_URL")
 }
```
The connection URL is typically configured through an environment variable, as demonstrated in the associated .env file.

I'm utilizing a PostgreSQL database set up through Railway. You might consider Railway for simplifying the process of configuring a PostgreSQL database and facilitating seamless deployment.

.env:
```
 //.env
 DATABASE_URL="postgresql://postgres:CDACGHUYHGTERUIUAeb*eG6-5-cbaG-6c6b@roundhouse.proxy.rlwy.net:23821/railway"
```
It's advisable to add .env to your .gitignore file to prevent unintentional exposure of your environment variables.

2. Configure Connection URL:

Adjust the connection URL in your .env file to reflect your specific database details. The format of the connection URL varies based on the database you're using. For PostgreSQL, it typically follows this structure:
```
 postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA
```
Here's a breakdown of each component:
- USER: Your database user's name
- PASSWORD: The password for your database user
- HOST: Your host name (use localhost for a local environment)
- PORT: The port where your database server is running (usually 5432 for PostgreSQL)
- DATABASE: The name of your database
- SCHEMA: The name of the schema inside the database (you can omit it if unsure, default is often public)

For example, a connection URL for a PostgreSQL database hosted on Heroku might look like this:

.env:

    DATABASE_URL="postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus?schema=hello-prisma"

When running PostgreSQL locally on macOS, user, password, and the database name may correspond to your OS user, like:

.env:

    DATABASE_URL="postgresql://wilfred:wilfred@localhost:5432/wilfred?schema=hello-postgres"

2.3 Defining Database Models

With Prisma seamlessly integrated into your project, it's time to define the models that represent your database structure. The Prisma schema serves as the blueprint for your data, and defining models is a pivotal step in ensuring the accuracy and clarity of your database interactions.

Example: Defining an "Account" Model

// schema.prisma
// Your schema.prisma file should look exactly like this

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}


// schema.prisma

model Account {
  id       Int      @id @default(autoincrement())
  name     String   @unique
  balance  Float
}

2.4 Migrating the Database

Prisma's migration capabilities empower you to manage changes in your database schema effortlessly. Once your models are defined, it's time to synchronize your database to reflect these changes.

Migration Command:

Execute the following command to apply the changes to your database:

npx prisma migrate dev --name init

This command creates the necessary tables and structures in your database to align with your defined models.

Installing and Generating Prisma Client:

Prisma Client allow you to view your database in the browser of your choice

To initiate Prisma Client in your project, follow these steps:

Install the @prisma/client Package:
```
 $ npm install @prisma/client
```
The installation command triggers prisma generate for you, which analyzes your Prisma schema and produces a customized version of Prisma Client aligned with your models.

The package installation is a crucial step in incorporating Prisma Client functionality into your Node.js application.
Updating Prisma Client and Database Schema: Whenever you make changes to your Prisma schema, it's essential to synchronize your database schema accordingly. Run the following commands:
- ```
    $ npx prisma generate
    $ npx prisma migrate dev
```
  This command applies the changes to your database schema and regenerates Prisma Client for development purposes.
- To View your database in browser:
```
  $ npx prisma studio
```

Ensure your package.json file is configured as follows before proceeding with the code:

{
  "name": "database-prisma-transaction",
  "version": "1.0.0",
  "description": "",
  "main": "index.ts",
  "scripts": {
    "start": "ts-node index.ts",
    "migrate": "npx prisma migrate dev",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "devDependencies": {
    "@types/node": "^20.10.0",
    "prisma": "^5.6.0",
    "ts-node": "^10.9.1",
    "typescript": "^5.3.2"
  },
  "dependencies": {
    "@prisma/client": "^5.6.0",
    "dotenv": "^16.3.1"
  }
}

Let's revisit the practical example we are implementing:

In this scenario, Wilfred initiates two transfers—one for $100 and another exceeding his remaining balance. The expected outcome is for the valid transfer to be successfully executed, resulting in Wilfred having $0, and David having $200.

For the successful balance, create a file called index.ts and add the following code:

// index.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

interface Account {
  name: string;
  balance: number;
}

// let fund the account first
const fundAccount = async (name: string, initialBalance: number): Promise<void> => {
    await prisma.account.create({
      data: {
        name,
        balance: initialBalance,
      },
    });
  };


// we can initiate the transfer process
const transferMoney = async (from: string, to: string, amount: number): Promise<void> => {
  await prisma.$transaction(async (tx) => {

// 1. Decrement amount from the sender.
const sender = await tx.account.update({
    data:{
        balance:{
            decrement:amount,
        },
    },
    where:{
        name:from,
    },
})
 // 2. Verify that the sender's balance didn't go below zero.
 if (sender.balance < 0) {
    throw new Error(`${from} doesn't have enough to send ${amount}`)
  }

  // 3. Increment the recipient's balance by amount
  const recipient = await tx.account.update({
    data: {
      balance: {
        increment: amount,
      },
    },
    where: {
      name: to,
    },
  })
  console.log(`${from} transfer  ${amount} to ${to}`)
  return recipient

  });
};



// Example usage
async function main() {
  try {

     // Fund Wilfred and David's accounts initially
     await fundAccount('Wilfred', 100); 
     await fundAccount('David', 100);   
     console.log('Wilfred account funded successfully with $100');
     console.log('David account funded successfully with $100');


    // This transfer is successful
    await transferMoney('Wilfred', 'David', 100);

    // This transfer is unsuccessfull
    await transferMoney('Wilfred','David',100);



  } catch (error) {
    console.error('Transfer failed:', error);
  } finally {
    await prisma.$disconnect();
  }
}

main();

In the terminal, run the following command to start your project and execute the file:

npm start

If the execution is successful, you'll observe the following output in your terminal:

Wilfred account funded successfully with $100
David account funded successfully with $100
Wilfred transfer  $100 to David
Transfer failed: Error: Wilfred doesn't have enough to send 100
    at /Users/wilfred/Desktop/Blog/Database-Prisma-Transaction/index.ts:38:11
    at Generator.next (<anonymous>)
    at fulfilled (/Users/wilfred/Desktop/Blog/Database-Prisma-Transaction/index.ts:5:58)

This output indicates that Wilfred and David's accounts were funded successfully with $100 each.The first Transfer was successfully However, the transfer failed due to insufficient funds in Wilfred's account, resulting in an error message stating that Wilfred doesn't have enough to send $100. The additional information includes the file path and line number where the error occurred.

To open Prisma Studio in the terminal, run the following command:

npx prisma studio

Your database should look exactly like this:

This concludes the discussion on database transactions using Prisma and TypeScript.

Find the link to the Github code

Feel free to share your thoughts or comments.

Wilfred