Definitely possible. It's a lot of inserts.

M

Murder ChickenOP•5/8/23, 8:46 PM

That was it. Batching the batch seemed to get past it.

MMurder Chicken Definitely possible. It's a lot of inserts.

M

Matt Silverlock•5/9/23, 12:11 AM

Curiously: how many? How long (in bytes) is each row/statement, and how big were the batches versions?

M

Murder ChickenOP•5/9/23, 12:23 AM

50k updates. Once I split it into 10k batch batches, it went through.

M

Murder ChickenOP•5/9/23, 12:24 AM

Small updates.

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

M

Murder ChickenOP•5/9/23, 12:24 AM

With bind values of integer, date and ~10 character string, so nothing too substantial.

M

Murder ChickenOP•5/9/23, 12:26 AM

So, 50k of basically this:

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

Ssourabh.sashank Yeah, but for the other table with 44K rows with the similar size of data size w...

M

Matt Silverlock•5/9/23, 11:08 AM

It’s not just the number of rows. How big is each row? What indexes are on the table? The issue is likely materializing the results from disk -> running OOM.

Ssourabh.sashank Yeah, but for the other table with 44K rows with the similar size of data size w...

M

Matt Silverlock•5/9/23, 11:08 AM

As before: d1-database

MMurder Chicken So, 50k of basically this: `INSERT INTO calendar (c_id, for_date, status) VALUES...

M

Matt Silverlock•5/9/23, 11:56 AM

Useful, thanks. Compiling some examples that will work on the new storage engine

MMatt Silverlock Useful, thanks. Compiling some examples that will work on the new storage engine...

M

Murder ChickenOP•5/9/23, 12:01 PM

Awesome! Looking forward to seeing how this feature progresses. One question. Is the issue the query / bind payload? If so, for my example, is it possible to use more compact syntax through the native binding functionality? Like this:

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

M

Murder ChickenOP•5/9/23, 12:05 PM

After reading through the docs, it didn't seem possible to bind an array of objects/data to statement values like this but I may have missed it. This would definitely reduce the payload of larger inserts/updates.

MMurder Chicken Awesome! Looking forward to seeing how this feature progresses. One question. ...

M

Matt Silverlock•5/9/23, 12:08 PM

The Wasm runtime we use now is pretty resource limited - a big single batch of inserts is just a lot of bytes.

MMurder Chicken After reading through the docs, it didn't seem possible to bind an array of obje...

M

Matt Silverlock•5/9/23, 12:09 PM

This is a clever way to bind arrays: d1-database

M

Murder ChickenOP•5/9/23, 12:09 PM

Gotcha. Makes sense. Thanks for your help and responsiveness. Get the warm and fuzzies about D1 and workers so far.

MMatt Silverlock This is a clever way to bind arrays: https://discord.com/channels/59531799019139...

M

Murder ChickenOP•5/9/23, 12:10 PM

Thanks! I'll dig into that example!

Ssourabh.sashank Here are the error logs with e.cause that we are getting while performing differ...

M

Matt Silverlock•5/9/23, 12:36 PM

To re-state: these are expected in the alpha and the prototype that it is. Hold tight

Ssourabh.sashank Here are the error logs with e.cause that we are getting while performing differ...

M

Matt Silverlock•5/9/23, 12:37 PM

This is also a public group and so any private data (if any) that sheet = not the place to post it.

D

divby0•5/9/23, 12:52 PM

https://github.com/cloudflare/workers-sdk/issues/3160
Has there been any status on this? I'd not ask this here because there already is an issue open, but someone from the cloudflare team replied and said that it seemed to be the wrong repo for submitting issues there so I don't know where to track this issue or if it's on your radar at all

GitHub

🐛 BUG: when trying to left join 2 tables that have same name column...

Which Cloudflare product(s) does this pertain to? D1 What version of Wrangler are you using? 2.19.0 What operating system are you using? macOS Describe the Bug As per issue #555 we can see, that d1...

Original message was deleted

D

divby0•5/9/23, 1:00 PM

I am using drizzle orm and theoretically the two values should reside in two different keys so they can have the same name. But I don't know the abstraction between drizzle and D1, does d1 always return the joined data as a flat json with only one level of key-value pairs? (in contrast to nested {user: {id: something}} format?

D

divby0•5/9/23, 1:24 PM

Thanks

PPrudhvi We have to insert a dynamic object into the table We don't want to use SQL inj...

M

Matt Silverlock•5/10/23, 4:08 PM

https://developers.cloudflare.com/d1/platform/client-api/#parameter-binding

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

Ggotfredsen regarding working with D1 build in json functions building a Order managment sho...

M

Matt Silverlock•5/10/23, 7:55 PM

That’s really up to you. You can mix and match - core relational data in native SQL types and more fluid data as TEXT columns you use json_* functions over.

Ssourabh.sashank Is there a way to backup database externally ? I mean can we backup the databas...

C

Cyb3r-Jak3•5/11/23, 2:48 AM

D1 doesn't have any documented API but if you use dev tools on a browser you could mimic to requests to create and download a backup

Ssourabh.sashank Is there a way to backup database externally ? I mean can we backup the databas...

M

Matt Silverlock•5/11/23, 6:42 PM

https://developers.cloudflare.com/d1/learning/backups/#downloading-a-backup-locally

Backups · Cloudflare D1 docs

D1 has built-in support for creating and restoring backups of your databases, including support for scheduled automatic backups and manual backup …

A

abc222•5/11/23, 6:42 PM

Hello, is it possible to access to my remote d1 db when developing locally on a SvelteKit project ?

Definitely possible. It's a lot of inserts.

Similar Threads