how do I connect to my d1 db using a desktop db client? I'd like to see the rows in my production db

TTitan how do I connect to my d1 db using a desktop db client? I'd like to see the rows...

J

johtso•4/10/24, 5:14 PM

also interested in knowing if this is possible. I ended up using this, which requires deploying to CF Pages https://github.com/JacobLinCool/d1-manager

T

TitanOP•4/10/24, 5:19 PM

Thanks, not sure my security team would sign off on a db client being accessible on the web though!

0

0xAndrew•4/10/24, 6:09 PM

I'm unable to get D1 working due to invalid

id

id

in

wrangler.toml

wrangler.toml

.

wrangler.toml

wrangler.toml

name = "something"
main = "src/index.ts"
compatibility_date = "2022-11-07"
node_compat = true

[[ d1_databases ]]
binding = "DB"
database_name = "something"
database_id = "abscsdf23"

name = "something"
main = "src/index.ts"
compatibility_date = "2022-11-07"
node_compat = true

[[ d1_databases ]]
binding = "DB"
database_name = "something"
database_id = "abscsdf23"

name = "something"
main = "src/index.ts"
compatibility_date = "2022-11-07"
node_compat = true

[[ d1_databases ]]
binding = "DB"
database_name = "something"
database_id = "abscsdf23"

name = "something"
main = "src/index.ts"
compatibility_date = "2022-11-07"
node_compat = true

[[ d1_databases ]]
binding = "DB"
database_name = "something"
database_id = "abscsdf23"

But I keep getting an error

notes: [
      {
        text: 'binding __D1_BETA__DB of type d1 must have a valid `id` specified [code: 10021]'
      }
    ],

notes: [
      {
        text: 'binding __D1_BETA__DB of type d1 must have a valid `id` specified [code: 10021]'
      }
    ],

notes: [
      {
        text: 'binding __D1_BETA__DB of type d1 must have a valid `id` specified [code: 10021]'
      }
    ],

notes: [
      {
        text: 'binding __D1_BETA__DB of type d1 must have a valid `id` specified [code: 10021]'
      }
    ],

Anyone know what it should look like? There's guidance in the docs on what the

database_name

database_name

database_name

database_name should be but not the

database_id

database_id

. Would be great to update the docs so that there was a better explanation there (https://developers.cloudflare.com/d1/get-started/#4-bind-your-worker-to-your-d1-database)

Cloudflare Docs

Get started · Cloudflare D1 docs

This guide will instruct you through:

00xAndrew I'm unable to get D1 working due to invalid `id` in `wrangler.toml`. `wrangler....

V

Vy•4/10/24, 6:14 PM

database_id

database_id

is auto-generated when you created the database. You can find it on the dash UI too

VVy `database_id` is auto-generated when you created the database. You can find it o...

0

0xAndrew•4/10/24, 6:18 PM

It hasn't been deployed. I've just run

wrangler d1 execute db --local --file=./drizzle/0000_short_lockheed.sql

wrangler d1 execute db --local --file=./drizzle/0000_short_lockheed.sql

wrangler d1 execute db --local --file=./drizzle/0000_short_lockheed.sql

wrangler d1 execute db --local --file=./drizzle/0000_short_lockheed.sql

and then

wrangler dev

wrangler dev

And then i receive this error

00xAndrew It hasn't been deployed. I've just run `wrangler d1 execute db --local --file=...

M

Max (@rozenmd)•4/10/24, 6:45 PM

whats your wrangler version

M

MyZeD•4/10/24, 6:56 PM

I created the table "articles" and everytime i want to insert something, it displays: "no such table: main.price"
WTF?

Sstevefrench is there an easy way to reset (erase everything) a database (all tables) without...

M

MyZeD•4/10/24, 7:02 PM

try https://stackoverflow.com/questions/525512/drop-all-tables-command

Stack Overflow

Drop all tables command

What is the command to drop all tables in SQLite?

Similarly I'd like to drop all indexes.

MMax (@rozenmd)whats your wrangler version

0

0xAndrew•4/10/24, 7:07 PM

2.21.1. Solved by upgrading to latest 3.x.x

MMyZeD I created the table "articles" and everytime i want to insert something, it disp...

M

Max (@rozenmd)•4/10/24, 7:10 PM

does escaping the double quotes help?

MMax (@rozenmd)does escaping the double quotes help?

M

MyZeD•4/10/24, 7:11 PM

No, it's the same

MMyZeD try https://stackoverflow.com/questions/525512/drop-all-tables-command

M

Max (@rozenmd)•4/10/24, 7:12 PM

you'd get billed for writes doing this btw

MMyZeD I created the table "articles" and everytime i want to insert something, it disp...

M

Max (@rozenmd)•4/10/24, 7:14 PM

not sure if it'll help, but sqlite doesn't have varchar nor decimal, its TEXT, and REAL

MMax (@rozenmd)not sure if it'll help, but sqlite doesn't have varchar nor decimal, its TEXT, a...

M

MyZeD•4/10/24, 7:16 PM

Changed all VARCHAR, JSON and DATETIME to TEXT and the DECIMALs to REAL. No change, same: "no such table: main.price"

MMax (@rozenmd)not sure if it'll help, but sqlite doesn't have varchar nor decimal, its TEXT, a...

M

MyZeD•4/10/24, 7:17 PM

Maybe your last developer forget to remove his static test-database

M

Max (@rozenmd)•4/10/24, 7:18 PM

D1 is completely isolated - it's an issue with either the schema, the query, or the API parsing the query

M

Max (@rozenmd)•4/10/24, 7:19 PM

what does your table list look like?

M

MyZeD•4/10/24, 7:21 PM

got it....

MMax (@rozenmd)what does your table list look like?

M

MyZeD•4/10/24, 7:21 PM

was the foreign key: "FOREIGN KEY (price) REFERENCES price (price)" sorry

M

MyZeD•4/10/24, 7:22 PM

I'm sorry for that

M

Max (@rozenmd)•4/10/24, 7:23 PM

all good

U

unk•4/11/24, 5:11 AM

Hi.
Why do I get this error?

Error: D1_ERROR: too many SQL variables at offset 484

Error: D1_ERROR: too many SQL variables at offset 484

Error: D1_ERROR: too many SQL variables at offset 484

Error: D1_ERROR: too many SQL variables at offset 484

I use drizzle.

const data = await db.query.projects.findMany({
    limit: PAGE_LIMIT,
    where: inArray(
    girls.id,
    girlsIds.map(({ id }) => id),
    ),
    offset: calcOffset(Number(qs.page), PAGE_LIMIT),
})

const data = await db.query.projects.findMany({
    limit: PAGE_LIMIT,
    where: inArray(
    girls.id,
    girlsIds.map(({ id }) => id),
    ),
    offset: calcOffset(Number(qs.page), PAGE_LIMIT),
})

const data = await db.query.projects.findMany({
    limit: PAGE_LIMIT,
    where: inArray(
    girls.id,
    girlsIds.map(({ id }) => id),
    ),
    offset: calcOffset(Number(qs.page), PAGE_LIMIT),
})

const data = await db.query.projects.findMany({
    limit: PAGE_LIMIT,
    where: inArray(
    girls.id,
    girlsIds.map(({ id }) => id),
    ),
    offset: calcOffset(Number(qs.page), PAGE_LIMIT),
})

M

Max (@rozenmd)•4/11/24, 9:06 AM

(I am probably going to be posting this every few days until we shut down the old alpha DBs)

On the off-chance you're still using an alpha D1 database, we've made a guide to make it easier to move across to the newer, faster (40x more requests per second) D1 DBs:

https://developers.cloudflare.com/d1/platform/alpha-migration

Original message was deleted

J

johtso•4/11/24, 9:25 AM

No, you're all good if you use batch, not 100% what the limits are for a single batch operation, but I think they're really high.. and not based on params

G

Gyurmatag•4/11/24, 9:44 AM

Hi guys! If I want to move from D1 to another Cloud provider, then what are my options? Thank you very much for the answers!

GGyurmatag Hi guys! If I want to move from D1 to another Cloud provider, then what are my o...

B

Beny•4/11/24, 9:49 AM

Export your data [1]:

wrangler d1 export <DATABASE_NAME> --output db.sql --remote

wrangler d1 export <DATABASE_NAME> --output db.sql --remote

wrangler d1 export <DATABASE_NAME> --output db.sql --remote

wrangler d1 export <DATABASE_NAME> --output db.sql --remote

Then upload it to another cloud & either use their API or Hyperdrive to connect your worker to the db [2]
[1] https://developers.cloudflare.com/workers/wrangler/commands/#export
[2] https://developers.cloudflare.com/hyperdrive/

CCole Xemi Maximum bound parameters per query is 100. https://developers.cloudflare.com/d1/...

U

unk•4/11/24, 9:57 AM

Suppose you wrote a query like this using offset

const data = await db.query.projects.findMany({
  where: inArray(
    projects.key,
    [... //over 100 items]
  ),
  offset: 50,
})

const data = await db.query.projects.findMany({
  where: inArray(
    projects.key,
    [... //over 100 items]
  ),
  offset: 50,
})

const data = await db.query.projects.findMany({
  where: inArray(
    projects.key,
    [... //over 100 items]
  ),
  offset: 50,
})

const data = await db.query.projects.findMany({
  where: inArray(
    projects.key,
    [... //over 100 items]
  ),
  offset: 50,
})

but But this exceeds the limit.
Is it not feasible to combine the search and OFFSET for more than 100 arrays?

G

Gunnar•4/11/24, 10:28 AM

Hi, sorry if this has been asked before, but how are you guys gonna work with this limit, if your service explodes in popularity?
Maximum database size 10 GB (Workers Paid)
https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

GGunnar Hi, sorry if this has been asked before, but how are you guys gonna work with th...

B

bun•4/11/24, 10:38 AM

10gb is plenty enough for a lot of users

B

bun•4/11/24, 10:38 AM

They recommend using multiple databases in any way

B

bun•4/11/24, 10:39 AM

Like theres this possibility of having a db per customer even

GGunnar Hi, sorry if this has been asked before, but how are you guys gonna work with th...

M

Max (@rozenmd)•4/11/24, 10:44 AM

(my canned reply to this is):

D1 is built to work to the strengths of Cloudflare's network, so we can't offer extremely large databases while also loading them quickly. It's significantly easier for us to offer millions of DBs with a size limit instead.

D1 will require a change in how you think about your project's data if you're looking to build a big platform. Instead of storing all of your data in a single DB, you would store each customer's data in their own DB (and we're building tooling to make it easier to dynamically access multiple DBs in a single Worker without redeploying)

MMax (@rozenmd)(my canned reply to this is): D1 is built to work to the strengths of Cloudflar...

H

Hello, I’m Allie!•4/11/24, 10:48 AM

One thing I've been wondering, if the main issue to having large databases is startup speed, what effect does a large database have on execution speed? As in, if you could somehow assume that the DB is always hot, would the upper limit for storage be much higher?

MMax (@rozenmd)(my canned reply to this is): D1 is built to work to the strengths of Cloudflar...

F

forslunds•4/11/24, 10:51 AM

in a single worker.. not a single pages function?

Fforslunds in a single worker.. not a single pages function? 🥲

H

Hello, I’m Allie!•4/11/24, 10:52 AM

All of the Functions in a Pages Project are bundled into a Worker, so it should apply to Functions too

HHello, I’m Allie!All of the Functions in a Pages Project are bundled into a Worker, so it should ...

F

forslunds•4/11/24, 10:55 AM

if i got a cent for every time i heard that..

HHello, I’m Allie!All of the Functions in a Pages Project are bundled into a Worker, so it should ...

M

Max (@rozenmd)•4/11/24, 10:59 AM

this, but I always worked around Pages functions not having all the features by using a separate Worker for my API

Workers and Pages becoming the same "thing" will make this better

GGunnar Hi, sorry if this has been asked before, but how are you guys gonna work with th...

B

Beny•4/11/24, 11:03 AM

If your service gets to that size then you shouldn't be using sqlite (which is what D1 is) anyway.
I have one app with 8000+ users and over 11 million records in the db and it is under 2gb, so I dont think it's an issue unless you are storing some big data in there

MMax (@rozenmd)(my canned reply to this is): D1 is built to work to the strengths of Cloudflar...

G

Gunnar•4/11/24, 11:04 AM

Is there a max db count limit?

GGunnar Is there a max db count limit?

B

Beny•4/11/24, 11:05 AM

It's 50,000 but they've mentioned they've just set that so people don't go crazy. It can be increased to anything.
https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

BBeny It's 50,000 but they've mentioned they've just set that so people don't go crazy...

G

Gunnar•4/11/24, 11:07 AM

Oh jesus, sorry, I'm blind, it's the top line

GGunnar Is there a max db count limit?

M

Max (@rozenmd)•4/11/24, 11:08 AM

when we release dynamic bindings, it'll look a bit more like DO's limits page

B

Beny•4/11/24, 11:09 AM

Next we need an unlimited KV's limit

That one is pretty small

BBeny Next we need an unlimited KV's limit 😄 That one is pretty small

G

Gunnar•4/11/24, 11:11 AM

There is a "Limit Increase Request Form" for KV
https://developers.cloudflare.com/kv/platform/limits/

Cloudflare Docs

Limits · Cloudflare Workers KV

Request adjustments to limits that conflict with your project goals by contacting Cloudflare. To request an increase to a limit, complete the Limit …

BBeny Next we need an unlimited KV's limit 😄 That one is pretty small

H

Hello, I’m Allie!•4/11/24, 11:12 AM

You can also just create new “namespaces” by giving each a prefix

HHello, I’m Allie!You can also just create new “namespaces” by giving each a prefix

B

Beny•4/11/24, 11:14 AM

Yeah this is a thing, but that workaround wouldn't be good in combination with Workers for Platforms, otherwise the user workers will be able to see other's data

GGunnar There is a "Limit Increase Request Form" for KV https://developers.cloudflare.co...

B

Beny•4/11/24, 11:18 AM

I've never tried the limit increase before but I've seen some people here mention their requests went unanswered for weeks so it puts me off a bit (and it is strange hosting it on a Google form, doesn't feel official)

how do I connect to my d1 db using a desktop db client? I'd like to see the rows in my production db

Similar Threads

Similar Threads

Similar Threads