`select user.id as userid, data.id as dataid from user, data`

V

vassbenceOP•6/25/23, 8:03 PM

basially you just have to give a name for the keys which are the same

Vvassbence `select user.id as userid, data.id as dataid from user, data`

I

itsmatteomanf•6/25/23, 8:10 PM

This is what I wanted to do, @yamiteru

W

Walshy•6/26/23, 10:04 AM

https://developers.cloudflare.com/workers/wrangler/configuration/#d1-databases

MMr.D3V1L Is there a object like the one for KV: kv_namespaces = [ { binding = "<TEST_NA...

M

Matt Silverlock•6/26/23, 10:22 AM

Also strongly recommend following the guide: https://developers.cloudflare.com/d1/get-started/

Get started · Cloudflare D1 docs

This guide will instruct you through:

MMr.D3V1L I am getting error at: https://developers.cloudflare.com/d1/get-started/#configu...

M

Max (@rozenmd)•6/26/23, 10:31 AM

which version of wrangler are you using?

M

Max (@rozenmd)•6/26/23, 10:34 AM

personally:

% wrangler -v
 ⛅️ wrangler 3.1.0 (update available 3.1.1)
-----------------------------------------------------

% wrangler -v
 ⛅️ wrangler 3.1.0 (update available 3.1.1)
-----------------------------------------------------

% wrangler -v
 ⛅️ wrangler 3.1.0 (update available 3.1.1)
-----------------------------------------------------

% wrangler -v
 ⛅️ wrangler 3.1.0 (update available 3.1.1)
-----------------------------------------------------

MMr.D3V1L 1.20.0

M

Max (@rozenmd)•6/26/23, 10:35 AM

https://developers.cloudflare.com/workers/wrangler/install-and-update/#installupdate-wrangler

M

Max (@rozenmd)•6/26/23, 10:36 AM

you might need to follow these steps to remove v1: https://developers.cloudflare.com/workers/wrangler/migration/v1-to-v2/update-v1-to-v2/#1-uninstall-wrangler-v1

LLiudmila Hey everyone! Quick question to D1 devs: (you're awesome:) do you guys plan to d...

A

Adi•6/26/23, 2:08 PM

If you're on the old backend for D1, it should take backups every few hours automatically.
Afaik time travel for the new backend is planned but no release date is set.

J

John Spurlock•6/26/23, 5:44 PM

D1 preparing for a world with fractional bytes! : )

DDrinahn I noticed an interesting behaviour. I have table with a unique index on a user-p...

M

Matt Silverlock•6/26/23, 9:42 PM

Can you show your queries? I would expect the index does not retain a reference.

LLiudmila Hey everyone! Quick question to D1 devs: (you're awesome:) do you guys plan to d...

I

itsmatteomanf•6/27/23, 11:40 AM

I personally do these myself at the moment on the new backend.

Iitsmatteomanf I personally do these myself at the moment on the new backend.

M

Max (@rozenmd)•6/27/23, 12:47 PM

I'm curious - how?

MMax (@rozenmd)I'm curious - how?

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