Hi, quick question about Indices for D1. I'm trying to improve my queries so that fewer rows have to

Hi, quick question about Indices for D1. I'm trying to improve my queries so that fewer rows have to be read. I'm using Prisma as my ORM and all my IDs are

@id @default(uuid())

@id @default(uuid())

@id @default(uuid())

@id @default(uuid()). Do I need to add an explicit index for each id column? The docs say that they are added automatically, but only for

INTEGER PRIMARY KEYS

INTEGER PRIMARY KEYS

INTEGER PRIMARY KEYS

INTEGER PRIMARY KEYS. Prisma generates

TEXT NOT NULL PRIMARY KEY

TEXT NOT NULL PRIMARY KEY

TEXT NOT NULL PRIMARY KEY

TEXT NOT NULL PRIMARY KEY id columns.

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index'); shows one sqlite autoindex for each table, for example

sqlite_autoindex_Organization_1

sqlite_autoindex_Organization_1

sqlite_autoindex_Organization_1

sqlite_autoindex_Organization_1. Is that sufficient or will explicit id indices help with querying?
Thanks in advance!

HHannes Hi, quick question about Indices for D1. I'm trying to improve my queries so tha...

Matt Silverlock•4/16/24, 5:22 PM

You likely want an index on your ID PK if there isn't one.

MMatt Silverlock You likely want an index on your ID PK if there isn't one.

HannesOP•4/16/24, 5:23 PM

Hi Matt, that is what I suspected. Thanks for the clarification!

HHannes Hi Matt, that is what I suspected. Thanks for the clarification!

Matt Silverlock•4/16/24, 5:31 PM

Surprised Prisma doesn't auto-create one, actually!

MMatt Silverlock Surprised Prisma doesn't auto-create one, actually!

HannesOP•4/16/24, 5:37 PM

I can't find them. There are indices for foreign keys on the other side, but not for ids. I'll see if I create an issue there

adjlsncm•4/16/24, 10:09 PM

When developing against D1 locally, every single line change requires a rebuild using cloudflare@next-on-pages

adjlsncm•4/16/24, 10:09 PM

Is there a way to have the D1 locally running and usable without needing to do all that

adjlsncm•4/16/24, 10:10 PM

So I can still use fast refresh from next via run dev instead?

Original message was deleted

adjlsncm•4/16/24, 10:27 PM

thank you!

Original message was deleted

adjlsncm•4/16/24, 11:28 PM

do i also have to change how im accessing the DB? using setupDevPlatform in next.config didnt seem to change anything

adjlsncm•4/16/24, 11:36 PM

the docs are just really bad at telling you how to access the env variables for some reason theres a billion different ways

adjlsncm•4/16/24, 11:52 PM

everything seems to be the same/aligned so yeah i have no idea

adjlsncm•4/16/24, 11:52 PM

seeing about 6 different variations of getRequestContext being called in the docs

Fabian•4/16/24, 11:53 PM

Is it only me or are D1 queries extremely slow in deployed Workers?
Running the same query using wrangler d1 execute it says executed in ~0.1ms, and local performance measuring also outputs <1ms.
The second it's deployed I can run the same query 100 times and it always takes >10ms. It's a very very simple query on a small table so I expected it to behave more like in wrangler (<1ms)

kian•4/16/24, 11:54 PM

Are you measuring the .meta.duration.meta.duration or how long it takes to get a response?

adjlsncm•4/16/24, 11:57 PM

i have @cloudflare/next-on-pages installed via npm so what gives

Kkian Are you measuring the `.meta.duration` or how long it takes to get a response?

Fabian•4/17/24, 12:06 AM

Yeah it seems like .meta.duration is indeed the same as reported in wrangler d1 execute. But why is the difference so extreme?

Unsmart•4/17/24, 12:08 AM

network

Fabian•4/17/24, 12:10 AM

Probably because the worker runs in the nearest location to my machine, but the D1 replica is not in this location, right?

Unsmart•4/17/24, 12:12 AM

D1 doesnt have replicas (yet) but yes D1 only runs in some locations

Fabian•4/17/24, 12:15 AM

Ok, thanks for the info :)

Aadjlsncm i have @cloudflare/next-on-pages installed via npm so what gives

adjlsncm•4/17/24, 1:43 AM

apparently getRequestContext just doesnt work sometimes? so you cant develop locally with D1? im just going to opt for planetscale then because half my time is spent researching why local development with D1 isnt working

Brayn•4/17/24, 2:16 AM

Hello why am I getting this on all my website I uploaded

Brayn•4/17/24, 2:16 AM

Error 1101 Ray ID: 8758b630f8926652 • 2024-04-17 01:41:15 UTC
Worker threw exception
What happened?
You've requested a page on a website (1a72d5b2.mergeappsevers.pages.dev) that is on the Cloudflare network. An unknown error occurred while rendering the page.
What can I do?
If you are the owner of this website:
you should login to Cloudflare and check the error logs for 1a72d5b2.mergeappsevers.pages.dev.
Cloudflare Ray ID: 8758b630f8926652
Your IP: Click to reveal
Performance & security by Cloudflare

CaptainNemo•4/17/24, 5:05 AM

Just wanna say, I am loving the D1 developer experience so far. It strikes the perfect balance between boring technology (it's "just SQL", with all the straightforwardness and dev speed that that offers) and shiny new technology (deploying via Wrangler is so fast!!). I'm building a SvelteKit app, and having a database that Just Works like D1 does is awesome.

Original message was deleted

Max (@rozenmd)•4/17/24, 7:33 AM

There is a plan, but that's as much detail as I have

Max (@rozenmd)•4/17/24, 7:34 AM

once it's live, it'll take half a day for us to setup D1 to know how to create DBs there, like we did for Australia

Elliot Hesp•4/17/24, 10:49 AM

Hey all, we have a simple status endpoint which we use for uptime on a worker which uses D1, using Drizzle ORM. We have a schedule which checks every 1 minute this endpoint.

The code is simple:

app.get("/status", async (c) => {
  await getDatabaseClient(c.env).select().from(schema.variables).limit(1);
  return c.text("OK");
});

app.get("/status", async (c) => {
  await getDatabaseClient(c.env).select().from(schema.variables).limit(1);
  return c.text("OK");
});

Every so often (maybe once every few weeks) we get alerts coming through reporting the same error:

Error: D1_ERROR: Error 9000: something went wrong

Error: D1_ERROR: Error 9000: something went wrong

No other errors / messages. Any idea what causes this? We use D1 for a critical part of our infra, so these sort of "unexplained" issues are a little concerning for us. Thanks!

EElliot Hesp Hey all, we have a simple status endpoint which we use for uptime on a worker wh...

Max (@rozenmd)•4/17/24, 11:12 AM

Hey Elliot, let me investigate - can you let me know the accountId and databaseId?

MMax (@rozenmd)Hey Elliot, let me investigate - can you let me know the accountId and databaseI...

Elliot Hesp•4/17/24, 11:13 AM

Thanks and sure - want me to DM or ok in here?

Elliot Hesp•4/17/24, 11:14 AM

I have DM'd you

Max (@rozenmd)•4/17/24, 11:32 AM

Just a reminder:

Max (@rozenmd)•4/17/24, 11:32 AM

?d1-alpha-migration

Flare•4/17/24, 11:32 AM

On the off-chance you're still using an alpha D1 database, we've made a guide to make it easier to move across to the newer, faster (40x more requests per second) D1 DBs:

https://developers.cloudflare.com/d1/platform/alpha-migration

Max (@rozenmd)•4/17/24, 11:32 AM

run npx wrangler d1 infonpx wrangler d1 info if you're not sure which version you're on (it'll tell you if it's alpha)

EElliot Hesp Hey all, we have a simple status endpoint which we use for uptime on a worker wh...

Matt Silverlock•4/17/24, 1:09 PM

For others here as well: you're using the original alpha DB infra, which was superseded in July last year and by default in Sept when D1 went into open beta.

https://developers.cloudflare.com/d1/platform/changelog/#d1-alpha-databases-will-stop-accepting-live-sql-queries-on-july-1-2024

Cloudflare Docs

Changelog · Cloudflare D1 docs

Previously, D1’s HTTP API API link label Open API docs link returned a HTTP 500 Internal Server error for an invalid query. An invalid SQL query now …

rye•4/17/24, 2:31 PM

Doing some load testing and getting weird results. Any recommendations to get the average response time down to something manageable so I can use D1 in the wild

Doing simple query SELECT * FROM table_name WHERE updated_at >= ? AND name > ? ORDER BY updated_at ASC, name LIMIT 1000SELECT * FROM table_name WHERE updated_at >= ? AND name > ? ORDER BY updated_at ASC, name LIMIT 1000

100k row table, 17mb
30s load test, 1k requests, average response time is 40s
normal response time is 230ms
Confirmed it's using an index with updated_atupdated_at and namename
using cloudflare worker to query database

F0rce•4/17/24, 3:04 PM

is there a way to some sort of "prioritise" this issue ? This has been around for quite some time (and fixed in prod / when deployed) but still happening in local development. With D1 reaching GA I think this should be fixed, before other people fall over it.

Unsmart•4/17/24, 5:56 PM

how are you determining where it is running?

Unsmart•4/17/24, 5:58 PM

Im 99% sure that the request.cf.colo will be the one you connect to and not where the worker actually runs in the end. I think theres supposed to be a way to know where its placing but dont know off the top of my head will try to find out

Isaac McFadyen•4/17/24, 5:59 PM

Yeah I think request.cf.colo will be the ingress colo. And 0.2ms is quite good to be honest - unless you mean 0.2s?

Hi, quick question about Indices for D1. I'm trying to improve my queries so that fewer rows have to

Similar Threads

Similar Threads

Similar Threads