Cloudflare Developers•3y ago

SQLite binaries aren't guaranteed to be safe - best we can do is SQL strings at the moment

MMax (@rozenmd)SQLite binaries aren't guaranteed to be safe - best we can do is SQL strings at ...

Samantas5855•10/9/23, 8:17 PM

While not in the documentation link you sent, didn't the api have a method to backup and restore from a backup?

SSamantas5855 While not in the documentation link you sent, didn't the api have a method to ba...

Max (@rozenmd)OP•10/9/23, 8:19 PM

the alpha had backup and restore, the beta has default-on point in time recovery

MMax (@rozenmd)the alpha had backup and restore, the beta has default-on point in time recovery

Samantas5855•10/9/23, 8:23 PM

Hmm, I see thanks

Samantas5855•10/9/23, 8:23 PM

Is there a limit on consecutive requests?

Samantas5855•10/9/23, 8:23 PM

and their size

Max (@rozenmd)OP•10/9/23, 8:27 PM

when you reach your database memory limits, you'll start seeing errors from the api

kian•10/9/23, 8:27 PM

Is it subject to the 1200/5 min limit of the API generally?

kian•10/9/23, 8:27 PM

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

iirc there's a few APIs with their own limits - I guess if D1 ends up being popular enough to use outside of a Worker then that might be an option, but I suppose that might be directed to setting up your own API w/ a Worker

EvanPiro•10/9/23, 8:49 PM

Is there a way to force npx wrangler d1 migrations applynpx wrangler d1 migrations apply to run without needing to supply a yy? We're setting up CI to run migrations on deployment and are not seeing them apply.

EEvanPiro Is there a way to force `npx wrangler d1 migrations apply` to run without needin...

Cyb3r-Jak3•10/9/23, 8:52 PM

It should auto skip that prompt if detcted as CI or non-interactive. Are you seeing the prompt appear and thus CI fails or that when it applies migration there are no changes?

Samantas5855•10/9/23, 9:00 PM

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 = ?1SELECT 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)OP•10/10/23, 7:50 AM

i think i might just add something like that to wrangler

Max (@rozenmd)OP•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?

SQLite binaries aren't guaranteed to be safe - best we can do is SQL strings at the moment

Similar Threads

Similar Threads

Similar Threads