A Sale as One Database Transaction in NestJS and TypeORM

A sale touches many things at once. A customer might need to be created. Stock has to go down. A phone has to flip to "sold" so nobody sells it twice. Totals, a payment status and an invoice have to be written. If the server dies halfway, you do not want a customer with no invoice, or stock that vanished without a sale.

That is what database transactions are for. This post shows how I made the checkout of a mobile-shop point-of-sale system one transaction in NestJS with TypeORM, and then points at a concurrency gap that most tutorials, including my first version, leave open.

The system is described in the Mobile Ganj case study. Here I focus on the sale.

What a sale has to do

In order:

1. Find the customer by phone number, or create them (a new customer needs a name).
2. For each cart item: load the product, check there is enough stock, apply the rules (a phone can only be sold one at a time), copy the IMEI and warranty onto the sale line, and deduct stock.
3. Work out the discount (fixed amount or percentage) and the grand total.
4. Refuse a payment larger than the total.
5. Set the payment status: paid, partially paid or due.
6. Generate an invoice number and save the sale and its lines.

Either all of that happens or none of it does.

The shape of a TypeORM transaction

TypeORM gives you a QueryRunner. You connect, start a transaction, do your work through the runner's manager, then commit, or roll back on any error. The finally block always releases the connection back to the pool.

async create(dto: CreateSaleDto, userId: string) {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    // ...all the work, using queryRunner.manager...
    await queryRunner.commitTransaction();
    return savedSale;
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release();
  }
}

Forgetting release() is the classic bug: it leaks connections, and the app freezes under load an hour after deploy. Putting it in finally makes that impossible.

The stock rules

Phones and accessories behave differently, so the code is category-aware:

for (const item of dto.items) {
  const product = await this.productRepository.findOne({ where: { id: item.productId } });
  if (!product) throw new NotFoundException(`Product ${item.productId} not found`);

  if (product.stockQty < item.quantity) {
    throw new BadRequestException(`Insufficient stock for ${product.title}`);
  }
  if (product.category === ProductCategory.PHONE && item.quantity !== 1) {
    throw new BadRequestException('Phone quantity must be 1');
  }

  product.stockQty -= item.quantity;
  if (product.category === ProductCategory.PHONE) {
    product.status = ProductStatus.SOLD;
  } else if (product.stockQty === 0) {
    product.status = ProductStatus.OUT_OF_STOCK;
  }
  await queryRunner.manager.save(product);
}

A phone is a unique unit: quantity must be 1, and once sold it is marked SOLD. An accessory is a quantity that counts down and becomes OUT_OF_STOCK at zero.

Each sale line also copies the IMEI and warranty from the product at the moment of sale. That is deliberate: if someone edits the product next month, last month's invoice must not change.

Money and status

let discountAmount = 0;
if (dto.discountType === 'fixed') discountAmount = dto.discountValue;
else discountAmount = (subtotal * dto.discountValue) / 100;

const grandTotal = subtotal - discountAmount;
const dueAmount = grandTotal - dto.paidAmount;

if (dto.paidAmount > grandTotal) {
  throw new BadRequestException('Paid amount cannot exceed grand total');
}

const status =
  dueAmount === 0 ? PaymentStatus.PAID
  : dto.paidAmount > 0 ? PaymentStatus.PARTIAL_PAID
  : PaymentStatus.DUE;

The overpayment check throws after stock was already deducted, and that is fine: the exception triggers the rollback and the deducted stock is restored. This is the transaction earning its keep. Without it you would have to remember to undo each step by hand, and you would forget one.

(If you are building something with real money, round each amount to cents in one helper, as I describe in Don't Store Invoice Status: Derive It from Payments.)

The gap: reads outside the lock

Here is the honest part. My first version loaded each product with this.productRepository.findOne(...), the normal repository, not the transaction's manager. That has two consequences:

1. The read does not take part in the transaction.
2. Nothing stops two cashiers selling the last unit at the same moment. Both read stockQty = 1, both pass the check, both subtract one, and one of them ends up selling something that is not there (or the stock goes to -1).

A transaction alone does not prevent this. Isolation level and locking do. The fix is to read through the same runner and ask the database to lock the row until the transaction ends:

const product = await queryRunner.manager.findOne(Product, {
  where: { id: item.productId },
  lock: { mode: 'pessimistic_write' },
});

With pessimistic_write (SELECT ... FOR UPDATE in MySQL and PostgreSQL), the second cashier's transaction waits for the first to finish, then reads the updated stock and fails the check correctly. Two details to remember:

• Lock rows in a consistent order (sort the cart by product id), or two carts that contain the same products in a different order can deadlock.
• Keep the transaction short. Do not call an external service while rows are locked.

An alternative that needs no explicit lock is a single guarded update, UPDATE product SET stock_qty = stock_qty - ? WHERE id = ? AND stock_qty >= ?, then checking that one row changed. It is simple and fast, and it works well when each line is independent.

I am telling you about this gap because a post that only shows the happy path teaches people to ship the bug. A transaction makes your writes atomic. It does not, by itself, make your reads safe.

A few smaller habits that paid off

• Create the customer inside the transaction (queryRunner.manager.save), so a failed sale does not leave a stray customer behind.
• Generate the invoice number in the service, once, right before saving. Do not let the client choose it.
• Return the saved sale with its relations after the commit, not before. The caller should never receive data that might still roll back.
• Write one test per rule: oversell, phone quantity 2, overpayment, partial payment. Each is a few lines against a test database, and each protects real money.

What to take from this

• A multi-step business operation that must succeed or fail as a whole belongs in one transaction.
• With TypeORM: QueryRunner, work through its manager, commit, roll back on error, and always release in finally.
• Reads that decide a write must be inside the transaction too, with a row lock or a guarded update, or two requests can both win.
• Copy the facts an invoice needs onto its lines, so history never changes by accident.

The transaction is the easy half. The lock is the half that decides whether your stock numbers are true.