Hi 👋 Are there any plans to add spacial data support to D1 for geo-lookups? Maybe adding an extens

Are there any plans to add spacial data support to D1 for geo-lookups? Maybe adding an extension like Spatialite (ideally), or enabling R*Tree support?

Jjohnswarbrick Hi 👋 Are there any plans to add spacial data support to D1 for geo-lookups? Ma...

Matt Silverlock•11/8/23, 2:46 PM

More likely R*Tree, but no formal plans yet

LLila Rest We are building a SaaS, so ideally the DB should be able to scale infinitely wit...

Lila Rest•11/8/23, 2:47 PM

Bumping up this message @Matt

MMatt Silverlock More likely R*Tree, but no formal plans _yet_

johnswarbrickOP•11/8/23, 3:09 PM

Regarding replication in D1, I understand it's not replicated currently, but I assume the plan is to eventually have read-replicas in each geo to ensure that queries run quickly within that particular location rather than having to go all the way back to the central core?

Jökull Sólberg•11/8/23, 3:39 PM

It's crazy there is no good way to get a backup from D1 anymore. Why not at least support

.dump

.dump

in execute?

JJökull Sólberg It's crazy there is no good way to get a backup from D1 anymore. Why not at leas...

Cyb3r-Jak3•11/8/23, 3:56 PM

I believe it’s due to how the new backend stores data with time travel. There are a couple community projects that offer the ability to dump the DB.

Jökull Sólberg•11/8/23, 3:58 PM

This feels like lock in - I'm getting errors using the community projects, and they also have limitations according to the README's. I hope the blunt feedback is appreciated on some level. I know there are amazing people working on this project and I've been very supportive on Twitter thus far. I'm also a bit frustrated because there's a regression - this used to be very simple and easy.

Jökull Sólberg•11/8/23, 3:59 PM

I'm now doing

execute --command select * from ...

execute --command select * from ...

for each table and writing import scripts. So I'm not blocked per se ... just expected to have some way to dump the db

Cyb3r-Jak3•11/8/23, 4:03 PM

It is planned to have it just no ETA for it

JJökull Sólberg This feels like lock in - I'm getting errors using the community projects, and t...

Matt Silverlock•11/8/23, 4:04 PM

It's planned by GA.

kian•11/8/23, 4:04 PM

.dump

.dump

is more or less doing the same thing as the exporters. It's purely a SQLite3 shell command, rather than being available in their C API (which embedders like Workers use) so it would need the functionality written up rather than being something they can enable or use from SQLite.

Kkian `.dump` is more or less doing the same thing as the exporters. It's purely a SQL...

Matt Silverlock•11/8/23, 4:04 PM

Additionally, on the "alpha" backend, backups would constantly fail due to the limitations with that backend. We built a new storage engine that is reliable + far, far faster + enables PITR as a priority over exporting backups during the beta.

kian•11/8/23, 4:05 PM

The shell has a lot of niceties ( including being overly permissive on single vs double quotes, :-( ) which embedders have to reproduce

kian•11/8/23, 4:05 PM

Jökull Sólberg•11/8/23, 4:07 PM

Ok thanks for the clarification. Good point about GA. I'm hyped about D1

Kkian The shell has a lot of niceties ( including being overly permissive on single vs...

kian•11/8/23, 4:13 PM

The double-quoted string misfeature is now disabled by default for CLI builds

nevermind, thankfully that's gone in 3.41.0

kian•11/8/23, 4:20 PM

There's a lot of cool functions that'll land if/when D1 moves from 3.40.1 to 3.44.0

kian•11/8/23, 4:20 PM

unhex, base64, timediff, concat, etc

Victor•11/8/23, 5:01 PM

@kian In the sqlite upgrade pr, what is the allowlist that you are mentioned

kian•11/8/23, 5:02 PM

kian•11/8/23, 5:02 PM

Functions are explicitly allowed.

MMax (@rozenmd)does your use case tolerate data being out of date for up to 60 seconds?

Rick•11/8/23, 5:14 PM

Can you help me understand this please? So if a user creates a new session, then the session goes into the closest kv. All subsequent requests by this user should be hitting this same kv, and as such the 60 sec latency shouldn't matter. Or am I misunderstanding this and there is no gaurantee that the user request will hit that very kv or not

kian•11/8/23, 5:15 PM

The value for a given key in a given colo (a user may hit different colos in the same point of presence, i.e London) will be cached, meaning users may see stale (or no data) despite the data being written elsewhere.

kian•11/8/23, 5:16 PM

If you modelled it like

user: token

user: token

then you might read

user

user

and there's nothing, so you now have a cached 404.

kian•11/8/23, 5:17 PM

If you modelled it like

token: user

token: user

, then there is no reason for you to read it before you've generated the token so it's more resistant to having cached 404's.

kian•11/8/23, 5:17 PM

#durable-objects is likely comparable on performance within the same region, but Durable Objects don't move - so if you had a DO per user and you talked to that user's DO to check the session tokens, and the user moves regions, it'd be a bit slow (comparable to a KV cold read).

Fun Miles•11/8/23, 6:37 PM

I have a cron worker that populates a table in a database. I am testing this cron worker locally in one directory.
In another directory, I have an app that uses the table created by the cron worker plus some other app tables.
How can I copy (with wrangler) the content of the table that the cron worker has created to the database the app is using?

Kkian #durable-objects is likely comparable on performance within the same region, but...

Unsmart•11/8/23, 6:50 PM

Durable objects in the same region as the user is faster than even a cache hit KV read (at least for me where my local cf datacenter hosts DOs) so I would personally always use DOs for user sessions

KV is also quite a bit more expensive and the likely-hood your user moves a large distance that it gets noticeably slower is quite low imo

SellApp•11/8/23, 7:06 PM

Hey all! I am building a multitenant platform and want to use D1 to store some tenant specific data.

SellApp•11/8/23, 7:14 PM

My platform is a standalone project and I needed to find a way to interact with D1 to create and provision each DB. But there is a potential problem I've come across that I would like help with.

First, if I use the API, I have to worry about the 1200 req / 5 min. This makes the API not usable since we also store dynamic tenant specific data that has a good chance of exceeding the 1200 req / 5 min threshold.

Then I looked into Workers as a bridge between my platform and the D1 databases. Idea was platform <-> worker <-> specific D1 DB. But the docs on Cloudflare say that I need to instantiate wrangler.toml with database-specific bindings, so how do I dynamically update the bindings based on the type of request my platform makes? Is this possible at all?

SSellApp My platform is a standalone project and I needed to find a way to interact with ...

Max (@rozenmd)•11/8/23, 7:27 PM

You've pretty much identified all the sharp edges with working with multiple databases at the moment - it's in our backlog to improve this, but at the moment it's not great.

To avoid the rate limit you'd need to use a Worker, and PATCH the Worker settings to include bindings to the new database (https://developers.cloudflare.com/api/operations/worker-script-patch-settings)

MMax (@rozenmd)You've pretty much identified all the sharp edges with working with multiple dat...

SellApp•11/8/23, 7:34 PM

Hey there, appreciate the info! The PATCH method sounds like a good workaround, but what in the event of >2 simultaneous tenant database updates? Or would this require some sort of queuing so updates are handled one-by-one?

SSellApp Hey there, appreciate the info! The PATCH method sounds like a good workaround, ...

SellApp•11/8/23, 8:44 PM

So we've come up with a solution for this, but another problem has presented itself. Any feedback or suggestions would be welcome here

I mentioned 1 tenant = 1 DB. The tenant has their own site, such as siteA.com, siteB.com, siteC.com, and so on.

We planned on using one worker to fetch & set tenant-specific dynamic data from D1. So it would be: visitor <-> worker <-> tenant-specific D1.

This approach would bring considerable load speed improvements, but seems like that's not possible, right? Or am I missing something obvious?

windwp•11/9/23, 5:29 AM

when your app depend on multiple worker you need to patch all worker. and if you deploy a new code by wrangler the patch with new d1 DB will be lost. I suggest it should have a concept to use connection string to connect. it just binding all D1 and use connectstring to select db.

SSellApp Hey there, appreciate the info! The PATCH method sounds like a good workaround, ...

GuillaumeLakano•11/9/23, 12:10 PM

We currently working on something similar, and I give you our feedback
We've isolated one worker with voluntary very few lines of code ( we call it « d1-bindings » ) and attach it as a service bindings to all workers than need to communicate with dynamic databases. The code is very short and only do the prepare().bind() and run(), nothing else because when you PATCH settings of a script, if you later want to deploy a new change of your code, you lost all the dynamically associated bindings from the API (or need a smart way to restore them after each deploy).

Then you only have to uses the API to PATCH only this d1-bindings worker, with 1st a previous call to GET the full settings and injecting your new binding when you create a new DB. Be careful, currently the GET / PATCH will not have the same syntax about the bindings D1 structure. After you GET the settings and extract the "bindings" array, you need to pass in each row to rename the key « database_id » to « id », to permit to PATCH your bindings.

It's not perfect and it's limited to ~5000 bindings per workers, but It's a temporary solution that works for the period of development.
Another problem is the changes are not atomic, so we can loose a bindings if a new DB is created between the GET and the PATCH moment.

forslunds•11/9/23, 12:42 PM

Thanks for feedback! Its still in Beta ofc but its hard to decide a path forward.. its too much hassle to go one-db-per-user right now it seems. all on one db.. who knows the max limit, will we get stuck in beta or even a hard limit in QA? Too much hassle to change from one to one per user later on maybe? So maybe stick with another service that we know can scale enough?

Fforslunds Thanks for feedback! Its still in Beta ofc but its hard to decide a path forward...

GuillaumeLakano•11/9/23, 12:55 PM

For our uses cases, it's a question of security. If each user have it's own DB, it's mitigate the risk and possibilities for an attacker. For example, it's not possible to extract the list of others users if the only user is you in the DB.
It's also interesting for performances, because the D1 will be created closes to the user when he signup, imagine one person in New York, another one in Paris, then when there is writes operations this will be close to where the DB have been created ( The future replica feature it's only for reads operations from what I've understood )

GGuillaumeLakano For our uses cases, it's a question of security. If each user have it's own DB, ...

forslunds•11/9/23, 1:01 PM

yeah thats good ofc, think i tinker on with one db and hope before im done there is a better way to manage several bindings.

forslunds•11/9/23, 1:02 PM

im also on pages so that makes it extra tricky

Fforslunds im also on pages so that makes it extra tricky

GuillaumeLakano•11/9/23, 1:09 PM

Pages seems each time in late of features, we've switched to isolated workers since many months.
I've read in a blog article may be 1 or 2 years ago that the main goal is to merge the Pages & Workers, this will then be better.

Fforslunds yeah thats good ofc, think i tinker on with one db and hope before im done there...

GuillaumeLakano•11/9/23, 1:44 PM

We've created our SQL structure tables/fields and all requests in the exact same way as if the DB will be isolate per user or common to everybody, so you then only have to adjust the DB selector to choose what's the best for you when you go in production

Cretezy•11/10/23, 7:26 AM

Hey everyone, I wrote a package to use the D1 API outside Workers: https://github.com/Cretezy/d1-api

GitHub

GitHub - Cretezy/d1-api: Interact with the Cloudflare D1 API outsid...

Interact with the Cloudflare D1 API outside of Cloudflare Workers - GitHub - Cretezy/d1-api: Interact with the Cloudflare D1 API outside of Cloudflare Workers

Cretezy•11/10/23, 7:27 AM

It uses the Cloudflare API to run queries directly without using bindings. Useful if you need to interact with D1 in a non-live manner (e.g. scripts, since the D1 HTTP API has latency).

Cretezy•11/10/23, 7:28 AM

It also supports tagged templating, e.g.

d1api.first`SELECT * FROM users WHERE id = ${id}`

d1api.first`SELECT * FROM users WHERE id = ${id}`

CCretezy Hey everyone, I wrote a package to use the D1 API outside Workers: https://githu...

Pascale•11/10/23, 8:10 AM

Can confirm that this is awesome, use it a lot for my game

Cretezy•11/10/23, 8:21 AM

Yes, if I have time. Open to contribution!

Cretezy•11/10/23, 8:25 AM

I will likely support custom URLs, to proxy through Workers for better performance. But it'll be optional still

GuillaumeLakano•11/10/23, 9:12 AM

Hi! I'm worries about the meta.last_row_id, the name suggest it's the actual last rowid, and may be not the rowid assigned to the submitted INSERT query.
I'm afraid about concurrency, if multiple INSERT arrives at the same time, a last rowid could be different of the rowid assigned to the submitted INSERT query.

Could you please tell us if the meta.last_row_id is 100% guarantee it's the assigned rowid of our submitted INSERT query ?

Hi 👋 Are there any plans to add spacial data support to D1 for geo-lookups? Maybe adding an extens

Similar Threads