npm package: @200systems/mf-repository-base
Description: Base repository classes with generic CRUD operations and query building
Author: Cleiton Marques
Latest version: 1.1.6

# @200systems/mf-repository-base

💾 SQL Repository Foundation for PostgreSQL and MySQL


Generic repository base classes with CRUD operations for the microframework. Provides a simple, type-safe data access layer that works consistently across PostgreSQL and MySQL databases.

## 🚀 Features

- ✅ **Generic CRUD Operations**: Type-safe create, read, update, delete operations
- 🔁 **Database Agnostic**: Works with both PostgreSQL and MySQL through unified interface
- 🧠 **Simple Query Methods**: Easy-to-use methods for common database operations
- 🧩 **Pagination Support**: Built-in pagination with metadata
- 🛡 **Automatic SQL Adaptation**: Handles PostgreSQL `$1` vs MySQL `?` parameter differences
- 🔍 **Logging Integration**: Comprehensive logging with performance tracking
- 🧪 **Type Safety**: Full TypeScript support with generic entity types
- 📦 **No ORM Complexity**: Simple, lightweight approach focused on SQL

## 📦 Installation

```bash
npm install @200systems/mf-repository-base
```

## ⚡ Quick Start

### 1. Define Your Entity

```typescript
import { BaseEntity } from '@200systems/mf-repository-base';

// Simple entity interface
interface User extends BaseEntity {
  name: string;
  email: string;
  active: boolean;
}
```

### 2. Create Repository

```typescript
import { BaseRepository, DatabaseClient } from '@200systems/mf-repository-base';

class UserRepository extends BaseRepository<User> {
  constructor(db: DatabaseClient) {
    super(db, 'users'); // Pass database client and table name
  }

  // Add custom methods as needed
  async findByEmail(email: string): Promise<User | null> {
    return this.findOne('email = ?', [email]);
  }

  async findActiveUsers(): Promise<User[]> {
    return this.findMany('active = ?', [true]);
  }
}
```

### 3. Use Repository

```typescript
import { DatabaseFactory } from '@200systems/mf-db-postgres';

// Initialize database
const db = DatabaseFactory.getInstance({
  host: 'localhost',
  database: 'myapp',
  user: 'user',
  password: 'password'
});

// Create repository
const userRepository = new UserRepository(db);

// Use CRUD operations
const user = await userRepository.create({
  name: 'John Doe',
  email: 'john@example.com',
  active: true
});

const foundUser = await userRepository.findById(user.id!);
const allUsers = await userRepository.findAll();
const activeUsers = await userRepository.findActiveUsers();
```

## 📚 API Reference

### BaseRepository<T>

Generic base repository class providing CRUD operations for any entity type.

#### Constructor

```typescript
constructor(
  db: DatabaseClient,
  tableName: string,
  logger?: Logger
)
```

#### Basic CRUD Methods

##### `findById(id: number): Promise<T | null>`
Find a single record by ID.

```typescript
const user = await userRepository.findById(123);
```

##### `findOne(condition: string, params?: any[]): Promise<T | null>`
Find a single record by condition.

```typescript
const user = await userRepository.findOne('email = ?', ['john@example.com']);
```

##### `findMany(condition?: string, params?: any[]): Promise<T[]>`
Find multiple records by condition.

```typescript
const activeUsers = await userRepository.findMany('active = ?', [true]);
const allUsers = await userRepository.findMany(); // No condition = all records
```

##### `findAll(options?: QueryOptions): Promise<T[]>`
Find all records with optional sorting and pagination.

```typescript
// Simple find all
const users = await userRepository.findAll();

// With pagination
const users = await userRepository.findAll({
  pagination: { limit: 10, offset: 20 }
});

// With sorting
const users = await userRepository.findAll({
  sort: { column: 'name', direction: 'ASC' }
});

// Multiple sorts
const users = await userRepository.findAll({
  sort: [
    { column: 'active', direction: 'DESC' },
    { column: 'name', direction: 'ASC' }
  ]
});
```

##### `create(data: CreateEntityData<T>): Promise<T>`
Create a new record. Automatically excludes `id`, `created_at`, and `updated_at`.

```typescript
const user = await userRepository.create({
  name: 'John Doe',
  email: 'john@example.com',
  active: true
});
```

##### `update(id: number, data: UpdateEntityData<T>): Promise<T | null>`
Update a record by ID. Automatically excludes `id` and `created_at`.

```typescript
const updated = await userRepository.update(123, {
  name: 'Jane Doe',
  active: false
});
```

##### `delete(id: number): Promise<boolean>`
Delete a record by ID. Returns `true` if record was deleted, `false` if not found.

```typescript
const deleted = await userRepository.delete(123);
```

#### Utility Methods

##### `count(condition?: string, params?: any[]): Promise<number>`
Count records with optional condition.

```typescript
const totalUsers = await userRepository.count();
const activeCount = await userRepository.count('active = ?', [true]);
```

##### `exists(id: number): Promise<boolean>`
Check if a record exists by ID.

```typescript
const userExists = await userRepository.exists(123);
```

##### `findPaginated(options): Promise<PaginatedResult<T>>`
Find records with pagination and metadata.

```typescript
const result = await userRepository.findPaginated({
  condition: 'active = ?',
  params: [true],
  pagination: { limit: 10, offset: 0 }
});

console.log(result.data);        // Array of records
console.log(result.total);       // Total count
console.log(result.hasNext);     // Boolean
console.log(result.hasPrevious); // Boolean
```

##### `rawQuery<R>(sql: string, params?: any[]): Promise<QueryResult<R>>`
Execute raw SQL query (use with caution).

```typescript
const result = await userRepository.rawQuery<User>(
  'SELECT * FROM users WHERE created_at > ?',
  [new Date('2024-01-01')]
);
```

## 🧾 Types

### Entity Types

```typescript
// Base entity with common fields
interface BaseEntity {
  id?: number;
  created_at?: Date;
  updated_at?: Date;
}

// Simple entity with just ID
interface SimpleEntity {
  id?: number;
}

// Helper types for CRUD operations
type CreateEntityData<T> = Omit<T, 'id' | 'created_at' | 'updated_at'>;
type UpdateEntityData<T> = Partial<Omit<T, 'id' | 'created_at'>>;
```

### Query Types

```typescript
interface QueryOptions {
  pagination?: {
    limit?: number;
    offset?: number;
  };
  sort?: {
    column: string;
    direction?: 'ASC' | 'DESC';
  } | Array<{
    column: string;
    direction?: 'ASC' | 'DESC';
  }>;
}

interface PaginatedResult<T> {
  data: T[];
  total: number;
  limit: number;
  offset: number;
  hasNext: boolean;
  hasPrevious: boolean;
}
```

## 🛠 Database Compatibility

This package automatically handles differences between PostgreSQL and MySQL:

| Feature | PostgreSQL | MySQL | Repository Behavior |
|---------|------------|-------|-------------------|
| Parameters | `$1, $2, $3` | `?, ?, ?` | Automatically converted |
| RETURNING | Supported | Not supported | Uses RETURNING when available, falls back to SELECT |
| INSERT ID | RETURNING * | LAST_INSERT_ID() | Handles both approaches |
| Timestamps | NOW() | NOW() | Consistent across both |

## 🧠 Custom Repository Examples

### Basic Custom Repository

```typescript
class ProductRepository extends BaseRepository<Product> {
  constructor(db: DatabaseClient) {
    super(db, 'products');
  }

  async findByCategory(categoryId: number): Promise<Product[]> {
    return this.findMany('category_id = ?', [categoryId]);
  }

  async findFeatured(): Promise<Product[]> {
    return this.findMany('featured = ?', [true]);
  }

  async searchByName(name: string): Promise<Product[]> {
    return this.findMany('name ILIKE ?', [`%${name}%`]);
  }
}
```

### Repository with Complex Queries

```typescript
class OrderRepository extends BaseRepository<Order> {
  constructor(db: DatabaseClient) {
    super(db, 'orders');
  }

  async findByDateRange(startDate: Date, endDate: Date): Promise<Order[]> {
    return this.findMany(
      'created_at >= ? AND created_at <= ?',
      [startDate, endDate]
    );
  }

  async getMonthlyStats(year: number, month: number) {
    const sql = `
      SELECT 
        COUNT(*) as total_orders,
        SUM(total_amount) as total_revenue,
        AVG(total_amount) as avg_order_value
      FROM orders 
      WHERE EXTRACT(YEAR FROM created_at) = ?
        AND EXTRACT(MONTH FROM created_at) = ?
    `;
    
    const result = await this.rawQuery<{
      total_orders: number;
      total_revenue: number;
      avg_order_value: number;
    }>(sql, [year, month]);
    
    return result.rows[0];
  }
}
```

### Repository with Soft Deletes

```typescript
interface SoftDeletableEntity extends BaseEntity {
  deleted_at?: Date;
}

class UserRepository extends BaseRepository<User & SoftDeletableEntity> {
  constructor(db: DatabaseClient) {
    super(db, 'users');
  }

  // Override findMany to exclude soft-deleted records by default
  async findMany(condition?: string, params: any[] = []): Promise<User[]> {
    const softDeleteCondition = 'deleted_at IS NULL';
    
    if (condition) {
      condition = `${condition} AND ${softDeleteCondition}`;
    } else {
      condition = softDeleteCondition;
    }
    
    return super.findMany(condition, params);
  }

  // Soft delete method
  async softDelete(id: number): Promise<boolean> {
    const updated = await this.update(id, { 
      deleted_at: new Date() 
    } as Partial<User>);
    return updated !== null;
  }

  // Find including soft-deleted records
  async findAllIncludingDeleted(): Promise<User[]> {
    return super.findMany(); // Bypass the override
  }
}
```

## 🧩 Integration with Service Layer

```typescript
import { UserRepository } from './repositories/UserRepository';
import { createLogger } from '@200systems/mf-logger';

class UserService {
  private logger = createLogger().child('user-service');

  constructor(private userRepository: UserRepository) {}

  async createUser(userData: CreateUserRequest): Promise<User> {
    // Validation happens here in service layer
    if (!userData.email || !userData.name) {
      throw new Error('Email and name are required');
    }

    // Check if user already exists
    const existing = await this.userRepository.findByEmail(userData.email);
    if (existing) {
      throw new Error('User with this email already exists');
    }

    // Create user (repository handles the database operation)
    const user = await this.userRepository.create({
      name: userData.name,
      email: userData.email,
      active: true
    });

    this.logger.info('User created successfully', { userId: user.id });
    return user;
  }

  async getUserById(id: number): Promise<User> {
    const user = await this.userRepository.findById(id);
    if (!user) {
      throw new Error('User not found');
    }
    return user;
  }
}
```

## 💡 Best Practices

### 1. Keep Repositories Simple
Repositories should focus on data access, not business logic:

```typescript
// ✅ Good - Simple data access
async findActiveUsers(): Promise<User[]> {
  return this.findMany('active = ?', [true]);
}

// ❌ Bad - Business logic in repository
async createUserWithValidation(userData: any): Promise<User> {
  // Validation should be in service layer
  if (!userData.email) throw new Error('Email required');
  return this.create(userData);
}
```

### 2. Use Type Safety
Always define proper entity interfaces:

```typescript
// ✅ Good - Properly typed
interface User extends BaseEntity {
  name: string;
  email: string;
  active: boolean;
}

// ❌ Bad - Losing type safety
interface User {
  [key: string]: any;
}
```

### 3. Custom Methods for Domain Logic
Add repository methods that make sense for your domain:

```typescript
class UserRepository extends BaseRepository<User> {
  // Domain-specific finder methods
  async findByEmail(email: string): Promise<User | null> {
    return this.findOne('email = ?', [email]);
  }

  async findActiveUsersInDepartment(departmentId: number): Promise<User[]> {
    return this.findMany(
      'active = ? AND department_id = ?', 
      [true, departmentId]
    );
  }
}
```

### 4. Error Handling
Let errors bubble up to the service layer:

```typescript
class UserService {
  async getUser(id: number): Promise<User> {
    try {
      const user = await this.userRepository.findById(id);
      if (!user) {
        throw new Error('User not found');
      }
      return user;
    } catch (error) {
      this.logger.error('Failed to get user', error, { userId: id });
      throw error; // Re-throw for caller to handle
    }
  }
}
```

## ⚙️ Performance Tips

### 1. Use Appropriate Queries
```typescript
// ✅ Good - Specific condition
const activeUsers = await userRepository.findMany('active = ?', [true]);

// ❌ Bad - Loading all then filtering
const allUsers = await userRepository.findAll();
const activeUsers = allUsers.filter(u => u.active);
```

### 2. Pagination for Large Datasets
```typescript
// ✅ Good - Paginated
const users = await userRepository.findPaginated({
  pagination: { limit: 20, offset: 0 }
});

// ❌ Bad - Loading everything
const allUsers = await userRepository.findAll();
```

### 3. Count Before Fetching
```typescript
// ✅ Good - Check count first
const userCount = await userRepository.count('active = ?', [true]);
if (userCount > 1000) {
  // Handle large dataset differently
}
```

## 🧬 Dependencies

This package depends on:
- `@200systems/mf-db-core` - Database interfaces and types
- `@200systems/mf-logger` - Logging functionality

## 📜 License

MIT