hi is it possible to save a d1 prepared statement in a global variable or something when my worker i

hi is it possible to save a d1 prepared statement in a global variable or something when my worker is initializing, so I can reuse it across multiple invocations? currently, I've got the following:

export class SampleEndpoint extends OpenAPIRoute {
  
  async handle(c) {
    const stmt = await c.env.DB.prepare(...);
  }
}

export class SampleEndpoint extends OpenAPIRoute {
  
  async handle(c) {
    const stmt = await c.env.DB.prepare(...);
  }
}

export class SampleEndpoint extends OpenAPIRoute {
  
  async handle(c) {
    const stmt = await c.env.DB.prepare(...);
  }
}

export class SampleEndpoint extends OpenAPIRoute {
  
  async handle(c) {
    const stmt = await c.env.DB.prepare(...);
  }
}

so unless there's some sort of caching under the hood, I guess it'd have to parse the query every time my handler gets called, which I want to avoid. Mainly, I'm not sure how to access the

c.env.DB

c.env.DB

object from outside the

handle

handle

function of an endpoint

edit: actually i'm gonna introduce a db abstraction layer which caches prepared stmts in global variables and if there's no prepared stmt cached then it creates a fresh one. from reading online, it seems like global variables' consistency guarantees would b fine for this sort of use-case

o7•9/15/24, 12:01 AM

I've encountered a use case for dynamic bindings. Can't wait!

Ooof2win2 i am executing the queries in a new fetch request every time. i literally can’t ...

akashra•9/15/24, 8:15 AM

Right, so in this case, you should probably consider writing something that either enabled these to be batched from whatever is calling to the worker, or having them push to a queue which can periodically process data off that queue if you don't require synchronous responses.

oof2win2•9/15/24, 8:16 AM

sqlite should 100% be able to handle 300 writes per second

Ooof2win2 sqlite should 100% be able to handle 300 writes per second

akashra•9/15/24, 8:17 AM

It can, but I was more getting at why if you have a single process responsible for initiating all of these, why you're calling 300 separate queries when they don't need to be - or can be procesed without the overhead of locks in different sessions.

oof2win2•9/15/24, 8:18 AM

wym they don't need to be

oof2win2•9/15/24, 8:18 AM

its 300 separate http requests so 300 separate inserts make sense

akashra•9/15/24, 8:18 AM

you gotta remember how each worker is loading that data, obtaining the lock to write that data, then having to process that to write back and return.

akashra•9/15/24, 8:18 AM

yes - why are you doing 300 separate http requests - from the way this is a specific number, it sounds like they all come from the same client?

oof2win2•9/15/24, 8:19 AM

i just did 300 at once

oof2win2•9/15/24, 8:19 AM

they come from db triggers

oof2win2•9/15/24, 8:19 AM

it's possible i get only 1 and it's possible i get 2000

akashra•9/15/24, 8:20 AM

you're calling out so a db trigger fires a call to a http endpoint having access to the same db the event triggering it originated from?

akashra•9/15/24, 8:21 AM

I'm trying to figure out how you've ended up in a situation where you have hundreds of queries needing to be executed as the result of any single event, such that they can't be batched - unless they're a synchronous chain

Aakashra you're calling out so a db trigger fires a call to a http endpoint having access...

oof2win2•9/15/24, 8:22 AM

it doesnt have access to the same db

oof2win2•9/15/24, 8:22 AM

it's from an external supabase db and here i am doing external processing + batching

akashra•9/15/24, 8:23 AM

okay, but what's the process flow look like such that those calls can't be batched to justify writing a single service endpoint if you have a situation that necessitates making a large number of calls to the d1 db (which you're currently doing via a worker for each?)

oof2win2•9/15/24, 8:25 AM

i thought the point of workers+d1 was that it could handle this usecase, adding a whole queue seems like overkill to me

akashra•9/15/24, 8:26 AM

the performance hit you're likely complaining about here is not that sqlite can't handle 300 (or N) queries in quick succession. It's that you have potentially that many separate processes trying to obtain a connection and lock on that db which have to be handled in a queue.

oof2win2•9/15/24, 8:27 AM

yes i get that, but i thought that was the point of cf workers - they could just handle that

oof2win2•9/15/24, 8:27 AM

like if you have a realworld application with hundreds of requests per second it should "just work"

akashra•9/15/24, 8:28 AM

for read-heavy instances on the same data, sure.

oof2win2•9/15/24, 8:30 AM

well in that case something write heavy would make more sense here

oof2win2•9/15/24, 8:31 AM

ah well

akashra•9/15/24, 8:35 AM

I'm trying to see if I can find the tech architecture article on how workers and D1 DBs are exposed to a worker and kept in sync - that might make it make more sense as to why you would run in to issues with what you're trying to do here.

So yes, from the limited detail you've given, it sounds more like an appropriate use-case for a more persistent DB.

Yyeeet hi is it possible to save a d1 prepared statement in a global variable or someth...

kian•9/15/24, 12:24 PM

This isn’t necessary, prepared statements in your Worker are just a JS class.

kian•9/15/24, 12:24 PM

(calling prepare never talks to your DB yet, only when you run it)

kian•9/15/24, 12:25 PM

All of the statement reuse is handled by D1 itself.

Ephemeros•9/15/24, 12:51 PM

what's the standard way of initializing a database's values? would that be in a migration sql?

Ephemeros•9/15/24, 12:51 PM

like, i want to seed some default admin user etc

Deep•9/15/24, 2:40 PM

hi is there anyway to sync a local sqlite db for a desktop app with D1?

Kkian This isn’t necessary, prepared statements in your Worker are just a JS class.

yeeetOP•9/15/24, 2:54 PM

Thanks

cosbgn•9/15/24, 4:55 PM

Has cloudflare ever thought about allowing https://github.com/asg017/sqlite-vec on D1?

I know about vectorize, but having everything as simple as SQLITE would be unbeatable. I know turso does it and it seems to work very well.

cosbgn•9/15/24, 4:56 PM

it would allow for much better metadata filtering etc having the whole power of SQL

cosbgn•9/15/24, 5:05 PM

Can I get it to work simply using:

npm:sqlite-vec

npm:sqlite-vec

?
Like on this: https://alexgarcia.xyz/sqlite-vec/js.html

Ccosbgn Can I get it to work simply using: `npm:sqlite-vec`? Like on this: https://alexg...

kian•9/15/24, 5:20 PM

On Workers? No, you won't be able to use that package.

Vijin•9/16/24, 5:29 AM

Hi, i am new to D1, i would like to use D1 for my production application. Can you guys please give a clarification on the below doubts
1. Is D1 Production Ready?
2. Saw that D1 has a maximum size limit of 10GB per Database in paid plan. Can we upgrade that?

Dinfer•9/16/24, 8:17 AM

Is transaction supported in future?

DDinfer Is transaction supported in future?

oof2win2•9/16/24, 9:37 AM

it's on the roadmap. the intention is that transactions are ran next to the db https://blog.cloudflare.com/whats-new-with-d1/

VVijin Hi, i am new to D1, i would like to use D1 for my production application. Can yo...

Isaac McFadyen•9/16/24, 11:14 AM

Production ready: yes, D1 is Generally Available.
Maximum limit: no, cannot be raised. The intention is that you use multiple smaller DBs (i.e. one per customer) instead of one large one, as the infrastructire is designed for many smaller individual DBs.

0x146231489231923•9/16/24, 11:32 AM

can i connect to d1 from any client?

00x146231489231923 can i connect to d1 from any client?

Isaac McFadyen•9/16/24, 12:01 PM

You can either use a Worker or the Cloudflare REST API (which is rate-limited and slower than a Worker binding). There's no "connection string" or similar though, because SQLite doesn't have a standardized connection protocol. You'll need to write anything that connects to D1 outside a Worker yourself.

0x146231489231923•9/16/24, 2:39 PM

so the best scenario is query inside worker?

IIsaac McFadyen Production ready: yes, D1 is Generally Available. Maximum limit: no, cannot be r...

boop•9/16/24, 3:21 PM

Dynamic bindings when

Deep•9/16/24, 3:23 PM

hi is there anyway to sync a local sqlite db for a desktop app with D1?

Bboop Dynamic bindings when 😭

�

𝓈𝒽𝓎•9/16/24, 3:27 PM

:NotLikeThis:

Bboop Dynamic bindings when 😭

oof2win2•9/16/24, 4:10 PM

i bet on cf birthday week

oof2win2•9/16/24, 5:56 PM

once

oof2win2•9/16/24, 5:56 PM

also probably use r2 for logs

Original message was deleted

oof2win2•9/16/24, 5:58 PM

the reason theres a limit and you will be forced to use dynamic bindings is just filesize. its not practical to load a 10gb file for a database for every request

hi is it possible to save a d1 prepared statement in a global variable or something when my worker i

Similar Threads

Similar Threads

Similar Threads