Waiting for your reply, my service has been interrupted for 3 hours and customers have been asking m

神神龙出海 Waiting for your reply, my service has been interrupted for 3 hours and customer...

M

Max (@rozenmd)•8/28/24, 9:36 AM

the DB should be back up now

神

神龙出海OP•8/28/24, 9:39 AM

May I ask what's the reason?

M

Max (@rozenmd)•8/28/24, 9:42 AM

We found a bug last week that in rare cases would stop D1 DBs from booting up successfully, the fix to that bug isn't 100% rolled out yet across the network

T

Trad•8/28/24, 9:47 AM

hi everyone, I am planning to use Honojs with worker and d1 database for my new project.
There are two topics that I would like to get opinions about this project. I plan to get millions of reservations when I will keep data for tourism businesses.
Is D1 Database the right choice for this?
If D1 is the right choice, is there a way to provide IF NOT EXIST control when I add a column with the ALTER TABLE command? I am writing the database structure in .sql format on the local and I am migrating remotely and locally with wrangler.

V

veeque•8/28/24, 2:34 PM

In SvelteKit I'm using Drizzle ORM

db.transaction(async (tx) => {
  const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });
});

db.transaction(async (tx) => {
  const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });
});

db.transaction(async (tx) => {
  const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });
});

db.transaction(async (tx) => {
  const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });
});

This code doesn't work, but doing it without a transaction (remove the wrapper and use

db

db

db

db rather than

tx

tx

tx

tx does.

This is the error I'm getting in dev:

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.

How do I go about using this JS API?

V

veeque•8/28/24, 2:35 PM

I also get this:

Error: D1_ERROR: not authorized: SQLITE_AUTH
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SQLiteD1Session.transaction (...) {
  [cause]: Error: not authorized: SQLITE_AUTH
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.run (cloudflare-internal:d1-api:178:29) {
    [cause]: undefined
  }
}

Error: D1_ERROR: not authorized: SQLITE_AUTH
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SQLiteD1Session.transaction (...) {
  [cause]: Error: not authorized: SQLITE_AUTH
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.run (cloudflare-internal:d1-api:178:29) {
    [cause]: undefined
  }
}

Error: D1_ERROR: not authorized: SQLITE_AUTH
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SQLiteD1Session.transaction (...) {
  [cause]: Error: not authorized: SQLITE_AUTH
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.run (cloudflare-internal:d1-api:178:29) {
    [cause]: undefined
  }
}

Error: D1_ERROR: not authorized: SQLITE_AUTH
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SQLiteD1Session.transaction (...) {
  [cause]: Error: not authorized: SQLITE_AUTH
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.run (cloudflare-internal:d1-api:178:29) {
    [cause]: undefined
  }
}

Vveeque In SvelteKit I'm using Drizzle ORM ```ts db.transaction(async (tx) => { const...

S

Sam•8/28/24, 2:39 PM

I don't think transactions are supported by D1

https://orm.drizzle.team/docs/batch-api

Drizzle ORM - Batch

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

V

veeque•8/28/24, 3:22 PM

nice find, thanks! i'll look into it now

V

veeque•8/28/24, 3:24 PM

the only thing i don't understand is how to use the returning value

V

veeque•8/28/24, 3:25 PM

ince i just plonk my statements into the array I don't see a way to use the returned value in the next statement

Vveeque ince i just plonk my statements into the array I don't see a way to use the retu...

H

Hurby•8/28/24, 4:37 PM

why?

H

Hurby•8/28/24, 4:37 PM

can you elaborate?

V

veeque•8/28/24, 4:39 PM

i currently do this:

const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });

const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });

const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });

const returned = await tx
    .insert(schema.universities)
    .values({ handle: form.data.handle })
    .returning({ id: schema.universities.id });

  await tx.insert(schema.localizedUniversityNames).values({
    university: returned[0].id,
    languageTag: form.data.languageTag,
    name: form.data.name,
  });

as you can see the second statement uses

returned

returned

returned

returned. If i have to put them into an array like so:

db.bulk([
  db.insert(schema.universities).values({ handle: form.data.handle }).returning({ id: schema.universities.id }),
  db.insert(schema.localizedUniversityNames).values({university: returned[0].id,languageTag: form.data.languageTag,name: form.data.name}),
]);

db.bulk([
  db.insert(schema.universities).values({ handle: form.data.handle }).returning({ id: schema.universities.id }),
  db.insert(schema.localizedUniversityNames).values({university: returned[0].id,languageTag: form.data.languageTag,name: form.data.name}),
]);

db.bulk([
  db.insert(schema.universities).values({ handle: form.data.handle }).returning({ id: schema.universities.id }),
  db.insert(schema.localizedUniversityNames).values({university: returned[0].id,languageTag: form.data.languageTag,name: form.data.name}),
]);

db.bulk([
  db.insert(schema.universities).values({ handle: form.data.handle }).returning({ id: schema.universities.id }),
  db.insert(schema.localizedUniversityNames).values({university: returned[0].id,languageTag: form.data.languageTag,name: form.data.name}),
]);

i don't have a

returned

returned

returned

returned variable i can now use, so what do i do?

Vveeque i currently do this: ```ts const returned = await tx .insert(schema.univers...

H

Hurby•8/28/24, 4:51 PM

hmm yea, I don't think it is possible to

H

Hurby•8/28/24, 4:52 PM

you can try using raw sql but idk

JJames We use neon + workers, with Postgres.js very successfully. What are you building...

T

tester123•8/28/24, 5:32 PM

I'm trying to connect neondb basically with cloudflare workers via Hyperdrive using the serverless driver. My issue is that the neon serverless driver does NOT work with Hyperdrive. Moreover, I don't see any spectacular low latency, rather something like 200-300ms for a single query to be honest, thus I was thinking whether D1 would be better in those terms. However I generally prefer neondb to be honest as well. Have you faced such issue maybe? Has anyone tried to connect hyperdrive with neondb successfully using the serverless driver?

J

James•8/28/24, 5:33 PM

I'd recommend just using https://github.com/porsager/postgres with Neon + Hyperdrive. There's no real benefit using the serverless driver in this case.

J

James•8/28/24, 5:34 PM

^ That's my exact setup and it works well. I don't always use Hyperdrive, but I do use Neon + postgres.js very successfully.

T

tester123•8/28/24, 5:34 PM

may I please ask what is the latency you get approximately for a simple query?

JJames I'd recommend just using https://github.com/porsager/postgres with Neon + Hyperd...

T

tester123•8/28/24, 5:35 PM

in fact yes, postgres.js works, but was giving me a latency of 200-300ms for a simple query

J

James•8/28/24, 5:36 PM

With hyperdrive? Moving to #hyperdrive might make more sense here

T

tester123•8/28/24, 5:36 PM

without hyperdrive was like 600+ms

JJames With hyperdrive? Moving to #hyperdrive might make more sense here

T

tester123•8/28/24, 5:38 PM

may I please ask which web framework you are using? I'm thinking to migrate to hono.js btw

J

James•8/28/24, 5:39 PM

we use hono for all of our workers projects these days

JJames we use hono for all of our workers projects these days

T

tester123•8/28/24, 5:40 PM

that's interesting, thanks for sharing:)

M

Max (@rozenmd)•8/28/24, 8:49 PM

we also use Hono for all our D1 projects fwiw

J

jose luis•8/29/24, 12:35 AM

When using the workers' vitest integration, should i be able to access my local d1 database for testing?

import { env } from "cloudflare:test";
import { describe, it, expect } from "vitest";
import { Env } from "../src/index.js";

//This is in the docs for env
declare module "cloudflare:test" {
    interface ProvidedEnv extends Env {}
}

it("Prepared statements", async () => {
//test some function that uses env, but doesn't query d1, and it works
...
//test some function which queries my local d1 db
// doesn't found the table/s used in my query, sql error.
const table_list = await env.MY_DB.exec("pragma table_list;");
console.log("Table list:", table_list);
// which prints: Table list: { count: 1, duration: 0 }
})

import { env } from "cloudflare:test";
import { describe, it, expect } from "vitest";
import { Env } from "../src/index.js";

//This is in the docs for env
declare module "cloudflare:test" {
    interface ProvidedEnv extends Env {}
}

it("Prepared statements", async () => {
//test some function that uses env, but doesn't query d1, and it works
...
//test some function which queries my local d1 db
// doesn't found the table/s used in my query, sql error.
const table_list = await env.MY_DB.exec("pragma table_list;");
console.log("Table list:", table_list);
// which prints: Table list: { count: 1, duration: 0 }
})

import { env } from "cloudflare:test";
import { describe, it, expect } from "vitest";
import { Env } from "../src/index.js";

//This is in the docs for env
declare module "cloudflare:test" {
    interface ProvidedEnv extends Env {}
}

it("Prepared statements", async () => {
//test some function that uses env, but doesn't query d1, and it works
...
//test some function which queries my local d1 db
// doesn't found the table/s used in my query, sql error.
const table_list = await env.MY_DB.exec("pragma table_list;");
console.log("Table list:", table_list);
// which prints: Table list: { count: 1, duration: 0 }
})

import { env } from "cloudflare:test";
import { describe, it, expect } from "vitest";
import { Env } from "../src/index.js";

//This is in the docs for env
declare module "cloudflare:test" {
    interface ProvidedEnv extends Env {}
}

it("Prepared statements", async () => {
//test some function that uses env, but doesn't query d1, and it works
...
//test some function which queries my local d1 db
// doesn't found the table/s used in my query, sql error.
const table_list = await env.MY_DB.exec("pragma table_list;");
console.log("Table list:", table_list);
// which prints: Table list: { count: 1, duration: 0 }
})

There is only one recipe for

d1

d1

d1

d1 in the github repo linked in the docs, and it creates the db for testing using migrations. I expected to test my local d1 directly and this will know my local db from my bindings (toml is passed to

vitest.config.mts

vitest.config.mts

vitest.config.mts

vitest.config.mts).

HHurby you can try using raw sql but idk

V

veeque•8/29/24, 8:24 AM

I don't know, as it seems that doing a batch is more for performance reasons rather than acid-ity

D

diffthanevery01•8/29/24, 9:07 AM

Hi, I have a question, is there an option to have the Worker created on one cloudflare account and the D1 database on the other and everything works together?

Z

zorous•8/29/24, 5:33 PM

rn i have some csv data converted to json...

how would i automatically upload them to d1?

Zzorous rn i have some csv data converted to json... how would i automatically upload t...

H

Hurby•8/29/24, 6:10 PM

I think you can just write basic script on cf workers to upload them

I

itsmatteomanf•8/29/24, 7:02 PM

Text...?

I

itsmatteomanf•8/29/24, 7:06 PM

There is none in SQLite, they differ in other database types.

I

itsmatteomanf•8/29/24, 7:06 PM

https://www.sqlite.org/datatype3.html

I

itsmatteomanf•8/29/24, 7:10 PM

Not as far as I know, the limits are the ones state on the dev docs.

I

itsmatteomanf•8/29/24, 7:10 PM

And those of SQLite, of course

I

IEatBeans•8/30/24, 2:09 AM

Is CF aware of the random intermittent D1 Errors that cause a 500? Such as:

Internal error in D1 storage caused object to be reset

IIEatBeans Is CF aware of the random intermittent D1 Errors that cause a 500? Such as: In...

M

Max (@rozenmd)•8/30/24, 5:47 AM

we are, and have rolled out a bug fix for it

on the off chance this is something new, what's your database id?

MMax (@rozenmd)we are, and have rolled out a bug fix for it on the off chance this is somethin...

I

IEatBeans•8/30/24, 5:49 AM

That’s great to hear. Here’s the ID if needed:

e2e1eb93-f9ba-4615-aea7-bb44f89b61c3

O

o7•8/30/24, 6:02 AM

Getting D1_ERROR: Internal error in D1 storage caused object to be reset.

Oo7 Getting D1_ERROR: Internal error in D1 storage caused object to be reset.

M

Max (@rozenmd)•8/30/24, 6:10 AM

what's your database id?

MMax (@rozenmd)what's your database id?

O

o7•8/30/24, 6:10 AM

b2ea5e97-258f-4752-a68b-4dea4b10bf6c

O

o7•8/30/24, 6:11 AM

its calmed down now but it was intermittent. alongside 520s on a different site which didnt belong to me

O

o7•8/30/24, 6:18 AM

it might've been 85951405-f007-4d17-b1d4-094ed462f582 as well, which is the auth layer, but it would let me authenticate before returning that error when trying to access the 2nd DB pasted above

R

RexNormous•8/31/24, 1:42 AM

Also seeing intermittent

Internal error in D1 storage caused object to be reset.

Internal error in D1 storage caused object to be reset.

Internal error in D1 storage caused object to be reset.

Internal error in D1 storage caused object to be reset.
Database ID: 82459739-eaf4-4ad4-ac50-24ff60338cb1

Original message was deleted

V

Vikash•8/31/24, 2:07 AM

No

V

Vikash•8/31/24, 2:10 AM

I had to convert my data files to JSON > Upload to R2 and run a scheduled task to import all the files one by one using db.batch command

C

cosbgn•8/31/24, 8:09 AM

D1_ERROR: Internal error in D1 storage caused object to be reset.

Any ideas what could cause this?

H

Hiky•8/31/24, 2:02 PM

what is the problem with this the log shows client is undefined pls help

Waiting for your reply, my service has been interrupted for 3 hours and customers have been asking m

Similar Threads

Similar Threads

Similar Threads