Accessing local D1 database with Vitest integration

[dherran]

I've been following the guide to create a simple integration test with Vitest. The missing bit is the D1 integration, as it seems like the test environment doesn't have access to the local DB binding from wrangler.toml. Here is my setup:

// vitest.config.mts

import { defineWorkersConfig } from "@cloudflare/vitest-pool-workers/config";

export default defineWorkersConfig({
  test: {
    poolOptions: {
      workers: {
        wrangler: { configPath: "./wrangler.toml" },
      },
    },
  }
});

// wrangler.toml

[[d1_databases]]
binding = "DB"
database_name = "my_db"
database_id = "abdcde"

// index.ts

import { Hono, Context } from "hono";

const app = new Hono<{ Bindings: CloudflareBindings }>();

app.get("/api/users/:id", async (c) => {
  const userId = c.req.param("id");
  try {
    let { results } = await c.env.DB.prepare("SELECT * FROM users WHERE id = ?")
      .bind(userId)
      .all();
    return c.json(results);
  } catch (e) {
    console.log(e);
    return c.json({ err: e.message }, 500);
  }
});

export default app;

// test/index.spec.ts

import { env, createExecutionContext, waitOnExecutionContext, SELF } from "cloudflare:test";
import { describe, it, expect } from "vitest";
import worker from "index";

describe("GET /api/users/1", () => {
  it("Should return 200 response", async () => {
    const res = await worker.request("/api/users/1", {}, env);

    expect(res.status).toBe(200);
  });
});

All of this works perfectly fine when running the local worker with npm run dev, but npm test will throw the following error:

Error: D1_ERROR: no such table: users: SQLITE_ERROR

Any ideas as to how to make Vitest access the local D1 database? is this possible? There is a recipe for executing a migration as part of the test, but ideally I'd like to run the integration test with the local db.

[rxliuli]

The same question, does anyone know how to solve it?

[rxliuli]

Resolved, the key point is that d1 does not automatically execute the initialization SQL, so it needs to be executed manually. However, since @cloudflare/vitest-pool-workers is used, Node.js APIs cannot be used in the test code, so it needs to be injected through env. The relevant code is as follows.

// vitest.config.ts
import { defineWorkersConfig } from '@cloudflare/vitest-pool-workers/config'
import { readdir, readFile } from 'node:fs/promises'
import path from 'node:path'

async function getInitSql() {
  const list = (await readdir(path.resolve(__dirname, 'migrations'))).sort(
    (a, b) => a.localeCompare(b),
  )
  return (
    await Promise.all(
      list.map((file) =>
        readFile(path.resolve(__dirname, 'migrations', file), 'utf-8'),
      ),
    )
  ).join('\n')
}

export default defineWorkersConfig(async () => {
  return {
    test: {
      include: ['./test/**/*.test.ts'],
      globalSetup: './test/setup.ts',
      poolOptions: {
        workers: {
          wrangler: { configPath: './wrangler.toml' },
          miniflare: {
            bindings: {
              APP_ENV: 'development',

              TEST_INIT_SQL: await getInitSql(),
            },
          },
        },
      },
    },
  }
})

// index.test.ts
beforeAll(async () => {
    const _env = env as HonoEnv['Bindings']
    await _env.MY_KV.put(
      '123',
      JSON.stringify({
        id: '123',
        email: 'test',
        createdAt: new Date().toISOString(),
        updatedAt: new Date().toISOString(),
      } satisfies TokenInfo),
    )
    await _env.DB.prepare(_env.TEST_INIT_SQL).run()
    const prisma = await prismaClients.fetch(_env.DB)
    await prisma.localUser.create({
      data: {
        id: '123',
        email: 'test',
        password: 'test',
        emailVerified: true,
        createdAt: new Date().toISOString(),
        updatedAt: new Date().toISOString(),
      },
    })
  })

I found vitest-dev/vitest#3986 (comment) very helpful.