🗄️ DB Setup and Schema

Database schema design is the foundation of any data-driven application. In this lesson, we'll explore relational database concepts, understand ORMs and their benefits, dive deep into migrations, and build a complete schema for our habit tracking API using Drizzle ORM.

Understanding Relational Databases

The Relational Model

Relational databases organize data into tables with rows and columns, connected through relationships:

<aside> 🔗

Core Relational Concepts:

Tables: Store entities (users, habits, tags)
Rows: Individual records (specific user, specific habit)
Columns: Attributes (name, email, created date)
Primary Keys: Unique identifiers for each row
Foreign Keys: References to other tables
Relationships: How tables connect to each other </aside>

PostgreSQL + Node.js: Perfect Match

Why PostgreSQL?

ACID Compliance: Reliable transactions
Rich Data Types: JSON, arrays, custom types
Advanced Features: Full-text search, GIS
Performance: Excellent for complex queries
Open Source: No licensing costs

Node.js Integration

JSON Native: Easy data exchange
Async/Await: Non-blocking database calls
Strong Ecosystem: Excellent ORM options
TypeScript Support: Type-safe database operations

ORMs: Bridging Code and Database

What is an ORM?

Object-Relational Mapping (ORM) translates between database tables and JavaScript objects:

Without ORM 😰

// Raw SQL queries
const result = await client.query(
  'SELECT u.*, h.* FROM users u LEFT JOIN habits h ON [u.id](<http://u.id>) = h.user_id WHERE [u.id](<http://u.id>) = $1',
  [userId]
)

// Manual result parsing
const user = {
  id: result.rows[0].id,
  email: result.rows[0].email,
  habits: [result.rows.map](<http://result.rows.map>)(row => ({
    id: row.habit_id,
    name: row.habit_name
  }))
}

Problems:

SQL injection risks
Manual type conversion
Complex result parsing
Database-specific SQL

With ORM 😊

// Type-safe, intuitive queries
const user = await db.query.users.findFirst({
  where: eq([users.id](<http://users.id>), userId),
  with: {
    habits: true
  }
})

// Perfect TypeScript types
[user.email](<http://user.email>)        // ✅ string
user.habits[0].id // ✅ string
user.nonExistent  // ❌ TypeScript error

Benefits:

SQL injection prevention
Type safety
Automatic serialization
Database abstraction

ORM Benefits for APIs

Benefit	Description	Impact
Type Safety	Compile-time error checking	Fewer runtime bugs, better DX
Query Building	Programmatic query construction	Dynamic filtering, pagination
Relationship Loading	Automatic joins and includes	Less code, optimized queries
Schema Management	Version-controlled database changes	Reliable deployments
Migration System	Automated schema evolution	Safe production updates

Migrations: Database Version Control

What Are Migrations?

Migrations are versioned scripts that modify your database schema over time: