run a query plan and add the right index

Original message was deleted

C

CedricHadjian•12/10/24, 4:42 PM

I've never tried it before, lemme check out what it is, thanks!

JJames run a query plan and add the right index

C

CedricHadjian•12/10/24, 4:44 PM

Would you elaborate?

Original message was deleted

M

Max (@rozenmd)•12/10/24, 4:47 PM

I've been using raw SQL with my paid side projects for about 8 years now, it's honestly fine (and if you're strict about typing your query output, you won't notice the difference)

M

Max (@rozenmd)•12/10/24, 4:47 PM

(we also use raw SQL internally for D1)

MMax (@rozenmd)I've been using raw SQL with my paid side projects for about 8 years now, it's h...

I

Isaac McFadyen•12/10/24, 10:53 PM

^ I'd advocate the use of

zod

zod

or similar because it enforces exact 1:1 match between types and actual output share versus types drifting away from the actual type

I

Isaac McFadyen•12/10/24, 10:53 PM

IMO it's better to have an error thrown from type mismatch than have types that aren't actually correct

K

Kisaragi•12/11/24, 12:21 PM

Is there a way in D1 table schema to define a uuid column?
The

id INTEGER PRIMARY KEY AUTOINCREMENT

id INTEGER PRIMARY KEY AUTOINCREMENT

is only one-digit, I want to replace this with 10-digit uuid. Anyone got a solution for this?
Thanks!!

K

Kisaragi•12/11/24, 12:29 PM

Dug around a bit, I guess manually generating

id = str(uuid.uuid4())

id = str(uuid.uuid4())

is the easiest. I thought there might be some native methods from cloudflare side.

Llimzykenneth Is something going on for D1 at the moment as I'm seeing gateway timeout when ru...

T

TeeLusk•12/11/24, 10:31 PM

We see it every so often on one of our apps as well. Seems to happen the first time the db is requested after sitting idle for a while (like in a dev environment) but then works after the first failure. Haven’t tracked down a cause

AAlexander Thoukydides I am currently seeing a lot of `500 Internal Server Error` errors from D1.

M

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

Whats your database id?

AAlexander Thoukydides I am currently seeing a lot of `500 Internal Server Error` errors from D1.

M

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

Also where are you seeing the errors? wrangler/dash, HTTP API, your own Worker?

Mm0c Random question, a D1 read on an index, that has 0 rows as result, would that al...

L

lambrospetrou•12/13/24, 12:14 AM

It's not the rows in the result that count, but how many rows are read as part of the query execution.

MMatt Silverlock With the initial support for Python + Workers (https://blog.cloudflare.com/pytho...

S

Someone•12/13/24, 1:04 AM

How would I get just the

results

results

from the return Response.json(results) area? I'm getting this:

{"success":true,"meta":{"served_by":"v3-prod","duration":0.1977,"changes":0,"last_row_id":0,"changed_db":false,"size_after":16384,"rows_read":1,"rows_written":0},"results":[{"test":"a"}]}

{"success":true,"meta":{"served_by":"v3-prod","duration":0.1977,"changes":0,"last_row_id":0,"changed_db":false,"size_after":16384,"rows_read":1,"rows_written":0},"results":[{"test":"a"}]}

{"success":true,"meta":{"served_by":"v3-prod","duration":0.1977,"changes":0,"last_row_id":0,"changed_db":false,"size_after":16384,"rows_read":1,"rows_written":0},"results":[{"test":"a"}]}

{"success":true,"meta":{"served_by":"v3-prod","duration":0.1977,"changes":0,"last_row_id":0,"changed_db":false,"size_after":16384,"rows_read":1,"rows_written":0},"results":[{"test":"a"}]}

S

Someone•12/13/24, 1:05 AM

results.get('results')

results.get('results')

throws an error. Also,

type(results)

type(results)

is

undefined

undefined

?

D

dev•12/13/24, 4:09 AM

is pages/d1 available in Canada when using the enterprise plan?

Ddev is pages/d1 available in Canada when using the enterprise plan?

I

Isaac McFadyen•12/13/24, 4:12 AM

Is this for data residency? Both services are available in Canada on all plans but the data doesn't live in Canada.

I

Isaac McFadyen•12/13/24, 4:13 AM

See https://where.durableobjects.live for D1 locations. They're not 1:1 but you can get a sense of where D1 doesn't live based on the colos without Durable Objects on that map (which is all of Canada).

I

Isaac McFadyen•12/13/24, 4:13 AM

Pages uses KV which has 2 central stores, 1 in the US and one in the EU (so again, not in Canada but a Pages site can be accessed from Canada).

IIsaac McFadyen Is this for data residency? Both services are *available* in Canada on all plans...

D

dev•12/13/24, 4:42 AM

it is, yes. data needs to live in Canada for compliance reasons. I know CF doesn't have that region available for regular users, but I thought it was worth asking if Enterprise customers had that option.

D

dev•12/13/24, 4:42 AM

that's basically an entire segment of apps that can't be hosted in CF due to that

D

dev•12/13/24, 4:42 AM

in my case, edtech

Ddev it is, yes. data needs to live in Canada for compliance reasons. I know CF doesn...

H

Hello, I’m Allie!•12/13/24, 4:50 AM

It may help to ask your CSM anyway, as expressing interest in supporting a certain region may make them more likely to prioritize that region in the future

H

Hello, I’m Allie!•12/13/24, 4:50 AM

No guarantees either way, but I don’t think it could hurt

SSomeone How would I get just the `results` from the return Response.json(results) area? ...

L

lambrospetrou•12/13/24, 9:44 AM

The response is an object, not a

Map

Map

Map

Map so the

.get()

.get()

.get()

.get() doesn't exist on objects. You can just do

response.results

response.results

response.results

response.results.

edit: Just realized you are talking about Python, not JavaScript. Not sure if that's different.

A

ak_pikliz•12/13/24, 2:17 PM

I've heard people discuss a 1 d1 db per user paradigm or system design as a way of getting around the size limit

A

ak_pikliz•12/13/24, 2:18 PM

Is there a good article or guide on what this would entail and what people mean when they are talking about this

A

ak_pikliz•12/13/24, 2:19 PM

Like would we have to trigger db creation on registration through the api or something?

Aak_pikliz Like would we have to trigger db creation on registration through the api or som...

M

Max (@rozenmd)•12/13/24, 2:22 PM

pretty much - create DB via the API and patch Worker settings to bind to the new DB over the API again

DOs w/ SQLite are a better fit until we release dynamic bindings for D1

MMax (@rozenmd)pretty much - create DB via the API and patch Worker settings to bind to the new...

L

lambrospetrou•12/13/24, 3:04 PM

I would even say you might not need one per user. Depending on your load you could create N databases (e.g. 10), and hash your users onto those based on some ID they have. If you roughly have an idea of storage needs you can create your databases upfront.

Otherwise, 1 per user might require more work on your side for cross-user queries.

If you will use SQLite DOs in the end, checkout this reference architecture for an introduction: https://developers.cloudflare.com/reference-architecture/diagrams/storage/durable-object-control-data-plane-pattern/

A

AngusMa•12/14/24, 3:47 PM

I’m trying to get all columns in a table by the following code:

var table='PLiFNg5fXiX33oJoBNfuq2yO7exoc_9qJW';
var queries=[env.db.prepare("PRAGMA table_info(\"?\")").bind(table)]
const batchResult = await env.db.batch(queries)

var table='PLiFNg5fXiX33oJoBNfuq2yO7exoc_9qJW';
var queries=[env.db.prepare("PRAGMA table_info(\"?\")").bind(table)]
const batchResult = await env.db.batch(queries)

var table='PLiFNg5fXiX33oJoBNfuq2yO7exoc_9qJW';
var queries=[env.db.prepare("PRAGMA table_info(\"?\")").bind(table)]
const batchResult = await env.db.batch(queries)

var table='PLiFNg5fXiX33oJoBNfuq2yO7exoc_9qJW';
var queries=[env.db.prepare("PRAGMA table_info(\"?\")").bind(table)]
const batchResult = await env.db.batch(queries)

But it threw an error:

Wrong number of parameter bindings for SQL query.

Wrong number of parameter bindings for SQL query.

Wrong number of parameter bindings for SQL query.

Wrong number of parameter bindings for SQL query.

Am I using the

bind

bind

bind

bind function in the wrong way?

Original message was deleted

A

AngusMa•12/14/24, 4:08 PM

How come? I only escaped "

Original message was deleted

A

AngusMa•12/14/24, 4:18 PM

Got it. Thanks.

Original message was deleted

A

AngusMa•12/15/24, 10:22 AM

I changed to

PRAGMA table_info(?);

PRAGMA table_info(?);

PRAGMA table_info(?);

PRAGMA table_info(?); but it threw another error:

near "?": syntax error at offset 18: SQLITE_ERROR

near "?": syntax error at offset 18: SQLITE_ERROR

near "?": syntax error at offset 18: SQLITE_ERROR

near "?": syntax error at offset 18: SQLITE_ERROR

I'm using string concatenation like

"PRAGMA table_info(\""+table+"\")"

"PRAGMA table_info(\""+table+"\")"

"PRAGMA table_info(\""+table+"\")"

"PRAGMA table_info(\""+table+"\")". It worked good.

AAngusMa I changed to `PRAGMA table_info(?);` but it threw another error: ``` near "?": s...

D

D Trombett•12/15/24, 12:08 PM

Did you use bind? It's

db.prepare("PRAGMA table_info(?)").bind(table)

db.prepare("PRAGMA table_info(?)").bind(table)

db.prepare("PRAGMA table_info(?)").bind(table)

db.prepare("PRAGMA table_info(?)").bind(table)

DD Trombett Did you use bind? It's `db.prepare("PRAGMA table_info(?)").bind(table)`

A

AngusMa•12/15/24, 12:09 PM

Yes I did it.

D

datwelk•12/15/24, 1:29 PM

hey guys I keep getting

ERROR] {"D1_RESET_DO":true}

ERROR] {"D1_RESET_DO":true}

ERROR] {"D1_RESET_DO":true}

ERROR] {"D1_RESET_DO":true} or

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset. when trying to import a sql dump. has anyone managed to get this work without this obscure error?

Ddatwelk hey guys I keep getting `ERROR] {"D1_RESET_DO":true}` or `✘ [ERROR] Durable Obje...

D

datwelk•12/15/24, 1:30 PM

npx wrangler d1 execute mydb --file scripts/sql/db.sql --remote

 ⛅️ wrangler 3.95.0
-------------------

:heavy_check_mark: :warning: This process may take some time, during which your D1 database will be unavailable to serve queries.
  Ok to proceed? … yes
:cyclone: Executing on remote database mydb ([redacted]):
:cyclone: To execute on your local development database, remove the --remote flag from your wrangler command.
Note: if the execution fails to complete, your DB will return to its original state and you can safely retry.
:cyclone: File already uploaded. Processing.
:cyclone: Starting import...
:cyclone: Processed 36213 queries.
:cyclone: Processed 52452 queries.
:cyclone: Processed 69008 queries.
:cyclone: Processed 85617 queries.
:cyclone: Processed 101990 queries.
:cyclone: Processed 118607 queries.
:cyclone: Processed 135103 queries.
:cyclone: Processed 151403 queries.
:cyclone: Processed 168062 queries.
:cyclone: Processed 184991 queries.
:cyclone: Processed 201756 queries.
:cyclone: Processed 218587 queries.
:cyclone: Processed 235727 queries.
:cyclone: Processed 252693 queries.
:cyclone: Processed 269614 queries.
:cyclone: Processed 288195 queries.
:cyclone: Processed 305191 queries.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

npx wrangler d1 execute mydb --file scripts/sql/db.sql --remote

 ⛅️ wrangler 3.95.0
-------------------

:heavy_check_mark: :warning: This process may take some time, during which your D1 database will be unavailable to serve queries.
  Ok to proceed? … yes
:cyclone: Executing on remote database mydb ([redacted]):
:cyclone: To execute on your local development database, remove the --remote flag from your wrangler command.
Note: if the execution fails to complete, your DB will return to its original state and you can safely retry.
:cyclone: File already uploaded. Processing.
:cyclone: Starting import...
:cyclone: Processed 36213 queries.
:cyclone: Processed 52452 queries.
:cyclone: Processed 69008 queries.
:cyclone: Processed 85617 queries.
:cyclone: Processed 101990 queries.
:cyclone: Processed 118607 queries.
:cyclone: Processed 135103 queries.
:cyclone: Processed 151403 queries.
:cyclone: Processed 168062 queries.
:cyclone: Processed 184991 queries.
:cyclone: Processed 201756 queries.
:cyclone: Processed 218587 queries.
:cyclone: Processed 235727 queries.
:cyclone: Processed 252693 queries.
:cyclone: Processed 269614 queries.
:cyclone: Processed 288195 queries.
:cyclone: Processed 305191 queries.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

npx wrangler d1 execute mydb --file scripts/sql/db.sql --remote

 ⛅️ wrangler 3.95.0
-------------------

:heavy_check_mark: :warning: This process may take some time, during which your D1 database will be unavailable to serve queries.
  Ok to proceed? … yes
:cyclone: Executing on remote database mydb ([redacted]):
:cyclone: To execute on your local development database, remove the --remote flag from your wrangler command.
Note: if the execution fails to complete, your DB will return to its original state and you can safely retry.
:cyclone: File already uploaded. Processing.
:cyclone: Starting import...
:cyclone: Processed 36213 queries.
:cyclone: Processed 52452 queries.
:cyclone: Processed 69008 queries.
:cyclone: Processed 85617 queries.
:cyclone: Processed 101990 queries.
:cyclone: Processed 118607 queries.
:cyclone: Processed 135103 queries.
:cyclone: Processed 151403 queries.
:cyclone: Processed 168062 queries.
:cyclone: Processed 184991 queries.
:cyclone: Processed 201756 queries.
:cyclone: Processed 218587 queries.
:cyclone: Processed 235727 queries.
:cyclone: Processed 252693 queries.
:cyclone: Processed 269614 queries.
:cyclone: Processed 288195 queries.
:cyclone: Processed 305191 queries.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

npx wrangler d1 execute mydb --file scripts/sql/db.sql --remote

 ⛅️ wrangler 3.95.0
-------------------

:heavy_check_mark: :warning: This process may take some time, during which your D1 database will be unavailable to serve queries.
  Ok to proceed? … yes
:cyclone: Executing on remote database mydb ([redacted]):
:cyclone: To execute on your local development database, remove the --remote flag from your wrangler command.
Note: if the execution fails to complete, your DB will return to its original state and you can safely retry.
:cyclone: File already uploaded. Processing.
:cyclone: Starting import...
:cyclone: Processed 36213 queries.
:cyclone: Processed 52452 queries.
:cyclone: Processed 69008 queries.
:cyclone: Processed 85617 queries.
:cyclone: Processed 101990 queries.
:cyclone: Processed 118607 queries.
:cyclone: Processed 135103 queries.
:cyclone: Processed 151403 queries.
:cyclone: Processed 168062 queries.
:cyclone: Processed 184991 queries.
:cyclone: Processed 201756 queries.
:cyclone: Processed 218587 queries.
:cyclone: Processed 235727 queries.
:cyclone: Processed 252693 queries.
:cyclone: Processed 269614 queries.
:cyclone: Processed 288195 queries.
:cyclone: Processed 305191 queries.

✘ [ERROR] Durable Object's isolate exceeded its memory limit and was reset.

J

jordanfinners•12/15/24, 4:52 PM

Are there any plans to add a change feed to D1, they could be pumped into a queue like R2 notifications? It would be a huge enabler! Please

Ddatwelk hey guys I keep getting `ERROR] {"D1_RESET_DO":true}` or `✘ [ERROR] Durable Obje...

J

JamesOP•12/15/24, 5:04 PM

transaction processing is using too much memory, import less data at a time

JJames transaction processing is using too much memory, import less data at a time

D

datwelk•12/15/24, 5:44 PM

tnx, fixed it by splitting up into 300k rows per file

D

datwelk•12/15/24, 5:46 PM

im running into extremely slow latency from a cloudflare pages remix app (99 percentile 18 seconds). smart placement is enabled on the pages and d1 db has the right indices. any way to improve performance?

Ddatwelk im running into extremely slow latency from a cloudflare pages remix app (99 per...

J

JamesOP•12/15/24, 5:47 PM

measure the latency of the request and all sub requests (incl db/binding calls) to pinpoint the issue

JJames measure the latency of the request and all sub requests (incl db/binding calls) ...

D

datwelk•12/15/24, 6:06 PM

in the d1 console a simple select * query time is fast (4000 rows) but response time really slow: Response time 2009ms, query time 15.03ms
im on the paid plan

K

karenr•12/16/24, 8:59 AM

Hi,

I am new to the cloudflare d1. I am confused as to why the local data persist when deploying to production? I don't want the local data used for testing to be part of the production build. Only when deleting the local data inside the .wrangler/state/v3/d1/* files it displays the production data during deployment.

right now, I have 21 records in the d1 dashboard and locally I have 5 records. But on production the 5 records are displaying instead of the 21 records. Am I missing something? Or is this how the cloudflare build works?

I am using opennext to deploy next.js in cloudflare. these are the default script during the opennext cloudflare set up

"scripts": {
"dev": "next dev",
"build": "next build",
"start": "next start",
"lint": "next lint",
"deploy": "cloudflare && wrangler deploy",
"preview": "cloudflare && wrangler dev",
"cf-typegen": "wrangler types --env-interface CloudflareEnv env.d.ts",
"build:worker": "cloudflare",
"dev:worker": "wrangler dev",
"preview:worker": "cloudflare && wrangler dev",
"deploy:worker": "npm run build:worker && wrangler deploy",
},

Appreciate any enlightenment. Also if this was previously asked and resolved, please kindly share the links to the post. Thank you!

Kkarenr Hi, I am new to the cloudflare d1. I am confused as to why the local data persi...

L

lambrospetrou•12/16/24, 12:20 PM

Local files are not deployed in production.

But on production the 5 records are displaying instead of the 21 records.

What exactly do you mean by this? You said that the D1 Dashboard correctly shows the 21 records, so what is "production" in this sentence that still shows 5 records?

run a query plan and add the right index

Similar Threads

Similar Threads

Similar Threads