Hello Cloudflare community 👋 . I need help accessing tables in databases. I have created a database

Hello Cloudflare community

. I need help accessing tables in databases. I have created a database and binded it to my worker through workers.toml file. But when I make a search in a particular table, it gives "no such table" error. Here is an image of tables in my database, "Customers" is just for test purposes.

YasinOP•10/15/23, 2:35 PM

app.get('/posts/:lang/:slug/comments', async c => {

    const { results } = await c.env.comDB.prepare(

app.get('/posts/:lang/:slug/comments', async c => {

    const { results } = await c.env.comDB.prepare(

SELECT name, sql FROM sqlite_master

);
        return c.text(results, 200);

}

);
        return c.text(results, 200);

}

Here I am just trying to list all tables

YasinOP•10/15/23, 2:36 PM

here is the response, no tables

YasinOP•10/15/23, 2:40 PM

I have tried deleting and recreating the database and the tables many times. It doesn't work

YYasin Hello Cloudflare community 👋 . I need help accessing tables in databases. I hav...

Cyb3r-Jak3•10/15/23, 2:52 PM

Wrangler dev uses local mode by default so it doesn’t use the values in the dashboard. You can add values by running the d1 execute command with the —local—local flag

Ian•10/15/23, 3:24 PM

For database-per-user usecase, is there no dynamic binding to avoid having to modify workers binding every time a new database is added ?

IIan For database-per-user usecase, is there no dynamic binding to avoid having to mo...

Cyb3r-Jak3•10/15/23, 3:43 PM

Correct. That is something they want to do but isn’t possible currently.

CCyb3r-Jak3 Correct. That is something they want to do but isn’t possible currently.

Ian•10/15/23, 3:43 PM

perhaps having somehting like "database-group"

Ian•10/15/23, 3:44 PM

well, i'll keep waiting for updates i guess

CCyb3r-Jak3 Wrangler dev uses local mode by default so it doesn’t use the values in the dash...

YasinOP•10/15/23, 4:00 PM

Nothing has changed unfortunately

YYasin Nothing has changed unfortunately

Cyb3r-Jak3•10/15/23, 4:03 PM

Actually looking at your code you never execute the query. You need to add .all().all() at the end of your prepare in order to use the statement

Cyb3r-Jak3•10/15/23, 4:04 PM

Docs: https://developers.cloudflare.com/d1/platform/client-api/#await-stmtall

CCyb3r-Jak3 Actually looking at your code you never execute the query. You need to add `.all...

YasinOP•10/15/23, 4:06 PM

It's working now. The weird thing is, it used to work without --local keyword when I first created it. Thanks for the help

Paul•10/16/23, 2:29 AM

anyone else with boolean issues?
Error: D1_TYPE_ERROR: Type 'boolean' not supported for value 'true'Error: D1_TYPE_ERROR: Type 'boolean' not supported for value 'true'

PPaul anyone else with boolean issues? `Error: D1_TYPE_ERROR: Type 'boolean' not suppo...

Cyb3r-Jak3•10/16/23, 2:31 AM

What does your schema look like? SQLite doesn't have a concept of boolean and uses an integer instead

Paul•10/16/23, 2:32 AM

oh lol, well it is somehow a boolean

Paul•10/16/23, 2:32 AM

email_verified BOOLEAN DEFAULT 0,email_verified BOOLEAN DEFAULT 0,

Paul•10/16/23, 2:32 AM

how is this possible

Cyb3r-Jak3•10/16/23, 2:33 AM

Ah instead of using

true

true

use

for your SQL query

Paul•10/16/23, 2:33 AM

ooof, kinda difficult, i'm many layers above where that happens.

Paul•10/16/23, 2:33 AM

https://tenor.com/view/shummer-death_dive-dive-fail-failed-dive-dive-gif-12569456

Tenor

Cyb3r-Jak3•10/16/23, 2:34 AM

Are you using an ORM or something?

Paul•10/16/23, 2:34 AM

using Lucia adapter

Paul•10/16/23, 2:34 AM

so i don't really have access to that

Cyb3r-Jak3•10/16/23, 2:35 AM

Might be worth asking in their discord if this is a known thing

riskiidrus•10/16/23, 3:08 AM

Does anyone know why D1 feels slow for simple queries or commands?

I live in Indonesia, I access D1 from there. This is what the query or command looks like:

SELECT * FROM users WHERE id = ? LIMIT 1

.....

UPDATE user SET ..... WHERE id = ?

Even the User Registration Process took a few seconds for me to complete. I thought it was just a problem of my slow internet connection, but when I tried to deploy it on Pages, it made no difference.

Isn't it on the Page "https://pages.cloudflare.com" that Cloudflare claims that they run the Pages Project on the Cloudflare Edge Network which is up to 115% faster than competing platforms?

I'm afraid this will be a Bad End User Experience.

Unsmart•10/16/23, 3:17 AM

D1 is single region so the database exists in 1 location and might not be super close to you causing latency.

For me D1 queries are pretty fast ~100ms

TigersWay•10/16/23, 4:54 AM

Cloud someone confirm the create DB D1 API (https://developers.cloudflare.com/api/operations/cloudflare-d1-create-database#Request) doesn't allow to set a location hint as wrangler or the dashboard do?

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

kian•10/16/23, 7:44 AM

d1-database

GuillaumeLakano•10/16/23, 9:38 AM

Hi! There is a similar D1 endpoint ( https://http-api-d1.<your-worker-subdomain>.workers.dev/query/all/ ) for local dev ( offline ) ? My goal is to create a database from a worker in wrangler dev

GGuillaumeLakano Hi! There is a similar D1 endpoint ( https://http-api-d1.<your-worker-subdomain>...

Hello, I’m Allie!•10/16/23, 1:26 PM

Couldn’t you just work the one on GitHub? It should work the same whether deployed or running locally

HHello, I’m Allie!Couldn’t you just work the one on GitHub? It should work the same whether deploy...

GuillaumeLakano•10/16/23, 1:38 PM

I haven't tested because this require an URL to works. Which URL could I put in the configuration to make it works offline in wrangler dev --local mode ? May be there is a specific URL for make it works in local ?

GGuillaumeLakano I haven't tested because this require an URL to works. Which URL could I put in ...

Hello, I’m Allie!•10/16/23, 2:12 PM

Localhost + the port the Worker is running on. Or are you talking about something else?

HHello, I’m Allie!Localhost + the port the Worker is running on. Or are you talking about somethin...

GuillaumeLakano•10/16/23, 2:24 PM

I've just take a look inside the code, and I was confused, It's not what I've thought.

I need something like this Cloudflare API to create a D1 database ( and query it ) but in local mode ( offline )
https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

I suppose this is not yet possible until the feature of dynamic bindings

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

GGuillaumeLakano I've just take a look inside the code, and I was confused, It's not what I've th...

Hello, I’m Allie!•10/16/23, 2:36 PM

Would this work? https://github.com/elithrar/http-api-d1-example

GitHub

GitHub - elithrar/http-api-d1-example: An example HTTP API for Clou...

An example HTTP API for Cloudflare's D1 database: https://developers.cloudflare.com/d1/

GitHub - elithrar/http-api-d1-example: An example HTTP API for Cloudflare's D1 database: ht...

Hello, I’m Allie!•10/16/23, 2:37 PM

It’s not specifically made for local, but I don’t see why it shouldn’t work

HHello, I’m Allie!It’s not specifically made for local, but I don’t see why it shouldn’t work

GuillaumeLakano•10/16/23, 3:20 PM

This project isn't what I needed, it's a proxy to uses a remote D1 database, and could be used with as a REST API to be hosted in local, but the database is still on CF servers (online).

In fact, I was confused with the Cloudflare API, that permit to create a D1 database and to query it through REST API too, but it's create the D1 database in CF servers too (need to be online too).

I suppose there is not an equivalent Cloudflare API to call in REST on localhost, that could permit us to create a new database (offline) inside our .wrangler/d1/ folder

GGuillaumeLakano This project isn't what I needed, it's a proxy to uses a remote D1 database, and...

Cyb3r-Jak3•10/16/23, 3:23 PM

You should be able to use miniflare for this. See https://github.com/cloudflare/miniflare/releases/tag/v3.20230918.0

GitHub

Release v3.20230918.0 · cloudflare/miniflare

What's Changed

Implement simulators as Durable Objects by @mrbbot in #656.
Allow GET requests with bodies in entry worker by @mrbbot in #677
Remove unused handleQueue() function from entry wor...

CCyb3r-Jak3 You should be able to use miniflare for this. See https://github.com/cloudflare/...

GuillaumeLakano•10/16/23, 3:27 PM

Thank you, indeed there is example to access any kind of databases, ( « const d1Database = await mf.getD1Database("DATABASE");» ), I'm not sure this also permit to create one, but I'll check, thank you for the link!

GGuillaumeLakano This project isn't what I needed, it's a proxy to uses a remote D1 database, and...

mrwind•10/17/23, 1:20 AM

my project offer a self-hosted version so i create a local sqlite it doesn't depend on miniflare or cloudflare and use better-sqlite3 to run query

WWill Hackett Okay, that's all good. I would ADORE a best guess timeline if you have one. ❤️

forslunds•10/17/23, 10:14 AM

The latest dev blog about D1 says the limit will be upped coming weeks and months. (note, Several times) Once they have fast read on big sizes they would love to be on double-digit GB size. And they are aiming on GA Q1 2024. So i would guess.. "soon"

Elliot Hesp•10/17/23, 10:35 AM

Would it be expected that KV is quicker than D1 (indexed) reads?

I've got a performance sensitive worker, and I'm not sure whether adding a KV check before D1 query makes much sense or not

zegevlier•10/17/23, 10:52 AM

KV is extremely fast (faster than D1 without query caching) if the read is hot. If it's a cold read, it would probably depend on the location of you D1 database relative to the person requesting.

niconiahi•10/17/23, 1:59 PM

hey! is it possible to change the

migrations

migrations

directory?

I don't want my migrations directory to be at the top of my repository, but instead in a folder

db

db

, the complete location being ./db/migrations./db/migrations

in the config toml I have this configuration but it doesn't read it

migrations_dir = "db/migrations"

migrations_dir = "db/migrations"

thanks in advance

James•10/17/23, 6:20 PM

The pricing did change recently, yes, during birthday week

https://blog.cloudflare.com/d1-open-beta-is-here/#row-based-pricing

Unsmart•10/17/23, 6:40 PM

2 different pricing models is super confusing hence why they are removing the bundled/unbound models and making 1 standard pricing.

And SQL in general almost never follows denormalization as its pretty much built to normalize and support joins. But im not sure how the new billing model encourages normalization when it costs more to get 2 records via a join than it does to get 1?

Unsmart•10/17/23, 6:42 PM

Oh ok well I dont see why thats a problem? Denormalization makes it faster computationally and storage is cheaper than compute

Unsmart•10/17/23, 6:43 PM

SQL may be designed with joins in mind but denormalization is faster because of the fact it doesnt need to join.

Original message was deleted

Michał•10/17/23, 7:25 PM

Can you share any sources to support that?

Michał•10/17/23, 7:30 PM

I see, so you can't. That's what I thought, thanks for confirming!

Hello Cloudflare community 👋 . I need help accessing tables in databases. I have created a database

Similar Threads

Similar Threads

Similar Threads