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

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

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