@200systems/mf-db-postgres

Cliente PostgreSQL para o microframework TypeScript com connection pooling, monitoramento de saúde, gerenciamento de transações e sistema de migrações completo.

Features Implementadas

• 🔗 Connection Pooling - Usando pg (node-postgres) com configuração avançada
• 🏭 Singleton Factory - Gerenciamento centralizado de instâncias de clientes
• 🔄 Transaction Management - Transações automáticas e manuais com BaseTransaction
• 📊 Health Monitoring - Verificações de saúde com métricas de timing
• 🗃️ Migration System - Sistema completo com UP/DOWN scripts e checksum
• ⚙️ TypeScript Support - Tipagem completa para queries e configurações
• 🛡️ Error Handling - Tipos de erro específicos (ConnectionError, QueryError, etc.)
• 📈 Pool Monitoring - Informações detalhadas sobre estado do pool de conexões

Instalação

npm install @200systems/mf-db-postgres

Peer Dependencies

npm install pg
npm install --save-dev @types/pg

Quick Start

1. Configuração Básica

// src/database.ts
import { PostgresFactory, PostgresConfig } from '@200systems/mf-db-postgres';
import { createLogger } from '@200systems/mf-logger';

const config: PostgresConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME || 'myapp',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'secret',
  
  // Pool configuration
  max: 10,
  min: 2,
  connectionTimeoutMillis: 60000,
  idleTimeoutMillis: 30000,
  
  // SSL configuration (production)
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: true
  } : false
};

const logger = createLogger({ context: 'database' });
export const dbClient = PostgresFactory.getInstance(config, logger);

2. Inicialização e Queries

// src/server.ts
import { dbClient } from './database.js';

// Inicializar conexão
await dbClient.initialize();

// Query simples
const users = await dbClient.query<{ id: number; name: string; email: string }>(
  'SELECT id, name, email FROM users WHERE active = $1',
  [true]
);

console.log(`Found ${users.rowCount} users:`, users.rows);

// Cleanup gracioso
process.on('SIGTERM', async () => {
  await dbClient.close();
  process.exit(0);
});

3. Transações

// Transação automática (recomendado)
const result = await dbClient.transaction(async (trx) => {
  // Log de auditoria
  await trx.query('INSERT INTO audit_logs (action, user_id) VALUES ($1, $2)', 
    ['USER_CREATED', 123]);

  // Criar usuário
  const user = await trx.query<{ id: number }>(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
    ['João Silva', '[email protected]']
  );

  // Criar perfil associado
  await trx.query('INSERT INTO user_profiles (user_id, settings) VALUES ($1, $2)', 
    [user.rows[0].id, '{}']);

  return user.rows[0];
});

console.log('Usuário criado:', result);


// ou manual (não recomendado)

const transaction = await client.transaction();
try {
  await transaction.query('INSERT INTO logs (event) VALUES ($1)', ['MANUAL_TX']);
  await transaction.query('UPDATE users SET active = true WHERE id = $1', [42]);
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
}

API Reference

PostgresFactory

interface PostgresConfig {
  // Conexão básica
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  
  // Pool de conexões
  max?: number;                    // Máximo de conexões (padrão: 10)
  min?: number;                    // Mínimo de conexões (padrão: 2)
  connectionTimeoutMillis?: number; // Timeout de conexão (padrão: 60000)
  idleTimeoutMillis?: number;      // Timeout de idle (padrão: 30000)
  
  // SSL (produção)
  ssl?: boolean | {
    rejectUnauthorized?: boolean;
    ca?: string;
    cert?: string;
    key?: string;
  };
  
  // PostgreSQL específico
  statement_timeout?: number;
  query_timeout?: number;
  application_name?: string;
}

// Singleton Factory
class PostgresDatabaseFactory {
  static getInstance(): PostgresDatabaseFactory;
  getInstance(config: DatabaseConfig, logger?: Logger): DatabaseClient;
  closeInstance(): Promise<void>;
}

// Instância padrão para conveniência
const PostgresFactory: PostgresDatabaseFactory;

PostgresClient

class PostgresClient implements DatabaseClient {
  // Ciclo de vida
  initialize(): Promise<void>;
  close(): Promise<void>;
  isReady(): boolean;
  
  // Queries
  query<T>(sql: string, params?: any[]): Promise<QueryResult<T>>;
  transaction<T>(callback: (trx: PostgresTransaction) => Promise<T>): Promise<T>;
  
  // Monitoramento
  healthCheck(): Promise<HealthCheckResult>;
  getConnectionInfo(): { total: number; idle: number; waiting: number };
}

interface QueryResult<T> {
  rows: T[];
  rowCount: number;
  fields: Array<{ name: string; dataTypeID: number }>;
}

interface HealthCheckResult {
  status: 'healthy' | 'unhealthy';
  message: string;
  timestamp: Date;
  responseTime: number;
}

PostgresTransaction

class PostgresTransaction extends BaseTransaction {
  query<T>(sql: string, params?: any[]): Promise<QueryResult<T>>;
  commit(): Promise<void>;
  rollback(): Promise<void>;
  isCompleted(): boolean;
}

// Uso manual (não recomendado)
const trx = await dbClient.transaction();
try {
  await trx.query('INSERT INTO logs (event) VALUES ($1)', ['START']);
  await trx.query('UPDATE counters SET value = value + 1');
  await trx.commit();
} catch (error) {
  await trx.rollback();
  throw error;
}

Sistema de Migrações

PostgresMigrator

import { PostgresMigrator } from '@200systems/mf-db-postgres';

const migrator = new PostgresMigrator(
  dbClient,
  './migrations',
  logger.child('migrator')
);

// Aplicar todas as migrações pendentes
await migrator.migrate();

// Rollback das últimas N migrações
await migrator.rollback(2);

// Verificar status
const status = await migrator.getStatus();
console.log(`Migrações: ${status.applied}/${status.total} aplicadas`);
console.log('Pendentes:', status.pending);

// Listar migrações aplicadas
const applied = await migrator.getApplied();
console.log('Aplicadas:', applied.map(m => `${m.id} - ${m.description}`));

Estrutura de Arquivos de Migração

migrations/
├── 20241201_001_create_users_table.sql
├── 20241201_002_add_user_profiles.sql
├── 20241202_001_add_indexes.sql
└── 20241203_001_add_audit_logs.sql

Formato de Arquivo de Migração

-- Criar tabela de usuários
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- DOWN
DROP INDEX IF EXISTS idx_users_created_at;
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

Regras importantes:

• Arquivo deve ter seção UP (antes de -- DOWN) e seção DOWN (após -- DOWN)
• Primeira linha com -- vira a descrição da migração
• Arquivos aplicados em ordem lexicográfica
• Rollback executa a seção DOWN em ordem reversa

Exemplos de Migrações

-- migrations/20241201_001_create_users_table.sql
-- Criar tabela inicial de usuários
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'user',
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- DOWN
DROP TABLE IF EXISTS users;

-- migrations/20241201_002_add_user_profiles.sql
-- Adicionar tabela de perfis de usuário
CREATE TABLE user_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(20),
  avatar_url TEXT,
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

-- DOWN
DROP INDEX IF EXISTS idx_user_profiles_user_id;
DROP TABLE IF EXISTS user_profiles;

Exemplos Práticos

Configuração com Variáveis de Ambiente

// src/config/database.ts
import { PostgresConfig } from '@200systems/mf-db-postgres';

export const dbConfig: PostgresConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME!,
  user: process.env.DB_USER!,
  password: process.env.DB_PASSWORD!,
  
  // Pool otimizado para produção
  max: parseInt(process.env.DB_POOL_MAX || '10'),
  min: parseInt(process.env.DB_POOL_MIN || '2'),
  connectionTimeoutMillis: parseInt(process.env.DB_CONNECTION_TIMEOUT || '60000'),
  idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT || '30000'),
  
  // SSL em produção
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: process.env.DB_SSL_REJECT_UNAUTHORIZED === 'true',
    ca: process.env.DB_SSL_CA,
    cert: process.env.DB_SSL_CERT,
    key: process.env.DB_SSL_KEY
  } : false,
  
  // Configurações PostgreSQL
  application_name: process.env.APP_NAME || 'microframework-app',
  statement_timeout: 30000,
  query_timeout: 10000
};

Repository Pattern

// src/repositories/UserRepository.ts
import { DatabaseClient, QueryResult } from '@200systems/mf-db-postgres';

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

export class UserRepository {
  constructor(private db: DatabaseClient) {}

  async findById(id: number): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0] || null;
  }

  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE email = $1',
      [email]
    );
    return result.rows[0] || null;
  }

  async findPaginated(limit: number, offset: number): Promise<{ users: User[]; total: number }> {
    const [usersResult, countResult] = await Promise.all([
      this.db.query<User>(
        'SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2',
        [limit, offset]
      ),
      this.db.query<{ count: string }>(
        'SELECT COUNT(*) as count FROM users'
      )
    ]);

    return {
      users: usersResult.rows,
      total: parseInt(countResult.rows[0].count)
    };
  }

  async create(userData: Omit<User, 'id' | 'created_at'>): Promise<User> {
    const result = await this.db.query<User>(
      `INSERT INTO users (name, email, active) 
       VALUES ($1, $2, $3) 
       RETURNING *`,
      [userData.name, userData.email, userData.active]
    );
    return result.rows[0];
  }

  async update(id: number, userData: Partial<Omit<User, 'id' | 'created_at'>>): Promise<User | null> {
    const setClause = Object.keys(userData)
      .map((key, index) => `${key} = $${index + 2}`)
      .join(', ');
    
    const values = [id, ...Object.values(userData)];
    
    const result = await this.db.query<User>(
      `UPDATE users SET ${setClause}, updated_at = NOW() 
       WHERE id = $1 
       RETURNING *`,
      values
    );
    return result.rows[0] || null;
  }

  async delete(id: number): Promise<boolean> {
    const result = await this.db.query(
      'DELETE FROM users WHERE id = $1',
      [id]
    );
    return (result.rowCount || 0) > 0;
  }
}

Service com Transações

// src/services/UserService.ts
import { DatabaseClient } from '@200systems/mf-db-postgres';
import { UserRepository } from '../repositories/UserRepository.js';

export class UserService {
  constructor(
    private db: DatabaseClient,
    private userRepo: UserRepository
  ) {}

  async createUserWithProfile(userData: {
    name: string;
    email: string;
    firstName: string;
    lastName: string;
  }): Promise<{ user: User; profile: UserProfile }> {
    return this.db.transaction(async (trx) => {
      // Criar usuário
      const user = await trx.query<User>(
        'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
        [userData.name, userData.email]
      );

      // Criar perfil
      const profile = await trx.query<UserProfile>(
        `INSERT INTO user_profiles (user_id, first_name, last_name) 
         VALUES ($1, $2, $3) RETURNING *`,
        [user.rows[0].id, userData.firstName, userData.lastName]
      );

      // Log de auditoria
      await trx.query(
        'INSERT INTO audit_logs (action, entity_type, entity_id, details) VALUES ($1, $2, $3, $4)',
        ['CREATE', 'user', user.rows[0].id, JSON.stringify(userData)]
      );

      return {
        user: user.rows[0],
        profile: profile.rows[0]
      };
    });
  }
}

Health Check e Monitoring

// src/health.ts
import { dbClient } from './database.js';

export async function checkDatabaseHealth() {
  const health = await dbClient.healthCheck();
  const connectionInfo = dbClient.getConnectionInfo();
  
  return {
    database: {
      status: health.status,
      message: health.message,
      responseTime: health.responseTime,
      timestamp: health.timestamp
    },
    connectionPool: {
      total: connectionInfo.total,
      idle: connectionInfo.idle,
      waiting: connectionInfo.waiting,
      utilization: `${Math.round((connectionInfo.total - connectionInfo.idle) / connectionInfo.total * 100)}%`
    }
  };
}

// src/routes/health.ts (Express)
app.get('/health', async (req, res) => {
  try {
    const health = await checkDatabaseHealth();
    const statusCode = health.database.status === 'healthy' ? 200 : 503;
    res.status(statusCode).json(health);
  } catch (error) {
    res.status(503).json({
      database: { status: 'unhealthy', message: error.message },
      connectionPool: { total: 0, idle: 0, waiting: 0, utilization: '0%' }
    });
  }
});

Error Handling

Tipos de Erro Disponíveis

import { 
  ConnectionError, 
  QueryError, 
  TransactionError, 
  MigrationError 
} from '@200systems/mf-db-postgres';

try {
  await dbClient.query('SELECT * FROM users');
} catch (error) {
  if (error instanceof ConnectionError) {
    console.error('Erro de conexão:', error.message);
    // Tentar reconectar ou usar fallback
  } else if (error instanceof QueryError) {
    console.error('Erro na query:', error.message);
    console.error('SQL:', error.sql);
    console.error('Params:', error.params);
  } else if (error instanceof TransactionError) {
    console.error('Erro na transação:', error.message);
    // Transaction já foi automaticamente revertida
  }
}

Padrão Singleton e Múltiplos Clientes

// Múltiplos clientes para diferentes bases
const mainDbClient = PostgresFactory.getInstance({
  host: 'localhost',
  database: 'main_app',
  user: 'app_user',
  password: 'secret'
});

const analyticsDbClient = PostgresFactory.getInstance({
  host: 'analytics-server',
  database: 'analytics',
  user: 'analytics_user', 
  password: 'secret'
});

// Clients são automaticamente reutilizados se config for idêntica
const sameClient = PostgresFactory.getInstance({
  host: 'localhost',
  database: 'main_app',
  user: 'app_user',
  password: 'secret'
}); // Retorna a mesma instância de mainDbClient

// Cleanup de todas as conexões
await PostgresDatabaseFactory.getInstance().closeInstance();

Configuração de Ambiente

# .env
# Configuração do Banco
DB_HOST=localhost
DB_PORT=5432
DB_NAME=microframework_app
DB_USER=postgres
DB_PASSWORD=your_password

# SSL (produção)
DB_SSL=true
DB_SSL_REJECT_UNAUTHORIZED=true
DB_SSL_CA=/path/to/ca.pem
DB_SSL_CERT=/path/to/cert.pem
DB_SSL_KEY=/path/to/key.pem

# Pool de Conexões
DB_POOL_MAX=10
DB_POOL_MIN=2
DB_CONNECTION_TIMEOUT=60000
DB_IDLE_TIMEOUT=30000

# Configuração da Aplicação
APP_NAME=microframework-app
NODE_ENV=production
LOG_LEVEL=info

Migration CLI Script

// scripts/migrate.ts
import { dbClient } from '../src/database.js';
import { PostgresMigrator } from '@200systems/mf-db-postgres';
import { createLogger } from '@200systems/mf-logger';

const logger = createLogger({ context: 'migration-cli' });
const migrator = new PostgresMigrator(dbClient, './migrations', logger);

async function main() {
  const command = process.argv[2];
  
  await dbClient.initialize();
  
  try {
    switch (command) {
      case 'up':
        await migrator.migrate();
        break;
      case 'down':
        const steps = parseInt(process.argv[3]) || 1;
        await migrator.rollback(steps);
        break;
      case 'status':
        const status = await migrator.getStatus();
        console.log(`Migrations: ${status.applied}/${status.total}`);
        if (status.pending.length > 0) {
          console.log('Pending:', status.pending);
        }
        break;
      default:
        console.log('Usage: npm run migrate [up|down|status] [steps]');
    }
  } finally {
    await dbClient.close();
  }
}

main().catch(console.error);

// package.json scripts
{
  "scripts": {
    "migrate": "tsx scripts/migrate.ts",
    "migrate:up": "npm run migrate up",
    "migrate:down": "npm run migrate down",
    "migrate:status": "npm run migrate status"
  }
}

Integração com Express Adapter

// src/server.ts - Integração com @200systems/mf-express-adapter
import { ExpressApp } from '@200systems/mf-express-adapter';
import { dbClient } from './database.js';

const app = new ExpressApp({
  port: 3000,
  cors: { origin: true }
});

// Middleware de database (personalizado)
app.getApp().use((req, res, next) => {
  req.db = dbClient;
  next();
});

// Health check endpoint
app.addRoute({
  method: 'get',
  path: '/health',
  handler: async (req, res) => {
    const health = await checkDatabaseHealth();
    const statusCode = health.database.status === 'healthy' ? 200 : 503;
    res.status(statusCode).json(health);
  }
});

// Aplicar migrações na inicialização
const migrator = new PostgresMigrator(dbClient, './migrations', logger);
await dbClient.initialize();
await migrator.migrate();

await app.start();

Melhores Práticas

1. Use o padrão Singleton para evitar múltiplas conexões desnecessárias
2. Sempre inicialize o cliente antes de usar (await dbClient.initialize())
3. Use transações para operações que modificam múltiplas tabelas
4. Implemente health checks para monitoramento de produção
5. Configure SSL adequadamente em produção
6. Use Repository pattern para organizar queries
7. Monitore o pool de conexões em produção
8. Teste migrações em ambiente de desenvolvimento primeiro
9. Implemente error handling específico para cada tipo de erro
10. Use TypeScript para tipagem de queries e configurações

Dependencies

Required

• @200systems/mf-logger - Sistema de logging
• @200systems/mf-db-core - Abstrações de banco de dados

Peer Dependencies

• pg - Driver PostgreSQL para Node.js (^8.11.0)

Dev Dependencies

• @types/pg - Tipos TypeScript para pg

License

MIT