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

CretezyOP•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).

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

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

Yes, if I have time. Open to contribution!

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