Project Milestone: The Dynamic Backend | Cloudflare for Developers

Previously in this course, we covered Introduction to D1 SQL Database and Integrating R2 with Workers. Now that you can store files in R2 and manage relational data in D1, it’s time to unify them.

In this lesson, we will build a "Dynamic Backend" that treats your database as the source of truth for your file metadata, ensuring that every asset served from R2 has a corresponding record in D1.

The Architecture of a Unified Backend

When building a production-grade application, you rarely store assets in isolation. You need context: Who uploaded this? When? What is its MIME type? Is it public or private? By storing this metadata in D1, you gain the ability to search, filter, and audit your R2 assets without scanning the bucket itself.

How Data Flows

1. The Request: A client requests an asset by ID via an API endpoint.
2. The Lookup: The Worker queries D1 for the file record (metadata) matching that ID.
3. The Retrieval: If found, the Worker uses the file key from the database to fetch the actual bytes from R2.
4. The Response: The Worker streams the file back to the client with the correct headers.

Worked Example: Building the Asset API

First, ensure your wrangler.toml includes bindings for both your D1 database and your R2 bucket.

TOML
[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "your-database-id"

[[r2_buckets]]
binding = "MY_BUCKET"
bucket_name = "my-app-assets"

Now, we’ll implement a GET /assets/:id route in our Worker.

JAVASCRIPT
export default {
  async fetch(request, env) {
    const url = new URL(request.url);
    const id = url.pathname.split(CE9178">'/').pop();

    if (url.pathname.startsWith(CE9178">'/assets/')) {
      // 1. Fetch metadata from D1
      const stmt = env.DB.prepare("SELECT * FROM files WHERE id = ?");
      const fileRecord = await stmt.bind(id).first();

      if (!fileRecord) {
        return new Response("Asset not found", { status: 404 });
      }

      // 2. Fetch object from R2 using the key stored in D1
      const object = await env.MY_BUCKET.get(fileRecord.r2_key);

      if (!object) {
        return new Response("File missing from storage", { status: 404 });
      }

      // 3. Return the asset with appropriate headers
      return new Response(object.body, {
        headers: {
          "Content-Type": fileRecord.mime_type,
          "Cache-Control": "public, max-age=86400"
        }
      });
    }

    return new Response("Not Found", { status: 404 });
  }
};

Verifying Data Integrity

A common issue in distributed systems is the "Orphaned Asset"—a file in R2 with no corresponding record in D1, or vice versa. To prevent this, always wrap your logic in a transaction or perform a check before deletion.

Practice Exercise:

1. Create a POST /upload endpoint.
2. Inside the handler, upload the file to R2 first.
3. If the upload succeeds, execute an INSERT statement in D1 with the file metadata and the R2 key.
4. If the database insertion fails, include logic to delete the object from R2 to keep your storage clean.

Common Pitfalls

• N+1 Queries: Do not fetch a list of items from D1 and then perform a separate R2 get() for every single item inside a loop. This will kill your performance. Always fetch metadata in batches or only when requested by a specific ID.
• MIME Type Mismatch: R2 objects do not always store metadata perfectly if it wasn't set during the initial upload. Always store the mime_type string in D1 so your API can serve the correct Content-Type header.
• Case Sensitivity: Ensure your R2 keys are handled consistently. While R2 keys are case-sensitive, SQL queries in D1 might be case-insensitive depending on your collation settings. Stick to lowercase IDs for sanity.

FAQ

Q: Can I use D1 to store the actual file data? A: No. D1 is designed for relational metadata (up to 10MB per row, but practically much less). Use R2 for binary blobs like images, videos, or PDFs.

Q: Does this count as two billable operations? A: Yes, you are performing a D1 read and an R2 read. Monitor your usage in the Cloudflare Dashboard to stay within your plan limits.

Recap

You have now successfully integrated your primary storage (R2) with your relational database (D1). By using D1 as an index for your R2 objects, you’ve built a robust, queryable backend capable of serving dynamic content. This is the foundation of the production-ready application we are building throughout this course.

Up next: We will implement authentication to protect these endpoints.