Error running migrations with Vite

[drewbitt]

This originally came up in a Sveltekit project but reduced to just Vite, in a Vite project (ESM), running a migration script in Linux results in

TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for xxx.ts
    at new NodeError (node:internal/errors:387:5)
    at Object.getFileProtocolModuleFormat [as file:] (node:internal/modules/esm/get_format:75:11)
    at defaultGetFormat (node:internal/modules/esm/get_format:117:38)
    at defaultLoad (node:internal/modules/esm/load:81:20)
    at nextLoad (node:internal/modules/esm/loader:163:28)
    at ESMLoader.load (node:internal/modules/esm/loader:601:26)
    at ESMLoader.moduleProvider (node:internal/modules/esm/loader:457:22)
    at new ModuleJob (node:internal/modules/esm/module_job:63:26)
    at ESMLoader.#createModuleJob (node:internal/modules/esm/loader:476:17)
    at ESMLoader.getModuleJob (node:internal/modules/esm/loader:434:34) {
  code: 'ERR_UNKNOWN_FILE_EXTENSION'

The path being used in a module scope is migrationFolder: new URL('./migrations', import.meta.url).pathname

On Windows the path has to be relative for migrationFolder due to file:// shenanigans. A relative path to src/migrations results instead in:

TypeError [ERR_INVALID_MODULE_SPECIFIER]: Invalid module "src\migrations\0001_initial.ts" is not a valid package name imported from C:\GitHub\MyStuff\vite-repo\node_modules\.pnpm\kysely@0.23.3\node_modules\kysely\dist\esm\migration\file-migration-provider.js
    at new NodeError (node:internal/errors:387:5)
    at parsePackageName (node:internal/modules/esm/resolve:777:11)
    at packageResolve (node:internal/modules/esm/resolve:800:5)
    at moduleResolve (node:internal/modules/esm/resolve:901:20)
    at defaultResolve (node:internal/modules/esm/resolve:1115:11)
    at nextResolve (node:internal/modules/esm/loader:163:28)
    at ESMLoader.resolve (node:internal/modules/esm/loader:841:30)
    at ESMLoader.getModuleJob (node:internal/modules/esm/loader:424:18)
    at ESMLoader.import (node:internal/modules/esm/loader:525:22)
    at importModuleDynamically (node:internal/modules/esm/translators:110:35) {
  code: 'ERR_INVALID_MODULE_SPECIFIER'

Repro

Vite only: https://codesandbox.io/p/github/drewbitt/vite-kysely-repro/draft/intelligent-meninsky

Sveltekit using Vite by @Terbau: https://github.com/Terbau/kysely-sveltekit-migration-error-recreation

There was also related issue #254

[koskimas]

Load the transpiled migrations, not the typescript ones. Since you're using vite, you probably need to implement your own migration provider instead of using a one that loads modules dynamically. See the MigrationProvider interface.

[Nick-Riggs]

I think this may have more to do with the package being an ESM package than vite. I am experiencing it with a fairly straightforward node.js scenario. I am running the transpiled .js code.

My package is set up with { "type": "module" } in package.json and { "module": "NodeNext","moduleResolution": "NodeNext" } in tsconfig.json.

Seems to be that the loader isn't finding the path, or for ESM, the "URL". This is the alternate loader I wrote to make it work:

class ESMFileMigrationProvider implements MigrationProvider {
    constructor(private relativePath: string) { }

    async getMigrations(): Promise<Record<string, Migration>> {
        const migrations: Record<string, Migration> = { };
        const __dirname = url.fileURLToPath(new URL(".", import.meta.url));
        const resolvedPath = path.resolve(__dirname, this.relativePath);
        const files = await fs.readdir(resolvedPath);

        for (const fileName of files) {
            if (!fileName.endsWith(".js")) {
                continue;
            }

            const importPath = path.join(this.relativePath, fileName).replaceAll("\\", "/");
            const migration = await import(importPath);
            const migrationKey = fileName.substring(0, fileName.lastIndexOf("."));

            migrations[migrationKey] = migration;
        }

        return migrations;
    }
}

[zerosym]

Leaving this here in case it might help someone.

For people using SvelteKit with default config I had success writing a custom migrator using the following to import the migrations:

const migrations: Record<string, Migration> = import.meta.glob('./migrations/**.ts', {
    eager: true,
})

[luixo]

@koskimas The ESMFileMigrationProvider works as expected (with a small fix), is there a way to add a check for being in a ESM module? Got a similar error trying to run a ESM environment (in Vitest) which included running migrations.

[jericirenej]

Load the transpiled migrations, not the typescript ones. Since you're using vite, you probably need to implement your own migration provider instead of using a one that loads modules dynamically. See the MigrationProvider interface.

I had no issues running typescript migrations directly with ts-node and an adjusted FileMigrationProvider, similar to the one provided by @Nick-Riggs (by modifying the condition to continue unless the .ts ending is present).

@koskimas Is there any specific reason why we should avoid using to typescript migration files directly and refer only to transpiled ones?

[Jhappy77]

To add to Nick's solution:

make sure to import

import { fileURLToPath } from "url";

and I found it wasn't working for relative filepaths for me until I changed the migration import line to include a ./:

const migration = await import(`./${importPath}`);

[igalklebanov]

Try https://github.com/kysely-org/kysely-ctl .

[theogravity]

Here's a simplified version - requires ts-node to be installed:

import { promises as fs } from "node:fs";
import path from "node:path";
import type { Migration, MigrationProvider } from "kysely";
import ts from "ts-node";

ts.register({
  transpileOnly: true,
});

export class TypeScriptFileMigrationProvider implements MigrationProvider {
  constructor(private absolutePath: string) {}

  async getMigrations(): Promise<Record<string, Migration>> {
    const migrations: Record<string, Migration> = {};
    const files = await fs.readdir(this.absolutePath);

    for (const fileName of files) {
      if (!fileName.endsWith(".ts")) {
        continue;
      }

      const importPath = path.join(this.absolutePath, fileName).replaceAll("\\", "/");
      const { up, down } = await import(importPath);
      const migrationKey = fileName.substring(0, fileName.lastIndexOf("."));

      migrations[migrationKey] = { up, down };
    }

    return migrations;
  }
}

The absolutePath input should be the full path to the migrations folder.

// global-setup.ts
// Adapted from https://github.com/ivandotv/vitest-database-containers/blob/main/src/__tests__/db/vitestGlobalSetup.ts

import * as path from "node:path";
import { PostgreSqlContainer } from "@testcontainers/postgresql";
import { Kysely, Migrator, PostgresDialect } from "kysely";
import { Pool } from "pg";
import { TypeScriptFileMigrationProvider } from "./ts-migration-transpiler";

global.containers = [];

export async function setup(_config: any) {
  console.log("Setting up global environment");
  const postgresContainer = initializePostgres();

  const startedContainers = await Promise.all([postgresContainer]);

  global.containers.push(...startedContainers);
}

async function initializePostgres() {
  console.log("Starting postgres container");
  const postgresContainer = await new PostgreSqlContainer().start();

  process.env.DB_PORT = postgresContainer.getPort().toString();
  process.env.DB_USER = postgresContainer.getUsername();
  process.env.DB_PASSWORD = postgresContainer.getPassword();
  process.env.DB_NAME = postgresContainer.getDatabase();

  const pool = new Pool({
    host: "localhost",
    port: postgresContainer.getPort(),
    user: postgresContainer.getUsername(),
    password: postgresContainer.getPassword(),
    database: postgresContainer.getDatabase(),
  });

  const db = new Kysely({
    dialect: new PostgresDialect({
      pool,
    }),
  });

  // Run migrations
  const migrator = new Migrator({
    db,
    provider: new TypeScriptFileMigrationProvider(path.join(__dirname, "..", "db", "migrations")),
  });

  console.log("Migrating database");

  const { error } = await migrator.migrateToLatest();

  if (error) {
    console.error("failed to migrate");
    console.error(error);
    process.exit(1);
  }

  await pool.end();

  return postgresContainer;
}