Cloudflare Developers•3y ago

Is this a brand new database or did you create it during early alpha?

Kkian Is this a brand new database or did you create it during early alpha?

Curtis Jones•10/9/23, 4:20 PM

This is a brand new database (created a couple hours ago).

kianOP•10/9/23, 4:20 PM

since I can't reproduce your issue, GLOBGLOB is limited to 50 bytes as documented for me

Curtis Jones•10/9/23, 4:20 PM

I'll try the same thing.

Kkian since I can't reproduce your issue, `GLOB` is limited to 50 bytes as documented ...

Curtis Jones•10/9/23, 4:23 PM

I can confirm the same results you got with that test. However, a "real" query on my db that uses LIKE (with a 98 byte string) fails with that error while the GLOB equivalent succeeds. I'd very much like you to not fix this bug, so please disregard.

kianOP•10/9/23, 4:35 PM

I don’t think it’s a bug in D1 - D1 doesn’t enforce the limit itself, it just tells SQLite to do it. Maybe SQLite is optimising your pattern under the limit? I’m not sure.

kianOP•10/9/23, 4:35 PM

In any case, I wouldn’t consider it a bug.

David•10/9/23, 6:10 PM

Since today rows_written and rows_read is always 0 and the sql action wont get executed when running with "EXPLAIN QUERY PLAN". I have changed nothing in my code and it worked perfectly fine 2 days ago. Anyone else experiencing this?

Unsmart•10/9/23, 6:13 PM

d1.prepare('select * from abc where id in(?,?)').bind(1,2)

d1.prepare('select * from abc where id in(?,?)').bind(1,2)

Unsmart•10/9/23, 6:14 PM

Yup you would just add more to the list

Samantas5855•10/9/23, 7:49 PM

Currently we are on mongodb and everyday we insert 20k rows to the db. On mongodb we insert them all together as a table and it parses it. How can we mass insert rows on D1 all together in order to avoid spaming 20k requests.

SSamantas5855 Currently we are on mongodb and everyday we insert 20k rows to the db. On mongod...

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

https://developers.cloudflare.com/d1/platform/client-api/#batch-statements

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

UUnsmart https://developers.cloudflare.com/d1/platform/client-api/#batch-statements

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

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

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

Samantas5855•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

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

what do you mean

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

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

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

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)•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)•10/9/23, 8:27 PM

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

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

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

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

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

Is this a brand new database or did you create it during early alpha?

Similar Threads

Similar Threads

Similar Threads