Skip to content
ZeroDirs

Listings from D1

Markdown

In D1 mode the build reads every approved row from D1 over the REST API instead of src/content/listings/. What changes is the loader and four build variables; site.config.ts and the deploy on push stay as they are. You need it the moment a submission approved in /admin/ should appear on the site: approvals write rows, and only a build reads them.

Before you start

Section titled “Before you start”
Check Where You should see
pnpm cf:setup has run: the database exists, the migrations are applied wrangler.jsonc a database_id under d1_databases — Go live
Deploys happen on push Workers Builds, or the shipped GitHub Actions workflow a build and a deploy after a push — Deploy on git push

  1. Put approved rows in the database.

    Terminal
    pnpm db:seed:remote --with-images --with-favicons

    It reads src/content/listings/ (--dataset <name> for a sample dataset), inserts every file as an approved row — status = 'approved', source = 'seed', submitter seed@localhost — turns popularity into a clicks row, and runs the SQL through wrangler d1 execute DB --remote under your wrangler login. Then the logos, each pass skipping rows that already have one: --with-images stores each listing’s own https:// logo in R2; --with-favicons fetches the icon each remaining site publishes; --with-images last uploads the /logos/ files for the rest. A re-run deletes only the rows it seeded before; submissions and imports stay.

    db:seed — src/content/listings → D1 (remote)
      stored N dataset logo URL(s) in R2 in 40s
      fetched N favicon(s) into R2 in 90s (no icon: unreachable=N)
      uploaded N /logos/ file(s) to R2
      N listing(s), N click row(s) in N statement(s); largest N bytes (limit 90000)
    seeded N listing(s) into the remote database

    --dry-run prints the SQL and touches nothing. Rows also arrive by approving a submission in /admin/, or from pnpm listings:import my-data.csv --to d1 --remote.

  2. Create the read token.

    Cloudflare dashboard → My Profile → API Tokens → Create Token → Create Custom Token. Permissions: one row, Account → D1 → Read; Account Resources: the account that owns the database; nothing else. Continue to summary → Create Token, and copy the value now — it is shown once. The summary names one permission, D1 Read.

    It is a build secret: not the token Workers Builds deploys with, not the deploy token in GitHub Actions, and never CLOUDFLARE_API_TOKEN, the name wrangler reads.

  3. Set the four variables where the build runs.

    Variable Value
    LISTINGS_SOURCE d1
    CLOUDFLARE_ACCOUNT_ID the account id pnpm exec wrangler whoami prints
    D1_DATABASE_ID database_id in wrangler.jsonc
    D1_READ_TOKEN the token from step 2, stored as a secret

    Workers Builds: Workers & Pages → your Worker → Settings → Build → Variables and secrets. GitHub Actions: the shipped deploy.yml already sets LISTINGS_SOURCE: d1 in its build step and reads the other three from repository secrets. All four are build-time variables; they never reach the Worker. The list shows four names, D1_READ_TOKEN marked as a secret.

  4. Try it locally first.

    .env
    LISTINGS_SOURCE=d1
    CLOUDFLARE_ACCOUNT_ID=<from wrangler whoami>
    D1_DATABASE_ID=<database_id from wrangler.jsonc>
    D1_READ_TOKEN=<the token from step 2>

    .env is gitignored. Then:

    Terminal
    pnpm build
    [zerodirs-d1] D1: N approved listings (featured N, 30-day clicks for N, N REST requests)

    pnpm preview: the home page shows the database’s listings, including anything approved in /admin/ that has no Markdown file.

  5. Push, then read the deploy’s build log.

    Workers & Pages → your Worker → Deployments → that build’s log, or the build step of the Actions run. The same [zerodirs-d1] D1: … line must be there; [zerodirs-files] listings: … means the variables did not reach that build — see The trap.

  6. Turn on publishing on approval.

    site.config.ts
    providers: { rebuild: 'deploy-hook', rebuildAuto: true },

    Publishing on approval has the hook URL and the GitHub alternative. Until then /admin/ reports providers.rebuild is "none": approved listings stay off the site until someone publishes.; afterwards that line is gone.

Verify

Section titled “Verify”
Where You should see
The loader the build log, local or in the deploy [zerodirs-d1] D1: N approved listings (…); a [zerodirs-files] listings: … line means files mode
The count the home page hero, N tools across M categories, against the database the same N as the operator’s query: pnpm exec wrangler d1 execute DB --remote --command "SELECT count(*) FROM listings WHERE status='approved' AND slug IS NOT NULL"
A seeded logo a seeded listing’s page, the logo’s src https://<your media host>/logos/<id>.<ext> — the R2 key on media.baseUrl, not a /logos/ path

The trap

Section titled “The trap”

The loader picks its source from LISTINGS_SOURCE and the three credentials:

LISTINGS_SOURCE The three credentials The build
unset all three set reads D1, silently
unset any missing prints the warning below and reads Markdown; it succeeds
d1 any missing fails at once with the error below
d1 all three set reads D1
files ignored reads Markdown, no warning
anything else ignored fails: LISTINGS_SOURCE="x" is invalid: expected "d1" or "files" 
listings: LISTINGS_SOURCE is unset and D1_READ_TOKEN is missing — using files (src/content/listings). Set LISTINGS_SOURCE=files to silence this warning.
listings loader: LISTINGS_SOURCE=d1 but D1_READ_TOKEN is not set. The D1 REST loader needs CLOUDFLARE_ACCOUNT_ID, D1_DATABASE_ID, D1_READ_TOKEN at build time — put them in .env locally, or in Workers Builds → Settings → Build variables (D1_READ_TOKEN as a secret). D1_READ_TOKEN is an account API token with the "D1 Read" permission scoped to this one database; it is not the Workers Builds deploy token and it is not CLOUDFLARE_API_TOKEN. Set LISTINGS_SOURCE=files to build from src/content/listings instead.

The second row is the dangerous one: the build succeeds, the deploy goes live, and it was built from src/content/listings/ — every listing that exists only in the database, which is every approval since the seed, is gone until the next build with all four variables. The usual cause is a variable set in .env but not in Workers Builds. Always set LISTINGS_SOURCE=d1 explicitly where the build runs: a missing credential then fails the build, and a failed build leaves the previous deploy live. A wrong token or database id fails the same way, with a line beginning D1 REST and the status code.

Logos and the move

Section titled “Logos and the move”

A Markdown listing’s logo is an https:// URL or a /logos/ path in public/. A row has logo_key instead — an object in the R2 bucket bound as MEDIA, keyed logos/<id>.<ext> — which the loader serves as media.baseUrl + / + the key. The seed’s two flags fill it; without the bucket they stop: wrangler.jsonc has no r2_buckets entry bound as MEDIA; --with-images needs one. A row without a key renders the initial-letter tile, as a file without logo does.

Set media.baseUrl to the bucket’s public host before the first D1 build: the default https://media.example.com turns every logo into a broken image, and nothing warns. A logo on that host is converted at build time, so pnpm check:config counts one more file per logo once LISTINGS_SOURCE=d1. Logos and media covers the bucket’s domain and submitted logos.

Configuration

Section titled “Configuration”
Field What it changes Reference
media.baseUrl the host every logo_key is served from; the only site.config.ts field this page needs media
LISTINGS_SOURCE, CLOUDFLARE_ACCOUNT_ID, D1_DATABASE_ID, D1_READ_TOKEN the loader and its credentials; build variables, not config Build-time variables
providers.rebuild, providers.rebuildAuto what an approval triggers providers

Files

Section titled “Files”
  • Directorysrc/
    • Directoryloaders/
      • index.ts picks the loader from LISTINGS_SOURCE and the credentials
      • d1.ts the REST loader: query, paging, retries, the missing-variable error
      • normalize.ts one row into the shape a Markdown file produces
  • Directoryscripts/
    • seed.ts pnpm db:seed:local and pnpm db:seed:remote, the three logo passes
  • Directorydrizzle/ the migrations pnpm cf:setup and pnpm db:migrate:remote apply
    • …
  • .env.example the four build variables, with comments
  • wrangler.jsonc database_id, and the MEDIA bucket the seed uploads to
Section titled “Related”
Environment and secretsEvery variable and secret, where it lives, and what breaks without it.
Deploy on git pushWorkers Builds or GitHub Actions — where the four variables go.
Publishing on approvalThe Deploy Hook that turns an approval into a build.
Logos and mediaThe bucket, its domain, and what a listing without a logo shows.