📘 PostgreSQL with TypeScript: SQL Database Magic! 🚀

Welcome to the awesome world of PostgreSQL with TypeScript! 🎉 If you’ve ever dreamed of having a database that’s both powerful and type-safe, you’re in for a treat! Think of PostgreSQL as your reliable friend who never forgets anything 🧠, and TypeScript as your helpful assistant who makes sure you never make silly mistakes 🛡️.

Ready to build some amazing database-powered applications? Let’s dive in! 💪

🎯 Introduction

Imagine you’re building the next big social media platform 📱, and you need to store millions of users, posts, and comments. You want something that’s:

✅ Super reliable - No data loss, ever! 🔒
✅ Lightning fast - Even with millions of records ⚡
✅ Type-safe - Catch errors before they reach production 🐛
✅ Scalable - Grows with your success 📈

That’s exactly what PostgreSQL + TypeScript gives you! It’s like having a superhero database team 🦸‍♂️🦸‍♀️.

📚 Understanding PostgreSQL with TypeScript

Think of PostgreSQL as the world’s most organized librarian 📚. It knows exactly where every piece of information is stored, can find it lightning-fast, and never loses track of anything. But here’s the cool part - when you combine it with TypeScript, it’s like having that librarian speak your language perfectly! 🗣️

Why This Combo Rocks 🎸

PostgreSQL is like a Swiss Army knife 🔧 for databases:

• ACID compliance - Your data is always consistent
• Advanced features - JSON support, full-text search, and more
• Performance - Handles millions of rows like a champ
• Reliability - Used by companies like Instagram and Spotify

TypeScript adds the magic sauce ✨:

• Type safety - Know your data structure at compile time
• IntelliSense - Your editor becomes super smart
• Refactoring - Change things confidently
• Documentation - Types are living documentation

🔧 Basic Setup and Connection

Let’s get our hands dirty! 🛠️ First, we need to set up our PostgreSQL playground.

Step 1: Install PostgreSQL 📦

# 🍎 macOS with Homebrew
brew install postgresql
brew services start postgresql

# 🐧 Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# 🪟 Windows
# Download from postgresql.org

Step 2: Install TypeScript Dependencies 📚

# 📦 Install the essentials
npm install pg @types/pg
npm install -D @types/node typescript

# 🚀 Optional but awesome
npm install dotenv

Step 3: Your First Connection! 🔗

// 📁 database.ts
import { Pool, Client } from 'pg';

// 🔧 Basic connection (don't do this in production!)
const client = new Client({
  user: 'your_username',     // 👤 Your database user
  host: 'localhost',         // 🏠 Where PostgreSQL lives
  database: 'my_awesome_app', // 🗄️ Your database name
  password: 'your_password', // 🔑 Keep this secret!
  port: 5432,               // 🚪 PostgreSQL's favorite port
});

// 🌟 Better approach with connection pooling
const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || '5432'),
  max: 20,                  // 🎯 Maximum connections
  idleTimeoutMillis: 30000, // ⏰ How long to keep idle connections
});

// 🎉 Test our connection
const testConnection = async (): Promise<void> => {
  try {
    const client = await pool.connect();
    console.log('🎊 Connected to PostgreSQL!');
    
    const result = await client.query('SELECT NOW()');
    console.log('⏰ Server time:', result.rows[0].now);
    
    client.release(); // 🔄 Always return connections to the pool!
  } catch (error) {
    console.error('💥 Connection failed:', error);
  }
};

export { pool, testConnection };

Step 4: Environment Variables 🌍

// 📁 .env
DB_USER=postgres
DB_HOST=localhost
DB_NAME=my_awesome_app
DB_PASSWORD=your_super_secret_password
DB_PORT=5432

💡 Practical Examples

Let’s build some real-world applications! 🏗️

Example 1: User Management System 👤

First, let’s create our user table:

-- 🗄️ Create users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,              -- 🔢 Auto-incrementing ID
  username VARCHAR(50) UNIQUE NOT NULL, -- 👤 Unique username
  email VARCHAR(255) UNIQUE NOT NULL,   -- 📧 Email address
  password_hash VARCHAR(255) NOT NULL,  -- 🔒 Hashed password
  created_at TIMESTAMP DEFAULT NOW(),   -- ⏰ When they joined
  updated_at TIMESTAMP DEFAULT NOW()    -- 📝 Last update
);

Now let’s create type-safe TypeScript code:

// 📁 types/user.ts
export interface User {
  id: number;           // 🔢 User ID
  username: string;     // 👤 Username
  email: string;        // 📧 Email
  password_hash: string; // 🔒 Password hash
  created_at: Date;     // ⏰ Creation time
  updated_at: Date;     // 📝 Update time
}

export interface CreateUserData {
  username: string;     // 👤 New username
  email: string;        // 📧 New email
  password_hash: string; // 🔒 Hashed password
}

// 📁 services/userService.ts
import { pool } from '../database';
import { User, CreateUserData } from '../types/user';

export class UserService {
  // 🎉 Create a new user
  static async createUser(userData: CreateUserData): Promise<User> {
    const query = `
      INSERT INTO users (username, email, password_hash)
      VALUES ($1, $2, $3)
      RETURNING *
    `;
    
    try {
      const result = await pool.query(query, [
        userData.username,
        userData.email,
        userData.password_hash
      ]);
      
      console.log('✅ User created successfully!');
      return result.rows[0] as User;
    } catch (error) {
      console.error('💥 Failed to create user:', error);
      throw new Error('User creation failed');
    }
  }

  // 🔍 Find user by email
  static async findUserByEmail(email: string): Promise<User | null> {
    const query = 'SELECT * FROM users WHERE email = $1';
    
    try {
      const result = await pool.query(query, [email]);
      
      if (result.rows.length === 0) {
        console.log('😔 User not found');
        return null;
      }
      
      console.log('🎯 User found!');
      return result.rows[0] as User;
    } catch (error) {
      console.error('💥 Database error:', error);
      throw new Error('User lookup failed');
    }
  }

  // 📋 Get all users with pagination
  static async getAllUsers(page: number = 1, limit: number = 10): Promise<User[]> {
    const offset = (page - 1) * limit;
    const query = `
      SELECT * FROM users 
      ORDER BY created_at DESC 
      LIMIT $1 OFFSET $2
    `;
    
    try {
      const result = await pool.query(query, [limit, offset]);
      console.log(`📊 Found ${result.rows.length} users`);
      return result.rows as User[];
    } catch (error) {
      console.error('💥 Failed to fetch users:', error);
      throw new Error('User fetch failed');
    }
  }

  // 🔄 Update user
  static async updateUser(id: number, updates: Partial<CreateUserData>): Promise<User> {
    const setClause = Object.keys(updates)
      .map((key, index) => `${key} = $${index + 2}`)
      .join(', ');
    
    const query = `
      UPDATE users 
      SET ${setClause}, updated_at = NOW()
      WHERE id = $1
      RETURNING *
    `;
    
    try {
      const values = [id, ...Object.values(updates)];
      const result = await pool.query(query, values);
      
      if (result.rows.length === 0) {
        throw new Error('User not found');
      }
      
      console.log('✅ User updated successfully!');
      return result.rows[0] as User;
    } catch (error) {
      console.error('💥 Failed to update user:', error);
      throw error;
    }
  }
}

Example 2: Blog System with Relationships 📝

Let’s create a more complex example with related tables:

-- 📝 Posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- 💬 Comments table
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW()
);

TypeScript models with relationships:

// 📁 types/blog.ts
export interface Post {
  id: number;
  title: string;
  content: string;
  author_id: number;
  published: boolean;
  created_at: Date;
  updated_at: Date;
  // 🔗 Optional joined data
  author?: User;
  comments?: Comment[];
}

export interface Comment {
  id: number;
  content: string;
  post_id: number;
  author_id: number;
  created_at: Date;
  // 🔗 Optional joined data
  author?: User;
}

// 📁 services/blogService.ts
export class BlogService {
  // 📝 Create a new post
  static async createPost(authorId: number, title: string, content: string): Promise<Post> {
    const query = `
      INSERT INTO posts (title, content, author_id)
      VALUES ($1, $2, $3)
      RETURNING *
    `;
    
    try {
      const result = await pool.query(query, [title, content, authorId]);
      console.log('🎉 Post created!');
      return result.rows[0] as Post;
    } catch (error) {
      console.error('💥 Failed to create post:', error);
      throw new Error('Post creation failed');
    }
  }

  // 📚 Get posts with author information (JOIN!)
  static async getPostsWithAuthor(): Promise<Post[]> {
    const query = `
      SELECT 
        p.*,
        u.username as author_username,
        u.email as author_email
      FROM posts p
      JOIN users u ON p.author_id = u.id
      WHERE p.published = true
      ORDER BY p.created_at DESC
    `;
    
    try {
      const result = await pool.query(query);
      
      // 🎨 Transform the results
      const posts: Post[] = result.rows.map(row => ({
        id: row.id,
        title: row.title,
        content: row.content,
        author_id: row.author_id,
        published: row.published,
        created_at: row.created_at,
        updated_at: row.updated_at,
        author: {
          id: row.author_id,
          username: row.author_username,
          email: row.author_email,
        } as User
      }));
      
      console.log(`📊 Found ${posts.length} posts with authors`);
      return posts;
    } catch (error) {
      console.error('💥 Failed to fetch posts:', error);
      throw new Error('Posts fetch failed');
    }
  }
}

🚀 Advanced Concepts

Ready to level up? Let’s explore some advanced PostgreSQL + TypeScript patterns! 💪

Connection Pooling Best Practices 🏊‍♂️

// 📁 database/pool.ts
import { Pool, PoolConfig } from 'pg';

// 🎯 Advanced pool configuration
const poolConfig: PoolConfig = {
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || '5432'),
  
  // 🏊‍♂️ Pool settings
  max: 20,                    // Maximum number of clients
  min: 5,                     // Minimum number of clients
  idleTimeoutMillis: 30000,   // How long a client can be idle
  connectionTimeoutMillis: 2000, // How long to wait for connection
  
  // 🔧 Advanced settings
  statement_timeout: 30000,   // Query timeout
  query_timeout: 30000,       // Another query timeout
  application_name: 'my_awesome_app', // Shows up in PostgreSQL logs
};

export const pool = new Pool(poolConfig);

// 🎉 Pool event listeners
pool.on('connect', (client) => {
  console.log('🔗 New client connected');
});

pool.on('acquire', (client) => {
  console.log('🎯 Client acquired from pool');
});

pool.on('error', (err, client) => {
  console.error('💥 Pool error:', err);
});

// 🧹 Graceful shutdown
process.on('SIGINT', async () => {
  console.log('🛑 Shutting down gracefully...');
  await pool.end();
  process.exit(0);
});

Database Transactions 💳

// 📁 services/transactionService.ts
import { PoolClient } from 'pg';
import { pool } from '../database/pool';

export class TransactionService {
  // 💳 Transfer money between accounts (atomic operation!)
  static async transferMoney(
    fromUserId: number, 
    toUserId: number, 
    amount: number
  ): Promise<void> {
    const client: PoolClient = await pool.connect();
    
    try {
      // 🚦 Start transaction
      await client.query('BEGIN');
      
      // 💰 Check if sender has enough money
      const balanceResult = await client.query(
        'SELECT balance FROM accounts WHERE user_id = $1',
        [fromUserId]
      );
      
      if (balanceResult.rows[0].balance < amount) {
        throw new Error('💸 Insufficient funds!');
      }
      
      // 📉 Deduct from sender
      await client.query(
        'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
        [amount, fromUserId]
      );
      
      // 📈 Add to receiver
      await client.query(
        'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
        [amount, toUserId]
      );
      
      // 📝 Log the transaction
      await client.query(
        `INSERT INTO transactions (from_user_id, to_user_id, amount) 
         VALUES ($1, $2, $3)`,
        [fromUserId, toUserId, amount]
      );
      
      // ✅ Commit transaction
      await client.query('COMMIT');
      console.log('🎉 Money transfer completed!');
      
    } catch (error) {
      // 🔄 Rollback on error
      await client.query('ROLLBACK');
      console.error('💥 Transaction failed:', error);
      throw error;
    } finally {
      // 🔄 Always release the client
      client.release();
    }
  }
}

Query Builder Pattern 🏗️

// 📁 utils/queryBuilder.ts
export class QueryBuilder {
  private query: string = '';
  private values: any[] = [];
  private paramCount: number = 0;

  // 🎯 SELECT builder
  select(columns: string[]): QueryBuilder {
    this.query += `SELECT ${columns.join(', ')} `;
    return this;
  }

  // 📋 FROM builder
  from(table: string): QueryBuilder {
    this.query += `FROM ${table} `;
    return this;
  }

  // 🔗 JOIN builder
  join(table: string, condition: string): QueryBuilder {
    this.query += `JOIN ${table} ON ${condition} `;
    return this;
  }

  // 🎯 WHERE builder
  where(column: string, value: any): QueryBuilder {
    this.paramCount++;
    const operator = this.query.includes('WHERE') ? 'AND' : 'WHERE';
    this.query += `${operator} ${column} = $${this.paramCount} `;
    this.values.push(value);
    return this;
  }

  // 📊 ORDER BY builder
  orderBy(column: string, direction: 'ASC' | 'DESC' = 'ASC'): QueryBuilder {
    this.query += `ORDER BY ${column} ${direction} `;
    return this;
  }

  // 🔢 LIMIT builder
  limit(count: number): QueryBuilder {
    this.paramCount++;
    this.query += `LIMIT $${this.paramCount} `;
    this.values.push(count);
    return this;
  }

  // 🏗️ Build the final query
  build(): { query: string; values: any[] } {
    return {
      query: this.query.trim(),
      values: this.values
    };
  }
}

// 🎉 Usage example
const getUserPosts = async (userId: number, limit: number = 10): Promise<Post[]> => {
  const { query, values } = new QueryBuilder()
    .select(['p.*', 'u.username'])
    .from('posts p')
    .join('users u', 'p.author_id = u.id')
    .where('p.author_id', userId)
    .where('p.published', true)
    .orderBy('p.created_at', 'DESC')
    .limit(limit)
    .build();

  console.log('🔍 Generated query:', query);
  
  const result = await pool.query(query, values);
  return result.rows as Post[];
};

⚠️ Common Pitfalls and Solutions

Let’s learn from common mistakes! 🎓

Pitfall 1: SQL Injection 🚨

❌ NEVER do this:

// 💀 DANGEROUS! SQL injection vulnerability
const getUser = async (email: string) => {
  const query = `SELECT * FROM users WHERE email = '${email}'`;
  return await pool.query(query);
};

// 😈 Hacker input: "'; DROP TABLE users; --"
// 💥 Your entire users table gets deleted!

✅ Always use parameterized queries:

// 🛡️ SAFE! Parameterized query
const getUser = async (email: string) => {
  const query = 'SELECT * FROM users WHERE email = $1';
  return await pool.query(query, [email]);
};

// 😊 PostgreSQL handles escaping automatically!

Pitfall 2: Connection Leaks 💧

❌ Don’t forget to release connections:

// 💧 Connection leak - bad!
const badExample = async () => {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users');
  return result.rows;
  // 💥 Connection never released!
};

✅ Always release or use pool directly:

// ✅ Good - using pool directly
const goodExample = async () => {
  const result = await pool.query('SELECT * FROM users');
  return result.rows;
};

// ✅ Also good - explicit release
const alsoGoodExample = async () => {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users');
    return result.rows;
  } finally {
    client.release(); // 🔄 Always release!
  }
};

Pitfall 3: Ignoring Errors 🙈

❌ Silent failures are the worst:

// 🙈 Ignoring errors - terrible!
const badErrorHandling = async (email: string) => {
  try {
    const result = await pool.query('SELECT * FROM users WHERE email = $1', [email]);
    return result.rows[0];
  } catch (error) {
    // 😱 Silent failure!
    return null;
  }
};

✅ Proper error handling:

// 🎯 Good error handling
const goodErrorHandling = async (email: string): Promise<User | null> => {
  try {
    const result = await pool.query('SELECT * FROM users WHERE email = $1', [email]);
    
    if (result.rows.length === 0) {
      return null; // 😊 User not found, that's okay
    }
    
    return result.rows[0] as User;
  } catch (error) {
    console.error('💥 Database error in getUserByEmail:', error);
    
    // 🔍 Log important details for debugging
    console.error('📧 Email:', email);
    console.error('⏰ Timestamp:', new Date().toISOString());
    
    // 🚨 Re-throw so caller can handle appropriately
    throw new Error(`Failed to fetch user: ${error.message}`);
  }
};

🛠️ Best Practices

Here are the golden rules for PostgreSQL + TypeScript success! 🏆

1. Database Design Principles 🎨

-- 🎯 Good table design
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 🆔 Use UUIDs for distributed systems
  user_id INTEGER NOT NULL REFERENCES users(id), -- 🔗 Foreign key constraints
  status VARCHAR(20) NOT NULL DEFAULT 'pending', -- 📊 Meaningful defaults
  total_amount DECIMAL(10,2) NOT NULL,           -- 💰 Proper decimal for money
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- ⏰ Always use timezone
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  
  -- 📋 Constraints make your data trustworthy
  CONSTRAINT valid_status CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
  CONSTRAINT positive_amount CHECK (total_amount > 0)
);

-- 🚀 Indexes for performance
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

2. Type-Safe Database Layer 🛡️

// 📁 types/database.ts
// 🎯 Strict database types
export interface DatabaseUser {
  readonly id: number;           // 🔒 ID should never change
  username: string;
  email: string;
  password_hash: string;
  readonly created_at: Date;     // 🔒 Creation time shouldn't change
  updated_at: Date;
}

// 🎨 Separate types for different operations
export type CreateUserInput = Omit<DatabaseUser, 'id' | 'created_at' | 'updated_at'>;
export type UpdateUserInput = Partial<Pick<DatabaseUser, 'username' | 'email'>>;
export type UserResponse = Omit<DatabaseUser, 'password_hash'>; // 🔒 Never return password

// 📁 database/userRepository.ts
export class UserRepository {
  // 🎯 Type-safe repository pattern
  static async create(userData: CreateUserInput): Promise<UserResponse> {
    const query = `
      INSERT INTO users (username, email, password_hash)
      VALUES ($1, $2, $3)
      RETURNING id, username, email, created_at, updated_at
    `;
    
    const result = await pool.query(query, [
      userData.username,
      userData.email,
      userData.password_hash
    ]);
    
    return result.rows[0] as UserResponse;
  }
  
  // 🔍 Type-safe queries with validation
  static async findById(id: number): Promise<UserResponse | null> {
    if (!Number.isInteger(id) || id <= 0) {
      throw new Error('🚨 Invalid user ID');
    }
    
    const query = `
      SELECT id, username, email, created_at, updated_at 
      FROM users 
      WHERE id = $1
    `;
    
    const result = await pool.query(query, [id]);
    return result.rows[0] as UserResponse || null;
  }
}

3. Performance Optimization 🚀

// 📁 database/performance.ts
export class PerformanceOptimizer {
  // 🔄 Batch operations for better performance
  static async createMultipleUsers(users: CreateUserInput[]): Promise<UserResponse[]> {
    if (users.length === 0) return [];
    
    // 🎯 Single query instead of multiple queries
    const values: string[] = [];
    const params: any[] = [];
    
    users.forEach((user, index) => {
      const offset = index * 3;
      values.push(`($${offset + 1}, $${offset + 2}, $${offset + 3})`);
      params.push(user.username, user.email, user.password_hash);
    });
    
    const query = `
      INSERT INTO users (username, email, password_hash)
      VALUES ${values.join(', ')}
      RETURNING id, username, email, created_at, updated_at
    `;
    
    const result = await pool.query(query, params);
    console.log(`🎉 Created ${result.rows.length} users in one query!`);
    return result.rows as UserResponse[];
  }
  
  // 📊 Efficient pagination
  static async getUsersPaginated(
    page: number, 
    pageSize: number = 20
  ): Promise<{ users: UserResponse[]; total: number; hasMore: boolean }> {
    const offset = (page - 1) * pageSize;
    
    // 🎯 Get data and count in parallel
    const [dataResult, countResult] = await Promise.all([
      pool.query(`
        SELECT id, username, email, created_at, updated_at
        FROM users
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2
      `, [pageSize + 1, offset]), // 🎯 Get one extra to check if there's more
      
      pool.query('SELECT COUNT(*) FROM users')
    ]);
    
    const users = dataResult.rows.slice(0, pageSize) as UserResponse[];
    const hasMore = dataResult.rows.length > pageSize;
    const total = parseInt(countResult.rows[0].count);
    
    return { users, total, hasMore };
  }
}

4. Environment Configuration 🌍

// 📁 config/database.ts
import { config } from 'dotenv';
import { PoolConfig } from 'pg';

config(); // 🔧 Load environment variables

// 🎯 Validate required environment variables
const requiredEnvVars = ['DB_USER', 'DB_HOST', 'DB_NAME', 'DB_PASSWORD'];
const missingVars = requiredEnvVars.filter(varName => !process.env[varName]);

if (missingVars.length > 0) {
  throw new Error(`🚨 Missing required environment variables: ${missingVars.join(', ')}`);
}

// 🏗️ Environment-specific configurations
const isDevelopment = process.env.NODE_ENV === 'development';
const isProduction = process.env.NODE_ENV === 'production';

export const databaseConfig: PoolConfig = {
  user: process.env.DB_USER!,
  host: process.env.DB_HOST!,
  database: process.env.DB_NAME!,
  password: process.env.DB_PASSWORD!,
  port: parseInt(process.env.DB_PORT || '5432'),
  
  // 🎯 Environment-specific settings
  max: isProduction ? 20 : 5,
  min: isProduction ? 5 : 1,
  idleTimeoutMillis: isProduction ? 30000 : 10000,
  
  // 🔧 SSL settings for production
  ssl: isProduction ? { rejectUnauthorized: false } : false,
  
  // 📝 Logging
  log: isDevelopment ? console.log : undefined,
};

// 🎉 Export ready-to-use pool
export const pool = new Pool(databaseConfig);

🧪 Hands-On Exercise

Time to put your skills to the test! 💪 Let’s build a complete e-commerce system with products, categories, and orders.

Your Mission 🎯

Create a type-safe e-commerce database system with:

• 📦 Products with categories
• 🛒 Shopping cart functionality
• 📋 Order management
• 👤 Customer accounts

Database Schema 🗄️

-- 🏷️ Categories table
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 📦 Products table
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  category_id INTEGER REFERENCES categories(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT positive_price CHECK (price > 0),
  CONSTRAINT non_negative_stock CHECK (stock_quantity >= 0)
);

-- 🛒 Shopping cart items
CREATE TABLE cart_items (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL DEFAULT 1,
  added_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT positive_quantity CHECK (quantity > 0),
  UNIQUE(user_id, product_id)
);

-- 📋 Orders
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INTEGER REFERENCES users(id),
  status VARCHAR(20) DEFAULT 'pending',
  total_amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT valid_order_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

Your Challenge 🚀

Try to implement these TypeScript services:

1. ProductService - CRUD operations for products
2. CartService - Add/remove items, calculate totals
3. OrderService - Create orders from cart, update status

// 📁 types/ecommerce.ts
export interface Category {
  id: number;
  name: string;
  description?: string;
  created_at: Date;
}

export interface Product {
  id: number;
  name: string;
  description?: string;
  price: number;
  stock_quantity: number;
  category_id: number;
  created_at: Date;
  updated_at: Date;
  category?: Category;
}

export interface CartItem {
  id: number;
  user_id: number;
  product_id: number;
  quantity: number;
  added_at: Date;
  product?: Product;
}

export interface Order {
  id: string;
  user_id: number;
  status: 'pending' | 'processing' | 'shipped' | 'delivered' | 'cancelled';
  total_amount: number;
  created_at: Date;
}

// 📁 services/productService.ts
export class ProductService {
  // 📦 Get products with categories
  static async getProductsWithCategories(): Promise<Product[]> {
    const query = `
      SELECT 
        p.*,
        c.name as category_name,
        c.description as category_description
      FROM products p
      LEFT JOIN categories c ON p.category_id = c.id
      ORDER BY p.created_at DESC
    `;
    
    const result = await pool.query(query);
    
    return result.rows.map(row => ({
      id: row.id,
      name: row.name,
      description: row.description,
      price: parseFloat(row.price),
      stock_quantity: row.stock_quantity,
      category_id: row.category_id,
      created_at: row.created_at,
      updated_at: row.updated_at,
      category: row.category_name ? {
        id: row.category_id,
        name: row.category_name,
        description: row.category_description,
        created_at: new Date()
      } : undefined
    } as Product));
  }
  
  // 🔍 Search products
  static async searchProducts(searchTerm: string): Promise<Product[]> {
    const query = `
      SELECT * FROM products 
      WHERE name ILIKE $1 OR description ILIKE $1
      ORDER BY name
    `;
    
    const result = await pool.query(query, [`%${searchTerm}%`]);
    return result.rows as Product[];
  }
}

// 📁 services/cartService.ts
export class CartService {
  // 🛒 Add item to cart
  static async addToCart(userId: number, productId: number, quantity: number = 1): Promise<CartItem> {
    const query = `
      INSERT INTO cart_items (user_id, product_id, quantity)
      VALUES ($1, $2, $3)
      ON CONFLICT (user_id, product_id)
      DO UPDATE SET quantity = cart_items.quantity + $3
      RETURNING *
    `;
    
    const result = await pool.query(query, [userId, productId, quantity]);
    return result.rows[0] as CartItem;
  }
  
  // 📊 Get cart with product details
  static async getCartWithProducts(userId: number): Promise<CartItem[]> {
    const query = `
      SELECT 
        ci.*,
        p.name as product_name,
        p.price as product_price,
        p.stock_quantity
      FROM cart_items ci
      JOIN products p ON ci.product_id = p.id
      WHERE ci.user_id = $1
      ORDER BY ci.added_at DESC
    `;
    
    const result = await pool.query(query, [userId]);
    
    return result.rows.map(row => ({
      id: row.id,
      user_id: row.user_id,
      product_id: row.product_id,
      quantity: row.quantity,
      added_at: row.added_at,
      product: {
        id: row.product_id,
        name: row.product_name,
        price: parseFloat(row.product_price),
        stock_quantity: row.stock_quantity
      } as Product
    } as CartItem));
  }
  
  // 💰 Calculate cart total
  static async getCartTotal(userId: number): Promise<number> {
    const query = `
      SELECT SUM(ci.quantity * p.price) as total
      FROM cart_items ci
      JOIN products p ON ci.product_id = p.id
      WHERE ci.user_id = $1
    `;
    
    const result = await pool.query(query, [userId]);
    return parseFloat(result.rows[0].total || '0');
  }
}

// 📁 services/orderService.ts
export class OrderService {
  // 📋 Create order from cart
  static async createOrderFromCart(userId: number): Promise<Order> {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      // 💰 Calculate total
      const totalResult = await client.query(`
        SELECT SUM(ci.quantity * p.price) as total
        FROM cart_items ci
        JOIN products p ON ci.product_id = p.id
        WHERE ci.user_id = $1
      `, [userId]);
      
      const total = parseFloat(totalResult.rows[0].total || '0');
      
      if (total === 0) {
        throw new Error('🛒 Cart is empty!');
      }
      
      // 📋 Create order
      const orderResult = await client.query(`
        INSERT INTO orders (user_id, total_amount)
        VALUES ($1, $2)
        RETURNING *
      `, [userId, total]);
      
      // 🧹 Clear cart
      await client.query('DELETE FROM cart_items WHERE user_id = $1', [userId]);
      
      await client.query('COMMIT');
      
      console.log('🎉 Order created successfully!');
      return orderResult.rows[0] as Order;
      
    } catch (error) {
      await client.query('ROLLBACK');
      console.error('💥 Order creation failed:', error);
      throw error;
    } finally {
      client.release();
    }
  }
}

🎓 Key Takeaways

Congratulations! 🎉 You’ve mastered PostgreSQL with TypeScript! Here’s what you now know:

✅ Database Connections - Pool management and best practices
✅ Type Safety - Proper TypeScript types for database operations
✅ SQL Operations - CRUD, joins, transactions, and complex queries
✅ Performance - Connection pooling, batching, and optimization
✅ Security - Parameterized queries and proper error handling
✅ Real-World Patterns - Repository pattern, query builders, and more

You’re now equipped to build scalable, type-safe database applications! 💪

🤝 Next Steps

Ready to keep growing? Here’s your learning path:

1. 🔍 Advanced PostgreSQL - Learn about indexing, partitioning, and performance tuning
2. 🏗️ Database Migrations - Version control your database schema
3. 📊 ORM Libraries - Explore Prisma, TypeORM, or Drizzle for even more type safety
4. 🚀 Production Deployment - Learn about database hosting, backups, and monitoring
5. 🔄 Real-time Features - Implement subscriptions and live updates

Keep coding, keep learning, and most importantly, have fun building amazing applications! 🚀✨

You’ve got this! 💪🌟