Nah that's ok, I've worked around it by not using joins for now. But it's definitely something I hav

Nah that's ok, I've worked around it by not using joins for now. But it's definitely something I have to actively work around, so keen for a fix from the Cloudflare team.

manstfu•7/9/23, 8:45 AM

Mmanstfu just a kind reminder🙏

Max (@rozenmd)•7/9/23, 9:51 AM

Please don't do this.

Semigroup•7/9/23, 11:58 AM

It looks like TS bindings don't align with actual types when calling 'all' from a prepared statement

The 'results' field has a type of T[][], when actually it's just T[], so I'm forced to use ts-expect-error. Using version 4.20230628.0 of workers types, but it was the case at least since 4.20230321.0 (that's when I started using D1). Also, I'm using experimental backend for D1

Am I doing something wrong? If not, how do I report it properly (e.g. github issue) so it can be tracked?

SSemigroup It looks like TS bindings don't align with actual types when calling 'all' from ...

itsmatteomanf•7/9/23, 8:00 PM

It seems like it’s passing in Out[] as the result type, which is not what you are passing… have you tried restarting Typescript or the editor?

AAdi Nah that's ok, I've worked around it by not using joins for now. But it's defini...

Unsmart•7/9/23, 8:16 PM

From reading the issue it seems like they are probably just going to make that throw an error instead of returning data at all and you should be using

AS

AS

to rename duplicated columns https://github.com/cloudflare/workerd/pull/696#issuecomment-1561891627

vassbence•7/9/23, 8:51 PM

Durable Objects run in a ton of regions, as for the D1 read replicas will they match these regions or run in fewer ones? DO give the best performance as of now as they run the closest and was just wondering if this will be the same in the future. Guess it's kinda overkill to run 8 read replicas for a db.

Unsmart•7/9/23, 9:07 PM

D1 is a durable object so it will be the same regions but how many it will be in no clue. My guess is probably 3 maybe 4

Unsmart•7/9/23, 9:09 PM

Durable object’s performance really depend though because if you have 1 object used by many users it won’t be close to all of them as they aren’t replicated each object only exists in one location

vassbence•7/9/23, 9:25 PM

I have one DO per user and this is why I was wondering about D1/DO speed

vassbence•7/9/23, 9:26 PM

as with my current setup DO is really close to the user and so everything is snappy. D1 will be single-writer as stated by the team so the write speeds will be always faster for DO, but I guess reads will be at least as good or better for DOs too.

vassbence•7/9/23, 9:27 PM

It's just that I'd love to write SQL instead of DOs, lot easier for other developers in the team too.

vassbence•7/9/23, 9:28 PM

And so which one does one choose, a few hundered ms faster writes, and a couple ten ms faster reads for the end users or easier developer experience

vassbence•7/9/23, 9:28 PM

easier migrations etc etc, all the goodies that come with the abstraction of D1

Vvassbence And so which one does one choose, a few hundered ms faster writes, and a couple ...

Matt Silverlock•7/9/23, 11:00 PM

Most web applications are (at least) 80:20 read:write, for what it’s worth.

Vvassbence as with my current setup DO is really close to the user and so everything is sna...

Matt Silverlock•7/9/23, 11:02 PM

Nothing stops you from creating multiple D1 databases either — but if you want to query all of your (global) data you need to make a trade-off. We don’t think D1 and DO are really comparable: a SQL database vs a strongly consistent key:value store.

vassbence•7/10/23, 11:13 AM

Yeah multiple databases is the same tradeoff as with Durable Objects, I need a global singleton instance to keep track of like list of all users in the system and since that is a single DO it can increase latency for some endpoint where accessing it is needed.

vassbence•7/10/23, 11:18 AM

Also, even if 80:20 is the ratio of read:writes I have a hard time justifying a 500ms write response for globally distributed users. I know going single writer gives us a lot of consistency but honestly I really like Dynamos model, where one at least has the option to go multi writer while still having a single database/table

vassbence•7/10/23, 11:20 AM

Anyways, can't wait for D1 to get out of beta and to learn about it's capabilities and test it against other DBs

CChaika Did you ever get a chance to check into the broken d1 db 972558fd-cadf-4bec-8b0b...

kenton•7/10/23, 3:00 PM

Hey! Just want to let you know I'm continuing to try to debug what's going on with your database. It seems for some reason our metadata storage is dropping connections but only when we try to query your object specifically. The data is all there and fine, but the backend drops the connection whenever we try to grab the lock to start up the object, and our current observability isn't sufficient to tell me what exactly went wrong. Debugging is slow because we have to push a new runtime release to test each theory about what's happening here, since we can repro with any kind of test data, but it's going... (I did three releaess over the weekend for this.)

It's possible we could get the object working again more quickly by resetting some state but then we'd never know exactly what went wrong, so it could happen again someday. Since you say it's not critical for you, I'd like to keep trying to debug, but let me know if that changes and you want me to prioritize getting the object working without understanding what broke.

Kkenton Hey! Just want to let you know I'm continuing to try to debug what's going on wi...

Chaika•7/10/23, 3:05 PM

Thanks for the update! Yea no worries, take as much time as you need

micromashor•7/10/23, 3:20 PM

is there/will there be a way to import the file from

db.dump()

db.dump()

back into a database?

vassbence•7/10/23, 7:35 PM

How will workers figure out which D1 replica to read from? Something using the smart placement feature?

Vvassbence How will workers figure out which D1 replica to read from? Something using the s...

Matt Silverlock•7/10/23, 9:06 PM

We'll share more once we get closer

OOJ Hello beautiful people, I have a question. I have a worker bound to a table and ...

Max (@rozenmd)•7/10/23, 10:03 PM

sounds like you might want to debounce your searches every 300ms or so https://github.com/sindresorhus/debounce-fn

Original message was deleted

Matt Silverlock•7/10/23, 11:42 PM

If you had 100,000 rows, each 200 bytes wide, and had to perform a full table scan every time - that’s approximately 5,000 read units out of the 25 billion per month included - or 5 million full table scans. Free.

Original message was deleted

Matt Silverlock•7/10/23, 11:44 PM

This isn’t to say an index - https://developers.cloudflare.com/d1/learning/using-indexes/

https://stackoverflow.com/a/57082926/556573
- isn’t useful, but unless you have millions of rows or really wide rows, cost isn’t likely to be a problem. Performance is the reason.

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) …

Stack Overflow

Sort order for sqlite index

There is sort order when creating index for sqlite.

https://sqlite.org/lang_createindex.html
Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate ...

Original message was deleted

Matt Silverlock•7/10/23, 11:45 PM

And: https://use-the-index-luke.com/sql/sorting-grouping/order-by-asc-desc-nulls-last

ASC and DESC modifiers might affect query performance

Indexes that optimize ORDER BY must consider the ASC/DESC and NULLS FIRST/LAST modifiers.

VViper How can we use the D1 database and add or show data from it using Javascipt?

Matt Silverlock•7/11/23, 1:20 AM

I’d suggest following the D1 tutorial if you haven’t yet: https://developers.cloudflare.com/d1/get-started/

this will show you how to query a D1 DB from a Worker.

Get started · Cloudflare D1 docs

This guide will instruct you through:

Noel•7/11/23, 11:59 AM

I remember seeing an intellij plugin for d1, but I can't find it anywhere, could somebody help me?

Noel•7/11/23, 12:06 PM

nvm, found it: https://github.com/isaac-mcfadyen/d1-jdbc-driver

Qqiaoshouzi The return type of `stmt.first()` seems a bit strange to me. I'm not sure why th...

Matt Silverlock•7/11/23, 1:00 PM

cc @geelen

Matt Silverlock•7/11/23, 1:00 PM

Why indexes are useful (I was writing a little demo app w/ Remix...)

ORDER BY random() LIMIT 25
ORDER BY random() LIMIT 25
-> 78ms query execution time
ORDER BY created_at DESC LIMIT 25
ORDER BY created_at DESC LIMIT 25
with an index on
created_at DESC
created_at DESC
-> 0.20ms query execution time

juri•7/11/23, 3:49 PM

I just noticed a table called

_cf_KV

_cf_KV

on every D1 database. This table has two columns,

key

key

and

value

value

. This has nothing to do with the actual KV product, has it?

Jjuri I just noticed a table called `_cf_KV` on every D1 database. This table has two ...

Hello, I’m Allie!•7/11/23, 3:53 PM

That might be prep for the SQL-on-DOs thing they are coming out with soon. According to Kenton(https://canary.discord.com/channels/595317990191398933/773219443911819284/1128025139632681131), regular DO KV storage will be stored in a SQLite Table, which might be what that is...

juri•7/11/23, 3:55 PM

Thanks for the insights, that's very interesting and definitely good do know!

Jjuri I just noticed a table called `_cf_KV` on every D1 database. This table has two ...

Matt Silverlock•7/11/23, 4:35 PM

Nothing to do with KV the product

micromashor•7/11/23, 4:46 PM

is there a size limit to what you can do with

db.batch()

db.batch()

and/or

db.exec()

db.exec()

Braden•7/11/23, 8:02 PM

It appears

npx wrangler d1 migrations apply some-db --experimental-backend

npx wrangler d1 migrations apply some-db --experimental-backend

only works when logged in via

npx wrangler login

npx wrangler login

. In CI, however, where I'm not running

npx wrangler login

npx wrangler login

but instead providing a

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN

env variable the command exits with code 1. Is this a known issue? What workarounds exist for running migrations in CI?

Braden•7/11/23, 8:05 PM

(I've confirmed I can run commands such as

npx wrangler pages deploy dist --project-name abc

npx wrangler pages deploy dist --project-name abc

in CI only providing the

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN

env var.)

Braden•7/11/23, 8:30 PM

Yeah, also provided that. Unfortunately I don't see any additional output.

Braden•7/11/23, 8:31 PM

Since it works locally with

wrangler login

wrangler login

I assume this is probably just an auth bug? Is there any way to provide auth in CI besides using the env vars?

Braden•7/11/23, 8:31 PM

i.e. could I somehow make

wrangler login

wrangler login

work in CI?

Braden•7/11/23, 8:33 PM

Good question. I'll look into that. Thanks!

Braden•7/11/23, 8:36 PM

That was it!! Thanks @skye_31

Nah that's ok, I've worked around it by not using joins for now. But it's definitely something I hav

Similar Threads