Running a given query multiple times? Yes.

K

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

Your query, as you write it (i.e

SELECT ? FROM foo;

SELECT ? FROM foo;

SELECT ? FROM foo;

SELECT ? FROM foo;) is not what SQLite runs. SQLite has to compile that and run it.

K

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

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

?

? again.

K

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

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

?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

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

Is this an ORM or the D1 API directly?

K

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

:blobcatthonking:

K

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

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

d1-database

K

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

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

kianOP•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?

B

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

And what about this one:

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Original message was deleted

B

Beyondo•2/27/24, 12:01 PM

Yeah, just AFK at the moment but was really curious so asked here)) Would make for a useful ORM

.set()

.set()

.set()

.set() method if one takes only 1 write.

B

Beyondo•2/27/24, 12:01 PM

Well, when I'm back ig.

BBeyondo Yeah, just AFK at the moment but was really curious so asked here)) Would make f...

M

Max (@rozenmd)•2/27/24, 12:54 PM

not to bikeshed your ORM but i think others call that

.upsert()

.upsert()

.upsert()

.upsert()

MMax (@rozenmd)not to bikeshed your ORM but i think others call that `.upsert()`

B

Beyondo•2/27/24, 1:00 PM

Sorry for not giving enough context, I'm building an ORM with an optional document-style interface, so my .set() is actually part of my DocumentReference class, not of the database itself

M

Max (@rozenmd)•2/27/24, 1:01 PM

ah

E

Epailes•2/27/24, 4:11 PM

I could swear I saw a comment from a dev/rep around timeline when being able to dynamically create/bind d1 instances (properly, not a hack with the API), and improvements to migrations on d1 instances, but can't find it for the life of me searching on discord - I could be misremembering!

Can anyone confirm if that is planned for GA? Or if that's likely to be :soontm:

Running a given query multiple times? Yes.

Similar Threads

Similar Threads

Similar Threads