Applying D1 migrations in tests

I don't know how to get this working.

import { Miniflare } from 'miniflare';
import { afterAll, beforeAll, describe } from 'vitest';
import { spawn } from 'child_process';

describe('App entry', () => {
  beforeAll(() => {
    const mf = new Miniflare({
      modules: true,
      scriptPath: './dist/index.js',
      d1Databases: ['DB'],
      d1Persist: '.wrangler/state/v3/d1'
  });
  const migrationProcess = spawn('pnpm', ['wrangler', 'd1', 'migrations', 'apply', 'snow-db', '--local'], {
    shell: true,
    stdio: 'inherit'
  });

  await new Promise<void>((resolve, reject) => {
    migrationProcess.on('close', (code) => {
      if (code === 0) {
        console.log('Migrations completed successfully');
        resolve();
      } else {
        reject(`Migrations process exited with code ${code}`);
      }
    });
  });
  afterAll(() => mf.dispose());

  // test here
});

import { Miniflare } from 'miniflare';
import { afterAll, beforeAll, describe } from 'vitest';
import { spawn } from 'child_process';

describe('App entry', () => {
  beforeAll(() => {
    const mf = new Miniflare({
      modules: true,
      scriptPath: './dist/index.js',
      d1Databases: ['DB'],
      d1Persist: '.wrangler/state/v3/d1'
  });
  const migrationProcess = spawn('pnpm', ['wrangler', 'd1', 'migrations', 'apply', 'snow-db', '--local'], {
    shell: true,
    stdio: 'inherit'
  });

  await new Promise<void>((resolve, reject) => {
    migrationProcess.on('close', (code) => {
      if (code === 0) {
        console.log('Migrations completed successfully');
        resolve();
      } else {
        reject(`Migrations process exited with code ${code}`);
      }
    });
  });
  afterAll(() => mf.dispose());

  // test here
});

19 Replies

MrBBot•12mo ago

Hmmm, you could try applying migrations before constructing new Miniflare() maybe? You could use child_process.execSync("pnpm wrangler d1 migrations apply snow-db --local", { stdio: "inherit" }) for running migrations, then you wouldn't have the worry about creating that extra Promise. An alternative solution would be to just read and apply the migrations yourself using Miniflare's API. Something like...

import fs from "node:fs/promises";
import path from "node:path";
import { Miniflare } from "miniflare";

const mf = new Miniflare({
    modules: true,
    scriptPath: "index.js",
    d1Databases: { "DB": "uuid" }
});

const DB = await mf.getD1Database("DB");

const migrationsDir = "./migrations"
for (const migrationName of await fs.readdir(migrationsDir)) {
    const migrationPath = path.join(migrationsDir, migrationName);
    let migration = await fs.readFile(migrationPath, "utf8");
    // Remove migration comment
    migration = migration.split("\n").slice(1).join("\n");
    await DB.exec(migration);
}

import fs from "node:fs/promises";
import path from "node:path";
import { Miniflare } from "miniflare";

const mf = new Miniflare({
    modules: true,
    scriptPath: "index.js",
    d1Databases: { "DB": "uuid" }
});

const DB = await mf.getD1Database("DB");

const migrationsDir = "./migrations"
for (const migrationName of await fs.readdir(migrationsDir)) {
    const migrationPath = path.join(migrationsDir, migrationName);
    let migration = await fs.readFile(migrationPath, "utf8");
    // Remove migration comment
    migration = migration.split("\n").slice(1).join("\n");
    await DB.exec(migration);
}

Unfortunately, this requires each of the statements in your migrations to be on their own line, but you could probably get around that with something like https://www.npmjs.com/package/@databases/split-sql-query.

stibbsOP•12mo ago

Each statement on its own line, as in 100% of each statement in its own single line? Surprisingly this results in

D1_EXEC_ERROR: Error in line 1: CREATE TABLE posts (id integer PRIMARY KEY AUTOINCREMENT, title text NOT NULL, author text NOT NULL);: SQL code did not contain a statement.

MrBBot•12mo ago

Yeah 😕 Does it work if you remove the ; at the end?

stibbsOP•12mo ago

Nope, same error 😦

MrBBot•12mo ago

Any chance you're using Windows?

stibbsOP•12mo ago

Currently I am, yes Want me to try on mac? Lol

MrBBot•12mo ago

Maybe try migration = migration.split("\r\n").slice(1).join("\n"); instead Or migration = migration.split("\r\n").slice(1).map(l => l.trim()).filter(Boolean).join("\n");

stibbsOP•12mo ago

D1_EXEC_ERROR: Error in line 1: : No SQL statements detected.

MrBBot•12mo ago

One last go, migration = migration.split(/\r?\n/).slice(1).map(l => l.trim()).filter(Boolean).join("\n"); 😅

stibbsOP•12mo ago

Hah, well it runs without a D1_EXEC_ERROR , but it doesn't seem to run the migration as I now get D1_ERROR: no such table: posts

MrBBot•12mo ago

(╯°□°)╯︵ ┻━┻ what does migration look like after that line? ┬─┬ノ( º _ ºノ)

stibbsOP•12mo ago

Actually I'm wrong... I accidentally deleted the await DB.exec(migration);.... it's back to Error in line 1: CREATE ... SQL code did not contain a statement.

MrBBot•12mo ago

Hmmm, not too sure what's going on here. If you call DB.exec("CREATE TABLE ...") directly, does that work?

stibbsOP•12mo ago

Yes My migration creates the table and seeds 1 row. If I do those in seperate DB.exec() calls directly it works

MrBBot•12mo ago

Hmmm, ok, I'd probably just stick with that for now. Not ideal, but really not sure what's going wrong here.

stibbsOP•12mo ago

No worries, thank you for trying!! Is there anything planned in Miniflare to make it easier to work with D1 (and migrations, specifically)? Want me to raise a feature request or something 😅

MrBBot•12mo ago

Definitely things planned, I'm currently working on porting Miniflare 2's unit testing environments to Miniflare 3. An aim of this new version is to make migrations super easy. 👍

Kodie Goodwin•12mo ago

Can also confirm that it looks like miniflare is not using the DB created when migrations are applied. It's creating a new sqlite db. If you use wrangler d1 migrations apply Damn. Even ensuring the --persist-to configs are set to the same dir, still creating a new sqlite db

export async function initMf() {
  _mf = new Miniflare({
    d1Databases: { TEST_DB: "d2bde183-2d69-4ef5-af6f-8a80d52820a8" },
    d1Persist: ".wrangler/test_state/v3/d1",
    script: "",
    modules: true,
    modulesRules: [{ type: "ESModule", include: ["**/*.js"] }],
  });

  const migrationProcess = spawn(
    "pnpm",
    [
      "--filter",
      "api",
      "exec",
      "wrangler",
      "d1",
      "migrations",
      "apply",
      "--persist-to",
      ".wrangler/test_state",
      "test",
      "--local",
    ],
    {
      shell: true,
      stdio: "inherit",
    },
  );
....

export async function initMf() {
  _mf = new Miniflare({
    d1Databases: { TEST_DB: "d2bde183-2d69-4ef5-af6f-8a80d52820a8" },
    d1Persist: ".wrangler/test_state/v3/d1",
    script: "",
    modules: true,
    modulesRules: [{ type: "ESModule", include: ["**/*.js"] }],
  });

  const migrationProcess = spawn(
    "pnpm",
    [
      "--filter",
      "api",
      "exec",
      "wrangler",
      "d1",
      "migrations",
      "apply",
      "--persist-to",
      ".wrangler/test_state",
      "test",
      "--local",
    ],
    {
      shell: true,
      stdio: "inherit",
    },
  );
....

ls -latrh api/.wrangler/test_state/v3/d1/miniflare-D1DatabaseObject
total 312
  96B Dec  8 09:25 ..
  4.0K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite
  32K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite-shm
  109K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite-wal
  8.0K Dec  8 09:25 3c187a1e00838763923cf333b7f868310f03ba9f3ccc521d9e909e957823b7f7.sqlite
  192B Dec  8 09:25 .

ls -latrh api/.wrangler/test_state/v3/d1/miniflare-D1DatabaseObject
total 312
  96B Dec  8 09:25 ..
  4.0K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite
  32K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite-shm
  109K Dec  8 09:25 6c069595cae5563f7138a4d757a20efb3e391b66e0907d4b22b3622628057dc1.sqlite-wal
  8.0K Dec  8 09:25 3c187a1e00838763923cf333b7f868310f03ba9f3ccc521d9e909e957823b7f7.sqlite
  192B Dec  8 09:25 .

MrBBot•12mo ago

Hey! 👋 It looks like you're running the wrangler d1 migrations apply command inside a pnpm workspace project. This means the migrations are applied in $PWD/api/.wrangler/test_state whereas new Miniflare() appears to be pointed to $PWD/.wrangler/test_state. Try change the d1Persist option when constructing new Miniflare() to api/.wrangler/test_state/v3/d1. I'd also recommend apply migrations before constructing new Miniflare(), so Miniflare starts up with migrations already applied.

Gaming

Programming

Applying D1 migrations in tests