Cloudflare Developers•6mo ago

Error occurs accessing postgres with drizzle and postgres.js in Remix project

I've created a Remix project using create-cloudflare CLI as guided in https://developers.cloudflare.com/pages/framework-guides/deploy-a-remix-site/ and then I installed drizzle-orm and postgres, but I've got this error when I executed a simple select query:

Unexpected Server Error Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. Received protocol 'cloudflare:

Unexpected Server Error Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. Received protocol 'cloudflare:

The code:

export async function loader({ context }: LoaderFunctionArgs) {
  const client = postgres(context.cloudflare.env.DATABASE_URL);
  const db = drizzle(client);
  const users = await db.select().from(userTable); // <- error here
  return json({ users });
}

export async function loader({ context }: LoaderFunctionArgs) {
  const client = postgres(context.cloudflare.env.DATABASE_URL);
  const db = drizzle(client);
  const users = await db.select().from(userTable); // <- error here
  return json({ users });
}

This happens while running the app (remix vite:dev) locally.

2 Replies

lessquo•6mo ago

This issue also reported on postgres GitHub: https://github.com/porsager/postgres/issues/822

GitHub

Unexpected Server Error Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: On...

Unexpected Server Error Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. Received protocol 'cloudflare:' i a...

lessquo•6mo ago

I also tried with node-postgres:

export async function loader({ context }: LoaderFunctionArgs) {
  const client = new pg.Client(context.cloudflare.env.DATABASE_URL);
  await client.connect();
  const db = drizzle(client);
  const users = await db.select().from(userTable);
context.cloudflare.ctx.waitUntil(client.end());
  return json({ users });
}

export async function loader({ context }: LoaderFunctionArgs) {
  const client = new pg.Client(context.cloudflare.env.DATABASE_URL);
  await client.connect();
  const db = drizzle(client);
  const users = await db.select().from(userTable);
context.cloudflare.ctx.waitUntil(client.end());
  return json({ users });
}

and it works fortunately. However, it's quite slow (about 800ms). I guess it's because of calling connect and end, causing a roundtrip maybe.

Gaming

Programming

Error occurs accessing postgres with drizzle and postgres.js in Remix project