@tamasha/sql-common

Shared SQL database utilities and connection management for Tamasha services using pg-promise.

Overview

This package provides shared SQL database utilities, connection management, and repository patterns for Tamasha microservices. It ensures type safety, consistency, and robust error handling across all services that communicate with PostgreSQL databases.

Features

• 🚀 Connection Management: Singleton pattern for managing database connections with connection pooling
• 📝 RawQuery: Simple and direct SQL execution with parameterized queries
• 🏗️ Ultra-Minimal Base Repository: Essential CRUD operations only
• 🔄 Migration System: Database schema migrations with up/down support
• 💾 Transaction Management: Atomic operations with retry logic
• ✅ Validation: Input validation for database operations
• 📊 Pagination: Built-in pagination support
• 📈 Structured Logging: Pino-based logging with pretty formatting
• ⚙️ Configuration Management: Flexible database configuration for different environments

Installation

npm install @tamasha/sql-common

Usage

Basic Database Connection

import { dbManager, RawQuery } from '@tamasha/sql-common';

// Get database connection
const db = dbManager.getConnection();

// Create RawQuery instance (uses Pino logger by default)
const rawQuery = new RawQuery(db);

// Simple SELECT query
const users = await rawQuery.query<User>('SELECT * FROM users WHERE status = $1', ['active']);

Logging Configuration

The package uses Pino for structured logging with pretty formatting. You can configure logging via environment variables:

# Set log level (default: info)
LOG_LEVEL=debug

# Other Pino options can be configured via environment variables

RawQuery Examples

import { RawQuery } from '@tamasha/sql-common';

const rawQuery = new RawQuery(dbManager.getConnection());

// Query all records
const users = await rawQuery.query<User>('SELECT * FROM users');

// Query single record
const user = await rawQuery.queryOne<User>('SELECT * FROM users WHERE id = $1', ['123']);

// Query single value
const count = await rawQuery.queryValue<number>('SELECT COUNT(*) FROM users');

// Execute INSERT/UPDATE/DELETE
const result = await rawQuery.execute(
  'INSERT INTO users (email, name) VALUES ($1, $2)',
  ['[email protected]', 'User Name']
);

// Pagination
const paginated = await rawQuery.queryWithPagination<User>(
  'SELECT * FROM users ORDER BY id DESC',
  [],
  1, // page
  10 // pageSize
);

// Transactions
await rawQuery.transaction(async (tx) => {
  await tx.execute('INSERT INTO users (email) VALUES ($1)', ['[email protected]']);
  await tx.execute('INSERT INTO users (email) VALUES ($1)', ['[email protected]']);
});

Ultra-Minimal Repository Pattern

import { BaseRepository } from '@tamasha/sql-common';

interface User extends BaseEntity {
  email: string;
  name: string;
  status: string;
}

class UserRepository extends BaseRepository<User> {
  constructor(db: any) {
    super(db, {
      tableName: 'users',
      primaryKey: 'id'
    });
  }

  // Custom methods using RawQuery
  async findByEmail(email: string): Promise<User | null> {
    const sql = 'SELECT * FROM users WHERE email = $1';
    return this.createRawQuery().queryOne<User>(sql, [email]);
  }

  async findActiveUsers(): Promise<User[]> {
    const sql = 'SELECT * FROM users WHERE status = $1';
    return this.createRawQuery().query<User>(sql, ['active']);
  }
}

// Usage - Only 5 essential methods
const userRepo = new UserRepository(dbManager.getConnection());

// Essential methods
const users = await userRepo.findAll();           // Get all records
const user = await userRepo.findById('123');     // Get by ID
const newUser = await userRepo.create(data);      // Create new record
const updated = await userRepo.update('123', data); // Update record
const deleted = await userRepo.delete('123');     // Delete record
const count = await userRepo.count();             // Count records

// Custom methods
const userByEmail = await userRepo.findByEmail('[email protected]');
const activeUsers = await userRepo.findActiveUsers();

Database Configuration

import { DatabaseManager } from '@tamasha/sql-common';

// Create custom configuration
const customConfig = {
  host: 'localhost',
  port: 5432,
  database: 'my_database',
  user: 'postgres',
  password: 'password',
  max: 10,
  ssl: false
};

// Create custom manager instance (uses Pino logger by default)
const customManager = DatabaseManager.createInstance(customConfig);

// Use with repositories
const userRepo = new UserRepository(customManager.getConnection());

Migrations

import { MigrationManager } from '@tamasha/sql-common';

const migrationManager = new MigrationManager(dbManager.getConnection());

// Run all pending migrations
await migrationManager.migrate();

// Create new migration
await migrationManager.createMigration('add_user_table');

Transactions

import { createTransactionManager } from '@tamasha/sql-common';

const transactionManager = createTransactionManager(dbManager.getConnection());

await transactionManager.executeInTransaction(async (transaction) => {
  const userRepo = new UserRepository(transaction);
  await userRepo.create({ email: '[email protected]', name: 'User' });
});

Environment Variables

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=tamasha
DB_USER=postgres
DB_PASSWORD=password
DB_SSL=false

# Connection Pool Settings
DB_MAX_CONNECTIONS=20
DB_IDLE_TIMEOUT=30000
DB_CONNECTION_TIMEOUT=2000

# Logging Configuration
LOG_LEVEL=info

Development

Prerequisites

• Node.js >= 18.0.0
• PostgreSQL
• TypeScript

Setup

1. Install dependencies:

   npm install

2. Set up environment variables:

   cp .env.example .env
   # Edit .env with your database configuration

3. Run migrations:

   npm run migrate

4. Build the package:

   npm run build

Testing

npm test
npm run test:watch

Publishing

npm publish

Project Structure

├── src/                     # TypeScript source code
│   ├── database-manager.ts  # Database connection management
│   ├── raw-query.ts         # Simple SQL execution
│   ├── base-repository.ts   # Ultra-minimal repository pattern
│   ├── migration-manager.ts # Database migrations
│   ├── utils/               # Utility functions
│   │   ├── transaction-manager.ts
│   │   ├── query-utils.ts
│   │   ├── validation-utils.ts
│   │   └── logger.ts        # Pino logger implementation
│   ├── types/               # TypeScript type definitions
│   └── index.ts             # Main export file
├── migrations/              # Database migration files
├── example/                 # Usage examples
│   ├── usage.ts             # Basic usage example
│   ├── raw-query-example.ts # RawQuery examples
│   └── configuration-example.ts # Configuration examples
├── dist/                    # Compiled JavaScript (generated)
├── package.json             # Package configuration
├── tsconfig.json            # TypeScript configuration
└── README.md               # This file

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

License

MIT License - see LICENSE file for details.