hey folks, what's the typical workflow for teams working with D1? I was able to get pages+d1 working

Ddev hey folks, what's the typical workflow for teams working with D1? I was able to ...

A

Ashley•10/5/24, 9:09 AM

I would personally spin up a whole new environment per branch like you’re doing but then as it’s a serverless platform, it’s pretty easy to have a script that creates new resources, do a bit of Bash to extract the bindings from the create commands, and then put in wrangler.toml and then do your deploy. You’d probably want some clean up to run on merge to delete the resources too (if you’re using GH Actions or something)

P

preetramsha•10/5/24, 12:24 PM

PLEASE HELP!

I am not able to delete (drop) a table on production.

P

preetramsha•10/5/24, 12:27 PM

here it is

P

preetramsha•10/5/24, 12:28 PM

I wanted to remove some foreign key constrains that's why I was making the new table

P

preetramsha•10/5/24, 12:28 PM

what to do now?

Original message was deleted

P

preetramsha•10/5/24, 12:28 PM

hello you there?

AAshley I would personally spin up a whole new environment per branch like you’re doing ...

D

devOP•10/5/24, 3:38 PM

Yeah.. I’m thinking this is the best approach as well.. however, that only fixes the preview part. Local dev is still not ideal. It means I’ll probably be resetting the local database all the time

Ddev Yeah.. I’m thinking this is the best approach as well.. however, that only fixe...

J

James•10/5/24, 4:00 PM

you could script local dev to use a different wrangler state d1 folder per branch

Ddev Yeah.. I’m thinking this is the best approach as well.. however, that only fixe...

A

Ashley•10/5/24, 4:19 PM

I tend to do that anyway, makes it nice and clean per branch with no contamination possible - and if you need state in the DB for config etc. just put it in a migration so it's always ready to go

AAshley I tend to do that anyway, makes it nice and clean per branch with no contaminati...

D

devOP•10/5/24, 4:54 PM

any scripts you can share so I can reuse some of it?

JJames you could script local dev to use a different wrangler state d1 folder per branc...

D

devOP•10/5/24, 4:55 PM

interesting.. so every branch gets its own local wrangler state locally as well.. I kinda like this. have you implemented this yourself?

Ddev interesting.. so every branch gets its own local wrangler state locally as well....

J

James•10/5/24, 5:03 PM

yeah i run this in through my package json, caveat folder path is my local ref

const { execSync } = require('child_process');
const { spawnSync } = require('child_process');
const fs = require('fs');
const path = require('path');

// Get current git branch
const branch = execSync('git rev-parse --abbrev-ref HEAD').toString().trim();

// Sanitize branch name to be used as a folder name
const sanitizedBranch = branch.replace(/[^a-zA-Z0-9-_]/g, '_');

// Define the persist folder path
const persistFolder = path.resolve(`../whateverFolder/.wrangler/state/${sanitizedBranch}/`);

// Ensure the persist folder exists
if (!fs.existsSync(persistFolder)) {
  fs.mkdirSync(persistFolder, { recursive: true });
  console.log(`Created persist folder: ${persistFolder}`);
} else {
  console.log(`Persist folder already exists: ${persistFolder}`);
}

console.log(`Persisting data to: ${persistFolder}`);

// Run wrangler dev with the dynamic persist folder
const result = spawnSync('wrangler', [
  'dev',
  `--persist-to=${persistFolder}`
], { stdio: 'inherit' });

if (result.error) {
  console.error('Error running wrangler:', result.error);
  process.exit(1);
}

const { execSync } = require('child_process');
const { spawnSync } = require('child_process');
const fs = require('fs');
const path = require('path');

// Get current git branch
const branch = execSync('git rev-parse --abbrev-ref HEAD').toString().trim();

// Sanitize branch name to be used as a folder name
const sanitizedBranch = branch.replace(/[^a-zA-Z0-9-_]/g, '_');

// Define the persist folder path
const persistFolder = path.resolve(`../whateverFolder/.wrangler/state/${sanitizedBranch}/`);

// Ensure the persist folder exists
if (!fs.existsSync(persistFolder)) {
  fs.mkdirSync(persistFolder, { recursive: true });
  console.log(`Created persist folder: ${persistFolder}`);
} else {
  console.log(`Persist folder already exists: ${persistFolder}`);
}

console.log(`Persisting data to: ${persistFolder}`);

// Run wrangler dev with the dynamic persist folder
const result = spawnSync('wrangler', [
  'dev',
  `--persist-to=${persistFolder}`
], { stdio: 'inherit' });

if (result.error) {
  console.error('Error running wrangler:', result.error);
  process.exit(1);
}

const { execSync } = require('child_process');
const { spawnSync } = require('child_process');
const fs = require('fs');
const path = require('path');

// Get current git branch
const branch = execSync('git rev-parse --abbrev-ref HEAD').toString().trim();

// Sanitize branch name to be used as a folder name
const sanitizedBranch = branch.replace(/[^a-zA-Z0-9-_]/g, '_');

// Define the persist folder path
const persistFolder = path.resolve(`../whateverFolder/.wrangler/state/${sanitizedBranch}/`);

// Ensure the persist folder exists
if (!fs.existsSync(persistFolder)) {
  fs.mkdirSync(persistFolder, { recursive: true });
  console.log(`Created persist folder: ${persistFolder}`);
} else {
  console.log(`Persist folder already exists: ${persistFolder}`);
}

console.log(`Persisting data to: ${persistFolder}`);

// Run wrangler dev with the dynamic persist folder
const result = spawnSync('wrangler', [
  'dev',
  `--persist-to=${persistFolder}`
], { stdio: 'inherit' });

if (result.error) {
  console.error('Error running wrangler:', result.error);
  process.exit(1);
}

const { execSync } = require('child_process');
const { spawnSync } = require('child_process');
const fs = require('fs');
const path = require('path');

// Get current git branch
const branch = execSync('git rev-parse --abbrev-ref HEAD').toString().trim();

// Sanitize branch name to be used as a folder name
const sanitizedBranch = branch.replace(/[^a-zA-Z0-9-_]/g, '_');

// Define the persist folder path
const persistFolder = path.resolve(`../whateverFolder/.wrangler/state/${sanitizedBranch}/`);

// Ensure the persist folder exists
if (!fs.existsSync(persistFolder)) {
  fs.mkdirSync(persistFolder, { recursive: true });
  console.log(`Created persist folder: ${persistFolder}`);
} else {
  console.log(`Persist folder already exists: ${persistFolder}`);
}

console.log(`Persisting data to: ${persistFolder}`);

// Run wrangler dev with the dynamic persist folder
const result = spawnSync('wrangler', [
  'dev',
  `--persist-to=${persistFolder}`
], { stdio: 'inherit' });

if (result.error) {
  console.error('Error running wrangler:', result.error);
  process.exit(1);
}

JJames yeah i run this in through my package json, caveat folder path is my local ref ...

D

devOP•10/5/24, 6:49 PM

Thanks. I appreciate it. I have a script to sync prisma migrations with d1 as well. I’ll share when I’m back at my pc.

D

devOP•10/5/24, 6:49 PM

I feel most of these DX issues are solvable with scripts, but it’s a bummer that it’s not baked in

M

matt•10/6/24, 2:16 AM

lets say you have a json column of str -> str, with unknown keys can you create an index per each key?

K

Kyzer•10/6/24, 11:09 PM

Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2e79) metrics please?
Apparently I've gone through 2.16B Rows read this month on a database with a total of about ~20k rows.
I've checked the insights and this is the main culprit, but running this command returns about 6k rows (total of ~12k reads as the join is pretty much a 1 to 1).

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

Thanks

D

devOP•10/7/24, 2:41 AM

This is a quick Bun script to add a migration in d1, but using prisma to calculate the diff. You can adjust this to something else in case you're not using Bun.

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

KKyzer Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2...

A

Acе•10/7/24, 12:47 PM

Seems like you don’t have indexes on your columns so it’s reading through the entire table to find the row to join on

A

Acе•10/7/24, 12:50 PM

6266 records in entry multiplied by 6266 records in engineeringEntry, and ran 62 times would equal around 2 billion

AAcе Seems like you don’t have indexes on your columns so it’s reading through the en...

K

Kyzer•10/7/24, 1:30 PM

Ugh, thanks. Think I was assuming the primary key index would have been fine, forgetting the joining table isn't actually using the primary key column

L

LLT•10/7/24, 6:47 PM

What is the recommended way of getting accurate billing metrics data, without generating additional reads through the dashboard?

Should we use the GraphQL Analytics API?

And from what point on will the D1 dashboard generate additional rowsRead? Already from the first overview page on, where the databases are listed?

N

nejc•10/8/24, 4:24 AM

hi all. I am using import API for D1 https://developers.cloudflare.com/api/operations/cloudflare-d1-import-database. Does anybody used it before and are any limits on the file size? I am getting 500 internal server all the time. Thank you

LLLT What is the recommended way of getting accurate billing metrics data, without ge...

C

Cyb3r-Jak3•10/8/24, 12:03 PM

GraphQL is the recommend way.

A

Alexander Hupfer•10/8/24, 1:09 PM

Hi, I have applied migrations via "npx wrangler d1 migrations apply remix-db --local" - how can I connect to the local DB via CLI or otherwise to check the structure?

AAlexander Hupfer Hi, I have applied migrations via "npx wrangler d1 migrations apply remix-db --l...

C

Cyb3r-Jak3•10/8/24, 1:11 PM

I believe the command is

npx wrangler d1 exec —local <sql command>

npx wrangler d1 exec —local <sql command>

npx wrangler d1 exec —local <sql command>

npx wrangler d1 exec —local <sql command>

A

Alexander Hupfer•10/8/24, 1:17 PM

thanks,

 npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'"

npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'"

 npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'"

npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'" did the trick

AAlexander Hupfer thanks,` npx wrangler d1 execute remix-db --local --command "SELECT name FROM sq...

J

James•10/8/24, 2:40 PM

the sqlite file is sitting in the .wrangler folder you could use any db browser / tool that supports opening sqlite files as well, i.e. datagrip

V

Vinay Reddy•10/9/24, 5:13 PM

Hey, I have implement llama and flux models and deployed as workers , now how can i save every response from them in D1?

1

1984 Ford Laser•10/10/24, 1:17 AM

Has anyone used D1 with yootheme pro and zoolanders essentials as a custom source? It asks for a host and port, which the docs say I can get from the TCP socket API, but unsure if that applies to my use case. If this option isnt suitable, I will play around with delivering a xml/csv file to yootheme, using a worker to pull the data from D1 and do the formatting. Appreciate any assistance

11984 Ford Laser Has anyone used D1 with yootheme pro and zoolanders essentials as a custom sourc...

C

Cyb3r-Jak3•10/10/24, 2:06 AM

The only way you can connect with D1 is the HTTP API. They would need to implement support for it

S

shun•10/10/24, 2:15 AM

Has anyone linked to Redash or Metabase using D1 data? I'd like to know if there is a good way to do this.

T

toadfans•10/10/24, 9:24 AM

What is this error mean? Anything going on?

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

Ttoadfans What is this error mean? Anything going on? > D1_ERROR: D1 storage operation ex...

O

o7•10/10/24, 10:52 AM

always happens to me early morning on a simple select on a pk with a very low traffic db

Oo7 always happens to me early morning on a simple select on a pk with a very low tr...

T

toadfans•10/10/24, 12:46 PM

Same here

N

NickP•10/10/24, 2:28 PM

Me too

S

Sam•10/10/24, 5:07 PM

Is the D1 HTTP API down right now?

SSam Is the D1 HTTP API down right now?

C

Chaika•10/10/24, 5:17 PM

Thanks for the extra info, raised

SSam Is the D1 HTTP API down right now?

A

ajgeiss0702•10/10/24, 5:33 PM

this is also appears to be effecting the dasboard. You can see database and their tables, but anything other than that wont load

Aajgeiss0702 this is also appears to be effecting the dasboard. You can see database and thei...

C

Chaika•10/10/24, 5:42 PM

yea they use basically the same api. It looks like the query api is fine (if using direct) but not getting db/table info, which in the dash also prevents using console/metrics/etc tabs. They ack'd the issue

N

nullptr•10/10/24, 5:45 PM

Is the GUI broken for D1?

Nnullptr Is the GUI broken for D1?

C

Chaika•10/10/24, 5:46 PM

yes see above

C

Chaika•10/10/24, 5:49 PM

https://www.cloudflarestatus.com/incidents/p1jpzdbz4sbc

D1 API Performance issues - Increased Errors

C

Chaika•10/10/24, 5:59 PM

Seems fixed now

N

Norris Nam•10/11/24, 2:48 AM

Can I use Navicat to connect to Cloudflare D1 Database?

NNorris Nam Can I use Navicat to connect to Cloudflare D1 Database?

S

Sameer Ali•10/11/24, 6:13 AM

yes, there is a .sqlite file in .wrangler directory. you can open that in Navicat.

A

aj•10/11/24, 12:25 PM

Hi, could somebody help in short with D1? Using the standard API is really slow. Should I setup a new API using workers and connect it to D1? Or how does it work, so it's quick? A random VPS server is much quicker than the default D1. I don't want to believe that

hey folks, what's the typical workflow for teams working with D1? I was able to get pages+d1 working

Similar Threads

Similar Threads

Similar Threads