Does D1 support transactions? Seems like not: ```bash To execute a transaction, please use the state

몰

몰라세스•6/10/24, 7:38 AM

not supported? maybe.

Ppredaytor Does D1 support transactions? Seems like not: ```bash To execute a transaction, ...

H

Hello, I’m Allie!•6/10/24, 9:04 AM

All queries/batches are run within transactions,but you can’t manually create your own

P

predaytorOP•6/10/24, 11:21 AM

what is the correct way to store a reference to an uploaded cloudflare r2 image in the database? save id of the uploaded image, and then somehow get the necessary data such as width/height and other metadata (causing waterfalls?) or save the data during upload directly to the separate table, for example:

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: text('avatar_id', { mode: 'text', length: 255 }),
});

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: text('avatar_id', { mode: 'text', length: 255 }),
});

vs

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(),
});

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(),
});

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(),
});

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(),
});

J

JoeDoe•6/10/24, 6:42 PM

how to cache d1 requests?

J

JoeDoe•6/10/24, 6:42 PM

I'm using workers for a web api, but I need simple data like total users to be cached for like 60seconds or similiar

JJoeDoe how to cache d1 requests?

U

Unsmart•6/10/24, 6:57 PM

the cache api https://developers.cloudflare.com/workers/runtime-apis/cache/

UUnsmart the cache api <https://developers.cloudflare.com/workers/runtime-apis/cache/>

J

JoeDoe•6/10/24, 7:05 PM

is there some good examples on how to use this with workers?

JJoeDoe is there some good examples on how to use this with workers?

M

Max (@rozenmd)•6/10/24, 7:14 PM

https://developers.cloudflare.com/workers/examples/cache-api/

Cloudflare Docs

Using the Cache API · Cloudflare Workers docs

Documentation for Cloudflare Workers, a serverless execution environment that allows you to create entirely new applications or augment existing ones …

J

JoeDoe•6/10/24, 7:21 PM

hmmmmm interesting thank you

M

maj•6/10/24, 10:45 PM

FWIW: I'm using KV to cache some query results I'd like to share across requests. The Cache API would only cache the result of HTTP requests, wouldn't it?

U

Unsmart•6/10/24, 11:06 PM

The cache api can cache anything... normal http requests you can cache with just the fetch api

M

maj•6/11/24, 4:45 AM

Maybe I'm missing the obvious, but you have to pass it a response object, with caching headers set etc. How would I define the expiration if I wanted to cache a random blob?

M

maj•6/11/24, 4:46 AM

From https://developers.cloudflare.com/workers/reference/how-the-cache-works/#cache-api:

The 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.

Cloudflare Docs

How the Cache works · Cloudflare Workers docs

How Workers interacts with the Cloudflare cache.

Mmaj From https://developers.cloudflare.com/workers/reference/how-the-cache-works/#ca...

M

Max (@rozenmd)•6/11/24, 7:21 AM

make a new Response, put your blob in the body

MMax (@rozenmd)make a new Response, put your blob in the body

M

maj•6/11/24, 7:30 AM

My comment was in response to use @Unsmart | Tech debt claiming that the Cache API could cache anything. But you basically confirm that I can't cache the blob alone (because I want to reuse it in other handlers), don't you? Using the Cache API I can only cache the full response?

Mmaj My comment was in response to use @Unsmart | Tech debt claiming that the Cache A...

H

Hello, I’m Allie!•6/11/24, 7:39 AM

Well no? You create a new, completely separate Response, that is just the blob+cache headers. That Response is never served to the client, it is just used to interact with the Cache API

HHello, I’m Allie!Well no? You create a new, completely separate Response, that is just the blob+c...

M

maj•6/11/24, 9:15 AM

It's not a general purpose cache, is it? It's based on HTTP requests and responses. Even if you provide a random string as the cache key "it is interpreted as the URL for a new Request object." (https://developers.cloudflare.com/workers/runtime-apis/cache/). So in order to cache the raw result of a d1 query I'd have to wrap and unwrap the result. This only adds complexity and overhead, and still doesn't provide a global cache like using KV does. The original question was "how to cache d1 requests?". Not "how to cache http requests".

Mmaj It's not a general purpose cache, is it? It's based on HTTP requests and respons...

H

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

Well, yeah, it isn't a perfect system, but it is the best option to do it for free. You can use KV/DOs/an external service like Redis, but that would be at cost to you

H

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

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!•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!•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!•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)

and

.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!•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

...
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

Does D1 support transactions? Seems like not: ```bash To execute a transaction, please use the state

Similar Threads

Similar Threads

Similar Threads