@200systems/mf-db-postgres

@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', '[e-mail]']
  );

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

• Use o padrão Singleton para evitar múltiplas conexões desnecessárias
• Sempre inicialize o cliente antes de usar (await dbClient.initialize())
• Use transações para operações que modificam múltiplas tabelas
• Implemente health checks para monitoramento de produção
• Configure SSL adequadamente em produção
• Use Repository pattern para organizar queries
• Monitore o pool de conexões em produção
• Teste migrações em ambiente de desenvolvimento primeiro
• Implemente error handling específico para cada tipo de erro
• 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