Hey Guys, can we cache the D1 results ? and is it production ready ?

JJadugar_Jaggu Hey Guys, can we cache the D1 results ? and is it production ready ?

M

D1 is in public beta. While we expect breaking changes and issues to be minimal, they may still occur. We generally do not recommend running large production workloads on beta products.

from https://developers.cloudflare.com/d1/

---

Being in a Worker, you have full access to the cache API: https://developers.cloudflare.com/workers/runtime-apis/cache/

Ddpeek I have a question about prepared statements: the docs state that prepared statem...

M

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

prepared statements help protect you against sql injection and improve performance when a query is re-used, as the db doesn't need to re-parse the query each time

S

shixzie•10/9/23, 3:40 PM

I'd love an option to download a copy of the database to run and test locally

C

cfanclub•10/9/23, 3:54 PM

CREATE TABLE blog (
    id TEXT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    content_url TEXT,
    frontmatter JSON,
    slug TEXT UNIQUE,
    title TEXT,
    website_ref_id TEXT,
    FOREIGN KEY (website_ref_id) REFERENCES website(id)
);

CREATE TABLE blog (
    id TEXT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    content_url TEXT,
    frontmatter JSON,
    slug TEXT UNIQUE,
    title TEXT,
    website_ref_id TEXT,
    FOREIGN KEY (website_ref_id) REFERENCES website(id)
);

CREATE TABLE blog (
    id TEXT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    content_url TEXT,
    frontmatter JSON,
    slug TEXT UNIQUE,
    title TEXT,
    website_ref_id TEXT,
    FOREIGN KEY (website_ref_id) REFERENCES website(id)
);

CREATE TABLE blog (
    id TEXT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    content_url TEXT,
    frontmatter JSON,
    slug TEXT UNIQUE,
    title TEXT,
    website_ref_id TEXT,
    FOREIGN KEY (website_ref_id) REFERENCES website(id)
);

I have the given schema in d1 database, I am using the preprare statement as

DB.prepare('select * from blog').all()

DB.prepare('select * from blog').all()

DB.prepare('select * from blog').all()

DB.prepare('select * from blog').all() But the stored frontmatter is returning json as string instead of automatically parsing it out as json.

Am I missing something ?

Ccfanclub ``` CREATE TABLE blog ( id TEXT PRIMARY KEY, created_at DATETIME DEFAULT...

M

Michał•10/9/23, 3:56 PM

SQLite stores JSON as ordinary text.

C

cfanclub•10/9/23, 3:57 PM

Ohh, so that means i would need to manually parse it to object using something like

JSON.parse()

JSON.parse()

JSON.parse()

JSON.parse() or is there any way to do this automatically ? I read somewhere, that libraries like kysley or drizzle does this automatically, but I dont want to add extra layer of code and stick to prepare statements

M

Michał•10/9/23, 3:58 PM

I think you'd need to parse it from text to json manually, yeah

K

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

https://developers.cloudflare.com/d1/learning/querying-json/

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

C

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

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?

C

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

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

K

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

Theoretically

GLOB

GLOB

should already be limited, as

LIKE

LIKE

&

GLOB

GLOB

are supposed to be controlled by

SQLITE_LIMIT_LIKE_PATTERN_LENGTH

SQLITE_LIMIT_LIKE_PATTERN_LENGTH

which is set to 50.

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

C

Curtis Jones•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?

K

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?

C

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

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

K

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

since I can't reproduce your issue,

GLOB

GLOB

is limited to 50 bytes as documented for me

C

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

C

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.

K

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.

K

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

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

D

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?

U

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)

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

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

U

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

Yup you would just add more to the list

S

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

U

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 …