Is there a limit on the number of inserts in a batch?

Bbetim FYI, ticket logged https://github.com/cloudflare/workerd/issues/795

M

Matt Silverlock•6/21/23, 11:32 AM

Commented.

Vvassbence Is there a limit on the number of inserts in a batch?

M

Matt Silverlock•6/21/23, 11:33 AM

Not specific to INSERT, outside of any normal limits. “Thousands” works fine.

V

vassbenceOP•6/21/23, 11:34 AM

ok so definitely higher than queues max batch size, thanks

Pprogramjm How do you go about testing/running locally?

I

itsmatteomanf•6/21/23, 2:36 PM

It's kind of a pain... as it's a pain regardless testing with D1 on Pages, you need to pass it through the cli.

I

itsmatteomanf•6/21/23, 2:36 PM

I moved most of what I can to separate APIs, tbh.

V

vassbenceOP•6/21/23, 3:02 PM

I'd like to get some help with the batch API.

I have a queue consumer which gets a list of messages to process and write them to db. If a write fails in

.batch()

.batch()

.batch()

.batch() an error is thrown and everything is rolled back. This is not what I'd like to do, rather I'd like to ack/retry each item based on the success attribute

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

Is this possible or I'll need to use

.run()

.run()

.run()

.run()s inside try catches?

V

vassbenceOP•6/21/23, 3:04 PM

guess i could a condition in the sql to not do the insert if the foreign key fails but that would then output success: true and i'd get false positives

V

vassbenceOP•6/21/23, 3:11 PM

or maybe checking for the foreign key before running the batch is good enough

M

Max (@rozenmd)•6/21/23, 3:20 PM

naively, I'd probably chuck the writes into a Promise.allSettled and retry the failed ones myself

V

vassbenceOP•6/21/23, 3:24 PM

i think i figured out, with a conditional

insert into

insert into

insert into

insert into i can look at the

results.changes

results.changes

results.changes

results.changes attribute to tell if a row was inserted or not then

.ack()

.ack()

.ack()

.ack() or

.retry()

.retry()

.retry()

.retry() based on that

V

vassbenceOP•6/21/23, 3:24 PM

not like retrying will realy help if a foreign key was not present to begin with

V

vassbenceOP•6/21/23, 3:30 PM

is

meta.changes

meta.changes

meta.changes

meta.changes the same in production and in development? it's present in dev but the docs do not mention it, only

meta.duration

meta.duration

meta.duration

meta.duration

Vvassbence is `meta.changes` the same in production and in development? it's present in dev...

M

Matt Silverlock•6/21/23, 4:00 PM

Yes, it should be the same. Same code.

Pprogramjm Do you mean like Astro endpoints? And are you able to test them locally with Pos...

I

itsmatteomanf•6/21/23, 4:15 PM

Or Pages functions, but yeah, if you use the adapter correctly.

I

itsmatteomanf•6/21/23, 4:16 PM

Do note that you need to use SSR for D1 to work, it won't work to generate statically, unless you have an API already somewhere.

Bbetim FYI, ticket logged https://github.com/cloudflare/workerd/issues/795

M

Matt Silverlock•6/21/23, 7:26 PM

https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go out in the next week or two. We'll update the changelog + limits docs once it does.

GitHub

sql: increase SQLITE_LIMIT_COMPOUND_SELECT to 5 by elithrar · Pull ...

Fixes #795
This PR increases the SQLITE_MAX_COMPOUND_SELECT limit (https://www.sqlite.org/limits.html#max_compound_select) from the dark arts recommended 3 to a slightly more permissive 5 based on ...

MMatt Silverlock https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go ...

B

betim•6/21/23, 7:49 PM

great! will keep an eye out for it. thanks for the quick turnaround!

MMatt Silverlock https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go ...

D

Dahoom•6/21/23, 7:50 PM

While we wait for time travel could we please get a wrangler option or something to just download the db as an sqlite file?

DDahoom While we wait for time travel could we please get a wrangler option or something...

M

Matt Silverlock•6/21/23, 8:01 PM

That's not how D1 works: it's not a "single file on disk". Time Travel will land very shortly, but we don't expect to support "export a blob" for a while.

D

Dahoom•6/21/23, 8:04 PM

apologies for my ignorance, good luck with the implementation

V

vassbenceOP•6/21/23, 8:07 PM

wait then what does

db.dump()

db.dump()

db.dump()

db.dump() do if not dumping the db content into an sqlite file?

Vvassbence wait then what does `db.dump()` do if not dumping the db content into an sqlite ...

M

Matt Silverlock•6/21/23, 10:41 PM

That’s not available on the experimental backend, which will be the default in the near future.

M

Matt Silverlock•6/21/23, 10:43 PM

The “original” alpha was, as alphas tend to be, a prototype: SQLite in Wasm. A lot has changed since then - some of that I wrote about in our recent blog: d1-database

Original message was deleted

M

Matt Silverlock•6/21/23, 11:00 PM

Prod is

workerd@a2fbc3dfe25

workerd@a2fbc3dfe25

workerd@a2fbc3dfe25

workerd@a2fbc3dfe25 which maps to https://github.com/cloudflare/workerd/commits/main?before=a2fbc3dfe254ce6e94ceb183f7b1d6476b6c2b29+35&branch=main&qualified_name=refs%2Fheads%2Fmain ->

"ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse"

"ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse"

"ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse"

"ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse" passes.

TL;DR: Yes.

Original message was deleted

M

Matt Silverlock•6/21/23, 11:05 PM

Let me look into that. We may have a bug. @geelen

Original message was deleted

M

Matt Silverlock•6/21/23, 11:13 PM

There’s likely an edge case with how our storage layer & authorizer interact with temp tables during a

DROP COLUMN

DROP COLUMN

DROP COLUMN

DROP COLUMN op.

A

amorfati•6/22/23, 5:37 PM

how do I go about having my D1 id in

wrangler.toml

wrangler.toml

? The repo is public so I do I make the id public or is it supposed to stay secret? I assume the latter so currently I'm manually adding it to

wrangler.toml

wrangler.toml

every time before I deploy

A

amorfati•6/22/23, 5:40 PM

ah got it. Awesome, good to know

A

amorfati•6/22/23, 5:41 PM

Thank you!

W

Walshy•6/22/23, 5:43 PM

Everything in wrangler.toml is safe to have public

I

itsmatteomanf•6/23/23, 9:58 AM

Is there a way to get all table names from D1 via a statement in the JS API?

I

itsmatteomanf•6/23/23, 9:59 AM

I know of

/tables

/tables

/tables

/tables in the console, but does that work in the JS API?

Original message was deleted

I

itsmatteomanf•6/23/23, 10:10 AM

SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'

SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'

SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'

SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'

I

itsmatteomanf•6/23/23, 10:10 AM

That

_cf_KV

_cf_KV

_cf_KV

_cf_KV table isn't really usable, it doesn't let you see it

I

itsmatteomanf•6/23/23, 10:13 AM

That one is shown even when doing

/tables

/tables

/tables

/tables, though

M

Max (@rozenmd)•6/23/23, 10:44 AM

/tables

/tables

/tables

/tables literally just runs that query against the execute endpoint

M

Max (@rozenmd)•6/23/23, 10:44 AM

i should probably add the filters

M

Max (@rozenmd)•6/23/23, 10:44 AM

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name; is what it runs

Iitsmatteomanf Is there a way to get all table names from D1 via a statement in the JS API?

M

Matt Silverlock•6/23/23, 11:11 AM

SELECT PRAGMA table_list, PRAGMA table_info(table_name)

SELECT PRAGMA table_list, PRAGMA table_info(table_name)

SELECT PRAGMA table_list, PRAGMA table_info(table_name)

SELECT PRAGMA table_list, PRAGMA table_info(table_name)

M

Matt Silverlock•6/23/23, 11:12 AM

Also

index_list(table), index_info(index)

index_list(table), index_info(index)

index_list(table), index_info(index)

index_list(table), index_info(index)

I

itsmatteomanf•6/23/23, 11:27 AM

Gotcha, thanks @rozenmd and @elithrar

M

Murder Chicken•6/23/23, 12:00 PM

With regard to

batch

batch

batch

batch operations, is the Maximum SQL statement length of 100,000 (100kb) applied to the entire

batch

batch

batch

batch or is that applied to the individual statements within the

batch

batch

batch

batch?

MMurder Chicken With regard to `batch` operations, is the Maximum SQL statement length of 100,00...

M

Matt Silverlock•6/23/23, 12:26 PM

Per statement within a batch.

M

Matt Silverlock•6/23/23, 12:27 PM

DB.batch(statements)

DB.batch(statements)

DB.batch(statements)

DB.batch(statements) where

statements.length

statements.length

statements.length

statements.length is 400 = 400 statements.

M

Murder Chicken•6/23/23, 12:38 PM

Wonderful. Thank you for clarifying!

M

Murder Chicken•6/23/23, 3:01 PM

Are there any sqlite compatible gui clients (like tableplus) that can connect to production D1 databases?

Is there a limit on the number of inserts in a batch?

Similar Threads

Similar Threads

Similar Threads