[BUG]: Importing drizzle-kit/api fails in ESM modules

[aminfara]

What version of drizzle-orm are you using?

0.33.0

What version of drizzle-kit are you using?

0.24.2

Describe the Bug

When I import pushSchema from drizzle-kit/api in ESM files it fails with the following error:

https://codesandbox.io/p/devbox/drizzle-kit-import-xkmlfw?

// index.mjs
import { pushSchema } from "drizzle-kit/api";

const _ = pushSchema;

console.log("drizzle-kit API imported");

$ node index.mjs

file:///project/workspace/node_modules/drizzle-kit/api.mjs:12
  throw Error('Dynamic require of "' + x + '" is not supported');
        ^

Error: Dynamic require of "fs" is not supported
    at file:///project/workspace/node_modules/drizzle-kit/api.mjs:12:9
    at ../node_modules/.pnpm/fs.realpath@1.0.0/node_modules/fs.realpath/index.js (file:///project/workspace/node_modules/drizzle-kit/api.mjs:768:14)
    at __require2 (file:///project/workspace/node_modules/drizzle-kit/api.mjs:18:50)
    at ../node_modules/.pnpm/glob@8.1.0/node_modules/glob/glob.js (file:///project/workspace/node_modules/drizzle-kit/api.mjs:2317:14)
    at __require2 (file:///project/workspace/node_modules/drizzle-kit/api.mjs:18:50)
    at src/serializer/index.ts (file:///project/workspace/node_modules/drizzle-kit/api.mjs:8621:20)
    at __init (file:///project/workspace/node_modules/drizzle-kit/api.mjs:15:56)
    at src/migrationPreparator.ts (file:///project/workspace/node_modules/drizzle-kit/api.mjs:8644:5)
    at __init (file:///project/workspace/node_modules/drizzle-kit/api.mjs:15:56)
    at src/cli/commands/migrate.ts (file:///project/workspace/node_modules/drizzle-kit/api.mjs:15983:5)

Expected behavior

Import without a problem like the commonJS file.

// index.cjs
const _ = require("drizzle-kit/api");

console.log("drizzle-kit API imported");

$ node index.cjs 

drizzle-kit API imported

Environment & setup

I saw this problem first when I tried to use pushSchema in my unit tests for my Typescript(ESM) package. When I changed to CommonJS it just worked. So I created this empty ESM javascript package to make sure nothing is interfering with the imports.

https://codesandbox.io/p/devbox/drizzle-kit-import-xkmlfw

{
  "name": "drizzle-kit-import",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "type": "module",
  "scripts": {
    "start": "node --watch index.js"
  },
  "license": "MIT",
  "dependencies": {
    "drizzle-kit": "^0.24.2",
    "drizzle-orm": "^0.33.0"
  }
}

[aminfara]

If I add the following lines to the top of transpiled file node_modules/drizzle-kit/api.mjs it works (very messy hack).

import { createRequire as topLevelCreateRequire } from 'module';
const require = topLevelCreateRequire(import.meta.url);

The issue seems to be related to tsup bundling both cjs and esm dependencies in api.mjs where require is not defined. The lines above somehow need to be added to drizzle-kit build steps as esbuild banner.

[rkofman]

I believe a fix might look very similar to: https://github.com/hey-api/openapi-ts/pull/1086/files

[sohrab-]

This is the only thing stopping us from being able to run integration tests on our services that use Drizzle. The approach of using testcontainers with postgresql module, pushing the relevant subset of the schema on test setup programmatically and running the test cases is pretty sweet if we could only get past this issue.

[froid1911]

any updates on this? really a blocker right now :-(

[dnclain]

I'm not sure this can help or not, but I called the migration externally like this when using testcontainers :

// in drizzle.test.ts
port: process.env.DB_PORT ?? dbosConfig.poolConfig.port,

// in package.json
"migrate:test": "drizzle-kit migrate --config=drizzle.test.ts"

// in the test file (please ignore DBOS stuff) :
const container = new PostgreSqlContainer("pgvector/pgvector:pg16");
result.container = await container.start();
dbosConfig.poolConfig.port = result.container.getPort();
const migrationResult = execSync(
   `DB_PORT=${dbosConfig.poolConfig.port} pnpm migrate:test`
);

[jkazimierczak]

@dnclain sure it was helpful! :) However, if someone like me is a Windows guy, this solution won't work right away. I'm also using testcontainers, so here's a bit more complete example with a bit of context:

1. Make sure to remove import of pushSchema - this causes the above-mentioned error

- import { pushSchema } from "drizzle-kit/api";

2. I'm using DATABASE_URL as a way to specify connection URI to the database:

// drizzle.config.ts

export default defineConfig({
  schema: "./src/core/database/schema.ts",
  out: "./database/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL ?? "",  // This is what truly matters here
  },
  verbose: true,
  strict: true,
});

3. And in the place where you previously run pushSchema use execSync as @dnclain said:

// --force skips the prompt whether to apply push or not
child_process.execSync(`pnpm drizzle-kit push --force`, {
  env: {  // main difference is to set the ENV vars through the execSync option
    ...process.env,
    DATABASE_URL: databaseContainer.getConnectionUri(),
  },
  stdio: "inherit",  // makes it possible to view the output of command (optional)
});

Full example of my database setup with programmatic push:

import {
  PostgreSqlContainer,
  type StartedPostgreSqlContainer,
} from "@testcontainers/postgresql";
import { Client } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import * as schema from "@/core/database/schema";

import { type Database } from "@/core/database/database.module";
import * as child_process from "node:child_process";

const MAX_TIMEOUT_MS = 30 * 1000;

async function migrateDatabase(db: Database) {
  await migrate(db, {
    migrationsFolder: "./database/migrations",
  });
}

async function pushDatabase(databaseUri: string) {
  child_process.execSync(`pnpm drizzle-kit push --force`, {
    env: {
      ...process.env,
      DATABASE_URL: databaseUri,
    },
    stdio: "inherit",
  });
}

export function useDatabase() {
  let databaseContainer: StartedPostgreSqlContainer;
  let databaseClient: Client;
  let db: Database;

  beforeAll(async () => {
    databaseContainer = await new PostgreSqlContainer().start();
    const databaseUri = databaseContainer.getConnectionUri();
    databaseClient = new Client({
      connectionString: databaseUri,
    });
    await databaseClient.connect();

    db = drizzle(databaseClient, { schema });

    if (process.env.CI === "true") {
      await migrateDatabase(db);
    } else {
      await pushDatabase(databaseUri);
    }
  }, MAX_TIMEOUT_MS);

  afterAll(async () => {
    await databaseClient.end();
    await databaseContainer.stop();
  }, MAX_TIMEOUT_MS);

  return () => db;
}

Usage in tests:

describe("BaseDao", () => {
  let db: Database;
  const getDatabase = useDatabase();

  beforeAll(async () => {
    db = getDatabase();
    dao = new TestDao(db);

    // rest of setup
  });

  afterEach(async () => {
    await db.delete(tables.daoTestTable);
  });
})

[dnclain]

@jkazimierczak Thanks for the complete example, sorry I was too busy (lazy?) to elaborate 🥹.
Another possibility : I've seen this solution applied to one of a project I've contributed to worked successfully : #2532 (comment) (this is for pglite but it (should) work also for testcontainers)

[leptonite]

I use this workaround to import from drizzle-kit/api into my ESM code:

import { createRequire } from 'node:module'
const require = createRequire(import.meta.url)
const { generateDrizzleJson, generateMigration } = require('drizzle-kit/api') as typeof import('drizzle-kit/api')

This way the hacky part is limited to my own code and I don’t need to patch node_modules/drizzle-kit/api.mjs as @aminfara noted.

[dnclain]

@leptonite This solution is working like a charm.
I was using bun test that do not have this problem with drizzle migration, but sadly, bun still fails to work with testcontainers. So I was forced to fallback with vitest.
I'm using pgLite for postgres, and testcontainers for mysql in unit tests.

Here what I'm using now:

import type { MySql2Client } from "drizzle-orm/mysql2";
import type { NodePgClient } from "drizzle-orm/node-postgres";

// here your solution
import { createRequire } from "node:module";
const require = createRequire(import.meta.url);
const {
  generateDrizzleJson,
  generateMigration,
  generateMySQLDrizzleJson,
  generateMySQLMigration,
} = require("drizzle-kit/api") as typeof import("drizzle-kit/api");
// end

export async function genMysqlMigration(fromSchema: Record<string, unknown>) {
  const prev = await generateMySQLDrizzleJson({}); // <-- I want to migrate from ground
  const cur = await generateMySQLDrizzleJson(fromSchema); // <-- to top
  return generateMySQLMigration(prev, cur);
}

export async function genPgMigration(fromSchema: Record<string, unknown>) {
  const prev = await generateDrizzleJson({}); // <-- I want to migrate from ground
  const cur = await generateDrizzleJson(fromSchema); // <-- to top
  return generateMigration(prev, cur);
}

export async function applyMigration(
  migrations: string[],
  client: MySql2Client | NodePgClient,
) {
  for (const migration of migrations) {
    try {
      await client.execute(migration);
    } catch (e) {
      console.error("🚨", `Error executing this migration : ${migration}`, e);
      throw e;
    }
  }
}

Thanks for sharing.

[anks2k]

Thank you everyone in this thread starting from the OP to have saving me from having a panic attack.

[uncomplexity]

thank u all

import { createRequire } from 'node:module';
const require = createRequire(import.meta.url);

import type {
  generateDrizzleJson as generateDrizzleJsonType,
  generateMigration as generateMigrationType,
} from 'drizzle-kit/api';

const { generateDrizzleJson, generateMigration } =
  require('drizzle-kit/api') as {
    generateDrizzleJson: typeof generateDrizzleJsonType;
    generateMigration: typeof generateMigrationType;
  };

const NODE_ENV = process.env['NODE_ENV'];
assert(typeof NODE_ENV === 'string');

if (NODE_ENV === 'development') {
  await client.unsafe('DROP SCHEMA IF EXISTS public CASCADE;');
  await client.unsafe('CREATE SCHEMA public;');
  const previous = await generateDrizzleJson({});
  const current = await generateDrizzleJson(schema);
  const statements = await generateMigration(previous, current);
  for await (const statement of statements) {
    await client.unsafe(statement);
  }
}