Base de datos relacional

PostgreSQL es la base de datos open source más avanzada.

Instalación

# macOS
brew install postgresql@16
brew services start postgresql@16

# Docker
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16

Conectar

psql -U postgres
# o con Docker
docker exec -it pg psql -U postgres

SQL básico

-- Crear tabla
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insertar
INSERT INTO users (email, name) VALUES ('ana@email.com', 'Ana');

-- Consultar
SELECT * FROM users WHERE email LIKE '%@email.com';

-- Actualizar
UPDATE users SET name = 'Ana García' WHERE id = 1;

-- Eliminar
DELETE FROM users WHERE id = 1;

ORMs recomendados

ORM	Lenguaje
Prisma	TypeScript
Drizzle	TypeScript
SQLAlchemy	Python

Prisma ejemplo

const user = await prisma.user.create({
  data: { email: 'ana@email.com', name: 'Ana' }
})

const users = await prisma.user.findMany({
  where: { email: { contains: '@email.com' } }
})

🏦 Caso Fintech: Audit Trail y Encriptación

Las regulaciones financieras (PCI DSS, SOC 2) requieren registrar TODAS las operaciones. PostgreSQL tiene herramientas nativas para esto:

Tabla de auditoría

-- Tabla de audit trail
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  record_id VARCHAR(100) NOT NULL,
  action VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE
  old_data JSONB,
  new_data JSONB,
  changed_by VARCHAR(100),
  ip_address INET,
  user_agent TEXT,
  timestamp TIMESTAMPTZ DEFAULT NOW()
);

-- Índices para búsquedas rápidas de auditoría
CREATE INDEX idx_audit_table ON audit_log(table_name);
CREATE INDEX idx_audit_timestamp ON audit_log(timestamp);
CREATE INDEX idx_audit_user ON audit_log(changed_by);

Trigger automático

-- Función de auditoría genérica
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)
    VALUES (TG_TABLE_NAME, OLD.id::text, 'DELETE', row_to_json(OLD)::jsonb, current_setting('app.current_user', true));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
    VALUES (TG_TABLE_NAME, NEW.id::text, 'UPDATE', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_setting('app.current_user', true));
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)
    VALUES (TG_TABLE_NAME, NEW.id::text, 'INSERT', row_to_json(NEW)::jsonb, current_setting('app.current_user', true));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Aplicar a tablas sensibles
CREATE TRIGGER audit_transactions
AFTER INSERT OR UPDATE OR DELETE ON transactions
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Encriptación de datos sensibles

-- Usar pgcrypto para datos sensibles
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Tabla con datos encriptados
CREATE TABLE payment_methods (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  card_last_four VARCHAR(4), -- Solo últimos 4 dígitos (visible)
  card_token_encrypted BYTEA, -- Token encriptado
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertar con encriptación
INSERT INTO payment_methods (user_id, card_last_four, card_token_encrypted)
VALUES (
  1,
  '4242',
  pgp_sym_encrypt('tok_visa_4242', current_setting('app.encryption_key'))
);

-- Leer (solo con la llave correcta)
SELECT user_id, card_last_four,
       pgp_sym_decrypt(card_token_encrypted, current_setting('app.encryption_key')) as token
FROM payment_methods WHERE user_id = 1;

Consultas de auditoría para compliance

-- Quién modificó este registro?
SELECT * FROM audit_log
WHERE table_name = 'transactions' AND record_id = '12345'
ORDER BY timestamp DESC;

-- Todas las acciones de un usuario (para investigación de fraude)
SELECT * FROM audit_log
WHERE changed_by = 'user@email.com'
AND timestamp > NOW() - INTERVAL '30 days';

-- Exportar para auditor externo
COPY (
  SELECT * FROM audit_log
  WHERE timestamp BETWEEN '2026-01-01' AND '2026-12-31'
) TO '/tmp/audit_2026.csv' CSV HEADER;

💡 Un audit trail bien implementado en PostgreSQL cumple con PCI DSS Req. 10 y facilita auditorías SOC 2.

Practica

→ CRUD con PostgreSQL