You've pretty much identified all the sharp edges with working with multiple databases at the moment

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 ?

GGuillaumeLakano Hi! I'm worries about the meta.last_row_id, the name suggest it's the actual las...

Matt Silverlock•11/10/23, 11:41 AM

It’s only for the query you made.

Paul Shriner•11/10/23, 1:38 PM

Question on best practice. I am writing a durable object such that I would like to have some log information about who has interacted with it. The object itself will contain a small bit of information but scales horizontally accross a lot of users.

I would like to store some log information about who is interacting with the object. Where in the cloudflare ecosystem should this live?

My instinct is D1, then appends become very easy, as does shrinking, trimming etc... I could write some batch codes that backup data to R2 on a semi-regular basis.

Because of the potential gerth of the log data, I don't think R2 makes sense as I would have to do a set/get on each interaction, which is a lot of memory to pull and set, which introudces a brittleness that I am not comfortable with.

I can't imagine that I am the person that have considered this recipe. How have other people solved it?

PPaul Shriner Question on best practice. I am writing a durable object such that I would like ...

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

Write to a Queue and then store somewhere asynchronously? Where you store will highly depend on how you want to consume that “log” information, and what it represents.

MMatt Silverlock Write to a Queue and then store somewhere asynchronously? Where you store will h...

Paul Shriner•11/10/23, 1:44 PM

Yep, "how you want to consume that information"

If I want to stay in the Cloudflare ecosystem, the only option that includes append is D1, right? I could write to an external service, but would prefer to keep it all in one place.

PPaul Shriner Yep, "how you want to consume that information" If I want to stay in the Cloudf...

Matt Silverlock•11/10/23, 1:46 PM

You can write objects to R2, append to a D1 table, add to an ordered KV list… again, it will highly depend on how you want to access that information. If you just want to run SQL queries, then D1 is a great fit.

PPaul Shriner Yep, "how you want to consume that information" If I want to stay in the Cloudf...

Matt Silverlock•11/10/23, 1:46 PM

I would think less about “append” and more about accessing/query patterns.

Paul Shriner•11/10/23, 1:48 PM

Right, but to write an entry to R2, or KV, I would have to pull the entire value, write to it, and then store it back in the store. With log data, which can grow very quickly, it seems that introducing that much data to the mix would hit a memory ceilng at some point.

PPaul Shriner Right, but to write an entry to R2, or KV, I would have to pull the entire value...

GuillaumeLakano•11/10/23, 1:48 PM

May be Logs Engine could help for that ? https://developers.cloudflare.com/logs/r2-log-retrieval/

Logs Engine · Cloudflare Logs docs

Logs Engine gives you the ability to store your logs in R2 and query them directly.

Paul Shriner•11/10/23, 1:49 PM

Ooh, I didn't know about that.

PPaul Shriner Right, but to write an entry to R2, or KV, I would have to pull the entire value...

Matt Silverlock•11/10/23, 1:49 PM

Step back a second: how do you want to query it or access it? Many tools can read multiple R2 objects. I don’t think “append to a single object” is the problem here.

Paul Shriner•11/10/23, 1:54 PM

So it is engagement data, a user would interact with a product, or a post and I want to capture that for the person that owns that user. Once that data is caputred, I will want to aggregate the values into consumable insights:

Most active
Least active
Average Order Volume
Total purchases
Lapsed buyers
Lapsed viewers
Most active by chanel
Channel volume

The kind of thing that Elastic Search is good at. I am not ready for that technology bite yet, so in the interem, counts and sums is what I am looking for.

Durable objects don't have a ceiling in terms of storage per class so... I could write to a node there and then if I ever get to the ElasticSearch plan I could do a direct import from JSON.

Paul Shriner•11/10/23, 2:02 PM

I think I am considering memory because I have burned in the past by not considering it. (not at cloudflare).

Logs looks awesome and maybe exactly what I am looking for.

Paul Shriner•11/10/23, 2:03 PM

This has been a helpful exchange this morning, thank you.

Paul Shriner•11/10/23, 2:04 PM

It could eventually write directly to Elastic

Paul Shriner•11/10/23, 2:05 PM

And by that time, CloudFlare will have already implmented Elastic for me under my workers plan.

Foreignity•11/10/23, 3:15 PM

Hello! I've checking on the doc for the best way to add a new column in a table of my database, but I don't seem to find anything. My table already contains data, and I would like to add two new columns without altering the data already in my table, is there a place where I can find how to do this?

johnswarbrick•11/10/23, 11:18 PM

Hi - is it normal for the very first SELECT from a cold started worker to take ~300ms to return a single row from a D1 table with just one row and two columns? Subsequent SELECT queries are ~25ms. This timing is specifically just the D1 query, not the total worker run time.

Rabid•11/11/23, 2:42 PM

Is there some kind of alert when we are approaching the max DB size for D1? I launched a link shortener for my https://d4planner.io site, and I'm basically prepending shortened links with 1-1- to denote which DB it hits, so I can rotate through if I hit the max size.

RRabid Is there some kind of alert when we are approaching the max DB size for D1? I l...

itsmatteomanf•11/11/23, 2:44 PM

Is there a specific reason why you went with D1 for a link shortener?

itsmatteomanf•11/11/23, 2:45 PM

PS: it's a really high number of links at 2GB...

Rabid•11/11/23, 2:45 PM

Seemed like it would be a cheaper option than KV

itsmatteomanf•11/11/23, 2:45 PM

Interesting, never really did the calculations there. Is it?

Rabid•11/11/23, 2:45 PM

Basically the way the site works is that I encode character "builds" in the url like https://d4planner.io/skilltree/Sorcerer?cpr=F2:2;D2:1;E12:3;F5:1;E13:1;S3:1;T1:5;E16:1;S6:1;I3:1;E15:1;E5:3;G1:3;A2:1;M3:3;P3:3;C5:3;B3:5;E22:1;W3:1;S2:1;I8:3;C7:1;E25:3;U2:1;P6:1;S15:1;O1:1;EH:354:;GH1:19;EC:290:;GC1:19;GC2:19;EG:279:115,80,111,224;EP:355:;GP1:19;GP2:19;EB:343:;EW:101:237,240,105,253;GW1:14;EO:178:171,230,243,72;GO1:14;EA:1:349,76,315,72;GA1:29;ER1:369:;GR1-1:29;ER2:112:171,105,102,243;GR2-1:29;PBS:r:0⭕0❌0:y:0:g:KG-91-21_:n:KOKNMNMMMLMKLKLJLIKIJIJKIKILIMKHLGLFMFNFJGIGJFJEJDMELELDLCLBKBKALNJJ;PBSS:r:270⭕1❌0:y:1:g:KI-92-21_:n:KUKTJTJSJRJQIQHQGQHPGPGOGNHNINJNKNKOGMGLGKGJGIHJIJIKJKJJJLJIJHKJLIMININHOIPIQIQHQGRGRFQJQKRKTKSKFKEKDKCKBKAKUK;PBSH:r:0⭕2❌-1:y:1:g:PF-99-21_:n:UKTKTJTISISHRHQHRGRFQFOFNFMFMG;PBEM:r:270⭕3❌1:y:1:g:FP-85-21_:n:AKBKBLBMBNBOCODOCPDNENEOEPGPGQHQGRFRGSHRGTHTITJTKTKU;PBCC:r:180⭕4❌1:y:0:g:PF-97-21_:n:KAKBKCKDJDJEIEHEHFGFHGLDMDMENEOEPEQFRFSFTFSEPGPHQHQIQJRJRKSKTKUK;PBBI:r:0⭕5❌2:y:0:g:FP-96-21_:n:AKBKCKDKEKEJFJFIGIHIHHGHIHIGHGELEMENEOEPEQERFSFTESFQGQHQHPHOFNFMGMIQIRJRKRKSKTKU;PBES:r:0⭕6❌2:y:-1:g:KI-83-21_:n:KAKBKCKDLDLELFLGKGKHJIIIHILIMIMHNINJLJKJKKJKJL;SE:F2,C3;VP:VPRA,VPM,VPPR,VPA,VPI;I4:1;C3:1;C11:3&read_only=true

Rabid•11/11/23, 2:45 PM

So with the new shortener, that maps to https://d4planner.io/s/1-wIyR_T01w0T

itsmatteomanf•11/11/23, 2:47 PM

Oh yeah, you are right, if you read one row per request (or probably two, given index + table) it's definitely cheaper.