@groundbrick/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

npm install @groundbrick/repository-base

⚡ Quick Start

1. Define Your Entity

import { BaseEntity } from '@groundbrick/repository-base';

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

2. Create Repository

import { BaseRepository, DatabaseClient } from '@groundbrick/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

import { DatabaseFactory } from '@groundbrick/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: '[email protected]',
  active: true
});

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

📚 API Reference

BaseRepository

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

Constructor

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

Basic CRUD Methods

findById(id: number): Promise<T | null>

Find a single record by ID.

const user = await userRepository.findById(123);

findOne(condition: string, params?: any[]): Promise<T | null>

Find a single record by condition.

const user = await userRepository.findOne('email = ?', ['[email protected]']);

findMany(condition?: string, params?: any[]): Promise<T[]>

Find multiple records by condition.

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.

// 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.

const user = await userRepository.create({
  name: 'John Doe',
  email: '[email protected]',
  active: true
});

update(id: number, data: UpdateEntityData<T>): Promise<T | null>

Update a record by ID. Automatically excludes id and created_at.

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.

const deleted = await userRepository.delete(123);

Utility Methods

count(condition?: string, params?: any[]): Promise<number>

Count records with optional condition.

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

exists(id: number): Promise<boolean>

Check if a record exists by ID.

const userExists = await userRepository.exists(123);

findPaginated(options): Promise<PaginatedResult<T>>

Find records with pagination and metadata.

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).

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

🧾 Types

Entity Types

// 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

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

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

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

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

import { UserRepository } from './repositories/UserRepository';
import { createLogger } from '@groundbrick/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:

// ✅ 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:

// ✅ 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:

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:

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

// ✅ 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

// ✅ Good - Paginated
const users = await userRepository.findPaginated({
  pagination: { limit: 20, offset: 0 }
});

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

3. Count Before Fetching

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

🧬 Dependencies

This package depends on:

• @groundbrick/db-core - Database interfaces and types
• @groundbrick/logger - Logging functionality

📜 License

MIT