hello everyone, I'm encountering a problem when applying drizzle generated migrations to Cloudflare

hello everyone, I'm encountering a problem when applying drizzle generated migrations to Cloudflare D1. The initial migration is processed without any hiccups, but I run into an issue with subsequent migrations, especially those that modify existing columns. Here's the error I'm receiving:

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

I feel this is related to D1 than to drizzle as applying migrations locally runs smooth and raise errors only when attempting to run migrations to remote db

Bbkyerv hello everyone, I'm encountering a problem when applying drizzle generated migra...

Adi•10/8/23, 3:23 PM

That looks like the output of drizzle kit and not a D1 error.

The comment it's generated is accurate. Nothing to do with D1.

bkyervOP•10/8/23, 3:26 PM

I see. why can it then apply them locally and raise error in case remote db is attempted?

cfanclub•10/8/23, 3:28 PM

Is it possible to download time-travel backup locally of new d1 database using wrangler ?

Michał•10/8/23, 4:26 PM

Is there a way to provide the primary location hint when creating a D1 database via the API? (POST

/accounts/{account-identifier}/d1/database

/accounts/{account-identifier}/d1/database

)
Right now DBs created this way get assigned to the

WNAM

WNAM

region but I'd prefer Europe

kian•10/8/23, 4:34 PM

Add primary_location_hint: <loc> to the body

Kkian Add primary_location_hint: <loc> to the body

Michał•10/8/23, 6:58 PM

Thank you and sorry for late response. That did the trick, even though the openapi schema doesn't mention it

MarkB•10/8/23, 7:29 PM

hi, what is health check region in load balancing? if region is 1 in load balancing, it doesn't work for request from other regions?

Noel•10/8/23, 8:27 PM

I started getting

 binding __D1_BETA__DB of type d1 failed to generate. Please try again later or contact support.
  [code: 10021]

 binding __D1_BETA__DB of type d1 failed to generate. Please try again later or contact support.
  [code: 10021]

on new deploy attempts. Is it related to the current incident? https://www.cloudflarestatus.com/incidents/64wsqjxnljn3

James•10/8/23, 8:33 PM

Very likely, yes. I'd recommend trying again once the incident is resolved

FFerry I get the same and in the cloudflare web 'dashboard' under workers and pages/d1 ...

James•10/8/23, 8:42 PM

yep that one is definitely related to https://www.cloudflarestatus.com/incidents/64wsqjxnljn3. Please try again once the incident is resolved, apologies for the issues!

Jadugar_Jaggu•10/9/23, 7:28 AM

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 ?

Max (@rozenmd)•10/9/23, 7:48 AM

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

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

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

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

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)
);

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()

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

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

SQLite stores JSON as ordinary text.

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()

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

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

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

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:

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?

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

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

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?

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 Jones•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,

GLOB

GLOB

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.

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.