I'm curious - how? - Cloudflare Developers

I

itsmatteomanf•6/27/23, 12:48 PM

const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'").all();
const date = new Date();
const year = date.getFullYear();
const month = `00${date.getUTCMonth() + 1}`.slice(-2);
const day = `00${date.getUTCDate()}`.slice(-2);
const hour = `00${date.getUTCHours()}`.slice(-2);
const minutes = `00${date.getUTCMinutes()}`.slice(-2);
const seconds = `00${date.getUTCSeconds()}`.slice(-2);

const tableValues = await env.DB.batch(tables.results!.map(table => env.DB.prepare(`SELECT * FROM ${table}`)));
const tableNames = tables.results!.map(table => `backups/raw-tables/${year}/${month}/${day}/${year}-${month}-${day}T${hour}:${minutes}:${seconds}.000Z-${table.name}.json`);

await Promise.all(tableValues.map((tableValue, index) => env.R2.put(tableNames[index], JSON.stringify(tableValue.results), { httpMetadata: { contentType: 'application/json' } })));

const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'").all();
const date = new Date();
const year = date.getFullYear();
const month = `00${date.getUTCMonth() + 1}`.slice(-2);
const day = `00${date.getUTCDate()}`.slice(-2);
const hour = `00${date.getUTCHours()}`.slice(-2);
const minutes = `00${date.getUTCMinutes()}`.slice(-2);
const seconds = `00${date.getUTCSeconds()}`.slice(-2);

const tableValues = await env.DB.batch(tables.results!.map(table => env.DB.prepare(`SELECT * FROM ${table}`)));
const tableNames = tables.results!.map(table => `backups/raw-tables/${year}/${month}/${day}/${year}-${month}-${day}T${hour}:${minutes}:${seconds}.000Z-${table.name}.json`);

await Promise.all(tableValues.map((tableValue, index) => env.R2.put(tableNames[index], JSON.stringify(tableValue.results), { httpMetadata: { contentType: 'application/json' } })));

const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'").all();
const date = new Date();
const year = date.getFullYear();
const month = `00${date.getUTCMonth() + 1}`.slice(-2);
const day = `00${date.getUTCDate()}`.slice(-2);
const hour = `00${date.getUTCHours()}`.slice(-2);
const minutes = `00${date.getUTCMinutes()}`.slice(-2);
const seconds = `00${date.getUTCSeconds()}`.slice(-2);

const tableValues = await env.DB.batch(tables.results!.map(table => env.DB.prepare(`SELECT * FROM ${table}`)));
const tableNames = tables.results!.map(table => `backups/raw-tables/${year}/${month}/${day}/${year}-${month}-${day}T${hour}:${minutes}:${seconds}.000Z-${table.name}.json`);

await Promise.all(tableValues.map((tableValue, index) => env.R2.put(tableNames[index], JSON.stringify(tableValue.results), { httpMetadata: { contentType: 'application/json' } })));

const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'").all();
const date = new Date();
const year = date.getFullYear();
const month = `00${date.getUTCMonth() + 1}`.slice(-2);
const day = `00${date.getUTCDate()}`.slice(-2);
const hour = `00${date.getUTCHours()}`.slice(-2);
const minutes = `00${date.getUTCMinutes()}`.slice(-2);
const seconds = `00${date.getUTCSeconds()}`.slice(-2);

const tableValues = await env.DB.batch(tables.results!.map(table => env.DB.prepare(`SELECT * FROM ${table}`)));
const tableNames = tables.results!.map(table => `backups/raw-tables/${year}/${month}/${day}/${year}-${month}-${day}T${hour}:${minutes}:${seconds}.000Z-${table.name}.json`);

await Promise.all(tableValues.map((tableValue, index) => env.R2.put(tableNames[index], JSON.stringify(tableValue.results), { httpMetadata: { contentType: 'application/json' } })));

I

itsmatteomanf•6/27/23, 12:48 PM

They are not

.sql

.sql

.sql

.sql dumps, but I can use this and recreate it.

I

itsmatteomanf•6/27/23, 12:49 PM

I just save to R2

I

itsmatteomanf•6/27/23, 12:53 PM

(I know I can avoid a lot of the date handling with a

.toISOString()

.toISOString()

.toISOString()

.toISOString(), but it was multiple changes that led to this and I'm not bothered enough to redo it just yet ahah)

M

Max (@rozenmd)OP•6/27/23, 1:11 PM

still cool, nice

I

itsmatteomanf•6/27/23, 1:28 PM

I ended up updating it now lul

I

itsmatteomanf•6/27/23, 1:30 PM

I'd love to be able to get them all with

.raw()

.raw()

.raw()

.raw() and not

.all()

.all()

.all()

.all() as batch does. It'd be more space efficient than a JSON object. But doing the processing on tens and tens of thousands of rows doesn't seem useful.

Iitsmatteomanf I'd love to be able to get them all with `.raw()` and not `.all()` as batch does...

M

Matt Silverlock•6/27/23, 2:29 PM

We're looking at instead adding a

.values()

.values()

.values()

.values() API that just returns an array of values, without column names.

L

LeftSpace•6/27/23, 3:25 PM

should i store json as a blob or text?

LLeftSpace should i store json as a blob or text?

M

Matt Silverlock•6/27/23, 3:48 PM

TEXT

LLeftSpace should i store json as a blob or text?

M

Matt Silverlock•6/27/23, 3:48 PM

https://developers.cloudflare.com/d1/learning/querying-json/#types

M

Matt Silverlock•6/27/23, 3:50 PM

New docs & updates:

Metrics & analytics (now in the dash & via our GraphQL API): https://developers.cloudflare.com/d1/platform/metrics-analytics/
Generated columns (very powerful way to extract + index JSON, amongst other things): https://developers.cloudflare.com/d1/learning/generated-columns/
Deprecating
```
Error.cause
```
Error.cause
```
Error.cause
```
Error.cause
: https://developers.cloudflare.com/d1/changelog/#deprecating-errorcause

PPrisal Any estimate on when d1 transactions will be available?

M

Matt Silverlock•6/27/23, 7:21 PM

No ETA at the moment. We are focused on metrics, Time Travel (point in time recovery), and stability.

DDrinahn Re my index/table issue. I was the issue. ie: I’m a donkey. 🤪

M

Matt Silverlock•6/27/23, 8:15 PM

Curious: what happened?

MMatt Silverlock New docs & updates: - Metrics & analytics (now in the dash & via our GraphQL API...

M

Matt Silverlock•6/27/23, 8:18 PM

https://twitter.com/elithrar/status/1673753790552440833

Matt Silverlock 🐀 (@elithrar)

If you've used @Cloudflare D1, you may not realize that you can create generated (aka: virtual) columns based on properties within raw JSON objects

indexes over those properties to (dramatically) speed up access to often-queried properties.

ocs: https://t.co/EfzQ9RS4I0

Twitter

•

6/27/23, 6:02 PM

FFerry I've got a question about the write-ahead-log. Any change we could access that p...

M

Matt Silverlock•6/27/23, 8:51 PM

I think triggers are the right approach here and/or a formal audit logging feature.

FFerry Ok , thanks, was hoping to minimize my (future) datapricing somewhat 😉 . Anoth...

M

Matt Silverlock•6/27/23, 9:17 PM

At some point we want to expose Workers-based functions. We won’t be exposing raw SQLite APIs.

FFerry I understand, so you could reference this worker based function in your sql stat...

M

Matt Silverlock•6/27/23, 9:31 PM

That’s the rough plan. Won’t be soon — lots of more foundational things to solve first

MMatt Silverlock At some point we want to expose Workers-based functions. We won’t be exposing ra...

I

itsmatteomanf•6/27/23, 10:34 PM

That would be really cool

Understand the next message, but very cool.

O

omega24•6/28/23, 2:11 AM

I might be really stupid here, however when using a D1 database locally it works fine, however when I push the same code to running on workers, it produces this error:

"exceptions": [
    {
      "name": "Error",
      "message": "D1_ERROR: Error: not authorized",
      "timestamp": 1687918209429
    }
  ],

"exceptions": [
    {
      "name": "Error",
      "message": "D1_ERROR: Error: not authorized",
      "timestamp": 1687918209429
    }
  ],

"exceptions": [
    {
      "name": "Error",
      "message": "D1_ERROR: Error: not authorized",
      "timestamp": 1687918209429
    }
  ],

"exceptions": [
    {
      "name": "Error",
      "message": "D1_ERROR: Error: not authorized",
      "timestamp": 1687918209429
    }
  ],

If more info is needed please lmk, I'm likely missing something really simple but thats why I'm here

K

kian•6/28/23, 2:17 AM

Do you have an example of the query you're running into issues with?

O

omega24•6/28/23, 2:18 AM

i'll get it one moment

O

omega24•6/28/23, 2:20 AM

welp nevermind, it was a PRAGMA for

user_version

user_version

user_version

user_version and I just read the docs on PRAGMA so I'll come up with a workaround - ty

Oomega24 welp nevermind, it was a PRAGMA for `user_version` and I just read the docs on P...

M

Matt Silverlock•6/28/23, 12:57 PM

Curious: what are you trying to do with storing data in user_version?

I

itsmatteomanf•6/28/23, 4:42 PM

It's probably a known issue, but in the metrics tab these don't show anything, in other graphs they do.

Iitsmatteomanf It's probably a known issue, but in the metrics tab these don't show anything, i...

M

Max (@rozenmd)OP•6/28/23, 8:31 PM

not implemented yet

MMax (@rozenmd)not implemented yet 😅

I

itsmatteomanf•6/28/23, 8:31 PM

Ok, "known" issue then lul

no problem ahah

A

Adi•6/29/23, 1:18 AM

Out of curiosity, what are your thoughts on the stability of the experimental backend vs the old backend for D1?
I have a few projects that are using the old D1 backend, is it worth switching now or should we wait for the beta?

U

Unsmart•6/29/23, 1:19 AM

I mean both are inheritly unstable given its alpha

U

Unsmart•6/29/23, 1:19 AM

The new backend should be faster but doesnt have backups at the current moment afaik

UUnsmart I mean both are inheritly unstable given its alpha

A

Adi•6/29/23, 1:23 AM

Yup, asking with the assumption of the alpha tag haha. I more asked because when the new backend came out, I noticed that there were enough obvious errors that switching didn't make sense. Wondering if the situation has improved now to the point that a general happy path (simple query + insert statements with low load) isn't expected to encounter errors.

AAdi Yup, asking with the assumption of the alpha tag haha. I more asked because when...

C

Cyb3r-Jak3•6/29/23, 1:28 AM

The execution times are noticeably better and there haven't really been many major issues I've seen in this channel. Do keep in mind that the new backend doesn't have backups yet

A

Adi•6/29/23, 1:30 AM

Got it, that's good to know

. I suppose once backups are available it makes sense to switch then

AAdi Got it, that's good to know 🙂 . I suppose once backups are available it makes s...

C

Cyb3r-Jak3•6/29/23, 1:36 AM

Keep in mind it isn't going to be a sql dumb zip but Time Travel.
https://blog.cloudflare.com/d1-turning-it-up-to-11/ search for Time Travel header

P

pl•6/29/23, 8:23 AM

i have a decent oltp db in mysql, about 150gb, that i would love to get into d1 so the performance is going to be a real issue

P

pl•6/29/23, 10:37 AM

i have workers serving 500 req/sec out of kv at the moment but im really interested in having the backend db in d1, pushing aggregated data into kv

M

Max (@rozenmd)OP•6/29/23, 11:16 AM

fwiw #analytics-engine-beta is ridiculously good for high rps analytics data, we're using it to power the stats in the dashboard

M

Mirza A. Marotsaha•6/29/23, 2:28 PM

Hello, I planned to use d1 on a future product. I'm asking if there will be some breaking change from the alpha release to the production ready d1 ?

U

Unsmart•6/29/23, 2:33 PM

It is possible

I'm curious - how?

Similar Threads