Seeding a Cloudflare D1 Database

I started to explore using Cloudflare's D1 database in combination with drizzleORM. One challenge I encountered was seeding the database with data. In my specific case, the data I needed to add manually was the different subscription tiers and some related upgrade packages I set up on Lemonsqueezy.

Since I couldn't find any information about how to go about this I am sharing my approach. I created a small Javascript script which generates UUIDs, before creating the records via the Cloudflare D1 HTTP API.

import dotenv from "dotenv";
dotenv.config();

import { v4 as uuidv4 } from "uuid";

const CLOUDFLARE_ACCOUNT_ID = process.env.CLOUDFLARE_ACCOUNT_ID;
const CLOUDFLARE_DATABASE_ID = process.env.CLOUDFLARE_DATABASE_ID;
const CLOUDFLARE_API_TOKEN = process.env.CLOUDFLARE_API_TOKEN;

if (
  !CLOUDFLARE_ACCOUNT_ID ||
  !CLOUDFLARE_DATABASE_ID ||
  !CLOUDFLARE_API_TOKEN
) {
  throw new Error("Missing Cloudflare environment variables");
}

const subscriptionTiers = [
  {
    name: "Basic Plan",
    price: 32900,
    monthlyCredits: 1200
    lemonsqueezyVariantId: 548623,
    lemonsqueezyProductId: 368228,
    lemonsqueezyCreditsTopUpVariantId: 548656,
  },
  {
    name: "Growth Plan",
    price: 65900,
    monthlyCredits: 3000,
    lemonsqueezyVariantId: 548624,
    lemonsqueezyProductId: 368228,
    lemonsqueezyCreditsTopUpVariantId: 548693,
  },
  {
    name: "Pro Plan",
    price: 139900,
    monthlyCredits: 7000,
    lemonsqueezyVariantId: 548626,
    lemonsqueezyProductId: 368228,
    lemonsqueezyCreditsTopUpVariantId: 548705,
  },
];

const topUpPackages = [
  {
    name: "1,000 Credits | Basic Plan",
    price: 27000,
    lemonsqueezyVariantId: 548655,
    lemonsqueezyProductId: 368235,
    credits: 1000,
  },
  {
    name: "2,000 Credits | Basic Plan",
    price: 54000,
    lemonsqueezyVariantId: 548656,
    lemonsqueezyProductId: 368235,
    credits: 2000,
  },
  {
    name: "5,000 Credits | Basic Plan",
    price: 135000,
    lemonsqueezyVariantId: 548657,
    lemonsqueezyProductId: 368235,
    credits: 5000,
  },
  {
    name: "1,000 Credits | Growth Plan",
    price: 22000,
    lemonsqueezyVariantId: 548692,
    lemonsqueezyProductId: 368252,
    credits: 1000,
  },
  {
    name: "2,000 Credits | Growth Plan",
    price: 44000,
    lemonsqueezyVariantId: 548693,
    lemonsqueezyProductId: 368252,
    credits: 2000,
  },
  {
    name: "5,000 Credits | Growth Plan",
    price: 110000,
    lemonsqueezyVariantId: 548694,
    lemonsqueezyProductId: 368252,
    credits: 5000,
  },
  {
    name: "1,000 Credits | Pro Plan",
    price: 20000,
    lemonsqueezyVariantId: 548704,
    lemonsqueezyProductId: 368256,
    credits: 1000,
  },
  {
    name: "2,000 Credits | Pro Plan",
    price: 40000,
    lemonsqueezyVariantId: 548705,
    lemonsqueezyProductId: 368256,
    credits: 2000,
  },
  {
    name: "5,000 Credits | Pro Plan",
    price: 100000,
    lemonsqueezyVariantId: 548706,
    lemonsqueezyProductId: 368256,
    credits: 5000,
  },
];

for (const subscriptionTier of subscriptionTiers) {
  const id = uuidv4();
  const createdAt = Math.floor(Date.now() / 1000);
  const updatedAt = createdAt;
  const res = await fetch(
    `https://api.cloudflare.com/client/v4/accounts/${CLOUDFLARE_ACCOUNT_ID}/d1/database/${CLOUDFLARE_DATABASE_ID}/query`,
    {
      method: "POST",
      headers: {
        Authorization: `Bearer ${CLOUDFLARE_API_TOKEN}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        params: [
          id,
          subscriptionTier.name,
          subscriptionTier.price,
          subscriptionTier.monthlyCredits,
          subscriptionTier.lemonsqueezyVariantId,
          subscriptionTier.lemonsqueezyProductId,
          subscriptionTier.lemonsqueezyCreditsTopUpVariantId,
          createdAt,
          updatedAt,
          null,
        ],
        sql: "INSERT INTO subscription_tiers (id, name, price, monthly_credits, lemonsqueezy_variant_id, lemonsqueezy_product_id, lemonsqueezy_credits_top_up_variant_id, created_at, updated_at, deleted_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
      }),
    }
  );
  console.log(res);
  if (!res.ok) {
    const data = await res.json();
    console.log(JSON.stringify(data, null, 2));
    throw new Error(`Failed to insert subscription tier: ${res.statusText}`);
  }
}

for (const topUpPackage of topUpPackages) {
  const id = uuidv4();
  const createdAt = Math.floor(Date.now() / 1000);
  const updatedAt = createdAt;
  const res = await fetch(
    `https://api.cloudflare.com/client/v4/accounts/${CLOUDFLARE_ACCOUNT_ID}/d1/database/${CLOUDFLARE_DATABASE_ID}/query`,
    {
      method: "POST",
      headers: {
        Authorization: `Bearer ${CLOUDFLARE_API_TOKEN}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        params: [
          id,
          topUpPackage.name,
          topUpPackage.price,
          topUpPackage.lemonsqueezyVariantId,
          topUpPackage.lemonsqueezyProductId,
          topUpPackage.credits,
          createdAt,
          updatedAt,
          null,
        ],
        sql: "INSERT INTO top_up_packages (id, name, price, lemonsqueezy_variant_id, lemonsqueezy_product_id, credits, created_at, updated_at, deleted_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);",
      }),
    }
  );
  console.log(res);
  if (!res.ok) {
    const data = await res.json();
    console.log(JSON.stringify(data, null, 2));
    throw new Error(`Failed to insert top up package: ${res.statusText}`);
  }
}

This worked pretty well for seeding my D1 and also gave me a good way to push new plans to production in the future. I still have some open questions when it comes to D1 & drizzleORM, please let me know if you figured out a good approach for:

• Seeding the local SQLite database
• Running migrations against the D1 in a CI/CD pipeline (ideally Github Actions)