Been messing around with D1 for storing a websites searchable product list. However, any SQL query r

O

oof2win2•12/20/24, 10:03 AM

what is the write concurrency for d1? i have an event store and a batching queue system with deduplication via d1, but i'm not sure if i should be writing to a queue which writes to d1 after or if it should instead be writing to d1 itself. at peak i can get say 1k updates/s - therefore not sure if batching via queues is a better idea here

JJaykubz Been messing around with D1 for storing a websites searchable product list. Howe...

M

Matt Silverlock•12/20/24, 1:06 PM

If your FTS queries scan multiple columns you need to ensure your indexes cover those columns: https://developers.cloudflare.com/d1/best-practices/use-indexes/

Cloudflare Docs

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) the database has to scan when running a query.

JJaykubz Been messing around with D1 for storing a websites searchable product list. Howe...

M

Matt Silverlock•12/20/24, 1:09 PM

Also: https://www.sqlite.org/fts5.html#prefix_indexes

Ooof2win2 what is the write concurrency for d1? i have an event store and a batching queue...

L

lambrospetrou•12/20/24, 6:03 PM

If this 1k writes/s is for a single database, and you could be writing many rows each time, you are at the border of what you can do. It means you should not spend more than 1ms per query. Doable if they are selects, but for writes it will really depend on many factors.
Batching even more earlier, or using a few database instances would give you a lot more room and flexibility.

O

oof2win2•12/20/24, 6:09 PM

alright makes sense, thanks

T

TrulyUnlucky•12/20/24, 6:53 PM

Is there anything you can do to mitigate

D1_ERROR: Internal error while starting up D1 DB storage caused object to be reset.

D1_ERROR: Internal error while starting up D1 DB storage caused object to be reset.

? The web interface is also unresponsive for this database.

id:

6559cf5b-ea94-4b57-8f05-2228dc0075ea

6559cf5b-ea94-4b57-8f05-2228dc0075ea

TTrulyUnlucky Is there anything you can do to mitigate ``` D1_ERROR: Internal error while star...

D

Dean•12/20/24, 11:54 PM

happens fairly regularly, all you can do is re-attempt your query

J

James•12/20/24, 11:58 PM

Yeah, hopefully we'll get some better documentation around error rates and safely retriable ones as per https://github.com/cloudflare/cloudflare-docs/issues/18485. Until then, unfortunately all you can do is retry or use another more stable database provider if the error rates aren't acceptable for your use-case.

DDean happens fairly regularly, all you can do is re-attempt your query

T

TrulyUnlucky•12/21/24, 8:35 AM

I tried that obviously, but my database seems to be locked up in this state. It’s all it gives back for the past 14 hours. Can’t connect to it through web, wrangler or code.

TTrulyUnlucky I tried that obviously, but my database seems to be locked up in this state. It’...

D

Dean•12/21/24, 8:37 AM

ah, sounds like it's very broken, my D1 databases do that periodically but usually only briefly

K

Kyrre•12/21/24, 8:42 AM

When creating a D1 database, I can provide a location hint like EEUR. However, this region is big, and I want to be close to my users. Is there any way of figuring out which data center actually hosts the database, so I can retry until I actually hit the right location? I know about the

d1 info

d1 info

option, but this only returns EEUR.

Original message was deleted

K

Kyrre•12/21/24, 8:45 AM

My understanding is that D1 is not currently replicated?

K

Kyrre•12/21/24, 8:47 AM

Right, but presumably there is a "primary" data center for the database.

K

Kyrre•12/21/24, 8:49 AM

For one database we have in the WEUR region, it seems to always return MRS in the smart placement header, even though there are several much closer data centers.

K

Kyrre•12/21/24, 8:50 AM

I find it utterly baffling that this information is obscured from us.

TTrulyUnlucky Is there anything you can do to mitigate ``` D1_ERROR: Internal error while star...

L

lambrospetrou•12/21/24, 8:58 AM

Can you check your database now? It should be fixed.

Llambrospetrou Can you check your database now? It should be fixed.

T

TrulyUnlucky•12/21/24, 9:00 AM

It works indeed! Thank you

Bbutter hey guys i've been trying to setup a project that uses nextjs, hono, drizzle and...

T

Trojan•12/22/24, 1:17 PM

this happens because the API for the sqlite d1 file in miniflare is slightly different from the managed postgres api in live. Please share your solution because mine is really dumb

import { defineConfig, type Config } from "drizzle-kit";
import { parse as parseJsonc } from "comment-json";
import { existsSync, readFileSync } from "fs";
import { join } from "path";

const wranglerConfig = parseJsonc(
  readFileSync(join(__dirname, "wrangler.jsonc"), "utf-8"),
) as unknown as WranglerConfig;

const d1Config = wranglerConfig.d1_databases[0];

const env = Object.fromEntries(
  ((filePath) =>
    existsSync(filePath)
      ? readFileSync(filePath, "utf-8")
          .split("\n")
          .filter((line) => line.trim() && !line.startsWith("#"))
          .map((line) =>
            line
              .split(/=(.+)/)
              .filter(Boolean)
              .map((part) => part.trim()),
          )
          .filter((parts) => parts.length === 2)
          .map(([key, value]) => [key, value?.replace(/^["']|["']$/g, "")])
      : [])(join(process.cwd(), ".dev.vars")),
) as Record<string, string>;

/*
 * NOTE: Workaround to make drizzle studio work with D1.
 * https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/
 * Github discussion: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-8115423
 */
export default process.env["NODE_ENV"] === "production"
  ? defineConfig({
      dialect: "sqlite",
      driver: "d1-http",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        accountId: env["CF_ACCOUNT_ID"]!,
        token: env["CF_D1_TOKEN"]!,
        databaseId: d1Config!.database_id!,
      },
    } satisfies Config)
  : defineConfig({
      dialect: "sqlite",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        url: env["DB_LOCAL_PATH"]!,
      },
    } satisfies Config);

import { defineConfig, type Config } from "drizzle-kit";
import { parse as parseJsonc } from "comment-json";
import { existsSync, readFileSync } from "fs";
import { join } from "path";

const wranglerConfig = parseJsonc(
  readFileSync(join(__dirname, "wrangler.jsonc"), "utf-8"),
) as unknown as WranglerConfig;

const d1Config = wranglerConfig.d1_databases[0];

const env = Object.fromEntries(
  ((filePath) =>
    existsSync(filePath)
      ? readFileSync(filePath, "utf-8")
          .split("\n")
          .filter((line) => line.trim() && !line.startsWith("#"))
          .map((line) =>
            line
              .split(/=(.+)/)
              .filter(Boolean)
              .map((part) => part.trim()),
          )
          .filter((parts) => parts.length === 2)
          .map(([key, value]) => [key, value?.replace(/^["']|["']$/g, "")])
      : [])(join(process.cwd(), ".dev.vars")),
) as Record<string, string>;

/*
 * NOTE: Workaround to make drizzle studio work with D1.
 * https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/
 * Github discussion: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-8115423
 */
export default process.env["NODE_ENV"] === "production"
  ? defineConfig({
      dialect: "sqlite",
      driver: "d1-http",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        accountId: env["CF_ACCOUNT_ID"]!,
        token: env["CF_D1_TOKEN"]!,
        databaseId: d1Config!.database_id!,
      },
    } satisfies Config)
  : defineConfig({
      dialect: "sqlite",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        url: env["DB_LOCAL_PATH"]!,
      },
    } satisfies Config);

import { defineConfig, type Config } from "drizzle-kit";
import { parse as parseJsonc } from "comment-json";
import { existsSync, readFileSync } from "fs";
import { join } from "path";

const wranglerConfig = parseJsonc(
  readFileSync(join(__dirname, "wrangler.jsonc"), "utf-8"),
) as unknown as WranglerConfig;

const d1Config = wranglerConfig.d1_databases[0];

const env = Object.fromEntries(
  ((filePath) =>
    existsSync(filePath)
      ? readFileSync(filePath, "utf-8")
          .split("\n")
          .filter((line) => line.trim() && !line.startsWith("#"))
          .map((line) =>
            line
              .split(/=(.+)/)
              .filter(Boolean)
              .map((part) => part.trim()),
          )
          .filter((parts) => parts.length === 2)
          .map(([key, value]) => [key, value?.replace(/^["']|["']$/g, "")])
      : [])(join(process.cwd(), ".dev.vars")),
) as Record<string, string>;

/*
 * NOTE: Workaround to make drizzle studio work with D1.
 * https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/
 * Github discussion: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-8115423
 */
export default process.env["NODE_ENV"] === "production"
  ? defineConfig({
      dialect: "sqlite",
      driver: "d1-http",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        accountId: env["CF_ACCOUNT_ID"]!,
        token: env["CF_D1_TOKEN"]!,
        databaseId: d1Config!.database_id!,
      },
    } satisfies Config)
  : defineConfig({
      dialect: "sqlite",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        url: env["DB_LOCAL_PATH"]!,
      },
    } satisfies Config);

import { defineConfig, type Config } from "drizzle-kit";
import { parse as parseJsonc } from "comment-json";
import { existsSync, readFileSync } from "fs";
import { join } from "path";

const wranglerConfig = parseJsonc(
  readFileSync(join(__dirname, "wrangler.jsonc"), "utf-8"),
) as unknown as WranglerConfig;

const d1Config = wranglerConfig.d1_databases[0];

const env = Object.fromEntries(
  ((filePath) =>
    existsSync(filePath)
      ? readFileSync(filePath, "utf-8")
          .split("\n")
          .filter((line) => line.trim() && !line.startsWith("#"))
          .map((line) =>
            line
              .split(/=(.+)/)
              .filter(Boolean)
              .map((part) => part.trim()),
          )
          .filter((parts) => parts.length === 2)
          .map(([key, value]) => [key, value?.replace(/^["']|["']$/g, "")])
      : [])(join(process.cwd(), ".dev.vars")),
) as Record<string, string>;

/*
 * NOTE: Workaround to make drizzle studio work with D1.
 * https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/
 * Github discussion: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-8115423
 */
export default process.env["NODE_ENV"] === "production"
  ? defineConfig({
      dialect: "sqlite",
      driver: "d1-http",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        accountId: env["CF_ACCOUNT_ID"]!,
        token: env["CF_D1_TOKEN"]!,
        databaseId: d1Config!.database_id!,
      },
    } satisfies Config)
  : defineConfig({
      dialect: "sqlite",
      out: "drizzle",
      schema: "src/db/schema.ts",
      dbCredentials: {
        url: env["DB_LOCAL_PATH"]!,
      },
    } satisfies Config);

M

maguayo•12/24/24, 7:03 AM

If D1 is distributed (read only) why so much diference from just a SELECT from the EU and the US?

Mmaguayo If D1 is distributed (read only) why so much diference from just a SELECT from t...

H

Hello, I’m Allie!•12/24/24, 7:05 AM

It’s not, D1 is single-location for now

HHello, I’m Allie!It’s not, D1 is single-location for now

M

maguayo•12/24/24, 7:07 AM

It's there a timeline? I am migrating everything because the documentation says it's distributed

M

maguayo•12/24/24, 7:09 AM

I thought you could have many instances in each region or something like that

M

maguayo•12/24/24, 7:10 AM

There is a whole section in D1 documentation about read replicas. Is this not true??

FFriday Candour "RETURNING *" not working on D1 how to get around that?

M

maguayo•12/24/24, 7:57 AM

I mean.. they market as D1 is distributed and read replicas.. I expected you know, they have read replicas.

Mmaguayo There is a whole section in D1 documentation about read replicas. Is this not tr...

H

Hello, I’m Allie!•12/24/24, 8:07 AM

It’s a planned feature, but it doesn’t exist yet

FFriday Candour but it's same query right?

M

maguayo•12/24/24, 8:14 AM

Exactly the same query from different locations.

HHello, I’m Allie!It’s a planned feature, but it doesn’t exist yet

M

maguayo•12/24/24, 8:15 AM

Can we know if it's planned for Q1 or more into the future?

FFriday Candour omg cloudflare

M

maguayo•12/24/24, 10:17 AM

Yeah.. Time to refactor everything since I counted with read replicas since their documentation mentions them...

TTrojan this happens because the API for the sqlite d1 file in miniflare is slightly dif...

T

theo•12/24/24, 6:12 PM

I might have a tool that helps a bit with managing the env/config

T

theo•12/24/24, 6:14 PM

loads config like dotenv but also has validations, and a nice wrangler integration - https://dmno.dev/

Ttheo I might have a tool that helps a bit with managing the env/config

T

Trojan•12/24/24, 8:38 PM

dang thanks! i’ll check it out

TTrojan dang thanks! i’ll check it out

T

theo•12/24/24, 8:44 PM

the cloudflare integration is brand new, so happy to help and work through any issues

T

Trojan•12/24/24, 8:52 PM

“Being able to see all of these values and which one is currently active will save you tons of headaches”

huge. im tired of “zero config” “common sense” “best practice” “no code” etc

FFriday Candour "RETURNING *" not working on D1 how to get around that?

O

o7•12/26/24, 11:39 PM

it works for me

A

Amodeus R.•12/27/24, 12:59 AM

I was trying out the d1 get started guide and the first command to create a d1 project doesn't work. I tried using npm and it throw a 404 Error. I tried using npx also but a similar error happened, although a little bit different as you can see on the terminal. I tried installing create-cloudfare and it also didn't work. What do I need to do to make it work?

AAmodeus R.I was trying out the d1 get started guide and the first command to create a d1 p...

J

James•12/27/24, 1:28 AM

It looks like you typo’d cloudflare to cloudfare

A

Amodeus R.•12/27/24, 2:36 AM

Oh, what a rookie mistake haha thanks for pointing it out

K

kamama•12/27/24, 5:16 AM

If I got multiple databases in local, how can I get the exact SQLite path for specific database ?

I

iqorex•12/27/24, 8:27 AM

Hey i have db per user case, and now i need to check it with cron if a user have some event scheduled to do some processing and send emails. Is there any smart way of querying all databases at once?

J

James•12/27/24, 2:08 PM

sadly not really. If we had dynamic bindings this'd be easier, which were a "before GA" promise as per https://canary.discord.com/channels/595317990191398933/992060581832032316/1164575886520090684, but still nothing to report there.

JJames sadly not really. If we had dynamic bindings this'd be easier, which were a "bef...

I

iqorex•12/27/24, 3:12 PM

thanks

Been messing around with D1 for storing a websites searchable product list. However, any SQL query r

Similar Threads

Similar Threads

Similar Threads