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

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.

Am I using the

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(?);

but it threw another 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+"\")"

. 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)

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}

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.

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

James•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

James•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?

D

datwelk•12/16/24, 12:40 PM

how are we supposed to do long running maintenance operations in d1? cloudflare worker cron has cpu time limit of 15 mins, and generating an exported .sql file and executing it on d1 causes the db to be down for that time. any other options aside from launching a new worker as an api to my d1 instance?

Ddatwelk how are we supposed to do long running maintenance operations in d1? cloudflare ...

L

lambrospetrou•12/16/24, 1:25 PM

how are we supposed to do long running maintenance operations in d1?

What do you want to do exactly? What's "maintenance operations" here? A D1 database processes 1 request at a time and since SQLite queries are usually a few milliseconds you can execute many per second, But, if you are doing something that takes more than a few seconds/minutes, then it won't behave nicely for several reasons.

Ddatwelk in the d1 console a simple select * query time is fast (4000 rows) but response ...

L

lambrospetrou•12/16/24, 1:36 PM

The REST API is much slower than the performance you would get from using the Worker API using the D1 binding, due to going through some central locations. Hence the 2s latency for your end to end latency.

D

datwelk•12/16/24, 2:03 PM

i see, thanks @lambrospetrou. i had some other issues in my sqlite db too like missing foreign keys. i fall into the trap of letting anthropic / claude do some boilerplate work, but 9 out of 10 times it costs you more time than if you would do it yourself

D

datwelk•12/16/24, 2:04 PM

example of a long running maintenance operation is moving all data from one table to another after modifying / adding some foreign key constraints. this consistently fails in the web console for 2 million rows, i had to reduce it to 100k rows max

Ddatwelk example of a long running maintenance operation is moving all data from one tabl...

L

lambrospetrou•12/16/24, 2:13 PM

Yeah schema migrations for whole tables would block your database, no way around that at the moment. Splitting your migrations in manageable sizes is the usual recommendation if and when it's possible.

R

rob•12/16/24, 3:04 PM

We have been running into some basic issues with migrations on D1. Has anyone else experienced issues with D1 failing on queries for tables larger than 30MB or found workarounds for modifying columns given the lack of ALTER TABLE support?

Rrob We have been running into some basic issues with migrations on D1. Has anyone el...

D

datwelk•12/16/24, 6:10 PM

i managed to get it working by splitting my migration files into chunks of 300k lines each. if any of those fail with D1_RESET_DO that means a constraint is failing. try isolating the failing query by executing it on the web console

Llambrospetrou > how are we supposed to do long running maintenance operations in d1? What do ...

D

datwelk•12/16/24, 7:45 PM

something simple as pruning all records older dan x date also consistently fails (eg: Error: D1_ERROR: internal error). it is supposed to delete around 200k rows in a 3 million row db. query: DELETE FROM Foo WHERE bar < x. table has index on bar. any advice how to improve this?

Ddatwelk something simple as pruning all records older dan x date also consistently fails...

J

James•12/16/24, 7:49 PM

get the ids first and batch out the deletes

JJames get the ids first and batch out the deletes

D

datwelk•12/16/24, 8:06 PM

d1 has limit of 100 variables, so when binding the ids for the delete i should have batch size 100? if i run this in a worker it will fail after max 1000 api requests no?

J

James•12/16/24, 8:09 PM

youd have to chain them via queue or worker request likely

J

James•12/16/24, 8:09 PM

thats just one way, you could limit your delete another way, just add additional bounds to the delete where clause and step through it

JJames youd have to chain them via queue or worker request likely

D

datwelk•12/16/24, 8:11 PM

cheers, will step through it with some pagiantion client / server interplay

J

James•12/16/24, 8:11 PM

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

J

James•12/16/24, 8:12 PM

id bet you could bypass the 100 id limit with a subquery, could just be an api limitation, not sqlite

Ddatwelk d1 has limit of 100 variables, so when binding the ids for the delete i should h...

L

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

The limit is for how many variables you can

bind()

bind()

bind()

bind(), not how many rows you can operate on.

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

D

dave•12/16/24, 8:20 PM

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

Similar Threads

Similar Threads

Similar Threads