It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appears to be due to a js

It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appears to be due to a json_array() that is pulling around 34 columns :

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

The query is generated via Drizzle Query API (https://orm.drizzle.team/docs/rqb) so it uses json_array for the nested items.
Unfortunately all of them are pretty legitimate columns...I guess I'll have to find some columns I can do without.

It seems that regular sqlite has a limit of 100 arguments (https://www.sqlite.org/limits.html). Any particular reason why D1 has settled on a limit of 32? I found this: https://github.com/cloudflare/workerd/commit/cfe19fdd962c2898dcb4a4bd0c4e1b2a60949f74

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

GitHub

Increasing `SQLITE_LIMIT_FUNCTION_ARG` to 32 · cloudflare/workerd@c...

Also adding a test to demonstrate a

json_object

json_object

json_object

json_object query that requires the higher limit

PPatrick M It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appea...

Max (@rozenmd)•4/16/24, 9:33 AM

Any particular reason why D1 has settled on a limit of 32?

https://www.sqlite.org/security.html has 8, someone made a GitHub issue asking for more, we increased it - we'd appreciate a github issue if you'd like more: https://github.com/cloudflare/workerd/issues

Hannes•4/16/24, 3:15 PM

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()). 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 KEYSINTEGER PRIMARY KEYS. Prisma generates TEXT NOT NULL PRIMARY KEYTEXT 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'); shows one sqlite autoindex for each table, for example sqlite_autoindex_Organization_1sqlite_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.

Hannes•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!

Hannes•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 info

npx 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 1000

SELECT * 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_at
```
```
updated_at
```
and
```
name
```
```
name
```
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

It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appears to be due to a js

Similar Threads

Similar Threads

Similar Threads