🔷 관계형 데이터베이스 (RDBMS)

🔶 NoSQL 데이터베이스

🎯 데이터베이스 연동 프로세스

💻 언어별 데이터베이스 연동 방법

import mysql.connector

# 데이터베이스 연결
connection = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

# 커서 생성
cursor = connection.cursor()

# 쿼리 실행
cursor.execute("SELECT * FROM users WHERE age > 20")

# 결과 가져오기
results = cursor.fetchall()
for row in results:
    print(row)

# 연결 종료
cursor.close()
connection.close()

import java.sql.*;

public class DatabaseExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        
        try {
            // 드라이버 로드
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 연결 생성
            Connection conn = DriverManager.getConnection(url, user, password);
            
            // Statement 생성
            Statement stmt = conn.createStatement();
            
            // 쿼리 실행
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            // 결과 처리
            while(rs.next()) {
                System.out.println(rs.getString("name"));
            }
            
            // 리소스 해제
            rs.close();
            stmt.close();
            conn.close();
            
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
}

const mysql = require('mysql2');

// 연결 풀 생성 (여러 연결을 효율적으로 관리)
const pool = mysql.createPool({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database',
    waitForConnections: true,
    connectionLimit: 10
});

// Promise 기반 사용
const promisePool = pool.promise();

async function getUsers() {
    try {
        const [rows] = await promisePool.query('SELECT * FROM users');
        console.log(rows);
    } catch(error) {
        console.error(error);
    }
}

getUsers();

require 'pg'

# 연결 생성
conn = PG.connect(
    host: 'localhost',
    dbname: 'your_database',
    user: 'your_username',
    password: 'your_password'
)

# 쿼리 실행
result = conn.exec('SELECT * FROM users')

# 결과 처리
result.each do |row|
    puts row['name']
end

# 연결 종료
conn.close

📝 기본 SQL 명령어

-- 모든 컬럼 조회
SELECT * FROM users;

-- 특정 컬럼만 조회
SELECT name, email FROM users;

-- 조건을 추가한 조회
SELECT * FROM users WHERE age >= 20;

-- 정렬해서 조회
SELECT * FROM users ORDER BY created_at DESC;

-- 개수 제한
SELECT * FROM users LIMIT 10;

-- 여러 조건 조합
SELECT name, email 
FROM users 
WHERE age >= 20 AND status = 'active'
ORDER BY name ASC
LIMIT 50;

-- 단일 행 삽입
INSERT INTO users (name, email, age) 
VALUES ('홍길동', 'hong@example.com', 25);

-- 여러 행 동시 삽입
INSERT INTO users (name, email, age) 
VALUES 
    ('김철수', 'kim@example.com', 30),
    ('이영희', 'lee@example.com', 28),
    ('박민수', 'park@example.com', 35);

-- 특정 사용자의 이메일 변경
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- 여러 컬럼 동시 수정
UPDATE users 
SET age = 26, status = 'active' 
WHERE name = '홍길동';

-- 조건에 맞는 여러 행 수정
UPDATE users 
SET status = 'inactive' 
WHERE last_login < '2023-01-01';

-- 특정 사용자 삭제
DELETE FROM users WHERE id = 1;

-- 조건에 맞는 여러 행 삭제
DELETE FROM users WHERE status = 'deleted';

-- 모든 데이터 삭제 (위험!)
DELETE FROM users;

🔗 JOIN - 테이블 결합

-- INNER JOIN 예시
-- 사용자와 그들의 주문 정보를 함께 조회
SELECT users.name, orders.order_date, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN 예시
-- 모든 사용자와 주문 정보 조회 (주문이 없는 사용자도 포함)
SELECT users.name, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 여러 테이블 JOIN
SELECT 
    users.name,
    orders.order_date,
    products.product_name,
    order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

📊 집계 함수

-- COUNT: 개수 세기
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- SUM: 합계
SELECT SUM(total_amount) FROM orders;

-- AVG: 평균
SELECT AVG(age) FROM users;

-- MAX, MIN: 최대값, 최소값
SELECT MAX(total_amount), MIN(total_amount) FROM orders;

-- GROUP BY: 그룹별 집계
SELECT status, COUNT(*) as count
FROM users
GROUP BY status;

-- HAVING: 그룹 조건
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
HAVING count > 10;

🤔 왜 ORM을 사용할까?

⚖️ ORM의 장단점

🌟 주요 ORM 프레임워크

📋 모델 정의하기

from django.db import models
from django.contrib.auth.models import User

class Category(models.Model):
    """카테고리 모델"""
    name = models.CharField(max_length=100, unique=True)
    description = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        verbose_name_plural = "Categories"
        ordering = ['name']
    
    def __str__(self):
        return self.name

class Post(models.Model):
    """게시글 모델"""
    STATUS_CHOICES = [
        ('draft', '임시저장'),
        ('published', '발행됨'),
        ('archived', '보관됨'),
    ]
    
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, related_name='posts')
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='draft')
    views = models.IntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['status', '-created_at']),
        ]
    
    def __str__(self):
        return self.title

class Comment(models.Model):
    """댓글 모델"""
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments')
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    content = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        ordering = ['created_at']
    
    def __str__(self):
        return f'{self.author.username}: {self.content[:50]}'

🔄 마이그레이션

# 마이그레이션 파일 생성
python manage.py makemigrations

# 마이그레이션 실행 (테이블 생성)
python manage.py migrate

✏️ 데이터 생성 (Create)

# 방법 1: 객체 생성 후 저장
post = Post(
    title='Django ORM 완벽 가이드',
    content='ORM을 사용하면 정말 편리해요!',
    author=user,
    category=category,
    status='published'
)
post.save()

# 방법 2: create() 메서드 사용 (한 번에 생성하고 저장)
post = Post.objects.create(
    title='Python 웹 개발',
    content='Django로 웹사이트 만들기',
    author=user,
    category=category
)

# 여러 개 한 번에 생성
Post.objects.bulk_create([
    Post(title='첫 번째 글', content='내용1', author=user),
    Post(title='두 번째 글', content='내용2', author=user),
    Post(title='세 번째 글', content='내용3', author=user),
])

🔍 데이터 조회 (Read)

# 모든 게시글 가져오기
all_posts = Post.objects.all()

# 특정 조건으로 필터링
published_posts = Post.objects.filter(status='published')

# 여러 조건 조합
recent_posts = Post.objects.filter(
    status='published',
    created_at__gte='2024-01-01'
).order_by('-created_at')

# 단일 객체 가져오기
post = Post.objects.get(id=1)

# 존재하지 않을 수 있는 경우
try:
    post = Post.objects.get(id=999)
except Post.DoesNotExist:
    post = None

# 또는 get_or_404 사용 (Django shortcuts)
from django.shortcuts import get_object_or_404
post = get_object_or_404(Post, id=1)

# 첫 번째/마지막 객체
first_post = Post.objects.first()
last_post = Post.objects.last()

# 개수 제한
latest_5_posts = Post.objects.all()[:5]

# 특정 필드만 가져오기 (성능 최적화)
posts = Post.objects.values('id', 'title', 'created_at')

# 중복 제거
categories = Post.objects.values('category').distinct()

# 개수 세기
total_posts = Post.objects.count()
published_count = Post.objects.filter(status='published').count()

# 존재 여부 확인
has_posts = Post.objects.filter(author=user).exists()

🔎 고급 쿼리

# Q 객체로 복잡한 조건 만들기
from django.db.models import Q

# OR 조건
posts = Post.objects.filter(
    Q(status='published') | Q(status='archived')
)

# AND와 OR 조합
posts = Post.objects.filter(
    Q(status='published') & (Q(title__contains='Django') | Q(content__contains='Django'))
)

# NOT 조건
posts = Post.objects.filter(~Q(status='draft'))

# 관계를 통한 조회 (JOIN)
# 특정 카테고리의 게시글
posts = Post.objects.filter(category__name='Python')

# 특정 작성자의 게시글
posts = Post.objects.filter(author__username='john')

# 댓글이 있는 게시글
posts = Post.objects.filter(comments__isnull=False).distinct()

# 역참조 (related_name 사용)
user_posts = user.posts.all()
category_posts = category.posts.filter(status='published')

# 집계 함수
from django.db.models import Count, Avg, Sum, Max, Min

# 각 카테고리별 게시글 수
categories = Category.objects.annotate(post_count=Count('posts'))

# 평균 조회수
avg_views = Post.objects.aggregate(Avg('views'))

# 작성자별 게시글 수
authors = User.objects.annotate(
    post_count=Count('posts'),
    total_views=Sum('posts__views')
)

# 조건부 집계
from django.db.models import Case, When, IntegerField

posts = Post.objects.annotate(
    comment_count=Count('comments'),
    is_popular=Case(
        When(views__gte=1000, then=1),
        default=0,
        output_field=IntegerField()
    )
)

✏️ 데이터 수정 (Update)

# 방법 1: 객체를 가져와서 수정
post = Post.objects.get(id=1)
post.title = '수정된 제목'
post.content = '수정된 내용'
post.save()

# 방법 2: update() 메서드 (더 효율적)
Post.objects.filter(id=1).update(
    title='수정된 제목',
    content='수정된 내용'
)

# 여러 객체 한 번에 수정
Post.objects.filter(status='draft').update(status='published')

# 조회수 증가
post = Post.objects.get(id=1)
post.views += 1
post.save()

# 또는 F 객체 사용 (경쟁 조건 방지)
from django.db.models import F
Post.objects.filter(id=1).update(views=F('views') + 1)

# 특정 필드만 저장 (성능 최적화)
post.title = '새 제목'
post.save(update_fields=['title'])

🗑️ 데이터 삭제 (Delete)

# 단일 객체 삭제
post = Post.objects.get(id=1)
post.delete()

# 조건에 맞는 여러 객체 삭제
Post.objects.filter(status='draft', created_at__lt='2023-01-01').delete()

# 모든 객체 삭제 (주의!)
Post.objects.all().delete()

🎯 성능 최적화 팁

# select_related: ForeignKey, OneToOneField 관계 (JOIN 사용)
posts = Post.objects.select_related('author', 'category').all()

# prefetch_related: ManyToManyField, 역참조 관계 (별도 쿼리)
posts = Post.objects.prefetch_related('comments').all()

# 둘 다 사용
posts = Post.objects.select_related('author', 'category').prefetch_related('comments')

# only: 특정 필드만 가져오기
posts = Post.objects.only('id', 'title', 'created_at')

# defer: 특정 필드 제외하고 가져오기
posts = Post.objects.defer('content')

# iterator: 대용량 데이터 처리 (메모리 절약)
for post in Post.objects.iterator(chunk_size=1000):
    # 처리 로직
    pass

# 쿼리 확인 (디버깅)
print(Post.objects.filter(status='published').query)

🏗️ 기본 설정

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

# 데이터베이스 엔진 생성
engine = create_engine('mysql+pymysql://user:password@localhost/dbname', echo=True)

# Base 클래스 생성
Base = declarative_base()

# 세션 팩토리 생성
Session = sessionmaker(bind=engine)

📋 모델 정의

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 관계 정의
    posts = relationship('Post', back_populates='author', cascade='all, delete-orphan')
    
    def __repr__(self):
        return f"<User(username='{self.username}')>"

class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    content = Column(Text)
    author_id = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 관계 정의
    author = relationship('User', back_populates='posts')
    comments = relationship('Comment', back_populates='post', cascade='all, delete-orphan')
    
    def __repr__(self):
        return f"<Post(title='{self.title}')>"

class Comment(Base):
    __tablename__ = 'comments'
    
    id = Column(Integer, primary_key=True)
    content = Column(Text, nullable=False)
    post_id = Column(Integer, ForeignKey('posts.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 관계 정의
    post = relationship('Post', back_populates='comments')
    user = relationship('User')
    
    def __repr__(self):
        return f"<Comment(content='{self.content[:20]}...')>"

# 테이블 생성
Base.metadata.create_all(engine)

✏️ CRUD 작업

# 세션 생성
session = Session()

# Create - 데이터 생성
user = User(username='john_doe', email='john@example.com')
session.add(user)
session.commit()

# 여러 개 추가
users = [
    User(username='alice', email='alice@example.com'),
    User(username='bob', email='bob@example.com')
]
session.add_all(users)
session.commit()

# Read - 데이터 조회
# 모든 사용자
all_users = session.query(User).all()

# 특정 조건
user = session.query(User).filter_by(username='john_doe').first()

# 복잡한 조건
from sqlalchemy import and_, or_

users = session.query(User).filter(
    and_(
        User.username.like('%john%'),
        User.created_at > '2024-01-01'
    )
).all()

# 정렬
users = session.query(User).order_by(User.created_at.desc()).all()

# 제한
users = session.query(User).limit(10).all()

# 개수
user_count = session.query(User).count()

# Update - 데이터 수정
user = session.query(User).filter_by(username='john_doe').first()
user.email = 'newemail@example.com'
session.commit()

# 또는 update() 사용
session.query(User).filter_by(username='john_doe').update({
    'email': 'newemail@example.com'
})
session.commit()

# Delete - 데이터 삭제
user = session.query(User).filter_by(username='john_doe').first()
session.delete(user)
session.commit()

# 또는 delete() 사용
session.query(User).filter_by(username='john_doe').delete()
session.commit()

# 세션 종료
session.close()

🔗 관계 활용

# 게시글과 작성자 함께 조회 (JOIN)
from sqlalchemy.orm import joinedload

posts = session.query(Post).options(
    joinedload(Post.author)
).all()

# 사용자의 모든 게시글
user = session.query(User).filter_by(username='john_doe').first()
user_posts = user.posts

# 역참조
post = session.query(Post).first()
author = post.author

# 중첩 관계
posts_with_comments = session.query(Post).options(
    joinedload(Post.author),
    joinedload(Post.comments)
).all()

📊 고급 쿼리

from sqlalchemy import func

# 집계
# 사용자별 게시글 수
results = session.query(
    User.username,
    func.count(Post.id).label('post_count')
).join(Post).group_by(User.username).all()

# 평균, 최대, 최소
avg_posts = session.query(func.avg(func.count(Post.id))).group_by(Post.author_id).scalar()

# 서브쿼리
from sqlalchemy import select

subq = select([func.count(Post.id)]).where(Post.author_id == User.id).label('post_count')
users_with_count = session.query(User, subq).all()

# 윈도우 함수
from sqlalchemy import over

ranked_posts = session.query(
    Post,
    func.row_number().over(
        partition_by=Post.author_id,
        order_by=Post.created_at.desc()
    ).label('rank')
).all()

🔷 Sequelize

const { Sequelize, DataTypes } = require('sequelize');

// 데이터베이스 연결
const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql',
    logging: false
});

// 모델 정의
const User = sequelize.define('User', {
    username: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate: {
            isEmail: true
        }
    },
    age: {
        type: DataTypes.INTEGER,
        validate: {
            min: 0,
            max: 150
        }
    }
}, {
    timestamps: true,
    tableName: 'users'
});

const Post = sequelize.define('Post', {
    title: {
        type: DataTypes.STRING,
        allowNull: false
    },
    content: {
        type: DataTypes.TEXT
    },
    status: {
        type: DataTypes.ENUM('draft', 'published', 'archived'),
        defaultValue: 'draft'
    }
});

// 관계 정의
User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

// 동기화
await sequelize.sync();

// CRUD 작업
// Create
const user = await User.create({
    username: 'john_doe',
    email: 'john@example.com',
    age: 25
});

// Read
const users = await User.findAll({
    where: {
        age: {
            [Sequelize.Op.gte]: 20
        }
    },
    order: [['createdAt', 'DESC']],
    limit: 10
});

const user = await User.findOne({
    where: { username: 'john_doe' }
});

// 관계 포함
const userWithPosts = await User.findOne({
    where: { username: 'john_doe' },
    include: [{
        model: Post,
        as: 'posts'
    }]
});

// Update
await User.update(
    { email: 'newemail@example.com' },
    { where: { username: 'john_doe' } }
);

// Delete
await User.destroy({
    where: { username: 'john_doe' }
});

// 트랜잭션
await sequelize.transaction(async (t) => {
    const user = await User.create({
        username: 'alice',
        email: 'alice@example.com'
    }, { transaction: t });
    
    await Post.create({
        title: 'First Post',
        content: 'Hello World',
        authorId: user.id
    }, { transaction: t });
});

🔶 Prisma

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

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  username  String   @unique
  email     String   @unique
  age       Int?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]
  
  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?  @db.Text
  status    Status   @default(DRAFT)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  comments  Comment[]
  
  @@index([authorId])
  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String   @db.Text
  postId    Int
  post      Post     @relation(fields: [postId], references: [id])
  userId    Int
  createdAt DateTime @default(now())
  
  @@map("comments")
}

enum Status {
  DRAFT
  PUBLISHED
  ARCHIVED
}

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
    data: {
        username: 'john_doe',
        email: 'john@example.com',
        age: 25
    }
});

// 관계와 함께 생성
const userWithPost = await prisma.user.create({
    data: {
        username: 'alice',
        email: 'alice@example.com',
        posts: {
            create: [
                { title: 'First Post', content: 'Hello World' },
                { title: 'Second Post', content: 'Another post' }
            ]
        }
    },
    include: {
        posts: true
    }
});

// Read
const users = await prisma.user.findMany({
    where: {
        age: {
            gte: 20
        }
    },
    orderBy: {
        createdAt: 'desc'
    },
    take: 10
});

const user = await prisma.user.findUnique({
    where: { username: 'john_doe' }
});

// 관계 포함
const userWithPosts = await prisma.user.findUnique({
    where: { username: 'john_doe' },
    include: {
        posts: {
            where: {
                status: 'PUBLISHED'
            },
            orderBy: {
                createdAt: 'desc'
            }
        }
    }
});

// Update
const updatedUser = await prisma.user.update({
    where: { username: 'john_doe' },
    data: { email: 'newemail@example.com' }
});

// Delete
await prisma.user.delete({
    where: { username: 'john_doe' }
});

// 집계
const userCount = await prisma.user.count();

const avgAge = await prisma.user.aggregate({
    _avg: {
        age: true
    }
});

// 그룹화
const postsByAuthor = await prisma.post.groupBy({
    by: ['authorId'],
    _count: {
        id: true
    },
    having: {
        authorId: {
            gt: 0
        }
    }
});

// 트랜잭션
await prisma.$transaction(async (tx) => {
    const user = await tx.user.create({
        data: {
            username: 'bob',
            email: 'bob@example.com'
        }
    });
    
    await tx.post.create({
        data: {
            title: 'Bob\'s Post',
            content: 'Content here',
            authorId: user.id
        }
    });
});

// Raw SQL (필요한 경우)
const result = await prisma.$queryRaw`
    SELECT * FROM users WHERE age > ${20}
`;

// 연결 종료
await prisma.$disconnect();

🐌 N+1 문제 해결

# Django
posts = Post.objects.all()  # 1번의 쿼리
for post in posts:
    print(post.author.name)  # N번의 추가 쿼리!

# 총 1 + N번의 쿼리 실행

# Django - select_related 사용
posts = Post.objects.select_related('author').all()  # 1번의 JOIN 쿼리
for post in posts:
    print(post.author.name)  # 추가 쿼리 없음!

# Sequelize - include 사용
const posts = await Post.findAll({
    include: [{ model: User, as: 'author' }]
});

# Prisma - include 사용
const posts = await prisma.post.findMany({
    include: { author: true }
});

📊 적절한 인덱스 사용

# Django
class Post(models.Model):
    title = models.CharField(max_length=200)
    status = models.CharField(max_length=20)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['status']),
            models.Index(fields=['-created_at']),
            models.Index(fields=['status', '-created_at']),  # 복합 인덱스
        ]

# SQLAlchemy
from sqlalchemy import Index

class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    status = Column(String(20), index=True)
    created_at = Column(DateTime, index=True)

# 복합 인덱스
Index('idx_status_created', Post.status, Post.created_at.desc())

# Prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  status    String
  createdAt DateTime @default(now())
  
  @@index([status])
  @@index([createdAt(sort: Desc)])
  @@index([status, createdAt(sort: Desc)])
}

🎯 필요한 필드만 조회

# Django - only()
posts = Post.objects.only('id', 'title', 'created_at')

# Django - values()
posts = Post.objects.values('id', 'title')

# Sequelize
const posts = await Post.findAll({
    attributes: ['id', 'title', 'createdAt']
});

# Prisma
const posts = await prisma.post.findMany({
    select: {
        id: true,
        title: true,
        createdAt: true
    }
});

💾 쿼리 결과 캐싱

# Django with Redis
from django.core.cache import cache

def get_popular_posts():
    cache_key = 'popular_posts'
    posts = cache.get(cache_key)
    
    if posts is None:
        posts = Post.objects.filter(
            status='published'
        ).order_by('-views')[:10]
        cache.set(cache_key, posts, 3600)  # 1시간 캐싱
    
    return posts

# Node.js with Redis
const redis = require('redis');
const client = redis.createClient();

async function getPopularPosts() {
    const cacheKey = 'popular_posts';
    const cached = await client.get(cacheKey);
    
    if (cached) {
        return JSON.parse(cached);
    }
    
    const posts = await Post.findAll({
        where: { status: 'published' },
        order: [['views', 'DESC']],
        limit: 10
    });
    
    await client.setEx(cacheKey, 3600, JSON.stringify(posts));
    return posts;
}

📦 벌크 연산 사용

# Django
# 나쁜 예
for data in large_dataset:
    Post.objects.create(**data)  # N번의 INSERT

# 좋은 예
posts = [Post(**data) for data in large_dataset]
Post.objects.bulk_create(posts, batch_size=1000)  # 배치로 INSERT

# 업데이트도 마찬가지
Post.objects.filter(status='draft').update(status='published')

# Sequelize
await Post.bulkCreate(dataArray, {
    updateOnDuplicate: ['title', 'content']
});

# Prisma
await prisma.post.createMany({
    data: dataArray,
    skipDuplicates: true
});

🔍 쿼리 분석 및 모니터링

# Django - 쿼리 로깅
import logging
logging.basicConfig()
logging.getLogger('django.db.backends').setLevel(logging.DEBUG)

# Django Debug Toolbar 사용
# settings.py
INSTALLED_APPS = [
    ...
    'debug_toolbar',
]

# SQLAlchemy - 쿼리 출력
engine = create_engine('...', echo=True)

# Prisma - 쿼리 로깅
const prisma = new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
});

# 실행 계획 확인
# Django
from django.db import connection
print(connection.queries)

# Raw SQL로 EXPLAIN 실행
cursor = connection.cursor()
cursor.execute('EXPLAIN SELECT * FROM posts WHERE status = %s', ['published'])
print(cursor.fetchall())

💉 SQL 인젝션 방어

# Django - 절대 이렇게 하지 마!
username = request.GET.get('username')
query = f"SELECT * FROM users WHERE username = '{username}'"
User.objects.raw(query)

# 공격자가 username에 "admin' OR '1'='1" 입력하면?
# SELECT * FROM users WHERE username = 'admin' OR '1'='1'
# 모든 사용자 정보가 노출됨!

# Django - 파라미터 바인딩 사용
username = request.GET.get('username')
User.objects.raw("SELECT * FROM users WHERE username = %s", [username])

# 또는 ORM 사용 (더 안전)
User.objects.filter(username=username)

# SQLAlchemy
username = request.args.get('username')
session.query(User).filter(User.username == username).all()

# Sequelize
const username = req.query.username;
await User.findAll({
    where: { username: username }
});

# Prisma
const username = req.query.username;
await prisma.user.findMany({
    where: { username: username }
});

🔐 비밀번호 암호화

# Django - 자동으로 해시 처리
from django.contrib.auth.models import User

user = User.objects.create_user(
    username='john',
    password='secret123'  # 자동으로 해시됨
)

# 비밀번호 확인
user.check_password('secret123')  # True

# Node.js - bcrypt 사용
const bcrypt = require('bcrypt');

// 비밀번호 해시
const saltRounds = 10;
const hashedPassword = await bcrypt.hash(password, saltRounds);

// 사용자 생성
await User.create({
    username: 'john',
    password: hashedPassword
});

// 비밀번호 확인
const isValid = await bcrypt.compare(inputPassword, user.password);

🔑 접근 권한 관리

# Django - 권한 체크
from django.contrib.auth.decorators import login_required, permission_required

@login_required
def view_post(request, post_id):
    post = get_object_or_404(Post, id=post_id)
    
    # 작성자만 수정 가능
    if post.author != request.user:
        return HttpResponseForbidden()
    
    return render(request, 'post.html', {'post': post})

# 또는 Django Guardian 사용 (객체 레벨 권한)
from guardian.shortcuts import assign_perm, get_objects_for_user

# 권한 부여
assign_perm('change_post', user, post)

# 권한 확인
if user.has_perm('change_post', post):
    # 수정 허용
    pass

# Express.js - 미들웨어로 권한 체크
const checkPostOwner = async (req, res, next) => {
    const post = await Post.findByPk(req.params.id);
    
    if (!post) {
        return res.status(404).json({ error: 'Post not found' });
    }
    
    if (post.authorId !== req.user.id) {
        return res.status(403).json({ error: 'Forbidden' });
    }
    
    req.post = post;
    next();
};

app.put('/posts/:id', authenticate, checkPostOwner, async (req, res) => {
    // 수정 로직
});

🔒 민감한 정보 보호

# Django - 필드 암호화
from django_cryptography.fields import encrypt

class UserProfile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    ssn = encrypt(models.CharField(max_length=11))  # 주민번호 암호화
    credit_card = encrypt(models.CharField(max_length=16))

# 환경 변수로 민감 정보 관리
# .env 파일
DATABASE_URL=mysql://user:password@localhost/dbname
SECRET_KEY=your-secret-key-here
AWS_ACCESS_KEY=your-aws-key

# Python - python-dotenv
from dotenv import load_dotenv
import os

load_dotenv()

DATABASE_URL = os.getenv('DATABASE_URL')
SECRET_KEY = os.getenv('SECRET_KEY')

# Node.js - dotenv
require('dotenv').config();

const dbConfig = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD
};

🚨 보안 체크리스트

📁 프로젝트 구조

blog_project/
├── manage.py
├── blog/
│   ├── __init__.py
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
└── posts/
    ├── __init__.py
    ├── models.py
    ├── serializers.py
    ├── views.py
    ├── urls.py
    └── permissions.py

📋 모델 정의 (models.py)

from django.db import models
from django.contrib.auth.models import User
from django.utils.text import slugify

class Category(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(unique=True)
    description = models.TextField(blank=True)
    
    class Meta:
        verbose_name_plural = "Categories"
        ordering = ['name']
    
    def save(self, *args, **kwargs):
        if not self.slug:
            self.slug = slugify(self.name)
        super().save(*args, **kwargs)
    
    def __str__(self):
        return self.name

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True)
    slug = models.SlugField(unique=True)
    
    def save(self, *args, **kwargs):
        if not self.slug:
            self.slug = slugify(self.name)
        super().save(*args, **kwargs)
    
    def __str__(self):
        return self.name

class Post(models.Model):
    STATUS_CHOICES = [
        ('draft', 'Draft'),
        ('published', 'Published'),
        ('archived', 'Archived'),
    ]
    
    title = models.CharField(max_length=200)
    slug = models.SlugField(unique=True)
    content = models.TextField()
    excerpt = models.TextField(max_length=500, blank=True)
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, related_name='posts')
    tags = models.ManyToManyField(Tag, related_name='posts', blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='draft')
    views = models.IntegerField(default=0)
    featured_image = models.URLField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    published_at = models.DateTimeField(null=True, blank=True)
    
    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['status', '-published_at']),
            models.Index(fields=['author', '-created_at']),
        ]
    
    def save(self, *args, **kwargs):
        if not self.slug:
            self.slug = slugify(self.title)
        if not self.excerpt:
            self.excerpt = self.content[:200]
        super().save(*args, **kwargs)
    
    def __str__(self):
        return self.title

class Comment(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments')
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    content = models.TextField()
    parent = models.ForeignKey('self', on_delete=models.CASCADE, null=True, blank=True, related_name='replies')
    is_approved = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        ordering = ['created_at']
    
    def __str__(self):
        return f'{self.author.username}: {self.content[:50]}'

🔄 Serializers (serializers.py)

from rest_framework import serializers
from .models import Post, Category, Tag, Comment
from django.contrib.auth.models import User

class UserSerializer(serializers.ModelSerializer):
    class Meta:
        model = User
        fields = ['id', 'username', 'email']

class CategorySerializer(serializers.ModelSerializer):
    post_count = serializers.SerializerMethodField()
    
    class Meta:
        model = Category
        fields = ['id', 'name', 'slug', 'description', 'post_count']
    
    def get_post_count(self, obj):
        return obj.posts.filter(status='published').count()

class TagSerializer(serializers.ModelSerializer):
    class Meta:
        model = Tag
        fields = ['id', 'name', 'slug']

class CommentSerializer(serializers.ModelSerializer):
    author = UserSerializer(read_only=True)
    replies = serializers.SerializerMethodField()
    
    class Meta:
        model = Comment
        fields = ['id', 'author', 'content', 'parent', 'replies', 'created_at']
    
    def get_replies(self, obj):
        if obj.replies.exists():
            return CommentSerializer(obj.replies.all(), many=True).data
        return []

class PostListSerializer(serializers.ModelSerializer):
    author = UserSerializer(read_only=True)
    category = CategorySerializer(read_only=True)
    tags = TagSerializer(many=True, read_only=True)
    comment_count = serializers.SerializerMethodField()
    
    class Meta:
        model = Post
        fields = ['id', 'title', 'slug', 'excerpt', 'author', 'category', 
                  'tags', 'status', 'views', 'comment_count', 'created_at', 'published_at']
    
    def get_comment_count(self, obj):
        return obj.comments.filter(is_approved=True).count()

class PostDetailSerializer(serializers.ModelSerializer):
    author = UserSerializer(read_only=True)
    category = CategorySerializer(read_only=True)
    tags = TagSerializer(many=True, read_only=True)
    comments = CommentSerializer(many=True, read_only=True)
    
    class Meta:
        model = Post
        fields = ['id', 'title', 'slug', 'content', 'excerpt', 'author', 
                  'category', 'tags', 'status', 'views', 'featured_image',
                  'comments', 'created_at', 'updated_at', 'published_at']

class PostCreateUpdateSerializer(serializers.ModelSerializer):
    class Meta:
        model = Post
        fields = ['title', 'content', 'category', 'tags', 'status', 'featured_image']
    
    def create(self, validated_data):
        tags = validated_data.pop('tags', [])
        post = Post.objects.create(**validated_data)
        post.tags.set(tags)
        return post
    
    def update(self, instance, validated_data):
        tags = validated_data.pop('tags', None)
        for attr, value in validated_data.items():
            setattr(instance, attr, value)
        instance.save()
        
        if tags is not None:
            instance.tags.set(tags)
        
        return instance

🎯 Views (views.py)

from rest_framework import viewsets, filters, status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.permissions import IsAuthenticatedOrReadOnly
from django.db.models import Q, Count
from .models import Post, Category, Tag, Comment
from .serializers import (
    PostListSerializer, PostDetailSerializer, PostCreateUpdateSerializer,
    CategorySerializer, TagSerializer, CommentSerializer
)
from .permissions import IsAuthorOrReadOnly

class PostViewSet(viewsets.ModelViewSet):
    queryset = Post.objects.select_related('author', 'category').prefetch_related('tags', 'comments')
    permission_classes = [IsAuthenticatedOrReadOnly, IsAuthorOrReadOnly]
    filter_backends = [filters.SearchFilter, filters.OrderingFilter]
    search_fields = ['title', 'content']
    ordering_fields = ['created_at', 'views', 'published_at']
    
    def get_serializer_class(self):
        if self.action == 'list':
            return PostListSerializer
        elif self.action in ['create', 'update', 'partial_update']:
            return PostCreateUpdateSerializer
        return PostDetailSerializer
    
    def get_queryset(self):
        queryset = super().get_queryset()
        
        # 필터링
        status_param = self.request.query_params.get('status')
        if status_param:
            queryset = queryset.filter(status=status_param)
        
        category = self.request.query_params.get('category')
        if category:
            queryset = queryset.filter(category__slug=category)
        
        tag = self.request.query_params.get('tag')
        if tag:
            queryset = queryset.filter(tags__slug=tag)
        
        # 공개된 게시글만 (작성자가 아닌 경우)
        if not self.request.user.is_authenticated:
            queryset = queryset.filter(status='published')
        
        return queryset
    
    def perform_create(self, serializer):
        serializer.save(author=self.request.user)
    
    def retrieve(self, request, *args, **kwargs):
        instance = self.get_object()
        # 조회수 증가
        Post.objects.filter(pk=instance.pk).update(views=models.F('views') + 1)
        serializer = self.get_serializer(instance)
        return Response(serializer.data)
    
    @action(detail=False, methods=['get'])
    def popular(self, request):
        """인기 게시글"""
        posts = self.get_queryset().order_by('-views')[:10]
        serializer = PostListSerializer(posts, many=True)
        return Response(serializer.data)
    
    @action(detail=False, methods=['get'])
    def trending(self, request):
        """최근 인기 게시글"""
        from datetime import timedelta
        from django.utils import timezone
        
        week_ago = timezone.now() - timedelta(days=7)
        posts = self.get_queryset().filter(
            created_at__gte=week_ago
        ).order_by('-views')[:10]
        
        serializer = PostListSerializer(posts, many=True)
        return Response(serializer.data)
    
    @action(detail=True, methods=['post'])
    def add_comment(self, request, pk=None):
        """댓글 추가"""
        post = self.get_object()
        serializer = CommentSerializer(data=request.data)
        
        if serializer.is_valid():
            serializer.save(post=post, author=request.user)
            return Response(serializer.data, status=status.HTTP_201_CREATED)
        
        return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)

class CategoryViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Category.objects.annotate(
        post_count=Count('posts', filter=Q(posts__status='published'))
    )
    serializer_class = CategorySerializer
    lookup_field = 'slug'

class TagViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Tag.objects.all()
    serializer_class = TagSerializer
    lookup_field = 'slug'

🔐 Permissions (permissions.py)

from rest_framework import permissions

class IsAuthorOrReadOnly(permissions.BasePermission):
    """
    작성자만 수정/삭제 가능, 나머지는 읽기만 가능
    """
    def has_object_permission(self, request, view, obj):
        # 읽기 권한은 모두에게 허용
        if request.method in permissions.SAFE_METHODS:
            return True
        
        # 쓰기 권한은 작성자에게만
        return obj.author == request.user

🛣️ URLs (urls.py)

from django.urls import path, include
from rest_framework.routers import DefaultRouter
from .views import PostViewSet, CategoryViewSet, TagViewSet

router = DefaultRouter()
router.register(r'posts', PostViewSet, basename='post')
router.register(r'categories', CategoryViewSet, basename='category')
router.register(r'tags', TagViewSet, basename='tag')

urlpatterns = [
    path('', include(router.urls)),
]

🗺️ 학습 단계

💡 실무 팁

🌐 유용한 리소스