Why should you use a DB transaction for updating, creating, and fetching multiple tables?

💡 Why Every Developer Should Understand Database Transactions

While working on a company project, I was tasked with updating multiple interdependent tables in the database. Inserting transaction details required the order table to be populated first, and updating user records relied on order data being available. Initially, I wrote separate queries for each operation, assuming it would be straightforward.

However, this led to data consistency issues. If one query failed in the middle of the process, the previous successful queries would still remain in the database, causing partial data to persist and creating a mess that was hard to debug and maintain.

That’s when I turned to my senior for guidance, and he introduced me to the concept of database transactions. I quickly realized how powerful and essential this technique is—especially when multiple operations must succeed or fail together.

Using a transaction block, I was able to group all dependent operations into a single atomic unit. If anything went wrong, the entire process would roll back automatically, leaving the database in a clean and consistent state.

This was a game-changer for me, and I believe every beginner developer should learn about transactions early on. They’re not just a "nice-to-have"—they're crucial for building reliable, bug-free systems.

Use Transaction When

How to use a transaction?

I am using a prisma ORM in my project.

ORM stands for Object-Relational Mapping. Prisma is a modern TypeScript ORM. Automatically maps your database tables to TypeScript objects. supports powerful features like transactions, relations, migrations, and raw queries.

export const createUserWithPosts = async (req: Request, res: Response) => {
  try {
    const result = await prisma.$transaction(async (tx) => { // Rename to tx
      const user = await tx.user.create({
        data: { email: 'test@example.com' }
      });

      const profile = await tx.profile.create({
        data: { userId: user.id, bio: 'Test bio' }
      });

      const post = await tx.post.create({
        data: {
          title: 'Test Post',
          content: 'Test content',
          authorId: user.id
        }
      });

      return post;
    });

    res.json(result);
  } catch (err) {
    console.error(err);
    res.status(500).end();
  }
};

🔁 How It Works Step-by-Step

1. Transaction Starts
   prisma.$transaction(async (tx) => { ... }) begins a transaction using the Prisma client. All operations inside use tx, the transactional version of Prisma.

2. Create User
   A new user is created with a dummy email.

3. Create Profile
   A profile is linked to that user via user.id.

4. Create Post
   A post is created and associated with the same user.

5. Commit or Rollback

   • If all three operations succeed, the transaction is committed — changes are saved to the database.

   • If any operation fails, the transaction is rolled back — everything is reverted and no changes are made.

6. Return Response
   If all goes well, it returns the created post. Otherwise, a 500 status is returned.

🙏 Thank You for Reading!

I genuinely appreciate you taking the time to read this blog. Whether you're just getting started with Prisma or looking to write more reliable database operations, I hope this helped deepen your understanding of transactions and ORM best practices.

If you found this useful, feel free to share it with your developer friends or drop a comment with your thoughts or questions — I’d love to hear from you!

Happy coding! 💻🚀