Performance Optimization

// Loads all columns
const users = await userRepo.find().execute(orm.client);

// Only load needed fields
const users = await userRepo
  .find()
  .select("id", "email", "name")
  .execute(orm.client);

// Or use selectColumns on the repository (no client argument)
const partial = await userRepo.selectColumns("id", "email");

// A single column, as a plain array of values
const emails = await userRepo.pluck("email");

// Built-in pagination
const page = await userRepo.paginate(1, 20);

// Or use query builder
const results = await userRepo.find()
  .limit(20)
  .offset(20)
  .execute(orm.client);

// Cursor-based pagination for large datasets
const results = await userRepo.findMany({
  take: 20,
  orderBy: { field: "id", direction: "ASC" },
});

// Bad: one extra query per user
const users = await userRepo.find().execute(orm.client);
for (const user of users) {
  const posts = await postRepo.find().where("userId = ?", user.id).execute(orm.client);
}

// Good: one batched query for the whole relation
const users = await userRepo.find().withRelations("posts").execute(orm.client);

// Good: the same option on the finders that take one
const user = await userRepo.findOne(id, { relations: ["posts"] });
const admins = await userRepo.findBy({ role: "admin" }, { relations: ["posts"] });
const paged = await userRepo.findAndCount({ relations: ["posts"] });
const many = await userRepo.findMany({ take: 20, relations: ["posts"] });

// Nested paths work too; "posts" is read once and shared
const thread = await userRepo.find()
  .withRelations("posts.comments", "posts.tags")
  .execute(orm.client);

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true },
    // unique creates <table>_<column>_uniq, so no index name is needed
    email: { type: DataTypes.STRING, unique: true, required: true },
    // index takes the name of the index to create
    status: { type: DataTypes.STRING, index: "idx_users_status" },
    createdAt: { type: DataTypes.DATETIME, index: "idx_users_created_at" },
  },
});

// autoMigrate creates any index that is missing. It is additive: it adds
// indexes and columns, and never drops either.
await orm.autoMigrate(User);

const orm = new Stabilize(
  dbConfig,
  { enabled: true, ttl: 300, redisUrl: process.env.REDIS_URL, strategy: "cache-aside" }
);

// Check cache stats
const stats = await orm.getCacheStats();
console.log("Hit ratio:", stats.hits / (stats.hits + stats.misses) * 100);

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

// Good: Single bulk insert
await userRepo.bulkCreate(users, { batchSize: 1000 });

// Bulk update
await userRepo.bulkUpdate([...]);

// Bulk upsert, keyed on one or more columns
await userRepo.bulkUpsert(users, ["email"]);

// Bad: leading wildcard, so no index can be used
const users = await userRepo.find()
  .where("email LIKE ?", "%@example.com")
  .execute(orm.client);

// Good: a trailing wildcard can use an index on email
const users = await userRepo.find()
  .where("email LIKE ?", "ciniso%@example.com")
  .execute(orm.client);

// Bad: multiple OR branches on the same column
await userRepo.find()
  .where("status = ?", "active")
  .orWhere("status = ?", "pending")
  .execute(orm.client);

// Good: a single IN predicate
await userRepo.find()
  .whereIn("status", ["active", "pending"])
  .execute(orm.client);

// Good: short, focused transaction
await orm.transaction(async (txClient) => {
  const user = await userRepo.create({ email: "ciniso@example.com" }, {}, txClient);
  await profileRepo.create({ userId: user.id, bio: "..." }, {}, txClient);
});

// Bad: doing slow work inside the transaction
await orm.transaction(async (txClient) => {
  const user = await userRepo.create({ email: "ciniso@example.com" }, {}, txClient);
  await sendWelcomeEmail(user.email); // third-party call - do it after commit
  await profileRepo.create({ userId: user.id }, {}, txClient);
});

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

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

export const orm = new Stabilize(dbConfig, { enabled: false, ttl: 60 }, loggerConfig);

// ORM level: reachability, measured by running "SELECT 1"
const ormHealth = await orm.healthCheck();
// { status, database, latencyMs, cacheStatus }

// Repository level: counts the model's rows
const repoHealth = await userRepo.healthCheck();
// { status, table, rows, latencyMs }  <- note "rows", not "cacheStatus"

// Pool numbers
const { active, idle, total } = await orm.poolStats();

const User = defineModel({
  tableName: "users",
  columns: { /* ... */ },
  scopes: {
    active: (qb) => qb.where("isActive = ?", true),
    recent: (qb, days: number) => qb.where(
      "createdAt >= ?",
      new Date(Date.now() - days * 24 * 60 * 60 * 1000).toISOString()
    ),
  },
});

// repository.scope() starts from find() and returns a QueryBuilder
const recentActive = await userRepo
  .scope("active")
  .scope("recent", 7)
  .execute(orm.client);

// The builder has scope() too, so a scope composes with a live query
const admins = await userRepo
  .find()
  .scope("active")
  .where("role = ?", "admin")
  .execute(orm.client);

// Bad: Counts all matching rows
const count = await userRepo.count({ role: "admin" });
if (count > 0) { ... }

// Good: Stops at first match
const exists = await userRepo.exists({ role: "admin" });
if (exists) { ... }

// Bad: Loads full objects
const users = await userRepo.find().execute(orm.client);
const emails = users.map(u => u.email);

// Good: Only fetches the column
const emails = await userRepo.pluck("email");