Transactions

Select ORMs to compare

Pick one or more ORMs from the bar above

Basic Transaction

SQL

BEGIN;

INSERT INTO users (name, email)
  VALUES ('Alice', 'alice@example.com');

UPDATE accounts SET balance = balance - 100
  WHERE user_id = 1;

COMMIT;

Django ORM

from django.db import transaction
from django.db.models import F

with transaction.atomic():
    user = User.objects.create(
        name="Alice", email="alice@example.com"
    )
    Account.objects.filter(user=user).update(
        balance=F("balance") - 100
    )

SQLAlchemy

from sqlalchemy import update

with Session(engine) as session:
    with session.begin():
        user = User(name="Alice", email="alice@example.com")
        session.add(user)
        session.flush()
        session.execute(
            update(Account)
            .where(Account.user_id == user.id)
            .values(balance=Account.balance - 100)
        )

SQLModel

from sqlalchemy import update

with Session(engine) as session:
    with session.begin():
        user = User(name="Alice", email="alice@example.com")
        session.add(user)
        session.flush()

        session.exec(
            update(Account)
            .where(Account.user_id == user.id)
            .values(balance=Account.balance - 100)
        )

Tortoise ORM

from tortoise.transactions import in_transaction
from tortoise.expressions import F

async with in_transaction() as conn:
    user = await User.create(
        name="Alice",
        email="alice@example.com",
        using_db=conn,
    )
    await Account.filter(
        user=user
    ).using_db(conn).update(
        balance=F("balance") - 100
    )

Peewee

with db.atomic():
    user = User.create(
        name="Alice",
        email="alice@example.com",
    )
    Account.update(
        balance=Account.balance - 100
    ).where(Account.user == user).execute()

Prisma

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { name: "Alice", email: "alice@example.com" },
  });
  await tx.account.update({
    where: { userId: user.id },
    data: { balance: { decrement: 100 } },
  });
});

Drizzle

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users)
    .values({ name: "Alice", email: "alice@example.com" })
    .returning();
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, user.id));
});

Kysely

import { sql } from 'kysely'

await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto('users')
    .values({
      name: 'Alice',
      email: 'alice@example.com',
    })
    .returning('id')
    .executeTakeFirstOrThrow()

  await trx
    .updateTable('accounts')
    .set({ balance: sql<number>`balance - 100` })
    .where('user_id', '=', user.id)
    .executeTakeFirst()
})

TypeORM

await dataSource.transaction(async (manager) => {
  const user = manager.create(User, {
    name: "Alice",
    email: "alice@example.com",
  });
  await manager.save(user);
  await manager.update(Account, { userId: user.id }, {
    balance: () => "balance - 100",
  });
});

MikroORM

import { raw } from "@mikro-orm/core";

await orm.em.transactional(async (em) => {
  const user = em.create(User, {
    name: "Alice",
    email: "alice@example.com",
  });
  await em.persistAndFlush(user);

  await em.nativeUpdate(Account, { user: user.id }, {
    balance: raw("balance - 100"),
  });
});

Sequelize

await sequelize.transaction(async (tx) => {
  const user = await User.create(
    { name: "Alice", email: "alice@example.com" },
    { transaction: tx },
  );

  await Account.decrement("balance", {
    by: 100,
    where: { userId: user.id },
    transaction: tx,
  });
});

Rollback on Error

SQL

BEGIN;

UPDATE accounts SET balance = balance - 100
  WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100
  WHERE user_id = 2;

-- on error:
ROLLBACK;
-- on success:
COMMIT;

Django ORM

from django.db import transaction
from django.db.models import F

try:
    with transaction.atomic():
        Account.objects.filter(user_id=1).update(
            balance=F("balance") - 100
        )
        Account.objects.filter(user_id=2).update(
            balance=F("balance") + 100
        )
        if error_condition:
            raise ValueError("Transfer failed")
except ValueError:
    pass  # transaction rolled back automatically

SQLAlchemy

from sqlalchemy import update

with Session(engine) as session:
    try:
        session.execute(
            update(Account)
            .where(Account.user_id == 1)
            .values(balance=Account.balance - 100)
        )
        session.execute(
            update(Account)
            .where(Account.user_id == 2)
            .values(balance=Account.balance + 100)
        )
        session.commit()
    except Exception:
        session.rollback()
        raise

SQLModel

from sqlalchemy import update

with Session(engine) as session:
    try:
        session.exec(
            update(Account)
            .where(Account.user_id == 1)
            .values(balance=Account.balance - 100)
        )
        session.exec(
            update(Account)
            .where(Account.user_id == 2)
            .values(balance=Account.balance + 100)
        )
        if error_condition:
            raise ValueError("Transfer failed")
        session.commit()
    except ValueError:
        session.rollback()

Tortoise ORM

from tortoise.transactions import in_transaction
from tortoise.expressions import F

try:
    async with in_transaction() as conn:
        await Account.filter(
            user_id=1
        ).using_db(conn).update(
            balance=F("balance") - 100
        )
        await Account.filter(
            user_id=2
        ).using_db(conn).update(
            balance=F("balance") + 100
        )
        if error_condition:
            raise ValueError("Transfer failed")
except ValueError:
    pass  # transaction auto-rolled back

Peewee

try:
    with db.atomic():
        Account.update(
            balance=Account.balance - 100
        ).where(Account.user == 1).execute()

        Account.update(
            balance=Account.balance + 100
        ).where(Account.user == 2).execute()

        if error_condition:
            raise ValueError("Transfer failed")
except ValueError:
    pass  # transaction rolled back automatically

Prisma

try {
  await prisma.$transaction(async (tx) => {
    await tx.account.update({
      where: { userId: 1 },
      data: { balance: { decrement: 100 } },
    });
    await tx.account.update({
      where: { userId: 2 },
      data: { balance: { increment: 100 } },
    });
    if (errorCondition) {
      throw new Error("Transfer failed");
    }
  });
} catch (e) {
  // transaction rolled back automatically
}

Drizzle

try {
  await db.transaction(async (tx) => {
    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} - 100` })
      .where(eq(accounts.userId, 1));
    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} + 100` })
      .where(eq(accounts.userId, 2));
    if (errorCondition) {
      tx.rollback();
    }
  });
} catch (e) {
  // transaction rolled back
}

Kysely

import { sql } from 'kysely'

try {
  await db.transaction().execute(async (trx) => {
    await trx
      .updateTable('accounts')
      .set({ balance: sql<number>`balance - 100` })
      .where('user_id', '=', 1)
      .executeTakeFirst()

    await trx
      .updateTable('accounts')
      .set({ balance: sql<number>`balance + 100` })
      .where('user_id', '=', 2)
      .executeTakeFirst()

    if (error_condition) {
      throw new Error('Transfer failed')
    }
  })
} catch {
  // transaction rolled back automatically
}

TypeORM

const queryRunner = dataSource.createQueryRunner();
await queryRunner.startTransaction();
try {
  await queryRunner.manager.update(
    Account, { userId: 1 },
    { balance: () => "balance - 100" }
  );
  await queryRunner.manager.update(
    Account, { userId: 2 },
    { balance: () => "balance + 100" }
  );
  await queryRunner.commitTransaction();
} catch (e) {
  await queryRunner.rollbackTransaction();
} finally {
  await queryRunner.release();
}

MikroORM

import { raw } from "@mikro-orm/core";

try {
  await orm.em.transactional(async (em) => {
    await em.nativeUpdate(Account, { user: 1 }, {
      balance: raw("balance - 100"),
    });

    await em.nativeUpdate(Account, { user: 2 }, {
      balance: raw("balance + 100"),
    });

    if (error_condition) {
      throw new Error("Transfer failed");
    }
  });
} catch {
  // transaction rolled back automatically
}

Sequelize

try {
  await sequelize.transaction(async (tx) => {
    await Account.decrement("balance", {
      by: 100,
      where: { userId: 1 },
      transaction: tx,
    });

    await Account.increment("balance", {
      by: 100,
      where: { userId: 2 },
      transaction: tx,
    });

    if (error_condition) {
      throw new Error("Transfer failed");
    }
  });
} catch {
  // transaction rolled back automatically
}

Savepoint / Nested

SQL

BEGIN;

INSERT INTO orders (user_id, amount)
  VALUES (1, 100)
  RETURNING id AS order_id;

SAVEPOINT sp1;

INSERT INTO order_items (order_id, product_id)
  VALUES (order_id, 42);

-- rollback only the savepoint
ROLLBACK TO sp1;

COMMIT;

Django ORM

from django.db import transaction

with transaction.atomic():
    order = Order.objects.create(
        user_id=1, amount=100
    )

    try:
        with transaction.atomic():
            OrderItem.objects.create(
                order_id=order.id, product_id=42
            )
            raise ValueError("item failed")
    except ValueError:
        pass  # only inner transaction rolled back

SQLAlchemy

with Session(engine) as session:
    with session.begin():
        order = Order(user_id=1, amount=100)
        session.add(order)
        session.flush()

        nested = session.begin_nested()
        try:
            session.add(
                OrderItem(
                    order_id=order.id, product_id=42
                )
            )
            nested.commit()
        except Exception:
            nested.rollback()

SQLModel

with Session(engine) as session:
    with session.begin():
        order = Order(user_id=1, amount=100)
        session.add(order)
        session.flush()

        try:
            with session.begin_nested():
                session.add(
                    OrderItem(
                        order_id=order.id,
                        product_id=42,
                    )
                )
                raise ValueError("item failed")
        except ValueError:
            pass  # only nested transaction rolled back

Tortoise ORM

from tortoise.transactions import in_transaction

async with in_transaction() as conn:
    order = await Order.create(
        user_id=1, amount=100, using_db=conn
    )

    try:
        async with in_transaction() as nested:
            await OrderItem.create(
                order_id=order.id,
                product_id=42,
                using_db=nested,
            )
            raise ValueError("item failed")
    except ValueError:
        pass  # only nested transaction rolled back

Peewee

with db.atomic():
    order = Order.create(
        user=1,
        amount=100,
    )

    try:
        with db.atomic():
            OrderItem.create(
                order=order,
                product=42,
            )
            raise ValueError("item failed")
    except ValueError:
        pass  # only inner transaction rolled back

Prisma

// No first-class savepoint API in Prisma Client;
// use raw SQL for true savepoints
await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId: 1, amount: 100 },
  });

  await tx.$executeRaw`SAVEPOINT sp1`;
  try {
    await tx.orderItem.create({
      data: { orderId: order.id, productId: 42 },
    });
  } catch {
    await tx.$executeRaw`ROLLBACK TO sp1`;
  }
});

// Alternative: sequential transactions
// (no true savepoint, but partial rollback effect)
const order = await prisma.$transaction(async (tx) => {
  return tx.order.create({
    data: { userId: 1, amount: 100 },
  });
});

try {
  await prisma.$transaction(async (tx) => {
    await tx.orderItem.create({
      data: { orderId: order.id, productId: 42 },
    });
  });
} catch {
  // order persists, only item insert failed
}

Drizzle

// Drizzle has no savepoint API;
// use raw SQL within a transaction
await db.transaction(async (tx) => {
  const [order] = await tx.insert(orders)
    .values({ userId: 1, amount: 100 })
    .returning();

  try {
    await tx.execute(sql`SAVEPOINT sp1`);
    await tx.insert(orderItems)
      .values({ orderId: order.id, productId: 42 });
  } catch {
    await tx.execute(sql`ROLLBACK TO sp1`);
  }
});

Kysely

await db.transaction().execute(async (trx) => {
  const order = await trx
    .insertInto('orders')
    .values({ user_id: 1, amount: 100 })
    .returning('id')
    .executeTakeFirstOrThrow()

  try {
    await trx.transaction().execute(async (inner) => {
      await inner
        .insertInto('order_items')
        .values({ order_id: order.id, product_id: 42 })
        .executeTakeFirst()

      throw new Error('item failed')
    })
  } catch {
    // only inner transaction rolled back
  }
})

TypeORM

const queryRunner = dataSource.createQueryRunner();
await queryRunner.startTransaction();
try {
  const order = await queryRunner.manager.save(
    Order, { userId: 1, amount: 100 }
  );

  await queryRunner.query("SAVEPOINT sp1");
  try {
    await queryRunner.manager.save(
      OrderItem, { orderId: order.id, productId: 42 }
    );
  } catch {
    await queryRunner.query("ROLLBACK TO sp1");
  }

  await queryRunner.commitTransaction();
} catch (e) {
  await queryRunner.rollbackTransaction();
} finally {
  await queryRunner.release();
}

MikroORM

await orm.em.transactional(async (em) => {
  const order = em.create(Order, {
    user: 1,
    amount: 100,
  });
  await em.persistAndFlush(order);

  try {
    await em.transactional(async (inner) => {
      const item = inner.create(OrderItem, {
        order: order.id,
        product: 42,
      });
      await inner.persistAndFlush(item);
      throw new Error("item failed");
    });
  } catch {
    // only inner transaction rolled back
  }
});

Sequelize

await sequelize.transaction(async (outerTx) => {
  const order = await Order.create(
    { userId: 1, amount: 100 },
    { transaction: outerTx },
  );

  try {
    await sequelize.transaction(
      { transaction: outerTx },
      async (innerTx) => {
        await OrderItem.create(
          { orderId: order.id, productId: 42 },
          { transaction: innerTx },
        );
        throw new Error("item failed");
      },
    );
  } catch {
    // only nested transaction rolled back
  }
});