```ts const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND n

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' } })));

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

They are not

.sql

.sql

dumps, but I can use this and recreate it.

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

I just save to R2

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

(I know I can avoid a lot of the date handling with a .toISOString().toISOString(), but it was multiple changes that led to this and I'm not bothered enough to redo it just yet ahah)

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

still cool, nice

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

I ended up updating it now lul

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

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

.raw()

.raw()

and not

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

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

We're looking at instead adding a .values().values() API that just returns an array of values, without column names.

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?

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

TEXT

LLeftSpace should i store json as a blob or text?

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

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

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.causeError.cause: https://developers.cloudflare.com/d1/changelog/#deprecating-errorcause

PPrisal Any estimate on when d1 transactions will be available?

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. 🤪

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

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.

Docs: 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...

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

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

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

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

That would be really cool

Understand the next message, but very cool.

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
    }
  ],

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

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

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

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

i'll get it one moment

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

welp nevermind, it was a PRAGMA for user_versionuser_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...

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

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

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

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

not implemented yet

MMax (@rozenmd)not implemented yet 😅

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

Ok, "known" issue then lul

no problem ahah

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?

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

I mean both are inheritly unstable given its alpha

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

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

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

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

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

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

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

Max (@rozenmd)•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

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 ?

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

It is possible

Unsmart•6/29/23, 9:25 PM

d1-database

```ts const tables = await env.DB.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND n

Similar Threads

Similar Threads

Similar Threads