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

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

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.9555,835,335.95 or 558,353,359.5558,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 billion25 billion + 55,835,335.9555,835,335.95 ? What's the number under the 55,835,335,9555,835,335,95 , does that mean I uses 55,835,335,95 x 355,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,95325 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

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

Max (@rozenmd)•6/17/24, 8:50 PM

do you have a WHERE clause in that query, and do you have indexes on what you're looking for in the WHERE clause?

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

no where clause

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

the above is the whole query

Max (@rozenmd)•6/17/24, 8:52 PM

and when you visit the cloudflare dashboard, there's no warnings about it being an alpha DB, right?

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

where would i check that?

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

Similar Threads

Similar Threads

Similar Threads