Transactions in D1 today requires the use of .batch(), right? I was hoping that the transaction api

ZZn4rK Transactions in D1 today requires the use of .batch(), right? I was hoping that...

L

lambrospetrou•12/17/24, 10:57 AM

The DO transactions API works really well because you access a local SQLite database, so you get very good performance even with tens/hundreds of queries. Unless you mess it up with async IO yourself.

D1 is a remote database, away from your Worker. This transaction API doesn't work well with that since you would be making multiple remote calls each time. Not saying there isn't an API that can work OK-ish, but that one isn't it.

Z

Zn4rKOP•12/17/24, 10:59 AM

Thank you! Then I think I'm beginning to understand the limitations and differences between DO SQLite and D1 better

Z

Zn4rKOP•12/17/24, 10:59 AM

Very exciting stuff!

ZZn4rK Thank you! Then I think I'm beginning to understand the limitations and differen...

L

lambrospetrou•12/17/24, 11:00 AM

Local vs remote is the key difference. Everything else derives from that.

You choose D1 for the usability layer (UI, insights for slow queries and metrics, wrangler commands, import/export), and you choose DO SQLite if you really want the local access + dynamic bindings benefits and are OK building the usability layer yourself (or using a library that provides it for you).

Llambrospetrou Local vs remote is the key difference. Everything else derives from that. You c...

Z

Zn4rKOP•12/17/24, 11:12 AM

Nice! Thank you!

Here's where I found the timeline btw: https://blog.cloudflare.com/sqlite-in-durable-objects/#what-about-d1

By the end of 2024, D1 will support automatic read replication for scalability and low-latency access around the world. If this managed model appeals to you, use D1.

Aandifined.dev I‘m looking for an architecture example of how to scale D1 per user as listed wi...

J

James•12/17/24, 4:26 PM

I use DO per user, but in my case it works nicely since user accounts are separate tenants. Each user registration runs the schema startup script / migrations to create the db on the fly. I ensure the DO ID is deterministic based on some user specific key. Every subsequent login / request, the key is unpacked / looked up and then I can re-access the DO dynamically.

P

Palash Bansal•12/17/24, 6:32 PM

Hello, I am trying to run a query in D1 with multiple

UNION ALL SELECT

UNION ALL SELECT

UNION ALL SELECT

UNION ALL SELECT terms. According to SQLite documentation, the number of terms in the query is limited by the value

SQLITE_MAX_COMPOUND_SELECT

SQLITE_MAX_COMPOUND_SELECT

SQLITE_MAX_COMPOUND_SELECT

SQLITE_MAX_COMPOUND_SELECT which defaults to 500. But in D1 this is set to 5 and any query with more than 5 terms (like for inserting multiple values at once) fail with

too many terms in compound SELECT

too many terms in compound SELECT

too many terms in compound SELECT

too many terms in compound SELECT.
Is there any reason for setting this to so low(like any other limitations) and is there way to increase this now or in the future with D1 or SQLite in DO?

Original message was deleted

P

Palash Bansal•12/17/24, 6:46 PM

I see thanks. Also saw its a recursive algorithm so makes sense now

A

ajgeiss0702•12/18/24, 3:15 AM

ive been seeing a bunch of

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost. errors being thrown since friday. Any idea if this is on d1 or my side?

A

ajgeiss0702•12/18/24, 3:18 AM

a similar issue appears to be happening with durable objects itself. Isn't D1 based on DO? so might actually be a DO issue?

Llambrospetrou Local files are not deployed in production. > But on production the 5 records a...

K

karenr•12/18/24, 9:00 AM

Sorry for the confusion.

The production has 21 records, see the attached. And the local in my machine has 5 records. If I am running the local using the wrangler dev + cloudflare since I am using opennext it behaves as expected, in the UI it only shows 5 records. but when i deploy with the wrangler deploy + cloudflare, say I fetched 21 records from an API. It still displays the 5 records from the local.

I used the --remote flag when migrating but still displaying the 5 records from the local.

I am using swr to reflect the real time changes from the database and storage. Not sure if this affects it.

Kkarenr Sorry for the confusion. The production has 21 records, see the attached. And t...

L

lambrospetrou•12/18/24, 9:06 AM

If the database in the cloudflare dashboard shows you the right rows, then it means your D1 database is correct.
So, the issue is in the code build process.

I don't use opennext or swr so cannot comment for those, but make sure when you build your application before deploying that it does not use the local database to build up the content of your site in some way and you fetch the data at runtime using the correct database in production. If you try to fetch at build time, then it's very likely using your local database. Again, just a guess though, so check your build process.

Llambrospetrou If the database in the cloudflare dashboard shows you the right rows, then it me...

K

karenr•12/18/24, 9:09 AM

I think it is. Cos when I delete the local generated sql from .wrangler. it displays the 21 records in the UI.

Kkarenr I think it is. Cos when I delete the local generated sql from .wrangler. it disp...

K

karenr•12/18/24, 10:27 AM

this is what solve the issue

import { defineConfig } from "drizzle-kit";
import type { Config } from "drizzle-kit";
import fs from "node:fs";
import path from "node:path";

function getLocalD1DB() {
try {
const basePath = path.resolve(".wrangler");
const dbFile = fs
.readdirSync(basePath, { encoding: "utf-8", recursive: true })
.find((f) => f.endsWith(".sqlite"));

if (!dbFile) {
throw new Error(

.sqlite file not found in ${basePath}

.sqlite file not found in ${basePath}

.sqlite file not found in ${basePath}

.sqlite file not found in ${basePath});
}

const url = path.resolve(basePath, dbFile);
return url;
} catch (err) {
console.log(

Error ${err}

Error ${err}

Error ${err}

Error ${err});
}
}

export default defineConfig({
dialect: "sqlite",
schema: "./schemas/drizzle.ts",
out: "./drizzle",
...(process.env.CLOUDFLARE_D1_ACCOUNT_ID
? {
driver: "d1-http",
dbCredentials: {
accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
databaseId: process.env.DATABASE,
token: process.env.CLOUDFLARE_D1_API_TOKEN,
},
}
: {
dbCredentials: {
url: getLocalD1DB(),
},
}),
}) satisfies Config;

Aajgeiss0702 ive been seeing a bunch of `D1_ERROR: Network connection lost.` errors being thr...

A

ajgeiss0702•12/18/24, 11:31 PM

the error has changed to

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

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

Aajgeiss0702 the error has changed to `D1_ERROR: D1 DB storage operation exceeded timeout whi...

C

Chaika•12/18/24, 11:42 PM

Anything interesting with metrics? Does it happen even on simple queries, consistently, etc?
The Network Connection Lost error just does happen every so often..unfortunately, but it depends on frequency and such.

A

ajgeiss0702•12/18/24, 11:44 PM

its not consistent. there doesn't appear to be anything out of the ordinary on the db's metrics

A

ajgeiss0702•12/18/24, 11:44 PM

well, that error specifically seems to only happen on one query, but when it happens on that one query seems random

A

ajgeiss0702•12/18/24, 11:45 PM

select * from table where timestamp > ? order by timestamp desc

select * from table where timestamp > ? order by timestamp desc

select * from table where timestamp > ? order by timestamp desc

select * from table where timestamp > ? order by timestamp desc

C

Chaika•12/18/24, 11:46 PM

index on timestamp? (or is table small enough to not need it?)
npx wrangler d1 insights <db> can be help as well to look through expensive queries

A

ajgeiss0702•12/18/24, 11:47 PM

that table should get cleared at the end of every week, so I would be suprised if it had more than a few thousand rows at the absolute most

A

ajgeiss0702•12/18/24, 11:48 PM

yeah that table currently has 0 rows

T

theo•12/19/24, 5:23 AM

I'm working on some tooling which is handling very sensitive data on behalf of my customers, along with user-defined workers (Workers for Platforms). But I'd like to ensure that I never have access to that data. Obviously I can encrypt the data with a key that I dont have, but I need workers to be able to access the data, and I dont want the user to have to manage that key either. Any experience / advice for doing something like this?

Aajgeiss0702 the error has changed to `D1_ERROR: D1 DB storage operation exceeded timeout whi...

M

Max (@rozenmd)•12/19/24, 9:49 AM

whats your database_id?

Ttheo I'm working on some tooling which is handling very sensitive data on behalf of m...

L

lambrospetrou•12/19/24, 10:02 AM

If you want encryption then someone has to have the key, otherwise you won't be able to decrypt the data. You could in theory add a secret in your worker and "lose it" so that it's only in the worker dashboard, but then if you ever need to put that into a different worker, or decrypt the data outside of that worker you cannot do anything, so do NOT do this.
You could infer keys from something unique to each user that they anyway provide into your application, as an alternative.

MMax (@rozenmd)whats your database_id?

A

ajgeiss0702•12/19/24, 4:26 PM

03bb813c-a64f-4a34-9483-dc285ab59ba2

A

ajgeiss0702•12/19/24, 4:28 PM

the error probably wont happen again until tonight at the earliest. Will most likely happen tomorrow around December 21, 2024, as that is my peak time and it seems to happen more often with more requests

Llambrospetrou If you want encryption then someone has to have the key, otherwise you won't be ...

T

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

Yeah I had been thinking about trying something like that. I feel like there are some interesting capabilities that could be opened up with some tooling like this that wouldn't be too hard to implement. Basically having the worker fully own the key and access to it. Would also need to think through how the worker code gets upgraded...

Basically trying to get some of the benefits of using a decentralized programmable key pair without having to involve blockchain... Relying on the Cloudflare's builtin security rather than something much more complicated. (see https://www.litprotocol.com/)

T

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

what you mentioned about "losing" the key is kind of how it works with Lit. I provision a new keypair, initially owned by me - so I can update the access rules/code. I dont actually get the key itself (it is never exposed), but I have full access to use it and update access rules. Then once it's created and set up properly I transfer the ownership to itself.

You could imagine something similar with a worker. It could implement some kind of standardized API, and then be put into a mode like this. In order to update the worker - youd need to get some kind of token that only the worker can returns - so it can manage access of its own updates.

J

Jaykubz•12/19/24, 8:32 PM

Been messing around with D1 for storing a websites searchable product list. However, any SQL query reads all rows and at 19k rows that would eat away at daily limits.

Was messing with FTS for a table and my worker api calls say it is only querying the exact amount of rows. On the back end in one of the FTS tables are there 19k row reads happening and only ~40 on the main table due to indexs?

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

Transactions in D1 today requires the use of .batch(), right? I was hoping that the transaction api

Similar Threads

Similar Threads

Similar Threads