Cloudflare Pages, vars, and helper code

[scarlet kraken]

I'm trying to build a DB utility class (app/utils/db.server.js) using prisma, following the pattern that we've all seen in the various tutorials. I understand that in Cloudflare Pages, prisma's client cannot access the DATABASE_URL env var the way it would in Node, so you have to pass it in (ie, new PrismaClient( { datasources: { db: { url: "prisma://blah" } })

It's not good practice to hard-code database urls as strings. So I want to use cloudflare env vars, but those are only accessible from the context which is passed to loaders/actions/etc. So there doesn't seem to be a way, other than threading it through from the loader. so every time I want to use the db it's got to be "db(context.cloudflare.env.DATABASE_URL).findMany(blah)" ?

Is this right? Is there some other way I should be accessing vars from modular/util/helper code?

[polar mulch]

That's right.

All of your assumptions here are correct. You need to instantiate your DB for every request, which you have to do with any kind of serverless anyway.

This gets better once Single Fetch is available in Remix, but until then you can do some singleton slight-of-hand to make it so its only instantiated once per request, not once per loader. (though that might backfire without Single Fetch, IDK)

As an aside, I didn't know that Prisma worked on Cloudflare Workers/Pages. You might want to double check. If you're not using Prisma, you'll have a better time, since other ORMs and query builders can instantiate faster and lighter.

[scarlet kraken]

Ok, cool. And yes, Prisma works with Cloudflare. There are a few ways to do it. I am using Prisma Accelerate, which seems to be the most performan,t reliable and future-proof way to do it. It basically offloads the query engine to their connection pooling service at the edge. So the client you generate is much smaller because it doesn't contain the query engine, and everything's over HTTP not TCP.

There's another approach involving database connection adapters like node-postgres. Both approaches are described here:

https://www.prisma.io/docs/orm/prisma-client/deployment/edge/overview

and
https://www.prisma.io/docs/accelerate/getting-started

[scarlet kraken]

actually, I figured out a nice way to handle this by adding the db to the AppLoadContext. That DRY's up initializing the db initialization, and ensures it's only created once per request