Blog
Full Stack Cloudflare Part 1 — Building an AI Sports Platform with D1, Drizzle, and Remix

DALL·E Illustration of a Database

Full Stack Cloudflare Part 1 — Building an AI Sports Platform with D1, Drizzle, and Remix

March 13, 2025•Tory Briggs•6 min read

Introduction

Welcome to part one of our new series covering Cloudflare as a full-stack development solution. I’m not going to put any guarantees on this working for all, or even most use cases, but it has served us quite well so far. We use Cloudflare from the database layer all the way up to the client — from serving images to storing embeddings in a vector database and everything in between.

First, a brief explainer about PressBox

To understand the technology choices, it would probably help to briefly explain our product. At PressBox we’re building an AI-powered content platform for leagues, teams, and media organizations. The content we aggregate, personalize, and distribute is specifically tailored for each of our customer’s needs. For example, based on the results of a sporting event (everything from surfing to NASCAR) we can create personalized graphics, recaps, newsletters, or even audio summaries that can then be distributed any way you want — from a post on TikTok to a push notification in a mobile app.

In order to build out these specialized features, we needed a production-ready way to test them. At PressBox we have a shared passion for motorsports, particularly F1, and we used this passion to build a production testbed for our platform. That testbed is Trackside. It provides a way for us to dogfood the features we’re building, specialized for motorsports.

Multi-Tenant Data Isolation

D1 is a serverless database built on SQLite, so all of the caveats with choosing SQLite as your database still apply here. For example, if you’re building a write intensive application (tens of thousands of writes per second) you may need to look elsewhere.

The beauty of D1 (and SQLite generally), is that it’s easy to create and work with many databases. Take our monerepo structure for instance:

├── apps/
│   ├── grandstand/                # public facing web app
│   └── race-control/              # Internal CMS
│   └── marketing/                 # public facing sites
└── packages/
    └── api/                       # Shared service layer
        └── contentService/        # API functions
    └── db/                        # Database schemas & migrations
        ├── pickle-ball-data/      # League specific data
        └── surf-data/             # League specific data
        └── ...                    # Add any additional DBs

Each app is a Cloudflare Worker (more on that in another part to this series), which can bind to any number of different databases. This makes it easy for Race Control (our internal CMS) to query across all databases while keeping customer specific data isolated and not in contention with each other.

When we first started working with Cloudflare bindings, I thought they were a real pain. You have to pass around an env object which contains the bindings for each cloudflare service (including D1) you want to interact with. So when you create a new database, you need to make sure the bindings are defined in the wrangler.toml config where you want to use it.

While this can be a pain, it’s also very powerful. We have the flexibility to bind one database to the worker that powers our marketing site versus multiple databases which are used in Race Control.

API Services and Drizzle

We use the Drizzle ORM to model and access our database. In general, it’s a very light layer on top of our D1 databases and allows for lower level sql queries when needed. Here is an example of what a table might look like in Drizzle:

export const venues = table("venues", {
  id: text("id", { length: 36 })
    .primaryKey()
    .$defaultFn(() => ulidx()),
  name: text("name").notNull(),
  imageUrl: text("image_url"),
  location: text("location"),
  yearOpened: integer("year_opened"),
});

From this table definition Drizzle can generate migration files you can apply to the database. One of the best parts is that it also generates types for all of the table definitions that can then be used in an API. Speaking of APIs…

If you take another look at our monorepo structure above, you’ll notice we have an api package. This package contains a number of shared services that can be used across other service apis or apps. Pretty much anywhere we have access to the env Cloudflare object.

Here’s a small snippet of what one of those service apis might look like, a simple CRUD interface for a “venue”:

import { eq } from "drizzle-orm";
import { getLeagueDataDatabase, leagueDataSchema } from "@trackside/db";
import { Venue, VenueInsertSchema } from "@/src/models";

export function createVenueApi(env: any) {
  const db = getLeagueDataDatabase(env);
  const { venues } = leagueDataSchema;

  return {
    async getById(id: string): Promise<Venue | undefined> {
      return db.query.venues.findFirst({
        where: eq(venues.id, id),
      });
    },

    async getAll(): Promise<Venue[]> {
      return db.query.venues.findMany({
        orderBy: [venues.name],
      });
    },

    async createVenue(data: Record<string, any>): Promise<Venue> {
      const validatedData = VenueInsertSchema.parse(data);
      const result = await db.insert(venues).values(validatedData).returning();
      return result[0];
    },

    async updateVenue(id: string, updates: Record<string, any>) {
      const result = await db
        .update(venues)
        .set(updates)
        .where(eq(venues.id, id))
        .returning();
      return result[0];
    },

    async deleteVenue(id: string) {
      await db.delete(venues).where(eq(venues.id, id));
    },
  };
}

This provides us a type-safe API for accessing the basic CRUD functions for a model. This is a simple example, but you can add more complex business logic to this layer as needed by your application. For example, when a venue is updated, we could publish a message to queue, upload a document, or send a slack notification.

How do we access this data from a client app?

Where this all comes together is in how we use Remix (and more recently React Router 7) in our client apps. Remix runs great within a Cloudflare Worker and provides convenient ways to access the env object and all necessary bindings.

In both Remix and React Router you define routes. Those routes can have a loader and/or an action function defined. The loader function provides data to the route for GET requests when rendering and only runs on the server (in this case, within a Cloudflare Worker). The action function does the same for other requests, like a POST. This means we can access our D1 database directly using the venue service we defined. Here’s an example loader for our “venues” route in our CMS:

export async function loader(args: LoaderFunctionArgs) {
  const venueApi = createVenueApi(args.context.cloudflare.env);
  const venues = await venueApi.getAll();
  return json({ venues });
}

When we navigate to /venues Remix will call this function and make the {venues} data available to the route component. Here’s a truncated example of what that looks like:

export default function Venues() {
  const { venues } = useLoaderData<typeof loader>();

  return (
    <div className="flex w-full h-full bg-gray-200">
      <table className="w-full border-2 border-gray-300 text-sm">
        <thead>
          <tr className="font-bold bg-gray-200">
            <th className="p-2">Name</th>
            <th className="p-2">Location</th>
            <th className="p-2">Year Opened</th>
            <th className="p-2"></th>
          </tr>
        </thead>
        <tbody>
          {venues.map((venue) => (
            <tr key={venue.id} className="border-b-[1px] border-gray-300">
              <td className="p-2 align-top">{venue.name}</td>
              <td className="p-2 align-top">{venue.location}</td>
              <td className="p-2 align-top">{venue.yearOpened}</td>
              <td className="p-2 text-sm underline text-red-400 align-top">
                <Link
                  to={`/league-data/venues/${venue.id}`}
                  className="mr-5"
                  preventScrollReset={true}
                >
                  Details
                </Link>
                <Link
                  to={`/league-data/venues/${venue.id}/edit`}
                  preventScrollReset={true}
                >
                  Edit
                </Link>
              </td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

useLoaderData<typeof loader>() (called at the top before returning the component) gives us type safe access to our data over the network. So we have type safety from the client all the way to the database.

Wrap up

The combination of D1, Drizzle, and Remix gives us:

  • Type Safety from Database to UI

  • Edge data access

  • Multi-Tenancy

  • Great developer experience

Using Cloudflare D1 hasn’t been without its growing pains and warts. It’s important to understand what the platform limits are and if they meet your specific use case. At a high-level D1 is designed with multiple databases in mind. If you’d rather throw all of your data into a single bucket, you’re probably better off with Postgres or MySQL. But if you value simplicity and flexibility, I highly recommend giving D1 a try.

March 13, 2025