Queries/Batches are automatically run in a transaction, but you cannot initiate them manually

HHello, I’m Allie!Queries/Batches are automatically run in a transaction, but you cannot initiate ...

H

Hurby•8/4/24, 2:54 PM

ah... , I have to write bunch of codes again then

H

Hurby•8/4/24, 2:54 PM

ty

B

Ben•8/5/24, 2:49 AM

What is the recommendation for scaling D1?

I see it has a storage limit of 10gb... and I don't see a place that says we can increase it... but what if my data goes beyond that?

BBen What is the recommendation for scaling D1? I see it has a storage limit of 10gb...

F

Fishy!•8/5/24, 6:58 AM

multiple databases, likely some kind of partitioning

F

Fishy!•8/5/24, 6:59 AM

10 GB is pretty small for headroom, I would be more comfortable if it was 50 GB

V

Vikash•8/5/24, 8:53 AM

Can anyone suggest why the

Rows read

Rows read

Rows read

Rows read is so big after using an index.

That

15,932

15,932

15,932

15,932 is the total number or rows in the table -

Select * from Jobs where status = 'scheduled' and scheduled_at <= datetime('now')

Select * from Jobs where status = 'scheduled' and scheduled_at <= datetime('now')

Select * from Jobs where status = 'scheduled' and scheduled_at <= datetime('now')

Select * from Jobs where status = 'scheduled' and scheduled_at <= datetime('now')

V

Vikash•8/5/24, 8:54 AM

This is the index definition

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (scheduled_at, status);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (scheduled_at, status);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (scheduled_at, status);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (scheduled_at, status);

VVikash This is the index definition ``` CREATE INDEX if not exists scheduled_jobs_sea...

S

Sam•8/5/24, 8:56 AM

I'm not really an index guy, but could you try switching to

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (status, 
scheduled_at);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (status, 
scheduled_at);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (status, 
scheduled_at);

CREATE INDEX if not exists scheduled_jobs_search ON Jobs (status, 
scheduled_at);

SSam I'm not really an index guy, but could you try switching to ```CREATE INDEX if n...

V

Vikash•8/5/24, 9:01 AM

Awesome, thanks. So the order of field also matter

J

JoeDoe•8/5/24, 7:19 PM

how to do a upsert?

J

JaveRynx•8/6/24, 4:57 AM

Hey, any idea how to integrate D1 in existing next Js project, I am facing some problems.
Anyone has an example repo?

V

Vikash•8/6/24, 6:41 AM

How to improve the parallel query or performance?

I get 50-100 alert daily in our logging system which says

D1_ERROR: D1 is overloaded. Requests queued for too long

D1_ERROR: D1 is overloaded. Requests queued for too long

D1_ERROR: D1 is overloaded. Requests queued for too long

D1_ERROR: D1 is overloaded. Requests queued for too long

C

cosbgn•8/6/24, 8:42 AM

Does D1 supports sqlite extensions?

Ccosbgn Does D1 supports sqlite extensions?

H

Hard@Work•8/6/24, 8:43 AM

IIRC there are some pre-installed, but you can't configure/add/remove them yourself

C

cosbgn•8/6/24, 8:43 AM

Can I use something like sqlite-vec ?

Ccosbgn Can I use something like [sqlite-vec](https://github.com/asg017/sqlite-vec) ?

H

Hard@Work•8/6/24, 8:44 AM

Looking at it, I don't think so?

H

Hard@Work•8/6/24, 8:45 AM

Don't remember where(if there is a place) you can see what extensions are installed

C

cosbgn•8/6/24, 8:46 AM

I tried googling but I can't find it

Ccosbgn I tried googling but I can't find it

H

Hard@Work•8/6/24, 8:49 AM

I think this is it: https://github.com/cloudflare/workerd/blob/920ff29acca7c91793ada337464aa50f0c70a71f/build/BUILD.sqlite3

GitHub

workerd/build/BUILD.sqlite3 at 920ff29acca7c91793ada337464aa50f0c70...

The JavaScript / Wasm runtime that powers Cloudflare Workers - cloudflare/workerd

C

cosbgn•8/6/24, 8:58 AM

Thanks!

Ccopper Pls give a bit more info about the load in reads and writes/sec, thanks

V

Vikash•8/6/24, 10:42 AM

This is last 24h metrics -

.

.gwapes•8/6/24, 9:59 PM

Hi, I just wanted to ask and clarify whether D1 is globally disturbed. As in, the data is everywhere and with a worker the data will come from a system closest to where the request came from. And what thjs means with that. Sorry if these are simple questions

Cloudflare Docs

Data location · Cloudflare D1 docs

Learn how the location of data stored in D1 is determined, including where the leader is placed and how you optimize that location based on your …

..gwapes Hi, I just wanted to ask and clarify whether D1 is globally disturbed. As in, th...

I

Isaac McFadyen•8/6/24, 10:05 PM

D1 is not currently globally distributed. D1 is in one location (the location closest to where you created it, from the available regions that host D1) but there's no read replication currently.

.

.gwapes•8/6/24, 10:05 PM

Oh right, I thought it was. My bad. Thanks for the info appreciate it

Ccopper Very nice, I just wonder now how we can get per-seconds-numbers from that chart ...

V

Vikash•8/7/24, 2:46 AM

No, where to find write limit?

A

ad-z•8/7/24, 3:13 AM

Hi everyone, after some research, I have summarized the process as follows. I'm unsure if it's accurate, so I'm open to discussion.

[user]
│
request
│
▼
+--------------------------------------------+
| V8 Engine |
| +--------------------+ +-------------+ |
| | Workers(isloate) | | DB Bindings | |
| +--------------------+ +-------------+ |
+-------------------------------------------+
│
RESTful API request to a specific location
│
---------------------------
│
▼
+-----------------------------------------------------+
| V8 Engine |
| +------------------------+ +--------------------+ |
| | D1 Durable Object | | wrapped SQLite | |
| | workers(isloate) | | with VFS etc | |
| +------------------------+ +--------------------+ |
+-----------------------------------------------------+
│
▼
[private storage]

J

jdruwe•8/7/24, 9:06 AM

Quick question, I am building a small system sending out coupon codes to customers. I want these coupon code to be stored somewhere and I was thinking about using D1. Now I wonder, is there a way for me to lock a coupon code while being READ by user A preventing user B from receiving the same coupon code? I know batching is an option but I want to be sure this will work as I know there are some plan to introduce(?) read replica's.

S

Shannor•8/7/24, 1:45 PM

Question with using D1 in Intellij Datagrip products. I'm using the library recommended for the driver but I noticed this error posted here https://github.com/isaac-mcfadyen/d1-jdbc-driver/issues/6

Wanted to know if anyone has looked into it? I'm assuming it's low priority. I'm doing the work around but what just curious. Thanks in advance!

GitHub

Updating records inline is not working · Issue #6 · isaac-mcfadyen/...

First, thanks a lot for this project, whatever is available is already amazing to have and is an extreme productivity booster when working with D1

. Maybe enable GitHub sponsorships? For the actu...

SShannor Question with using D1 in Intellij Datagrip products. I'm using the library reco...

I

Isaac McFadyen•8/7/24, 3:12 PM

Hi! That's actually my lib, and somewhat unmaintained at the moment

I

Isaac McFadyen•8/7/24, 3:12 PM

There's a v2 branch/PR that I've done a bit of work on but haven't yet gotten the chance to merge, and there are still a few outstanding issues in there.

S

Shannor•8/7/24, 6:46 PM

Oh nice! Any way I could help out on it? I use it at work so it's wonderful for me! lol I don't have much experience making Intellij drivers though

I

iwgu•8/8/24, 10:56 AM

Hey, is there any roadmap for increasing 10gb limit?

Ccopper I mean what you could do is to just send the load yourself, e.g. with ab (Apach...

V

Vikash•8/8/24, 11:37 AM

I found the slowest query/time consuming is this one -

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Because

Accounts

Accounts

Accounts

Accounts is the main table with foreign key in all other tables, so when a customer delete an account it freeze everything.
As the query also delete from 20 other tables as well with foreign key relationship.

Any suggestion?

F

Farhian•8/8/24, 12:29 PM

Hi guys, I am trying to restore (using time travel) one of our databases and I keep getting this error: {"D1_RESET_DO":true} [code: 7500]. i have tried using both the CLI and the web portal - incl. using different timestamps. Any thoughts? Thank you!

VVikash I found the slowest query/time consuming is this one - ``` Delete from Account...

S

Steved•8/8/24, 3:48 PM

Any suggestions?

It may reduce the duration of that query if you can do some pre-emptive cleanup before the final deletion of the Account. My guess is there's one or two tables that have most of the data in.

S

Steved•8/8/24, 3:50 PM

Though it may be trickier if you don't have account_id in some of the child tables, e.g. Accounts -> Users -> Conversations -> Messages with each cascading the deletion.

V

Vikash•8/8/24, 3:54 PM

I think other people also having similar issue with SQL Lite and I need to try with indexing as well. I am not really sure of foreign key are auto-indexed in SQL Lite or not. So, I'd need to try

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

on all tables

https://stackoverflow.com/questions/24259265/sqlite-bad-performance-with-on-delete-cascade

Stack Overflow

SQLite: Bad performance with ON DELETE CASCADE

I am using a SQLite Database in my application for storing measurement data.

It is organized in groups in projects. The tables are interconnected with foreign keys, that use ON DELETE CASCADE to h...

S

Steved•8/8/24, 4:00 PM

Oh my goodness. Yes, if there are missing indexes on FKs that would explain it - I had taken that for granted given the other DBs I use. Another quirk of SQLite to be mindful of.
The max query duration is 30s which is an incredibly long time, which suggests missing indexes, or eye watering amounts of data.
On the positive side, adding the indexes should be straightforward and may even improve your app's performance in other instances.

V

Vikash•8/8/24, 4:04 PM

yes, I was never thought of that coming from SQL being used on all other applications. So, I will build indexes tomorrow and hopefully it will resolve everything.

S

Steved•8/8/24, 4:46 PM

For what it is worth, I just checked one of my SQLite tables with a FK, and it doesn't claim to have an index with

PRAGMA index_list('TableName');

PRAGMA index_list('TableName');

PRAGMA index_list('TableName');

PRAGMA index_list('TableName'); and when I did a simple query on the table with

EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc'

EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc'

EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc'

EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc' it reported use of SCAN (full table scan). So I think that confirms to me that the FK indexes are not created automatically. Thank you for highlighting this, as I'll need to revisit the schema's for my SQLite apps.
(Positives) The indexes will probably also reduce your CF costs too.

M

Mike•8/8/24, 5:29 PM

If anyone is looking for a GUI for D1, we just added support for it to our VSCode extension: https://dbcode.io/docs/supported-databases/d1/d1

Docs - Cloudflare D1

Database Management in Visual Studio Code. Supports PostgreSQL, MySQL, MariaDB, SQL Server, and more. Connect to your database and manage your data with ease.

P

Peter JY•8/9/24, 9:38 AM

Parden me. Is it possible to insert into a table from a xml file, and select * from the table where a function returns true to a xml file?

Ssteida I want to ask whether D1 SQLite will be updated. I need jsonb. Thank you

V

Vy•8/9/24, 6:16 PM

no known timeline at the moment. But we're discussing an update in Q4, plus how we can stay more regular with sqlite updates.

Queries/Batches are automatically run in a transaction, but you cannot initiate them manually

Similar Threads

Similar Threads

Similar Threads