I am not sure to understand the purpose of the D1 database, the 10GB limit seems rather low for a se

MMartial I am not sure to understand the purpose of the D1 database, the 10GB limit seems...

I

IEatBeans•5/13/24, 3:10 AM

You are supposed to use many databases. I think you can have like thousands bound to a worker

M

MetalGear•5/14/24, 4:33 AM

I am building a hotel booking system. I am building the backend with cloudflare workers and wondering if my sql database should be D1 or supabase postgres. I would prefer to stay within cloudflare ecosystem but saw some older posts people reporting serious latency. Today is D1 ready? Thinking to have a D1 per tenant to store their bookings and etc. What do I do as I get closer to 10gb limit? Offload to planetscale?

Oom Per tenant. 🤔. Think about it. 10gb is a lot.. i realised it later. You can sto...

M

MetalGear•5/14/24, 2:16 PM

Wow please stop ur getting me too excited

what is your typical latency calling d1 from a worker by binding

M

MetalGear•5/14/24, 2:18 PM

Also do you recommend drizzzle orm

M

MetalGear•5/14/24, 2:19 PM

Wow nice okay

M

MetalGear•5/14/24, 2:19 PM

At this point cloudflare workers can literally do everything

M

MetalGear•5/14/24, 2:20 PM

I was going to build with nest js typical server but really not trying to manage all that. Cloudflare really can’t make it any easier

Oom Per tenant. 🤔. Think about it. 10gb is a lot.. i realised it later. You can sto...

J

Jadugar_Jaggu•5/14/24, 5:08 PM

10GB ? in docs it says 5 GB storage.

https://developers.cloudflare.com/d1/platform/pricing/

Am I missing something?

Cloudflare Docs

Pricing · Cloudflare D1 docs

D1 bills based on:

J

Jadugar_Jaggu•5/14/24, 5:12 PM

Great, looks like they forgot to update the 5 to 10 GB on the pricing page

JJadugar_Jaggu Great, looks like they forgot to update the 5 to 10 GB on the pricing page

C

Chaika•5/14/24, 5:31 PM

no, you just get 5gb for free on free plan and 5gb included on paid plan

C

Chaika•5/14/24, 5:31 PM

unless I'm missing something else you're talking about

C

Chaika•5/14/24, 5:32 PM

The free limit is 5 GB for free, spread across 10 max DBs with 500 mb max per each

CChaika unless I'm missing something else you're talking about

J

Jadugar_Jaggu•5/14/24, 5:32 PM

Yea I got it. I was confused with limits and pricing

M

matt•5/15/24, 4:28 AM

Are there any updates to a lot of the D1 features mentioned during developer week?

Mmatt Are there any updates to a lot of the D1 features mentioned during developer wee...

V

Vy•5/15/24, 2:46 PM

we're deep into read replication, targetted for Q3

M

Marcel Overdijk•5/15/24, 6:42 PM

Would it be an idea to create a

d1-database

d1-database

d1-database

d1-database help channel which support threads? So answered topics can be archived.
I'm asking as questions/discussions in this channel are not really trackable...

M

Marcel Overdijk•5/15/24, 6:43 PM

E.g. the disucssion I had about the

rows_read

rows_read

in case of counts is completely snowed under...

M

Marcel Overdijk•5/15/24, 6:43 PM

Or should I create an issue somewhere else for this?

MMarcel Overdijk I assume 174 + 1 as it accesses the index? But anyway, I cannot seem to get coun...

M

Matt Silverlock•5/15/24, 9:44 PM

Indexes don’t store row counts. It’s not possible to do a

count

count

and get one row read back in SQLite (the query engine D1 uses). Keep in mind you also get 25 billion reads included in the paid plan

MMarcel Overdijk I assume 174 + 1 as it accesses the index? But anyway, I cannot seem to get coun...

M

Matt Silverlock•5/15/24, 9:46 PM

https://marc.info/?l=sqlite-users&m=112679232205850

J

johtso•5/15/24, 11:43 PM

I'm getting a "D1_ERROR: no such table" error when running my worker locally. I've applied my migrations and have checked the table exists using "PRAGMA table_list;". The database id is definitely the same as the one that wrangler says it's binding to. Is there anything else I can check?

J

johtso•5/15/24, 11:49 PM

when I try to list the tables from my worker there seem to be none at all (just _cf_KV, sqlite_schema and sqlite_temp_schema)

J

johtso•5/15/24, 11:52 PM

oh wait.. I have a monorepo and the database schema is in a different package

J

johtso•5/15/24, 11:52 PM

that means when I ran my migrations locally, they were applied to the d1 database stored in that package's directory

J

johtso•5/15/24, 11:52 PM

but the one in my workers directory is empty

J

johtso•5/15/24, 11:53 PM

not sure what I'm supposed to be doing here

J

johtso•5/15/24, 11:58 PM

and now I bump into this issue https://github.com/cloudflare/workers-sdk/issues/4713

GitHub

🐛 BUG: D1 migrations/executions with multiple statements & quote ma...

Which Cloudflare product(s) does this pertain to? D1 What version(s) of the tool(s) are you using? 3.22.3 [Wrangler] What version of Node are you using? 20.9.0 What operating system are you using? ...

J

johtso•5/16/24, 12:22 AM

ok, so after deleting the multi-line comment from the migration I've been able to apply it to my remote database

J

johtso•5/16/24, 12:22 AM

now I can run wrangler pointing to that database and everything's working

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

I

Isaac McFadyen•5/16/24, 3:00 AM

https://canary.discord.com/channels/595317990191398933/992060581832032316/1237957306336149635
Interesting, that's the opposite of when Glen said here.

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

M

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

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 McFadyen•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 McFadyen•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 McFadyen•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 McFadyen•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 McFadyen•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 McFadyen•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?

I am not sure to understand the purpose of the D1 database, the 10GB limit seems rather low for a se

Similar Threads

Similar Threads

Similar Threads