K

kianOP•10/13/23, 1:05 AM

If you wanted to use parameters, then you'd do this.

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

K

kianOP•10/13/23, 1:05 AM

You can take a look at https://www.sqlite.org/lang_insert.html for the syntax of

INSERT

INSERT

INSERT

INSERT.

S

Santa DCON 🎄•10/13/23, 2:47 AM

Yay a bug! Not sure if posted, but if the primary key is set to null, the dashboard's update function fails to open:

S

Santa DCON 🎄•10/13/23, 2:47 AM

R

roftt•10/13/23, 5:51 AM

How do I check geolocation of my D1 database?

Rroftt How do I check geolocation of my D1 database?

M

Max (@rozenmd)•10/13/23, 6:56 AM

wrangler d1 info <DBNAME>

wrangler d1 info <DBNAME>

wrangler d1 info <DBNAME>

wrangler d1 info <DBNAME>

M

MrXO•10/13/23, 10:03 AM

is it expensive to use d1 for an ecommerce platform?

Mming I was wondering if I could do this: 1. Client/browser connect to my worker usin...

M

Max (@rozenmd)•10/13/23, 10:16 AM

you could probably do something like that now with a websocket in durable objects

M

Max (@rozenmd)•10/13/23, 10:16 AM

just manually setAlarm every N seconds

G

GuillaumeLakano•10/13/23, 12:30 PM

How can we create a local D1 database please? The --local flag doesn't seems to works, I've tried to add a binding in wrangler.toml but I'm forced to fill an UUID ( not yet created, and even if I force it to something random it's not works )

FFerry That works, but be aware there is a limit to the number of bound variables , whi...

K

kianOP•10/13/23, 12:43 PM

Just send more than one query then? 100 in one query seems more than acceptable.

K

kianOP•10/13/23, 12:44 PM

SQLite’s recommended high security value is 10, so D1 is already 10x’d that.

GGuillaumeLakano How can we create a local D1 database please? The --local flag doesn't seems to ...

W

w3bcode•10/13/23, 12:46 PM

I had the same issue, Local development with D1 is not working for me. So as a workaround I am using preview DB on remote

Ww3bcode I had the same issue, Local development with D1 is not working for me. So as a w...

G

GuillaumeLakano•10/13/23, 12:48 PM

Thank you for your feedback, few weeks ago I could create one in local, I don't remember how, may be with the --local ( or without the --remote flag, this is not really clear when we need one or not )

G

GuillaumeLakano•10/13/23, 12:49 PM

Currently, if I don't uses the --local flag, it's create the DB in remote ( and without any --remote flag )

E

Elliot Hesp•10/13/23, 2:14 PM

I've got a wranger.toml file like so

[env.production]
name = "krypton"
logpush = true
workers_dev = true
d1_databases = [
  { binding = "DB", database_name = "krypton", database_id = "<123>" },
]

[env.production]
name = "krypton"
logpush = true
workers_dev = true
d1_databases = [
  { binding = "DB", database_name = "krypton", database_id = "<123>" },
]

[env.production]
name = "krypton"
logpush = true
workers_dev = true
d1_databases = [
  { binding = "DB", database_name = "krypton", database_id = "<123>" },
]

[env.production]
name = "krypton"
logpush = true
workers_dev = true
d1_databases = [
  { binding = "DB", database_name = "krypton", database_id = "<123>" },
]

When I deploy, it deploys with the D1 database as expected bound. However when I run:

wrangler d1 migrations apply krypton

wrangler d1 migrations apply krypton

wrangler d1 migrations apply krypton

wrangler d1 migrations apply krypton

I get the error,

Can't find a DB with name/binding 'krypton' in local config. Check info in wrangler.toml...

Can't find a DB with name/binding 'krypton' in local config. Check info in wrangler.toml...

Can't find a DB with name/binding 'krypton' in local config. Check info in wrangler.toml...

Can't find a DB with name/binding 'krypton' in local config. Check info in wrangler.toml...

Migrations work for the non-prod env. No idea :/

K

kianOP•10/13/23, 2:16 PM

Try

wrangler d1 migrations apply krypton --env production

wrangler d1 migrations apply krypton --env production

wrangler d1 migrations apply krypton --env production

wrangler d1 migrations apply krypton --env production

E

Elliot Hesp•10/13/23, 2:17 PM

Thank you!

GGuillaumeLakano Thank you for your feedback, few weeks ago I could create one in local, I don't ...

M

Matt Silverlock•10/13/23, 2:26 PM

Can you please open a GitHub issue with a reproduction and/or where the docs fail you? https://developers.cloudflare.com/d1/learning/local-development/

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

T

Talaat•10/13/23, 2:45 PM

Hello
Can anyone guide me on how to connect workers with Cloudflare d1 and different environments
The connection is fine if i go with the default wrangler.toml configuration but the moment i go with this approach (Check code below) the db connection never happens at all

Approach i am trying to use

This is a staging environment

[env.staging]
d1_databases = [
{ binding = "DB", database_name = "<DATABASE_NAME_1>", database_id = "<UUID1>" },
]

This is a production environment

[env.production]
d1_databases = [
{ binding = "DB", database_name = "<DATABASE_NAME_2>", database_id = "<UUID2>" },
]

MY BINDING

export type Bindings = {
    DB: D1Database;
    ACCESS_TOKEN_SECRET: string;
};

export type Bindings = {
    DB: D1Database;
    ACCESS_TOKEN_SECRET: string;
};

export type Bindings = {
    DB: D1Database;
    ACCESS_TOKEN_SECRET: string;
};

export type Bindings = {
    DB: D1Database;
    ACCESS_TOKEN_SECRET: string;
};

my endpoint (Using hono js)

app.get('/', async (c) => {
    const res1 = await c.env.DB.prepare('SELECT * from testTable;').all();
    return c.json({ status: 'success', message: res1 });
});

app.get('/', async (c) => {
    const res1 = await c.env.DB.prepare('SELECT * from testTable;').all();
    return c.json({ status: 'success', message: res1 });
});

app.get('/', async (c) => {
    const res1 = await c.env.DB.prepare('SELECT * from testTable;').all();
    return c.json({ status: 'success', message: res1 });
});

app.get('/', async (c) => {
    const res1 = await c.env.DB.prepare('SELECT * from testTable;').all();
    return c.json({ status: 'success', message: res1 });
});

T

Talaat•10/13/23, 2:45 PM

i then get the following error
Trace: TypeError: Cannot read properties of undefined (reading 'prepare')
at file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:8029:31
at dispatch (file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:561:17)
at file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:562:33
at file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:1628:11
at dispatch (file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:561:17)
at file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:544:12
at file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:1009:21
at Hono2.dispatch (file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:1020:7)
at Hono.fetch (file:///C:/Users/User/AppData/Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:811:19) {
stack: TypeError: Cannot read properties of undefined (re…Local/Temp/tmp-3404-1q9ZFqgbsFEX/index.js:811:19),
message: Cannot read properties of undefined (reading 'prepare')

Would really appreciate your help

TTalaat Hello Can anyone guide me on how to connect workers with Cloudflare d1 and diffe...

C

Cyb3r-Jak3•10/13/23, 3:05 PM

Does

wrangler dev —env staging

wrangler dev —env staging

wrangler dev —env staging

wrangler dev —env staging work?

T

Talaat•10/13/23, 3:07 PM

unfortunately no

however if I change
[env.dev.vars]
to
[env.dev]
it does work but it only picks up the db connection and ignore other variables like the access token

C

Cyb3r-Jak3•10/13/23, 3:12 PM

Can you share your full wrangler file. My guess is this is toml inheritance being weird

TTalaat unfortunately no however if I change [env.dev.vars] to [env.dev] it does work b...

M

Max (@rozenmd)•10/13/23, 3:27 PM

deleted your message, it seems to contain secrets

T

Talaat•10/13/23, 3:27 PM

Ok this is crazy but i found the issue and it would be reall super nice if we could get a better solution to this
for D1 connection you need to use
[env.dev]

and for actual environment variables you need to use
[env.dev.vars]

Yes many would say that its on the documentation (both in workers and D1 docs) but its super difficult to ge this correct from the first time
i spent an entire day on this

AND PLZ dont get me wrong, cloudflare solutions are super amazing but if we could get a better way of doing this environment thing

MMax (@rozenmd)deleted your message, it seems to contain secrets

T

Talaat•10/13/23, 3:28 PM

yep
dummy not the actual secrets

BUT THANKS

F

forslunds•10/13/23, 5:34 PM

Feedback on onboarding is always great to get

LLane C @Max (@rozenmd) here is my query plan- do you see anything that would explain su...

M

Max (@rozenmd)•10/13/23, 6:46 PM

can you run that in both environments to verify you have indexes in both?

MMax (@rozenmd)can you run that in both environments to verify you have indexes in both?

L

Lane C•10/13/23, 11:06 PM

I did have one index to fix and that has increased performance a lot.My db was also the alpha version, so I recreated with beta, I have no idea how that happened. Its still about 1.2 seconds to load 10 records on a query with 4 joins and a base table of 10k rows. FWIW here is a front end demo. if you page thru enough times, you'll hit a page that hasn't been cached and can see the latency. https://client-demo.sonicjs.com/

Client Demo - SonicJs

Web site created using create-react-app

A

addison•10/14/23, 12:26 AM

is there any plans to show queries over the last 24 hours like planetscale has? would be good for debugging.

Kkian There's nothing special about how D1 handles it, so you can do whatever SQLite s...

S

Samantas5855•10/14/23, 12:26 AM

thank you!

Aaddison is there any plans to show queries over the last 24 hours like planetscale has? ...

C

Cyb3r-Jak3•10/14/23, 12:30 AM

What metrics are you looking for? There is https://developers.cloudflare.com/d1/platform/metrics-analytics/

Metrics and analytics · Cloudflare D1 docs

D1 exposes database analytics that allow you to inspect query volume, response size, and query latency across all and/or each database in your …

A

addison•10/14/23, 12:33 AM

looking for something similar to this maybe

S

Samantas5855•10/14/23, 12:49 AM

Our table has 5 columns, using INSERT INTO I was able to insert a row with 5 values using the query api. I did

"sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?);"

"sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?);"

"sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?);"

"sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?);"

Is it possible to insert multiple rows with one quest? Something like

sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"

sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"

sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"

sql": "INSERT INTO table (channel, title, start_time, end_time, description) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"

J

Jason Hostetter•10/14/23, 1:17 AM

Question regarding the proposed pricing: from what I’ve read, pricing is based on row writes/reads regardless of row size. This penalizes normalization then right? I have a schema that normalizes metadata for Dicom files, which is usually many small key value pairs that are very redundant. In other words, storing the denormalized metadata would waste a lot of storage, but normalizing means I have many small rows which then costs more. For example, a typical request might load metadata for 500 files, each file with 100 data items. So that’s at least 50,000 read units for one request right? Or I could stuff all the metadata for each file into one row, where maybe 60-80% of the data is identical. Considering there is a ceiling on storage with D1 I’m not totally sure where to draw the line here.

SSamantas5855 Our table has 5 columns, using INSERT INTO I was able to insert a row with 5 val...

W

windwp•10/14/23, 5:29 AM

you can use batch method. check d1 api docs

Aaddison is there any plans to show queries over the last 24 hours like planetscale has? ...

M

Max (@rozenmd)•10/14/23, 5:32 AM

yes, working on it

MMax (@rozenmd)yes, working on it

A

addison•10/14/23, 5:34 AM

yippie