We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962c2898dcb4a4bd0c4e1b2

We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962c2898dcb4a4bd0c4e1b2a60949f74/src/workerd/util/sqlite.c%2B%2B#L645, I think a github issue in workerd would help

MMax (@rozenmd)We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962...

betim•6/21/23, 10:11 AM

k, thanks all.. this prevents us from moving to the experimental backend, which we've been really looking forward to for perf. will log a ticket to make this adjustable

Bbetim k, thanks all.. this prevents us from moving to the experimental backend, which ...

Matt Silverlock•6/21/23, 10:13 AM

Not adjustable: we’re likely to just increase it. A minimal sample query that fails now, but would work at (e.g.) 5 would be great.

Original message was deleted

Matt Silverlock•6/21/23, 10:14 AM

I don’t remove .cause.cause in that

Original message was deleted

Matt Silverlock•6/21/23, 10:15 AM

The PR doesn’t remove it, and it’s not merged, so…

MMax (@rozenmd)We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962...

betim•6/21/23, 10:26 AM

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

GitHub

🐛 Experimental backend too restrictive on compount SELECT statement...

The experimental D1 backend configuration seems to have set limits around the compound SELECT statements that align with the SQLite high-security guidelines, but conflict with fairly simple use cas...

vassbence•6/21/23, 11:03 AM

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

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

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

Commented.

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

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

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

vassbence•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?

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.

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

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

vassbence•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()

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

Is this possible or I'll need to use

.run()

.run()

s inside try catches?

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

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

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

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

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

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

i think i figured out, with a conditional

insert into

insert into

i can look at the

results.changes

results.changes

attribute to tell if a row was inserted or not then

.ack()

.ack()

.retry()

.retry()

based on that

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

apologies for my ignorance, good luck with the implementation

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

wait then what does

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

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.

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

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

Prod is

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"

passes.

TL;DR: Yes.

Original message was deleted

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

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

Original message was deleted

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

op.

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

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

ah got it. Awesome, good to know

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

Thank you!

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

Everything in wrangler.toml is safe to have public

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?

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

I know of

/tables

/tables

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

Original message was deleted

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'

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

That

_cf_KV

_cf_KV

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

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

That one is shown even when doing

/tables

/tables

, though

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

/tables

/tables

literally just runs that query against the execute endpoint

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

i should probably add the filters

Max (@rozenmd)OP•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;

is what it runs

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

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)

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

Also

index_list(table), index_info(index)

index_list(table), index_info(index)

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

Gotcha, thanks @rozenmd and @elithrar

We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962c2898dcb4a4bd0c4e1b2

Similar Threads

Similar Threads

Similar Threads