<https://sqlite.org/draft/releaselog/3_44_0.html> > Add support for the JSONB binary representation

Kkian <https://sqlite.org/draft/releaselog/3_44_0.html> > Add support for the JSONB b...

H

Hello, I’m Allie!•10/3/23, 12:18 PM

Capnp on SQLite wen?

GGuillaumeLakano Good to heard, thank you for the answer ! I suppose you can't now spoil a little...

M

Matt Silverlock•10/3/23, 1:25 PM

Give me your account ID. You will need a paid Workers subscription though (I will not increase for free accounts). But to be transparent, are you worrying about a limit you are a long, long way from hitting? What limit do we set? No one believes “unlimited”

MMatt Silverlock Give me your account ID. You will need a paid Workers subscription though (I wil...

G

GuillaumeLakano•10/3/23, 1:51 PM

Thank you! I've just sent you in PM the requested private informations

MMatt Silverlock Open an issue on GitHub with your query and error. I will not keep up with it he...

D

divby0•10/3/23, 2:12 PM

I couldn't get any info or help on the exact SQL query generated by drizzle yet. But since drizzle is probably the most used ORM for d1, should I just create the issue with the drizzle queries (but not the exact generated SQL)?

Ddivby0 I couldn't get any info or help on the exact SQL query generated by drizzle yet....

M

Max (@rozenmd)•10/3/23, 2:17 PM

yep

MMax (@rozenmd)yep

D

divby0•10/3/23, 2:19 PM

Awesome, thanks. Just created it here: https://github.com/cloudflare/workers-sdk/issues/4094

It is not really a bug, so I put it in feature request since that was the only other thing. Feel free to move if not right

N

nclevenger•10/3/23, 9:02 PM

ugh ... I hate TOML ... does anyone have an example of the proper syntax in wrangler.toml for multiple D1 database bindings?

U

Unsmart•10/3/23, 9:08 PM

https://developers.cloudflare.com/workers/wrangler/configuration/#d1-databases

[[d1_databases]]
binding = "db1"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db2"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db1"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db2"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db1"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db2"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db1"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

[[d1_databases]]
binding = "db2"
database_name = "test-db"
database_id = "c020574a-5623-407b-be0c-cd192bab9545"

U

Unsmart•10/3/23, 9:09 PM

or

d1_databases = [
  { binding = "db1", database_name = "db1", database_id = "1" },
  { binding = "db2", database_name = "db2", database_id = "2" }
]

d1_databases = [
  { binding = "db1", database_name = "db1", database_id = "1" },
  { binding = "db2", database_name = "db2", database_id = "2" }
]

d1_databases = [
  { binding = "db1", database_name = "db1", database_id = "1" },
  { binding = "db2", database_name = "db2", database_id = "2" }
]

d1_databases = [
  { binding = "db1", database_name = "db1", database_id = "1" },
  { binding = "db2", database_name = "db2", database_id = "2" }
]

UUnsmart or ```toml d1_databases = [ { binding = "db1", database_name = "db1", databas...

D

Dahoom•10/3/23, 9:10 PM

thats .toml? or is there a way to specify the bindings in js

U

Unsmart•10/3/23, 9:10 PM

thats .toml not valid js dont think you can use js but there might be an option for json

D

Dahoom•10/3/23, 9:11 PM

interesting, never knew you know format it that way

Nnclevenger ugh ... I hate TOML ... does anyone have an example of the proper syntax in wran...

H

Hello, I’m Allie!•10/4/23, 7:56 AM

Hey Nathan, try

wrangler -j

wrangler -j

wrangler -j

wrangler -j

SPOILER_wrangler.json288B

O

Old Man Maker•10/4/23, 10:07 AM

Hi there.
This causes and error:

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?1");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?1");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?1");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?1");

this does not:

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?");

const batcher = await db.prepare("SELECT * FROM table WHERE ref = ?");

The inclusion of an indexer should be optional enough not to cause an issue.
Not sure if it's raised somewhere as a consideration?

V

Vikash•10/4/23, 11:30 AM

Any way to improve performance, it seems all queries takes 1.xx seconds, even on Cloudflare dashboard simple products table with 5 rows -

VVikash Any way to improve performance, it seems all queries takes 1.xx seconds, even on...

I

Isaac McFadyen•10/4/23, 11:34 AM

If all queries are slow can you check if you're on the beta or alpha version, and also whether anything else is slow other than D1? (for example, D1 is slow on my internet if I have an ongoing backup, since it saturates my internet link and slows everything else down)

VVikash Any way to improve performance, it seems all queries takes 1.xx seconds, even on...

M

Max (@rozenmd)•10/4/23, 11:41 AM

the speed of the API in the dashboard isn't indicative of what you should be seeing from your Worker - deploy a Worker and see how long your requests are taking

IIsaac McFadyen If all queries are slow can you check if you're on the beta or alpha version, an...

V

Vikash•10/4/23, 11:46 AM

Only D1 is slow, I am using other options like KV which works fine. How to check alpha or beta? I created the database via dashboard 2 weeks ago.

VVikash Only D1 is slow, I am using other options like KV which works fine. How to check...

I

Isaac McFadyen•10/4/23, 11:47 AM

If you created it that recently it should be

beta

beta

beta

beta, yes.

I

Isaac McFadyen•10/4/23, 11:47 AM

Alpha is the older D1 engine that doesn't work as well and is deprecated.

MMax (@rozenmd)the speed of the API in the dashboard isn't indicative of what you should be see...

V

Vikash•10/4/23, 11:47 AM

I deployed the worker and getting same result -

V

Vikash•10/4/23, 11:49 AM

While the KV is returning in less then 50ms

VVikash I deployed the worker and getting same result -

M

Max (@rozenmd)•10/4/23, 12:19 PM

what's the output of

wrangler d1 info <dbname>

wrangler d1 info <dbname>

?

MMax (@rozenmd)what's the output of `wrangler d1 info <dbname>`?

V

Vikash•10/4/23, 12:22 PM

VVikash Click to see attachment

I

Isaac McFadyen•10/4/23, 1:58 PM

Are you located in APAC (Asia-Pacific)?

IIsaac McFadyen Are you located in APAC (Asia-Pacific)?

V

Vikash•10/4/23, 2:02 PM

Yes, India

EEd Does this endpoint still work: `https://api.cloudflare.com/client/v4/accounts/${...

C

Cyb3r-Jak3•10/4/23, 5:05 PM

CORS is explicitly denied for api.cloudflare.com. You need else to proxy the request.

C

cfanclub•10/4/23, 5:29 PM

I want a help to use local sqlite created under .wrangler folder for local development and apply migration.

I used the command of

wrangler d1 migrations testdb apply --local

wrangler d1 migrations testdb apply --local

wrangler d1 migrations testdb apply --local

wrangler d1 migrations testdb apply --local command to apply migration, it worked successfully but when I use the prepare statement I am getting error of no table Test exists

G

God•10/4/23, 5:34 PM

Does D1 have any builtin support for generating an UUID? or is that something I should handle on the JS side?

GGod Does D1 have any builtin support for generating an UUID? or is that something I ...

I

Isaac McFadyen•10/4/23, 5:34 PM

JS-side, probably yeah.

C

Cretezy•10/4/23, 5:46 PM

Hi, congratulations on the open beta!
I would like to propose a feature: Exporting databases.
It's important to be able to do external backups, as well as be able to download entire databases for local analysis. Thank you.

CCretezy Hi, congratulations on the open beta! I would like to propose a feature: Exporti...

I

itsmatteomanf•10/4/23, 8:22 PM

There are packages that do it...

https://github.com/nora-soderlund/cloudflare-d1-backups

GitHub

GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a ...

A package to dump a copy of your experimental D1 database through a worker. - GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a copy of your experimental D1 database through a worker.

CCretezy Hi, congratulations on the open beta! I would like to propose a feature: Exporti...

M

Max (@rozenmd)•10/4/23, 8:39 PM

it's on my mind to add that, yep

VVikash Yes, India

V

Vikash•10/5/23, 2:50 AM

The database is for testing, feel free to takeover if you want to test? Or should I create new to test in different region?

O

Old Man Maker•10/5/23, 3:37 AM

Hi - I started getting these errors in a call that previously was not having errors:

Object {
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Failed to parse body as JSON, got: error code: 1042                                        
}

Object {
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Failed to parse body as JSON, got: error code: 1042                                        
}

Object {
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Failed to parse body as JSON, got: error code: 1042                                        
}

Object {
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Failed to parse body as JSON, got: error code: 1042                                        
}

Having checked the variables are all correct and the batching syntax appears correct, I'm a bit stuck on how to further debug this

OOld Man Maker Hi - I started getting these errors in a call that previously was not having err...

U

Unsmart•10/5/23, 3:40 AM

Can you share the code where you get this?

UUnsmart Can you share the code where you get this?

O

Old Man Maker•10/5/23, 3:42 AM

This and other functions are all now failing with the same error.
The code is

async function InsertIntoContentDatabase(db:D1Database, content:ContentDataType): Promise<D1Result<unknown>[]>{

    try {


        if(content.contentDataItemType === undefined){
            console.log('No contentDataItemType');            
            throw('Missing contentDataItemType Array');
        }


        //Because we may have multiple items to push into the database, we need to batch the call
        const batcher = await db.prepare("INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)")

        let binderCollection=[];

        for (let index = 0; index < content.contentDataItemType.length; index++) {
            const element = content.contentDataItemType[index];

            let temp = batcher.bind(
                element.contentGroupRef, 
                element.projectRef,
                element.contentElementRef,
                element.contentBakedStatement,
                element.publishState
            );
            binderCollection.push(temp);                

            console.log('Loop Batch=>', JSON.stringify(temp));
        }


        return await db.batch(binderCollection);
        
        
    } catch (error:any) {

        console.log('D1 Instance=>', JSON.stringify(db.dump()));

        console.log({
            message: error.message,
            cause: error.cause.message,
        });
        throw(error);
    }
}

async function InsertIntoContentDatabase(db:D1Database, content:ContentDataType): Promise<D1Result<unknown>[]>{

    try {


        if(content.contentDataItemType === undefined){
            console.log('No contentDataItemType');            
            throw('Missing contentDataItemType Array');
        }


        //Because we may have multiple items to push into the database, we need to batch the call
        const batcher = await db.prepare("INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)")

        let binderCollection=[];

        for (let index = 0; index < content.contentDataItemType.length; index++) {
            const element = content.contentDataItemType[index];

            let temp = batcher.bind(
                element.contentGroupRef, 
                element.projectRef,
                element.contentElementRef,
                element.contentBakedStatement,
                element.publishState
            );
            binderCollection.push(temp);                

            console.log('Loop Batch=>', JSON.stringify(temp));
        }


        return await db.batch(binderCollection);
        
        
    } catch (error:any) {

        console.log('D1 Instance=>', JSON.stringify(db.dump()));

        console.log({
            message: error.message,
            cause: error.cause.message,
        });
        throw(error);
    }
}

async function InsertIntoContentDatabase(db:D1Database, content:ContentDataType): Promise<D1Result<unknown>[]>{

    try {


        if(content.contentDataItemType === undefined){
            console.log('No contentDataItemType');            
            throw('Missing contentDataItemType Array');
        }


        //Because we may have multiple items to push into the database, we need to batch the call
        const batcher = await db.prepare("INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)")

        let binderCollection=[];

        for (let index = 0; index < content.contentDataItemType.length; index++) {
            const element = content.contentDataItemType[index];

            let temp = batcher.bind(
                element.contentGroupRef, 
                element.projectRef,
                element.contentElementRef,
                element.contentBakedStatement,
                element.publishState
            );
            binderCollection.push(temp);                

            console.log('Loop Batch=>', JSON.stringify(temp));
        }


        return await db.batch(binderCollection);
        
        
    } catch (error:any) {

        console.log('D1 Instance=>', JSON.stringify(db.dump()));

        console.log({
            message: error.message,
            cause: error.cause.message,
        });
        throw(error);
    }
}

async function InsertIntoContentDatabase(db:D1Database, content:ContentDataType): Promise<D1Result<unknown>[]>{

    try {


        if(content.contentDataItemType === undefined){
            console.log('No contentDataItemType');            
            throw('Missing contentDataItemType Array');
        }


        //Because we may have multiple items to push into the database, we need to batch the call
        const batcher = await db.prepare("INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)")

        let binderCollection=[];

        for (let index = 0; index < content.contentDataItemType.length; index++) {
            const element = content.contentDataItemType[index];

            let temp = batcher.bind(
                element.contentGroupRef, 
                element.projectRef,
                element.contentElementRef,
                element.contentBakedStatement,
                element.publishState
            );
            binderCollection.push(temp);                

            console.log('Loop Batch=>', JSON.stringify(temp));
        }


        return await db.batch(binderCollection);
        
        
    } catch (error:any) {

        console.log('D1 Instance=>', JSON.stringify(db.dump()));

        console.log({
            message: error.message,
            cause: error.cause.message,
        });
        throw(error);
    }
}

O

Old Man Maker•10/5/23, 3:42 AM

each loop batch has this being pushed:

Loop Batch=> {"database":{"fetcher":{}},"statement":"UPDATE content_models SET contentGroupRef = ?2, projectRef = ?3, contentBakedStatement = ?4, publishState = ?5 WHERE contentElementRef = ?1","params":["ref0","cg0","prTest0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"UPDATE content_models SET contentGroupRef = ?2, projectRef = ?3, contentBakedStatement = ?4, publishState = ?5 WHERE contentElementRef = ?1","params":["ref0","cg0","prTest0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"UPDATE content_models SET contentGroupRef = ?2, projectRef = ?3, contentBakedStatement = ?4, publishState = ?5 WHERE contentElementRef = ?1","params":["ref0","cg0","prTest0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"UPDATE content_models SET contentGroupRef = ?2, projectRef = ?3, contentBakedStatement = ?4, publishState = ?5 WHERE contentElementRef = ?1","params":["ref0","cg0","prTest0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

O

Old Man Maker•10/5/23, 3:43 AM

thats update. this is insert:

Loop Batch=> {"database":{"fetcher":{}},"statement":"INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)","params":["cg0","prTest0","ref0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)","params":["cg0","prTest0","ref0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)","params":["cg0","prTest0","ref0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

Loop Batch=> {"database":{"fetcher":{}},"statement":"INSERT INTO content_models (contentGroupRef, projectRef, contentElementRef, contentBakedStatement, publishState) VALUES (?1, ?2, ?3, ?4, ?5)","params":["cg0","prTest0","ref0","We are proud to present a gorgeous large green wall panel square made from ivory wood.",1]}

O

Old Man Maker•10/5/23, 3:44 AM

The databases fetcher property is showing {}

U

Unsmart•10/5/23, 3:44 AM

the fetcher logging as {} is expected afaik... but can you see if the operations actually go through? This does seem like its probably an internal error but wonder if its when trying to parse the request or the response

O

Old Man Maker•10/5/23, 3:45 AM

they do not go through. Just created another duplicate D1 instance with no data to see if the insert works. No dice

U

Unsmart•10/5/23, 3:46 AM

Hmm yeah thats weird does a normal non batch operation work?

O

Old Man Maker•10/5/23, 3:46 AM

I'll give it shot BRB

O

Old Man Maker•10/5/23, 3:50 AM

No - it failed as well.

Error=> Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1042                                                                                                                                                                                                                                                                                        
    at D1Database._send (cloudflare-internal:d1-api:105:19)                                                                                                                                                                                                                                                                                                         
    at async D1Database.exec (cloudflare-internal:d1-api:41:23)                                                                                                                                                                                                                                                                                                     
    at async InsertIntoContentDatabase (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3232:5)
    at async RunContent (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3155:17)
    at async Array.<anonymous> (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3727:12)
    at async file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21 {
  stack: Error: D1_ERROR: Failed to parse body as JSON, got…ocal/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21,
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Error: Failed to parse body as JSON, got: error code: 1042
    at D1Database._send (cloudflare-inte…
}

Error=> Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1042                                                                                                                                                                                                                                                                                        
    at D1Database._send (cloudflare-internal:d1-api:105:19)                                                                                                                                                                                                                                                                                                         
    at async D1Database.exec (cloudflare-internal:d1-api:41:23)                                                                                                                                                                                                                                                                                                     
    at async InsertIntoContentDatabase (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3232:5)
    at async RunContent (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3155:17)
    at async Array.<anonymous> (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3727:12)
    at async file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21 {
  stack: Error: D1_ERROR: Failed to parse body as JSON, got…ocal/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21,
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Error: Failed to parse body as JSON, got: error code: 1042
    at D1Database._send (cloudflare-inte…
}

Error=> Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1042                                                                                                                                                                                                                                                                                        
    at D1Database._send (cloudflare-internal:d1-api:105:19)                                                                                                                                                                                                                                                                                                         
    at async D1Database.exec (cloudflare-internal:d1-api:41:23)                                                                                                                                                                                                                                                                                                     
    at async InsertIntoContentDatabase (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3232:5)
    at async RunContent (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3155:17)
    at async Array.<anonymous> (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3727:12)
    at async file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21 {
  stack: Error: D1_ERROR: Failed to parse body as JSON, got…ocal/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21,
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Error: Failed to parse body as JSON, got: error code: 1042
    at D1Database._send (cloudflare-inte…
}

Error=> Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1042                                                                                                                                                                                                                                                                                        
    at D1Database._send (cloudflare-internal:d1-api:105:19)                                                                                                                                                                                                                                                                                                         
    at async D1Database.exec (cloudflare-internal:d1-api:41:23)                                                                                                                                                                                                                                                                                                     
    at async InsertIntoContentDatabase (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3232:5)
    at async RunContent (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3155:17)
    at async Array.<anonymous> (file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:3727:12)
    at async file:///C:XXXXX/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21 {
  stack: Error: D1_ERROR: Failed to parse body as JSON, got…ocal/Temp/tmp-35236-4jsc2tuBSi4U/index.js:2408:21,
  message: D1_ERROR: Failed to parse body as JSON, got: error code: 1042,
  cause: Error: Failed to parse body as JSON, got: error code: 1042
    at D1Database._send (cloudflare-inte…
}

O

Old Man Maker•10/5/23, 3:51 AM

funny that it fails with exactly the same error code

U

Unsmart•10/5/23, 3:53 AM

Hmm thats really weird... what wrangler version are you on?

<https://sqlite.org/draft/releaselog/3_44_0.html> > Add support for the JSONB binary representation

Similar Threads

Similar Threads

Similar Threads