```db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),``` Why is bind used he

db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),

db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),

Why is bind used here instead of doing everything in the sql code

SSamantas5855 ```db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),```...

Matt Silverlock•10/9/23, 9:13 PM

It’s an example: in practice, those values will be user provided and so you always want to use a parameterized query for security.

SSamantas5855 ```db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),```...

Matt Silverlock•10/9/23, 9:14 PM

https://www.sqlshack.com/using-parameterized-queries-to-avoid-sql-injection/#:~:text=Parameterized%20queries%20is%20a%20technique,for%20the%20type%20and%20length.

SQL Shack - articles about database auditing, server performance, data recovery, and moreHadi Fadlallah

Using parameterized queries to avoid SQL injection

In this article, we will explain what the SQL injection attack is, why it could be hazardous, and how to defend our SQL database from this attack using parameterized queries and some third-party tools.

soclearn•10/9/23, 9:18 PM

how d1 deal with non english charactors encoding
i can only play d1 with english values

soclearn•10/9/23, 9:19 PM

there is no docs just on cf offical site,even on google

soclearn•10/9/23, 9:19 PM

can anyone help

Ssoclearn how d1 deal with non english charactors encoding i can only play d1 with english...

Matt Silverlock•10/9/23, 9:20 PM

What are you trying to do? Most SQL functions accept UTF-8 encoding.

soclearn•10/9/23, 9:27 PM

sorry，it might be cause by i didnt store the data correctly

kojo•10/9/23, 10:17 PM

heya, good folks of d1!

I'm currently building a project on cloudflare workers+pages+d1 that I'd like to be forkable. So the ideal workflow for the users is:

fork the repo of my deployed solution
set things up
run GitHub Action and get a URL

Now, for project without D1 I can at least theoretically imagine how the step 2 would look like. I. e. "create a cloudflare account, get token, paste token to GitHub Action Secrets". But with D1 I'm a bit stuck.

The reason I'm stuck is that the unique id of the database is in wrangler.toml and that the d1 is not even creatable in the dashboard until the first worker is deployed. So the user flow for setup becomes something like:
a. run deployment action with database turned off. It will deploy the worker that can't work because there's no database
b. go create a database in the dashboard
c. copy-paste the details of the database from dashboard to repo
d. deploy the solution again - this time with database enabled

Is there a better way? Am I missing something?

kojo•10/9/23, 10:18 PM

(jeez, I didn't intent there to be a waving sticker and now apparently I can't remove it, sorry!)

Ddpeek But reused in what sense? In the same worker execution context? Or is the compil...

kian•10/9/23, 10:31 PM

It's not specific to D1, it's just your usual SQLite prepared statements. Executing a SQL statement requires compilation, so it's compiled and then held in memory for later re-use (all you have to do is bind new parameters rather than re-evaluate the entire query).

kian•10/9/23, 10:32 PM

The execution context in this case is the Durable Object that's backing your D1 database, so it'll hang around after inactivity before it's eventually evicted (assuming no more requests).

kian•10/9/23, 10:33 PM

If you had a generic query, like

SELECT email FROM users WHERE name = ?1

SELECT email FROM users WHERE name = ?1

, then SQLite doesn't need to re-compile

SELECT email FROM users WHERE name =

SELECT email FROM users WHERE name =

it can just take that statement from last time, bind a new
```
?1
```
```
?1
```
and execute it.

Kkian The execution context in this case is the Durable Object that's backing your D1 ...

Michał•10/9/23, 10:40 PM

So, in layman terms, this should improve performance across multiple worker executions, not just within one worker run?

Michał•10/9/23, 10:40 PM

Lovely

kian•10/9/23, 10:47 PM

https://x.com/KentonVarda/status/1659551757796515846?s=20 is a good thread by Kenton (the Tech Lead of Workers) on the implementation of the new storage subsystem (still Durable Objects, but SQLite in the runtime).

Kenton Varda (@KentonVarda)

So I've spent the last ~three months writing a new distributed storage engine based on SQLite and it has been FUN!

https://t.co/NVorwS46x8

Likes

485

Twitter

•

5/19/23, 1:29 PM

kian•10/9/23, 10:47 PM

The alpha backend was WebAssembly in DOs, likely similar to https://github.com/rkusa/wasm-sqlite, which ran into the issues that Kenton talks about.

kojo•10/9/23, 10:59 PM

"d1_databases[0]" bindings must have a "database_id" field

why? why can't I identify the database by name only, it would've been so much easier

kian•10/9/23, 11:01 PM

There's a TODO to allow that

Kkian There's a TODO to allow that

kojo•10/9/23, 11:03 PM

that would be super dope. Would solve the majority of my forkability problem

ANhaingMae•10/10/23, 1:09 AM

npx wrangler deploy

ANhaingMae•10/10/23, 1:10 AM

{
"result": {
"response": "Hello, World first appeared in 1974 at Bell Labs when Brian Kernighan included it in the C programming language example. It became widely used as a basic test program due to simplicity and clarity. It represents an inviting greeting from a program to the world."
}
}

Fun Miles•10/10/23, 3:40 AM

I created a database with wrangler, added some tables also with wrangler using SQL commands and the database shows up in the dashboards, but not the tables. Yet the number of queries climbs (with delay wrt the actual wrangler-submitted questions).
Is that expected?

meyer•10/10/23, 4:52 AM

ask in the drizzle discord

meyer•10/10/23, 4:52 AM

discord dot gg slash BHSuaJEY

Cretezy•10/10/23, 5:06 AM

Hi, I just published a new package to allow creating backups of D1 databases without using a Worker. Hopefully this helps someone!
https://github.com/Cretezy/cloudflare-d1-backup

GitHub

GitHub - Cretezy/cloudflare-d1-backup: Dump Cloudflare D1 databases...

Dump Cloudflare D1 databases (without Workers). Contribute to Cretezy/cloudflare-d1-backup development by creating an account on GitHub.

JSON•10/10/23, 5:51 AM

I'm asking you again because d1 is beta.
Do I still have to do the vacuum command manually?

Cretezy•10/10/23, 6:11 AM

Thank you!

Max (@rozenmd)•10/10/23, 7:50 AM

i think i might just add something like that to wrangler

Max (@rozenmd)•10/10/23, 7:51 AM

we'll eventually rewrite it to use workerd but running

select * ...

select * ...

against your whole db and dumping to a local sql file i can ship today

CCretezy Hi, I just published a new package to allow creating backups of D1 databases wit...

w3bcode•10/10/23, 8:11 AM

Thanks a lot for this

MMax (@rozenmd)i think i might just add something like that to wrangler

w3bcode•10/10/23, 8:13 AM

I would really appreciate a way to backup d1 database locally. I pitched D1 storage but as a fail safe, the client didnt trust enough on time travel and wanted a way to take backup locally on disk.

Paolo•10/10/23, 12:31 PM

does sequelize work with D1? can you suggest another ORM by any chance?

PPaolo does sequelize work with D1? can you suggest another ORM by any chance?

DaniFoldi•10/10/23, 12:41 PM

I recommend drizzle, we recently started migrating all projects to it and it works flawlessly - with D1, with postgres (through hyperdrive), and others

Ddpeek I'm seeing some performance numbers with D1 that don't add up... wondering if th...

Max (@rozenmd)•10/10/23, 1:19 PM

what you're seeing is the difference between how long d1 took to execute your query, and how long it took to go from request -> query -> response

Max (@rozenmd)•10/10/23, 1:19 PM

we return just the query response time, since that's controllable by the user (request less data/index your tables etc)

DDaniFoldi I recommend drizzle, we recently started migrating all projects to it and it wor...

Michał•10/10/23, 1:28 PM

Did you observer any performance penalty when using Drizzle with D1 compared to just raw SQL and prepared statements?

MMichał Did you observer any performance penalty when using Drizzle with D1 compared to ...

DaniFoldi•10/10/23, 1:29 PM

nothing measurable, so at most a couple ms - and the queries drizzle generates are now pretty efficient too, so in some cases it might be better on the DB site if we don't hand-write everythign

GuillaumeLakano•10/10/23, 3:19 PM

Hi!
An assigned UUID to a D1 database will stay the same forever, even if we move from Beta to GA, or if we need to uses Time Travel ?
To give some context, I'm asking because as the concept of database-per-user use-cases will be soon possible (only manual bindings in wrangler.toml for now and limited to ~5K), I suppose the future dynamic binding will uses the UUID assigned to the D1 database. Then, we want to save this UUID in other databases, and want to be sure this UUID will never change later, we could even consider it as a replacement of a classic id_customer field.

GGuillaumeLakano Hi! An assigned UUID to a D1 database will stay the same forever, even if we mo...

Isaac McFadyen•10/10/23, 3:21 PM

Time travel = yes, stable UUID
Alpha -> Beta, not stable, because you need to recreate the database
Beta -> GA, probably stable, an employee has said here before that there shouldn't need to be any migrations involving recreating of databases. Ref: https://canary.discord.com/channels/595317990191398933/992060581832032316/1153997053861576744

Isaac McFadyen•10/10/23, 3:21 PM

But D1 is in beta and no guarantees are made. If something goes wrong or DBs need to be migrated the UUID may change.