Modern Database Access

0. What are DBs fundamentally?

To understand about modern database access we first need to understand what these databases like MongoDB and PostgreSQL actually do.

These MongoDB and PostgreSQL are actually Database Management Systems. They don't store the data as in themselves but else they reduce our work by making it easier to handle the complex logic of safely writing, updating, and indexing information, preventing data corruption when multiple users try to save at once.

1. Why Applications Need Databases?

Why data needs to be stored permanently ?

• Program Volatility: Computer memory (RAM) is temporary. When a program closes or power is lost, RAM is cleared. Permanent storage ensures user profiles, orders, and application states survive.

• Historical Tracking: Businesses rely on permanent records for auditing, analytics, and historical trends.

• Crash Recovery: Built-in backup mechanisms allow systems to restore historical data if a catastrophic hardware or software failure occurs.

• Interconnected Access: Permanent storage acts as a single, centralized source of truth so multiple, distinct apps can reference the same reliable information.

Structured vs. Unstructured Data

• Structured Data: Information that obeys a strict, predictable blueprint. An e-commerce User will always have an email, a join date, and a hashed password. This lives neatly in rows and columns.

• Unstructured Data: Information that resists rigid formatting—an uploaded profile picture (JPEG), a 15-second voice note, or the raw text of a blog post. (Modern systems usually store the raw file in an object store like AWS S3, and store the structured URL pointing to that file inside the database).

Example of an Ecommerce store

In any standard commercial platform, your data breaks down into four foundational pillars:

1. Users: Who is acting? (Identity, credentials, preferences)

2. Products: What is being acted upon? (SKUs, inventory counts, prices)

3. Orders: The event connecting the two. (User X committed to buying Product Y at Timestamp Z)

4. Payments: The verification of the event. (Transaction IDs, gateway settlement statuses)

Without a database, these four concepts remain disconnected ideas floating in temporary code. The database binds them into a traceable business reality.

2. SQL vs. NoSQL Databases

SQL databases are relational, structured systems that use tables and fixed schemas, while NoSQL databases are non-relational, distributed systems that use flexible formats like documents or key-value pairs.

The choice between them depends entirely on the data structure, scaling needs, and consistency requirements.

Feature	SQL Databases	NoSQL Databases
Data Model	Relational (tables, rows, columns)	Non-relational (documents, key-value, graphs, columns)
Schema	Rigid, predefined, fixed	Dynamic, flexible, schema-less
Scalability	Vertical (scale up CPU/RAM on one server)	Horizontal (scale out by adding more servers)
Transactions	Strict ACID compliance for data integrity	Follows BASE model (Eventual Consistency)
Data Types	Best suited for highly structured data	Ideal for unstructured or semi-structured data
Complex Joins	Native support using standard SQL syntax	Generally avoided; handled via nested data or app level

3. The Problem with Raw Database Queries

To talk to a SQL database natively, you write Structured Query Language (SQL). For example:

SELECT users.name, orders.total 
FROM users 
JOIN orders ON users.id = orders.user_id 
WHERE users.email = 'buyer@example.com';

While writing raw SQL gives you 100% control over the engine, building an entire enterprise backend this way introduces massive friction:

1. The Impedance Mismatch: Databases think in Tables and Rows. Programming languages (like TypeScript or Python) think in Objects, Arrays, and Graphs. Translating a flat SQL row into a rich, nested Javascript Object manually for 150 different API endpoints wastes hundreds of engineering hours.

2. The Refactoring Trap: If you decide to rename the database column user_id to customer_id, standard code editors cannot warn you. Your app will simply compile fine, deploy to production, and crash the moment a user hits that specific endpoint.

3. Security Vulnerabilities: Hand-writing raw SQL strings makes developers prone to SQL Injection, where a malicious user types "; DROP TABLE users;-- into a login box and tricks the database into executing it.

4. What is an ORM?

An ORM (Object-Relational Mapper) is a productivity bridge. It sits directly between your application code and your database driver.

Instead of writing a raw SQL string, you call a method in your native programming language:

// The ORM translates this Javascript into SQL
const user = await db.user.findUnique({ where: { email: 'buyer@example.com' } })

The Reality

ORMs are tools, not magic.

• The Gain: You get massive developer velocity, auto-completed code, automatic sanitization against hackers, and unified type safety.

• The Cost: You lose a layer of transparency. Poorly configured ORMs are notorious for the "N+1 Query Problem"—where asking an ORM to fetch 100 blog posts and their respective authors accidentally triggers 101 separate database hits behind the scenes instead of 1 single optimized SQL JOIN.

5. Understanding Prisma

Prisma popularized the Schema-First ORM wave in the TypeScript ecosystem.

The Philosophy

With Prisma, you do not write database tables, nor do you write TypeScript interfaces for your data. You write a single, custom file called schema.prisma:

model User {
  id     Int     @id @default(autoincrement())
  email  String  @unique
  orders Order[]
}

When you save this file, Prisma's internal compiler reads it and auto-generates a bespoke, hyper-tailored TypeScript client inside your node_modules.

The Developer Experience

Prisma acts like an over-protective pair of guardrails. If you try to query db.user.findMany({ where: { emial: '...' } }), your code editor will throw a bright red underline instantly because it knows emial doesn't exist on the blueprint it generated. It also ships with Prisma Studio, a local spreadsheet GUI that lets non-technical teammates inspect live data safely.

6. Understanding Drizzle

If Prisma is an automatic transmission luxury sedan, Drizzle is a lightweight, manual track car.

The Philosophy

Drizzle operates on a SQL-First mandate: “If you know SQL, you already know Drizzle.” Instead of inventing a custom schema language like Prisma, Drizzle defines tables directly in 100% native TypeScript:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
});

The Architecture

Drizzle does not generate a heavy Rust-engine binary client behind the scenes. It is essentially a thin, zero-overhead type-checker that sits right on top of standard SQL drivers. When you write a Drizzle query, the syntax deliberately mirrors exact SQL syntax, meaning there is practically zero "ORM translation tax" occurring at runtime.

7. Prisma vs. Drizzle

Feature Metric	Prisma ORM	Drizzle ORM
Primary Philosophy	Abstraction-First (Hide the SQL)	SQL-First (Embrace the SQL)
Learning Curve	Extremely low for beginners	Requires solid fundamental SQL knowledge
Runtime Engine	Heavy (Relies on a compiled Rust binary)	Ultra-lightweight (Pure JS/TS functions)
Serverless / Edge	Requires external proxies (Prisma Accelerate)	Native (Runs instantly on Cloudflare/Vercel)
Type Safety	Generated from .prisma schema	Inferred directly from TypeScript code
Query Control	Harder to optimize complex edge-case joins	1:1 control over the generated SQL

8. Database Migrations

Imagine your live app has 50,000 registered users. You realize your Users table needs a phone_number column. You cannot simply log into the production database GUI and click "Add Column". If a user tries to register at the exact second you alter the live table structure, the transaction will crash.

Migrations are Version Control (Git) for your database structure.

1. The Delta: You update your code schema to include phone_number.

2. The Generation: You run a CLI command. The tool looks at your old schema, looks at your new schema, and writes a permanent, timestamped file (e.g., 20260627_add_phone.sql) containing the exact safe SQL instructions: ALTER TABLE "User" ADD COLUMN "phone_number" TEXT;.

3. The Execution: When you deploy your app to production, the server checks a hidden internal table to see which migration files have already been run, and sequentially executes any new ones.

If a deployment breaks production, you roll back the migration, safely reverting the database structure to its previous historical state.

9. Designing Data Models (ERDs)

Before writing code, architects map systems visually using Entity Relationship Diagrams. Relationships define how disparate data tables communicate.

One-to-One (1 : 1)

• The Concept: Entity A belongs to strictly one Entity B.

• Real-world example: A User and their IdentityVerification profile. A human user only has one driver's license upload; that license belongs to one human.

One-to-Many (1 : N)

• The Concept: Entity A can own multiple instances of Entity B, but Entity B belongs to only one Entity A.

• Real-world example: A Blogging Platform. One Author can write 40 different Articles. However, any single Article belongs to strictly one primary Author.

Many-to-Many (N : M)

• The Concept: Entity A can link to many Entity Bs, and Entity B can link to many Entity As.

• Real-world example: An Order and Products. Order #101 contains a Tshirt and a Hat. The Tshirt is also inside Order #102, #108, and #400.

• The Engineering Trick: Relational databases cannot physically draw a Many-to-Many line directly. Architects resolve this by inserting a Join Table (often called OrderItems) in the middle, breaking it into two clean One-to-Many relationships.

10. Choosing the Right Tool

When sitting down to architect a new system, apply this pragmatic heuristic:

• Choose Prisma if: You are building a standard monolithic web application (Next.js, NestJS, Express), you are working with a cross-functional team of varying backend experience levels, or your primary startup goal is maximum velocity to reach an MVP. The safety net of Prisma's DX is unmatched for rapid iteration.

• Choose Drizzle if: You are building on high-concurrency Serverless / Edge infrastructure (Cloudflare Workers, Bun, Deno), your app relies on deeply complex, highly customized reporting queries, or you are an experienced engineering team that refuses to let an abstraction layer dictate your database execution plans.