database

# Database Development ## Overview Database design, query optimization, and data management patterns for relational and NoSQL databases. --- ## PostgreSQL ### Schema Design ```sql -- Users table with proper constraints CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, name VARCHAR(100), role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')), status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'deleted')), metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- Posts with foreign key CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE, content TEXT, excerpt VARCHAR(500), status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')), author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, published_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- Many-to-many with junction table CREATE TABLE tags ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(50) NOT NULL UNIQUE, slug VARCHAR(50) NOT NULL UNIQUE ); CREATE TABLE post_tags ( post_id UUID REFERENCES posts(id) ON DELETE CASCADE, tag_id UUID REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (post_id, tag_id) ); -- Indexes CREATE INDEX idx_posts_author ON posts(author_id); CREATE INDEX idx_posts_status ON posts(status) WHERE status = 'published'; CREATE INDEX idx_posts_published_at ON posts(published_at DESC) WHERE status = 'published'; CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Full-text search ALTER TABLE posts ADD COLUMN search_vector tsvector; CREATE INDEX idx_posts_search ON posts USING GIN(search_vector); CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$ BEGIN NEW.search_vector := setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') || setweight(to_tsvector('english', COALESCE(NEW.excerpt, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER posts_search_update BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE FUNCTION update_search_vector(); ``` ### Advanced Queries ```sql -- Common Table Expressions (CTE) WITH post_stats AS ( SELECT author_id, COUNT(*) as post_count, AVG(LENGTH(content)) as avg_length FROM posts WHERE status = 'published' GROUP BY author_id ) SELECT u.name, u.email, ps.post_count, ps.avg_length FROM users u JOIN post_stats ps ON u.id = ps.author_id ORDER BY ps.post_count DESC LIMIT 10; -- Window functions SELECT p.title, p.published_at, u.name as author, ROW_NUMBER() OVER (PARTITION BY p.author_id ORDER BY p.published_at DESC) as author_rank, COUNT(*) OVER (PARTITION BY p.author_id) as author_total_posts, p.published_at - LAG(p.published_at) OVER (PARTITION BY p.author_id ORDER BY p.published_at) as days_since_last FROM posts p JOIN users u ON p.author_id = u.id WHERE p.status = 'published'; -- Recursive CTE (hierarchical data) WITH RECURSIVE category_tree AS ( -- Base case SELECT id, name, parent_id, 0 as depth, ARRAY[name] as path FROM categories WHERE parent_id IS NULL UNION ALL -- Recursive case SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.name FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY path; -- JSONB queries SELECT id, metadata->>'theme' as theme, metadata->'preferences'->>'notifications' as notifications FROM users WHERE metadata @> '{"verified": true}' AND metadata->'preferences' ? 'dark_mode'; -- Update JSONB UPDATE users SET metadata = jsonb_set( metadata, '{lastLogin}', to_jsonb(NOW()) ) WHERE id = $1; ``` ### Performance Optimization ```sql -- Analyze query plan EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT p.*, u.name as author_name FROM posts p JOIN users u ON p.author_id = u.id WHERE p.status = 'published' ORDER BY p.published_at DESC LIMIT 20; -- Partial index for common queries CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- Covering index (index-only scan) CREATE INDEX idx_posts_list ON posts(status, published_at DESC) INCLUDE (title, slug, excerpt, author_id); -- BRIN index for time-series data CREATE INDEX idx_events_created ON events USING BRIN(created_at); -- Table partitioning CREATE TABLE events ( id UUID DEFAULT gen_random_uuid(), event_type VARCHAR(50), payload JSONB, created_at TIMESTAMPTZ DEFAULT NOW() ) PARTITION BY RANGE (created_at); CREATE TABLE events_2024_q1 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE events_2024_q2 PARTITION OF events FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'); ``` --- ## MongoDB ### Schema Design ```javascript // User document with embedded data const userSchema = { _id: ObjectId, email: String, passwordHash: String, profile: { name: String, avatar: String, bio: String, }, preferences: { theme: String, notifications: { email: Boolean, push: Boolean, }, }, roles: [String], createdAt: Date, updatedAt: Date, }; // Post with references const postSchema = { _id: ObjectId, title: String, slug: String, content: String, authorId: ObjectId, // Reference to users tags: [String], // Denormalized for read performance stats: { views: Number, likes: Number, comments: Number, }, status: String, publishedAt: Date, createdAt: Date, }; // Indexes db.users.createIndex({ email: 1 }, { unique: true }); db.posts.createIndex({ authorId: 1, publishedAt: -1 }); db.posts.createIndex({ tags: 1 }); db.posts.createIndex({ title: "text", content: "text" }); ``` ### Aggregation Pipeline ```javascript // Complex aggregation db.posts.aggregate([ // Match published posts { $match: { status: "published" } }, // Lookup author { $lookup: { from: "users", localField: "authorId", foreignField: "_id", as: "author", }, }, { $unwind: "$author" }, // Group by author { $group: { _id: "$author._id", authorName: { $first: "$author.profile.name" }, postCount: { $sum: 1 }, totalViews: { $sum: "$stats.views" }, avgLikes: { $avg: "$stats.likes" }, posts: { $push: { title: "$title", publishedAt: "$publishedAt", }, }, }, }, // Sort by post count { $sort: { postCount: -1 } }, // Limit to top 10 { $limit: 10 }, // Project final shape { $project: { _id: 0, authorId: "$_id", authorName: 1, postCount: 1, totalViews: 1, avgLikes: { $round: ["$avgLikes", 2] }, recentPosts: { $slice: ["$posts", 5] }, }, }, ]); // Faceted search db.products.aggregate([ { $match: { $text: { $search: "laptop" } } }, { $facet: { results: [ { $sort: { score: { $meta: "textScore" } } }, { $skip: 0 }, { $limit: 20 }, ], priceRanges: [ { $bucket: { groupBy: "$price", boundaries: [0, 500, 1000, 2000, Infinity], default: "Other", output: { count: { $sum: 1 } }, }, }, ], brands: [{ $group: { _id: "$brand", count: { $sum: 1 } } }], totalCount: [{ $count: "count" }], }, }, ]); ``` --- ## Redis ### Data Structures ```typescript import Redis from 'ioredis'; const redis = new Redis(process.env.REDIS_URL); // String operations await redis.set('user:123:name', 'John'); await redis.setex('session:abc', 3600, JSON.stringify(sessionData)); // with TTL const name = await redis.get('user:123:name'); // Hash operations await redis.hset('user:123', { name: 'John', email: 'john@example.com', role: 'admin', }); const user = await redis.hgetall('user:123'); await redis.hincrby('user:123', 'loginCount', 1); // List operations (queues) await redis.lpush('queue:emails', JSON.stringify(emailJob)); const job = await redis.brpop('queue:emails', 0); // Blocking pop // Set operations await redis.sadd('user:123:followers', 'user:456', 'user:789'); await redis.sadd('user:456:followers', 'user:123', 'user:789'); const mutualFollowers = await redis.sinter( 'user:123:followers', 'user:456:followers' ); // Sorted set (leaderboard) await redis.zadd('leaderboard', 100, 'player1', 95, 'player2', 110, 'player3'); const topPlayers = await redis.zrevrange('leaderboard', 0, 9, 'WITHSCORES'); const rank = await redis.zrevrank('leaderboard', 'player1'); // HyperLogLog (unique counts) await redis.pfadd('pageviews:2024-01-15', 'user:123', 'user:456'); const uniqueViews = await redis.pfcount('pageviews:2024-01-15'); ``` ### Caching Patterns ```typescript // Cache-aside pattern async function getCachedUser(userId: string): Promise<User> { const cacheKey = `user:${userId}`; // Try cache first const cached = await redis.get(cacheKey); if (cached) { return JSON.parse(cached); } // Cache miss - fetch from DB const user = await db.user.findUnique({ where: { id: userId } }); if (user) { // Store in cache with TTL await redis.setex(cacheKey, 3600, JSON.stringify(user)); } return user; } // Cache invalidation async function updateUser(userId: string, data: UpdateUserInput) { const user = await db.user.update({ where: { id: userId }, data, }); // Invalidate cache await redis.del(`user:${userId}`); return user; } // Rate limiting async function checkRateLimit(userId: string, limit: number, window: number) { const key = `ratelimit:${userId}`; const current = await redis.incr(key); if (current === 1) { await redis.expire(key, window); } return current <= limit; } ``` --- ## Prisma ORM ### Schema Definition ```prisma // prisma/schema.prisma generator client { provider = "prisma-client-js" } datasource db { provider = "postgresql" url = env("DATABASE_URL") } model User { id String @id @default(cuid()) email String @unique password String name String? role Role @default(USER) posts Post[] comments Comment[] profile Profile?