Still coming :soontm:

CCyb3r-Jak3 Still coming :soontm:

Z

Zack•7/30/24, 10:15 PM

Alright, look forward to it arriving

Z

Zack•7/30/24, 10:15 PM

Sadly kind of useless to me currently until that arrives

O

Old Man Maker•7/31/24, 5:11 AM

Hi all - is INSERT OR IGNORE INTO supported on D1?

S

Super Morris•7/31/24, 7:01 AM

@cloudflare please add billing alarms for d1 with highest priority! that can ruin people

Hhannes just to be clear: there are already read replicas, 4 (I think) per D1 instance i...

C

Chaika•7/31/24, 12:45 PM

There's no read replicas at the moment. Your D1 Database is a singular Durable Object/single thread running in a single location.
Now DO Storage is usually replicated to a few nearby locations part of the region, but that's just for recovery/backup, it's not running in more then one location

K

KWAC•7/31/24, 3:28 PM

I'm using nuxt + drizzle orm + cloudflare D1.

I can insert, delete, update data from drizzle studio (binded with D1), but I defined api and make api calls from by webapp page to perform those actions, it returns status 200 (OK) but nothing changes in database

J

Jonathan Silva•8/2/24, 4:08 PM

I'm trying to create the database but it gives me the error "No route for that URI"

{
    "name": "my-database"
}

{
    "name": "my-database"
}

{
    "name": "my-database"
}

{
    "name": "my-database"
}

Original message was deleted

J

Jonathan Silva•8/2/24, 4:39 PM

First time using Postman and I thought I was right when pasting the url hahaha

J

jose luis•8/2/24, 7:30 PM

After an execution of

DELETE

DELETE

DELETE

DELETE statement from a sql file, the metric returned in the terminal (the same number is in the web dashboard) for rows read was astronomically bigger than it should be:

npx wrangler d1 execute prods --file $rm_rows_sql --remote
#returns
rows read: 70344030 (web dash display 70.72M)

npx wrangler d1 execute prods --file $rm_rows_sql --remote
#returns
rows read: 70344030 (web dash display 70.72M)

npx wrangler d1 execute prods --file $rm_rows_sql --remote
#returns
rows read: 70344030 (web dash display 70.72M)

npx wrangler d1 execute prods --file $rm_rows_sql --remote
#returns
rows read: 70344030 (web dash display 70.72M)

prods

prods

prods

prods db has close to 12K rows (yes 12_000, not 12M).
the executed slq file is something like this:

DELETE FROM products WHERE tid IN (5, 22, 34, 38, 41, 50, 57, 64, 90, 98,..)

DELETE FROM products WHERE tid IN (5, 22, 34, 38, 41, 50, 57, 64, 90, 98,..)

DELETE FROM products WHERE tid IN (5, 22, 34, 38, 41, 50, 57, 64, 90, 98,..)

DELETE FROM products WHERE tid IN (5, 22, 34, 38, 41, 50, 57, 64, 90, 98,..)

where the list of values is about 1k integers and

tid

tid

tid

tid is indexed. The statement was correctly applied and it took only a couple of seconds.
I'm on the free plan and can still execute queries despite exceeding the daily row read limit by more than 10 times,
according to this very faulty metrics. I want to upgrade to workers paid, but with things like this is very hard to do it.
Just for reference, in my local db with

.scanstats on

.scanstats on

.scanstats on

.scanstats on:

sqlite> .read del_not_shared.sql
QUERY PLAN (cycles=113272102 [100%])
`--SEARCH products USING INTEGER PRIMARY KEY (rowid=?)     (cycles=26065003 [23%] loops=1 rows=1272)

sqlite> .read del_not_shared.sql
QUERY PLAN (cycles=113272102 [100%])
`--SEARCH products USING INTEGER PRIMARY KEY (rowid=?)     (cycles=26065003 [23%] loops=1 rows=1272)

sqlite> .read del_not_shared.sql
QUERY PLAN (cycles=113272102 [100%])
`--SEARCH products USING INTEGER PRIMARY KEY (rowid=?)     (cycles=26065003 [23%] loops=1 rows=1272)

sqlite> .read del_not_shared.sql
QUERY PLAN (cycles=113272102 [100%])
`--SEARCH products USING INTEGER PRIMARY KEY (rowid=?)     (cycles=26065003 [23%] loops=1 rows=1272)

V

Vikash•8/3/24, 1:19 PM

Can anyone suggest why D1 is not using this index? and scanning the full table instead

Deleted all indexes
Re-created index multiple times
No other index on this table

VVikash Can anyone suggest why D1 is not using this index? and scanning the full table i...

V

Vikash•8/3/24, 4:56 PM

Please disregard, this was due to the date time function in query.

K

Kavatch•8/3/24, 9:19 PM

Sorry if I sound a bit salty, but I need to vent a bit: The Alpha Migration process is the worst.
I love workers and the D1 Database and was a vivid supported from the day it came out. But this broke me, the steps for migrating result in me having to do tons of manual work, having a period of time where the data will be out of sync and having to recompile my worker just to update the now new ID in there. Also the process prevents me from having the same name for the database so there are more inconsistencies.
When I then started the migration I got errors while importing the data, I then had to create the new table by hand and copy all the sql statements by hand to run them all manually so the new table has all the data.
Why isn't this a simple one click migration?

In case anyone is interested the error I got was

near "��P": syntax error at offset 0: SQLITE_ERROR [code: 7500]

near "��P": syntax error at offset 0: SQLITE_ERROR [code: 7500]

near "��P": syntax error at offset 0: SQLITE_ERROR [code: 7500]

near "��P": syntax error at offset 0: SQLITE_ERROR [code: 7500], and yes I followed the steps in the guide and yes I tried to remove the line and checked for invisible chars

A

ad-z•8/4/24, 4:04 AM

Hi everyone,
I'm new to D1 and want to understand how it works and whether it suits my needs. and have some questions about it, its happy to discuss it with everyone

A

ad-z•8/4/24, 4:04 AM

What consistency model does D1 use?
According to the blog, snapshot isolation is the default, but what about the issues mentioned in the blog regarding read replicas?
Is the timestamp solution already implemented or is there another method in use?

A

ad-z•8/4/24, 4:05 AM

What's the relationship between D1 and Durable Objects?
Is the D1 database file stored in a Durable Object or vice versa? The blog states:
"Durable Objects guarantee global uniqueness, so once we depend on Durable Objects, routing HTTP requests is easy: just send them to the D1 Durable Object."

does anyone know more about the routing process? It seems similar to a DNS or load-balancing process, so how exactly do we locate where the Durable Object is?

A

ad-z•8/4/24, 4:05 AM

About the connection pool
I am very curious about this. In a serverless environment, is a connection pool necessary? Or can we maintain connections between (user workers running workerd) and (D1 workers running workerd)?

My guess is that SQLite is wrapped by workerd, providing APIs (bindings) to upper-level workers, and that D1 runs as some kind of worker to provide RESTful APIs or handle other tasks.

A

ad-z•8/4/24, 4:06 AM

I'm happy to discuss this with friends here. Thanks!

Aad-z 1. What consistency model does D1 use? According to the blog, snapshot isolation...

C

Chaika•8/4/24, 4:18 AM

Read Replicas are still a Work in Progress/not released yet. As it is right now, your D1 Database is just a singular Durable Object running in a single location

Is the D1 database file stored in a Durable Object or vice versa? The blog states:
"Durable Objects guarantee global uniqueness, so once we depend on Durable Objects, routing HTTP requests is easy: just send them to the D1 Durable Object."
does anyone know more about the routing process? It seems similar to a DNS or load-balancing process, so how exactly do we locate where the Durable Object is?

Durable Objects are a building block. D1 runs on Durable Objects.
When you create your D1 Db it gets assigned a region. You can use a hint to pick the region. You can use wrangler's d1 info command to see the db region

 running_in_region │ ENAM

running_in_region │ ENAM

 running_in_region │ ENAM

running_in_region │ ENAM . A specific machine in a specific colo/data center runs your DB as a Durable Object, which is just a V8 Isolate/single thread. There's a community site which identifies all of the colos that are used for a specific region: https://where.durableobjects.live/region/enam. Your D1 Db will never switch regions, but may switch colo/machine (machine issues, migration, etc).

I am very curious about this. In a serverless environment, is a connection pool necessary? Or can we maintain connections between (user workers running workerd) and (D1 workers running workerd)?

My guess is that SQLite is wrapped by workerd, providing APIs (bindings) to upper-level workers, and that D1 runs as some kind of worker to provide RESTful APIs or handle other tasks.

Connection pool not necessary and with Workers not really doable either, with how often they are created/destroyed. From what they've said before in here, just a request from the user worker -> d1 worker -> d1 durable object is my understanding. Workerd is open source, you can see the implementation (to a degree, can see sqlite is accessible internally for them on d1 storage's class -> sql). There was talk about giving Durable Objects access to an Sqlite db a while ago.

A

ad-z•8/4/24, 4:37 AM

thanks this helps a lot.

H

Hurby•8/4/24, 12:11 PM

does d1 support transactions? I am using drizzle orm on cf workers.

HHurby does d1 support transactions? I am using drizzle orm on cf workers.

H

Hello, I’m Allie!•8/4/24, 2:52 PM

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.

Similar Threads

Similar Threads

Similar Threads