Database Integration

TypeScript Real World Applications — Database Integration
TypeScript Real World Applications
Course 3 · Chapter 6 · Database Integration

🗄️ Database Integration

A raw database driver hands you back any — the exact problem Chapter 3 solved for request bodies. This chapter covers the two dominant ways TypeScript projects keep the database honestly typed: decorator-based ORMs like TypeORM, and schema-first tools like Prisma — plus query builders, migrations, and wrapping it all behind the repository pattern from Chapter 1's DI container.

The Problem: Raw Drivers Return any

A raw SQL client has no idea what columns your query returns — the result is typed as any, and every mistake is a runtime surprise:

const result = await pool.query("SELECT id, name, emial FROM users"); // result.rows: any[] — TypeScript has no idea "emial" is a typo // until the query fails at runtime, or worse, silently returns nothing. const user = result.rows[0]; console.log(user.email.toUpperCase()); // ❌ Runtime crash: user.email is undefined

🏛️ Two ORM Philosophies

TypeScript's two most common ORMs solve this the same way Chapter 3 solved API typing — one source of truth — but they get there differently:

TypeORM (Decorator-Based) vs Prisma (Schema-First)

TypeORM
@Entity() class User { @PrimaryGeneratedColumn() id: number; @Column() name: string; @Column({ unique: true }) email: string; }

The entity is the class. Decorators (Course 2) describe the table shape directly on it.

Prisma
// schema.prisma model User { id Int @id @default(autoincrement()) name String email String @unique }

The schema is a separate file. Prisma generates a fully-typed client and types from it — nothing hand-written.

TypeORM Trade-off

Feels natural if you already like class-based, decorator-driven code (Angular, NestJS). Entities double as both the type and the runtime model.

Prisma Trade-off

The schema is the single source of truth; types are generated, never hand-maintained — closer to the zod "one schema, one type" pattern from Chapters 3 and 4.

Type-Safe Queries With Prisma

Once the schema is defined, every query is checked against the actual table shape — a typo is a compile error, not a runtime one:

const user = await prisma.user.findUnique({ where: { id: 1 }, select: { id: true, name: true, email: true }, }); // user is typed as { id: number; name: string; email: string } | null // prisma.user.findUnique({ where: { emial: "a@b.com" } }); // ❌ compile error: no "emial" field if (user) { console.log(user.email.toUpperCase()); // ✅ TypeScript knows email exists and is a string }

Query Builders: Typed SQL Without a Full ORM

Sometimes an ORM is more machinery than you want, but raw SQL is too loose. A type-safe query builder like Kysely sits in between — real SQL, fully typed:

Kysely: SQL-Shaped, Type-Checked

interface Database { users: { id: number; name: string; email: string }; orders: { id: number; user_id: number; total: number }; } const db = new Kysely<Database>({ /* dialect config */ }); const rows = await db .selectFrom("users") .innerJoin("orders", "orders.user_id", "users.id") .select(["users.name", "orders.total"]) .where("orders.total", ">", 100) .execute(); // rows: { name: string; total: number }[] — inferred from the Database interface, // with every table/column name checked against it.

🔀 Migrations

The database schema evolves over time just like the API versions from Chapter 3 — migrations are how that evolution stays reversible and tracked:

Migration Files

Each schema change (add column, add table, add index) is a numbered, ordered file — applied in sequence, never edited after the fact.

Up / Down

Every migration defines how to apply it and how to reverse it — a bad deploy can roll back the schema, not just the code.

Generated From the Schema

Prisma/TypeORM can diff your schema definition against the database and generate the migration file for you.

Types Follow the Schema

After a migration runs, regenerating types (prisma generate) keeps the TypeScript types in sync with the new schema automatically.

Wrapping It All: The Repository Pattern

Business logic shouldn't import prisma directly any more than it should hardcode new PostgresDatabase() — the same DI principle from Chapter 1 applies here too:

interface UserRepository { findById(id: number): Promise<User | null>; create(input: CreateUserInput): Promise<User>; } class PrismaUserRepository implements UserRepository { constructor(private prisma: PrismaClient) {} findById(id: number) { return this.prisma.user.findUnique({ where: { id } }); } create(input: CreateUserInput) { return this.prisma.user.create({ data: input }); } } // Tests can inject an in-memory FakeUserRepository — no real database needed, // exactly like the FakeDatabase from Chapter 2's integration testing section.

💻 Coding Challenges

Challenge 1: Design a Repository Interface

Write a ProductRepository interface with findById, findAll, and create methods, then implement two versions: one backed by an in-memory array, one that would call a real ORM.

Goal: Practice separating the data-access contract from its implementation.

→ Solution

Challenge 2: Type a Query Builder Result

Define a Database interface with a products table shape, then write a typed function that selects specific columns and returns a result type inferred from the interface (not hand-written).

Goal: Practice deriving a result type from a schema interface instead of writing it separately.

→ Solution

Challenge 3: Write an Up/Down Migration

Write a migration (in plain SQL or pseudo-code) that adds a discontinued: boolean column to a products table, including both the up (apply) and down (revert) steps.

Goal: Practice thinking about schema changes as reversible, not just forward-only.

→ Solution

⚠️ Gotcha: The N+1 Query

A type-safe ORM stops you from misspelling a column — it does nothing to stop you from looping over 100 users and querying their orders one at a time inside the loop, turning one page load into 101 database round trips. Watch for loops containing an await call to your repository, and prefer a single joined or batched query (include in Prisma, a WHERE user_id IN (...) query, or a DataLoader) instead.

🎯 What's Next

With data safely typed at rest, the next chapter looks at data in motion: Real-time Communication — WebSockets, event streaming, pub/sub patterns, and keeping real-time messages just as type-safe as everything else in this course.