Build a Personal Blog — Phase 3: PostgreSQL + Drizzle ORM

This is Phase 3 of the Build a Personal Blog series. Your Next.js blog already renders MDX posts and looks great. Now it's time to add a database — not to replace MDX content, but to track dynamic data: how many people read each post, who subscribed to your newsletter, and (optionally) reader comments.

Series: Build a Personal Blog — Complete Roadmap
Previous: Phase 2 — MDX On-Demand Rendering
Next: Phase 4 — Blog Features: Tags, Search & Pagination
Source Code: GitHub — personal-blog-phase-3

Why Add a Database at All?

MDX files are great for blog content — they're version-controlled, easy to write, and zero-latency to read from disk. But some data is dynamic: it changes every time a user visits the site. MDX can't handle that.

Feature	MDX (File System)	PostgreSQL (Database)
Blog post content	✅ Perfect	❌ Overkill
Post view counts	❌ Can't update a file per visit	✅ `UPDATE post_views SET count = count + 1`
Newsletter subscribers	❌ No	✅ Insert, verify, query
Comment threads	❌ No	✅ Insert, approve, query
Likes / reactions	❌ No	✅ Upsert

The pattern is called a hybrid approach: MDX for content, PostgreSQL for user-generated or interaction-driven data. Most serious blogs and CMS platforms work exactly this way.

What You'll Build

By the end of this phase:

✅ PostgreSQL running locally via Docker Compose
✅ Drizzle ORM installed and configured
✅ Three schemas: post_views, subscribers, comments
✅ Migrations generated and applied with drizzle-kit
✅ CRUD helper functions in lib/db/
✅ A live view counter on every blog post page
✅ A working subscribe form that writes to the database

Time commitment: 2–4 hours
Prerequisites: Phase 1: Next.js + ShadCN/UI Setup and Phase 2: MDX On-Demand Rendering

1. Start PostgreSQL with Docker Compose

Instead of installing PostgreSQL directly on your machine, use Docker. It keeps your dev environment clean and mirrors production.

If you don't have docker-compose.yml yet, create one at the project root:

# docker-compose.yml
services:
  db:
    image: postgres:17-alpine
    container_name: blog-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: blog_user
      POSTGRES_PASSWORD: blog_password
      POSTGRES_DB: blog_db
    ports:
      - "5432:5432"
    volumes:
      - blog_db_data:/var/lib/postgresql/data
 
volumes:
  blog_db_data:

Start it:

docker compose up -d db

Verify it's running:

docker compose ps
# NAME       IMAGE               STATUS
# blog-db    postgres:17-alpine  Up 10 seconds

Connect to it to confirm:

docker exec -it blog-db psql -U blog_user -d blog_db
# blog_db=# \l
# blog_db=# \q

2. Install Drizzle ORM

Why Drizzle over Prisma / TypeORM?

Before installing, here's the short answer:

	Drizzle ORM	Prisma	TypeORM
Type safety	Schema-first TypeScript	Schema-first (owns types)	Decorator-based
Bundle size	~30 KB	~200 KB+ (engine + client)	~250 KB
SQL control	Full SQL with type safety	Limited raw SQL escape hatches	Full raw SQL possible
Migrations	`drizzle-kit generate` → plain SQL files	`prisma migrate dev`	Class-based
Edge-compatible	✅ Works on Cloudflare/Vercel Edge	⚠️ Limited (Prisma Accelerate)	❌ Typically no
Learning curve	Low (looks like SQL)	Medium	High

Drizzle's killer feature: your schema is TypeScript. There's no separate .prisma language to learn. If you know SQL, you already know 80% of Drizzle.

Install packages

npm install drizzle-orm postgres
npm install -D drizzle-kit

drizzle-orm — the ORM runtime
postgres — the PostgreSQL driver (lighter than pg)
drizzle-kit — CLI for generating and running migrations

3. Environment Variables

Add your DATABASE_URL to .env.local:

# .env.local
DATABASE_URL=postgres://blog_user:blog_password@localhost:5432/blog_db

Production note: On your VPS, this will point to a Dockerized PostgreSQL on the same host — or to a managed service like Neon, Supabase, or Railway. You'll replace this value when you deploy in Phase 6.

Make sure .env.local is in your .gitignore — it should be there by default from create-next-app.

4. Drizzle Configuration

Create drizzle.config.ts at the project root:

// drizzle.config.ts
import type { Config } from "drizzle-kit";
 
export default {
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Key fields:

schema — where your table definitions live
out — where Drizzle generates SQL migration files
dialect — we're using PostgreSQL
dbCredentials.url — reads from your .env.local

5. Schema Design

Create the lib/db/ directory and the schema file:

mkdir -p lib/db
touch lib/db/schema.ts
touch lib/db/index.ts

5.1 Post Views Table

// lib/db/schema.ts
import { pgTable, text, integer, timestamp, boolean, serial, varchar } from "drizzle-orm/pg-core";
 
// ───────────────────────────────────────────────
// post_views
// One row per blog post slug. view_count increments on every visit.
// ───────────────────────────────────────────────
export const postViews = pgTable("post_views", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  viewCount: integer("view_count").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

5.2 Subscribers Table

// lib/db/schema.ts  (continued — add below postViews)
 
// ───────────────────────────────────────────────
// subscribers
// Email newsletter list with double opt-in verification.
// ───────────────────────────────────────────────
export const subscribers = pgTable("subscribers", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  verifiedAt: timestamp("verified_at"),
  verifyToken: varchar("verify_token", { length: 64 }).notNull(),
  unsubscribeToken: varchar("unsubscribe_token", { length: 64 }).notNull(),
  isActive: boolean("is_active").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

5.3 Comments Table (Optional)

// lib/db/schema.ts  (continued)
 
// ───────────────────────────────────────────────
// comments
// Reader comments per post — requires moderation approval.
// ───────────────────────────────────────────────
export const comments = pgTable("comments", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull(),
  authorName: varchar("author_name", { length: 100 }).notNull(),
  authorEmail: varchar("author_email", { length: 255 }),
  content: text("content").notNull(),
  isApproved: boolean("is_approved").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

Complete schema file

Here's the full lib/db/schema.ts with all three tables together:

// lib/db/schema.ts
import {
  pgTable,
  text,
  integer,
  timestamp,
  boolean,
  serial,
  varchar,
} from "drizzle-orm/pg-core";
 
export const postViews = pgTable("post_views", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  viewCount: integer("view_count").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});
 
export const subscribers = pgTable("subscribers", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  verifiedAt: timestamp("verified_at"),
  verifyToken: varchar("verify_token", { length: 64 }).notNull(),
  unsubscribeToken: varchar("unsubscribe_token", { length: 64 }).notNull(),
  isActive: boolean("is_active").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});
 
export const comments = pgTable("comments", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull(),
  authorName: varchar("author_name", { length: 100 }).notNull(),
  authorEmail: varchar("author_email", { length: 255 }),
  content: text("content").notNull(),
  isApproved: boolean("is_approved").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

6. Database Client

Set up the Drizzle client as a singleton — important in Next.js where the dev server restarts frequently:

// lib/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";
 
// Prevent multiple connections in development hot-reload
const globalForDb = globalThis as unknown as {
  _db: ReturnType<typeof drizzle> | undefined;
  _sql: ReturnType<typeof postgres> | undefined;
};
 
function createDb() {
  if (!process.env.DATABASE_URL) {
    throw new Error("DATABASE_URL environment variable is not set");
  }
 
  const sql = postgres(process.env.DATABASE_URL, {
    max: 10, // connection pool size
  });
 
  return drizzle(sql, { schema });
}
 
export const db =
  globalForDb._db ??
  (globalForDb._db = createDb());

Why the globalThis trick? In development, Next.js hot-reloads modules but doesn't restart the Node process. Without this guard, every file change would open a new connection pool and you'd eventually hit PostgreSQL's connection limit. The singleton pattern keeps one pool alive for the process lifetime.

7. Run Migrations

With the schema defined, generate the SQL migration files:

npx drizzle-kit generate

You'll see output like:

Generating migration...
  → drizzle/0000_initial_schema.sql

Inspect the generated SQL — it should look like:

-- drizzle/0000_initial_schema.sql
CREATE TABLE IF NOT EXISTS "post_views" (
  "id" serial PRIMARY KEY NOT NULL,
  "slug" varchar(255) NOT NULL,
  "view_count" integer DEFAULT 0 NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "post_views_slug_unique" UNIQUE("slug")
);
 
CREATE TABLE IF NOT EXISTS "subscribers" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255) NOT NULL,
  "verified_at" timestamp,
  "verify_token" varchar(64) NOT NULL,
  "unsubscribe_token" varchar(64) NOT NULL,
  "is_active" boolean DEFAULT false NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "subscribers_email_unique" UNIQUE("email")
);
 
CREATE TABLE IF NOT EXISTS "comments" (
  "id" serial PRIMARY KEY NOT NULL,
  "slug" varchar(255) NOT NULL,
  "author_name" varchar(100) NOT NULL,
  "author_email" varchar(255),
  "content" text NOT NULL,
  "is_approved" boolean DEFAULT false NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL
);

Apply the migration to your local database:

npx drizzle-kit migrate

Confirm the tables exist:

docker exec -it blog-db psql -U blog_user -d blog_db -c "\dt"
#            List of relations
#  Schema |    Name     | Type  |   Owner
# --------+-------------+-------+-----------
#  public | comments    | table | blog_user
#  public | post_views  | table | blog_user
#  public | subscribers | table | blog_user

Add the migration scripts to package.json for convenience:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push"
  }
}

db:generate — create a new SQL migration file from schema changes
db:migrate — apply pending migrations
db:studio — open Drizzle Studio (visual DB browser at http://localhost:4983)
db:push — skip migrations and push schema directly (dev-only shortcut)

8. CRUD Helpers

Create lib/db/queries.ts with all the database operations your blog needs:

// lib/db/queries.ts
import { eq, desc, and } from "drizzle-orm";
import { db } from "./index";
import { postViews, subscribers, comments } from "./schema";
import crypto from "crypto";
 
// ─────────────────────────────────────────────────────────────────────────────
// Post Views
// ─────────────────────────────────────────────────────────────────────────────
 
/**
 * Get the current view count for a post.
 * Returns 0 if the post has never been viewed.
 */
export async function getPostViewCount(slug: string): Promise<number> {
  const result = await db
    .select({ viewCount: postViews.viewCount })
    .from(postViews)
    .where(eq(postViews.slug, slug))
    .limit(1);
 
  return result[0]?.viewCount ?? 0;
}
 
/**
 * Increment the view count for a post by 1.
 * Creates the row if it doesn't exist yet (upsert pattern).
 */
export async function incrementPostView(slug: string): Promise<void> {
  await db
    .insert(postViews)
    .values({ slug, viewCount: 1 })
    .onConflictDoUpdate({
      target: postViews.slug,
      set: {
        viewCount: sql`${postViews.viewCount} + 1`,
        updatedAt: new Date(),
      },
    });
}
 
// ─────────────────────────────────────────────────────────────────────────────
// Newsletter Subscribers
// ─────────────────────────────────────────────────────────────────────────────
 
function generateToken(): string {
  return crypto.randomBytes(32).toString("hex");
}
 
/**
 * Create a new subscriber (unverified by default).
 * Returns the verification token to send in the email.
 */
export async function createSubscriber(
  email: string
): Promise<{ verifyToken: string; unsubscribeToken: string } | null> {
  const verifyToken = generateToken();
  const unsubscribeToken = generateToken();
 
  try {
    await db.insert(subscribers).values({
      email,
      verifyToken,
      unsubscribeToken,
      isActive: false,
    });
    return { verifyToken, unsubscribeToken };
  } catch (error) {
    // Email already exists
    return null;
  }
}
 
/**
 * Mark a subscriber as verified and active.
 */
export async function verifySubscriber(verifyToken: string): Promise<boolean> {
  const result = await db
    .update(subscribers)
    .set({ isActive: true, verifiedAt: new Date() })
    .where(
      and(
        eq(subscribers.verifyToken, verifyToken),
        eq(subscribers.isActive, false)
      )
    )
    .returning({ id: subscribers.id });
 
  return result.length > 0;
}
 
/**
 * Deactivate a subscriber (unsubscribe).
 */
export async function unsubscribeByToken(
  unsubscribeToken: string
): Promise<boolean> {
  const result = await db
    .update(subscribers)
    .set({ isActive: false })
    .where(eq(subscribers.unsubscribeToken, unsubscribeToken))
    .returning({ id: subscribers.id });
 
  return result.length > 0;
}
 
/**
 * Get all active (verified) subscribers for sending the newsletter.
 */
export async function getActiveSubscribers(): Promise<
  { email: string; unsubscribeToken: string }[]
> {
  return db
    .select({ email: subscribers.email, unsubscribeToken: subscribers.unsubscribeToken })
    .from(subscribers)
    .where(eq(subscribers.isActive, true));
}
 
// ─────────────────────────────────────────────────────────────────────────────
// Comments
// ─────────────────────────────────────────────────────────────────────────────
 
/**
 * Get all approved comments for a post.
 */
export async function getApprovedComments(slug: string) {
  return db
    .select()
    .from(comments)
    .where(and(eq(comments.slug, slug), eq(comments.isApproved, true)))
    .orderBy(desc(comments.createdAt));
}
 
/**
 * Submit a new comment (requires moderation before it shows).
 */
export async function createComment(data: {
  slug: string;
  authorName: string;
  authorEmail?: string;
  content: string;
}): Promise<void> {
  await db.insert(comments).values({
    ...data,
    isApproved: false, // always pending moderation
  });
}

Note on the sql tagged template: The incrementPostView function uses Drizzle's sql helper for the viewCount + 1 expression. Make sure to import it:

import { eq, desc, and, sql } from "drizzle-orm";

9. Post View Counter

Now wire everything up so each blog post automatically records a view when someone opens it.

9.1 API Route

Create an API route that handles the view increment:

// app/api/views/[slug]/route.ts
import { NextRequest, NextResponse } from "next/server";
import { getPostViewCount, incrementPostView } from "@/lib/db/queries";
 
export async function GET(
  _req: NextRequest,
  { params }: { params: { slug: string } }
) {
  const { slug } = params;
  const count = await getPostViewCount(slug);
  return NextResponse.json({ views: count });
}
 
export async function POST(
  _req: NextRequest,
  { params }: { params: { slug: string } }
) {
  const { slug } = params;
  await incrementPostView(slug);
  const count = await getPostViewCount(slug);
  return NextResponse.json({ views: count });
}

9.2 ViewCounter Component

Create a client component that calls the API and displays the count:

// components/ViewCounter.tsx
"use client";
 
import { useEffect, useState } from "react";
import { Eye } from "lucide-react";
 
interface ViewCounterProps {
  slug: string;
  shouldCount?: boolean; // false on list pages, true on post detail
}
 
export function ViewCounter({ slug, shouldCount = false }: ViewCounterProps) {
  const [views, setViews] = useState<number | null>(null);
 
  useEffect(() => {
    const method = shouldCount ? "POST" : "GET";
 
    fetch(`/api/views/${slug}`, { method })
      .then((res) => res.json())
      .then((data) => setViews(data.views))
      .catch(() => setViews(null));
  }, [slug, shouldCount]);
 
  if (views === null) return null;
 
  return (
    <span className="flex items-center gap-1 text-sm text-muted-foreground">
      <Eye className="h-4 w-4" />
      {views.toLocaleString()} views
    </span>
  );
}

9.3 Add ViewCounter to the Post Page

In your post detail page, add the ViewCounter with shouldCount={true}:

// app/blog/[slug]/page.tsx  (partial)
import { ViewCounter } from "@/components/ViewCounter";
 
// Inside the component JSX:
<div className="flex items-center gap-4 text-sm text-muted-foreground">
  <time dateTime={post.frontmatter.date}>
    {formatDate(post.frontmatter.date)}
  </time>
  <ViewCounter slug={params.slug} shouldCount={true} />
</div>

On the blog list page, show the view count without incrementing:

// Inside PostCard.tsx or wherever you render post cards:
<ViewCounter slug={post.slug} shouldCount={false} />

9.4 Test it

Open a blog post in your browser
Check the terminal — you should see a POST /api/views/[slug] request
Refresh the page — the count should be 2
Check the database directly:

docker exec -it blog-db psql -U blog_user -d blog_db \
  -c "SELECT slug, view_count FROM post_views;"
#          slug          | view_count
# -----------------------+------------
#  my-first-post         |          2

Add a simple subscribe form that inserts into the subscribers table.

10.1 API Route

// app/api/subscribe/route.ts
import { NextRequest, NextResponse } from "next/server";
import { createSubscriber } from "@/lib/db/queries";
 
export async function POST(req: NextRequest) {
  const { email } = await req.json();
 
  if (!email || !email.includes("@")) {
    return NextResponse.json({ error: "Invalid email" }, { status: 400 });
  }
 
  const result = await createSubscriber(email);
 
  if (!result) {
    // Email already registered — don't reveal this for privacy
    return NextResponse.json({ message: "Check your inbox!" });
  }
 
  // TODO Phase 5: send verification email with result.verifyToken
  // For now, log the token for local testing
  console.log(`Verification token for ${email}: ${result.verifyToken}`);
 
  return NextResponse.json({ message: "Check your inbox!" });
}

10.2 SubscribeForm Component

// components/SubscribeForm.tsx
"use client";
 
import { useState } from "react";
import { Button } from "@/components/ui/button";
import { Input } from "@/components/ui/input";
 
export function SubscribeForm() {
  const [email, setEmail] = useState("");
  const [status, setStatus] = useState<"idle" | "loading" | "success" | "error">("idle");
  const [message, setMessage] = useState("");
 
  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault();
    setStatus("loading");
 
    try {
      const res = await fetch("/api/subscribe", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ email }),
      });
 
      const data = await res.json();
 
      if (res.ok) {
        setStatus("success");
        setMessage(data.message);
        setEmail("");
      } else {
        setStatus("error");
        setMessage(data.error ?? "Something went wrong. Try again.");
      }
    } catch {
      setStatus("error");
      setMessage("Network error. Please try again.");
    }
  }
 
  return (
    <div className="rounded-xl border bg-card p-6 shadow-sm">
      <h3 className="text-lg font-semibold">Stay in the loop</h3>
      <p className="mt-1 text-sm text-muted-foreground">
        New posts delivered to your inbox. No spam, unsubscribe any time.
      </p>
 
      {status === "success" ? (
        <p className="mt-4 text-sm font-medium text-green-600">
          ✅ {message}
        </p>
      ) : (
        <form onSubmit={handleSubmit} className="mt-4 flex gap-2">
          <Input
            type="email"
            placeholder="you@example.com"
            value={email}
            onChange={(e) => setEmail(e.target.value)}
            required
            disabled={status === "loading"}
            className="flex-1"
          />
          <Button type="submit" disabled={status === "loading"}>
            {status === "loading" ? "Subscribing…" : "Subscribe"}
          </Button>
        </form>
      )}
 
      {status === "error" && (
        <p className="mt-2 text-sm text-destructive">{message}</p>
      )}
    </div>
  );
}

Add SubscribeForm to your blog post page or footer — wherever you want readers to see it.

11. Drizzle Studio

Drizzle ships with a browser-based database explorer. Run it in a separate terminal:

npm run db:studio

Open https://local.drizzle.studio and you'll see your tables, rows, and can run queries interactively. Extremely handy for debugging data issues during development.

12. Project Structure After Phase 3

Your project should now look like this:

my-blog/
├── app/
│   ├── api/
│   │   ├── views/
│   │   │   └── [slug]/
│   │   │       └── route.ts       ← view count API
│   │   └── subscribe/
│   │       └── route.ts           ← newsletter subscription API
│   └── blog/
│       └── [slug]/
│           └── page.tsx
├── components/
│   ├── ViewCounter.tsx             ← NEW
│   └── SubscribeForm.tsx           ← NEW
├── drizzle/
│   └── 0000_initial_schema.sql    ← generated migration
├── lib/
│   └── db/
│       ├── index.ts               ← Drizzle client
│       ├── schema.ts              ← table definitions
│       └── queries.ts             ← CRUD helpers
├── docker-compose.yml
├── drizzle.config.ts
└── .env.local

Common Issues

`DATABASE_URL is not set`

Make sure you're using .env.local (not .env) and that you've restarted the Next.js dev server after adding it.

`connection refused` to PostgreSQL

Check that Docker is running and the container is up:

docker compose ps
docker compose up -d db

`relation "post_views" does not exist`

You generated the migration but forgot to apply it. Run:

npm run db:migrate

Type error on `sql` helper

Make sure you import sql from drizzle-orm, not from postgres:

import { eq, desc, and, sql } from "drizzle-orm"; // ✅

Hot-reload creates too many DB connections

This is the globalThis singleton pattern. If you're still seeing connection warnings, add a max cap to the postgres() client:

const sql = postgres(process.env.DATABASE_URL!, { max: 5 });

Summary

In this phase you:

✅ Started a local PostgreSQL database with Docker Compose
✅ Installed Drizzle ORM and the postgres driver
✅ Designed three schemas: post_views, subscribers, comments
✅ Generated and applied a SQL migration with drizzle-kit
✅ Built a singleton DB client safe for Next.js hot-reload
✅ Wrote CRUD helper functions in lib/db/queries.ts
✅ Added a live view counter to every blog post
✅ Wired up a newsletter subscribe form with API route

The hybrid MDX + PostgreSQL approach gives you the best of both worlds: version-controlled prose in MDX and dynamic user data in a real database — without the overhead of a full CMS.

What's Next

In Phase 4, you'll add tag filtering, full-text search, and pagination to the blog listing — the features readers actually use to discover your content.

Next Post: Phase 4 — Blog Features: Tags, Search & Pagination

Series Index

Post	Title	Status
BLOG-1	Build a Personal Blog — Roadmap	✅ Complete
BLOG-2	Phase 1: Project Setup — Next.js 16 + ShadCN/UI	✅ Complete
BLOG-3	Phase 2: MDX On-Demand Rendering	✅ Complete
BLOG-4	Phase 3: PostgreSQL + Drizzle ORM	✅ You are here
BLOG-5	Phase 4: Tags, Search & Pagination	✅ Complete
BLOG-6	Phase 5: Docker Compose	✅ Complete
BLOG-7	Phase 6: Deploy to Ubuntu VPS on Hostinger	✅ Complete
BLOG-8	Phase 7: Custom Domain Setup on Hostinger	✅ Complete

Build a Personal Blog — Phase 3: PostgreSQL + Drizzle ORM

Why Add a Database at All?

What You'll Build

1. Start PostgreSQL with Docker Compose

2. Install Drizzle ORM

Why Drizzle over Prisma / TypeORM?

Install packages

3. Environment Variables

4. Drizzle Configuration

5. Schema Design

5.1 Post Views Table

5.2 Subscribers Table

5.3 Comments Table (Optional)

Complete schema file

6. Database Client

7. Run Migrations

8. CRUD Helpers

9. Post View Counter

9.1 API Route

9.2 ViewCounter Component

9.3 Add ViewCounter to the Post Page

9.4 Test it

10.1 API Route

10.2 SubscribeForm Component

11. Drizzle Studio

12. Project Structure After Phase 3

Common Issues

`DATABASE_URL is not set`

`connection refused` to PostgreSQL

`relation "post_views" does not exist`

Type error on `sql` helper

Hot-reload creates too many DB connections

Summary

What's Next

Series Index

📬 Subscribe to Newsletter

💬 Comments

Why Add a Database at All?

What You'll Build

1. Start PostgreSQL with Docker Compose

2. Install Drizzle ORM

Why Drizzle over Prisma / TypeORM?

Install packages

3. Environment Variables

4. Drizzle Configuration

5. Schema Design

5.1 Post Views Table

5.2 Subscribers Table

5.3 Comments Table (Optional)

Complete schema file

6. Database Client

7. Run Migrations

8. CRUD Helpers

9. Post View Counter

9.1 API Route

9.2 ViewCounter Component

9.3 Add ViewCounter to the Post Page

9.4 Test it

10. Newsletter Subscribe Form

10.1 API Route

10.2 SubscribeForm Component

11. Drizzle Studio

12. Project Structure After Phase 3

Common Issues

DATABASE_URL is not set

connection refused to PostgreSQL

relation "post_views" does not exist

Type error on sql helper

Hot-reload creates too many DB connections

Summary

What's Next

Series Index

📬 Subscribe to Newsletter

💬 Comments

`DATABASE_URL is not set`

`connection refused` to PostgreSQL

`relation "post_views" does not exist`

Type error on `sql` helper