https://canary.discord.com/channels/595317990191398933/992060581832032316/1237957306336149635 Intere

IIsaac McFadyen https://canary.discord.com/channels/595317990191398933/992060581832032316/123795...

M

I’m linking to comments (old, but still correct) from the core maintainer and you can find many SO answers confirming the same. I can’t speak to why Glen said otherwise (aka “show me the code”)

MMatt Silverlock I’m linking to comments (old, but still correct) from the core maintainer and yo...

I

Isaac McFadyenOP•5/16/24, 3:40 AM

I just spun up a test DB and I seem to see the opposite per this Stack Overflow: https://stackoverflow.com/questions/18807304/is-count-constant-time-in-sqlite-and-if-not-what-are-alternatives (edit: had the wrong link)

I

Isaac McFadyenOP•5/16/24, 3:41 AM

The some_string is not indexed, but there is an index on another column in the table.

I

Isaac McFadyenOP•5/16/24, 3:41 AM

The some_string COUNT takes 4s whereas the * count (which uses the index as you can see from the query planner there,

USING COVERING INDEX test_index

USING COVERING INDEX test_index

USING COVERING INDEX test_index

USING COVERING INDEX test_index) takes 0.3s

I

Isaac McFadyenOP•5/16/24, 3:41 AM

This is in a 3.13GB DB

IIsaac McFadyen The some_string COUNT takes 4s whereas the * count (which uses the index as you ...

M

Matt Silverlock•5/16/24, 3:44 AM

Does it scan every row or not, though? That’s what matters here.

MMatt Silverlock Does it scan every _row_ or not, though? That’s what matters here.

I

Isaac McFadyenOP•5/16/24, 3:45 AM

I'm assuming not given that the index is queried in 11x less time than the scan of the table?

I

Isaac McFadyenOP•5/16/24, 3:46 AM

Not sure why else it would take that much less time but maybe there's some reason that it would and still read every row somehow.

IIsaac McFadyen I'm assuming not given that the index is queried in 11x less time than the scan ...

M

Matt Silverlock•5/16/24, 3:46 AM

Scanning fewer columns than

. I’m not at a computer but try it with D1 (or libsql) so you can see the row counts. Time isn’t accurate.

M

Matt Silverlock•5/16/24, 3:49 AM

(I would LOVE to wrong here but when we tested this and looked at the code a few months ago - the SQLite mailing list stands true)

C

Christer•5/16/24, 7:26 AM

Hi there! Is there a way to rerun migrations? I'm still doing a lot of development and want to drop a re-create my db.

J

jkomyno•5/16/24, 11:46 AM

Hi, I think I have encountered a behavioral difference between SQLite and D1. I'm only using supported commands and PRAGMAs. Here goes:
- I start with the following schema

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

- I then apply the following migration

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

This migration works on standard SQLite, but fails with

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User" on D1. Any help, please?

Jjkomyno Hi, I think I have encountered a behavioral difference between SQLite and D1. I'...

H

harshil1712•5/16/24, 12:44 PM

Hey, it seems like the issue is with the way you have ordered the statements. You are referencing the

USER

USER

USER

USER table, even before it gets modified. Something like the below should work:

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

Hharshil1712 Hey, it seems like the issue is with the way you have ordered the statements. Yo...

J

jkomyno•5/16/24, 1:06 PM

Hi, thanks for the answer. You're right, what you provided works. My question is: why does my snippet only fail with D1, and not with SQLite? It seems to be that there are subtle differences in the way foreign keys are enforced / and or their checks are deferred within migrations?

Jjkomyno Hi, thanks for the answer. You're right, what you provided works. My question is...

H

harshil1712•5/16/24, 2:05 PM

That's a question, I don't have an answer for right now. I have joined recently, and still learning!

Hharshil1712 That's a question, I don't have an answer for right now. I have joined recently,...

J

jkomyno•5/16/24, 2:46 PM

Thanks for the transparency and for the time, regardless. Perhaps Matt may know more about this?

Jjkomyno Thanks for the transparency and for the time, regardless. Perhaps Matt may know ...

M

Matt Silverlock•5/16/24, 3:32 PM

D1 doesn’t allow you to turn off foreign keys, so

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off is a no-op (per SQLite spec). The

defer

defer

defer

defer works, as you can see.

AAnay-208 does cloudflare has any other database like d1, which supports higher storage? A...

Z

zwily•5/16/24, 5:16 PM

No, but they partner with people like Neon for Saas dbs

Z

zwily•5/16/24, 5:16 PM

Is drizzle still people's favorite for typed querying with D1 today?

AAnay-208 does cloudflare has any other database like d1, which supports higher storage? A...

M

Max (@rozenmd)•5/16/24, 6:23 PM

Hyperdrive + BYO Postgres DB

F

Fabian•5/16/24, 8:01 PM

When would it be the case that rows_written != changes?
I have a simple insert query and Miniflare (imo) incorrectly reports 2 rows written here

FFabian When would it be the case that rows_written != changes? I have a simple insert q...

M

Max (@rozenmd)•5/16/24, 8:13 PM

an index counts as a row written

F

Fabian•5/16/24, 8:23 PM

ah ok, that makes sense. Probably overread that somewhere in the docs. So what I would probably be looking for if I want the "real" amount of created/updated rows is changes, right?

J

jepp•5/16/24, 8:43 PM

Hi is there a way to upload a sqlite DB via API?

J

jepp•5/16/24, 8:45 PM

Ok. Found out how it works. Nevermind

R

roftt•5/17/24, 6:28 AM

Is it expected that this batch operation makes 100 write queries, not 1 (displayed in Dashboard's DB Metrics)?

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

MMatt Silverlock Indexes don’t store row counts. It’s not possible to do a `count` and get one ro...

M

Marcel Overdijk•5/17/24, 8:36 AM

then the ealier comment on this channel was incorrect I assume?

Rroftt Is it expected that this batch operation makes 100 write queries, not 1 (display...

I

Isaac McFadyenOP•5/17/24, 1:47 PM

It's not billed on queries but rows. You are inserting 100 rows (writes) so you'll be billed for 100 writes regardless of how many queries you split it into.

I

Isaac McFadyenOP•5/17/24, 1:50 PM

Ah, think I misunderstood. Are you referring to D1 analytics or billing?

IIsaac McFadyen It's not billed on queries but rows. You are inserting 100 rows (writes) so you'...

R

roftt•5/17/24, 1:50 PM

Yes, I'm aware of pricing by rows, thank you. I was just checking if it's a bug since I thought the batch operation should be executed in one query.

I

Isaac McFadyenOP•5/17/24, 1:51 PM

As far as I know a D1 batch is just a bunch of statements that are wrapped in a transaction for you by the D1 engine internally.

I

Isaac McFadyenOP•5/17/24, 1:51 PM

So it'll still be more than 1 query if you have more than 1 item in the batch.

M

Max (@rozenmd)•5/17/24, 2:41 PM

batch just saves you network trips from the Worker to D1

K

kindaraffy•5/19/24, 2:30 PM

Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it will sometimes (around 1/8 times) take a really long time (~ 5-10 secs) to execute the query.

I was blaming my SQL queries at the start, but I just saw this happen on a

SELECT * FROM <table>

SELECT * FROM <table>

query against a table with 4 rows from the D1 dashboard console.

I've noticed this occurring from the start of this day. I've attached a screenshot of my network health from

speed.cloudflare.com

speed.cloudflare.com

. I'm in Melb, Aus.

K

kindaraffy•5/19/24, 2:43 PM

K

kindaraffy•5/19/24, 2:44 PM

D1 returns a result at around

0:28

0:28

Kkindaraffy Is anyone else experience degraded D1 performance when querying data? I have a 6...

M

Max (@rozenmd)•5/19/24, 7:05 PM

whats your databaseId?

MMax (@rozenmd)whats your databaseId?

K

kindaraffy•5/19/24, 9:04 PM

Hi Max, thank you for looking into this :). The database id is

bfd358ee-8478-4a08-a37d-75dcabbf231b

bfd358ee-8478-4a08-a37d-75dcabbf231b

N

notjoemartinez•5/20/24, 5:48 AM

Is there a way run a worker locally while it talks to a remote d1-database? The get-started tutorial only shows you how to develop local db then creates a separate db for remote usage. I'd like to be able to query the remote db from a local dev environment.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

H

Hello, I’m Allie!•5/20/24, 5:56 AM

Not at the moment. Either you run the Worker locally, or you run it remotely, and get access to a real D1 instance

N

notjoemartinez•5/20/24, 6:00 AM

~~darn back to testing in prod~~ I guess I could just run a script to dump remote into a local db when testing. I'm not sure how that's going to scale when I enable FTS and virtual tables though. Would be intrested to see how other people handle this.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

J

James Flores•5/20/24, 7:26 AM

Use the API instead and you should be able to

JJames Flores Use the API instead and you should be able to

H

Hello, I’m Allie!•5/20/24, 7:30 AM

This can work, though note first that this requires an API wrapper around the normal D1 API, it doesn't support the same operations as the normal D1 API, and will in most cases perform worse than a real bound D1 database

HHello, I’m Allie!This can work, though note first that this requires an API wrapper around the no...

J

James Flores•5/20/24, 7:31 AM

Thanks for that info. I couldn’t find any JS examples for React and D1 so I ended up using the API.

JJames Flores Thanks for that info. I couldn’t find any JS examples for React and D1 so I ende...

H

Hello, I’m Allie!•5/20/24, 7:46 AM

I generally wouldn’t recommend using D1, or any Cloudflare API directly from the frontend

H

Hello, I’m Allie!•5/20/24, 7:47 AM

Other than direct upload stuff for R2/Stream/Calls etc

HHello, I’m Allie!I generally wouldn’t recommend using D1, or any Cloudflare API directly from the...