Any word on transaction support? I'd really love to use D1 for my application, but data integrity

J

jason•3/7/24, 6:35 AM

I'm trying to setup my first D1 db. I've successfully applied migrations using the command:

npx wrangler d1 migrations apply job-hunt-db --local --persist-to /tmp/job-hunt-tmp-data

but then I try to execute a command against the job-hunt-db with this command:

npx wrangler d1 execute job-hunt-db --local --command="SELECT * FROM TargetRoleAnalysis"

✘ [ERROR] no such table: TargetRoleAnalysis

I can see that there is a sqlite file in the /tmp/ directory, I've tried connecting to this db using the sqlite client, when I try to list tables the db returns empty, no tables exist. Yet the migrations to create these tables successfully executed, any ideas why this might be happening?

C

Chy•3/7/24, 10:11 AM

Is D1 database integration free?

C

Chy•3/7/24, 10:12 AM

Nevermind I found out it is free but there is a billing metric

CChy Nevermind I found out it is free but there is a billing metric

M

Max (@rozenmd)•3/7/24, 10:26 AM

D1 databases have a free tier, and a $5/mo plan with billed usage

JJames According to the d1 documentation, the maximum database size is 2GB. Will you su...

C

Cyb3r-Jak3•3/7/24, 1:00 PM

They are working towards 10 GB. d1-database

A

alpha1119179•3/7/24, 2:42 PM

A

alpha1119179•3/7/24, 2:43 PM

Hi. Is this a good place to ask technical questions e.g., code-related issues like using an orm with d1?

JJames According to the d1 documentation, the maximum database size is 2GB. Will you su...

K

K•3/7/24, 3:00 PM

GA will be in April.

Aalpha1119179 Hi. Is this a good place to ask technical questions e.g., code-related issues li...

M

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

yeah, check out drizzle if you haven't already https://orm.drizzle.team/

Drizzle ORM - next gen TypeScript ORM.

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

RRexNormous Any word on transaction support? I'd really love to use D1 for my application,...

R

RexNormousOP•3/7/24, 4:47 PM

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

M

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

D1 in a Durable Object transaction instead

You'll never guess what D1 uses

M

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

R

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

D

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

D

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

A

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

M

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

R

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

A

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

D

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

R

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!

D

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

yup, make sure to add a timeout too

M

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

npx wrangler@d1

npx wrangler@d1. For example:

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

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

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

H

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

How long until D1 comes out of beta?

M

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"

[[ 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]}`);

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

H

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

Only via the dashboard

Original message was deleted

C

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

H

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

F

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

whoops i definitely need to make some indexes haha

Original message was deleted

H

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

C

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

H

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

H

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

And increasing the max reads per second

Original message was deleted

H

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

It is not

H

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

M

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

H

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

K

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

C

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

C

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

E

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;

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

✘ [ERROR] FOREIGN KEY constraint failed

✘ [ERROR] FOREIGN KEY constraint failed

E

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

?

E

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

trying to figure out if I'm going crazy

A

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

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.

A

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

Although, such a flag exists on

npx wrangler d1 execute

npx wrangler d1 execute

npx wrangler d1 execute

npx wrangler d1 execute.

Original message was deleted

A

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"

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

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

J

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

M

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"

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

M

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

M

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

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

npx is-ci &&

npx is-ci && shouldn't be necessary in GH Actions

Any word on transaction support? I'd really love to use D1 for my application, but data integrity

Similar Threads

Similar Threads

Similar Threads