114ms is nice. Getting around 250ms for simple SELECT queries on an almost empty DB from here in the

S

schwartzmj•11/26/23, 10:00 PM

Anyone know if it's possible to run

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply during the Cloudflare Pages build step? And if so how to specify the database name at the end of the command dynamically based on a

.env

.env

or some binding

SSuchACharles Running Nextjs 14 edge pages to display data from D1, it works, however, after a...

M

Matt Silverlock•11/27/23, 12:03 AM

wrangler dev

wrangler dev

is local by default, so you aren’t seeing the local development changes reflected in your “global” D1 database

Jjohnswarbrick 114ms is nice. Getting around 250ms for simple SELECT queries on an almost empty...

R

Rob M.•11/27/23, 12:06 AM

250ms from the API or worker?

K

kian•11/27/23, 12:18 AM

What is your Worker's code?

K

kian•11/27/23, 12:23 AM

Yep!

K

kian•11/27/23, 12:25 AM

Have you re-generated the Drizzle schema since adding these new columns?

K

kian•11/27/23, 12:25 AM

Drizzle doesn't use

SELECT *

SELECT *

SELECT *

SELECT *, it lists all columns from your schema, so it may not be selecting new columns if it hasn't been generated again.

K

kian•11/27/23, 12:40 AM

No problem, glad it's sorted.

S

Santosh•11/27/23, 4:40 AM

can someone provide an example on this topic: How do I insert and then append a json object (not a scalar,) but a proper json object using json_insert at the end of an existing json array in D1? My objective is to store data like this in one text column: history: [{object1 with key and values}, {object2 with keys and values}]

E

Elodere•11/27/23, 10:12 AM

Does D1 support UUIDv7 or should I go with ULID for now?

EElodere Does D1 support UUIDv7 or should I go with ULID for now?

R

Rob M.•11/27/23, 10:29 AM

Why? UUIDv7 is still in a WIP state itself. Is it a game changer?

E

Elodere•11/27/23, 10:33 AM

UUIDv7 from what I've glanced of the spec is supposed to be a lot better than v6 (generally recommended to use v7 whenever possible basically)

RRob M.Why? UUIDv7 is still in a WIP state itself. Is it a game changer?

E

Elodere•11/27/23, 10:33 AM

I don't know enough to claim that it's a game changer, but it's definitely a significant improvement over previous versions

E

Elodere•11/27/23, 10:34 AM

hence why some people are looking at UUIDv7 as a possible replacement for using ULID

EElodere UUIDv7 from what I've glanced of the spec is supposed to be a lot better than v6...

E

Elodere•11/27/23, 10:44 AM

Also something I've realized is that the dashboard only seems to allow for string, int, blob, and real datatypes

But because D1 runs on SQL, I assume it should be fine to use datatypes like UUID right?

E

Elodere•11/27/23, 10:46 AM

Thank you I didn't realize it was SQLite

E

Elodere•11/27/23, 10:47 AM

Yeah then I was right in just using string as the type

EElodere I don't know enough to claim that it's a game changer, but it's definitely a sig...

R

Rob M.•11/27/23, 10:48 AM

D1 is a game changer dogekek

E

Elodere•11/27/23, 10:49 AM

Yeah the only thing I'm worried over is that because cloudflare pages is an edge based solution, that I'll have to think of distributed data from the get go

Then again, I did see it mentioned somewhere that D1 is transaction based, so race conditions will thankfully not happen

S

Stone•11/27/23, 10:49 AM

Does someone know if I use the FTS5 (full text search) extension in D1?

SStone Does someone know if I use the FTS5 (full text search) extension in D1?

M

Max (@rozenmd)•11/27/23, 11:31 AM

FTS5 is supported, yes

EElodere Yeah the only thing I'm worried over is that because cloudflare pages is an edge...

M

Max (@rozenmd)•11/27/23, 11:31 AM

Yep, we run every query in a transaction

S

Stone•11/27/23, 11:35 AM

@rozenmd That is beautiful! Goodbye 3rd party search engines, for small/medium sized data

R

Rob M.•11/27/23, 12:14 PM

I need to return a response from the worker asap and also run a write query to D1. Is it possible to not wait for the API call or maybe do it after the "return new Response()"? Using Queues is an option but not a desirable one.

RRob M.I need to return a response from the worker asap and also run a write query to D...

S

Sam•11/27/23, 12:19 PM

https://developers.cloudflare.com/workers/runtime-apis/handlers/fetch/#contextwaituntil

RRob M.250ms from the API or worker?

J

johnswarbrickOP•11/27/23, 1:29 PM

250ms is the time taken for the D1 database to respond with results back to my worker. Sometimes dips to 210ms, but it's quite consistent. Really hoping having read-replicas speeds this up.

Jjohnswarbrick 250ms is the time taken for the D1 database to respond with results back to my w...

M

Max (@rozenmd)•11/27/23, 1:39 PM

Are you using indexes?

M

Max (@rozenmd)•11/27/23, 1:41 PM

because fetching a specific record in the northwind db takes 0.2ms: https://northwind.d1sql.com/dash

J

johnswarbrickOP•11/27/23, 1:50 PM

Yes, I think it's networking latency.

The Cloudflare Worker is being spun up in the UK, because that's the closest CF endpoint.

But then the SQL query is being sent to wherever the D1 core is currently.

So even a trivial 1 row select from a 1 row table takes 200 - 250ms to return.

I've not tested from APAC yet, I guess it will end up 500-750ms from there.

I'm really hoping read-replicas fixes that problem, although I guess writes will still be slow.

Jjohnswarbrick Yes, I think it's networking latency. The Cloudflare Worker is being spun up in...

M

Matt Silverlock•11/27/23, 1:55 PM

D1 will locate the leader close to where you created it, or if you provide a hint, close to where you request: https://developers.cloudflare.com/d1/learning/data-location/

Jjohnswarbrick Yes, I think it's networking latency. The Cloudflare Worker is being spun up in...

M

Matt Silverlock•11/27/23, 1:56 PM

You can see where your leader is located with

wrangler d1 info DB_NAME

wrangler d1 info DB_NAME

wrangler d1 info DB_NAME

wrangler d1 info DB_NAME - e.g.

➜  npx wrangler@latest d1 info db-enam
┌───────────────────┬──────────────────────────────────────┐
│ ENAM              │ c89db32e-83f4-4e62-8cd7-7c8f97659029 │
├───────────────────┼──────────────────────────────────────┤
│ name              │ db-enam                              │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-06-12T16:52:03.071Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 8                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 1.83 GB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 1,437                                │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 1,551,235                            │
└───────────────────┴──────────────────────────────────────┘

➜  npx wrangler@latest d1 info db-enam
┌───────────────────┬──────────────────────────────────────┐
│ ENAM              │ c89db32e-83f4-4e62-8cd7-7c8f97659029 │
├───────────────────┼──────────────────────────────────────┤
│ name              │ db-enam                              │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-06-12T16:52:03.071Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 8                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 1.83 GB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 1,437                                │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 1,551,235                            │
└───────────────────┴──────────────────────────────────────┘

➜  npx wrangler@latest d1 info db-enam
┌───────────────────┬──────────────────────────────────────┐
│ ENAM              │ c89db32e-83f4-4e62-8cd7-7c8f97659029 │
├───────────────────┼──────────────────────────────────────┤
│ name              │ db-enam                              │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-06-12T16:52:03.071Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 8                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 1.83 GB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 1,437                                │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 1,551,235                            │
└───────────────────┴──────────────────────────────────────┘

➜  npx wrangler@latest d1 info db-enam
┌───────────────────┬──────────────────────────────────────┐
│ ENAM              │ c89db32e-83f4-4e62-8cd7-7c8f97659029 │
├───────────────────┼──────────────────────────────────────┤
│ name              │ db-enam                              │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-06-12T16:52:03.071Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 8                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 1.83 GB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 1,437                                │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 1,551,235                            │
└───────────────────┴──────────────────────────────────────┘

J

johnswarbrickOP•11/27/23, 2:01 PM

Thanks Matt, I'll take a look

SSantosh can someone provide an example on this topic: How do I insert and then append a ...

S

Santosh•11/27/23, 2:46 PM

Can someone give me an example on how to insert and append Json using json_insert? I see the example from the documentation with scalar but not an object. And i receive the following error :

Error: D1_TYPE_ERROR: Type 'object' not supported for value '[object Object]'
    at cloudflare-internal:d1-api:146:19

Error: D1_TYPE_ERROR: Type 'object' not supported for value '[object Object]'
    at cloudflare-internal:d1-api:146:19

Error: D1_TYPE_ERROR: Type 'object' not supported for value '[object Object]'
    at cloudflare-internal:d1-api:146:19

Error: D1_TYPE_ERROR: Type 'object' not supported for value '[object Object]'
    at cloudflare-internal:d1-api:146:19

SSantosh Can someone give me an example on how to insert and append Json using json_inse...

M

Matt Silverlock•11/27/23, 4:16 PM

Can you show what you have tried first?

S

Santosh•11/27/23, 5:49 PM

      const h = {id: 1, attr1: "abcdef} const t = JSON.stringify(h)
      //   console.log("JSON being inserted =>>>>", h)
        const result = await env.SMS_DEV.prepare(
          "UPDATE TestTable SET history = json_insert(history, '$.history[#]', '?1') WHERE id = '?2' ",
        )
          .bind(h, status.id)
          .run()

      const h = {id: 1, attr1: "abcdef} const t = JSON.stringify(h)
      //   console.log("JSON being inserted =>>>>", h)
        const result = await env.SMS_DEV.prepare(
          "UPDATE TestTable SET history = json_insert(history, '$.history[#]', '?1') WHERE id = '?2' ",
        )
          .bind(h, status.id)
          .run()

      const h = {id: 1, attr1: "abcdef} const t = JSON.stringify(h)
      //   console.log("JSON being inserted =>>>>", h)
        const result = await env.SMS_DEV.prepare(
          "UPDATE TestTable SET history = json_insert(history, '$.history[#]', '?1') WHERE id = '?2' ",
        )
          .bind(h, status.id)
          .run()

      const h = {id: 1, attr1: "abcdef} const t = JSON.stringify(h)
      //   console.log("JSON being inserted =>>>>", h)
        const result = await env.SMS_DEV.prepare(
          "UPDATE TestTable SET history = json_insert(history, '$.history[#]', '?1') WHERE id = '?2' ",
        )
          .bind(h, status.id)
          .run()

S

Santosh•11/27/23, 7:21 PM

Can someone point to Full Test search capabilities of D1? I couldn't locate any articles or discussions related to this

W

WoozyAnuki•11/27/23, 7:56 PM

what's the format for running SQL queries on D1 on PHP deployed as a project? I'm an absolute noob with web dev but using cloudflare to host a student project as well as my portfolio but for the project I'm requried to use SQL and saw D1 as an option so was wondering if it was possible to use d1 as I would use a normal SQL database (as in like XAMPP/MAMP)

WWoozyAnuki what's the format for running SQL queries on D1 on PHP deployed as a project? I'...

H

Hello, I’m Allie!•11/27/23, 8:04 PM

You can’t deploy PHP on Cloudflare

H

Hello, I’m Allie!•11/27/23, 8:04 PM

Or well, I think there is an adapter for it somewhere, but not sure it supports anything relatively modern

H

Hello, I’m Allie!•11/27/23, 8:05 PM

Or the PHP runtime someone wrote.

W

WoozyAnuki•11/27/23, 8:07 PM

Woof, thanks lol. Will still use it for my portfolio and just use R2 most likely since there's no extra mess lol. Tysm :)

114ms is nice. Getting around 250ms for simple SELECT queries on an almost empty DB from here in the

Similar Threads

Similar Threads

Similar Threads