Ìlànà (pronounced "ee-LAH-nah") - A Yoruba word meaning "pattern," "system," or "protocol."

A fully-featured, Eloquent-style ORM for Node.js with automatic TypeScript support. IlanaORM provides complete feature parity with Laravel's Eloquent ORM, following established patterns and protocols for database interaction, modeling, querying, relationships, events, casting, migrations, and more.

Table of Contents

• Features
• Installation
• Quick Start
• Configuration
• CLI Commands
• Models
• Query Builder
• Relationships
• Migrations
• Seeders
• Model Factories
• Database Connection
• Schema Builder
• Transactions
• Advanced Features
• Complete API Reference
• TypeScript Support
• Performance & Best Practices
• Testing

Features

🏗️ Active Record Pattern

• Full CRUD operations with intuitive, chainable API
• Model-based database interactions
• Automatic table mapping and attribute handling
• Built-in validation and mass assignment protection
• Direct property access for model attributes

🔗 Advanced Relationships

• One-to-One: hasOne(), belongsTo()
• One-to-Many: hasMany(), belongsTo()
• Many-to-Many: belongsToMany() with pivot tables and timestamps
• Polymorphic: morphTo(), morphMany() with model registry
• Has-Many-Through: Complex nested relationships
• Eager Loading: Prevent N+1 queries with with() and constraints
• Lazy Loading: Load relations on-demand with load()
• String Model References: Use string references to avoid circular dependencies

🔍 Fluent Query Builder

• Chainable methods for complex queries
• Raw SQL support when needed
• Subqueries and joins with multiple database support
• Aggregation functions (count, sum, avg, min, max)
• Conditional queries with when()
• Query scopes with automatic proxy support
• JSON queries for PostgreSQL and MySQL
• Date-specific queries (whereDate, whereMonth, whereYear)

🗄️ Database Management

• Migrations: Version control with rollback, fresh, and status commands
• Schema Builder: Create, modify, drop tables and columns
• Seeders: Populate database with test/initial data
• Multiple Connections: Support for multiple databases with connection-specific operations
• Database Timezone Support: Configurable timezone handling
• Laravel-Style Transactions: Automatic retry, seamless model integration, and connection support

🏭 Model Factories

• Generate realistic test data with Faker.js integration
• Model states for different scenarios
• Relationship factories for complex data structures
• TypeScript-aware factory generation

⏰ Lifecycle Management

• Soft Deletes: Mark records as deleted without removal
• Timestamps: Automatic created_at and updated_at with timezone support
• Model Events: Hook into model lifecycle (creating, created, updating, etc.)
• Observers: Organize event handling logic into dedicated classes
• Model Registry: Automatic model registration for polymorphic relationships

🛡️ Developer Experience & Type Safety

• JavaScript First: Works out of the box with JavaScript projects
• Automatic TypeScript Support: Detects TypeScript projects and generates typed code
• IntelliSense Support: Full IDE autocompletion for both JS and TS
• CLI Tools: Comprehensive code generation and database management
• UUID Support: Non-incrementing primary keys with automatic generation
• Custom Casts: Built-in and custom attribute casting
• Pagination: Standard, simple, and cursor-based pagination

🗃️ Database Support

• PostgreSQL: Full support with JSON operations and advanced features
• MySQL/MariaDB: Complete compatibility with JSON functions
• SQLite: Perfect for development and testing with null defaults

Installation

npm install ilana-orm

Database Drivers

Install only the database driver you need:

# PostgreSQL
npm install pg

# MySQL
npm install mysql2

# SQLite
npm install sqlite3

Quick Start

1. Initialize Project

Automatic Setup (Recommended)

# Initialize IlanaORM in your project
npx ilana setup

This command will:

• Create the ilana.config.js configuration file
• Generate the database/migrations/ directory
• Generate the database/seeds/ directory
• Generate the database/factories/ directory
• Generate the models/ directory
• Create a sample .env file with database variables

Manual Setup

If you prefer not to run the setup command, you can manually create the required files and directories:

# Create directories
mkdir -p database/migrations database/seeds database/factories models

# Create config file (see configuration section below)
touch ilana.config.js

2. Configure Database

For CommonJS projects, create ilana.config.js in your project root:

For ES Module projects (with "type": "module" in package.json), create ilana.config.mjs:

// ilana.config.mjs
export default {
  default: "sqlite",

  connections: {
    sqlite: {
      client: "sqlite3",
      connection: {
        filename: "./database.sqlite",
      },
    },

    mysql: {
      client: "mysql2",
      connection: {
        host: "localhost",
        port: 3306,
        user: "your_username",
        password: "your_password",
        database: "your_database",
      },
    },

    postgres: {
      client: "pg",
      connection: {
        host: "localhost",
        port: 5432,
        user: "your_username",
        password: "your_password",
        database: "your_database",
      },
    },
  },

  migrations: {
    directory: "./migrations",
    tableName: "migrations",
  },

  seeds: {
    directory: "./seeds",
  },
};

3. Create Your First Model

# Generate model with migration
npx ilana make:model User --migration

This creates:

• models/User.js - The model file (or .ts if TypeScript project detected)
• database/migrations/xxxx_create_users_table.js - Migration file (or .ts if TypeScript project)

4. Define the Model

JavaScript (CommonJS):

// models/User.js
const Model = require("ilana-orm/orm/Model");

JavaScript (ES Modules):

// models/User.js
import Model from "ilana-orm/orm/Model";

class User extends Model {
  static table = "users";
  static timestamps = true;
  static softDeletes = false;

  fillable = ["name", "email", "password"];
  hidden = ["password"];
  casts = {
    email_verified_at: "date",
    is_active: "boolean",
    metadata: "json",
  };

  // Relationships - use string references to avoid circular dependencies
  posts() {
    return this.hasMany("Post", "user_id");
  }

  roles() {
    return this.belongsToMany("Role", "user_roles", "user_id", "role_id");
  }

  // Register for polymorphic relationships
  static {
    this.register();
  }
}

export default User; // For ES modules
// module.exports = User; // For CommonJS

TypeScript (auto-generated when tsconfig.json detected):

// models/User.ts
import Model from "ilana-orm/orm/Model";

export default class User extends Model {
  protected static table = "users";
  protected static timestamps = true;
  protected static softDeletes = false;

  protected fillable: string[] = ["name", "email", "password"];
  protected hidden: string[] = ["password"];
  protected casts = {
    email_verified_at: "date" as const,
    is_active: "boolean" as const,
    metadata: "json" as const,
  };

  // Relationships - use string references to avoid circular dependencies
  posts() {
    return this.hasMany("Post", "user_id");
  }

  roles() {
    return this.belongsToMany("Role", "user_roles", "user_id", "role_id");
  }

  // Register for polymorphic relationships
  static {
    this.register();
  }
}

### 5. Run Migration

```bash
npx ilana migrate

6. Start Using the Model

CommonJS:

const User = require("./models/User");

ES Modules:

import User from "./models/User.js";

// Create user
const user = await User.create({
  name: "John Doe",
  email: "[email protected]",
  password: "secret",
});

// Query users
const users = await User.query()
  .where("is_active", true)
  .orderBy("created_at", "desc")
  .get();

// Update user
await user.update({ name: "John Smith" });

// Delete user
await user.delete();

Configuration

Complete Configuration Options

// ilana.config.js
module.exports = {
  // Default connection name
  default: "mysql_primary",

  // Database connections
  connections: {
    mysql_primary: {
      client: "mysql2",
      connection: {
        host: "primary.mysql.com",
        port: 3306,
        user: "primary_user",
        password: "primary_password",
        database: "primary_db",
        charset: "utf8mb4",
        timezone: "UTC",
      },
      pool: {
        min: 2,
        max: 10,
        acquireTimeoutMillis: 30000,
        createTimeoutMillis: 30000,
        destroyTimeoutMillis: 5000,
        idleTimeoutMillis: 30000,
        reapIntervalMillis: 1000,
        createRetryIntervalMillis: 100,
      },
      migrations: {
        tableName: "migrations",
        directory: "./migrations",
      },
      seeds: {
        directory: "./seeds",
      },
    },

    postgres_analytics: {
      client: "pg",
      connection: {
        host: "analytics.postgres.com",
        port: 5432,
        user: "analytics_user",
        password: "analytics_password",
        database: "analytics_db",
        ssl: { rejectUnauthorized: false },
        searchPath: ["public", "analytics"],
      },
      pool: {
        min: 1,
        max: 5,
      },
      migrations: {
        tableName: "analytics_migrations",
        directory: "./migrations/analytics",
        schemaName: "analytics",
      },
    },

    sqlite_test: {
      client: "sqlite3",
      connection: {
        filename: "./test.sqlite",
      },
      useNullAsDefault: true,
      migrations: {
        directory: "./migrations/test",
      },
    },
  },

  // Global migration settings
  migrations: {
    directory: "./migrations",
    tableName: "migrations",
    schemaName: "public", // PostgreSQL only
    extension: "ts", // or 'js'
    loadExtensions: [".ts", ".js"],
    sortDirsSeparately: false,
    stub: "./migration-stub.ts", // Custom migration template
  },

  // Global seed settings
  seeds: {
    directory: "./seeds",
    extension: "ts",
    loadExtensions: [".ts", ".js"],
    stub: "./seed-stub.ts", // Custom seed template
  },

  // Model settings
  models: {
    directory: "./models",
    extension: "ts",
  },

  // Factory settings
  factories: {
    directory: "./factories",
    extension: "ts",
  },

  // Debugging
  debug: process.env.NODE_ENV === "development",

  // Logging
  log: {
    warn(message) {
      console.warn(message);
    },
    error(message) {
      console.error(message);
    },
    deprecate(message) {
      console.warn("DEPRECATED:", message);
    },
    debug(message) {
      if (process.env.NODE_ENV === "development") {
        console.log("DEBUG:", message);
      }
    },
  },
};

Environment Variables

# .env
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password
DB_TIMEZONE=America/New_York

# SQLite Configuration
# DB_CONNECTION=sqlite
# DB_FILENAME=./database.sqlite

# PostgreSQL Configuration
# DB_CONNECTION=postgres
# DB_HOST=localhost
# DB_PORT=5432
# DB_DATABASE=your_database
# DB_USERNAME=postgres
# DB_PASSWORD=your_password

// ilana.config.js with environment variables
require("dotenv").config();
const Database = require("ilana-orm/database/connection");

const config = {
  default: process.env.DB_CONNECTION || "mysql",
  timezone: process.env.DB_TIMEZONE || "UTC",

  connections: {
    sqlite: {
      client: "sqlite3",
      connection: {
        filename: process.env.DB_FILENAME || "./database.sqlite",
      },
      useNullAsDefault: true,
    },

    mysql: {
      client: "mysql2",
      connection: {
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_DATABASE,
        timezone: process.env.DB_TIMEZONE || "UTC",
      },
    },

    postgres: {
      client: "pg",
      connection: {
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_DATABASE,
      },
    },
  },

  migrations: {
    directory: "./database/migrations",
    tableName: "migrations",
  },

  seeds: {
    directory: "./database/seeds",
  },
};

// Auto-initialize database connections
Database.configure(config);

module.exports = config;

CLI Commands

IlanaORM provides a comprehensive CLI for managing your database and models:

Model Generation

# Generate a model
npx ilana make:model User

# Generate model with migration
npx ilana make:model User --migration
npx ilana make:model User -m

# Generate model with factory
npx ilana make:model User --factory
npx ilana make:model User -f

# Generate model with seeder
npx ilana make:model User --seed
npx ilana make:model User -s

# Generate model with all extras
npx ilana make:model User --all
npx ilana make:model User -a

# Generate pivot model
npx ilana make:model UserRole --pivot

Migration Commands

# Create migration
npx ilana make:migration create_users_table
npx ilana make:migration add_column_to_users --table=users
npx ilana make:migration create_posts_table --create=posts

# Run migrations
npx ilana migrate
npx ilana migrate --connection=mysql
npx ilana migrate --connection=postgres_analytics

# Run specific migration file
npx ilana migrate --only=20231201_create_users_table.ts

# Run migrations up to specific batch
npx ilana migrate --to=20231201_120000

# Rollback migrations
npx ilana migrate:rollback
npx ilana migrate:rollback --step=2
npx ilana migrate:rollback --to=20231201_120000
npx ilana migrate:rollback --connection=postgres_analytics

# Reset all migrations
npx ilana migrate:reset
npx ilana migrate:reset --connection=mysql

# Fresh migration (drop all + migrate)
npx ilana migrate:fresh
npx ilana migrate:fresh --connection=postgres_analytics

# Fresh with seeding
npx ilana migrate:fresh --seed
npx ilana migrate:fresh --seed --connection=mysql

# Check migration status
npx ilana migrate:status
npx ilana migrate:status --connection=postgres_analytics

# List completed migrations
npx ilana migrate:list

# Unlock migrations (if stuck)
npx ilana migrate:unlock

Seeder Commands

# Create seeder
npx ilana make:seeder UserSeeder

# Run all seeders
npx ilana seed

# Run specific seeder
npx ilana seed --class=UserSeeder

# Run seeders for specific connection
npx ilana seed --connection=mysql

Factory Commands

# Create factory
npx ilana make:factory UserFactory

Database Commands

# Drop all tables
npx ilana db:wipe

# Run all seeders
npx ilana db:seed

Observer Commands

# Create model observer
npx ilana make:observer UserObserver

# Create observer for specific model
npx ilana make:observer UserObserver --model=User
npx ilana make:observer PostObserver --model=Post

Cast Commands

# Create custom cast
npx ilana make:cast MoneyCast

Models

Basic Model Definition

JavaScript:

const Model = require("ilana-orm/orm/Model");

class User extends Model {
  // Table configuration
  static table = "users";
  static primaryKey = "id";
  static keyType = "number"; // or 'string' for UUID
  static incrementing = true;

  // Connection (optional)
  static connection = "mysql";

  // Timestamps
  static timestamps = true;
  static createdAt = "created_at";
  static updatedAt = "updated_at";

  // Soft deletes
  static softDeletes = true;
  static deletedAt = "deleted_at";

  // Mass assignment protection
  fillable = ["name", "email", "password"];
  guarded = ["id", "created_at", "updated_at"];

  // Hidden attributes (won't appear in JSON)
  hidden = ["password", "remember_token"];

  // Appended attributes (accessors to include in JSON)
  appends = ["full_name", "avatar_url"];

  // Attribute casting
  casts = {
    email_verified_at: "date",
    preferences: "json",
    is_admin: "boolean",
  };

  // Default values
  attributes = {
    is_active: true,
    role: "user",
  };
}

module.exports = User;

TypeScript:

import Model from "ilana-orm/orm/Model";

export default class User extends Model {
  // Table configuration
  protected static table = "users";
  protected static primaryKey = "id";
  protected static keyType = "number" as const; // or 'string' for UUID
  protected static incrementing = true;

  // Connection (optional)
  protected static connection = "mysql";

  // Timestamps
  protected static timestamps = true;
  protected static createdAt = "created_at";
  protected static updatedAt = "updated_at";

  // Soft deletes
  protected static softDeletes = true;
  protected static deletedAt = "deleted_at";

  // Mass assignment protection
  protected fillable: string[] = ["name", "email", "password"];
  protected guarded: string[] = ["id", "created_at", "updated_at"];

  // Hidden attributes (won't appear in JSON)
  protected hidden: string[] = ["password", "remember_token"];

  // Appended attributes (accessors to include in JSON)
  protected appends: string[] = ["full_name", "avatar_url"];

  // Attribute casting
  protected casts = {
    email_verified_at: "date" as const,
    preferences: "json" as const,
    is_admin: "boolean" as const,
  };

  // Default values
  protected attributes = {
    is_active: true,
    role: "user",
  };
}

### UUID Primary Keys

**JavaScript:**
```javascript
class User extends Model {
  static table = 'users';
  static keyType = 'string';
  static incrementing = false;
}

module.exports = User;

// Usage
const user = await User.create({
  name: 'John Doe',
  email: '[email protected]',
});
// user.id will be a generated UUID

TypeScript:

export default class User extends Model {
  protected static table = "users";
  protected static keyType = "string" as const;
  protected static incrementing = false;

  // Attributes
  id!: string; // UUID primary key
  name!: string;
  email!: string;
}

// Usage
const user = await User.create({
  name: "John Doe",
  email: "[email protected]",
});
// user.id will be a generated UUID

### Attribute Casting

**JavaScript:**
```javascript
class User extends Model {
  casts = {
    // Date casting
    email_verified_at: 'date',
    birth_date: 'date',

    // Boolean casting
    is_admin: 'boolean',
    is_active: 'boolean',

    // Number casting
    age: 'number',
    salary: 'float',

    // JSON casting
    preferences: 'json',
    metadata: 'object',
    tags: 'array',
  };
}

TypeScript:

class User extends Model {
  protected casts = {
    // Date casting
    email_verified_at: "date" as const,
    birth_date: "date" as const,

    // Boolean casting
    is_admin: "boolean" as const,
    is_active: "boolean" as const,

    // Number casting
    age: "number" as const,
    salary: "float" as const,

    // JSON casting
    preferences: "json" as const,
    metadata: "object" as const,
    tags: "array" as const,
  };
}

### Custom Casts

**JavaScript:**
```javascript
// Built-in custom casts
const {
  MoneyCast,
  EncryptedCast,
  JsonCast,
  ArrayCast,
  DateCast,
} = require('ilana-orm/orm/CustomCasts');

class Product extends Model {
  casts = {
    price: new MoneyCast(),
    secret_data: new EncryptedCast('your-encryption-key'),
    metadata: new JsonCast(),
    tags: new ArrayCast(),
    published_at: new DateCast(),
  };
}

// Define custom cast
class MoneyCast {
  get(value) {
    return value ? parseFloat(value) / 100 : null;
  }

  set(value) {
    return value ? Math.round(value * 100) : null;
  }
}

// Generate custom cast with CLI
// ilana make:cast MoneyCast
// Creates: casts/MoneyCast.js

TypeScript:

// Built-in custom casts
import {
  MoneyCast,
  EncryptedCast,
  JsonCast,
  ArrayCast,
  DateCast,
} from "ilana-orm/orm/CustomCasts";

class Product extends Model {
  protected casts = {
    price: new MoneyCast(),
    secret_data: new EncryptedCast("your-encryption-key"),
    metadata: new JsonCast(),
    tags: new ArrayCast(),
    published_at: new DateCast(),
  };
}

// Define custom cast
class MoneyCast {
  get(value: any) {
    return value ? parseFloat(value) / 100 : null;
  }

  set(value: any) {
    return value ? Math.round(value * 100) : null;
  }
}

// Generate custom cast with CLI
// ilana make:cast MoneyCast
// Creates: casts/MoneyCast.ts

### Mutators and Accessors

**JavaScript:**
```javascript
class User extends Model {
  // Appended attributes (automatically included in JSON)
  appends = ['full_name', 'avatar_url'];

  // Mutator - transform data when setting
  setPasswordAttribute(value) {
    return value ? bcrypt.hashSync(value, 10) : value;
  }

  setEmailAttribute(value) {
    return value ? value.toLowerCase().trim() : value;
  }

  // Accessor - transform data when getting
  getFullNameAttribute() {
    return `${this.first_name} ${this.last_name}`;
  }

  getAvatarUrlAttribute() {
    return this.avatar
      ? `/uploads/avatars/${this.avatar}`
      : '/images/default-avatar.png';
  }
}

// Usage
const user = await User.find(1);
console.log(user.toJSON());
// Output includes: { id: 1, first_name: 'John', last_name: 'Doe', full_name: 'John Doe', avatar_url: '/images/default-avatar.png' }

TypeScript:

class User extends Model {
  // Appended attributes (automatically included in JSON)
  protected appends: string[] = ["full_name", "avatar_url"];

  // Mutator - transform data when setting
  setPasswordAttribute(value: string) {
    return value ? bcrypt.hashSync(value, 10) : value;
  }

  setEmailAttribute(value: string) {
    return value ? value.toLowerCase().trim() : value;
  }

  // Accessor - transform data when getting
  getFullNameAttribute(): string {
    return `${this.first_name} ${this.last_name}`;
  }

  getAvatarUrlAttribute(): string {
    return this.avatar
      ? `/uploads/avatars/${this.avatar}`
      : "/images/default-avatar.png";
  }
}

// Usage
const user = await User.find(1);
console.log(user.toJSON());
// Output includes: { id: 1, first_name: 'John', last_name: 'Doe', full_name: 'John Doe', avatar_url: '/images/default-avatar.png' }

### Model Events

**JavaScript:**
```javascript
class User extends Model {
  static {
    // Register events when class is loaded
    this.creating(async (user) => {
      user.email = user.email.toLowerCase();
    });

    this.created(async (user) => {
      await sendWelcomeEmail(user.email);
    });

    this.updating(async (user) => {
      if (user.isDirty('email')) {
        user.email_verified_at = null;
      }
    });

    this.updated(async (user) => {
      await syncUserData(user);
    });

    this.saving(async (user) => {
      user.updated_at = new Date();
    });

    this.saved(async (user) => {
      await clearUserCache(user.id);
    });

    this.deleting(async (user) => {
      await user.posts().delete();
    });

    this.deleted(async (user) => {
      await cleanupUserFiles(user.id);
    });
  }
}

TypeScript:

class User extends Model {
  static {
    // Register events when class is loaded
    this.creating(async (user) => {
      user.email = user.email.toLowerCase();
    });

    this.created(async (user) => {
      await sendWelcomeEmail(user.email);
    });

    this.updating(async (user) => {
      if (user.isDirty("email")) {
        user.email_verified_at = null;
      }
    });

    this.updated(async (user) => {
      await syncUserData(user);
    });

    this.saving(async (user) => {
      user.updated_at = new Date();
    });

    this.saved(async (user) => {
      await clearUserCache(user.id);
    });

    this.deleting(async (user) => {
      await user.posts().delete();
    });

    this.deleted(async (user) => {
      await cleanupUserFiles(user.id);
    });
  }
}

### Model Observers

Observers provide a clean way to organize model event handling logic into separate classes, promoting better code organization and reusability.

#### Creating Observers

```bash
# Generate observer
ilana make:observer UserObserver

# Generate observer for specific model
ilana make:observer UserObserver --model=User

Observer Structure

JavaScript:

// observers/UserObserver.js
const User = require("../models/User");

class UserObserver {
  async creating(user) {
    // Logic before creating user
    user.email = user.email.toLowerCase();
    user.uuid = generateUuid();
  }

  async created(user) {
    // Logic after creating user
    await sendWelcomeEmail(user.email);
    await createUserProfile(user.id);
  }

  async updating(user) {
    // Logic before updating user
    if (user.isDirty("email")) {
      user.email_verified_at = null;
    }
  }

  async updated(user) {
    // Logic after updating user
    await syncUserData(user);
    await clearUserCache(user.id);
  }

  async saving(user) {
    // Logic before saving (create or update)
    user.updated_at = new Date();
  }

  async saved(user) {
    // Logic after saving (create or update)
    await logUserActivity(user);
  }

  async deleting(user) {
    // Logic before deleting user
    await user.posts().delete();
    await user.comments().delete();
  }

  async deleted(user) {
    // Logic after deleting user
    await cleanupUserFiles(user.id);
    await removeFromExternalServices(user);
  }

  async restoring(user) {
    // Logic before restoring soft-deleted user
    await validateUserRestore(user);
  }

  async restored(user) {
    // Logic after restoring soft-deleted user
    await sendAccountRestoredEmail(user);
  }
}

module.exports = UserObserver;

TypeScript:

// observers/UserObserver.ts
import User from "../models/User";

export default class UserObserver {
  async creating(user: User): Promise<void> {
    // Logic before creating user
    user.email = user.email.toLowerCase();
    user.uuid = generateUuid();
  }

  async created(user: User): Promise<void> {
    // Logic after creating user
    await sendWelcomeEmail(user.email);
    await createUserProfile(user.id);
  }

  async updating(user: User): Promise<void> {
    // Logic before updating user
    if (user.isDirty("email")) {
      user.email_verified_at = null;
    }
  }

  async updated(user: User): Promise<void> {
    // Logic after updating user
    await syncUserData(user);
    await clearUserCache(user.id);
  }

  async saving(user: User): Promise<void> {
    // Logic before saving (create or update)
    user.updated_at = new Date();
  }

  async saved(user: User): Promise<void> {
    // Logic after saving (create or update)
    await logUserActivity(user);
  }

  async deleting(user: User): Promise<void> {
    // Logic before deleting user
    await user.posts().delete();
    await user.comments().delete();
  }

  async deleted(user: User): Promise<void> {
    // Logic after deleting user
    await cleanupUserFiles(user.id);
    await removeFromExternalServices(user);
  }

  async restoring(user: User): Promise<void> {
    // Logic before restoring soft-deleted user
    await validateUserRestore(user);
  }

  async restored(user: User): Promise<void> {
    // Logic after restoring soft-deleted user
    await sendAccountRestoredEmail(user);
  }
}

#### Registering Observers

**JavaScript:**
```javascript
// Register observer class
User.observe(UserObserver);

// Register multiple observers
User.observe(UserObserver);
User.observe(AuditObserver);
User.observe(EmailNotificationObserver);

// Register observer with events object
User.observe({
  creating: async (user) => {
    user.email = user.email.toLowerCase();
  },
  created: async (user) => {
    await sendWelcomeEmail(user.email);
  },
});

TypeScript:

// Register observer class
User.observe(UserObserver);

// Register multiple observers
User.observe(UserObserver);
User.observe(AuditObserver);
User.observe(EmailNotificationObserver);

// Register observer with events object
User.observe({
  creating: async (user) => {
    user.email = user.email.toLowerCase();
  },
  created: async (user) => {
    await sendWelcomeEmail(user.email);
  },
});

#### Observer Registration Patterns

```typescript
// 1. Application Bootstrap
// app.ts or index.ts
import User from "./models/User";
import UserObserver from "./observers/UserObserver";

// Register observers during app initialization
User.observe(UserObserver);

// 2. Service Provider Pattern
// providers/ObserverServiceProvider.ts
export class ObserverServiceProvider {
  static register(): void {
    User.observe(UserObserver);
    Post.observe(PostObserver);
    Order.observe(OrderObserver);
  }
}

// app.ts
ObserverServiceProvider.register();

// 3. Dedicated Observer Registration
// observers/index.ts
import User from "../models/User";
import Post from "../models/Post";
import UserObserver from "./UserObserver";
import PostObserver from "./PostObserver";
import AuditObserver from "./AuditObserver";

// Register all observers
User.observe(UserObserver);
User.observe(AuditObserver);
Post.observe(PostObserver);
Post.observe(AuditObserver);

// app.ts
import "./observers"; // Auto-registers all observers

Reusable Observers

// observers/AuditObserver.ts
export default class AuditObserver {
  async created(model: any): Promise<void> {
    await AuditLog.create({
      model_type: model.constructor.name,
      model_id: model.id,
      action: "created",
      data: model.toJSON(),
    });
  }

  async updated(model: any): Promise<void> {
    await AuditLog.create({
      model_type: model.constructor.name,
      model_id: model.id,
      action: "updated",
      changes: model.getDirty(),
    });
  }

  async deleted(model: any): Promise<void> {
    await AuditLog.create({
      model_type: model.constructor.name,
      model_id: model.id,
      action: "deleted",
    });
  }
}

// Use across multiple models
User.observe(AuditObserver);
Post.observe(AuditObserver);
Product.observe(AuditObserver);

Conditional Observer Registration

// Environment-specific observers
if (process.env.NODE_ENV === "production") {
  User.observe(ProductionUserObserver);
} else {
  User.observe(DevelopmentUserObserver);
}

// Feature-based observers
if (config.features.emailNotifications) {
  User.observe(EmailNotificationObserver);
}

if (config.features.analytics) {
  User.observe(AnalyticsObserver);
}

// A/B testing observers
if (user.isInExperimentGroup("new_onboarding")) {
  User.observe(NewOnboardingObserver);
} else {
  User.observe(StandardOnboardingObserver);
}

Observer vs Model Events

Use Model Events for:

• Core business logic that's integral to the model
• Simple, single-purpose operations
• Logic that should always run

Use Observers for:

• Side effects and cross-cutting concerns
• Complex logic that can be organized into classes
• Logic that might be conditionally applied
• Reusable functionality across multiple models
• Better testing and mocking capabilities

Query Scopes

JavaScript:

class Post extends Model {
  // Simple scope
  static scopePublished(query) {
    return query.where("is_published", true);
  }

  // Scope with parameters
  static scopeOfType(query, type) {
    return query.where("type", type);
  }

  // Complex scope
  static scopePopular(query, threshold = 100) {
    return query.where("views", ">", threshold).orderBy("views", "desc");
  }
}

// Usage
const posts = await Post.query()
  .published()
  .ofType("article")
  .popular(500)
  .get();

TypeScript:

class Post extends Model {
  // Simple scope
  static scopePublished(query: any) {
    return query.where("is_published", true);
  }

  // Scope with parameters
  static scopeOfType(query: any, type: string) {
    return query.where("type", type);
  }

  // Complex scope
  static scopePopular(query: any, threshold = 100) {
    return query.where("views", ">", threshold).orderBy("views", "desc");
  }
}

// Usage
const posts = await Post.query()
  .published()
  .ofType("article")
  .popular(500)
  .get();

## Query Builder

### Basic Queries

**JavaScript:**
```javascript
// Select all
const users = await User.all();

// Find by primary key
const user = await User.find(1);
const user = await User.findOrFail(1); // Throws if not found

// First record
const user = await User.first();
const user = await User.firstOrFail(); // Throws if not found

// Create or find
const user = await User.firstOrCreate(
  { email: '[email protected]' },
  { name: 'John Doe' }
);

// Update or create
const user = await User.updateOrCreate(
  { email: '[email protected]' },
  { name: 'John Smith', is_active: true }
);

TypeScript:

// Select all
const users = await User.all();

// Find by primary key
const user = await User.find(1);
const user = await User.findOrFail(1); // Throws if not found

// First record
const user = await User.first();
const user = await User.firstOrFail(); // Throws if not found

// Create or find
const user = await User.firstOrCreate(
  { email: "[email protected]" },
  { name: "John Doe" }
);

// Update or create
const user = await User.updateOrCreate(
  { email: "[email protected]" },
  { name: "John Smith", is_active: true }
);

### Where Clauses

**JavaScript:**
```javascript
// Basic where
const users = await User.query()
  .where('is_active', true)
  .where('age', '>', 18)
  .get();

// Where with operator
const users = await User.query()
  .where('age', '>=', 21)
  .where('name', 'like', '%john%')
  .get();

// Or where
const users = await User.query()
  .where('role', 'admin')
  .orWhere('role', 'moderator')
  .get();

// Where in
const users = await User.query()
  .whereIn('role', ['admin', 'editor', 'author'])
  .get();

// Where null/not null
const users = await User.query()
  .whereNull('deleted_at')
  .whereNotNull('email_verified_at')
  .get();

// Where between
const users = await User.query().whereBetween('age', [18, 65]).get();

// JSON queries (database-specific)
const users = await User.query()
  .whereJsonContains('preferences', { theme: 'dark' })
  .whereJsonLength('tags', '>', 3)
  .get();

// Date queries
const users = await User.query()
  .whereDate('created_at', '2023-12-01')
  .whereMonth('created_at', 12)
  .whereYear('created_at', 2023)
  .get();

// Exists queries
const users = await User.query()
  .whereExists((query) => {
    query.select('*').from('posts').whereRaw('posts.user_id = users.id');
  })
  .get();

// Conditional queries
const users = await User.query()
  .when(filters.role, (query, role) => {
    query.where('role', role);
  })
  .when(filters.search, (query, search) => {
    query.where('name', 'like', `%${search}%`);
  })
  .get();

TypeScript:

// Basic where
const users = await User.query()
  .where("is_active", true)
  .where("age", ">", 18)
  .get();

// Where with operator
const users = await User.query()
  .where("age", ">=", 21)
  .where("name", "like", "%john%")
  .get();

// Or where
const users = await User.query()
  .where("role", "admin")
  .orWhere("role", "moderator")
  .get();

// Where in
const users = await User.query()
  .whereIn("role", ["admin", "editor", "author"])
  .get();

// Where null/not null
const users = await User.query()
  .whereNull("deleted_at")
  .whereNotNull("email_verified_at")
  .get();

// Where between
const users = await User.query().whereBetween("age", [18, 65]).get();

// JSON queries (database-specific)
const users = await User.query()
  .whereJsonContains("preferences", { theme: "dark" })
  .whereJsonLength("tags", ">", 3)
  .get();

// Date queries
const users = await User.query()
  .whereDate("created_at", "2023-12-01")
  .whereMonth("created_at", 12)
  .whereYear("created_at", 2023)
  .get();

// Exists queries
const users = await User.query()
  .whereExists((query) => {
    query.select("*").from("posts").whereRaw("posts.user_id = users.id");
  })
  .get();

// Conditional queries
const users = await User.query()
  .when(filters.role, (query, role) => {
    query.where("role", role);
  })
  .when(filters.search, (query, search) => {
    query.where("name", "like", `%${search}%`);
  })
  .get();

### Joins and Aggregates

**JavaScript:**
```javascript
// Inner join
const posts = await Post.query()
  .join('users', 'posts.user_id', 'users.id')
  .select('posts.*', 'users.name as author_name')
  .get();

// Aggregates
const count = await User.query().count();
const avgAge = await User.query().avg('age');
const totalSalary = await User.query().sum('salary');

// Group by with having
const roleStats = await User.query()
  .select('role')
  .selectRaw('COUNT(*) as count')
  .groupBy('role')
  .having('count', '>', 10)
  .get();

TypeScript:

// Inner join
const posts = await Post.query()
  .join("users", "posts.user_id", "users.id")
  .select("posts.*", "users.name as author_name")
  .get();

// Aggregates
const count = await User.query().count();
const avgAge = await User.query().avg("age");
const totalSalary = await User.query().sum("salary");

// Group by with having
const roleStats = await User.query()
  .select("role")
  .selectRaw("COUNT(*) as count")
  .groupBy("role")
  .having("count", ">", 10)
  .get();

### Ordering and Limiting

**JavaScript:**
```javascript
const users = await User.query()
  .orderBy('name')
  .orderBy('created_at', 'desc')
  .limit(10)
  .offset(20)
  .get();

TypeScript:

const users = await User.query()
  .orderBy("name")
  .orderBy("created_at", "desc")
  .limit(10)
  .offset(20)
  .get();

### Raw Queries

**JavaScript:**
```javascript
// Raw where
const users = await User.query()
  .whereRaw('age > ? AND salary < ?', [25, 50000])
  .get();

// Raw select
const users = await User.query().selectRaw('*, YEAR(created_at) as year').get();

TypeScript:

// Raw where
const users = await User.query()
  .whereRaw("age > ? AND salary < ?", [25, 50000])
  .get();

// Raw select
const users = await User.query().selectRaw("*, YEAR(created_at) as year").get();

## Relationships

**Important:** To avoid circular dependency issues, always use string references for related models in relationships instead of importing the model classes directly.

### One-to-One

**JavaScript:**
```javascript
class User extends Model {
  // User has one profile
  profile() {
    return this.hasOne(Profile, 'user_id');
  }
}

class Profile extends Model {
  // Profile belongs to user
  user() {
    return this.belongsTo(User, 'user_id');
  }
}

// Usage
const user = await User.with('profile').first();
const profile = user.profile;

TypeScript:

class User extends Model {
  // User has one profile
  profile() {
    return this.hasOne(Profile, "user_id");
  }
}

class Profile extends Model {
  // Profile belongs to user
  user() {
    return this.belongsTo(User, "user_id");
  }
}

// Usage
const user = await User.with("profile").first();
const profile = user.profile;

### One-to-Many

**JavaScript:**
```javascript
class User extends Model {
  // User has many posts
  posts() {
    return this.hasMany(Post, 'user_id');
  }
}

class Post extends Model {
  // Post belongs to user
  author() {
    return this.belongsTo(User, 'user_id');
  }
}

// Usage
const user = await User.with('posts').first();
const posts = user.posts;

TypeScript:

class User extends Model {
  // User has many posts
  posts() {
    return this.hasMany(Post, "user_id");
  }
}

class Post extends Model {
  // Post belongs to user
  author() {
    return this.belongsTo(User, "user_id");
  }
}

// Usage
const user = await User.with("posts").first();
const posts = user.posts;

### Many-to-Many

**JavaScript:**
```javascript
class User extends Model {
  // User belongs to many roles
  roles() {
    return this.belongsToMany(Role, 'user_roles', 'user_id', 'role_id')
      .withPivot('assigned_at', 'assigned_by')
      .withTimestamps();
  }
}

class Role extends Model {
  // Role belongs to many users
  users() {
    return this.belongsToMany(User, 'user_roles', 'role_id', 'user_id');
  }
}

// Usage
const user = await User.with('roles').first();
const roles = user.roles;

// Access pivot data
roles.forEach((role) => {
  console.log(role.pivot.assigned_at);
});

TypeScript:

class User extends Model {
  // User belongs to many roles
  roles() {
    return this.belongsToMany(Role, "user_roles", "user_id", "role_id")
      .withPivot("assigned_at", "assigned_by")
      .withTimestamps();
  }
}

class Role extends Model {
  // Role belongs to many users
  users() {
    return this.belongsToMany(User, "user_roles", "role_id", "user_id");
  }
}

// Usage
const user = await User.with("roles").first();
const roles = user.roles;

// Access pivot data
roles.forEach((role) => {
  console.log(role.pivot.assigned_at);
});

### Polymorphic Relationships

**JavaScript:**
```javascript
class Comment extends Model {
  // Comment can belong to Post or Video
  commentable() {
    return this.morphTo('commentable');
  }
}

class Post extends Model {
  // Post has many comments (polymorphic)
  comments() {
    return this.morphMany(Comment, 'commentable');
  }
}

class Video extends Model {
  // Video has many comments (polymorphic)
  comments() {
    return this.morphMany(Comment, 'commentable');
  }
}

TypeScript:

class Comment extends Model {
  // Comment can belong to Post or Video
  commentable() {
    return this.morphTo("commentable");
  }
}

class Post extends Model {
  // Post has many comments (polymorphic)
  comments() {
    return this.morphMany(Comment, "commentable");
  }
}

class Video extends Model {
  // Video has many comments (polymorphic)
  comments() {
    return this.morphMany(Comment, "commentable");
  }
}

### Has-Many-Through

**JavaScript:**
```javascript
class Country extends Model {
  // Country has many posts through users
  posts() {
    return this.hasManyThrough(Post, User, 'country_id', 'user_id');
  }
}

TypeScript:

class Country extends Model {
  // Country has many posts through users
  posts() {
    return this.hasManyThrough(Post, User, "country_id", "user_id");
  }
}

### Eager Loading

**JavaScript:**
```javascript
// Basic eager loading
const users = await User.with('posts').get();

// Multiple relationships
const users = await User.with('posts', 'roles', 'profile').get();

// Nested relationships
const users = await User.with('posts.comments').get();

// Constrained eager loading
const users = await User.query()
  .withConstraints('posts', (query) => {
    query.where('is_published', true).orderBy('created_at', 'desc').limit(5);
  })
  .get();

// Lazy loading
const user = await User.first();
await user.load('posts');

// Load missing relations only
await user.loadMissing('posts', 'roles');

// Count relationships
const users = await User.withCount('posts').get();
// Each user will have posts_count attribute

// Constrained eager loading
const users = await User.query()
  .withConstraints('posts', (query) => {
    query.where('is_published', true).limit(5);
  })
  .get();

TypeScript:

// Basic eager loading
const users = await User.with("posts").get();

// Multiple relationships
const users = await User.with("posts", "roles", "profile").get();

// Nested relationships
const users = await User.with("posts.comments").get();

// Constrained eager loading
const users = await User.query()
  .withConstraints("posts", (query) => {
    query.where("is_published", true).orderBy("created_at", "desc").limit(5);
  })
  .get();

// Lazy loading
const user = await User.first();
await user.load("posts");

// Count relationships
const users = await User.withCount("posts").get();
// Each user will have posts_count attribute

## Migrations

### Creating Migrations

```bash
# Create a new migration
ilana make:migration create_users_table

# Create migration for existing table
ilana make:migration add_avatar_to_users_table --table=users

Migration Structure

import { SchemaBuilder } from "ilana-orm";

export default class CreateUsersTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.createTable("users", (table) => {
      // Primary key
      table.increments("id");
      // For UUID: table.uuid('id').primary();

      // Basic columns
      table.string("name").notNullable();
      table.string("email").unique().notNullable();
      table.string("password").notNullable();

      // Nullable columns
      table.string("avatar").nullable();
      table.timestamp("email_verified_at").nullable();

      // Timestamps
      table.timestamps(true, true);

      // Soft deletes
      table.timestamp("deleted_at").nullable();

      // Indexes
      table.index("email");
      table.index(["name", "email"]);
    });
  }

  async down(schema: SchemaBuilder): Promise<void> {
    await schema.dropTable("users");
  }
}

Column Types

JavaScript:

class CreateProductsTable {
  async up(schema) {
    await schema.createTable("products", (table) => {
      // Primary key types
      table.increments("id"); // Auto-incrementing integer
      table.bigIncrements("big_id"); // Auto-incrementing big integer
      // table.uuid('id').primary(); // UUID primary key

      // Numeric types
      table.integer("quantity");
      table.bigInteger("views");
      table.smallInteger("priority");
      table.tinyInteger("status_code");
      table.decimal("price", 8, 2); // precision, scale
      table.float("rating", 3, 1); // precision, scale
      table.double("coordinates");
      table.real("measurement");

      // String types
      table.string("name", 255); // VARCHAR with length
      table.text("description"); // TEXT
      table.longText("content"); // LONGTEXT (MySQL)
      table.mediumText("summary"); // MEDIUMTEXT (MySQL)
      table.char("code", 10); // CHAR with fixed length
      table.varchar("slug", 100); // VARCHAR (alias for string)

      // Date/Time types
      table.date("release_date"); // DATE
      table.time("available_time"); // TIME
      table.datetime("published_at"); // DATETIME
      table.timestamp("created_at"); // TIMESTAMP
      table.timestamps(); // created_at & updated_at
      table.timestamps(true, true); // with timezone

      // Boolean
      table.boolean("is_active").defaultTo(true);

      // JSON types
      table.json("metadata"); // JSON (all databases)
      table.jsonb("settings"); // JSONB (PostgreSQL only)

      // Binary types
      table.binary("file_data"); // BLOB/BYTEA
      table.varbinary("hash", 32); // VARBINARY

      // UUID
      table.uuid("uuid");

      // Enum (MySQL/PostgreSQL)
      table.enum("status", ["draft", "published", "archived"]);

      // Set (MySQL only)
      table.set("permissions", ["read", "write", "delete"]);

      // Geometry types (PostgreSQL/MySQL)
      table.geometry("location");
      table.point("coordinates");
      table.lineString("path");
      table.polygon("area");

      // Array types (PostgreSQL only)
      table.specificType("tags", "text[]");
      table.specificType("scores", "integer[]");

      // Network types (PostgreSQL only)
      table.inet("ip_address");
      table.macaddr("mac_address");

      // Range types (PostgreSQL only)
      table.specificType("price_range", "numrange");
      table.specificType("date_range", "daterange");

      // Full-text search (PostgreSQL)
      table.specificType("search_vector", "tsvector");

      // Custom types
      table.specificType("custom_type", "your_custom_type");

      // Foreign keys
      table.integer("user_id").unsigned();
      table.foreign("user_id").references("id").inTable("users");

      // Shorthand foreign key
      table.foreignId("category_id").constrained();
      table.foreignUuid("parent_id").constrained("products");
    });
  }
}

TypeScript:

export default class CreateProductsTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.createTable("products", (table) => {
      // Primary key types
      table.increments("id"); // Auto-incrementing integer
      table.bigIncrements("big_id"); // Auto-incrementing big integer
      // table.uuid('id').primary(); // UUID primary key

      // Numeric types
      table.integer("quantity");
      table.bigInteger("views");
      table.smallInteger("priority");
      table.tinyInteger("status_code");
      table.decimal("price", 8, 2); // precision, scale
      table.float("rating", 3, 1); // precision, scale
      table.double("coordinates");
      table.real("measurement");

      // String types
      table.string("name", 255); // VARCHAR with length
      table.text("description"); // TEXT
      table.longText("content"); // LONGTEXT (MySQL)
      table.mediumText("summary"); // MEDIUMTEXT (MySQL)
      table.char("code", 10); // CHAR with fixed length
      table.varchar("slug", 100); // VARCHAR (alias for string)

      // Date/Time types
      table.date("release_date"); // DATE
      table.time("available_time"); // TIME
      table.datetime("published_at"); // DATETIME
      table.timestamp("created_at"); // TIMESTAMP
      table.timestamps(); // created_at & updated_at
      table.timestamps(true, true); // with timezone

      // Boolean
      table.boolean("is_active").defaultTo(true);

      // JSON types
      table.json("metadata"); // JSON (all databases)
      table.jsonb("settings"); // JSONB (PostgreSQL only)

      // Binary types
      table.binary("file_data"); // BLOB/BYTEA
      table.varbinary("hash", 32); // VARBINARY

      // UUID
      table.uuid("uuid");

      // Enum (MySQL/PostgreSQL)
      table.enum("status", ["draft", "published", "archived"]);

      // Set (MySQL only)
      table.set("permissions", ["read", "write", "delete"]);

      // Geometry types (PostgreSQL/MySQL)
      table.geometry("location");
      table.point("coordinates");
      table.lineString("path");
      table.polygon("area");

      // Array types (PostgreSQL only)
      table.specificType("tags", "text[]");
      table.specificType("scores", "integer[]");

      // Network types (PostgreSQL only)
      table.inet("ip_address");
      table.macaddr("mac_address");

      // Range types (PostgreSQL only)
      table.specificType("price_range", "numrange");
      table.specificType("date_range", "daterange");

      // Full-text search (PostgreSQL)
      table.specificType("search_vector", "tsvector");

      // Custom types
      table.specificType("custom_type", "your_custom_type");

      // Foreign keys
      table.integer("user_id").unsigned();
      table.foreign("user_id").references("id").inTable("users");

      // Shorthand foreign key
      table.foreignId("category_id").constrained();
      table.foreignUuid("parent_id").constrained("products");
    });
  }
}

### Column Modifiers and Constraints

```typescript
export default class CreateUsersTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.createTable("users", (table) => {
      table.increments("id");

      // Nullable/Not nullable
      table.string("name").notNullable();
      table.string("nickname").nullable();

      // Default values
      table.boolean("is_active").defaultTo(true);
      table.timestamp("created_at").defaultTo(schema.fn.now());
      table.string("role").defaultTo("user");
      table.integer("login_count").defaultTo(0);

      // Unique constraints
      table.string("email").unique();
      table.string("username").unique("unique_username");

      // Indexes
      table.string("slug").index();
      table.string("search_vector").index("search_idx");

      // Composite indexes
      table.index(["name", "email"], "name_email_idx");
      table.unique(["email", "tenant_id"], "unique_email_per_tenant");

      // Comments
      table.string("api_key").comment("User API key for external services");

      // Unsigned (for integers)
      table.integer("age").unsigned();

      // Auto increment
      table.integer("order_number").autoIncrement();

      // Column positioning (MySQL only)
      table.string("middle_name").after("first_name");
      table.string("prefix").first();

      // Check constraints (PostgreSQL/SQLite)
      table.integer("age").checkPositive();
      table.string("email").checkRegex("^[^@]+@[^@]+.[^@]+$");

      // Generated columns (MySQL 5.7+/PostgreSQL)
      table
        .string("full_name")
        .generatedAs('CONCAT(first_name, " ", last_name)');

      // Collation (MySQL/PostgreSQL)
      table.string("name").collate("utf8_unicode_ci");
    });
  }
}

Modifying Tables

// Add columns
export default class AddAvatarToUsersTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.table("users", (table) => {
      table.string("avatar").nullable().after("email");
      table.text("bio").nullable();
      table.timestamp("last_login_at").nullable();

      // Add index
      table.index("last_login_at");

      // Add foreign key
      table.integer("department_id").unsigned().nullable();
      table.foreign("department_id").references("id").inTable("departments");
    });
  }

  async down(schema: SchemaBuilder): Promise<void> {
    await schema.table("users", (table) => {
      // Drop foreign key first
      table.dropForeign(["department_id"]);

      // Drop columns
      table.dropColumn(["avatar", "bio", "last_login_at", "department_id"]);

      // Drop index
      table.dropIndex(["last_login_at"]);
    });
  }
}

// Modify existing columns
export default class ModifyUsersTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.table("users", (table) => {
      // Change column type
      table.text("bio").alter();

      // Rename column
      table.renameColumn("name", "full_name");

      // Change column to nullable
      table.string("phone").nullable().alter();

      // Change default value
      table.boolean("is_active").defaultTo(false).alter();

      // Add/drop constraints
      table.string("email").unique().alter();
      table.dropUnique(["username"]);

      // Modify index
      table.dropIndex(["old_column"]);
      table.index(["new_column"]);
    });
  }

  async down(schema: SchemaBuilder): Promise<void> {
    await schema.table("users", (table) => {
      table.string("bio").alter();
      table.renameColumn("full_name", "name");
      table.string("phone").notNullable().alter();
      table.boolean("is_active").defaultTo(true).alter();
    });
  }
}

Indexes and Foreign Keys

export default class CreatePostsTable {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.createTable("posts", (table) => {
      table.increments("id");
      table.string("title");
      table.text("content");
      table.integer("user_id").unsigned();
      table.integer("category_id").unsigned();
      table.timestamps();

      // Simple indexes
      table.index("title");
      table.index("created_at");

      // Composite indexes
      table.index(["user_id", "created_at"], "user_posts_idx");
      table.index(["category_id", "is_published"], "category_published_idx");

      // Unique indexes
      table.unique(["user_id", "slug"], "unique_user_slug");

      // Partial indexes (PostgreSQL)
      table.index(["title"], "published_posts_title_idx", {
        where: "is_published = true",
      });

      // Full-text indexes (MySQL)
      table.index(["title", "content"], "fulltext_idx", "FULLTEXT");

      // Spatial indexes (MySQL/PostgreSQL)
      table.index(["location"], "location_idx", "SPATIAL");

      // Foreign keys with actions
      table
        .foreign("user_id")
        .references("id")
        .inTable("users")
        .onDelete("CASCADE")
        .onUpdate("CASCADE");

      table
        .foreign("category_id")
        .references("id")
        .inTable("categories")
        .onDelete("SET NULL")
        .onUpdate("RESTRICT");

      // Named foreign keys
      table
        .foreign("user_id", "fk_posts_user_id")
        .references("id")
        .inTable("users");

      // Shorthand foreign keys
      table.foreignId("author_id").constrained("users");
      table.foreignUuid("parent_id").constrained("posts");
    });
  }

  async down(schema: SchemaBuilder): Promise<void> {
    await schema.dropTable("posts");
  }
}

Database-Specific Features

// PostgreSQL specific features
export default class PostgreSQLFeatures {
  async up(schema: SchemaBuilder): Promise<void> {
    // Create schema
    await schema.createSchema("analytics");

    // Create table in specific schema
    await schema.createTable("analytics.events", (table) => {
      table.uuid("id").primary();
      table.jsonb("data");
      table.specificType("tags", "text[]");
      table.timestamp("created_at").defaultTo(schema.fn.now());

      // GIN index for JSONB
      table.index(["data"], "events_data_gin", "GIN");

      // Partial index
      table.index(["created_at"], "recent_events_idx", {
        where: "created_at > NOW() - INTERVAL '30 days'",
      });
    });

    // Create extension
    await schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');

    // Create custom type
    await schema.raw(`
      CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')
    `);
  }
}

// MySQL specific features
export default class MySQLFeatures {
  async up(schema: SchemaBuilder): Promise<void> {
    await schema.createTable("products", (table) => {
      table.increments("id");
      table.string("name");
      table.text("description");

      // Full-text index
      table.index(["name", "description"], "fulltext_idx", "FULLTEXT");

      // JSON column with generated column
      table.json("attributes");
      table
        .string("brand")
        .generatedAs('JSON_UNQUOTE(JSON_EXTRACT(attributes, "$.brand"))');

      // Spatial data
      table.point("location");
      table.index(["location"], "location_idx", "SPATIAL");
    });

    // Set table engine and charset
    await schema.raw(`
      ALTER TABLE products 
      ENGINE=InnoDB 
      DEFAULT CHARSET=utf8mb4 
      COLLATE=utf8mb4_unicode_ci
    `);
  }
}

Migration Utilities

export default class UtilityMigration {
  async up(schema: SchemaBuilder): Promise<void> {
    // Check if table exists
    if (await schema.hasTable("users")) {
      console.log("Users table already exists");
      return;
    }

    // Check if column exists
    if (await schema.hasColumn("users", "email")) {
      console.log("Email column already exists");
      return;
    }

    // Raw SQL execution
    await schema.raw("SET foreign_key_checks = 0");

    // Create table with raw SQL
    await schema.raw(`
      CREATE TABLE IF NOT EXISTS custom_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        data JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // Conditional operations based on database
    if (schema.client.config.client === "mysql2") {
      await schema.raw("ALTER TABLE users ADD FULLTEXT(name, bio)");
    } else if (schema.client.config.client === "pg") {
      await schema.raw(
        "CREATE INDEX CONCURRENTLY idx_users_name ON users(name)"
      );
    }
  }
}

Running Migrations

# Run all pending migrations
ilana migrate
ilana migrate --connection=mysql
ilana migrate --connection=postgres_analytics

# Run specific migration file
ilana migrate --only=20231201_create_users_table.ts

# Run migrations up to specific batch
ilana migrate --to=20231201_120000

# Rollback migrations
ilana migrate:rollback
ilana migrate:rollback --step=2
ilana migrate:rollback --to=20231201_120000
ilana migrate:rollback --connection=postgres_analytics

# Reset all migrations
ilana migrate:reset
ilana migrate:reset --connection=mysql

# Fresh migration (drop all + migrate)
ilana migrate:fresh
ilana migrate:fresh --connection=postgres_analytics

# Fresh with seeding
ilana migrate:fresh --seed
ilana migrate:fresh --seed --connection=mysql

# Check migration status
ilana migrate:status
ilana migrate:status --connection=postgres_analytics

# List completed migrations
ilana migrate:list

# Unlock migrations (if stuck)
ilana migrate:unlock

Seeders

Creating Seeders

# Create a seeder
ilana make:seeder UserSeeder

Seeder Structure

JavaScript:

const Seeder = require("ilana-orm/orm/Seeder");
const User = require("../../models/User");

class UserSeeder extends Seeder {
  async run() {
    // Create admin user
    await User.create({
      name: "Admin User",
      email: "[email protected]",
      password: "password",
      role: "admin",
    });

    // Create test users using factory
    await User.factory().times(50).create();

    // Create users with specific states
    await User.factory().times(5).state("admin").create();
  }
}

module.exports = UserSeeder;

TypeScript:

import Seeder from "ilana-orm/orm/Seeder";
import User from "../../models/User";

export default class UserSeeder extends Seeder {
  async run(): Promise<void> {
    // Create admin user
    await User.create({
      name: "Admin User",
      email: "[email protected]",
      password: "password",
      role: "admin",
    });

    // Create test users using factory
    await User.factory().times(50).create();

    // Create users with specific states
    await User.factory().times(5).state("admin").create();
  }
}

### Advanced Seeding Techniques

```typescript
export default class DatabaseSeeder extends Seeder {
  async run(): Promise<void> {
    // Disable foreign key checks
    await this.disableForeignKeyChecks();

    // Truncate tables in correct order
    await this.truncateInOrder([
      "user_roles",
      "posts",
      "users",
      "roles",
      "categories",
    ]);

    // Seed in dependency order
    await this.call([
      RoleSeeder,
      CategorySeeder,
      UserSeeder,
      PostSeeder,
      UserRoleSeeder,
    ]);

    // Re-enable foreign key checks
    await this.enableForeignKeyChecks();
  }

  private async truncateInOrder(tables: string[]): Promise<void> {
    for (const table of tables) {
      await this.db.raw(`TRUNCATE TABLE ${table}`);
    }
  }

  private async call(seeders: any[]): Promise<void> {
    for (const SeederClass of seeders) {
      const seeder = new SeederClass();
      await seeder.run();
      console.log(`Seeded: ${SeederClass.name}`);
    }
  }

  private async disableForeignKeyChecks(): Promise<void> {
    const client = this.db.client.config.client;

    if (client === "mysql2") {
      await this.db.raw("SET FOREIGN_KEY_CHECKS = 0");
    } else if (client === "pg") {
      await this.db.raw("SET session_replication_role = replica");
    }
  }

  private async enableForeignKeyChecks(): Promise<void> {
    const client = this.db.client.config.client;

    if (client === "mysql2") {
      await this.db.raw("SET FOREIGN_KEY_CHECKS = 1");
    } else if (client === "pg") {
      await this.db.raw("SET session_replication_role = DEFAULT");
    }
  }
}

Connection-Specific Seeding

export default class AnalyticsSeeder extends Seeder {
  // Specify connection for this seeder
  protected connection = "analytics_db";

  async run(): Promise<void> {
    // This will run on analytics_db connection
    await AnalyticsEvent.create({
      event_type: "user_signup",
      data: { source: "web" },
      created_at: new Date(),
    });
  }
}

Conditional and Environment-Specific Seeding

export default class UserSeeder extends Seeder {
  async run(): Promise<void> {
    // Only seed if no users exist
    const userCount = await User.count();

    if (userCount === 0) {
      await this.seedUsers();
    }

    // Environment-specific seeding
    if (process.env.NODE_ENV === "development") {
      await this.seedTestData();
    }

    if (process.env.NODE_ENV === "production") {
      await this.seedProductionData();
    }

    // Feature flag based seeding
    if (process.env.ENABLE_PREMIUM_FEATURES === "true") {
      aw