Applying D1 migrations in tests

stibbs · 2023-11-29T04:21:50.393Z

I don't know how to get this working. ```ts 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 ((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 }); ```

M

MrBBot•11/29/23, 8:30 AM

Hmmm, you could try applying migrations before constructing

new Miniflare()

new Miniflare()

new Miniflare()

new Miniflare() maybe? You could use

child_process.execSync("pnpm wrangler d1 migrations apply snow-db --local", { stdio: "inherit" })

child_process.execSync("pnpm wrangler d1 migrations apply snow-db --local", { stdio: "inherit" })

child_process.execSync("pnpm wrangler d1 migrations apply snow-db --local", { stdio: "inherit" })

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

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);
}

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.

S

stibbsOP•11/29/23, 10:28 AM

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

S

stibbsOP•11/29/23, 10:34 AM

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.

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.

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.

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.

Sstibbs Each statement on its own line, as in 100% of each statement in its own single l...

M

MrBBot•11/29/23, 10:35 AM

Yeah

M

MrBBot•11/29/23, 10:35 AM

Does it work if you remove the

;

; at the end?

S

stibbsOP•11/29/23, 10:54 AM

Nope, same error

M

MrBBot•11/29/23, 11:00 AM

Any chance you're using Windows?

S

stibbsOP•11/29/23, 11:11 AM

Currently I am, yes

S

stibbsOP•11/29/23, 11:11 AM

Want me to try on mac? Lol

M

MrBBot•11/29/23, 11:12 AM

Maybe try

migration = migration.split("\r\n").slice(1).join("\n");

migration = migration.split("\r\n").slice(1).join("\n");

migration = migration.split("\r\n").slice(1).join("\n");

migration = migration.split("\r\n").slice(1).join("\n"); instead

M

MrBBot•11/29/23, 11:13 AM

Or

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

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

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

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

S

stibbsOP•11/29/23, 11:14 AM

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

M

MrBBot•11/29/23, 11:15 AM

One last go,

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

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

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

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

S

stibbsOP•11/29/23, 11:18 AM

Hah, well it runs without a

D1_EXEC_ERROR

D1_EXEC_ERROR

D1_EXEC_ERROR

D1_EXEC_ERROR , but it doesn't seem to run the migration as I now get

D1_ERROR: no such table: posts

D1_ERROR: no such table: posts

D1_ERROR: no such table: posts

D1_ERROR: no such table: posts

M

MrBBot•11/29/23, 11:18 AM

(╯°□°)╯︵ ┻━┻

M

MrBBot•11/29/23, 11:19 AM

what does

migration

migration

migration

migration look like after that line? ┬─┬ノ( º _ ºノ)

S

stibbsOP•11/29/23, 11:21 AM

Actually I'm wrong... I accidentally deleted the

await DB.exec(migration);

await DB.exec(migration);

await DB.exec(migration);

await DB.exec(migration);.... it's back to

Error in line 1: CREATE ... SQL code did not contain a statement.

Error in line 1: CREATE ... SQL code did not contain a statement.

Error in line 1: CREATE ... SQL code did not contain a statement.

Error in line 1: CREATE ... SQL code did not contain a statement.

M

MrBBot•11/29/23, 11:22 AM

Hmmm, not too sure what's going on here. If you call

DB.exec("CREATE TABLE ...")

DB.exec("CREATE TABLE ...")

DB.exec("CREATE TABLE ...")

DB.exec("CREATE TABLE ...") directly, does that work?

S

stibbsOP•11/29/23, 11:25 AM

Yes

S

stibbsOP•11/29/23, 11:27 AM

My migration creates the table and seeds 1 row. If I do those in seperate

DB.exec()

DB.exec()

DB.exec()

DB.exec() calls directly it works

M

MrBBot•11/29/23, 11:28 AM

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

S

stibbsOP•11/29/23, 11:28 AM

No worries, thank you for trying!!

S

stibbsOP•11/29/23, 11:29 AM

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

M

MrBBot•11/29/23, 11:30 AM

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.

K

Kodie Goodwin•12/8/23, 5:13 PM

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.

K

Kodie Goodwin•12/8/23, 5:14 PM

If you use wrangler d1 migrations apply

K

Kodie Goodwin•12/8/23, 5:27 PM

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",
    },
  );
....

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",
    },
  );
....

K

Kodie Goodwin•12/8/23, 5:27 PM

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 .

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 .

KKodie Goodwin Damn. Even ensuring the --persist-to configs are set to the same dir, still crea...

M

MrBBot•12/9/23, 12:36 PM

Hey!

It looks like you're running the

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply command inside a

pnpm

pnpm

workspace project. This means the migrations are applied in

$PWD/api/.wrangler/test_state

$PWD/api/.wrangler/test_state

$PWD/api/.wrangler/test_state

$PWD/api/.wrangler/test_state whereas

new Miniflare()

new Miniflare()

new Miniflare()

new Miniflare() appears to be pointed to

$PWD/.wrangler/test_state

$PWD/.wrangler/test_state

$PWD/.wrangler/test_state

$PWD/.wrangler/test_state. Try change the

d1Persist

d1Persist

d1Persist

d1Persist option when constructing

new Miniflare()

new Miniflare()

new Miniflare()

new Miniflare() to

api/.wrangler/test_state/v3/d1

api/.wrangler/test_state/v3/d1

api/.wrangler/test_state/v3/d1

api/.wrangler/test_state/v3/d1. I'd also recommend apply migrations before constructing

new Miniflare()

new Miniflare()

new Miniflare()

new Miniflare(), so Miniflare starts up with migrations already applied.

Similar Threads

Similar Threads

Similar Threads