@Max (@rozenmd) @Max (@rozenmd) @kian

KKisa @Max (@rozenmd) @Max (@rozenmd) @kian

M

Matt Silverlock•2/27/24, 6:47 AM

Please don’t ping people directly like this.

B

Beyondo•2/27/24, 11:53 AM

Does this query consume 2 writes, 1 read 1 write, or only 1 write?

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

Assuming the row already exists in the table?

B

Beyondo•2/27/24, 11:56 AM

And what about this one:

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Original message was deleted

B

Beyondo•2/27/24, 12:01 PM

Yeah, just AFK at the moment but was really curious so asked here)) Would make for a useful ORM

.set()

.set()

.set()

.set() method if one takes only 1 write.

B

Beyondo•2/27/24, 12:01 PM

Well, when I'm back ig.

BBeyondo Yeah, just AFK at the moment but was really curious so asked here)) Would make f...

M

Max (@rozenmd)•2/27/24, 12:54 PM

not to bikeshed your ORM but i think others call that

.upsert()

.upsert()

.upsert()

.upsert()

MMax (@rozenmd)not to bikeshed your ORM but i think others call that `.upsert()`

B

Beyondo•2/27/24, 1:00 PM

Sorry for not giving enough context, I'm building an ORM with an optional document-style interface, so my .set() is actually part of my DocumentReference class, not of the database itself

M

Max (@rozenmd)•2/27/24, 1:01 PM

ah

E

Epailes•2/27/24, 4:11 PM

I could swear I saw a comment from a dev/rep around timeline when being able to dynamically create/bind d1 instances (properly, not a hack with the API), and improvements to migrations on d1 instances, but can't find it for the life of me searching on discord - I could be misremembering!

Can anyone confirm if that is planned for GA? Or if that's likely to be :soontm:

EEpailes I could swear I saw a comment from a dev/rep around timeline when being able to ...

V

Vignesh•2/27/24, 5:02 PM

I was also interested in using it in production, but as it doesn't have dynamic binding and due to storage limits, I am using Postgresql.

EEpailes I could swear I saw a comment from a dev/rep around timeline when being able to ...

M

Max (@rozenmd)•2/27/24, 5:31 PM

we don't normally give ETAs - but yeah its coming

H

haz•2/27/24, 5:31 PM

Hi guys, I am trying to use cloudflare d1 database using https://knexjs.org/

I am currently reading this https://github.com/knex/knex/blob/master/CONTRIBUTING.md#i-would-like-to-add-support-for-new-dialect-to-knex-is-it-possible Any help is appreciated. Especially if someone has already managed to do it

SQL Query Builder for Javascript | Knex.js

GitHub

knex/CONTRIBUTING.md at master · knex/knex

A query builder for PostgreSQL, MySQL, CockroachDB, SQL Server, SQLite3 and Oracle, designed to be flexible, portable, and fun to use. - knex/knex

Hhaz Hi guys, I am trying to use cloudflare d1 database using https://knexjs.org/ - I...

M

Max (@rozenmd)•2/27/24, 5:33 PM

i don't know of anyone using knexjs with d1 (it's one of my favourite ORMs so i'm personally curious too) - most folks use drizzle: https://orm.drizzle.team/

Drizzle ORM - next gen TypeScript ORM.

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

H

haz•2/27/24, 5:34 PM

The reason why I want to use knex is because Directus uses knex js and I am thinking, it would be amazing to deploy Directus with CloudFlare D1

EEpailes I could swear I saw a comment from a dev/rep around timeline when being able to ...

U

Unsmart•2/27/24, 5:37 PM

Read replicas, Backups, Larger DB Storage, and Dynamic Bindings have all been said to be coming by GA from employees.

Read replicas: https://blog.cloudflare.com/d1-open-beta-is-here/

Backups: d1-database

Larger DB Storage: d1-database

Dynamic bindings: d1-database

UUnsmart Read replicas, Backups, Larger DB Storage, and Dynamic Bindings have all been sa...

M

Max (@rozenmd)•2/27/24, 5:44 PM

Bear in mind that the linked comments are not promises - "we'd like to"/"it's planned" !== coming by date X with certainty

U

Unsmart•2/27/24, 5:45 PM

Dynamic bindings did say "will be part of/ahead of GA"

U

Unsmart•2/27/24, 5:46 PM

but yes

UUnsmart Dynamic bindings did say "will be part of/ahead of GA" 😛

M

Matt Silverlock•2/27/24, 5:49 PM

Still not promises. Team is working on shipping a lot, and we want to be transparent with timelines, but these are not guarantees.

MMatt Silverlock Still not promises. Team is working on shipping a lot, and we want to be transpa...

E

Epailes•2/27/24, 7:06 PM

Good to know it's at least up there on the priority list even if it doesn't end up making GA!

UUnsmart Read replicas, Backups, Larger DB Storage, and Dynamic Bindings have all been sa...

B

bun•2/27/24, 9:55 PM

what does GA mean?

Bbun what does GA mean?

U

Unsmart•2/27/24, 9:55 PM

generally available aka production ready

B

bun•2/27/24, 9:56 PM

so they just mean that those features will be available once its production ready ? like not in beta?

U

Unsmart•2/27/24, 9:56 PM

Yes, and its more of a plan to have them so it might be ready might be after

B

bun•2/27/24, 9:57 PM

ok but why does this not work

await this.db.batch([
      this.db.insert(blobs).values({ hash: blobHash }),
      this.db
        .insert(assets)
        .values({ projectId, blobId: sql`last_insert_rowid()` })
        .returning({ assetId: assets.id }),
    ]);

await this.db.batch([
      this.db.insert(blobs).values({ hash: blobHash }),
      this.db
        .insert(assets)
        .values({ projectId, blobId: sql`last_insert_rowid()` })
        .returning({ assetId: assets.id }),
    ]);

await this.db.batch([
      this.db.insert(blobs).values({ hash: blobHash }),
      this.db
        .insert(assets)
        .values({ projectId, blobId: sql`last_insert_rowid()` })
        .returning({ assetId: assets.id }),
    ]);

await this.db.batch([
      this.db.insert(blobs).values({ hash: blobHash }),
      this.db
        .insert(assets)
        .values({ projectId, blobId: sql`last_insert_rowid()` })
        .returning({ assetId: assets.id }),
    ]);

    [cause]: Error: FOREIGN KEY constraint failed

[cause]: Error: FOREIGN KEY constraint failed

    [cause]: Error: FOREIGN KEY constraint failed

[cause]: Error: FOREIGN KEY constraint failed
like what

U

Unsmart•2/27/24, 9:57 PM

No clue I dont use SQL

B

bun•2/27/24, 9:57 PM

what do you use then

U

Unsmart•2/27/24, 9:57 PM

On cloudflare I use durable objects :NODDER:

B

bun•2/27/24, 10:00 PM

idk i couuldnt ever get used to workin with durable objects. they seem great at first but even for a simple key value store i have to write a class? like what

B

bun•2/27/24, 10:01 PM

yeah its probably the

blobId

blobId

blobId

blobId, but shouldnt

last_insert_rowid()

last_insert_rowid()

last_insert_rowid()

last_insert_rowid() return the

id

id

of the latest inserted

blob

blob

blob

blob ? as in the first batch statement?

B

bun•2/27/24, 10:02 PM

B

bun•2/27/24, 10:03 PM

do you prefer raw sql ?

B

bun•2/27/24, 10:03 PM

CREATE TABLE `assets` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `ref_count` integer DEFAULT 1 NOT NULL,
    `blob_id` integer NOT NULL,
    `user_id` integer NOT NULL,
    `created_at` integer DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    FOREIGN KEY (`blob_id`) REFERENCES `blobs`(`id`) ON UPDATE cascade ON DELETE restrict,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE cascade ON DELETE restrict
);

CREATE TABLE `blobs` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `hash` text NOT NULL
);

CREATE TABLE `assets` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `ref_count` integer DEFAULT 1 NOT NULL,
    `blob_id` integer NOT NULL,
    `user_id` integer NOT NULL,
    `created_at` integer DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    FOREIGN KEY (`blob_id`) REFERENCES `blobs`(`id`) ON UPDATE cascade ON DELETE restrict,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE cascade ON DELETE restrict
);

CREATE TABLE `blobs` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `hash` text NOT NULL
);

CREATE TABLE `assets` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `ref_count` integer DEFAULT 1 NOT NULL,
    `blob_id` integer NOT NULL,
    `user_id` integer NOT NULL,
    `created_at` integer DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    FOREIGN KEY (`blob_id`) REFERENCES `blobs`(`id`) ON UPDATE cascade ON DELETE restrict,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE cascade ON DELETE restrict
);

CREATE TABLE `blobs` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `hash` text NOT NULL
);

CREATE TABLE `assets` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `ref_count` integer DEFAULT 1 NOT NULL,
    `blob_id` integer NOT NULL,
    `user_id` integer NOT NULL,
    `created_at` integer DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    FOREIGN KEY (`blob_id`) REFERENCES `blobs`(`id`) ON UPDATE cascade ON DELETE restrict,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE cascade ON DELETE restrict
);

CREATE TABLE `blobs` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `hash` text NOT NULL
);

B

bun•2/27/24, 10:06 PM

hm

B

bun•2/27/24, 10:07 PM

ah ye. i renamed the column to project_id without changing the reference. i will fix that and see if the issue's still thare

B

Beyondo•2/28/24, 2:33 PM

Hello! Is it an expected behavior for an UPDATE query to always take 1 rows_read?

BBeyondo Hello! Is it an expected behavior for an UPDATE query to always take 1 rows_read...

M

Max (@rozenmd)•2/28/24, 3:05 PM

Is it just an UPDATE, or perhaps an

UPDATE ... RETURNING *

UPDATE ... RETURNING *

UPDATE ... RETURNING *

UPDATE ... RETURNING *?

M

Mongy•2/28/24, 3:45 PM

Anyone here have made API calls to a D1 instance to query the db?

The error im receiving:

The request is malformed: failed to decode id

The request is malformed: failed to decode id

The request is malformed: failed to decode id

The request is malformed: failed to decode id

url:

https://api.cloudflare.com/client/v4/accounts/{account_id}/d1/database/{db_id}/query

https://api.cloudflare.com/client/v4/accounts/{account_id}/d1/database/{db_id}/query

https://api.cloudflare.com/client/v4/accounts/{account_id}/d1/database/{db_id}/query

https://api.cloudflare.com/client/v4/accounts/{account_id}/d1/database/{db_id}/query

const insertStatement = `INSERT INTO table (column1, column2) VALUES ('value1', 'value2');`;

const insertStatement = `INSERT INTO table (column1, column2) VALUES ('value1', 'value2');`;

const insertStatement = `INSERT INTO table (column1, column2) VALUES ('value1', 'value2');`;

const insertStatement = `INSERT INTO table (column1, column2) VALUES ('value1', 'value2');`;

const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: 'Bearer {api_token}'
    },
    body: `{"sql": ${insertStatement}`
  }

const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: 'Bearer {api_token}'
    },
    body: `{"sql": ${insertStatement}`
  }

const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: 'Bearer {api_token}'
    },
    body: `{"sql": ${insertStatement}`
  }

const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: 'Bearer {api_token}'
    },
    body: `{"sql": ${insertStatement}`
  }

I'm not exactly where to go from here unfortunately. Any assistance would be greatly appreciated.

K

kian•2/28/24, 3:46 PM

Are you sure

db_id

db_id

db_id

db_id is right?

K

kian•2/28/24, 3:46 PM

You're also missing a closing bracket in your

body

body

from the looks of it

M

Mongy•2/28/24, 3:46 PM

copied straight from the d1 instance

K

kian•2/28/24, 3:47 PM

the

Authorization

Authorization

Authorization

Authorization header also doesn't seem to be actually interpolating the

api_token

api_token

api_token

api_token variable

M

Mongy•2/28/24, 3:48 PM

ive tried using

X-Auth-Key

X-Auth-Key

X-Auth-Key

X-Auth-Key but i get an error that it doesn't exist in the request

Kkian You're also missing a closing bracket in your `body` from the looks of it

M

Mongy•2/28/24, 3:48 PM

that's embarresing. Added the bracket but the error still persists

K

kian•2/28/24, 3:48 PM

It's probably an issue with the URL path or the API token

K

kian•2/28/24, 3:49 PM

What did you put in for

account_id

account_id

account_id

account_id and

db_id

db_id

db_id

db_id? They're not sensitive.

M

Mongy•2/28/24, 3:49 PM

Account id:

4f6d1970bc967b2c2a90b643bbb5ac94

4f6d1970bc967b2c2a90b643bbb5ac94

4f6d1970bc967b2c2a90b643bbb5ac94

4f6d1970bc967b2c2a90b643bbb5ac94
db id:

0e35121-f3cc-4e24-b5c1-4d758860a69d

0e35121-f3cc-4e24-b5c1-4d758860a69d

0e35121-f3cc-4e24-b5c1-4d758860a69d

0e35121-f3cc-4e24-b5c1-4d758860a69d

M

Mongy•2/28/24, 3:50 PM

permissions for api token has read and write for D1

K

kian•2/28/24, 3:50 PM

Does the URL look correct if you

console.log

console.log

console.log

console.log it?

M

Mongy•2/28/24, 3:52 PM

looks good

https://api.cloudflare.com/client/v4/accounts/4f6d1970bc967b2c2a90b643bbb5ac94/d1/database/0e35121-f3cc-4e24-b5c1-4d758860a69d/query

https://api.cloudflare.com/client/v4/accounts/4f6d1970bc967b2c2a90b643bbb5ac94/d1/database/0e35121-f3cc-4e24-b5c1-4d758860a69d/query

https://api.cloudflare.com/client/v4/accounts/4f6d1970bc967b2c2a90b643bbb5ac94/d1/database/0e35121-f3cc-4e24-b5c1-4d758860a69d/query

https://api.cloudflare.com/client/v4/accounts/4f6d1970bc967b2c2a90b643bbb5ac94/d1/database/0e35121-f3cc-4e24-b5c1-4d758860a69d/query

@Max (@rozenmd) @Max (@rozenmd) @kian

Similar Threads

Similar Threads

Similar Threads