M
BMM
pivot or crosstab queries, calendar querying (for the type of data I'm dealing with) is very complex. I'd like to try a different approach. One column per day per resource with the value of the availability stored as a tinyint.
QSELECT id FROM projects WHERE url = ? then SELECT something FROM metadata WHERE id = ? using that ID (where there could be multiple somethings in the latter table).
AA
I
D
P
몰
HP
JJ
UJ
M
JMUMMThe Cache API can be thought of as an ephemeral key-value store, whereby the Request object (or more specifically, the request URL) is the key, and the Response is the value.
MMHMHHHHHM
.get(key) and .put(key,value)M
. 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).
KEXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}' and it'll tell you what it's doingMM
M
DD
DM
H
nullpivotcrosstabtinyintSELECT id FROM projects WHERE url = ?SELECT something FROM metadata WHERE id = ?something.get(key).put(key,value)EXPLAIN QUERY PLAN SELECT * FROM objects WHERE object = '${key}'To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
Error: D1_ERROR: not authorized
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)export const user = sqliteTable('user', {
id: integer('id').notNull().primaryKey(),
avatarId: text('avatar_id', { mode: 'text', length: 255 }),
});export const user = sqliteTable('user', {
id: integer('id').notNull().primaryKey(),
avatarId: integer('avatar_id').references(() => media.id, { onDelete: 'set null' }),
});
// ...
export const media = sqliteTable('media', {
id: integer('id').notNull().primaryKey(),
createdAt: integer('created_at', { mode: 'timestamp' })
.notNull()
.default(sql`(unixepoch())`),
updatedAt: integer('updated_at', { mode: 'timestamp' })
.notNull()
.default(sql`(unixepoch())`),
fileName: text('file_name', { mode: 'text', length: 255 }).notNull(),
mediaType: text('file_type', { mode: 'text' }).notNull(),
mimeType: text('mime_type', { mode: 'text', length: 100 }).notNull(),
size: integer('size', { mode: 'number' }).notNull(),
width: integer('width', { mode: 'number' }).notNull(),
height: integer('height', { mode: 'number' }).notNull(),
src: text('src', { mode: 'text' }).notNull(),
srcSet: text('srcset').notNull(),
alt: text('alt', { mode: 'text' }).notNull(),
});-- 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;
CDCloudflare Developers / general-helpCDCloudflare Developers / pages-helpCDCloudflare Developers / workers-and-pages-helpCDCloudflare Developers / general-help