Migrator assumes running as a ESModule

[TheDevMinerTV]

The migrator assumes that it can use import here: https://github.com/tim-smart/sqlfx/blob/main/packages/sql/src/Migrator/Node.ts#L32
This makes the migrator fail in projects that haven't migrated over to ESM yet.

This also breaks on Windows where the following code breaks loading:

const MigratorLive = Layer.provide(
	PgLive,
	Migrator.makeLayer({
		loader: Migrator.fromDisk(fileURLToPath(new URL('migrations', import.meta.url))),
		schemaDirectory: 'src/migrations'
	})
);

which fails with:

node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

[Error: {"reason":"import-error","message":"Could not import migration \"1_init\"\n\nError [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. On Windows, absolute paths must be valid file:// URLs. Received protocol 'd:'","_tag":"MigrationError"}] {
  toJSON: [Function (anonymous)],
  toString: [Function (anonymous)],
  [Symbol(effect/Runtime/FiberFailure)]: Symbol(effect/Runtime/FiberFailure),
  [Symbol(effect/Runtime/FiberFailure/Cause)]: {
    _tag: 'Parallel',
    left: { _tag: 'Empty' },
    right: {
      _tag: 'Fail',
      error: {
        reason: 'import-error',
        message: 'Could not import migration "1_init"\n' +
          '\n' +
          "Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. On Windows, absolute paths must be valid file:// URLs. Received protocol 'd:'",
        _tag: 'MigrationError'
      }
    }
  },
  [Symbol(nodejs.util.inspect.custom)]: [Function (anonymous)]
}

Related: nodejs/node#31710 nodejs/node#34765

[tim-smart]

Sounds like the opposite of #132

Will likely need feature detection to work around it.

[TheDevMinerTV]

I just noticed that you're also relying on pg_dump which bad, since it might not be installed already.

[TheDevMinerTV]

To get around loading from disk, I've written this code (and confirmed that it works):

import { tag } from '@sqlfx/pg';
import { Loader, ResolvedMigration } from '@sqlfx/sql/Migrator';
import { Effect, ReadonlyArray } from 'effect';

export const getMigrations = (): Loader =>
	Effect.succeed(
		ReadonlyArray.of([
			1,
			'init',
			Effect.succeed(
				Effect.flatMap(
					tag,
					(sql) => sql`
					CREATE TABLE ips (
						id serial PRIMARY KEY,
						name varchar(255) NOT NULL,
						notified boolean NOT NULL DEFAULT false,
						created_at TIMESTAMP NOT NULL DEFAULT NOW(),
						updated_at TIMESTAMP NOT NULL DEFAULT NOW()
					)
				`
				)
			)
		] satisfies ResolvedMigration)
	);