Well, yeah, it isn't a perfect system, but it is the best option to do it for free. You can use KV/D

H

And it not being a "general-purpose" cache isn't much of an issue. Open a custom cache that you reserve for D1 Caching, then use bogus URLs

H

Hello, I’m Allie!OP•6/11/24, 9:20 AM

It won't be cached in more than one datacenter, but it is better than no cache at all

H

Hello, I’m Allie!OP•6/11/24, 9:24 AM

Though it would be cool to have an arbitrary-object cache that didn't require wrapping stuff in a Request/Response, especially if it could auto-serialize values like DO storage can

H

Hello, I’m Allie!OP•6/11/24, 9:24 AM

Since that is one thing that you can't do manually unless you bring a serializer yourself(which is kind of heavy-handed)

M

Max (@rozenmd)•6/11/24, 9:32 AM

you could also write an abstraction on top of the cache api so it'd just be

.get(key)

.get(key)

.get(key)

.get(key) and

.put(key,value)

.put(key,value)

.put(key,value)

.put(key,value)

MMax (@rozenmd)you could also write an abstraction on top of the cache api so it'd just be `.ge...

H

Hello, I’m Allie!OP•6/11/24, 9:34 AM

That too

HHello, I’m Allie!Well, yeah, it isn't a perfect system, but it is the best option to do it for fr...

M

maj•6/11/24, 11:32 AM

Yeah, cost. I'm still being cheap, using the free plan to the extreme

. So in my case using KV is more cost effective than running the query on each request, as I easily cross the threshold for daily rows read by running that one query a few times... but I don't need up to date data all the time. Running it twice a day and otherwise read from KV is the most cost effective solution for me, as it's few reads (a few hundred per day) but lots of rows (1.6M per request).

K

kian•6/12/24, 7:52 AM

Run

EXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}'

EXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}'

EXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}'

EXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}' and it'll tell you what it's doing

Ppeshakoo that answers my first question, thanks. Secondly, if I do something like `SELECT...

M

Max (@rozenmd)•6/12/24, 10:56 AM

but generally yes, if the rows_read and rows_written are 0 (might be a bug), you're not getting billed for that query

M

Max (@rozenmd)•6/13/24, 1:08 PM

You can now delete multiple rows at a time in the D1 UI btw

MMax (@rozenmd)You can now delete multiple rows at a time in the D1 UI btw

M

Max (@rozenmd)•6/13/24, 1:09 PM

also when you modify records, the back/cancel buttons now take you back to the page you were just on

D

Disciple of Caoximeihao•6/14/24, 9:12 AM

help

D

Disciple of Caoximeihao•6/14/24, 9:12 AM

D

Disciple of Caoximeihao•6/14/24, 9:12 AM

d1 error in hono insert data

DDisciple of Caoximeihao Click to see attachment

M

Max (@rozenmd)•6/14/24, 10:49 AM

post your query

H

Hannes•6/14/24, 3:04 PM

Hi, I have a very strange behaviour I don't really understand.
When I run a migration adding a column in Table A (by deferring foreign keys ON, creating a new table, copying all data, deleting the old table, renaming the new table to the name of the old table and setting defer foreign keys OFF), all relations of table B to table A (with the id of A in table B) are now

null

null

null

null...
How can I work around this? The migration is generated by Prisma and looks like this:

-- RedefineTables
PRAGMA defer_foreign_keys=ON;
CREATE TABLE "new_Product" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    ...some more rows
    CONSTRAINT "Product_eventId_fkey" FOREIGN KEY ("eventId") REFERENCES "Event" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "Product_totalCapacityId_fkey" FOREIGN KEY ("totalCapacityId") REFERENCES "Capacity" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Product" ("createdAt", "description", "id", "name", "updatedAt", ...) SELECT "createdAt", "description", "id", "name", "updatedAt", ... FROM "Product";
DROP TABLE "Product";
ALTER TABLE "new_Product" RENAME TO "Product";
PRAGMA foreign_key_check("Product");
PRAGMA defer_foreign_keys=OFF;

-- RedefineTables
PRAGMA defer_foreign_keys=ON;
CREATE TABLE "new_Product" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    ...some more rows
    CONSTRAINT "Product_eventId_fkey" FOREIGN KEY ("eventId") REFERENCES "Event" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "Product_totalCapacityId_fkey" FOREIGN KEY ("totalCapacityId") REFERENCES "Capacity" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Product" ("createdAt", "description", "id", "name", "updatedAt", ...) SELECT "createdAt", "description", "id", "name", "updatedAt", ... FROM "Product";
DROP TABLE "Product";
ALTER TABLE "new_Product" RENAME TO "Product";
PRAGMA foreign_key_check("Product");
PRAGMA defer_foreign_keys=OFF;

-- RedefineTables
PRAGMA defer_foreign_keys=ON;
CREATE TABLE "new_Product" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    ...some more rows
    CONSTRAINT "Product_eventId_fkey" FOREIGN KEY ("eventId") REFERENCES "Event" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "Product_totalCapacityId_fkey" FOREIGN KEY ("totalCapacityId") REFERENCES "Capacity" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Product" ("createdAt", "description", "id", "name", "updatedAt", ...) SELECT "createdAt", "description", "id", "name", "updatedAt", ... FROM "Product";
DROP TABLE "Product";
ALTER TABLE "new_Product" RENAME TO "Product";
PRAGMA foreign_key_check("Product");
PRAGMA defer_foreign_keys=OFF;

-- RedefineTables
PRAGMA defer_foreign_keys=ON;
CREATE TABLE "new_Product" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    ...some more rows
    CONSTRAINT "Product_eventId_fkey" FOREIGN KEY ("eventId") REFERENCES "Event" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "Product_totalCapacityId_fkey" FOREIGN KEY ("totalCapacityId") REFERENCES "Capacity" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Product" ("createdAt", "description", "id", "name", "updatedAt", ...) SELECT "createdAt", "description", "id", "name", "updatedAt", ... FROM "Product";
DROP TABLE "Product";
ALTER TABLE "new_Product" RENAME TO "Product";
PRAGMA foreign_key_check("Product");
PRAGMA defer_foreign_keys=OFF;

Any idea? Thanks in advance

HHannes Hi, I have a very strange behaviour I don't really understand. When I run a migr...

H

Hannes•6/14/24, 3:08 PM

Oh - probably this is due a ON DELETE SET NULL. Is there a way to work around that in the migration?
However, I wonder if that is the expected behaviour...

S

SonyC•6/14/24, 4:05 PM

hey guys, good day to all, I'm trying to alter a table and modify a table row like this:

wrangler d1 execute db-name --command "ALTER TABLE items MODIFY COLUMN user_id INT NOT NULL;"

wrangler d1 execute db-name --command "ALTER TABLE items MODIFY COLUMN user_id INT NOT NULL;"

wrangler d1 execute db-name --command "ALTER TABLE items MODIFY COLUMN user_id INT NOT NULL;"

wrangler d1 execute db-name --command "ALTER TABLE items MODIFY COLUMN user_id INT NOT NULL;"

but I'm getting a syntax error so I asume MODIFY is not supported, HELP!

K

kian•6/14/24, 4:25 PM

D1 is SQLite which doesn't have

MODIFY

MODIFY

MODIFY

MODIFY - https://www.sqlite.org/lang_altertable.html

S

SonyC•6/14/24, 4:39 PM

ty!

HHannes Oh - probably this is due a ON DELETE SET NULL. Is there a way to work around th...

H

Hannes•6/14/24, 7:26 PM

Actually, it seems like D1 behaves different here than SQLite. In SQLite, those actions are not triggered when using

PRAGMA foreign_keys=OFF;

PRAGMA foreign_keys=OFF;

PRAGMA foreign_keys=OFF;

PRAGMA foreign_keys=OFF;. In D1, however, with

PRAGMA defer_foreign_keys=ON;

PRAGMA defer_foreign_keys=ON;

PRAGMA defer_foreign_keys=ON;

PRAGMA defer_foreign_keys=ON;, the referential actions still run. That happens in Miniflare and in remote D1...

HHannes Actually, it seems like D1 behaves different here than SQLite. In SQLite, those ...

H

Hannes•6/14/24, 7:48 PM

Here's a minimal reproduction: https://github.com/hrueger/prisma-24540 (not really prisma specific).

GitHub

GitHub - hrueger/prisma-24540

Contribute to hrueger/prisma-24540 development by creating an account on GitHub.

HHannes Here's a minimal reproduction: https://github.com/hrueger/prisma-24540 (not real...

H

Hannes•6/14/24, 8:10 PM

For completeness: I just found the GitHub Issue of that bug: https://github.com/cloudflare/workers-sdk/issues/5438
I'd love to see this fixed, I almost lost staging (luckily not productiond) data due to this today.

GitHub

🚀 Feature Request: Allow turning off foreign key check while migrat...

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column t...

Z

Zillo Beast•6/15/24, 5:32 AM

I created a simple library for D1 in python ... https://github.com/Suleman-Elahi/D1py

GitHub

GitHub - Suleman-Elahi/D1py: A very simple wrapper for Cloudflare D...

A very simple wrapper for Cloudflare D1 Databases' REST API in using Python for Python xD - Suleman-Elahi/D1py

N

Nob•6/15/24, 7:04 PM

Hi it seem like there is a small mistake with "Databas"

W

Walshy•6/15/24, 7:13 PM

got a fix up: https://github.com/cloudflare/workers-sdk/pull/6062

WWalshy got a fix up: <https://github.com/cloudflare/workers-sdk/pull/6062>

N

Nob•6/15/24, 7:23 PM

is d1 replicated yet ?

N

Nob•6/15/24, 7:23 PM

or its still one single primary db atm

NNob or its still one single primary db atm

H

Hello, I’m Allie!OP•6/15/24, 7:31 PM

One DB Instance

N

notjoemartinez•6/15/24, 10:02 PM

I'm having trouble understanding my d1 bill. If it's a formatting problem and the number is

55,835,335.95

55,835,335.95

55,835,335.95

55,835,335.95 or

558,353,359.5

558,353,359.5

558,353,359.5

558,353,359.5 how do I read a fraction of a row? Also if first 25 billion included rows is included is the real number

25 billion

25 billion

25 billion

25 billion +

55,835,335.95

55,835,335.95

55,835,335.95

55,835,335.95 ? What's the number under the

55,835,335,95

55,835,335,95

55,835,335,95

55,835,335,95 , does that mean I uses

55,835,335,95 x 3

55,835,335,95 x 3

55,835,335,95 x 3

55,835,335,95 x 3 ?

Nnotjoemartinez I'm having trouble understanding my d1 bill. If it's a formatting problem and th...

H

Hello, I’m Allie!OP•6/15/24, 10:06 PM

That looks like 55,835,335,953 to me

H

Hello, I’m Allie!OP•6/15/24, 10:06 PM

It appears the 3 got wrapped to the next line because it ran out of space

N

notjoemartinez•6/15/24, 10:07 PM

Okay but would it still be

25 billion + 55,835,335,953

25 billion + 55,835,335,953

25 billion + 55,835,335,953

25 billion + 55,835,335,953 ?

W

Walshy•6/15/24, 10:08 PM

25 bil is included in the number, so it's actually 30 billion billable rows

N

notjoemartinez•6/15/24, 10:08 PM

wait that's 55 billion

Nnotjoemartinez wait that's 55 billion

H

Hello, I’m Allie!OP•6/15/24, 10:11 PM

H

Hello, I’m Allie!OP•6/15/24, 10:13 PM

Or https://www.wolframalpha.com/input?i=Absolute+Value+of+%28%2855835335953+minus+25+billion%29+times+0.001+per+million%29

Absolute Value of ((55835335953 minus 25 billion) times 0.001 per m...

Wolfram|Alpha brings expert-level knowledge and capabilities to the broadest possible range of people—spanning all professions and education levels.

N

notjoemartinez•6/15/24, 10:14 PM

thanks for the clarification! so if I have a full text search table with a million rows, does that mean every time I run a search I'm using a million reads?

N

notjoemartinez•6/15/24, 10:14 PM

Ppeshakoo to my knowledge, unless you filter by primary key or use [indexes](https://devel...

N

notjoemartinez•6/15/24, 11:03 PM

Doesn't sqlite fts add indexes by default? This is how I'm making the schema:

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

Nnotjoemartinez Doesn't sqlite fts add indexes by default? This is how I'm making the schema: ``...

I

Isaac McFadyen•6/15/24, 11:06 PM

So kinda. Depends on your queries.

I

Isaac McFadyen•6/15/24, 11:06 PM

The table you'll need to add indexes on yourself.

Well, yeah, it isn't a perfect system, but it is the best option to do it for free. You can use KV/D

Similar Threads

Similar Threads

Similar Threads