Getting Started with Cloudflare D1 & Astro 5

Cloudflare D1 is Cloudflare’s native serverless SQL database built on SQLite. In this guide, we explore how dotBlog leverages D1 to store post statistics and reader comments seamlessly at the edge.

Architecture Highlights

1. Prepared SQL Statements: Standard SQLite syntax with Cloudflare D1Database TypeScript bindings.
2. Anti-Spam & Rate Limiting: Client IP SHA-256 hashing to enforce submission rate limits without storing raw PII.
3. Threaded Comments: Efficient parent-child relation querying for 2-level comment threads.

Preparing SQL Statements in Astro API Routes

Here is how D1 binding is queried inside Astro API routes:

import type { APIRoute } from 'astro';

export const POST: APIRoute = async ({ request, locals }) => {
  const db = locals.runtime.env.DB;
  const { slug } = await request.json();

  await db
    .prepare('UPDATE post_stats SET pv = pv + 1 WHERE post_id = (SELECT id FROM posts WHERE slug = ?)')
    .bind(slug)
    .run();

  return new Response(JSON.stringify({ success: true }), {
    headers: { 'Content-Type': 'application/json' },
  });
};

Combining static page generation with edge-driven dynamic endpoints delivers an unbeatable combination of speed and functionality!