Install / psql

# ===== Option 1: Docker (recommended for dev) =====
docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_USER=dev -e POSTGRES_PASSWORD=dev -e POSTGRES_DB=app \
  -v pg-data:/var/lib/postgresql/data \
  postgres:16

# Connect:
psql 'postgres://dev:dev@localhost:5432/app'

# ===== Option 2: native install =====
# macOS:
brew install postgresql@16
brew services start postgresql@16
createdb $(whoami)
psql

# Ubuntu / Debian:
sudo apt update && sudo apt install -y postgresql-16
sudo -u postgres createuser --interactive --pwprompt dev
sudo -u postgres createdb -O dev app
psql 'postgres://dev:dev@localhost:5432/app'

# Windows: download the installer from postgresql.org
# Includes pgAdmin GUI.

# ===== Option 3: managed (when you want production-ish) =====
# - Supabase, Neon, Render, Railway: free dev tiers
# - AWS RDS, Google Cloud SQL, Azure DB for Postgres for serious use
# Pick when you do not want to run a DB locally.

# ===== Verify =====
psql -c 'SELECT version();'
# PostgreSQL 16.x ... on ...

# ===== Hello, database =====
psql 'postgres://dev:dev@localhost:5432/app'

CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name  TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (email, name) VALUES ('a@x.io', 'Alex');
SELECT * FROM users;

# Note: gen_random_uuid() lives in pgcrypto (or built-in on 13+).
CREATE EXTENSION IF NOT EXISTS pgcrypto;

# ===== Drivers =====
# Node:    npm install pg            (low-level) or @prisma/client (ORM)
# Python:  pip install psycopg2-binary
# Go:      github.com/jackc/pgx
# Java:    org.postgresql:postgresql
# Ruby:    gem install pg

# ===== Client tools =====
# psql        canonical CLI
# pgcli       psql with autocomplete + syntax highlighting
# DBeaver     free cross-DB GUI
# TablePlus   commercial GUI

# ===== Patterns to internalise =====
# - Docker for ephemeral dev DBs
# - One Postgres per environment; one DB per service
# - Use TIMESTAMPTZ everywhere; never TIMESTAMP without tz
# - UUIDs as PKs from day one for distributed-friendly IDs

# ===== Pitfalls =====
# - Exposing 5432 without auth on a public IP
# - postgres user with the default password
# - Storing data in the container without a volume -> wipes on rm
# - Mixing client + server major versions; check protocol compat

# Ubuntu
sudo apt install postgresql postgresql-client
# macOS
brew install postgresql
# Connect
psql -h localhost -U postgres