[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.