# "Project: Fresh 2 + PostgreSQL + Drizzle blog"

This tutorial expands [blueprint 1](/en/docs/projects) into concrete steps. For routing, islands, and middleware syntax see [Fresh 2 full-stack development](/en/docs/web/fresh); this page covers only what the blog adds.

## Goal and final shape

```text
fresh-blog/
├── routes/posts/[slug].tsx      # post detail (server-rendered)
├── routes/index.tsx             # post list
├── routes/admin/posts.tsx       # protected create page + POST
├── routes/admin/_middleware.ts  # access control for the admin area
├── routes/admin/login.tsx       # login form (sets a cookie)
├── islands/PostEditor.tsx       # the only component that hydrates
├── components/
├── src/db/{client,schema}.ts    # Drizzle connection and tables
├── drizzle.config.ts
└── deno.json
```

Phase one ships the list, detail pages, and a protected create flow. Phase two adds Markdown rendering, drafts, images, and search. The create form uses a native `<form method="post">`, so the whole site stays readable and writable without JavaScript.

## Setup

```bash
deno run -Ar jsr:@fresh/init
cd fresh-blog
deno install npm:drizzle-orm npm:drizzle-kit npm:pg npm:@types/pg
```

Run PostgreSQL locally with Docker and put the connection string in `.env`:

```bash
docker run --name blog-pg -e POSTGRES_PASSWORD=dev-only -p 5432:5432 -d postgres
```

```bash title=".env"
DATABASE_URL=postgresql://postgres:dev-only@localhost:5432/postgres
```

<Callout type="warn" title="Drizzle needs node_modules">
Running drizzle-kit under Deno requires `--node-modules-dir` (the approach from the official Deno + Drizzle tutorial). See the task definitions below.
</Callout>

## Milestone 1: list, detail, protected create

### Schema and migrations

```ts title="src/db/schema.ts"
import { boolean, pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: serial().primaryKey(),
  slug: text().notNull().unique(),
  title: text().notNull(),
  body: text().notNull(),
  published: boolean().notNull().default(false),
  createdAt: timestamp().notNull().defaultNow(),
});
```

The `unique()` on `slug` is an acceptance point: duplicate slugs must be rejected by the database, not by an application-level check.

```ts title="src/db/client.ts"
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({ connectionString: Deno.env.get("DATABASE_URL") });
export const db = drizzle(pool);
```

```ts title="drizzle.config.ts"
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: { url: Deno.env.get("DATABASE_URL")! },
});
```

Add these to `tasks` in `deno.json` (leave the template's `dev` and `check` untouched):

```json title="deno.json (excerpt)"
{
  "tasks": {
    "db:generate": "deno run -A --node-modules-dir npm:drizzle-kit generate",
    "db:migrate": "deno run -A --env --node-modules-dir npm:drizzle-kit migrate"
  }
}
```

`-A` is granted only to the locally trusted migration tool (drizzle-kit needs to read config, connect to the database, and write SQL files); it is not the application's runtime permission set. Configure the production process with its own minimal permissions.

`--env` makes Deno load `.env`. Run `deno task db:generate` first, review the SQL under `drizzle/`, then apply it with `deno task db:migrate`.

### Data-access functions

Keep queries as plain functions shared by routes and tests:

```ts title="src/db/posts.ts"
import { desc, eq } from "drizzle-orm";
import { db } from "./client.ts";
import { posts } from "./schema.ts";

export function listPublishedPosts() {
  return db.select().from(posts)
    .where(eq(posts.published, true))
    .orderBy(desc(posts.createdAt));
}

export function getPostBySlug(slug: string) {
  return db.select().from(posts).where(eq(posts.slug, slug)).limit(1);
}

export async function createPost(input: { slug: string; title: string; body: string }) {
  // Phase one publishes on submit; the draft flow arrives in milestone 2
  await db.insert(posts).values({ ...input, published: true });
}
```

### Routes

```tsx title="routes/posts/[slug].tsx"
import { define } from "@/utils.ts";
import { HttpError } from "fresh";
import { getPostBySlug } from "@/src/db/posts.ts";

export default define.page(async (ctx) => {
  const [post] = await getPostBySlug(ctx.params.slug);
  if (!post || !post.published) {
    // Hand it to Fresh's error handling: the response status is a real 404, not a 200 "not found" page
    throw new HttpError(404);
  }
  return (
    <main>
      <h1>{post.title}</h1>
      <article>{post.body}</article>
    </main>
  );
});
```

The list page `routes/index.tsx` likewise calls `listPublishedPosts()` inside `define.page` and renders a `<ul>`. Both are server components; the browser receives no JavaScript for them.

The create page uses a native form plus a POST handler, redirecting with 303 on success:

```tsx title="routes/admin/posts.tsx"
import { define } from "@/utils.ts";
import { createPost } from "@/src/db/posts.ts";

export const handlers = define.handlers({
  async POST(ctx) {
    const form = await ctx.req.formData();
    const slug = form.get("slug")?.toString() ?? "";
    const title = form.get("title")?.toString() ?? "";
    const body = form.get("body")?.toString() ?? "";
    if (!/^[a-z0-9-]+$/.test(slug) || !title || !body) {
      return new Response("invalid input", { status: 400 });
    }
    try {
      await createPost({ slug, title, body });
    } catch (err) {
      // Map only unique-constraint violations (SQLSTATE 23505) to 409; connection failures and the like are 500
      if (err && typeof err === "object" && "code" in err && err.code === "23505") {
        return new Response("slug already exists", { status: 409 });
      }
      console.error("createPost failed", err);
      return new Response("internal error", { status: 500 });
    }
    return new Response(null, {
      status: 303,
      headers: { location: `/posts/${slug}` },
    });
  },
});

export default define.page<typeof handlers>(function NewPost() {
  return (
    <main>
      <h1>New post</h1>
      <form method="post">
        <input name="slug" required pattern="[a-z0-9-]+" />
        <input name="title" required />
        <textarea name="body" required />
        <button type="submit">Publish</button>
      </form>
    </main>
  );
});
```

### Protecting the admin area

A `_middleware.ts` in a subdirectory applies only to that directory's routes:

Browser forms cannot attach an `Authorization` header, so the middleware accepts both a cookie (after browser login) and a Bearer token (for API/curl):

```ts title="routes/admin/_middleware.ts"
import { define } from "@/utils.ts";
import { getCookies } from "jsr:@std/http/cookie";

const token = Deno.env.get("ADMIN_TOKEN");

export default define.middleware((ctx) => {
  if (new URL(ctx.req.url).pathname === "/admin/login") return ctx.next();
  const bearer = !!token && ctx.req.headers.get("authorization") === `Bearer ${token}`;
  const cookie = !!token && getCookies(ctx.req.headers)["admin_token"] === token;
  if (!bearer && !cookie) {
    return new Response("Unauthorized", { status: 401 });
  }
  return ctx.next();
});
```

The login page verifies the password and sets an `HttpOnly; SameSite=Lax` cookie — with SameSite=Lax, cross-site POSTs do not carry the cookie, which is the CSRF floor for this placeholder scheme:

```tsx title="routes/admin/login.tsx"
import { define } from "@/utils.ts";

export const handlers = define.handlers({
  async POST(ctx) {
    const form = await ctx.req.formData();
    const token = Deno.env.get("ADMIN_TOKEN");
    if (!token || form.get("token") !== token) {
      return new Response("Unauthorized", { status: 401 });
    }
    return new Response(null, {
      status: 303,
      headers: {
        location: "/admin/posts",
        "set-cookie": `admin_token=${token}; Path=/admin; HttpOnly; SameSite=Lax`,
      },
    });
  },
});

export default define.page<typeof handlers>(function Login() {
  return (
    <main>
      <h1>Admin login</h1>
      <form method="post">
        <input name="token" type="password" required />
        <button type="submit">Log in</button>
      </form>
    </main>
  );
});
```

<Callout type="warn" title="This is only placeholder authentication">
A shared password plus a cookie is only a starting point for a single-person project. Before launch, switch to a real session scheme (the official docs have a Session management example in the Examples section) and proper CSRF protection — the official Fresh plugin list includes a csrf plugin.
</Callout>

## Milestone 2: Markdown, drafts, images, search

- **Markdown**: render in server components (see the official Rendering Markdown example); never create an island just for rendering.
- **Drafts**: the `published` column is already in the schema; filter with `where(eq(posts.published, true))` in list and detail, and add publish/unpublish actions in admin.
- **Images**: small sites can use `static/`; user uploads go through a separate protected endpoint with MIME and size validation (see the validation approach in the [image API tutorial](/en/docs/projects/image-api)).
- **Search**: start with a `routes/search.tsx` page reading query params and matching title/body via Drizzle's `ilike`; move to PostgreSQL full-text indexes when volume demands it.
- **Editor**: only `islands/PostEditor.tsx` (preview, keyboard shortcuts) hydrates. Post bodies, navigation, and lists stay server components.

## Test strategy

- **Data layer**: run `deno test` against a dedicated test database, covering slug-constraint conflicts and draft filtering.
- **Handlers**: Fresh's official testing pattern mounts route handlers on an in-memory `App` and drives them with Web `Request` objects:

```ts title="routes/admin/posts_test.ts"
import { App } from "fresh";
import { handlers } from "./posts.tsx";

Deno.test("POST rejects invalid slug", async () => {
  const handler = new App().post("/admin/posts", handlers.POST).handler();
  const form = new FormData();
  form.set("slug", "Bad Slug!");
  form.set("title", "t");
  form.set("body", "b");
  const res = await handler(
    new Request("http://localhost/admin/posts", { method: "POST", body: form }),
  );
  if (res.status !== 400) throw new Error(`expected 400, got ${res.status}`);
});
```

- **No-JS acceptance**: walk list → detail → create with JavaScript disabled (or plain `curl`).

## Deployment

Fresh deploys to the new Deno Deploy: after attaching PostgreSQL, the platform injects `DATABASE_URL`, `PGHOST`, and friends, and migrations can run as a pre-deploy command before a revision receives traffic — which is exactly what "migrations are a separate release step" means. Details in [Databases, cron, and timelines](/en/docs/deploy/data-and-cron). Run `deno task check` and the full test suite before deploying.

## Acceptance checklist

<Checklist id="fresh-blog-acceptance" items={[
  "List and detail pages readable and the create form submittable with JavaScript disabled",
  "Duplicate slugs rejected by the database unique constraint with a 409 response",
  "No component besides the PostEditor island ships JavaScript to the browser",
  "SQL from drizzle-kit generate is reviewed; migrate runs as a separate release step",
  "Unauthenticated requests to admin routes return 401",
  "deno fmt --check, deno lint, deno task check, and deno test all pass"
]} />

Official references: [Fresh Getting started](https://usefresh.dev/docs/getting-started), [Fresh Forms](https://usefresh.dev/docs/advanced/forms), [Fresh Testing](https://usefresh.dev/docs/testing), [Deno + Drizzle tutorial](https://docs.deno.com/examples/tutorials/drizzle/), [Drizzle with PostgreSQL](https://orm.drizzle.team/docs/get-started/postgresql-new).