Share all of your code. Does your query return anything at all? Are you sure? If your bound values a

CCatt0s Not sure what I'm doing wrong, and I'm not experienced with JS or SQL, but 1) t...

M

Matt SilverlockOP•7/4/23, 11:52 PM

You should log your values BEFORE you run the query to ensure they are not null

MMatt Silverlock You should log your values BEFORE you run the query to ensure they are not null

C

Catt0s•7/5/23, 2:29 AM

can you explain what you mean? I am only using static test data for now, and the query I am running (which i log to console as well) works fine in the D1 console.
logged query:

SELECT * from items where id=(SELECT item_id FROM parts WHERE ? in (name, id))

SELECT * from items where id=(SELECT item_id FROM parts WHERE ? in (name, id))

SELECT * from items where id=(SELECT item_id FROM parts WHERE ? in (name, id))

SELECT * from items where id=(SELECT item_id FROM parts WHERE ? in (name, id))
binded

?

? to

. Here it is in console:

MMatt Silverlock Share all of your code. Does your query return anything at all? Are you sure? If...

C

Catt0s•7/5/23, 2:46 AM

I was creating this minimum code to reproduce my issue:

export async function onRequestGet(context) {
  const value = 0; // normally this is from the URL
  var partialQuery = "SELECT item_id FROM ";
  partialQuery+= 'categories';
  partialQuery+=" WHERE ? in (name, id)";
  var query = "SELECT * from items where id=("+partialQuery+")";
  console.log(query);
  var result = context.env.DB.
    prepare(query).bind(value);
  const res = await result.raw();
  console.log("RESULT: "+res);
  return new Response("");
}

export async function onRequestGet(context) {
  const value = 0; // normally this is from the URL
  var partialQuery = "SELECT item_id FROM ";
  partialQuery+= 'categories';
  partialQuery+=" WHERE ? in (name, id)";
  var query = "SELECT * from items where id=("+partialQuery+")";
  console.log(query);
  var result = context.env.DB.
    prepare(query).bind(value);
  const res = await result.raw();
  console.log("RESULT: "+res);
  return new Response("");
}

export async function onRequestGet(context) {
  const value = 0; // normally this is from the URL
  var partialQuery = "SELECT item_id FROM ";
  partialQuery+= 'categories';
  partialQuery+=" WHERE ? in (name, id)";
  var query = "SELECT * from items where id=("+partialQuery+")";
  console.log(query);
  var result = context.env.DB.
    prepare(query).bind(value);
  const res = await result.raw();
  console.log("RESULT: "+res);
  return new Response("");
}

export async function onRequestGet(context) {
  const value = 0; // normally this is from the URL
  var partialQuery = "SELECT item_id FROM ";
  partialQuery+= 'categories';
  partialQuery+=" WHERE ? in (name, id)";
  var query = "SELECT * from items where id=("+partialQuery+")";
  console.log(query);
  var result = context.env.DB.
    prepare(query).bind(value);
  const res = await result.raw();
  console.log("RESULT: "+res);
  return new Response("");
}

and in doing so, I have found the problem(s)/possible bugs(? I do not know as I am not experienced with databases):

When getting parameters from the URL they are lowercase and are strings.

I was under the impression due to https://developers.cloudflare.com/d1/platform/client-api/#type-conversion that it would be converted for me. So, I need to solve the following problems:
1) Numbers in my url need to be changed from

strings

strings

strings

strings to

int

int

int

ints.
2) I need to make my query non case-sensitive

C

Catt0s•7/5/23, 2:47 AM

in other words, my

value

value

value

value was

'0'

'0'

'0'

'0' and not

and my

'Test_Entry'

'Test_Entry'

'Test_Entry'

'Test_Entry' was

'test_entry'

'test_entry'

'test_entry'

'test_entry' which is why nothing was working

B

bkyerv•7/5/23, 8:52 AM

I can clearly list all production d1 dbs by running

wrangler d1 list

wrangler d1 list

wrangler d1 list

wrangler d1 list. is there similar way to detect what local d1 dbs are available?

B

bkyerv•7/5/23, 8:54 AM

This is important because when running into errors it helps a lot to first check if the local db has been properly set and if the migration ran successfully against the db (i.e. being able to check the contents of the db). In other words it would help to check potential causes of the error when running queries

B

bkyerv•7/5/23, 9:03 AM

To enhance the user experience when working with different environments in our local development setup, it would be cool if an api has been similar to Python's environment management. For instance, we could introduce a command like

wrangler source dev

wrangler source dev

wrangler source dev

wrangler source dev. Upon execution, this command would switch to the development environment. As a result, any subsequent command using wrangler would interact with local development databases and settings. This approach could simplify the development workflow by eliminating potential confusions related to various flags such as --persist, --local, and others.

B

bkyerv•7/5/23, 9:04 AM

this is just speaking out my ideas/desire and not a complain or criticism

B

bkyerv•7/5/23, 9:19 AM

running

wrangler d1 execute <database-name> --local --file ./schema.sql

wrangler d1 execute <database-name> --local --file ./schema.sql

wrangler d1 execute <database-name> --local --file ./schema.sql

wrangler d1 execute <database-name> --local --file ./schema.sql command creates a db in the

.wrangler

.wrangler

.wrangler

.wrangler folder. Is it possible to set a different location to put the db to?

B

bkyerv•7/5/23, 9:22 AM

turns out yes it is possible to set the location by adding --persist-to flag to the command above. Now when I figured it it seems obvious, but when reading the docs it is not that obvious you can add flags like --persist-to not only to the wrangler dev command as shown in the docs as an example but to other commands as well

MMatt Silverlock We have some ideas here for the “thousands to tens of thousands of tenants” - th...

G

GuillaumeLakano•7/5/23, 1:24 PM

If D1 could have unlimited databases like we have unlimited DO (and D1 is based on DO...) this could permit us to add a very strong security layer by associate a dedicated D1 DB to each user, but this is not possible if it's limited (even « thousands to tens of thousands of tenants » )

GGuillaumeLakano If D1 could have unlimited databases like we have unlimited DO (and D1 is based ...

I

itsmatteomanf•7/5/23, 3:13 PM

Do note that DOs will, eventually, expose their own private SQL instance, with the same interface (or similar, at least) as D1. Which might solve your problem

Ssebosp Hello! Does anybody know if something is going on? I suddenly start receiving th...

C

Cyb3r-Jak3•7/5/23, 4:31 PM

pages-discussions

Ssebosp thank you, what is this reference for?

C

Cyb3r-Jak3•7/5/23, 4:40 PM

There is an incident going on with workers which could be causing the error code

Jjschlesser Is there a way to see what the cost of a given D1 query is, in terms of billable...

M

Matt SilverlockOP•7/5/23, 10:22 PM

Not yet, but we will (before we start billing) instrument responses with this info. You should expect to see readUnits and/or writeUnits for any given query or batch.

Jjschlesser Is there a way to see what the cost of a given D1 query is, in terms of billable...

M

Matt SilverlockOP•7/5/23, 10:26 PM

Reads & writes primarily map to underlying pages. A full table scan of (e.g) 100k rows @ 200B each = ~5,000 read units. A single row lookup on an indexed column would typically be 2 units (one for the index, one for the row itself).

Y

yat•7/6/23, 12:42 AM

can i access D1 externally ?

Yyat can i access D1 externally ?

C

Cyb3r-Jak3•7/6/23, 12:45 AM

Not officially. There is an HTTP API but is isn't documented and isn't stable

Vvikim I want to learn more ways to use D1 for reference, I wonder if discord masters c...

M

Matt SilverlockOP•7/6/23, 1:40 AM

Are you looking for our docs? https://developers.cloudflare.com/d1

Cloudflare D1 · Cloudflare D1 docs

D1 is Cloudflare’s native serverless database. Create your first D1 database by following the Get started guide, learn how to import data into a …

CCyb3r-Jak3 Not officially. There is an HTTP API but is isn't documented and isn't stable

Y

yat•7/6/23, 10:35 AM

also are changes instant ? like immediatly visible in all cf locations

Yyat also are changes instant ? like immediatly visible in all cf locations

M

Max (@rozenmd)•7/6/23, 10:59 AM

we haven't turned on replication yet, so yep

MMax (@rozenmd)we haven't turned on replication yet, so yep

A

Adi•7/6/23, 11:59 AM

we all await the magical day when this becomes reality

AAdi we all await the magical day when this becomes reality

M

Max (@rozenmd)•7/6/23, 12:22 PM

same

M

Mozzy•7/6/23, 12:49 PM

Dumb question... What is replication and why is it wanted if changes are not immediately visible in all locations?

V

vassbence•7/6/23, 12:50 PM

single write multipe reader is good because you can move a read only copy of your database close to your users

V

vassbence•7/6/23, 12:50 PM

eg your writer instance is in Europe but users in Sydney can read data from their local Sydney reader replica

MMozzy Dumb question... What is replication and why is it wanted if changes are not imm...

M

Matt SilverlockOP•7/6/23, 2:14 PM

In most user-facing / web application databases, ~80% of your queries are just reads. Getting those closer to users = better app performance

C

Chaika•7/6/23, 2:18 PM

My D1 DB running the new experimental backend has stopped working entirely, any clue on why? All Queries return

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset., doesn't load in the dashboard at all either, "Failed to reach database. Please try again later."
972558fd-cadf-4bec-8b0b-5a140d7d51fb
I waited a bit to see if it would come back after a bit, but it seems hard dead

M

Mozzy•7/6/23, 2:18 PM

How does that work with data consistency? Let's say I write to it, and I want the user in any location to immediately be able to get that fresh write?

Can I opt-in to / choose this in any way?

MMozzy How does that work with data consistency? Let's say I write to it, and I want th...

M

Matt SilverlockOP•7/6/23, 2:29 PM

We’ll manage that behind the scenes so you can read your writes. When we ship replication there will be docs covering all of this

CChaika My D1 DB running the new experimental backend has stopped working entirely, any ...

M

Matt SilverlockOP•7/6/23, 2:44 PM

Looking. Is this still occurring?

C

Chaika•7/6/23, 2:46 PM

Thanks! Yea it seems to be in an even worse state now, it's not instantly erroring, but infinitely loading, queries against it, and trying to load it in the dashboard, just take so long I get a gateway timeout
Edit: Looks like I'm back to instant d1 errors, same error as above. Still broken sadly

MMatt Silverlock We’ll manage that behind the scenes so you can read your writes. When we ship re...

M

Mozzy•7/6/23, 2:49 PM

Okay thanks

CChaika Thanks! Yea it seems to be in an even worse state now, it's not instantly errori...

M

Matt SilverlockOP•7/6/23, 10:03 PM

Still broken?

A

Andrezzz•7/6/23, 10:09 PM

hey guys, can we manage the database using some client?

AAndrezzz hey guys, can we manage the database using some client?