dromanis.finora.db

A powerful, type-safe MySQL database utility library designed for modern Node.js applications and AWS Lambda functions. Built with TypeScript and optimized for serverless environments with automatic environment-based configuration.

📋 Table of Contents

• Features
• Installation
• Quick Start
• API Reference
• Environment Configuration
• Usage Examples
• Best Practices
• Error Handling
• Testing
• Contributing
• Changelog
• License

✨ Features

• 🔧 Zero-Config Setup - Automatic configuration from environment variables
• 🛡️ Full TypeScript Support - Comprehensive type definitions and IntelliSense
• ⚡ AWS Lambda Optimized - Connection reuse and serverless-friendly design
• 🔗 Smart Connection Management - Automatic pooling and lifecycle management
• 💾 ACID Transactions - Full transaction support with automatic rollback
• 🔒 SQL Injection Protection - Parameterized queries and prepared statements
• 🔐 SSL/TLS Support - Secure connections for cloud databases (AWS RDS, etc.)
• 📊 Comprehensive Error Handling - Detailed error reporting and logging
• 🧪 Battle-Tested - Full test coverage and production-ready
• 📦 Lightweight - Minimal dependencies with mysql2 as the core

📦 Installation

# Using npm
npm install dromanis.finora.db

# Using yarn
yarn add dromanis.finora.db

# Using pnpm
pnpm add dromanis.finora.db

Requirements

• Node.js: >= 14.0.0
• TypeScript: >= 4.0.0 (optional, but recommended)
• MySQL: >= 5.7 or MariaDB >= 10.2

🚀 Quick Start

Basic Usage

import { Database } from 'dromanis.finora.db';

const db = new Database(); // Auto-loads from environment variables

async function example() {
  try {
    await db.connect();
    
    // Query data
    const users = await db.query('SELECT * FROM users WHERE active = ?', [true]);
    console.log(`Found ${users.rowCount} active users`);
    
    // Insert new record
    const result = await db.insert(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['John Doe', '[email protected]']
    );
    console.log(`Created user with ID: ${result.insertId}`);
    
  } finally {
    await db.disconnect();
  }
}

AWS Lambda Usage

import { Database } from 'dromanis.finora.db';

// Reuse connection across Lambda invocations
let db: Database | null = null;

export const handler = async (event: any) => {
  if (!db) {
    db = new Database();
    await db.connect();
  }
  
  const result = await db.query('SELECT * FROM users WHERE id = ?', [event.userId]);
  
  return {
    statusCode: 200,
    body: JSON.stringify({ user: result.rows[0] })
  };
};

📚 API Reference

Database Class

The main class for all database operations.

Constructor

new Database()

Creates a new Database instance with automatic environment-based configuration.

Throws:

• Error - When required environment variables are missing

Connection Management

connect(): Promise<void>

Establishes a connection to the MySQL database.

await db.connect();

Throws:

• Error - On connection failure (authentication, network, etc.)

disconnect(): Promise<void>

Closes the database connection and cleans up resources.

await db.disconnect();

isConnected(): boolean

Returns the current connection status.

if (db.isConnected()) {
  // Database is ready for operations
}

Query Operations

query<T>(sql: string, params?: any[]): Promise<QueryResult<T>>

Executes a SELECT query and returns typed results.

interface User {
  id: number;
  name: string;
  email: string;
}

const result = await db.query<User>('SELECT * FROM users WHERE id = ?', [1]);
const user: User = result.rows[0];

Parameters:

• sql - The SQL query string
• params - Optional array of parameter values

Returns: Promise<QueryResult<T>>

insert(sql: string, params?: any[]): Promise<QueryResult>

Executes an INSERT statement.

const result = await db.insert(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  ['John Doe', '[email protected]']
);
console.log(`Inserted record with ID: ${result.insertId}`);

Returns: Promise<QueryResult> with insertId and affectedRows

update(sql: string, params?: any[]): Promise<QueryResult>

Executes an UPDATE statement.

const result = await db.update(
  'UPDATE users SET last_login = NOW() WHERE id = ?',
  [userId]
);
console.log(`Updated ${result.affectedRows} record(s)`);

Returns: Promise<QueryResult> with affectedRows

delete(sql: string, params?: any[]): Promise<QueryResult>

Executes a DELETE statement.

const result = await db.delete('DELETE FROM users WHERE id = ?', [userId]);
console.log(`Deleted ${result.affectedRows} record(s)`);

Returns: Promise<QueryResult> with affectedRows

Advanced Operations

transaction<T>(operations: (() => Promise<T>)[]): Promise<T[]>

Executes multiple operations within a single transaction.

const results = await db.transaction([
  () => db.insert('INSERT INTO users (name) VALUES (?)', ['John']),
  () => db.insert('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [1, 100])
]);

Parameters:

• operations - Array of functions that return promises

Returns: Promise<T[]> - Array of results from each operation

getConnection(): Connection | null

Returns the underlying MySQL connection for advanced use cases.

const connection = db.getConnection();
if (connection) {
  // Direct mysql2 operations
}

getConfig(): DatabaseConfig

Returns the current database configuration (excluding sensitive data).

const config = db.getConfig();
console.log(`Connected to: ${config.host}:${config.port}/${config.database}`);

Interfaces and Types

QueryResult

Generic interface for query results.

interface QueryResult<T = any> {
  /** Array of result rows (typed for SELECT queries) */
  rows: T[];
  
  /** Number of rows returned */
  rowCount: number;
  
  /** Auto-generated ID for INSERT operations */
  insertId?: number;
  
  /** Number of affected rows for INSERT/UPDATE/DELETE */
  affectedRows?: number;
}

DatabaseConfig

Configuration interface (internal use).

interface DatabaseConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  ssl?: boolean;
  connectionLimit?: number;
  acquireTimeout?: number;
  timeout?: number;
}

🔧 Environment Configuration

Configure the database connection using environment variables. No manual configuration required!

Required Variables

| Variable | Description | Example | |----------|-------------|---------| | DB_HOST | MySQL server hostname or IP | localhost, my-db.region.rds.amazonaws.com | | DB_PORT | MySQL server port | 3306 | | DB_USER | Database username | myapp_user | | DB_PASSWORD | Database password | secure_password_123 | | DB_NAME | Database name | myapp_production |

Optional Variables

| Variable | Description | Default | Example | |----------|-------------|---------|---------| | DB_SSL | Enable SSL connections | undefined | true, false | | DB_CONNECTION_LIMIT | Max concurrent connections | unlimited | 10 | | DB_ACQUIRE_TIMEOUT | Connection timeout (ms) | mysql2 default | 60000 | | DB_TIMEOUT | Query timeout (ms) | mysql2 default | 30000 |

Environment Examples

Development (.env)

DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=development_password
DB_NAME=myapp_dev
DB_SSL=false

Production (AWS RDS)

DB_HOST=myapp-prod.cluster-abc123.us-east-1.rds.amazonaws.com
DB_PORT=3306
DB_USER=myapp_prod_user
DB_PASSWORD=super_secure_production_password
DB_NAME=myapp_production
DB_SSL=true
DB_CONNECTION_LIMIT=20
DB_ACQUIRE_TIMEOUT=60000
DB_TIMEOUT=30000

📖 Usage Examples

Basic CRUD Operations

import { Database } from 'dromanis.finora.db';

interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

const db = new Database();

async function userOperations() {
  await db.connect();
  
  try {
    // Create user
    const insertResult = await db.insert(
      'INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())',
      ['Alice Johnson', '[email protected]']
    );
    
    const userId = insertResult.insertId!;
    
    // Read user
    const userResult = await db.query<User>(
      'SELECT * FROM users WHERE id = ?',
      [userId]
    );
    
    if (userResult.rowCount === 0) {
      throw new Error('User not found');
    }
    
    const user = userResult.rows[0];
    console.log('User:', user);
    
    // Update user
    await db.update(
      'UPDATE users SET name = ? WHERE id = ?',
      ['Alice Smith', userId]
    );
    
    // Delete user
    await db.delete('DELETE FROM users WHERE id = ?', [userId]);
    
  } finally {
    await db.disconnect();
  }
}

Transaction Example

async function transferFunds(fromAccountId: number, toAccountId: number, amount: number) {
  await db.connect();
  
  try {
    const results = await db.transaction([
      // Check source account balance
      async () => {
        const result = await db.query(
          'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
          [fromAccountId]
        );
        
        if (result.rows[0].balance < amount) {
          throw new Error('Insufficient funds');
        }
        
        return result.rows[0];
      },
      
      // Debit source account
      async () => {
        return await db.update(
          'UPDATE accounts SET balance = balance - ? WHERE id = ?',
          [amount, fromAccountId]
        );
      },
      
      // Credit destination account
      async () => {
        return await db.update(
          'UPDATE accounts SET balance = balance + ? WHERE id = ?',
          [amount, toAccountId]
        );
      },
      
      // Log transaction
      async () => {
        return await db.insert(
          'INSERT INTO transactions (from_account, to_account, amount, created_at) VALUES (?, ?, ?, NOW())',
          [fromAccountId, toAccountId, amount]
        );
      }
    ]);
    
    console.log('Transfer completed successfully');
    return results[3].insertId; // Transaction ID
    
  } finally {
    await db.disconnect();
  }
}

AWS Lambda with Error Handling

import { Database } from 'dromanis.finora.db';

let db: Database | null = null;

export const handler = async (event: any) => {
  try {
    // Initialize connection (reused across invocations)
    if (!db) {
      db = new Database();
      await db.connect();
    }
    
    const { action, payload } = event;
    
    switch (action) {
      case 'getUser':
        const userResult = await db.query(
          'SELECT id, name, email FROM users WHERE id = ?',
          [payload.userId]
        );
        
        if (userResult.rowCount === 0) {
          return {
            statusCode: 404,
            body: JSON.stringify({ error: 'User not found' })
          };
        }
        
        return {
          statusCode: 200,
          body: JSON.stringify({ user: userResult.rows[0] })
        };
        
      case 'createUser':
        const insertResult = await db.insert(
          'INSERT INTO users (name, email) VALUES (?, ?)',
          [payload.name, payload.email]
        );
        
        return {
          statusCode: 201,
          body: JSON.stringify({ 
            userId: insertResult.insertId,
            message: 'User created successfully'
          })
        };
        
      default:
        return {
          statusCode: 400,
          body: JSON.stringify({ error: 'Invalid action' })
        };
    }
    
  } catch (error) {
    console.error('Lambda execution failed:', error);
    
    return {
      statusCode: 500,
      body: JSON.stringify({
        error: 'Internal server error',
        message: error instanceof Error ? error.message : 'Unknown error'
      })
    };
  }
};

🏆 Best Practices

Connection Management

// ✅ Always use try-finally for cleanup
async function operation() {
  const db = new Database();
  try {
    await db.connect();
    // Your operations here
  } finally {
    await db.disconnect();
  }
}

// ✅ AWS Lambda: Reuse connections
let db: Database | null = null;
export const handler = async (event: any) => {
  if (!db) {
    db = new Database();
    await db.connect();
  }
  // Use db here - don't disconnect
};

Query Safety

// ✅ Use parameterized queries
const users = await db.query('SELECT * FROM users WHERE status = ?', ['active']);

// ❌ Never concatenate user input
const users = await db.query(`SELECT * FROM users WHERE status = '${status}'`);

Error Handling

// ✅ Specific error handling
try {
  const result = await db.query('SELECT * FROM users WHERE id = ?', [id]);
  if (result.rowCount === 0) {
    throw new Error('User not found');
  }
  return result.rows[0];
} catch (error) {
  if (error instanceof Error) {
    if (error.message === 'User not found') {
      // Handle business logic error
      return null;
    }
    // Handle database errors
    logger.error('Database error:', error.message);
  }
  throw error;
}

TypeScript Usage

// ✅ Define interfaces for your data
interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

// ✅ Use generic types for type safety
const users = await db.query<User>('SELECT * FROM users');
users.rows.forEach(user => {
  // TypeScript knows user is of type User
  console.log(user.name); // ✅ Type-safe access
});

🚨 Error Handling

The library provides comprehensive error handling for common scenarios:

Connection Errors

try {
  await db.connect();
} catch (error) {
  if (error instanceof Error) {
    if (error.message.includes('Missing required environment variables')) {
      console.error('Configuration error - check environment variables');
    } else if (error.message.includes('ECONNREFUSED')) {
      console.error('Connection refused - check if MySQL server is running');
    } else if (error.message.includes('Access denied')) {
      console.error('Authentication failed - check credentials');
    }
  }
}

Query Errors

try {
  await db.query('SELECT * FROM users');
} catch (error) {
  if (error instanceof Error) {
    if (error.message.includes('Table')) {
      console.error('Table does not exist');
    } else if (error.message.includes('syntax')) {
      console.error('SQL syntax error');
    }
  }
}

Transaction Errors

try {
  await db.transaction([
    () => db.insert('INSERT INTO users (name) VALUES (?)', ['John']),
    () => db.update('UPDATE accounts SET balance = balance + 100 WHERE user_id = ?', [1])
  ]);
} catch (error) {
  // All operations are automatically rolled back
  console.error('Transaction failed and was rolled back:', error);
}

🧪 Testing

Running Tests

# Run all tests
npm test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage
npm run test:coverage

Test Example

import { Database } from 'dromanis.finora.db';

describe('Database Operations', () => {
  let db: Database;

  beforeEach(() => {
    // Set up test environment variables
    process.env.DB_HOST = 'localhost';
    process.env.DB_PORT = '3306';
    process.env.DB_USER = 'test_user';
    process.env.DB_PASSWORD = 'test_password';
    process.env.DB_NAME = 'test_database';
    
    db = new Database();
  });

  it('should create database instance', () => {
    expect(db).toBeInstanceOf(Database);
    expect(db.isConnected()).toBe(false);
  });

  it('should validate required environment variables', () => {
    delete process.env.DB_HOST;
    expect(() => new Database()).toThrow('Missing required environment variables');
  });
});

🔨 Development

Building

npm run build

Cleaning

npm run clean

Project Structure

dromanis.finora.db/
├── src/
│   ├── database.ts           # Main Database class implementation
│   ├── index.ts              # Public API exports
│   └── __tests__/
│       ├── database.test.ts  # Comprehensive test suite
│       └── setup.ts          # Test configuration
├── dist/                     # Compiled TypeScript output
├── tsconfig.json            # TypeScript configuration
├── tsconfig.test.json       # Test-specific TypeScript config
├── jest.config.js           # Jest testing configuration
└── package.json            # Package configuration

🤝 Contributing

We welcome contributions! Please follow these guidelines:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Development Setup

# Clone your fork
git clone https://github.com/yourusername/dromanis.finora.db.git
cd dromanis.finora.db

# Install dependencies
npm install

# Run tests
npm test

# Build the project
npm run build

📝 Changelog

[3.0.1] - Current

• Full TypeScript support with comprehensive type definitions
• Environment-based configuration system
• AWS Lambda optimization with connection reuse
• Transaction support with automatic rollback
• Comprehensive error handling and logging
• Complete test coverage
• SQL injection protection with parameterized queries
• SSL/TLS support for secure connections

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

Made with ❤️ for the Dromanis Finora ecosystem

For questions, issues, or contributions, please visit our GitHub repository.