Performance Optimization

// ❌ Bad: Loads all columns
const users = await userRepo.find().execute(dbClient);

// ✅ Good: Only load needed fields
const users = await userRepo
  .find()
  .select("id", "email", "name")
  .execute(dbClient);

// ❌ Bad: Loads everything
const allPosts = await postRepo.find().execute(dbClient);

// ✅ Good: Paginate results
const page = 1;
const perPage = 20;

const posts = await postRepo
  .find()
  .limit(perPage)
  .offset((page - 1) * perPage)
  .execute(dbClient);

// Even better: Use cursor-based pagination
const posts = await postRepo
  .find()
  .where("id > ?", lastSeenId)
  .limit(20)
  .orderBy("id ASC")
  .execute(dbClient);

// ❌ Bad: N+1 query problem
const users = await userRepo.find().execute(dbClient);
for (const user of users) {
  const posts = await postRepo.find().where("userId = ?", user.id).execute(dbClient);
  // This runs a query for each user!
}

// ✅ Good: Eager load relationships
const users = await userRepo.find().with("posts").execute(dbClient);

export const User = defineModel({
  tableName: "users",
  columns: {
    id: {
      type: DataTypes.Integer
    },
    email: {
      type: DataTypes.String,
      unique: true, // Automatically creates an index
      required: true,
    },
    status: {
      type: DataTypes.String,
      index: true, // Add index for frequent filtering
    },
    createdAt: {
      type: DataTypes.Timestamp,
      index: true, // Index for sorting/filtering by date
    },
  },

});

import { Stabilize } from "stabilize-orm";

const orm = new Stabilize({
  type: DBType.Postgres,
  connectionString: process.env.DATABASE_URL!,
  cache: {
    enabled: true,
    redisUrl: process.env.REDIS_URL,
    ttl: 300, // Cache for 5 minutes
    cachePrefix: "myapp:",
    strategy: "cache-aside", // or "write-through"
  },
});

// Queries are automatically cached
const user = await userRepo.find().where("id = ?", 1).first();
const user2 = await userRepo.find().where("id = ?", 1).first(); // Hits cache

// Invalidate cache on updates
await userRepo.update(1, { name: "New Name" }); // Auto-invalidates cache

// ❌ Bad: Multiple individual inserts
for (const userData of users) {
  await userRepo.create(userData);
}

// ✅ Good: Single bulk insert
await userRepo.bulkCreate(users);

// ✅ Good: Bulk update
await userRepo.bulkUpdate([
  { id: 1, name: "User 1" },
  { id: 2, name: "User 2" },
]);

// ❌ Bad: Using LIKE with leading wildcard (can't use index)
const users = await userRepo.find().where("email LIKE ?", "%@stabilize.xyz").execute(dbClient);

// ✅ Good: Use exact matches or trailing wildcards
const users = await userRepo.find().where("email LIKE ?", "ciniso%@stabilzie.xyz").execute(dbClient);

// ❌ Bad: Using OR conditions (harder to optimize)
const users = await userRepo
  .find()
  .where("status = ?", "active")
  .orWhere("status = ?", "pending")
  .execute(dbClient);

// ✅ Good: Use IN clause
const users = await userRepo.find().where("status IN (?, ?)", "active", "pending").execute(dbClient);

// ✅ Good: Short, focused transaction
await dbClient.transaction(async (trx) => {
  const user = await userRepo.create({ email: "ciniso@stabilzie.xyz" }, trx);
  await profileRepo.create({ userId: user.id, bio: "..." }, trx);
});

// ❌ Bad: Long-running transaction with external API calls
await dbClient.transaction(async (trx) => {
  const user = await userRepo.create({ email: "ciniso@stabilzie.xyz" }, trx);
  await sendWelcomeEmail(user.email); // Don't do this in transaction!
  await profileRepo.create({ userId: user.id }, trx);
});

import { Stabilize, type LoggerConfig, LogLevel } from "stabilize-orm";

const loggerConfig: LoggerConfig = {
  level: LogLevel.Info,
  filePath: "logs/stabilize.log",
  maxFileSize: 5 * 1024 * 1024, // 5MB
  maxFiles: 3,
};

export const orm = new Stabilize(dbConfig, cacheConfig, loggerConfig);

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.Integer, required: true },
    email: { type: DataTypes.String, length: 100, required: true },
    isActive: { type: DataTypes.Boolean, required: true },
    createdAt: { type: DataTypes.DateTime },
    updatedAt: { type: DataTypes.DateTime },
  },
  scopes: {
    active: (qb) => qb.where("isActive = ?", true),
    recent: (qb, days: number) => qb.where("createdAt >= ?",
     new Date(Date.now() - days * 24 * 60 * 60 * 1000)),
  },
});

// Use scopes for consistent, optimized queries
const recentActiveUsers = await userRepo
  .scope("active")
  .scope("recent")
  .find()
  .execute(dbClient);