anyone using raw sql? or everyone using an ORM like drizzle? just curious. 🤔

GGagan Suie anyone using raw sql? or everyone using an ORM like drizzle? just curious. 🤔

I

I previously used raw SQL and I moved to Drizzle. I still try to use Drizzle as close to raw SQL as I can (I use the query builder not their

db.query

db.query

db.query

db.query wrapper) but IMO it's very handy to be able to construct SQL with standard JS/TS functions rather than string interpolation.

W

Walshy•1/1/25, 3:44 AM

Drizzle's functions are fine for simple queries. Any more advanced and they're messy and limited

W

Walshy•1/1/25, 3:45 AM

Luckily most queries usually are simple

P

progame•1/1/25, 3:48 AM

does D1 have "varbinary"? also where can i find a list of all the types?

W

Walshy•1/1/25, 3:50 AM

https://www.sqlite.org/datatype3.html

WWalshy Luckily most queries usually are simple

I

Isaac McFadyen•1/1/25, 4:00 AM

Yeah and then you can use the raw API for anything that doesn't fit in the simple query builder pattern.

I

Isaac McFadyen•1/1/25, 4:01 AM

Or just use the driver directly I guess :lul:

GGagan Suie anyone using raw sql? or everyone using an ORM like drizzle? just curious. 🤔

J

James•1/1/25, 6:28 AM

Raw sql for db usage, drizzle kit for migrations.

G

Gagan SuieOP•1/1/25, 6:32 AM

I'm just glad I'm not the only one that prefers raw sql.

K

kamama•1/1/25, 9:44 AM

I’m encountering very slow query issue, any way to check why? Already enabled smart place worker

K

kamama•1/1/25, 9:44 AM

query is simple and datasets are small

G

Gagan SuieOP•1/1/25, 10:19 AM

does CAST work in d1 queries?

Kkamama I’m encountering very slow query issue, any way to check why? Already enabled sm...

A

Astral•1/1/25, 11:44 AM

@kamama I've reported the same issue yesterday. I guess it's how it is. Maybe it's the free plan.
I've dabbled in trying out different cloud DB solutions as suggested by other members here.
But I've haven't found anything I super like.
I'm gonna continue with D1, and it's slow response times - which I will try to mitigate with the CacheAPI.

AAstral @kamama I've reported the same issue yesterday. I guess it's how it is. Maybe it...

K

kamama•1/1/25, 11:51 AM

If it’s because of free to make the performance poor, I can understand that, but at least let us know. Just worry after paid, the performance is still poor

K

kamama•1/1/25, 12:11 PM

Seems it’s better move to Turso until the issue fixed by Cloudflare team

G

Gagan SuieOP•1/1/25, 9:55 PM

is there any way to view indexes in D1 dashboard?

L

Larry•1/2/25, 2:15 AM

I don't think there is any intention to increase the overall 10GB limit

T

tcarson•1/2/25, 3:36 AM

Bummer right

K

kamama•1/2/25, 11:05 AM

1 query take 224ms (foods_aus raw) and another one take 1.1s (nutrients_aus raw), both enable smart place and under same region

K

kamama•1/2/25, 11:05 AM

how come it have 5x diff..

K

kamama•1/2/25, 11:08 AM

both records < 10k rows

Kkamama 1 query take 224ms (foods_aus raw) and another one take 1.1s (nutrients_aus raw)...

M

Max (@rozenmd)•1/2/25, 11:55 AM

compare the query

rows_read

rows_read

rows_read

rows_read vs how many rows you get back (or take a look at

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights and look for query efficiency) - chances are you're missing an index

K

kamama•1/2/25, 11:57 AM

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

i think most of the time consume is on network layer

0

0x3lang•1/2/25, 4:49 PM

anynone can help me? my d1 database stuck: D1 DB is overloaded. Requests queued for too long

0

0x3lang•1/2/25, 4:49 PM

00x3lang anynone can help me? my d1 database stuck: D1 DB is overloaded. Requests queued...

J

James•1/2/25, 5:01 PM

What's your database ID and we can escalate this?

J

James•1/2/25, 5:01 PM

What kind of traffic are you sending to the DB? How many requests?

JJames What kind of traffic are you sending to the DB? How many requests?

0

0x3lang•1/2/25, 5:02 PM

ed9b192b-6631-451c-ba45-efb3baf87f56

JJames What kind of traffic are you sending to the DB? How many requests?

0

0x3lang•1/2/25, 5:02 PM

D1 DB is overloaded. Requests queued for too long.
Suddenly, all inquiries became lengthy

J

James•1/2/25, 5:03 PM

Historically, what kind of traffic are you sending to this? Any estimate on requests per second?

0

0x3lang•1/2/25, 5:04 PM

Less than 100 request queries per second

0

0x3lang•1/2/25, 5:05 PM

for now

J

James•1/2/25, 5:06 PM

Thanks, I've escalated - hopefully the team will get back to you soon

0

0x3lang•1/2/25, 5:07 PM

this is worker error log for now

0

0x3lang•1/2/25, 5:07 PM

My product is now forced to stop running and suffered a lot of losses

0

0x3lang•1/2/25, 5:08 PM

It's 1: 00 a.m. on my side now, can't sleep at all

0

0x3lang•1/2/25, 5:10 PM

It has been running smoothly for more than a month, and the number of visits has increased in recent days, resulting in the above problem.

0

0x3lang•1/2/25, 5:11 PM

:NotLikeThis:

J

James•1/2/25, 5:21 PM

So sorry for the issues. I've escalated to the team and hopefully they will get back to you soon

F

Farhian•1/2/25, 6:13 PM

Hi, does D1 have a 'sleep mode' of some sort? The reason I am asking is because we have noticed that if a database it not accessed during a certain period of time (approx. 24h from our experience), the first request to update the DB after that, takes a long time. Sometimes we are getting a timeout error (even if most of the times, the operation actually succeeded).

F

Farhian•1/2/25, 6:13 PM

After that initial delay, everything is fast as expected again.

E

evefan•1/2/25, 8:21 PM

I'd like to share a local D1 database between by backend worker and Next.js pages app, but I'm having trouble. I'm using the

persist-to

persist-to

persist-to

persist-to flag on my worker and

persist

persist

persist

persist on my setupDevPlatform() configuration to point to the shared directory, but it seems that instead of Next using the same sqlite file that's already there, it just creates a new one in the same directory.

Ideally, I'd like to use the binding in

next dev --turbopack

next dev --turbopack

next dev --turbopack

next dev --turbopack instead of having to build my Next app every time using

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

J

Jikyu•1/3/25, 2:14 AM

Is there a way to check the deployed location of an existing D1 database?

JJikyu Is there a way to check the deployed location of an existing D1 database?

C

Chaika•1/3/25, 2:30 AM

wrangler d1 info <db-name>

C

Chaika•1/3/25, 2:31 AM

ex: > running_in_region │ ENAM

J

Jikyu•1/3/25, 2:31 AM

thank you!

FFarhian Hi, does D1 have a 'sleep mode' of some sort? The reason I am asking is because ...

C

Chaika•1/3/25, 2:35 AM

Yes, D1 Databases go to sleep / they have cold starts. They should be very quick though, depends what you mean by "a long time", and I suppose database size as well

F

Farhian•1/3/25, 2:37 AM

Thank you for the response. Well we do get a timeout exception from the DB when invoked from a worker

I would say after about 5-10 seconds or so.

F

Farhian•1/3/25, 2:38 AM

For that reason (the error response), we consider to operation to have failed, when in fact it did not.

anyone using raw sql? or everyone using an ORM like drizzle? just curious. 🤔

Similar Threads

Similar Threads

Similar Threads