SoraKumo

Posted on Oct 14 • Updated on Oct 16

Cloudflare + Remix + PostgreSQL with Prisma Accelerate's Self Hosting

#cloudflare #prisma #remix #postgres

Necessity of Self Hosting for Prisma Accelerate

When creating queries with Prisma Client, they are converted into SQL corresponding to each database via the Prisma Engine. The Prisma Engine is written in Rust, and binaries are provided for different environments. However, native binaries cannot be used in environments like Cloudflare Workers/Pages, Vercel Edge Functions, and Deno, so a WebAssembly version of the Prisma Engine is provided. Unfortunately, the WebAssembly version is quite large, around 900KB. Since Cloudflare Workers/Pages' free version and Vercel Edge Functions have a size limit of 1MB, it becomes difficult to deploy alongside other code.

To address this situation, Prisma's official service, Prisma Accelerate, is offered. Prisma Accelerate is a service that separates the Prisma Client from the Prisma Engine and provides it remotely. By utilizing this feature, queries created with Prisma Client are executed on a remote server, eliminating the need for a local engine. This allows you to use Prisma Client without worrying about the size of the Prisma Engine.

As mentioned above, it can be used for free for up to 60,000 queries per month. Beyond that, it costs $18 per million queries. This can lead to significant expenses when access increases, making one want to operate a self-hosted equivalent of Prisma Accelerate. However, since there is no official provision for Self Hosting, it is necessary to come up with a solution independently.

How to Self Host

Package for Self Hosting

A package for Self Hosting Prisma Accelerate is available here.

https://www.npmjs.com/package/prisma-accelerate-local

Sample on Cloudflare and Deno Deploy

The sample uses PostgreSQL, but similar methods can be utilized with other databases. By deploying the following repository, you can Self Host Prisma Accelerate. After that, you simply need to send requests to the issued address to use Prisma Accelerate.

Self Hosting on Deno Deploy

https://github.com/SoraKumo001/prisma-accelerate-deno

You can use up to 1 million requests per month on the free tier. There is no restriction on code size, so you do not need to worry about the size of the Prisma Engine.

Self Hosting on Cloudflare Workers

https://github.com/SoraKumo001/prisma-accelerate-workers

You can use up to 100,000 requests per day on the free tier. In the free plan, an error occurs if the compressed code size exceeds 1MB, so you need to pay attention to the size of the Prisma Engine.

Explanation of Self Hosting Code

The basic operation is handled by prisma-accelerate-local, which will set up the secret needed for remote access and load the WASM files necessary to operate the Prisma Engine, as well as create the Adapter.

import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";
import { createFetcher } from "prisma-accelerate-local/workers";
import WASM from "@prisma/client/runtime/query_engine_bg.postgresql.wasm";

export type Env = {
  SECRET: string;
};

export default {
  fetch: createFetcher({
    runtime: () =>
      require(`@prisma/client/runtime/query_engine_bg.postgresql.js`),
    secret: (env: Env) => env.SECRET,
    queryEngineWasmModule: WASM,
    adapter: (datasourceUrl: string) => {
      const url = new URL(datasourceUrl);
      const schema = url.searchParams.get("schema") ?? undefined;
      const pool = new Pool({
        connectionString: url.toString() ?? undefined,
      });
      return new PrismaPg(pool, {
        schema,
      });
    },
  }),
};

Usage

Creating an API Key

Here's an example using the free PostgreSQL service Supabase. It is recommended to use the pooler on port 6543 for speed.

npx prisma-accelerate-local -s secret -m postgres://postgres.xxxxxxxx:xxxxxxx@aws-0-ap-northeast-1.pooler.supabase.com:6543/postgres

This will generate a key like the following.

eyJhbGciOiJIUzI1NiJ9.eyJkYXRhc291cmNlVXJsIjoicG9zdGdyZXM6Ly9wb3N0Z3Jlcy54eHh4eHh4eDp4eHh4eHh4QGF3cy0wLWFwLW5vcnRoZWFzdC0xLnBvb2xlci5zdXBhYmFzZS5jb206NjU0My9wb3N0Z3JlcyIsImlhdCI6MTcyODgyNTc3NywiaXNzIjoicHJpc21hLWFjY2NsZXJhdGUifQ.Hyn0W8aBbTJ77BhqAuNkJeHEohXaLM7K0AxUtppcz8A

Use this key to set up the connection for Prisma.

.env

DATABASE_URL="prisma://
xxxxxx.xxxxx.workers.dev?api_key=eyJhbGciOiJIUzI1NiJ9.eyJkYXRhc291cmNlVXJsIjoicG9zdGdyZXM6Ly9wb3N0Z3Jlcy54eHh4eHh4eDp4eHh4eHh4QGF3cy0wLWFwLW5vcnRoZWFzdC0xLnBvb2xlci5zdXBhYmFzZS5jb206NjU0My9wb3N0Z3JlcyIsImlhdCI6MTcyODgyNTc3NywiaXNzIjoicHJpc21hLWFjY2VsZXJhdGUifQ.Hyn0W8aBbTJ77BhqAuNkJeHEohXaLM7K0AxUtppcz8A"

Using with Remix

https://github.com/SoraKumo001/cloudflare-remix-accelerate

The above repository explains how to use Prisma Accelerate with Remix.

Environment Variables

Set the connection information for Supabase as a sample.

.env

This is the information needed to execute prisma migrate dev.

DIRECT_DATABASE_URL=postgres://postgres.xxxx:xxxxx@aws-0-ap-northeast-1.pooler.supabase.com:5432/postgres?schema=public

.dev.var

This is the information needed at runtime. Set the address of the Self Hosted Prisma Accelerate and the API Key.

DATABASE_URL=prisma://xxxx.xxxx.workers.dev?api_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Using Prisma Client

app/routes/_index.tsx

Import PrismaClient from @prisma/client/edge. Note that the PrismaClient instance cannot be reused across requests, so you need to create a new instance each time. For more details, refer to this link.

import { PrismaClient } from "@prisma/client/edge";
import { LoaderFunctionArgs } from "@remix-run/cloudflare";
import { useLoaderData } from "@remix-run/react";

export default function Index() {
  const values = useLoaderData<string[]>();
  return (
    <div>
      {values.map((v) => (
        <div key={v}>{v}</div>
      ))}
    </div>
  );
}

export async function loader({
  context,
}: LoaderFunctionArgs): Promise<string[]> {
  const prisma = new PrismaClient({
    datasourceUrl: context.cloudflare.env.DATABASE_URL,
  });
  await prisma.test.create({ data: {} });
  return prisma.test.findMany({ where: {} }).then((r) => r.map(({ id }) => id));
}

Conclusion

By Self Hosting Prisma Accelerate, you can use Prisma Client on the free plans of Cloudflare Workers/Pages and Vercel Edge Functions without worrying about the size of the Prisma Engine. Additionally, when using the WebAssembly version of the Prisma Engine directly, you need to write Adapter settings, but by using Self Hosting for Prisma Accelerate, you can eliminate that hassle. Particularly when considering free plan operations, coexisting frameworks like Remix with the Prisma Engine is practically impossible due to size constraints, making Engine separation essential.

DEV Community