Preview feature feedback: @prisma/adapter-d1 / Cloudflare D1 support

[janpio]

Support for the Cloudflare D1 is in preview behind the driverAdapters preview feature with the release of 5.12.0 on April 2nd.

Please share your feedback about @prisma/adapter-d1 in this discussion.

• If you encounter a bug, please open a bug report in this repo.
• If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.
• If you have any questions, don't hesitate to ask them in the Prisma Discord community

[m-dierker]

Feedback: The Prisma engine adds about 2MB to the Cloudflare D1 bundle size. This means that free tier users (with a 1MB bundle size limit) are unable to use Prisma without confusing errors. 😜 The minimum cost to use Prisma becomes $60/yr. I wound up switching to Drizzle for my project.

Most of the engine seems to be a 2MB seems to be a giant webassembly file. Cloudflare doesn't count static assets against the 1MB limit. I wonder if there's a way to load it in asynchronously as a static asset rather than compiling it into the function.

[janpio]

Yes, this is an unfortunate limitation on our side right now. The Webassembly file actually clocks in at ~785kb gzipped, which is what Cloudflare counts towards the bundle size limit. So small apps can actually be deployed in the free account.

But good you found a solution that works for you. As soon as we make progress here, we will let you know and you can consider switching back if that is still an option then. Thanks!

(The Webassembly file unfortunately has to be part of the function as far as I know, as it is loaded by the function and executed - which is not allowed for static assets. But thanks for mentioning this idea anyway.)

[janpio]

A related issue: #23350

[hrueger]

Feedback: It's working generally well for me, I have a couple of smaller applications deployed already using the D1 adapter. However, my main application has more than 100 rows in almost every table and I'm running into trouble here:
I'm usually not someone to ping people or constantly beg for issues to be resolved, but I'd really appreciate if someone took a look at #23743. This is quite a bummer since it affects almost every query in that app (you only need more than 100 rows and select with one relation to trigger that bug). I believe this affects lots of people once they start running production apps.
Thanks in advance!

[hrueger]

Update: I've found a hacky workaround for that bug by inlining escaped variables if there are more than 100. However, now that I'm actually running the app with data on Cloudflare Workers, I noticed some performance issues. On my VPS, page load times were about 400-600ms in total, now we're looking at 1800 - 2500ms... I'm trying to figure out where this is coming from, but I suspect prisma to be the culprit. I tried to implement Tracing with OpenTelemetry to see query duration, but that does not work on Workers due to it using node apis like process, etc

[hrueger]

I built a super simple test app which deletes a row and recreates it 100 times. That means, I do 200 round trips to the database. Here are the request durations:

• Locally with node and miniflare: 500 - 600ms
• Locally with wrangler dev and miniflare: 150 - 160ms
• On Workers: 7500 - 7800ms
  That said, now it looks like the D1 database itself might be so slow? The metrics in the dashboard show a constant latency of 0ms.

Edit: It indeed looks like this. I patched the d1 adapter like so:

      let start = performance.now();
      const stmt = this.client.prepare(query.sql).bind(...query.args);
      console.log(`[js::performIO] prepare/bind took ${performance.now() - start}ms`);
      start = performance.now();
      if (executeRaw) {
        const r = (0, import_driver_adapter_utils2.ok)(await stmt.run());
        console.log(`[js::performIO] run took ${performance.now() - start}ms`);
        return r;
      } else {
        const [columnNames, ...rows] = await stmt.raw({ columnNames: true });
        const r = (0, import_driver_adapter_utils2.ok)([columnNames, rows]);
        console.log(`[js::performIO] raw took ${performance.now() - start}ms`);
        return r;
      }

It prints:

  (log) [js::performIO] prepare/bind took 0ms
  (log) [js::performIO] raw took 30ms
  (log) [js::performIO] ok took 0ms

(note that prepare/bind and ok will always be 0, because timers only advance after io).
According to this chart it should be more like 1.8ms... (that article is about sql processing time, not network latency / round trip time).

Edit 2:
The problem has been solved. "Smart Placing" improves the latency to D1 massively and fixes the issue.

[steebchen]

Thanks for the "smart placing" tip!

[derekhearst]

I often write queries with many to many relationships being included, and it errors most of the time due to D1 having a very small parameter max length.

I would really like to see joins being supported so I don't have to query raw.

I am really happy with the performance.

[janpio]

Are there related issues tracking this problem? If so, please link to them here. If not, please open one. Thanks!

Probably related, but not an issue for the actual problem this is causing: #23348 (comment)

[jamiehaywood]

feedback around the D1 developer workflow:

Generally really nice & well thoughout. I have a use-case where my local .wrangler/../miniflare-D1DatabaseObject/xx.sqlite is in the root of my monorepo, so I'm not able to run the suggested prisma migrate script - npx prisma migrate diff --from-local-d1 --to-schema-datamodel ./schema.prisma --script --output migrations/0002_second_migration.sql. I think the --from-local-d1 should accept an optional argument to point to the .wrangler folder.

[janpio]

Oh monorepos, of course. Thanks for mentioning that!

Can you maybe quickly create a feature request issue with this? Then we will put that into the backlog for our D1 support.

[alex-mironov]

Do I understand correctly that in each loader I'd need to create a new prisma client, like this?

  const adapter = new PrismaD1(env.DB)
  const prisma = new PrismaClient({ adapter })

Of course I can add a function to make it reusable, but is it how it's supposed to be used? What about $connect/$disconnect, should I care about them?

export const getPrisma = (env: Env) => {
  const adapter = new PrismaD1(env.DB)
  const prisma = new PrismaClient({ adapter })
  return prisma
}

Are there any disadvantages of such approach? Is there a way to create a single instance of PrismaClient and use it across the app?

[stewarthsoj]

I ended up initializing the Prisma client in some middleware and sharing the client via the request context.

[Noitidart]

Why would you need to share via request context @JoshStwrt ? A simple util to import getPrisma seems cleaner solution no?

[zshannon]

Something's exploding here on inserts and it's killing performance. Here are some benchmarks from inserting 10, 100, 1000 new rows using prisma.createMany, prisma.$executeRawUnsafe, and d1.exec. prisma.$executeRawUnsafe and d1.exec are close enough, but prisma.createMany blows up with row count.

Running on Worker (with region hint wnam, making requests from SF, CA, USA)

(median of 10 runs)	Prisma.createMany ms	Prisma.$executeRawUnsafe ms	D1.exec ms
10 records	33	33	32
100 recrods	98	35.5	35
1000 records	778.5	42.5	46

Running on localhost (M1 Max)

(median of 10 runs)	Prisma.createMany ms	Prisma.$executeRawUnsafe ms	D1.exec ms
10 records	9	5	2
100 recrods	19	4	2
1000 records	81	9	4

You can test the worker version here, which is running the code from this repo/branch

export async function writeProperties(count: number) {
	const t0 = performance.now()
	const data = Array.from({ length: count })
		.fill(null)
		.map(() => ({ key: createId(), value: createId() }))
	const t1 = performance.now()
	await prisma.property.createMany({
		data,
	})
	const t2 = performance.now()
	const values = Array.from({ length: count })
		.fill(null)
		.map(() => `('${createId()}', '${createId()}')`)
		.join(', ')
	const sql = `INSERT INTO Property (key, value) VALUES ${values}`
	const t3 = performance.now()
	await prisma.$executeRawUnsafe(sql)
	const t4 = performance.now()
	const values2 = Array.from({ length: count })
		.fill(null)
		.map(() => `('${createId()}', '${createId()}')`)
		.join(', ')
	const sql2 = `INSERT INTO Property (key, value) VALUES ${values2}`
	const t5 = performance.now()
	await process.env.DB.exec(sql2)
	const t6 = performance.now()
	return {
		'D1.exec': t6 - t5,
		'Prisma.createMany': t2 - t1,
		'Prisma.$executeRawUnsafe': t4 - t3,
	}
}

"@prisma/adapter-d1": "5.21.0",
"@prisma/client": "5.21.0",

[zshannon]

I should add: this benchmark is for 2 columns of 24 character strings. I originally ran into the performance issue for an application that stores Google Calendar events in rows that are 40 columns (~2000 characters of text) wide, and in that case upsert on just 100 rows takes 30 seconds!

[nicolaspapp]

I have feedback regarding the D1 adapter. From what I am seeing in the logs, nested reads are not being done on either the include or select statements. I have even tried adding relationJoins as preview feature but when I see the logs it is still doing the extra queries.
I believe this could be very important for having performant queries. Smaller priority but nested writes would also be handy