Databases

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

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  published BOOLEAN DEFAULT FALSE
);

// MongoDB document
{
  "_id": ObjectId("64a1b2c3d4e5f6a7b8c9d0e1"),
  "name": "Mayank",
  "email": "mayank@example.com",
  "tags": ["developer", "student"],
  "address": {
    "city": "Nagpur",
    "state": "MH"
  }
}

-- Atomic: both inserts succeed or neither does
BEGIN;
  INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
  INSERT INTO accounts (user_id, balance) VALUES (2, -1000);
COMMIT;

-- Use SQL when:
-- ✅ Structured data with clear relationships
-- ✅ ACID compliance needed (banking, inventory)
-- ✅ Complex queries with JOINs
-- ✅ Data integrity is critical

-- Use NoSQL when:
-- ✅ Rapidly changing schema
-- ✅ Massive horizontal scale needed
-- ✅ High write throughput (logs, IoT)
-- ✅ Simple key-value lookups (caching, sessions)

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE,
  grade INTEGER CHECK (grade BETWEEN 1 AND 12),
  enrolled BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE students ADD COLUMN phone VARCHAR(15);

DROP TABLE IF EXISTS students;

-- Single row
INSERT INTO students (first_name, last_name, email, grade)
VALUES ('Mayank', 'Basena', 'mayank@example.com', 11);

-- Multiple rows
INSERT INTO students (first_name, last_name, email, grade) VALUES
  ('Aarav', 'Sharma', 'aarav@example.com', 10),
  ('Priya', 'Patil', 'priya@example.com', 12),
  ('Rohan', 'Desai', 'rohan@example.com', 11);

-- Insert with subquery
INSERT INTO archived_students
SELECT * FROM students WHERE enrolled = FALSE;

-- Basic select
SELECT first_name, last_name FROM students;

-- All columns
SELECT * FROM students;

-- With aliases
SELECT first_name AS "First Name",
       last_name AS "Last Name"
FROM students;

-- Distinct values
SELECT DISTINCT grade FROM students ORDER BY grade;

-- Limit and offset (pagination)
SELECT * FROM students
ORDER BY id
LIMIT 10 OFFSET 20;

-- Comparison operators
SELECT * FROM students WHERE grade = 11;
SELECT * FROM students WHERE grade >= 10;
SELECT * FROM students WHERE first_name != 'Mayank';

-- Logical operators
SELECT * FROM students
WHERE grade >= 10 AND enrolled = TRUE;

SELECT * FROM students
WHERE grade = 10 OR grade = 11;

-- IN / NOT IN
SELECT * FROM students
WHERE grade IN (10, 11, 12);

-- LIKE pattern matching
SELECT * FROM students WHERE first_name LIKE 'A%';  -- starts with A
SELECT * FROM students WHERE email LIKE '%@gmail.com';

-- NULL check
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;

-- Update single row
UPDATE students
SET grade = 12
WHERE email = 'mayank@example.com';

-- Update multiple rows
UPDATE students
SET enrolled = FALSE
WHERE grade < 10;

-- Update with expression
UPDATE students
SET grade = grade + 1
WHERE enrolled = TRUE AND grade < 12;

-- Delete specific rows
DELETE FROM students WHERE id = 5;

-- Delete with condition
DELETE FROM students WHERE enrolled = FALSE AND grade < 5;

-- Delete all (keeps schema)
DELETE FROM students;

-- TRUNCATE (faster, resets sequences)
TRUNCATE TABLE students;

-- Sort ascending (default)
SELECT * FROM students ORDER BY last_name ASC;

-- Sort descending
SELECT * FROM students ORDER BY created_at DESC;

-- Multiple sort keys
SELECT * FROM students
ORDER BY grade DESC, last_name ASC;

-- Top 5
SELECT * FROM students
ORDER BY grade DESC
LIMIT 5;

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  country VARCHAR(50)
);

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE,
  price DECIMAL(10,2) CHECK (price >= 0)
);

SELECT books.title, authors.name, authors.country
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- Alias shorthand
SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.country = 'India'
ORDER BY b.title;

-- All authors, even those with no books
SELECT a.name, COUNT(b.id) AS book_count
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
GROUP BY a.id, a.name
ORDER BY book_count DESC;

-- Authors with zero books
SELECT a.name
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
WHERE b.id IS NULL;

-- All books, even with unknown authors (orphan data)
SELECT b.title, a.name
FROM books b
RIGHT JOIN authors a ON b.author_id = a.id;

-- Equivalent using LEFT JOIN (preferred for readability)
SELECT b.title, a.name
FROM authors a
LEFT JOIN books b ON a.id = b.author_id;

SELECT a.name, b.title
FROM authors a
FULL OUTER JOIN books b ON a.id = b.author_id;

-- Find orphan authors AND orphan books
SELECT
  COALESCE(a.name, 'Unknown') AS author,
  COALESCE(b.title, 'No book') AS book
FROM authors a
FULL OUTER JOIN books b ON a.id = b.author_id
WHERE a.id IS NULL OR b.id IS NULL;

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Junction table
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
  course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  grade CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

-- Query many-to-many
SELECT s.name, c.title, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.title = 'Databases'
ORDER BY s.name;

-- Count courses per student
SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name;

-- Employees and their managers
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# Install PostgreSQL
# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib

# macOS
brew install postgresql@16

# Start service
sudo systemctl start postgresql

# Connect via psql
sudo -u postgres psql

\l                    -- list databases
\c database_name      -- connect to database
\dt                   -- list tables
\d table_name         -- describe table
\du                   -- list users
\q                    -- quit psql
\echo 'Hello'         -- print output
\timing               -- toggle query timing

CREATE TABLE type_examples (
  -- Numeric
  small_num SMALLINT,
  normal_int INTEGER,
  big_num BIGINT,
  precise DECIMAL(10,2),
  float_val REAL,
  double_val DOUBLE PRECISION,

  -- Text
  short VARCHAR(255),
  long_text TEXT,

  -- Date/Time
  today DATE,
  now TIMESTAMP,
  tz_aware TIMESTAMPTZ,

  -- Boolean
  is_active BOOLEAN DEFAULT TRUE,

  -- Binary
  data BYTEA,

  -- Arrays (PG specific)
  tags TEXT[] DEFAULT '{}',

  -- UUID
  id UUID DEFAULT gen_random_uuid()
);

-- SERIAL (auto-increment shorthand)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- IDENTITY (modern, SQL standard)
CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total DECIMAL(10,2)
);

-- Get last inserted ID
INSERT INTO users (name) VALUES ('Mayank') RETURNING id;

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (payload) VALUES
  ('{"type": "click", "target": "button", "meta": {"x": 120, "y": 340}}'),
  ('{"type": "scroll", "depth": 85, "meta": {"direction": "down"}}');

-- Query JSONB
SELECT payload->>'type' AS event_type FROM events;
SELECT payload->'meta'->>'x' AS x_coord FROM events;
SELECT payload @> '{"type": "click"}' AS is_click FROM events;

-- Index JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Aggregate
SELECT
  payload->>'type' AS event_type,
  COUNT(*) AS occurrences
FROM events
GROUP BY event_type;

-- Recursive CTE: generate numbers 1-10
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- CTE for readable complex queries
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE last_login > NOW() - INTERVAL '30 days'
),
user_stats AS (
  SELECT
    au.id,
    au.name,
    COUNT(p.id) AS post_count
  FROM active_users au
  LEFT JOIN posts p ON au.id = p.author_id
  GROUP BY au.id, au.name
)
SELECT * FROM user_stats
WHERE post_count > 5
ORDER BY post_count DESC;

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  region VARCHAR(20),
  amount DECIMAL(10,2),
  sale_date DATE
);

-- Row number within each region
SELECT
  region,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

-- Running total
SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- Moving average (3-day window)
SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

-- Lag/Lead for comparisons
SELECT
  region,
  amount,
  LAG(amount) OVER (PARTITION BY region ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY region ORDER BY sale_date) AS diff
FROM sales;

-- Percent of total
SELECT
  region,
  amount,
  ROUND(amount / SUM(amount) OVER () * 100, 1) AS pct_of_total
FROM sales;

// Insert a document
db.users.insertOne({
  name: "Mayank Basena",
  email: "mayank@example.com",
  age: 15,
  skills: ["JavaScript", "Python", "SQL"],
  address: {
    city: "Nagpur",
    state: "Maharashtra",
    pin: "440001"
  },
  joinedAt: new Date("2024-01-15")
});

// Insert multiple
db.users.insertMany([
  { name: "Aarav", email: "aarav@example.com", age: 16, skills: ["Go", "Rust"] },
  { name: "Priya", email: "priya@example.com", age: 17, skills: ["Python", "React"] }
]);

// READ
db.users.find({ age: { $gte: 15 } });            // filter
db.users.findOne({ email: "mayank@example.com" }); // single doc
db.users.find({}).sort({ age: -1 }).limit(5);     // sorted, limited
db.users.find({}, { name: 1, email: 1, _id: 0 }); // projection

// UPDATE
db.users.updateOne(
  { email: "mayank@example.com" },
  { $set: { age: 16 }, $push: { skills: "Rust" } }
);

db.users.updateMany(
  { age: { $lt: 16 } },
  { $inc: { age: 1 } }
);

// DELETE
db.users.deleteOne({ email: "aarav@example.com" });
db.users.deleteMany({ age: { $lt: 15 } });

// Comparison
db.users.find({ age: { $gt: 15, $lte: 18 } });
db.users.find({ name: { $in: ["Mayank", "Priya"] } });

// Logical
db.users.find({ $and: [{ age: { $gte: 15 } }, { skills: "Python" }] });
db.users.find({ $or: [{ age: 15 }, { age: 17 }] });

// Array
db.users.find({ skills: { $all: ["Python", "JavaScript"] } });
db.users.find({ skills: { $size: 3 } });
db.users.find({ "address.city": "Nagpur" });

// Regex
db.users.find({ name: { $regex: /^May/ } });

// Group by and compute stats
db.users.aggregate([
  { $unwind: "$skills" },
  { $group: {
      _id: "$skills",
      count: { $sum: 1 },
      avgAge: { $avg: "$age" }
    }
  },
  { $sort: { count: -1 } },
  { $limit: 5 }
]);

// $match → $group → $project pipeline
db.sales.aggregate([
  { $match: { date: { $gte: new Date("2024-01-01") } } },
  { $group: {
      _id: "$region",
      totalRevenue: { $sum: "$amount" },
      avgOrder: { $avg: "$amount" },
      orderCount: { $sum: 1 }
    }
  },
  { $project: {
      region: "$_id",
      totalRevenue: 1,
      avgOrder: { $round: ["$avgOrder", 2] },
      orderCount: 1,
      _id: 0
    }
  }
]);

// Single field
db.users.createIndex({ email: 1 }, { unique: true });

// Compound index
db.users.createIndex({ age: 1, name: 1 });

// Multikey (arrays)
db.users.createIndex({ skills: 1 });

// Text search index
db.users.createIndex({ name: "text", email: "text" });
db.users.find({ $text: { $search: "Mayank" } });

// TTL index (auto-delete after time)
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 3600 }
);

// List all indexes
db.users.getIndexes();

-- Create index
CREATE INDEX idx_users_email ON users (email);

-- Composite index (order matters)
CREATE INDEX idx_posts_author_date ON posts (author_id, created_at DESC);

-- Partial index (only index relevant rows)
CREATE INDEX idx_active_users ON users (email) WHERE is_active = TRUE;

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- Use case: session lookup, cache keys
-- SELECT * FROM sessions WHERE token = 'abc123';

-- Basic plan
EXPLAIN SELECT * FROM users WHERE email = 'mayank@example.com';

-- With actual execution stats (runs the query)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
WHERE u.is_active = TRUE
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 5;

-- Key things to look for:
-- Seq Scan      → missing index
-- Nested Loop   → might need a join index
-- Sort Method   → external merge = needs more memory
-- Rows Removed  → filter is too broad

-- ✅ Index columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_date ON orders (order_date DESC);

-- ✅ Composite index: leftmost prefix rule
-- This index covers queries on (a), (a,b), and (a,b,c)
CREATE INDEX idx_composite ON table (a, b, c);

-- ❌ Don't index every column
-- Indexes slow down writes (INSERT/UPDATE/DELETE)

-- ✅ Use covering indexes (INCLUDE)
CREATE INDEX idx_covering ON posts (author_id) INCLUDE (title, created_at);

-- ✅ Check index usage
SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- ❌ N+1: 1 query for users + N queries for their posts
-- SELECT * FROM users WHERE is_active = TRUE;
-- -- then for each user:
-- SELECT * FROM posts WHERE author_id = $1;

-- ✅ Fix 1: JOIN
SELECT u.name, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.author_id
WHERE u.is_active = TRUE;

-- ✅ Fix 2: Subquery
SELECT u.name,
  (SELECT json_agg(p.title) FROM posts p WHERE p.author_id = u.id) AS titles
FROM users u
WHERE u.is_active = TRUE;

-- ✅ Fix 3: Batch IDs (in ORMs)
-- users = User.objects.filter(is_active=True)
-- posts = Post.objects.filter(author_id__in=[u.id for u in users])

-- 1. SELECT only columns you need
SELECT id, name, email FROM users;        -- ✅
SELECT * FROM users;                       -- ❌

-- 2. Use LIMIT for large result sets
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- 3. Avoid functions on indexed columns (breaks index)
SELECT * FROM users WHERE LOWER(email) = 'mayank@example.com'; -- ❌
-- Fix: functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 4. Use EXISTS instead of IN for subqueries
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM posts p WHERE p.author_id = u.id
);

-- 5. Analyze tables regularly
ANALYZE users;

-- ❌ Violates 1NF (comma-separated tags)
CREATE TABLE bad_posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT  -- "python,sql,databases"
);

-- ✅ 1NF: junction table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id),
  tag VARCHAR(50) NOT NULL,
  PRIMARY KEY (post_id, tag)
);

-- ❌ Violates 2NF
CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  product_name TEXT,     -- depends on product_id only, not (order_id, product_id)
  quantity INTEGER,
  price DECIMAL(10,2),
  PRIMARY KEY (order_id, product_id)
);

-- ✅ 2NF: split into separate tables
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL(10,2)
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- ❌ Violates 3NF
CREATE TABLE users_bad (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department_id INTEGER,
  department_name TEXT,   -- depends on department_id, not on id directly
  department_floor TEXT
);

-- ✅ 3NF: separate department table
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  floor INTEGER
);

CREATE TABLE users_3nf (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id)
);

-- Example: student-course-instructor
-- Dependency: course → instructor (instructor determines course)
-- But course is not a candidate key → violates BCNF

-- ✅ BCNF: split
CREATE TABLE course_instructors (
  course_id SERIAL PRIMARY KEY,
  course_name TEXT NOT NULL,
  instructor_id INTEGER REFERENCES instructors(id)
);

CREATE TABLE student_enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES course_instructors(course_id),
  grade TEXT,
  PRIMARY KEY (student_id, course_id)
);

-- When to denormalize:
-- ✅ Read-heavy workloads (analytics dashboards)
-- ✅ Avoid expensive JOINs at scale
-- ✅ Caching computed values

-- Example: cached count
CREATE TABLE posts_with_counts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER REFERENCES users(id),
  comment_count INTEGER DEFAULT 0,   -- denormalized
  like_count INTEGER DEFAULT 0       -- denormalized
);

-- Keep in sync with triggers or application logic
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts_with_counts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts_with_counts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

-- 1NF:  Atomic values, no repeating groups
-- 2NF:  No partial dependencies (on composite keys)
-- 3NF:  No transitive dependencies
-- BCNF: Every determinant is a candidate key

-- Practical approach:
-- Start normalized (3NF/BCNF)
-- Measure performance
-- Denormalize specific bottlenecks
-- Keep denormalized data in sync (triggers, app logic)

-- Blog domain ER model:

-- USERS ──< POSTS ──< COMMENTS
--   │                   │
--   └──< FOLLOWERS      └──< LIKES

-- Entities: User, Post, Comment, Like
-- Relationships:
--   User 1:N Post       (one user has many posts)
--   Post 1:N Comment    (one post has many comments)
--   User N:M User       (follows — junction table)
--   User N:M Post       (likes — junction table)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(30) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  bio TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  body TEXT,
  status VARCHAR(10) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- Indexes for common queries
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_slug ON posts (slug);
CREATE INDEX idx_posts_status_date ON posts (status, published_at DESC);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_post_tags_tag ON post_tags (tag_id);

-- Manual: versioned SQL files
-- 001_create_users.sql
-- 002_create_posts.sql
-- 003_add_avatar_column.sql

-- Using Alembic (SQLAlchemy)
# Initialize
alembic init alembic

# Generate migration
alembic revision --autogenerate -m "add avatar to users"

# Apply
alembic upgrade head

# Rollback
alembic downgrade -1

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  tags      Tag[]
  createdAt DateTime @default(now())
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

// Usage in application code
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: { email: 'mayank@example.com', name: 'Mayank' }
});

// Query with relations
const posts = await prisma.post.findMany({
  where: { published: true },
  include: { author: true, tags: true },
  orderBy: { createdAt: 'desc' },
  take: 10
});

// Update
await prisma.post.update({
  where: { id: 1 },
  data: { published: true }
});

// Generate migration
// npx prisma migrate dev --name add_comments

from sqlalchemy import create_engine, Column, Integer, String, Text, Boolean, ForeignKey, DateTime
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from datetime import datetime

engine = create_engine('postgresql://user:pass@localhost/mydb')
Session = sessionmaker(bind=engine)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(100), nullable=False)
    posts = relationship('Post', back_populates='author', cascade='all, delete-orphan')

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    body = Column(Text)
    published = Column(Boolean, default=False)
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    author = relationship('User', back_populates='posts')
    created_at = Column(DateTime, default=datetime.utcnow)

# Create tables
Base.metadata.create_all(engine)

# CRUD
session = Session()

# Create
user = User(email='mayank@example.com', name='Mayank')
session.add(user)
session.commit()

# Read
posts = session.query(Post).filter(Post.published == True).order_by(Post.created_at.desc()).limit(10).all()

# Update
post = session.query(Post).get(1)
post.published = True
session.commit()

# Delete
session.delete(post)
session.commit()

# Alembic migration
# alembic revision --autogenerate -m "add users table"
# alembic upgrade head

-- 1. Name tables and columns clearly (snake_case for SQL)
-- 2. Always have a PRIMARY KEY
-- 3. Use foreign keys with ON DELETE behavior
-- 4. Add NOT NULL where data is required
-- 5. Use CHECK constraints for validation
-- 6. Create indexes for frequent queries
-- 7. Use created_at/updated_at timestamps
-- 8. Version your migrations
-- 9. Design for your query patterns, not just data shape
-- 10. Start simple, optimize when needed