I think andrew is confused because D1 has a different API for it.

B

BenyOP•4/26/24, 10:22 AM

In other sqlite APIs you can re-use the prepared statement with different bindings like this:

const stmt = db.prepare('SELECT * FROM users WHERE name = ?')
const user1 = stmt.bind("John Doe").first()
const user2 = stmt.reset().bind("Donald Duck").first()
const user3 = stmt.reset().bind("Mickey Mouse").first()

const stmt = db.prepare('SELECT * FROM users WHERE name = ?')
const user1 = stmt.bind("John Doe").first()
const user2 = stmt.reset().bind("Donald Duck").first()
const user3 = stmt.reset().bind("Mickey Mouse").first()

const stmt = db.prepare('SELECT * FROM users WHERE name = ?')
const user1 = stmt.bind("John Doe").first()
const user2 = stmt.reset().bind("Donald Duck").first()
const user3 = stmt.reset().bind("Mickey Mouse").first()

const stmt = db.prepare('SELECT * FROM users WHERE name = ?')
const user1 = stmt.bind("John Doe").first()
const user2 = stmt.reset().bind("Donald Duck").first()
const user3 = stmt.reset().bind("Mickey Mouse").first()

Original message was deleted

M

Max (@rozenmd)•4/26/24, 10:37 AM

what specifically is the workload here again, cron trigger every minute running an SQL update?

C

cosbgn•4/26/24, 12:33 PM

14:28:29.976    > db:migrate-remote
14:28:29.976    > npx wrangler d1 migrations apply D1_BINDING --remote
14:28:29.976    
14:28:31.315     ⛅️ wrangler 3.52.0
14:28:31.316    -------------------
14:28:32.030    Failed: Error while executing user command. Exited with error code: 1
14:28:32.040    Failed: build command exited with code: 1
14:28:33.462    Failed: error occurred while running build command

14:28:29.976    > db:migrate-remote
14:28:29.976    > npx wrangler d1 migrations apply D1_BINDING --remote
14:28:29.976    
14:28:31.315     ⛅️ wrangler 3.52.0
14:28:31.316    -------------------
14:28:32.030    Failed: Error while executing user command. Exited with error code: 1
14:28:32.040    Failed: build command exited with code: 1
14:28:33.462    Failed: error occurred while running build command

14:28:29.976    > db:migrate-remote
14:28:29.976    > npx wrangler d1 migrations apply D1_BINDING --remote
14:28:29.976    
14:28:31.315     ⛅️ wrangler 3.52.0
14:28:31.316    -------------------
14:28:32.030    Failed: Error while executing user command. Exited with error code: 1
14:28:32.040    Failed: build command exited with code: 1
14:28:33.462    Failed: error occurred while running build command

14:28:29.976    > db:migrate-remote
14:28:29.976    > npx wrangler d1 migrations apply D1_BINDING --remote
14:28:29.976    
14:28:31.315     ⛅️ wrangler 3.52.0
14:28:31.316    -------------------
14:28:32.030    Failed: Error while executing user command. Exited with error code: 1
14:28:32.040    Failed: build command exited with code: 1
14:28:33.462    Failed: error occurred while running build command

Anyone know why this might fail?

I have in

package.json scripts

package.json scripts

package.json scripts

package.json scripts:

"postbuild": "npm run db:migrate-remote",
"db:migrate-remote": "npx wrangler d1 migrations apply D1_BINDING --remote",

"postbuild": "npm run db:migrate-remote",
"db:migrate-remote": "npx wrangler d1 migrations apply D1_BINDING --remote",

"postbuild": "npm run db:migrate-remote",
"db:migrate-remote": "npx wrangler d1 migrations apply D1_BINDING --remote",

"postbuild": "npm run db:migrate-remote",
"db:migrate-remote": "npx wrangler d1 migrations apply D1_BINDING --remote",

I have in my env:

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN where I added a

d1

d1

read/write token

C

cosbgn•4/26/24, 12:35 PM

Also does it make sense my setup? Run d1 locally and generate/migrate all the migration as I developed, then when pushed after the build is successful, it migrates the production db

Ccosbgn ``` 14:28:29.976 > db:migrate-remote 14:28:29.976 > npx wrangler d1 migra...

M

Max (@rozenmd)•4/26/24, 2:14 PM

i think it should be the name of your database instead of

D1_BINDING

D1_BINDING

D1_BINDING

D1_BINDING

C

cosbgn•4/26/24, 3:11 PM

Locally it works (using the binding name) - I've now tested with the DB name and it works as well locally, but I get the same error while deploying.

17:09:38.403    > db:migrate-remote
17:09:38.403    > npx wrangler d1 migrations apply db-prod --remote
17:09:38.404    
17:09:39.845     ⛅️ wrangler 3.52.0
17:09:39.845    -------------------
17:09:41.463    Failed: Error while executing user command. Exited with error code: 1
17:09:41.473    Failed: build command exited with code: 1
17:09:42.674    Failed: error occurred while running build command

17:09:38.403    > db:migrate-remote
17:09:38.403    > npx wrangler d1 migrations apply db-prod --remote
17:09:38.404    
17:09:39.845     ⛅️ wrangler 3.52.0
17:09:39.845    -------------------
17:09:41.463    Failed: Error while executing user command. Exited with error code: 1
17:09:41.473    Failed: build command exited with code: 1
17:09:42.674    Failed: error occurred while running build command

17:09:38.403    > db:migrate-remote
17:09:38.403    > npx wrangler d1 migrations apply db-prod --remote
17:09:38.404    
17:09:39.845     ⛅️ wrangler 3.52.0
17:09:39.845    -------------------
17:09:41.463    Failed: Error while executing user command. Exited with error code: 1
17:09:41.473    Failed: build command exited with code: 1
17:09:42.674    Failed: error occurred while running build command

17:09:38.403    > db:migrate-remote
17:09:38.403    > npx wrangler d1 migrations apply db-prod --remote
17:09:38.404    
17:09:39.845     ⛅️ wrangler 3.52.0
17:09:39.845    -------------------
17:09:41.463    Failed: Error while executing user command. Exited with error code: 1
17:09:41.473    Failed: build command exited with code: 1
17:09:42.674    Failed: error occurred while running build command

Ccosbgn Locally it works (using the binding name) - I've now tested with the DB name and...

B

BenyOP•4/26/24, 3:15 PM

You could try enabling more logs?
Set environment variable

WRANGLER_LOG=debug

WRANGLER_LOG=debug

WRANGLER_LOG=debug

WRANGLER_LOG=debug and hopefully you'll see what's going wrong

C

cosbgn•4/26/24, 3:23 PM

thanks @Beny this helped, I get:

17:21:21.937    🪵  Writing logs to "/opt/buildhome/.config/.wrangler/logs/wrangler-2024-04-26_15-21-21_813.log"
17:21:21.938    Failed to load .env file ".env": Error: ENOENT: no such file or directory, open '.env'
17:21:21.938        at Object.openSync (node:fs:603:3)
17:21:21.938        at Object.readFileSync

17:21:21.937    🪵  Writing logs to "/opt/buildhome/.config/.wrangler/logs/wrangler-2024-04-26_15-21-21_813.log"
17:21:21.938    Failed to load .env file ".env": Error: ENOENT: no such file or directory, open '.env'
17:21:21.938        at Object.openSync (node:fs:603:3)
17:21:21.938        at Object.readFileSync

17:21:21.937    🪵  Writing logs to "/opt/buildhome/.config/.wrangler/logs/wrangler-2024-04-26_15-21-21_813.log"
17:21:21.938    Failed to load .env file ".env": Error: ENOENT: no such file or directory, open '.env'
17:21:21.938        at Object.openSync (node:fs:603:3)
17:21:21.938        at Object.readFileSync

17:21:21.937    🪵  Writing logs to "/opt/buildhome/.config/.wrangler/logs/wrangler-2024-04-26_15-21-21_813.log"
17:21:21.938    Failed to load .env file ".env": Error: ENOENT: no such file or directory, open '.env'
17:21:21.938        at Object.openSync (node:fs:603:3)
17:21:21.938        at Object.readFileSync

So it seems it's probably caused by drizzle trying to load .env file - I now now that to debug

C

cosbgn•4/26/24, 3:23 PM

Great to know about

WRANGLER_LOG=debug

WRANGLER_LOG=debug

WRANGLER_LOG=debug

WRANGLER_LOG=debug the other error was just too vague

C

cosbgn•4/26/24, 3:28 PM

mmm it's actually nothing to do with drizzle, as it's all handled by d1

W

wong26•4/26/24, 6:27 PM

Hi, guys. I saw there is 10GB/database limit for D1. I would like to know what if the D1 database grow to more than 10GB ?

Wwong26 Hi, guys. I saw there is 10GB/database limit for D1. I would like to know what i...

M

Max (@rozenmd)•4/26/24, 7:37 PM

unlikely for now

M

Max (@rozenmd)•4/26/24, 7:37 PM

easier to offer millions of dbs than one big one

MMax (@rozenmd)unlikely for now

W

wong26•4/26/24, 7:40 PM

what if my business's data grow and require more than 10GB size of database? Does that mean I need to export the D1 and find another alternative to host it?

Wwong26 what if my business's data grow and require more than 10GB size of database? Doe...

I

IEatBeans•4/26/24, 8:16 PM

Looks like you can request to increase limits here:

https://forms.gle/ukpeZVLWLnKeixDu7

Google Docs

Cloudflare Developer Platform: Limit Increase Request Form

Want to increase the limits of your current plan? Let us know what your requirements are here.

I

IEatBeans•4/26/24, 8:16 PM

That form is found at the bottom of this page btw: https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

IIEatBeans Looks like you can request to increase limits here: https://forms.gle/ukpeZVLWL...

I

Isaac McFadyen•4/26/24, 8:36 PM

Just an FYI that @Max (@rozenmd) is an engineer on the D1 team and if he says it's not likely to be increased in the future then filling out that form won't get you much considering it eventually ends up with his team anyway

Wwong26 what if my business's data grow and require more than 10GB size of database? Doe...

C

Chaika•4/26/24, 8:37 PM

D1 wants you to spread your load and size throughput multiple D1 DBs

C

Chaika•4/26/24, 8:37 PM

currently a single D1 DB is a single single-threaded Durable Object as well, so limited in the querys per second aspect as well

C

Chaika•4/26/24, 8:38 PM

Although it is worth noting D1 currently lacks the dynamic bindings to make such an approach workable using Bindings, would have to use the API (Which goes back to core from all locations/is slow), or bind each separate DB manually

A

andrewitsover•4/27/24, 8:27 AM

I am basically going to assume then that the prepared statements are only for the parameter binding and don't have any performance usage on d1.

A

andrewitsover•4/27/24, 8:27 AM

(outside of multiple inserts or something)

A

andrewitsover•4/27/24, 9:14 AM

because you aren't supposed to store the statement object outside of the request handler..... unless d1 does some pattern matching that is different to sqlite in general

A

andrewitsover•4/27/24, 9:14 AM

but isn't specified anywhere

A

andrewitsover•4/27/24, 9:14 AM

and doesnt make sense... because you have to call .prepare each request

BBeny I think andrew is confused because D1 has a different API for it.

A

andrewitsover•4/27/24, 9:20 AM

errr... you can do the same with d1, but it must only be per request, not between requests. The documentation is here: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#reuse-prepared-statements nobody seems to know anything about this topic, including the employee here

Cloudflare Docs

Query D1 · Cloudflare D1 docs

D1 is compatible with most SQLite’s SQL convention since it leverages SQLite’s query engine. D1 client API allows you to interact with a D1 database …

N

Nick•4/27/24, 10:07 AM

I’m looking to build something with D1 but I’ll almost certainly exceed the 10GB max database size. Is that a hard limit, or can it be increased? If it’s a hard limit, what’s the best solution? Consistent hashing based on key and have N databases?

N

Nick•4/27/24, 10:08 AM

I would have a database per customer but creating a D1 on the fly seems awful as does applying migrations to all of them

NNick I would have a database per customer but creating a D1 on the fly seems awful as...

B

bret_pat•4/27/24, 11:16 AM

From my understanding, D1 has a very narrow scope where it's beneificial and the drawbacks that you mentioned are not minor.

N

Nick•4/27/24, 11:41 AM

Hmm, my use case is I need to check metadata on every request, and there will be 10s-100s of millions of metadata rows. Need it to be fast and cheap. KV better suited I guess?

N

Nick•4/27/24, 11:42 AM

Having a SQL like Interface would be ideal though

Aandrewitsover errr... you can do the same with d1, but it must only be per request, not betwee...

K

kian•4/27/24, 12:18 PM

It's been explained several times correctly. The object you get back from

env.DB.prepare(...)

env.DB.prepare(...)

env.DB.prepare(...)

env.DB.prepare(...) is just an object, it has almost nothing to do with SQLite. It is sent to your database, which makes a prepared statement with the SQL text and binds it with your parameters.

By re-using the same SQL text, you will re-use the prepared statement. (when using

prepare

prepare

both times, of course)

NNick Hmm, my use case is I need to check metadata on every request, and there will be...

B

bret_pat•4/27/24, 12:36 PM

From what you've said, you haven't excluded using D1 yet.

Things like knowing how performant it needs to be (remember, it's just a single DO doing all the work), and space limitations. Also how big per row you're expecting

NNick Hmm, my use case is I need to check metadata on every request, and there will be...

B

bret_pat•4/27/24, 12:36 PM

Row limits are 1MB, and then of course, the 10GB limit per db

NNick Hmm, my use case is I need to check metadata on every request, and there will be...

B

bret_pat•4/27/24, 12:37 PM

100s of millions of rows probably means you need multiple D1 and so D1 is a lot harder to use then

K

kamama•4/28/24, 11:20 AM

Sharding is your best friend ever

R

Rubi•4/29/24, 12:27 AM

I'm doing D1 load test 1 INSERT/hit

Ccopper super nice, so d1 maxes out at -300 inserts rpm?!

R

Rubi•4/29/24, 5:40 AM

not really a maxes out, it can go more depends on the Locust setting

also I'm doing the benchmark from my home PC to Cloudflare Pages with D1 binding, GET / without client payload, so my connection quality take a part there

Ccopper ah ok but still nice

R

Rubi•4/29/24, 5:41 AM

absolutely

Ccopper wondeirng for weeks if sqlite (d1 or turso) is enough re insert perf or i just s...

R

Rubi•4/29/24, 5:43 AM

this is exactly what I wondering about in #kv , and I decide to stay with D1 for my startup

R

Rubi•4/29/24, 5:47 AM

I just can't handle the stress managing VPS's, or even, DB on VPS

I think andrew is confused because D1 has a different API for it.

Similar Threads

Similar Threads

Similar Threads