As far as I can tell, D1 does not have any transaction support. To get around this, I'm considering

As far as I can tell, D1 does not have any transaction support. To get around this, I'm considering writing to D1 in a Durable Object transaction instead, as described here: https://ma.rkusa.st/transactions-for-sqlite-on-cloudflare-durable-objects

Does anyone see any problems with this or have experience with this?

RRexNormous As far as I can tell, D1 does not have any transaction support. To get around th...

Max (@rozenmd)•3/7/24, 5:03 PM

D1 in a Durable Object transaction instead

You'll never guess what D1 uses

Max (@rozenmd)•3/7/24, 5:04 PM

Every query is run in a DO transaction

MMax (@rozenmd)Every query is run in a DO transaction

RexNormousOP•3/7/24, 5:06 PM

Ah! Sounds like adding some JS inbetween D1 writes in a DO tx is perfect then, thanks!

RRexNormous As far as I can tell, D1 does not have any transaction support. To get around th...

DaniFoldi•3/7/24, 5:09 PM

what you're planning won't work though, because the DO transaction only reverts what it can - from its point of view, running a query in D1 is the same as calling fetch() that has some side-effect

DaniFoldi•3/7/24, 5:09 PM

with the implementation in the blog, they were running sqlite wasm in the same DO, whereas D1 runs in a separate one

alpha1119179•3/7/24, 5:10 PM

Hi Max. I'm trying to use Drizzle currently with my Pages project to run migrations for my d1 database. I'm facing some issues.

The sql generated in migrations doesn't have IF NOT EXISTS in CREATE TABLE statements which causes errors while re-running the migrations against the db where the table already exists so I've to manually add it.
I'm getting ERROR] A prepared SQL statement must contain only one statement. [code: 7500] even though all sql statements have semicolon at the end.
Is there a way to do CI-CD with drizzle. I want to use do migrations from my github actions?

Aalpha1119179 Hi Max. I'm trying to use Drizzle currently with my Pages project to run migrati...

Max (@rozenmd)•3/7/24, 5:12 PM

is probably best handled via a github issue to their repo
there's a bug in our sql parser/splitter at the moment - is there a comment at the start of the file by any chance?
not sure about that one

DDaniFoldi what you're planning won't work though, because the DO transaction only reverts ...

RexNormousOP•3/7/24, 5:13 PM

Yes, I should be more specific. For my use-case, I had been relying on a MySQL serializable transaction as a sort of “global lock”, where I would:

Insert a row that is enforced unique
Fetch some external service
Rollback 1 if 2 fails, else commit

This will work with DO & D1 I believe since there is 1 write ONLY. Instead of using SQL as the global lock, DO will be my global, ID-based lock

alpha1119179•3/7/24, 5:13 PM

Oh come on! Yes!! Drizzle generated whole bunch of comments before sql statements I removed them and it worked!!! Thanks Max.

RRexNormous Yes, I should be more specific. For my use-case, I had been relying on a MySQL s...

DaniFoldi•3/7/24, 5:14 PM

okay that might work then, looks like sort of a 2PC

DDaniFoldi okay that might work then, looks like sort of a 2PC

RexNormousOP•3/7/24, 5:17 PM

Right! Re-engineering this in DO, I believe I will:

Start DO tx to acquire lock
Fetch external service
Rollback if 2 fails
Insert D1 row to record 2 happened
Commit

4 could fail, and I need to figure that out but it’s a good start. Thanks!

DaniFoldi•3/7/24, 5:18 PM

yup, make sure to add a timeout too

Max (@rozenmd)•3/7/24, 5:20 PM

I've been working on making

wrangler d1 execute

wrangler d1 execute

default to local (to match wrangler dev), and I think it's in a good place to try out: https://github.com/cloudflare/workers-sdk/pull/4930

You can try this out via

npx wrangler@d1

npx wrangler@d1

. For example:

npx wrangler@d1 d1 execute <DBNAME> --command ="..."

npx wrangler@d1 d1 execute <DBNAME> --command ="..."

You can also now be explicit with the command to be 100% sure where you're executing -

--local

--local

for local,

--remote

--remote

for remote

haz•3/7/24, 7:54 PM

How long until D1 comes out of beta?

mw10013•3/7/24, 8:23 PM

Can I create a Pages binding using wrangler or only with the Pages dashboard?

Where this is coming up for me is "resetting" a D1 database during prototyping by simply recreating the database and binding. In my

wrangler.toml

wrangler.toml

[[ env.preview.d1_databases ]]
binding = "D1"
database_name = "rcms-d1-preview"
database_id = "618bb8af-dad2-482d-82b0-ffd0780073f0" # pnpm wrangler d1 info rcms-d1-preview
preview_database_id = "D1"
migrations_table = "d1_migrations"
migrations_dir = "drizzle"

[[ env.preview.d1_databases ]]
binding = "D1"
database_name = "rcms-d1-preview"
database_id = "618bb8af-dad2-482d-82b0-ffd0780073f0" # pnpm wrangler d1 info rcms-d1-preview
preview_database_id = "D1"
migrations_table = "d1_migrations"
migrations_dir = "drizzle"

I can use wrangler cli to delete, recreate, and get the database id to populate

wrangler.toml

wrangler.toml

. Can't seem to figure out a way to use wrangler to recreate the dangling Pages binding to the deleted D1 database and have to go to the dashboard.

Also very interested in what workflows others are using to "reset" D1 locally. I'm currently using a clumsy script:

import { $, glob } from "zx";

/**
 * Reset the local d1 database violently.
 * Run any migrations and seed.
 */

await $`rm -rf ./.wrangler`;

// Create db.
// await $`pnpm wrangler d1 execute local-d1-dev --local --command "select datetime('now');"`;
// https://github.com/cloudflare/workers-sdk/issues/5092
await $`pnpm wrangler d1 execute local-d1-dev --local --command "pragma foreign_keys = ON;"`;

const migrationFiles = await glob("./drizzle/*.sql");
console.log({ migrationFiles });
if (migrationFiles.length > 0) {
  await $`wrangler d1 migrations apply local-d1-dev --local`;
  await $`pnpm d1:seed`;
}

const sqliteFiles = await glob("./.wrangler/**/*.sqlite");
console.log({ sqliteFiles });
if (sqliteFiles.length !== 1) {
  console.error("Expected exactly one sqlite file under .wrangler");
  process.exit(1);
}

const statements = `
.schema
pragma table_list`;
await $`echo ${statements} | sqlite3 ${sqliteFiles[0]}`;

console.log(`sqlite3 ${sqliteFiles[0]}`);

import { $, glob } from "zx";

/**
 * Reset the local d1 database violently.
 * Run any migrations and seed.
 */

await $`rm -rf ./.wrangler`;

// Create db.
// await $`pnpm wrangler d1 execute local-d1-dev --local --command "select datetime('now');"`;
// https://github.com/cloudflare/workers-sdk/issues/5092
await $`pnpm wrangler d1 execute local-d1-dev --local --command "pragma foreign_keys = ON;"`;

const migrationFiles = await glob("./drizzle/*.sql");
console.log({ migrationFiles });
if (migrationFiles.length > 0) {
  await $`wrangler d1 migrations apply local-d1-dev --local`;
  await $`pnpm d1:seed`;
}

const sqliteFiles = await glob("./.wrangler/**/*.sqlite");
console.log({ sqliteFiles });
if (sqliteFiles.length !== 1) {
  console.error("Expected exactly one sqlite file under .wrangler");
  process.exit(1);
}

const statements = `
.schema
pragma table_list`;
await $`echo ${statements} | sqlite3 ${sqliteFiles[0]}`;

console.log(`sqlite3 ${sqliteFiles[0]}`);

Mmw10013 Can I create a Pages binding using wrangler or only with the Pages dashboard? W...

Hello, I’m Allie!•3/7/24, 8:37 PM

Only via the dashboard

Original message was deleted

Chaika•3/7/24, 9:25 PM

D1's current plan is that you can create a really unlimited number of D1 Databases (well, the architecture can scale that way) so you could shard your data by user or something that makes logical sense to scale -> d1-database

Original message was deleted

Hello, I’m Allie!•3/7/24, 9:27 PM

There will also be replicas, so once the data is replicated, D1 should scale in a similar way to Workers themselves(at least when it comes to reads).

fin•3/7/24, 9:29 PM

whoops i definitely need to make some indexes haha

Original message was deleted

Hello, I’m Allie!•3/7/24, 9:29 PM

It should happen by default, iirc, so you shouldn't need to do it yourself

Original message was deleted

Chaika•3/7/24, 9:29 PM

It depends on your use case. Some have more obvious places where you can shard.
Every database has trade offs though, usually sacrificing consistency, or availability to scale more

Hello, I’m Allie!•3/7/24, 9:29 PM

It should even be able to replicate within the same region, allowing multiple D1 instances to serve requests in parallel

Hello, I’m Allie!•3/7/24, 9:30 PM

And increasing the max reads per second

Original message was deleted

Hello, I’m Allie!•3/7/24, 9:40 PM

It is not

Hello, I’m Allie!•3/7/24, 9:41 PM

Or well, D1 is replicated for disaster recovery iirc, but it doesn't actually spawn replicas at the moment

Original message was deleted

Max (@rozenmd)•3/7/24, 9:46 PM

D1's beta doesn't scale because the db you access is running on a single threaded durable object instance without replication - there's only so much work you can do in a second on a single thread

haz•3/7/24, 10:43 PM

I live in London, wondering if there is any event coming up in CloudFlare where I can meet the guys behind Workers, D1 and stuff surrounding that

kian•3/7/24, 10:46 PM

Meetups & events will be announced here, but I don't think there are currently any that have been scheduled

Original message was deleted

Chaika•3/7/24, 10:55 PM

That would be one approach. They've also talked about some sort of automatic sharding by a shardkey or something in the past but nothing more on it: d1-database
It depends on your use case and how big your databases would be. If you were offering a SaaS Product, having each customer on its own database would be logical and you could have that resolve differently by just looking at the hostname/leveraging something like CF For SaaS to support their own domains. If it was users instead, that would be more messy and require more glue/requests always containing user id and such

Chaika•3/7/24, 10:56 PM

The big thing we're missing right now is the ability to dynamically create and bind to DBs without a redeploy. They've said its planned to be part of/ahead of GA: d1-database

Enduriel•3/8/24, 12:07 AM

Is there some reason I would be unable to turn off foreign key constraints? I sanity checked doc and afaik it should be possible, but when I execute the following query:

PRAGMA foreign_keys = OFF;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Rooms;
DROP TABLE IF EXISTS Sessions;
PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = OFF;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Rooms;
DROP TABLE IF EXISTS Sessions;
PRAGMA foreign_keys = ON;

I still get a

✘ [ERROR] FOREIGN KEY constraint failed

✘ [ERROR] FOREIGN KEY constraint failed

Enduriel•3/8/24, 12:10 AM

Enduriel•3/8/24, 12:10 AM

trying to figure out if I'm going crazy

alpha1119179•3/8/24, 10:43 AM

there isn't a --yes flag on

npx wrangler d1 migrations apply db

npx wrangler d1 migrations apply db

due to which I can't apply migrations in my github actions workflow.

alpha1119179•3/8/24, 10:46 AM

Although, such a flag exists on

npx wrangler d1 execute

npx wrangler d1 execute

Original message was deleted

alpha1119179•3/8/24, 11:00 AM

My command -

"apply:preview": "npx is-ci && npx wrangler d1 migrations apply db --preview"

"apply:preview": "npx is-ci && npx wrangler d1 migrations apply db --preview"

jason•3/8/24, 12:27 PM

I'm stuck on this issue - d1-database

would anyone have any ideas? Or is this not the right place to ask such questions?

Jjason I'm trying to setup my first D1 db. I've successfully applied migrations using t...

Max (@rozenmd)•3/8/24, 12:29 PM

try

npx wrangler d1 execute job-hunt-db --local --persist-to /tmp/job-hunt-tmp-data --command="SELECT * FROM TargetRoleAnalysis"

npx wrangler d1 execute job-hunt-db --local --persist-to /tmp/job-hunt-tmp-data --command="SELECT * FROM TargetRoleAnalysis"

Aalpha1119179 there isn't a --yes flag on `npx wrangler d1 migrations apply db` due to which I...

Max (@rozenmd)•3/8/24, 12:31 PM

it auto-confirms when you're in CI, likely the command is actually throwing an error

Aalpha1119179 My command - `"apply:preview": "npx is-ci && npx wrangler d1 migrations apply db...

Max (@rozenmd)•3/8/24, 12:35 PM

can you temporarily tweak it to be

WRANGLER_LOG=debug npx wrangler d1 migrations apply db --preview

WRANGLER_LOG=debug npx wrangler d1 migrations apply db --preview

and DM me the result?

npx is-ci &&

npx is-ci &&

shouldn't be necessary in GH Actions

ericwilhite•3/8/24, 1:43 PM

Is there any overhead to just using the D1 REST API to query the databases?
(https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database)
I'm building an architecture with 1 D1 database per customer and managing the bindings is a headache.

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

Eericwilhite Is there any overhead to just using the D1 REST API to query the databases? (ht...

Hello, I’m Allie!•3/8/24, 1:47 PM

Quite a bit. Going through a Worker, a query can head directly to the database. Via the API, it has to hit the API Core datacenters first, then back out to the datacenter your database is in, along with auth layers, etc.

ericwilhite•3/8/24, 1:47 PM

Original message was deleted

ericwilhite•3/8/24, 1:50 PM

Nice! this looks helpful thanks for the link

jason•3/8/24, 2:32 PM

that worked @Max (@rozenmd) , or at least there were no errors, will continue playing around with it to get a better understanding, thanks for your help

Jjason that worked @Max (@rozenmd) , or at least there were no errors, will continue pl...

Max (@rozenmd)•3/8/24, 2:40 PM

unfortunately you need to specify

--persist-to

--persist-to

in every command you use if you customise it

Tony Leung•3/8/24, 3:34 PM

Hi, do cf has any plan to extend KV to some kind of db like DynamoDB? I find that half of my case I don't really need complex JOIN or select by key but a simple query on a data partition with hash key defined and filtering on a key in the document. D1 is kinda overkill for me and with a large table I don't know that can D1 handle a query like that.

Unsmart•3/8/24, 3:35 PM

Durable objects is very similar to DynamoDB

Ian•3/8/24, 3:36 PM

Hi folks - somewhat stuck with bringing D1 into our next-on-pages project. We have pages up and running with our code base (previously hosted on Amplify) but having crawled all over the docs I don't see clearly my steps to integrate D1 into a NextJS code base specifically.

UUnsmart Durable objects is very similar to DynamoDB

Tony Leung•3/8/24, 3:40 PM

DO is transactional, so when I make multiple queries at the same time, can DO returns data for me fast enough :? BTW how can I implements smth like dynamodb with DO though?

[[ env.preview.d1_databases ]] binding = "D1" database_name = "rcms-d1-preview" database_id = "618bb8af-dad2-482d-82b0-ffd0780073f0" # pnpm wrangler d1 info rcms-d1-preview preview_database_id = "D1" migrations_table = "d1_migrations" migrations_dir = "drizzle"

import { $, glob } from "zx"; /** * Reset the local d1 database violently. * Run any migrations and seed. */ await $`rm -rf ./.wrangler`; // Create db. // await $`pnpm wrangler d1 execute local-d1-dev --local --command "select datetime('now');"`; // https://github.com/cloudflare/workers-sdk/issues/5092 await $`pnpm wrangler d1 execute local-d1-dev --local --command "pragma foreign_keys = ON;"`; const migrationFiles = await glob("./drizzle/*.sql"); console.log({ migrationFiles }); if (migrationFiles.length > 0) { await $`wrangler d1 migrations apply local-d1-dev --local`; await $`pnpm d1:seed`; } const sqliteFiles = await glob("./.wrangler/**/*.sqlite"); console.log({ sqliteFiles }); if (sqliteFiles.length !== 1) { console.error("Expected exactly one sqlite file under .wrangler"); process.exit(1); } const statements = ` .schema pragma table_list`; await $`echo ${statements} | sqlite3 ${sqliteFiles[0]}`; console.log(`sqlite3 ${sqliteFiles[0]}`);

As far as I can tell, D1 does not have any transaction support. To get around this, I'm considering

Similar Threads

Similar Threads

Similar Threads