E-Commerce Store

Build a complete e-commerce backend with products, categories, customers, and orders using transactions

Models

Define the core e-commerce models with relationships:

models/Product.ts

import { defineModel, DataTypes, RelationType } from "stabilize-orm";

export const Product = defineModel({
  tableName: "products",
  timestamps: { createdAt: "createdAt", updatedAt: "updatedAt" },
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 200, required: true },
    slug: { type: DataTypes.STRING, length: 200, required: true, unique: true },
    price: { type: DataTypes.DECIMAL, required: true },
    stock: { type: DataTypes.INTEGER, required: true, defaultValue: 0 },
    categoryId: { type: DataTypes.STRING, required: true },
    isActive: { type: DataTypes.BOOLEAN, defaultValue: true },
    deletedAt: { type: DataTypes.DATETIME, softDelete: true },
    version: { type: DataTypes.INTEGER, optimisticLock: true },
  },
  relations: [
    {
      type: RelationType.ManyToOne,
      target: () => Category,
      property: "category",
      foreignKey: "categoryId",
    },
  ],
  scopes: {
    inStock: (qb) => qb.where("stock > ?", 0),
    active: (qb) => qb.where("isActive = ?", true),
    cheap: (qb, maxPrice: number) => qb.where("price <= ?", maxPrice),
  },
});

export const Category = defineModel({
  tableName: "categories",
  timestamps: { createdAt: "createdAt", updatedAt: "updatedAt" },
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 100, required: true, unique: true },
    slug: { type: DataTypes.STRING, length: 100, required: true, unique: true },
  },
  relations: [
    {
      type: RelationType.OneToMany,
      target: () => Product,
      property: "products",
      foreignKey: "categoryId",
    },
  ],
});

Transactional Order Creation

Create orders with items in a single atomic transaction:

examples/create-order.ts

const orderResult = await orm.transaction(async (txClient) => {
  const order = await orderRepo.create({
    id: generateUUID(),
    customerId: customer.id,
    status: "pending",
    totalAmount: 0,
  });

  const item1 = await orderItemRepo.create({
    id: generateUUID(),
    orderId: order.id,
    productId: laptop.id,
    quantity: 1,
    unitPrice: laptop.price,
  });

  const item2 = await orderItemRepo.create({
    id: generateUUID(),
    orderId: order.id,
    productId: phone.id,
    quantity: 2,
    unitPrice: phone.price,
  });

  const total = Number(laptop.price) * 1 + Number(phone.price) * 2;
  const updatedOrder = await orderRepo.update(order.id, {
    totalAmount: total,
  });

  return { order: updatedOrder, items: [item1, item2] };
});

console.log("Order total:", orderResult.order.totalAmount);

Product Scopes & Filtering

Use scopes and query builder for product filtering:

examples/product-filter.ts

// In stock + active products, sorted by price
const inStock = await productRepo
  .scope("inStock")
  .scope("active")
  .orderBy("price", "DESC")
  .execute(orm.client);

// Cheap products under a max price
const cheap = await productRepo
  .scope("cheap", 1500)
  .execute(orm.client);

// Search with LIKE
const searchResults = await productRepo
  .find()
  .where("name LIKE ?", "%Mac%")
  .execute(orm.client);

// Complex query builder
const expensiveInStock = await productRepo
  .find()
  .where("price > ?", 1000)
  .where("stock > ?", 0)
  .whereNotNull("categoryId")
  .orderBy("price", "DESC")
  .limit(5)
  .execute(orm.client);

Aggregate Statistics

Get product statistics using aggregates:

examples/product-stats.ts

const productStats = await productRepo.aggregate({
  count: "*",
  avg: ["price"],
  min: ["price"],
  max: ["price"],
  sum: ["stock"],
});
console.log("Stats:", productStats);
// { count_all: 5, avg_price: 1200, min_price: 249, max_price: 2499, sum_stock: 850 }

// Pluck product names
const productNames = await productRepo.pluck("name");
console.log("Names:", productNames);

// Count distinct categories
const distinctCats = await productRepo.countDistinct("categoryId");
console.log("Distinct categories:", distinctCats);

Bulk Upsert

Upsert multiple products at once:

examples/bulk-upsert.ts

const bulkProducts = await productRepo.bulkUpsert(
  [
    {
      id: generateUUID(),
      name: "iPad Air",
      slug: "ipad-air",
      price: 599.99,
      stock: 100,
      categoryId: electronicsId,
    },
    {
      id: generateUUID(),
      name: "AirPods Pro",
      slug: "airpods-pro",
      price: 249.99,
      stock: 500,
      categoryId: electronicsId,
    },
  ],
  ["slug"], // unique key for upsert matching
);
console.log("Upserted:", bulkProducts.length, "products");