Yeah but D1 is different because it isn't a standard file system sqllite implementation, they're doi

Yeah but D1 is different because it isn't a standard file system sqllite implementation, they're doing a lot behind the scenes streaming data to/from R2 (I think), so it's possible they'll be doing something funky with transactions to guarantee one writer/multiple readers, and I'm curious what that is/if it's implemented yet.

I could probably spend hours testing to see what the behaviour is like, but testing concurrency/race conditions is difficult and it's alpha so that behaviour will likely change anyway so there's not much point.

EEpailes Yeah but D1 is different because it isn't a standard file system sqllite impleme...

Matt Silverlock•3/24/23, 12:44 PM

We’ll be publishing a lot more about the storage architecture, how replicas work, and the consistency story — won’t be “next month” but sooner rather than later. There is a LOT changing under the hood but important that we battle test things first.

Ssteida Is it possible to use D1 in edge runtime? I would like to use Next.js web with A...

Matt Silverlock•3/25/23, 11:04 PM

Yes: d1-database

https://developers.cloudflare.com/d1/learning/using-d1-from-pages/

Bindings · Cloudflare Pages docs

A binding enables your Pages Functions to interact with resources on the Cloudflare developer platform. Use bindings to integrate your Pages Functions …

Ssatoshi I've created a tutorial on how to build a web application using Cloudflare D1 an...

Matt Silverlock•3/26/23, 1:55 AM

^ Drizzle is really great.

Matt Silverlock•3/26/23, 1:56 AM

There’s also https://superflare.dev/ as a “Rails like” framework, with support for D1

Hello, Superflare

A new way to build full-stack applications on Cloudflare Workers

justaname•3/27/23, 2:32 AM

is Read replication exclusive to d1 i haven't seen it anywhere else

Cc becker seems like with d1 its more on by default than you might find elsewhere

Max (@rozenmd)•3/27/23, 9:57 AM

(we haven't turned on replication just yet)

Jason Hostetter•3/28/23, 2:47 PM

Is there a way to log the final SQL statement that is run (i.e. with interpolated values)? I can log the prepared statement object which gives me the statement string and the array of values to interpolate, but would be helpful for debugging if I could just grab the final SQL that is run.

JJason Hostetter Is there a way to log the final SQL statement that is run (i.e. with interpolate...

Matt Silverlock•3/28/23, 3:28 PM

Note that prepared statements/parameterized queries are sent in two parts to nearly any DB (SQLite, Postgres, MySQL) that handles them — the client isn't doing the interpolation, and thus can't "see" that query.

JJason Hostetter Is there a way to log the final SQL statement that is run (i.e. with interpolate...

Matt Silverlock•3/28/23, 3:31 PM

Are there specific cases you're trying to debug / an example you can share?

Jason Hostetter•3/28/23, 3:54 PM

Ah that's too bad. If I'm trying to figure out/optimize a complex query I just like to copy the query into a separate SQL application so that I can easily edit/re-run queries on the local sqlite db. I am generating the queries using template strings at runtime, so I log the final constructed prepared statement, manually fill in the values ,then copy that to the other SQL application. It's just kind of a pain.

Could the final query be returned in the response object? Or is there some security concern there?

JJason Hostetter Ah that's too bad. If I'm trying to figure out/optimize a complex query I just l...

Matt Silverlock•3/28/23, 5:03 PM

It's technically possible, but SQLite (and other DBs) don't return it with the query results — your generated SQL could be large, and you may not want that coming back "by default" as it's a non-zero cost.

JJason Hostetter Ah that's too bad. If I'm trying to figure out/optimize a complex query I just l...

Matt Silverlock•3/28/23, 5:06 PM

In other words: something we can look into! FWIW, PHP does this — you can call $result->getSQL(true)$result->getSQL(true) — which asks SQLite to bind the params but doesn't run the query again.

MMatt Silverlock In other words: something we can look into! FWIW, PHP does this — you can call `...

Jason Hostetter•3/28/23, 5:30 PM

That's good to know. PHP's approach seems reasonable! Would be a nice DX improvement. Thanks for the response.

Elie Zedeck R.•3/29/23, 12:21 PM

I'm experiencing a likely bug, I'm not sure if anyone has encountered this or not. It happens on both live and local. Doing a simple SELECT on a DB of about 2MB is size, I randomly get

null

null

as the result, even though the queried item definitely exists. It doesn't happen if that is the only query that I run against the DB, but it happens as soon as realistic traffic is sent to wranger, and other tables and other types of queries happen at the same time.
Is this is a known issue?

EElie Zedeck R.I'm experiencing a likely **bug**, I'm not sure if anyone has encountered this o...

Matt Silverlock•3/29/23, 1:03 PM

What is your schema? What is the query? Types?

MMatt Silverlock What is your schema? What is the query? Types?

Elie Zedeck R.•3/29/23, 1:21 PM

Query: await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first()await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first() ... It is not typed. My schema is like the following:

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

EElie Zedeck R.Query: `await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind...

Matt Silverlock•3/29/23, 3:56 PM

but it happens as soon as realistic traffic is sent to wranger, and other tables and other types of queries happen at the same time.

What kind of query volume (in QPS) - ?

EElie Zedeck R.Query: `await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind...

Matt Silverlock•3/29/23, 4:12 PM

Can you also send me your accountId + databaseId? Here or in DM?

MMatt Silverlock > but it happens as soon as realistic traffic is sent to wranger, and other tabl...

Elie Zedeck R.•3/29/23, 8:01 PM

Really small, I didn't measure or anything, but I guess not more than 10/s ... and it's just a short burst.

MMatt Silverlock Can you also send me your accountId + databaseId? Here or in DM?

Elie Zedeck R.•3/29/23, 8:01 PM

Ok, I'll DM you, I don't know my account ID

Elie Zedeck R.•3/29/23, 8:04 PM

I don't know if the DM was received or not ... anyway, DB ID = 2d159a31-757e-46c2-865b-095792688269

Elie Zedeck R.•3/29/23, 8:05 PM

I believe you should be able to find the account info from that

justaname•3/29/23, 10:06 PM

is there any way of doing a connection link instead of using workers?

Jjustaname is there any way of doing a connection link instead of using workers?

Hello, I’m Allie!•3/29/23, 10:08 PM

Not at the moment

Pinheiro•3/30/23, 12:06 PM

Is there any limit on server locations when using D1? I'm from Brazil and there's about ~500ms to make a simple crud GET/POST. When I use another database (Cloudflare pages/workers + any other DB service) this value decreases to ~150ms

Original message was deleted

Pinheiro•3/30/23, 12:22 PM

Thank you! =)

chientrm.com•3/30/23, 1:19 PM

does D1 support full text search?

Original message was deleted

chientrm.com•3/30/23, 1:40 PM

is that D1 atomic write?

Original message was deleted

chientrm.com•3/30/23, 1:42 PM

can perform transaction?

Mozzy•3/30/23, 2:30 PM

No worries

CREATE TABLE publishers ( `id` INTEGER PRIMARY KEY, `name` TEXT NOT NULL, `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `isDifficult` BOOLEAN NOT NULL DEFAULT 0 ); CREATE INDEX idx_publishers_name ON publishers (name); CREATE TABLE products ( `id` INTEGER PRIMARY KEY, `name` TEXT NOT NULL, `publisherId` INTEGER NOT NULL, `amazonAsin` TEXT UNIQUE, `dateSeen` TEXT NOT NULL, `publisherApprovalStatus` TEXT NOT NULL DEFAULT 'pending', `sellable` BOOLEAN NOT NULL DEFAULT 0, `lastSalesRank` INTEGER, `lastSalesRankDate` DATETIME, bookPublishingDate DATETIME, FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE ); CREATE INDEX idx_products_publisherId ON products (publisherId); CREATE INDEX idx_products_amazonAsin ON products (amazonAsin); CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus); CREATE INDEX idx_products_sellable ON products (sellable); CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

Yeah but D1 is different because it isn't a standard file system sqllite implementation, they're doi

Similar Threads

Similar Threads

Similar Threads