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

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

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

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

, 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

You may like using Workers + Hyperdrive

Llambrospetrou The limit is for how many variables you can `bind()`, not how many rows you can ...

D

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

but copying 2 million rows from one table to another i havent gotten to work yet, this presumably then hits some query timeout limits?

R

RexNormous•12/17/24, 2:01 AM

Are there any tips to decrease the rate of

D1_ERROR: Network connection lost

D1_ERROR: Network connection lost

errors?

I am see a concerning increase in these that is causing issues in my application.

RRexNormous Are there any tips to decrease the rate of `D1_ERROR: Network connection lost` e...

D

Dean•12/17/24, 2:05 AM

not that i'm aware of. i get about 1-2 per day that i monitor on my worker. Only real solution is re-attempt the query

A

andifined.dev•12/17/24, 10:20 AM

I‘m looking for an architecture example of how to scale D1 per user as listed within the First Couple of lines in the documentation. Can someone help me?

Aandifined.dev I‘m looking for an architecture example of how to scale D1 per user as listed wi...

L

lambrospetrou•12/17/24, 10:36 AM

There isn't much advertised architecture really. The idea is that you create more than 1 database depending on your needs.
If your data can be sharded in a manageable way, you can create N databases, and shard your data onto those databases.
The extreme case is when you have a new database for each individual user/account, but right now you need a bit of extra work to create and attach those databases dynamically through the REST API.

My suggestion is the first one, a couple of databases (5-20) and sharding your data across them depending on your load/access needs. But as always, it depends what you want to do, and what you data looks like, and how your access patterns look like.

Z

Zn4rK•12/17/24, 10:36 AM

Has sessions for D1 shipped? Would the session api allow for transactions since it's sequential consistent for that session?

ZZn4rK Has sessions for D1 shipped? Would the session api allow for transactions since ...

L

lambrospetrou•12/17/24, 10:38 AM

By sessions, do you refer to the read replication feature? Which blog post do you have in mind?

Llambrospetrou By sessions, do you refer to the read replication feature? Which blog post do yo...

Z

Zn4rK•12/17/24, 10:42 AM

Yes, the read replication feature.

This blog post:
https://blog.cloudflare.com/building-d1-a-global-database/

But it actually doesn't mention a timeline there, I don't know where I got end of the year from. I've been reading up on DO SQLite and D1 quite a lot today, so it might just be wishful thinking from my side

ZZn4rK Yes, the read replication feature. This blog post: https://blog.cloudflare.com/...

L

lambrospetrou•12/17/24, 10:44 AM

Yes, this sessions API and read replication is coming soon-ish. And transactions/writes will continue working fine too.

Z

Zn4rK•12/17/24, 10:54 AM

Transactions in D1 today requires the use of .batch(), right?

I was hoping that the transaction api from DO SQLite would be available within sessions as well (https://github.com/outerbase/starbasedb/issues/12).

This is not something that has been mentioned anywhere (other than this blog post from 2022: https://blog.cloudflare.com/whats-new-with-d1/).

ZZn4rK Transactions in D1 today requires the use of .batch(), right? I was hoping that...

L

lambrospetrou•12/17/24, 10:57 AM

The DO transactions API works really well because you access a local SQLite database, so you get very good performance even with tens/hundreds of queries. Unless you mess it up with async IO yourself.

D1 is a remote database, away from your Worker. This transaction API doesn't work well with that since you would be making multiple remote calls each time. Not saying there isn't an API that can work OK-ish, but that one isn't it.

Z

Zn4rK•12/17/24, 10:59 AM

Thank you! Then I think I'm beginning to understand the limitations and differences between DO SQLite and D1 better

Z

Zn4rK•12/17/24, 10:59 AM

Very exciting stuff!

ZZn4rK Thank you! Then I think I'm beginning to understand the limitations and differen...

L

lambrospetrou•12/17/24, 11:00 AM

Local vs remote is the key difference. Everything else derives from that.

You choose D1 for the usability layer (UI, insights for slow queries and metrics, wrangler commands, import/export), and you choose DO SQLite if you really want the local access + dynamic bindings benefits and are OK building the usability layer yourself (or using a library that provides it for you).

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

Similar Threads

Similar Threads

Similar Threads