Is it possible to use the results of a previous query in a `d1.batch` call in the next query? I'm wo

Llouiek22 Is it possible to use the results of a previous query in a `d1.batch` call in th...

L

louiek22OP•8/27/24, 12:53 AM

Solved this by combing my

SELECT

SELECT

query into my

UPDATE

UPDATE

query and using

LIMIT 1

LIMIT 1

LIMIT 1

LIMIT 1 so I can do everything in a single query.

T

tester123•8/27/24, 11:49 AM

hey guys

T

tester123•8/27/24, 11:49 AM

d1 or neondb is better overall for cloudflare workers?

M

minwoo•8/27/24, 5:44 PM

hey,
Are you having issues with your current D1 database?

Z

zorous•8/27/24, 6:13 PM

how do i pull the data that is on my dashboard into my local project?

D

discondition•8/27/24, 9:34 PM

What latency are you guys getting?

I don’t what to expect but my Remix worker that calls out to my DB is taking 300-400 ms for really simple queries. This ain’t good enough for what I want really, hopefully there are ways to speed it up. I have the smart location switched on.

Ttester123 d1 or neondb is better overall for cloudflare workers?

J

James•8/27/24, 10:34 PM

Neon is Postgres which is much more versatile than SQLite in my opinion and what I’d generally recommend personally

J

James•8/27/24, 10:35 PM

We use neon + workers, with Postgres.js very successfully. What are you building though? Maybe SQLite makes more sense?

Ddiscondition What latency are you guys getting? I don’t what to expect but my Remix worker t...

O

o7•8/28/24, 1:48 AM

right under 100ms and I am not near a D1/DO colo

Ddiscondition What latency are you guys getting? I don’t what to expect but my Remix worker t...

T

toadfans•8/28/24, 2:06 AM

200-300ms

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

D

discondition•8/28/24, 2:11 AM

Just a basic crud kind of app

Separated user data

Oo7 right under 100ms and I am not near a D1/DO colo

D

discondition•8/28/24, 2:11 AM

What’s the secret?

O

o7•8/28/24, 2:12 AM

ermmm I guess I am just doing a simple select on a primary key

D

discondition•8/28/24, 2:12 AM

So am I

Ddiscondition So am I 🥲

O

o7•8/28/24, 2:13 AM

This isn't a map of D1, but it's a map of DO and D1 is based on DO so it might give u a little bit of an idea where your DB is

Tracking where Durable Objects are created, wherever you are in the world.

O

o7•8/28/24, 2:16 AM

I may or may not be 800km away from my DB, getting under 100ms with the smart placement being local. Sometimes when the smart placement works (enough traffic) I get around 70ms

D

discondition•8/28/24, 2:20 AM

Thanks, I guess I gotta look into optimistic UI

Ttoadfans 200-300ms

M

Max (@rozenmd)•8/28/24, 5:41 AM

where are you based? D1 is still subject to the speed of light, so 200ms round-trips between australia and usa are a thing

B

bennycondon•8/28/24, 6:34 AM

Is there a way to lift the "Maximum Length Of A LIKE Or GLOB Pattern"? I'm running into "Error: LIKE or GLOB pattern too complex: SQLITE_ERROR" when the pattern exceeds 50 characters.

J

Jorge•8/28/24, 7:52 AM

the 10GB limit worries me, well thats a problem for future me anyways

神

神龙出海•8/28/24, 8:42 AM

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

神

神龙出海•8/28/24, 8:43 AM

Does anyone know what is going on? It has been more than 2 hours since my production environment database suddenly became unavailable. The test environment is fine.

神神龙出海 Does anyone know what is going on? It has been more than 2 hours since my produc...

M

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

what's your database id?

神

神龙出海•8/28/24, 8:56 AM

神

神龙出海•8/28/24, 8:57 AM

Neither the worker nor the console can connect to the production environment D1 database.

神神龙出海 Click to see attachment

M

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

please paste your database id, I can't investigate further without it

神

神龙出海•8/28/24, 8:58 AM

339c818f-a6d1-44c7-b860-32cfb8564809

神

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

The same problem occurred a long time ago, and it automatically recovered after waiting for a few hours. This time the problem reappeared, and we now have users using it.

M

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

looking into it

神

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

Waiting for your reply, my service has been interrupted for 3 hours and customers have been asking me when it will be restored.

神神龙出海 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

神

神龙出海•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

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?

Is it possible to use the results of a previous query in a `d1.batch` call in the next query? I'm wo

Similar Threads

Similar Threads

Similar Threads