Hi. Now that the max db size is adequate for my use, I'm testing D1 out again. I noticed that the do

Hi. Now that the max db size is adequate for my use, I'm testing D1 out again. I noticed that the docs still indicate a 50 byte limit for both LIKE and GLOB. I have found that to be accurate for LIKE, but GLOB does not seem to have that same limit (which is important to me). Is there a plan to impose a 50 byte limit on GLOB?

cfanclub•10/9/23, 4:04 PM

Yeah read through docs but this query didnt work

select id,
            content_url,
            json(frontmatter) as frontmatter,
            slug,
            title,
            website_ref_id,
            created_at
from blog

select id,
            content_url,
            json(frontmatter) as frontmatter,
            slug,
            title,
            website_ref_id,
            created_at
from blog

in prepare statement, but unfortunately that also didnt work.

CCurtis Jones Hi. Now that the max db size is adequate for my use, I'm testing D1 out again. I...

kian•10/9/23, 4:07 PM

Theoretically GLOBGLOB should already be limited, as LIKELIKE & GLOBGLOB are supposed to be controlled by SQLITE_LIMIT_LIKE_PATTERN_LENGTHSQLITE_LIMIT_LIKE_PATTERN_LENGTH which is set to 50.

Kkian Theoretically `GLOB` should already be limited, as `LIKE` & `GLOB` are supposed ...

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

Good to know. I guess I'll enjoy D1 while I can. If this is a limit that Cloudflare can adjust individually, is there any way I can check to see if that's what's going on in my case?

kian•10/9/23, 4:19 PM

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 JonesOP•10/9/23, 4:20 PM

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

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

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

Curtis JonesOP•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 JonesOP•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.

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

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

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

Hi. Now that the max db size is adequate for my use, I'm testing D1 out again. I noticed that the do

Similar Threads