okay, I'm having a table which containing large text, i guess I have to move it now

MMax (@rozenmd)row count doesn't matter as much as DB size - as you approach 2GB, sharding beco...

T

Tony LeungOP•2/26/24, 10:28 AM

is there anyway I can dump a db like old D1? I can't find any command which supports it

TTony Leung okay, I'm having a table which containing large text, i guess I have to move it ...

M

Max (@rozenmd)•2/26/24, 10:28 AM

what's the use case?

TTony Leung is there anyway I can dump a db like old D1? I can't find any command which supp...

M

Max (@rozenmd)•2/26/24, 10:29 AM

we're working on adding dump functionality atm, until then we recommend https://github.com/nora-soderlund/cloudflare-d1-backups

MMax (@rozenmd)what's the use case?

T

Tony LeungOP•2/26/24, 10:31 AM

I mindlessly store a large paragraph (about 4-6k characters each row) into d1 in the development phase, planned to move it to R2 but completely forgot until now

T

Tony LeungOP•2/26/24, 10:31 AM

it's not recommended to store it in any SQL anw, even when FTS is needed i should index it to elasticsearch

M

Max (@rozenmd)•2/26/24, 10:34 AM

ah yeah was going to suggest KV but if you need to search the text, that's harder

T

Tony LeungOP•2/26/24, 10:42 AM

thanks for your support

M

Max (@rozenmd)•2/26/24, 10:50 AM

no worries!

B

bun•2/26/24, 1:06 PM

transactions ?

B

bun•2/26/24, 1:16 PM

like

B

bun•2/26/24, 1:17 PM

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

will something like this work ?

Uulrikstrid What is the easiest way of resetting a local d1? Just delete the file?

M

Max (@rozenmd)•2/26/24, 2:31 PM

pretty much - or keep a bunch of

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS at the start of your schema.sql, you can then add a package.json script that runs

wrangler d1 execute dbname --local --file=schema.sql

wrangler d1 execute dbname --local --file=schema.sql

wrangler d1 execute dbname --local --file=schema.sql

wrangler d1 execute dbname --local --file=schema.sql to reinitialize the db as needed

Ddhrubo Yes. Getting there ... Will report

D

dhrubo•2/26/24, 4:28 PM

seeing response times between 400-600 ms - not ideal but will let clients decide.

Ddhrubo seeing response times between 400-600 ms - not ideal but will let clients decide...

M

Max (@rozenmd)•2/26/24, 4:38 PM

don't use the HTTP API if you want performance.

MMax (@rozenmd)don't use the HTTP API if you want performance.

D

dhrubo•2/26/24, 5:22 PM

Yeh for sure. But received feature request for DB2REST an open source project that I maintain on GitHub . Will add the perf issue as part of docs so that users are aware of it.

Original message was deleted

D

dhrubo•2/26/24, 6:04 PM

Good suggestion will check this option once the non worker version is out of the gate

B

Beyondo•2/26/24, 6:58 PM

What is the point of

D1PreparedStatement

D1PreparedStatement

D1PreparedStatement

D1PreparedStatement if we could just

D1Database->exec()

D1Database->exec()

D1Database->exec()

D1Database->exec()? I get it can be "faster" to prepare a statement and bind different values, but since CloudFlare Workers are temporary, then what's the point? If we know that our worker is always gonna execute the same single query over and over (not multiple queries), does it make a difference in performance?

K

kian•2/26/24, 6:59 PM

Yes.

Kkian Yes.

B

Beyondo•2/26/24, 6:59 PM

For a single query?

K

kian•2/26/24, 6:59 PM

Running it once? No.

K

kian•2/26/24, 6:59 PM

Running a given query multiple times? Yes.

K

kian•2/26/24, 7:00 PM

Your query, as you write it (i.e

SELECT ? FROM foo;

SELECT ? FROM foo;

) is not what SQLite runs. SQLite has to compile that and run it.

K

kian•2/26/24, 7:00 PM

By using prepared statements, you keep around a compiled version that only has to substitute

again.

K

kian•2/26/24, 7:00 PM

If you don't, all of it is compiled every time.

Kkian Running a given query multiple times? Yes.

B

Beyondo•2/26/24, 7:00 PM

Yeah, that's what I was talking about. I guess then it should be fine to just .exec in my case (For example, an auth solution to select a user by email which is just a single database call - single query)

K

kian•2/26/24, 7:00 PM

The difference in performance is likely neglible, but I haven't tested it.

Kkian Your query, as you write it (i.e `SELECT ? FROM foo;`) is not what SQLite runs. ...

B

Beyondo•2/26/24, 7:01 PM

Gotcha.

B

Beyondo•2/26/24, 7:02 PM

So DB->exec basically does the same thing, just automates the process of .binding() and .run()'ing.

Original message was deleted

B

Beyondo•2/26/24, 7:02 PM

If you mean replacing the

?1

?1

it does

Original message was deleted

B

Beyondo•2/26/24, 7:02 PM

It binds, not through a method, but through a parameter

B

Beyondo•2/26/24, 7:03 PM

K

kian•2/26/24, 7:03 PM

Is this an ORM or the D1 API directly?

K

kian•2/26/24, 7:03 PM

:blobcatthonking:

K

kian•2/26/24, 7:03 PM

It definitely doesn't replace anything

B

Beyondo•2/26/24, 7:04 PM

wait, let me see.

B

Beyondo•2/26/24, 7:04 PM

ohh mb, this is my ORM; it looks so like Cloudflare's API, I forgot it existed. :NotLikeThis: I think I made it for safety

Original message was deleted

B

Beyondo•2/26/24, 7:05 PM

Yup mb

B

Beyondo•2/26/24, 7:06 PM

Looks like I'm always using D1Statement all the way then. There's no question using it for me now; to prevent injections))

Original message was deleted

B

Beyondo•2/26/24, 7:09 PM

That would be cool; though multiple queries would count towards the quota anyways, and not sure how the D1Result would handle the success for each to be honest. Have to try at some point

D

dhrubo•2/26/24, 10:12 PM

Running the following query -

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') SELECT at.name table_name, pti.* FROM all_tables at INNER JOIN pragma_table_info(at.name) pti ORDER BY table_name

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') SELECT at.name table_name, pti.* FROM all_tables at INNER JOIN pragma_table_info(at.name) pti ORDER BY table_name

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') SELECT at.name table_name, pti.* FROM all_tables at INNER JOIN pragma_table_info(at.name) pti ORDER BY table_name

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') SELECT at.name table_name, pti.* FROM all_tables at INNER JOIN pragma_table_info(at.name) pti ORDER BY table_name

results in not authorized 7500 - which permission should be assigned to the API key?

K

kian•2/26/24, 10:17 PM

d1-database

K

kian•2/26/24, 10:17 PM

You're probably trying to call

pragma_table_info

pragma_table_info

on protected tables returned by

sqlite_master

sqlite_master

D

dhrubo•2/26/24, 10:19 PM

hmm so only user created tables are allowed?

Ddhrubo hmm so only user created tables are allowed?

I

Isaac McFadyen•2/26/24, 10:20 PM

Some sqlite tables are allowed, but ones that may give away internal/sensitive details are restricted.

I

Isaac McFadyen•2/26/24, 10:20 PM

They exist, it's just that you as a user aren't allowed to query them.

D

dhrubo•2/26/24, 10:21 PM

ok so if its my table i can then let me try

D

dhrubo•2/26/24, 10:21 PM

works

K

kian•2/26/24, 10:21 PM

bool SqlStorage::isAllowedName(kj::StringPtr name) {
  return !name.startsWith("_cf_");
}

bool SqlStorage::isAllowedName(kj::StringPtr name) {
  return !name.startsWith("_cf_");
}

bool SqlStorage::isAllowedName(kj::StringPtr name) {
  return !name.startsWith("_cf_");
}

bool SqlStorage::isAllowedName(kj::StringPtr name) {
  return !name.startsWith("_cf_");
}

K

kian•2/26/24, 10:21 PM

This seems to be the check that the authorizer uses

okay, I'm having a table which containing large text, i guess I have to move it now

Similar Threads