I guess if you're not using the database for a while, it gets unloaded, and the whole thing needs to

AArma I guess if you're not using the database for a while, it gets unloaded, and the ...

M

Max (@rozenmd)•1/17/24, 11:53 AM

pretty much, yeah

A

ArmaOP•1/17/24, 2:56 PM

Maybe I should split up my databases then

A

ArmaOP•1/17/24, 2:57 PM

and put the big fat table on its own database, this way I can query other tables without waiting as much

A

ArmaOP•1/17/24, 3:00 PM

And to avoid making Clouflare load n gigabytes of data when I just want to query a table of 2 KB

B

Bonadio•1/17/24, 4:55 PM

Hi, I am trying to simulate some stored procedure in D1 using batch. I was able to create a "TRIGGER" that RAISE an error. Is it ok to use the "TRIGGER" on D1?

K

kian•1/17/24, 6:25 PM

So you don't get an error at all, or you do but it's vague?

P

pinky•1/17/24, 7:23 PM

Hi, is it safe to create cached orm that is using bindings from previous fetch, something like this?

const db = getDrizzle(d1: D1Database) => {
    if (!db.cached) {
        db.cached = drizzle(d1);
    }
    return db;
}

export default {
    async fetch(request: Request, env: Env) {
        const db = getDrizzle(env.DB);
        ...
    }
}

const db = getDrizzle(d1: D1Database) => {
    if (!db.cached) {
        db.cached = drizzle(d1);
    }
    return db;
}

export default {
    async fetch(request: Request, env: Env) {
        const db = getDrizzle(env.DB);
        ...
    }
}

const db = getDrizzle(d1: D1Database) => {
    if (!db.cached) {
        db.cached = drizzle(d1);
    }
    return db;
}

export default {
    async fetch(request: Request, env: Env) {
        const db = getDrizzle(env.DB);
        ...
    }
}

const db = getDrizzle(d1: D1Database) => {
    if (!db.cached) {
        db.cached = drizzle(d1);
    }
    return db;
}

export default {
    async fetch(request: Request, env: Env) {
        const db = getDrizzle(env.DB);
        ...
    }
}

Ppinky Hi, is it safe to create cached orm that is using bindings from previous fetch, ...

M

Matt Silverlock•1/17/24, 8:11 PM

There's no long-running connection to cache, and so I'd consider this an anti-pattern (I assume you're trying to reduce latency)

MMatt Silverlock There's no long-running connection to cache, and so I'd consider this an anti-pa...

P

pinky•1/17/24, 9:57 PM

Should every worker run be treated as an isolated environment so nothing should be cached into module scope for reuse?

Ppinky Should every worker run be treated as an isolated environment so nothing should ...

M

Matt Silverlock•1/17/24, 10:06 PM

You should assume it may not be retained as invocations may not share the same underlying context.

But that’s orthogonal to my advice: there should be no measurable performance benefit to trying to “memoize” a binding like that - D1 doesn’t have the heavy connection setup that a traditional database does. Same goes for other bindings.

MMatt Silverlock You should assume it may not be retained as invocations may not share the same u...

P

pinky•1/17/24, 10:17 PM

Hmm ok, but let's say there is a trpc implementation and it has a bunch of routers that are loaded dynamically to avoid loading a big chunk of javascript for no reason. The most procedures do some database operation. However, to follow the advice that the same running worker (I assume it's alive for some time it won't die after the first fetch) should not consider any caching, a route to a procedure would need to be loaded dynamically every time with the latest env.DB passed into drizzle or used directly for a db operation. Is that a suggested approach?

I'm trying to understand if I should think about a worker as a lightweight express server that can be restarted any time but for the period when it's not restarted it can leverage some scoped caching or if this approach can cause some trouble and everything should be created fresh on every request.

Ppinky Hmm ok, but let's say there is a trpc implementation and it has a bunch of route...

M

Matt Silverlock•1/17/24, 10:20 PM

Memoizing heavy compute operations or serialization makes sense.

Memoizing bindings (which is what you asked) does not.

MMatt Silverlock Memoizing heavy _compute_ operations or serialization makes sense. Memoizing bi...

P

pinky•1/17/24, 10:23 PM

Ok got it, thanks for the clarification

Ppinky Hmm ok, but let's say there is a trpc implementation and it has a bunch of route...

K

kian•1/17/24, 10:25 PM

Think of it like a spot instance that you're unlikely to hit again, since that would require you hitting the same Cloudflare server.

Even if you can use the global scope, it'd only really benefit the user who already hit that Worker up until their connection ends.

C

Canopy•1/18/24, 7:34 AM

Would absolutely love to be able to use a D1 with my nextjs project but working out how on earth I'm meant to do local development with that is hard as nails. If anyone has an example project where they've solved this problem lemme know, because it's consuming so much of my time trying to work this out.

Y

Yiwan•1/18/24, 9:36 AM

Why do you need to do it locally? Set up dev environment on the actual cloud, wrangler can stream logs

JJoël Hello @Matt ! (from Prisma 👋 ) I found out that the types were recently changed...

M

Matt Silverlock•1/18/24, 11:26 AM

rows_written are rows affected, because index writes/modifications are… affected.

JJoël Hello @Matt ! (from Prisma 👋 ) I found out that the types were recently changed...

M

Matt Silverlock•1/18/24, 11:27 AM

The rest we haven’t yet documented as it may be subject to change, which is why that PR is still in draft

R

Rubi•1/18/24, 2:55 PM

GA when

V

Vy•1/18/24, 3:16 PM

targetting GA early April

MMatt Silverlock This is an interim solution, for what it's worth. As we've called out before, we...

V

Victor•1/18/24, 6:27 PM

With the GA announcement ^, is there an update on the "group bindings"? Interested for multi tenancy bindings for D1

VVictor With the GA announcement ^, is there an update on the "group bindings"? Interest...

M

Matt Silverlock•1/18/24, 6:39 PM

No update yet (and we announced the GA timeline back in Sept 2023, for what it's worth!)

M

Max (@rozenmd)•1/18/24, 7:07 PM

you'll find early april is at the end of Q1

U

Unsmart•1/18/24, 7:07 PM

big if true

G

Gagan Suie•1/18/24, 10:50 PM

Reporting a VERY weird bug. Anytime we add the word "What" then the remaining text turns into an object:
The topic column has TEXT: "What is your favorite clip?"

const result = await env.DB.prepare(
            SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1
        )
            .bind(weekNumber)
            .first()

const result = await env.DB.prepare(
            SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1
        )
            .bind(weekNumber)
            .first()

const result = await env.DB.prepare(
            SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1
        )
            .bind(weekNumber)
            .first()

const result = await env.DB.prepare(
            SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1
        )
            .bind(weekNumber)
            .first()

result {"_id":1,"weekNumber":3,"topic":"What [object Object]

G

Gagan Suie•1/18/24, 10:54 PM

row was added via the "Add data" button in cloudflare D1 dashboard.

G

Gagan Suie•1/18/24, 10:56 PM

const result = await env.DB.prepare(
            `SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1`
        )
            .bind(weekNumber)
            .first()
        console.log('result', JSON.stringify(result))

const result = await env.DB.prepare(
            `SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1`
        )
            .bind(weekNumber)
            .first()
        console.log('result', JSON.stringify(result))

const result = await env.DB.prepare(
            `SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1`
        )
            .bind(weekNumber)
            .first()
        console.log('result', JSON.stringify(result))

const result = await env.DB.prepare(
            `SELECT * 
            FROM weekly 
            WHERE weekNumber = ?1`
        )
            .bind(weekNumber)
            .first()
        console.log('result', JSON.stringify(result))

U

Unsmart•1/18/24, 10:57 PM

you can use 3 backticks:

```js
// my code

```js
// my code

```js
// my code

```js
// my code

```

U

Unsmart•1/18/24, 10:59 PM

Oh this is the wrangler issue... if your log has the word "at" everything after becomes [object Object]

G

Gagan Suie•1/18/24, 11:00 PM

result {"type":"table","name":"weekly","tbl_name":"weekly","rootpage":7,"sql":"CREATE TABLE weekly (\r\n    _id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    weekNumber INTEGER NOT NULL,\r\n    topic TEXT NOT NULL\r\n)"}

result {"type":"table","name":"weekly","tbl_name":"weekly","rootpage":7,"sql":"CREATE TABLE weekly (\r\n    _id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    weekNumber INTEGER NOT NULL,\r\n    topic TEXT NOT NULL\r\n)"}

result {"type":"table","name":"weekly","tbl_name":"weekly","rootpage":7,"sql":"CREATE TABLE weekly (\r\n    _id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    weekNumber INTEGER NOT NULL,\r\n    topic TEXT NOT NULL\r\n)"}

result {"type":"table","name":"weekly","tbl_name":"weekly","rootpage":7,"sql":"CREATE TABLE weekly (\r\n    _id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    weekNumber INTEGER NOT NULL,\r\n    topic TEXT NOT NULL\r\n)"}

U

Unsmart•1/18/24, 11:00 PM

https://github.com/cloudflare/workers-sdk/pull/4683
was merged already so maybe update your wrangler version?

G

Gagan Suie•1/18/24, 11:01 PM

ill try that now. i appreciate you guys.

G

Gagan Suie•1/18/24, 11:03 PM

brilliant! it worked!

A

anthony•1/19/24, 7:33 AM

Hi, i have around 50,000 rows i want to insert into D1 database regularly, if i do a sqlite database dump with wrangler like this:

wrangler d1 execute MYDB --file=MYFILE.sql

wrangler d1 execute MYDB --file=MYFILE.sql

wrangler d1 execute MYDB --file=MYFILE.sql

wrangler d1 execute MYDB --file=MYFILE.sql it is suuperr fast,
But when I want to insert them via Workers with

.prepare(..).bind(..).run(..)

.prepare(..).bind(..).run(..)

.prepare(..).bind(..).run(..)

.prepare(..).bind(..).run(..), it takes on average 20 rows/second which is very slow.
Is there a way I can insert them via Workers as fast as

wrangler

wrangler

?

A

anthony•1/19/24, 7:42 AM

Oh damn, I just realised while writing that question i was not reusing prepared statements... it is now suuper fast

K

kian•1/19/24, 7:42 AM

Wrangler uses the batch API https://developers.cloudflare.com/d1/how-to/query-databases/#batch-statements

A

anthony•1/19/24, 7:47 AM

Ohh thank you, I didn't see this EDIT: works perfectly ty

T

TRSC325•1/19/24, 10:31 PM

there is a securty risk for d1 if used for user data hosting as that would require a set of rules to prevent such a thing . which is not found in d1

T

TRSC325•1/19/24, 11:11 PM

I just have a concern as if a program was to read from a database in d1 it could get some form. of data like passwords and i can't controll what data they see when they log in

T

TRSC325•1/19/24, 11:26 PM

thinks but do you know any why for my to regulate who sees what and edits what like the user can edit there acount but not others.

N

Nikil•1/20/24, 12:32 AM

https://developers.cloudflare.com/pages/functions/bindings/#d1-databases Is NORTHWIND_DB just the example of the name of the DB in the environment variable or is it always called that?

Bindings · Cloudflare Pages docs

A binding enables your Pages Functions to interact with resources on the Cloudflare developer platform. Use bindings to integrate your Pages Functions …

K

kian•1/20/24, 12:32 AM

Just an example, the binding is whatever name you give it.

K

kian•1/20/24, 12:32 AM

Give your binding a name under Variable name.

N

Nikil•1/20/24, 12:33 AM

Okay. They really should update the doc to make that slightly clearer. It should say "For example, if your binding is NORTHIND_DB"

TTRSC325 thinks but do you know any why for my to regulate who sees what and edits what l...

U

Unsmart•1/20/24, 12:48 AM

you just do that with your code you dont give anyone direct access to the db

I guess if you're not using the database for a while, it gets unloaded, and the whole thing needs to

Similar Threads