Given the max row size of 1MB, is there a good pattern for breaking a large blob or JSON string acro

JJason Hostetter Given the max row size of 1MB, is there a good pattern for breaking a large blob...

M

How large are your blobs? You could also write to R2 and reference from D1 - especially for large large blobs / multi MB blobs. No different from an image or binary data in that way.

J

Jason HostetterOP•3/20/24, 12:57 PM

Yeah the issue is I guess it's technically unbounded. Vast majority are fairly small, but some object types can be very big. I have a mechanism for storing large data in R2 with a reference for other columns so I think that is probably the best solution here too. Just wanted to make sure there wasn't some other pattern to take advantage of that would preserve JSON queryability.

JJason Hostetter Yeah the issue is I guess it's technically unbounded. Vast majority are fairly s...

M

Matt Silverlock•3/20/24, 1:13 PM

You could always split across multiple rows - you’ll eat some query and insert complexity but you can still use JSON functions across rows.

J

jordan•3/20/24, 1:24 PM

Just working on a little prototype / test with SvelteKit + D1.

Trying out the REST API on a test database (1 row / col)

import { json } from "@sveltejs/kit";
import { ACCOUNT_ID, CLOUDFLARE_API_KEY, CLOUDFLARE_EMAIL, DATABASE_ID } from '$lib/server/secrets.js';

/** @type {import('./$types').RequestHandler} */
export async function GET({ platform }) {
  const url = `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`;
  const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-Auth-Email': CLOUDFLARE_EMAIL,
      'X-Auth-Key': CLOUDFLARE_API_KEY
    },
    body: '{"sql":"SELECT * from test"}'
  };
  const resp = await fetch(url, options);
  const data = await resp.json();
  return json(data);
}

import { json } from "@sveltejs/kit";
import { ACCOUNT_ID, CLOUDFLARE_API_KEY, CLOUDFLARE_EMAIL, DATABASE_ID } from '$lib/server/secrets.js';

/** @type {import('./$types').RequestHandler} */
export async function GET({ platform }) {
  const url = `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`;
  const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-Auth-Email': CLOUDFLARE_EMAIL,
      'X-Auth-Key': CLOUDFLARE_API_KEY
    },
    body: '{"sql":"SELECT * from test"}'
  };
  const resp = await fetch(url, options);
  const data = await resp.json();
  return json(data);
}

import { json } from "@sveltejs/kit";
import { ACCOUNT_ID, CLOUDFLARE_API_KEY, CLOUDFLARE_EMAIL, DATABASE_ID } from '$lib/server/secrets.js';

/** @type {import('./$types').RequestHandler} */
export async function GET({ platform }) {
  const url = `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`;
  const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-Auth-Email': CLOUDFLARE_EMAIL,
      'X-Auth-Key': CLOUDFLARE_API_KEY
    },
    body: '{"sql":"SELECT * from test"}'
  };
  const resp = await fetch(url, options);
  const data = await resp.json();
  return json(data);
}

import { json } from "@sveltejs/kit";
import { ACCOUNT_ID, CLOUDFLARE_API_KEY, CLOUDFLARE_EMAIL, DATABASE_ID } from '$lib/server/secrets.js';

/** @type {import('./$types').RequestHandler} */
export async function GET({ platform }) {
  const url = `https://api.cloudflare.com/client/v4/accounts/${ACCOUNT_ID}/d1/database/${DATABASE_ID}/query`;
  const options = {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-Auth-Email': CLOUDFLARE_EMAIL,
      'X-Auth-Key': CLOUDFLARE_API_KEY
    },
    body: '{"sql":"SELECT * from test"}'
  };
  const resp = await fetch(url, options);
  const data = await resp.json();
  return json(data);
}

I can't get this query to run fast at all. Am I overlooking somemthing silly (an index)? The live endpoint won't run faster than 300ms

J

jordan•3/20/24, 1:26 PM

Oh geez. I found this:

D1's REST API doesn't involve a Worker, which is why it's considerably slower

D1's REST API doesn't involve a Worker, which is why it's considerably slower

above in the chat. Oy vey.

J

jordan•3/20/24, 1:27 PM

The dev experience for SK + D1 was rough, so I tried this route. I'll try again. https://developers.cloudflare.com/d1/examples/d1-and-sveltekit/

P

Prisma Gestión Empresarial•3/20/24, 2:26 PM

did the migrations script break for anyone? I got a github action that always runs

d1 migrations apply <DB>

d1 migrations apply <DB>

and it always did a check to see if there where any new migrations to apply and applied them (if none, then did nothing).

but since my last commit ~30 mins ago, it started applying them all over again (not just the new ones)

SSam I'm using https://dataflare.app

P

Patrick•3/20/24, 2:28 PM

Ah cool I was looking for a lightweight one of these, any idea if there's anything similar to drizzle-kit studio that can be npm installed and used directly from the project?

V

vnphanquang•3/21/24, 5:25 AM

Hi all, is it safe to expose

database_id

database_id

in

wrangler.toml

wrangler.toml

in public repo? If no is there any convenient alternative rather than using a separate secret wrangler.toml? Thank you

D

down•3/21/24, 8:46 AM

Hi, is there any way to connect the d1 locally without a worker? I going to write a migration script with typescript. Thanks

Ddown Hi, is there any way to connect the d1 locally without a worker? I going to writ...

D

dakom•3/21/24, 9:08 AM

I think it's safe to just connect to the local sqlite files directly. By default they'll be somewhere in

.wrangler/state/...

.wrangler/state/...

but you can set the path explicitly when you start the worker with

--persist-to={path}

--persist-to={path}

Ddakom I _think_ it's safe to just connect to the local sqlite files directly. By defau...

D

down•3/21/24, 9:24 AM

Thanks i will try it

AAaronO @down You can usually do `wrangler d1 migrations apply <YOUR_DB> --local`, assum...

D

down•3/21/24, 9:56 AM

Thanks, but i have to connect the db to read and write back, not just import data

AAaronO @down Would that work ?

D

down•3/21/24, 10:59 AM

yes, direct access to .sqlite works and I'll probably make a temporary entry point for remote modifications, thanks.

S

squareclamp•3/21/24, 6:17 PM

I like how my queries are all 0ms

We should just remove the y-axis.

A

Amblamps•3/21/24, 7:49 PM

How does D1 handle concurrent access? Should we be using queues in front of it?

AAmblamps How does D1 handle concurrent access? Should we be using queues in front of it?

I

Isaac McFadyen•3/21/24, 7:53 PM

Concurrent access is completely fine.

I

Isaac McFadyen•3/21/24, 7:54 PM

It's like a normal SQLite database in that respect.

C

Chaika•3/21/24, 7:56 PM

It is, at least right now, a single threaded Durable Object so if you slam it too hard with writes you can kill it. It performs quite well though, idk what the upper limit would be, and it would depend on other factors too like what exactly you're writing

M

Max (@rozenmd)•3/21/24, 8:05 PM

is it a bug or are they under 1ms

Mmare arcis can you clarify "kill it"? DB corruption? extremely slowness until recovery?

C

Chaika•3/21/24, 8:07 PM

No corruption. From what I've seen, it just gets really slow/sometimes becomes overloaded and stops working for "a bit" until it fixes itself/writes slow down.

CChaika It is, at least right now, a single threaded Durable Object so if you slam it to...

S

Sam•3/21/24, 10:22 PM

What about reads. How many per second can it handle? About 20k people can open our app on a push notification. To that’s 20k queries in about 2-3 minutes.

Is D1 able to handle that? There aren’t any writes.

SSam What about reads. How many per second can it handle? About 20k people can open o...

M

Max (@rozenmd)•3/22/24, 7:53 AM

a single DO instance can do around 1k requests per second, for complicated queries i've seen it drop as low as 400 requests per second - if in doubt, use the cache api where possible

V

vakno•3/22/24, 10:04 AM

hello i need some help with indexes. Pretty new to this so sorry if this seems stupid.

i created table

  CREATE TABLE IF NOT EXISTS ProductData (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      url TEXT,
      price NUMBER,
      description TEXT
  )

  CREATE TABLE IF NOT EXISTS ProductData (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      url TEXT,
      price NUMBER,
      description TEXT
  )

  CREATE TABLE IF NOT EXISTS ProductData (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      url TEXT,
      price NUMBER,
      description TEXT
  )

  CREATE TABLE IF NOT EXISTS ProductData (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      url TEXT,
      price NUMBER,
      description TEXT
  )

then i created index

  'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (price, description)'

  'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (price, description)'

  'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (price, description)'

  'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (price, description)'

when i query table i get same numbers of row read as without index.

SELECT * FROM ProductData

SELECT * FROM ProductData

SELECT * FROM ProductData

SELECT * FROM ProductData

What am i doing wrong?

Vvakno hello i need some help with indexes. Pretty new to this so sorry if this seems s...

S

Sam•3/22/24, 10:27 AM

Because you’re not using a filter in the query. You’re basically requesting all rows with that query

SSam Because you’re not using a filter in the query. You’re basically requesting all ...

V

vakno•3/22/24, 10:38 AM

oh okay. Are there any other ways i can reduce rows read everytime route is triggered? Any caching systems?

MMax (@rozenmd)a single DO instance can do around 1k requests per second, for complicated queri...

S

Sam•3/22/24, 10:38 AM

Max mentions here that you could use the cache api @joker

SSam Max mentions here that you could use the cache api @joker

V

vakno•3/22/24, 10:40 AM

thanks!

A

a_sasin•3/22/24, 11:00 AM

How would I seed a local database every time it’s created? A migration would end up being applied to the prod db

Original message was deleted

S

Sam•3/22/24, 11:03 AM

https://developers.cloudflare.com/workers/runtime-apis/cache/

Cloudflare Docs

Cache · Cloudflare Workers docs

Control reading and writing from the Cloudflare global network cache.

Vvakno hello i need some help with indexes. Pretty new to this so sorry if this seems s...

M

Max (@rozenmd)•3/22/24, 11:27 AM

an index is only useful if you're looking for a specific record/types of records.

a good rule of thumb is, if you're using a WHERE clause on anything other than the PRIMARY KEY on a query that runs often, it might be worth indexing it

if you had a column like

user_id

user_id

user_id

user_id on

ProductData

ProductData

ProductData

ProductData, you could add an index for that column, and reduce your rows read when querying

ProductData

ProductData

ProductData

ProductData via

SELECT * FROM ProductData WHERE user_id = ?

SELECT * FROM ProductData WHERE user_id = ?

SELECT * FROM ProductData WHERE user_id = ?

SELECT * FROM ProductData WHERE user_id = ?

Aa_sasin How would I seed a local database every time it’s created? A migration would end...

M

Max (@rozenmd)•3/22/24, 11:29 AM

add

--local

--local

when migrating

MMax (@rozenmd)add `--local` when migrating

A

a_sasin•3/22/24, 11:29 AM

But then the seed data migration will be applied to

—remote

—remote

as well when deploying

Aa_sasin But then the seed data migration will be applied to `—remote` as well when deplo...

M

Max (@rozenmd)•3/22/24, 11:31 AM

I'm not sure I understand what you're trying to do here, no queries/migrations will be run against your remote DB unless you explicitly add the

--remote

--remote

flag

E

Enduriel•3/22/24, 11:43 AM

say I have 100s or 1000s of rows I'd like to be able to insert during a query, but those rows are based on user input in such a way where I'd like to prevent sql injection, what's the proper way to handle this scenario?

should I just create a separate d1 statement for each row and bind them individually and then batch that all, is there a clever way to make this into one statement?

I want to create something equivalent to:

INSERT OR IGNORE INTO your_table_name (column1, column2, unique_column)
VALUES 
    (value1_1, value1_2, unique_value1),
    (value2_1, value2_2, unique_value2),
    (value3_1, value3_2, unique_value3),
    ...
    (valueN_1, valueN_2, unique_valueN);

INSERT OR IGNORE INTO your_table_name (column1, column2, unique_column)
VALUES 
    (value1_1, value1_2, unique_value1),
    (value2_1, value2_2, unique_value2),
    (value3_1, value3_2, unique_value3),
    ...
    (valueN_1, valueN_2, unique_valueN);

INSERT OR IGNORE INTO your_table_name (column1, column2, unique_column)
VALUES 
    (value1_1, value1_2, unique_value1),
    (value2_1, value2_2, unique_value2),
    (value3_1, value3_2, unique_value3),
    ...
    (valueN_1, valueN_2, unique_valueN);

INSERT OR IGNORE INTO your_table_name (column1, column2, unique_column)
VALUES 
    (value1_1, value1_2, unique_value1),
    (value2_1, value2_2, unique_value2),
    (value3_1, value3_2, unique_value3),
    ...
    (valueN_1, valueN_2, unique_valueN);

E

Enduriel•3/22/24, 11:47 AM

ah yeah

E

Enduriel•3/22/24, 11:47 AM

obviously

Original message was deleted

E

Enduriel•3/22/24, 11:47 AM

wow I feel kinda dumb lol, tysm

MMax (@rozenmd)an index is only useful if you're looking for a specific record/types of records...

V

vakno•3/22/24, 12:02 PM

i tried it but still i get same amount of rows read. i deleted the table and created the index. Am i missing something?

my index

'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (url)'

'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (url)'

'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (url)'

'CREATE INDEX IF NOT EXISTS idx_url ON ProductData (url)'

my query

SELECT * FROM ProductData WHERE url = ?

SELECT * FROM ProductData WHERE url = ?

SELECT * FROM ProductData WHERE url = ?

SELECT * FROM ProductData WHERE url = ?

Vvakno i tried it but still i get same amount of rows read. i deleted the table and cre...

M

Max (@rozenmd)•3/22/24, 12:03 PM

how many rows is the table?

MMax (@rozenmd)how many rows is the table?

V

vakno•3/22/24, 12:03 PM

100

Given the max row size of 1MB, is there a good pattern for breaking a large blob or JSON string acro

Similar Threads

Similar Threads

Similar Threads