Is it possible to build analytics system in D1 Like i have 2k + posts and each has unique id. So i

Is it possible to build analytics system in D1

Like i have 2k + posts and each has unique id.

So i want to use D1 as database for pageviews system (hourly)

So i can see trends hourly, monthly and yearly

ⱤⱤØ₥₳₦Is it possible to build analytics system in D1 Like i have 2k + posts and each ...

Max (@rozenmd)•2/13/24, 3:29 PM

if you have under 400 writes per second, yep, but strongly recommend Workers Analytics Engine for that use case instead

MMax (@rozenmd)if you have under 400 writes per second, yep, but strongly recommend Workers Ana...

Ɽ

ⱤØ₥₳₦OP•2/13/24, 3:33 PM

More than 3k cause on average 3k users are live on my site. I thought i should use google analytics with API

but that's too complex

ⱤⱤØ₥₳₦More than 3k cause on average 3k users are live on my site. I thought i should u...

Max (@rozenmd)•2/13/24, 3:41 PM

check this out: https://developers.cloudflare.com/analytics/analytics-engine/
we do hundreds of millions of requests per second through it, and you can query it with SQL

Chaika•2/13/24, 3:42 PM

could always do something silly where you have a worker with a cron trigger every hour or so to pull aggregated stats down into D1

Chaika•2/13/24, 3:43 PM

then you'd have 90 days or nice adaptive sampled analytics for some graphs and longer term aggregated

MMax (@rozenmd)check this out: https://developers.cloudflare.com/analytics/analytics-engine/ we...

Ɽ

ⱤØ₥₳₦OP•2/13/24, 4:04 PM

That's insane. I'll try this

thanks for reply

CChaika then you'd have 90 days or nice adaptive sampled analytics for some graphs and l...

Ɽ

ⱤØ₥₳₦OP•2/13/24, 4:04 PM

Thanks ma'am

Luka•2/13/24, 6:48 PM

Hey @Max (@rozenmd) , I underestimate the rate of growth of my database. I cannot export using Nora's tool because I hit all types of limits and I will hit the upper filesize limit in the next month (I estimate, if not sooner).

I feel trapped and the only reasonable way is to write an exporter and export each row to remote using API. Would Cloudflare be able to export a .sqlite dump or temporarily lift any limits on my account to complete it?

Ssteida Hi. Any news about D1 transaction via workers? Thank you

Hello, I’m Allie!•2/13/24, 7:57 PM

Using a batch statement?

Mrxbox98•2/13/24, 8:05 PM

Mrxbox98•2/13/24, 8:05 PM

anyone know how to fix this?

MMrxbox98 anyone know how to fix this?

Cyb3r-Jak3•2/13/24, 8:16 PM

Check the encoding on the sql. Should be UTF-8

CCyb3r-Jak3 Check the encoding on the sql. Should be UTF-8

Mrxbox98•2/13/24, 8:25 PM

Tysm, this worked

SShyim Is it possible to disable FOREIGN KEY constraints? I just want to import my loca...

Matt Silverlock•2/13/24, 9:25 PM

PRAGMA defer_foreign_keys = true

PRAGMA defer_foreign_keys = true

draft docs: https://silverlock-d1-foreign-keys.cloudflare-docs-7ou.pages.dev/d1/learning/foreign-keys/#relax-foreign-key-constraints

Foreign keys · Cloudflare D1 docs

D1 supports defining and enforcing foreign key constraints across tables in a database.

Max (@rozenmd)•2/13/24, 10:01 PM

@geelen

Max (@rozenmd)•2/13/24, 10:01 PM

DDavid Renshaw ``` const db = env.DB; let stmt1 = db.prepare( "SELECT def, author...

Max (@rozenmd)•2/13/24, 10:01 PM

.run doesn't return results (any more - it was a bug that it did)

Max (@rozenmd)•2/13/24, 10:01 PM

.all does

DDavid Renshaw ah, https://developers.cloudflare.com/d1/build-databases/query-databases/#await-...

itsmatteomanf•2/13/24, 10:03 PM

i was about to link that to you... it's definitely not clear above :NotLikeThis:

LLuka Hey @Max (@rozenmd) , I underestimate the rate of growth of my database. I canno...

Max (@rozenmd)•2/13/24, 10:10 PM

.sqlite export is being specced out but is unlikely to arrive within the month, i would look at splitting the workload across multiple dbs in the meanwhile or using a query limit on nora's tool

DDavid Renshaw https://developers.cloudflare.com/d1/build-databases/query-databases/#return-obj...

Matt Silverlock•2/13/24, 10:55 PM

Fixing in a PR

Matt Silverlock•2/13/24, 10:56 PM

Changelog going out soon, but for those here:

D1's
```
.raw()
```
```
.raw()
```
,
```
.all()
```
```
.all()
```
and
```
.run()
```
```
.run()
```
query statement methods (https://developers.cloudflare.com/d1/build-databases/query-databases/#query-statement-methods) have been updated to reflect their intended behaviour and improving compatibility with ORMs.
```
.raw()
```
```
.raw()
```
now correctly returns results an array of arrays, allowing the correct handling of duplicate column names (such as when joining tables), as compared to
```
.all()
```
```
.all()
```
, which is unchanged and returns an array of objects. To include an array of column names in the results when using
```
.raw()
```
```
.raw()
```
, use
```
.raw({columnNames: true})
```
```
.raw({columnNames: true})
```
.
```
.run()
```
```
.run()
```
no longer incorrectly returns a
```
D1Result
```
```
D1Result
```
and instead returns a
```
D1ExecResult
```
```
D1ExecResult
```
(https://developers.cloudflare.com/d1/build-databases/query-databases/#return-object) as originally intended and documented.

This may be a breaking change for some applications that expected

.raw()

.raw()

to return an array of objects.

MMatt Silverlock Changelog going out soon, but for those here: * D1's `.raw()`, `.all()` and `.r...

kev-ac•2/14/24, 1:56 AM

It was indeed a breaking change for us, even if it was never intended to work that way. Replacing the .run() with .all() seemed to have done the trick for now. Back to sleep

Unsmart•2/14/24, 1:57 AM

That’s what a betas for tho

Jadugar_Jaggu•2/14/24, 4:05 AM

beeeetaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

ZZiga Zajc Multiple statements in a single query are not supported?

Unsmart•2/14/24, 4:19 AM

https://developers.cloudflare.com/d1/build-databases/query-databases/#batch-statements

Kkev-ac It was indeed a breaking change for us, even if it was never intended to work th...

conneo•2/14/24, 7:48 AM

Same for us. .all() seems to fix the problem

Kkev-ac It was indeed a breaking change for us, even if it was never intended to work th...

conneo•2/14/24, 7:50 AM

On top of that doc the .run() is mentioned as responding with results array, that's somewhat confusing. On the details section for .run() it's correctly mentioned that there are no results in response