Hi Max. I'm trying to use Drizzle currently with my Pages project to run migrations for my d1 databa

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

RexNormous•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

alpha1119179OP•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

RexNormous•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

alpha1119179OP•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

alpha1119179OP•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

alpha1119179OP•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

E

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

H

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.

E

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

Original message was deleted

E

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

Nice! this looks helpful thanks for the link

J

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

M

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

unfortunately you need to specify

--persist-to

--persist-to

--persist-to

--persist-to in every command you use if you customise it

T

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.

U

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

Durable objects is very similar to DynamoDB

I

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

T

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?

U

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

Queries are extremely fast so you wont really notice if 2 storage requests are not happening at the exact same time. And why would you implement DynamoDB just use DOs as a DO or just use Dynamo instead

UUnsmart Queries are extremely fast so you wont really notice if 2 storage requests are n...

T

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

AWS outbound data transfer is not cheap, I was living in fear that someday Jeff Bozos would come to my house to slap a big fat bill in my face...

IIan Hi folks - somewhat stuck with bringing D1 into our next-on-pages project. We ha...

A

alpha1119179OP•3/8/24, 3:56 PM

Exactly my problem. I'm doing the same thing. Pages built on nextjs 14. I've been trying to integrate d1 with limited success. DM me Ian if you wanna share my findings.

Original message was deleted

D

DaniFoldi•3/8/24, 4:19 PM

fun fact it also converts

number[]

number[]

s in line 227-230

Original message was deleted

I

Ian•3/8/24, 4:19 PM

Relatively new to this business - I was having trouble running npx wrangler pages dev -- npm run dev but have fixed that and have been able to bind the DB by adding to wrangler.toml so making progress. Will see if I can get at it next. Thanks for the reply.

T

Tomas Pietravallo•3/8/24, 4:20 PM

Hey everyone! Sorry if this is not the correct channel but I am having trouble finding a solution to this...

I've got a table TABLE_NAME on my D1. pragma table_list does show it, and pragma table_info(TABLE_NAME) indeed shows the correct info:

// This code lives inside a route.tsx file running locally with:
// npm run pages:preview -- --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx
// console.log(await env.DB.prepare(`pragma table_info(TABLE_NAME)`).all());
{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    // ...
  },
  results: [
    {
      cid: 0,
      name: 'id',
      type: 'INT AUTO_INCREMENT',
      notnull: 0,
      dflt_value: null,
      pk: 1
    },
    {
      cid: 1,
      name: 'url',
      type: 'VARCHAR(2048)',
      notnull: 1,
      dflt_value: null,
      pk: 0
    },
// ...
// ...

// This code lives inside a route.tsx file running locally with:
// npm run pages:preview -- --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx
// console.log(await env.DB.prepare(`pragma table_info(TABLE_NAME)`).all());
{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    // ...
  },
  results: [
    {
      cid: 0,
      name: 'id',
      type: 'INT AUTO_INCREMENT',
      notnull: 0,
      dflt_value: null,
      pk: 1
    },
    {
      cid: 1,
      name: 'url',
      type: 'VARCHAR(2048)',
      notnull: 1,
      dflt_value: null,
      pk: 0
    },
// ...
// ...

// This code lives inside a route.tsx file running locally with:
// npm run pages:preview -- --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx
// console.log(await env.DB.prepare(`pragma table_info(TABLE_NAME)`).all());
{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    // ...
  },
  results: [
    {
      cid: 0,
      name: 'id',
      type: 'INT AUTO_INCREMENT',
      notnull: 0,
      dflt_value: null,
      pk: 1
    },
    {
      cid: 1,
      name: 'url',
      type: 'VARCHAR(2048)',
      notnull: 1,
      dflt_value: null,
      pk: 0
    },
// ...
// ...

// This code lives inside a route.tsx file running locally with:
// npm run pages:preview -- --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx
// console.log(await env.DB.prepare(`pragma table_info(TABLE_NAME)`).all());
{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    // ...
  },
  results: [
    {
      cid: 0,
      name: 'id',
      type: 'INT AUTO_INCREMENT',
      notnull: 0,
      dflt_value: null,
      pk: 1
    },
    {
      cid: 1,
      name: 'url',
      type: 'VARCHAR(2048)',
      notnull: 1,
      dflt_value: null,
      pk: 0
    },
// ...
// ...

But then trying to run

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE_NAME returns an empty array, whereas I can see the actual data (not empty) on my CF dashboard, and I can query the records correctly using

npx wrangler d1 execute site-db --command="SELECT * FROM TABLE_NAME"

npx wrangler d1 execute site-db --command="SELECT * FROM TABLE_NAME"

npx wrangler d1 execute site-db --command="SELECT * FROM TABLE_NAME"

npx wrangler d1 execute site-db --command="SELECT * FROM TABLE_NAME"

My wrangler.toml looks like this:

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "site-db"
database_id = "xxxxxxxxxx-xxxx-xxxx-xxxxxxxxxxx"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "site-db"
database_id = "xxxxxxxxxx-xxxx-xxxx-xxxxxxxxxxx"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "site-db"
database_id = "xxxxxxxxxx-xxxx-xxxx-xxxxxxxxxxx"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "site-db"
database_id = "xxxxxxxxxx-xxxx-xxxx-xxxxxxxxxxx"
preview_database_id = "DB"

Any help is appreciated, thanks!

Hi Max. I'm trying to use Drizzle currently with my Pages project to run migrations for my d1 databa

Similar Threads