[TUTORIAL]: Using in-memory Postgres when testing with vitest

[wilfredjonathanjames]

Crosspost from here - just realised discussions gets use.

Sharing for posterity as it may help someone. A vi.mock to switch pg databases to in-memory pglite, push your drizzle schema, and insert seed data. pglite runs-WASM compiled postgres in memory, which is ideal for testing as there's no docker containers, no delay, and it's real pg. This solution supports parallelism and watch mode. Most importantly - it uses push in place of migrate, as there's no need to create migration files when testing.

Note to Drizzle maintainers, please do not remove drizzle-kit/api's pushSchema method. While it's not well documented, it allows us to push schema directly to test dbs. Please don't make me push via child_process execSync again.

Stack:

• Typescript
• Vitest
• Drizzle
• NodePostgres (drizzle-orm/node-postgres)

Mock:

// vitest.setup.ts

vi.mock("src/db", async (importOriginal) => {
  const { default: _, ...rest } =
    await importOriginal<typeof import("src/db")>()

  const { PGlite } = await vi.importActual<
    typeof import("@electric-sql/pglite")
  >("@electric-sql/pglite")
  const { drizzle } =
    await vi.importActual<typeof import("drizzle-orm/pglite")>(
      "drizzle-orm/pglite",
    )

  // use require to defeat dynamic require error
  // (https://github.com/drizzle-team/drizzle-orm/issues/2853#issuecomment-2668459509)
  const { createRequire } =
    await vi.importActual<typeof import("node:module")>("node:module")
  const require = createRequire(import.meta.url)
  const { pushSchema } =
    require("drizzle-kit/api") as typeof import("drizzle-kit/api")

  const { schema, User } = rest

  const client = new PGlite()
  const db = drizzle(client, { schema })

  // apply schema to db
  const { apply } = await pushSchema(schema, db as any)
  await apply()

  // seed test data
  await db
    .insert(User)
    .values({ email: "[email protected]", externalId: "external" })

  return { default: db, ...rest }
})

Detail:

// vitest.config.mts

import { defineConfig } from "vitest/config"
import tsconfigPaths from "vite-tsconfig-paths"

const config = defineConfig({
  plugins: [tsconfigPaths({ root: "./" })],
  test: {
    globals: true,
    environment: "node",
    setupFiles: "./vitest.setup.ts",
  },
})

export default config

// src/db/index.ts (mocked, db is not exposed to tests)

import { Pool } from "pg"
import { drizzle } from "drizzle-orm/node-postgres"

import { User } from "./user.sql"

export const schema = {
  User,
  ...
}

const db = drizzle(
  new Pool({
    ...
  }),
  {
    schema,
  },
)
export default db

export * from "./user.sql"

[mintydev789]

Using normal imports also works (except the pushSchema one):

import { drizzle } from "drizzle-orm/pglite";
import { PGlite } from "@electric-sql/pglite";

[tsatsujnr139]

Thanks for this… any ideas on how I could do something similar in a cloudflare environment?

push api won’t work there nether will manually executing the sql files with drizzle