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

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?

veequeOP•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
  }
}

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

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.

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

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

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

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

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

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

why?

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

can you elaborate?

veequeOP•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,
  });

as you can see the second statement uses returnedreturned. 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}),
]);

i don't have a returnedreturned variable i can now use, so what do i do?

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

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

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

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

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?

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.

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.

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

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

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

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

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

without hyperdrive was like 600+ms

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

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

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

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

that's interesting, thanks for sharing:)

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

we also use Hono for all our D1 projects fwiw

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

There is only one recipe for d1d1 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.mtsvitest.config.mts).

HHurby you can try using raw sql but idk

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

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?

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

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

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

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

Text...?

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

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

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

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

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

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

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

And those of SQLite, of course

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

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

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

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

e2e1eb93-f9ba-4615-aea7-bb44f89b61c3

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.

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

what's your database id?

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

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

b2ea5e97-258f-4752-a68b-4dea4b10bf6c

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

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

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.
Database ID: 82459739-eaf4-4ad4-ac50-24ff60338cb1

Original message was deleted

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

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

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?

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

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

Alex Taccard•8/31/24, 4:55 PM

When one runs npx wrangler d1 createnpx wrangler d1 create to create a D1 database, he has to manually insert the new D1 binding configuration to the file