Basic CRUD Operations

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

export const User = defineModel({
  tableName: "users",
  timestamps: { createdAt: "createdAt", updatedAt: "updatedAt" },
  columns: {
    id: {
      type: DataTypes.STRING,
      required: true,
      unique: true,
    },
    email: {
      type: DataTypes.STRING,
      length: 255,
      required: true,
      unique: true,
    },
    name: {
      type: DataTypes.STRING,
      length: 100,
      required: true,
    },
    age: {
      type: DataTypes.INTEGER,
    },
    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
    },
  },
});

import { orm } from "./db";
import { User } from "./models/User";
import { generateUUID } from "stabilize-orm";

const userRepository = orm.getRepository(User);

// Create a single user
const newUser = await userRepository.create({
  id: generateUUID(),
  email: "lwazicd@icloud.com",
  name: "Lwazi Dlamini",
  age: 30,
});

console.log("Created user:", newUser);

// Create multiple users at once
const users = await userRepository.bulkCreate([
  { id: generateUUID(), email: "sibusiso@swazi.com", name: "Sibusiso", age: 25 },
  { id: generateUUID(), email: "phindile@swazi.com", name: "Phindile", age: 35 },
]);

// Find all users
const allUsers = await userRepository.find().execute(orm.client);

// Find by ID
const user = await userRepository.findOne(user.id);

// Find one by conditions (TypeORM-style)
const userByEmail = await userRepository.findOneBy({ email: "lwazicd@icloud.com" });

// Find many by conditions
const activeUsers = await userRepository.findBy({ isActive: true });

// Query builder with conditions
const filtered = await userRepository
  .find()
  .where("age >= ?", 25)
  .where("isActive = ?", true)
  .orderBy("name", "ASC")
  .limit(10)
  .execute(orm.client);

// Update by ID
const updatedUser = await userRepository.update(user.id, {
  name: "Lwazi Smith",
  age: 31,
});
console.log("Updated:", updatedUser);

// Conditional bulk update
const affectedRows = await userRepository.updateBy(
  { isActive: false },
  { age: 0 }
);
console.log("Updated", affectedRows, "users");

// Increment/decrement a field
await userRepository.increment(user.id, "age", 1);
await userRepository.decrement(user.id, "age", 1);

// Delete by ID (soft delete if model has deletedAt)
await userRepository.delete(user.id);

// Conditional bulk delete
const deletedCount = await userRepository.deleteBy({ isActive: false });
console.log("Deleted", deletedCount, "users");

// Bulk delete by IDs
await userRepository.bulkDelete([id1, id2, id3]);

// Recover soft-deleted record
await userRepository.recover(user.id);

// Recover all soft-deleted
await userRepository.recoverAll();

// Count all users
const totalUsers = await userRepository.count();

// Count with conditions
const activeCount = await userRepository.count({ isActive: true });

// Check existence
const exists = await userRepository.exists({ email: "lwazicd@icloud.com" });

// Aggregate functions
const stats = await userRepository.aggregate({
  count: "*",
  avg: ["age"],
  min: ["age"],
  max: ["age"],
});
console.log(stats);
// { count_all: 10, avg_age: 28.5, min_age: 18, max_age: 65 }

// Count distinct values
const distinctAges = await userRepository.countDistinct("age");

// Get single column values
const emails = await userRepository.pluck("email");