Hi, I have a very strange behaviour I don't really understand. When I run a migration adding a colum

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

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

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;"

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

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

D1 is SQLite which doesn't have MODIFYMODIFY - https://www.sqlite.org/lang_altertable.html

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

HannesOP•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;. In D1, however, with 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 ...

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

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

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

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

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

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>

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

is d1 replicated yet ?

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

or its still one single primary db atm

NNob or its still one single primary db atm

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

One DB Instance

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

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

55,835,335.95

55,835,335.95

? What's the number under the

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

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

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

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

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

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

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

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

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

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

wait that's 55 billion

Nnotjoemartinez wait that's 55 billion

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

Hello, I’m Allie!•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.

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?

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

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

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);

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

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

So kinda. Depends on your queries.

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

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

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

The virtual table has certain default indexes applied. But if you're querying for a prefix you'll need to add an explicit prefix index: https://sqlite.org/fts5.html

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

By default, FTS5 maintains a single index recording the location of each token instance within the document set. This means that querying for complete tokens is fast, as it requires a single lookup, but querying for a prefix token can be slow, as it requires a range scan. For example, to query for the prefix token "abc*" requires a range scan of all tokens greater than or equal to "abc" and less than "abd".

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

A prefix index is a separate index that records the location of all instances of prefix tokens of a certain length in characters used to speed up queries for prefix tokens.

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

I expect you're seeing the result of that there with the

MATCH ?

MATCH ?

depending on what the substitution is.

Ruurtjan•6/16/24, 12:38 PM

I’m working on a domain name availability checker that is faster than you can type. I’ve got access to a database of all domain names that are in use, updated daily.

As I understand, D1 currently doesn’t have global read replicas, but it’s been in the works for a while. Is there a private alpha I can join?

HannesOP•6/16/24, 1:23 PM

I currently have trouble (again) importing my database. The SQL file is about 18 MB. I cannot import it directly because of this issue https://github.com/cloudflare/workers-sdk/issues/5683, but also after sorting all CREATE TABLE commands to the top of the file, it fails with

internal error

internal error

... A local import works fine. Any idea what to try?

RRuurtjan I’m working on a domain name availability checker that is faster than you can ty...

Hello, I’m Allie!•6/16/24, 1:23 PM

Afaik it isn’t available in any capacity atm

HHannes I currently have trouble (again) importing my database. The SQL file is about 18...

Gerbuuun•6/16/24, 6:48 PM

I tried doing

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF

and that made it work somehow. (even though it is not supported?) Idk if that fixed it but you could at least try

Gerbuuun•6/16/24, 6:48 PM

I don't really know how foreign keys work in D1. At least not as described in the SQLite docs

Gerbuuun•6/16/24, 6:49 PM

To me it seems very inconsistent

Gerbuuun•6/16/24, 6:49 PM

Or a recent update broke something

GGerbuuun I tried doing `PRAGMA foreign_keys=OFF` and that made it work somehow. (even tho...

HannesOP•6/16/24, 6:56 PM

Interesting. Did not work for me, unfortunately. Thanks for the idea, though

RRuurtjan I’m working on a domain name availability checker that is faster than you can ty...

Nob•6/17/24, 12:13 AM

use redis if you need smth really fast

NNob use redis if you need smth really fast

Ruurtjan•6/17/24, 4:50 AM

0ms response in Europe will still be 100-200 in the US

RRuurtjan 0ms response in Europe will still be 100-200 in the US 😉

Nob•6/17/24, 7:25 AM

you do redis replicas in us then

check engine•6/17/24, 7:10 PM

I'm new to D1, @cloudflare/d1 is deprecated on npm. What should i use with node.js?

check engine•6/17/24, 7:12 PM

i dont want to use prisma :<

kian•6/17/24, 7:29 PM

@cloudflare/d1

@cloudflare/d1

was always for Workers, not NodeJS - it's just replaced by a built-in equivalent now.

kian•6/17/24, 7:29 PM

The only way to use D1 outside of Workers is the HTTP API, and I don't believe Cloudflare offer any wrappers around that.

Kkian `@cloudflare/d1` was always for Workers, not NodeJS - it's just replaced by a bu...

check engine•6/17/24, 7:40 PM

ohhhh

check engine•6/17/24, 7:40 PM

thanks

beau•6/17/24, 8:46 PM

howdy! i have a database with ~10k deposits. I'm running a query like:

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

and it's taking ~30+ seconds. anyone have any clue about what's happening or how we could speed it up?

i've tried cloning my remote data locally and i'm getting similar performance

Hi, I have a very strange behaviour I don't really understand. When I run a migration adding a colum

Similar Threads

Similar Threads

Similar Threads