Can I create a Pages binding using wrangler or only with the Pages dashboard? Where this is coming

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

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?

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

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

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

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

fun fact it also converts

number[]

number[]

s in line 227-230

Original message was deleted

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.

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
    },
// ...
// ...

But then trying to run

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"

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"

Any help is appreciated, thanks!

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

This is the entire pages:preview command my bad

npm run pages:build && wrangler pages dev .vercel/output/static --compatibility-date=2024-01-17 --compatibility-flag=nodejs_compat --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx

npm run pages:build && wrangler pages dev .vercel/output/static --compatibility-date=2024-01-17 --compatibility-flag=nodejs_compat --d1 DB=xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx

Where pages:build is just

npx @cloudflare/next-on-pages

npx @cloudflare/next-on-pages

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

Haha, thanks! Well that solves it

So https://developers.cloudflare.com/d1/configuration/local-development/ is outdates then?

"""
To access your production (remote) database, pass the --remote flag when calling wrangler dev. Any changes you make when running in --remote mode cannot be undone.
"""

This gave me the impression I would've been able to connect to the db locally

Original message was deleted

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

Thank you very much!

They weren't joking when they said cf discord was incredibly active and helpful

Original message was deleted