PostgreSQL

Relational database

PostgreSQL is the most advanced open source database.

Installation

# 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

Connect

psql -U postgres
# or with Docker
docker exec -it pg psql -U postgres

Basic SQL

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

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

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

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

-- Delete
DELETE FROM users WHERE id = 1;

Recommended ORMs

Prisma example

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

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

🏦 Fintech Case: Audit Trail and Encryption

Financial regulations (PCI DSS, SOC 2) require logging ALL operations. PostgreSQL has native tools for this:

Audit table

-- Audit trail table
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()
);

-- Indexes for fast audit queries
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);

Automatic trigger

-- Generic audit function
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;

-- Apply to sensitive tables
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();

Sensitive data encryption

-- Use pgcrypto for sensitive data
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Table with encrypted data
CREATE TABLE payment_methods (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  card_last_four VARCHAR(4), -- Only last 4 digits (visible)
  card_token_encrypted BYTEA, -- Encrypted token
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert with encryption
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'))
);

-- Read (only with correct key)
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;

Audit queries for compliance

-- Who modified this record?
SELECT * FROM audit_log
WHERE table_name = 'transactions' AND record_id = '12345'
ORDER BY timestamp DESC;

-- All actions by a user (for fraud investigation)
SELECT * FROM audit_log
WHERE changed_by = 'user@email.com'
AND timestamp > NOW() - INTERVAL '30 days';

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

💡 A well-implemented audit trail in PostgreSQL complies with PCI DSS Req. 10 and facilitates SOC 2 audits.

Practice

→ CRUD with PostgreSQL