Cloudflare Developers•3y ago

Hi, can we use this outside of workers? Since currently the db is managed by python code running in

Hi, can we use this outside of workers? Since currently the db is managed by python code running in our servers.

Unsmart•10/9/23, 7:53 PM

No clue

afaik the only documented usage of D1 is through workers

UUnsmart No clue 🤷 afaik the only documented usage of D1 is through workers

Samantas5855OP•10/9/23, 7:55 PM

It has an https api

UUnsmart No clue 🤷 afaik the only documented usage of D1 is through workers

Max (@rozenmd)•10/9/23, 7:58 PM

i went through and wrote up an openapi spec: https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

SSamantas5855 Hi, can we use this outside of workers? Since currently the db is managed by pyt...

Max (@rozenmd)•10/9/23, 7:58 PM

it won't be as fast as writing code in a Worker and invoking it from your existing code, but calling the API should work, yeah

MMax (@rozenmd)it won't be as fast as writing code in a Worker and invoking it from your existi...

Samantas5855OP•10/9/23, 8:00 PM

speed isn't an issue, how do you insert two rows for example using one post request?

SSamantas5855 speed isn't an issue, how do you insert two rows for example using one post requ...

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

the sql payload takes any valid SQL string - so like "INSERT ..; INSERT ..; ..." works

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

you can test it in the Cloudflare dashboard, D1 console tab

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

uses the same API

SSamantas5855 speed isn't an issue, how do you insert two rows for example using one post requ...

Hello, I’m Allie!•10/9/23, 8:05 PM

Though you can also add multiple value fields to a single insert

HHello, I’m Allie!Though you can also add multiple value fields to a single insert

Samantas5855OP•10/9/23, 8:13 PM

what do you mean

MMax (@rozenmd)the sql payload takes any valid SQL string - so like "INSERT ..; INSERT ..; ..."...

Samantas5855OP•10/9/23, 8:14 PM

thank you, I wonder tho what the limits of the api are. Won't such big requests get blocked?

Samantas5855OP•10/9/23, 8:14 PM

Is it possible to upload the db each time as a file?

SSamantas5855 Is it possible to upload the db each time as a file?

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

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 ...

Samantas5855OP•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)•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

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

Hmm, I see thanks

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

Is there a limit on consecutive requests?

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

and their size

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

npx wrangler d1 migrations apply

to run without needing to supply a

? 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?

Samantas5855OP•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 = ?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