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

if we could just

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

pragma_table_info

pragma_table_info on protected tables returned by

sqlite_master

sqlite_master

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

D

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

D

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

yipee thats all i wanted so i have to run a loop for each table

D

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

@Isaac McFadyen | YYZ01 - your jave library is pretty cool thanks

Ddhrubo @Isaac McFadyen | YYZ01 - your jave library is pretty cool thanks

I

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

:lul: surprised you haven't run into one of the many bugs I haven't gotten around to fixing

I

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

Glad it works though

D

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

Not using it

J

José Junior•2/27/24, 2:03 AM

Hello, hope you all doing well. Can someone tell me if I can expect D1 dbs (leader) located in south america? if yes, is there a ETA?

J

José Junior•2/27/24, 2:05 AM

Also, I read that workers that have a connection to D1 are instantiated close to it, what happens if the worker connects to more than one D1 in different locations?

JJosé Junior Also, I read that workers that have a connection to D1 are instantiated close to...

I

Isaac McFadyen•2/27/24, 2:18 AM

Currently there's only one D1 DB instance located globally per database.

I

Isaac McFadyen•2/27/24, 2:18 AM

That means the DB is initially created close to where you are (or in a region you choose at creation) and then any Worker connects to that specific location. DB instances don't move around after creation.

I

Isaac McFadyen•2/27/24, 2:18 AM

There is a read-replica system planned but it's not currently available so D1 is just a single location for now.

J

José Junior•2/27/24, 3:02 AM

Alright, I've searched around the channel about the South America thing, thank you.

K

Kisa•2/27/24, 6:09 AM

@Max (@rozenmd) @Max (@rozenmd) @kian

KKisa @Max (@rozenmd) @Max (@rozenmd) @kian

M

Matt Silverlock•2/27/24, 6:47 AM

Please don’t ping people directly like this.

B

Beyondo•2/27/24, 11:53 AM

Does this query consume 2 writes, 1 read 1 write, or only 1 write?

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;

Assuming the row already exists in the table?

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

Similar Threads