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)OP•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)OP•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

JJason Hostetter Question regarding the proposed pricing: from what I’ve read, pricing is based o...

P

Paolo•10/14/23, 7:52 AM

Denormalized rows can often be more efficient in real life. I'm experimenting with JSON fields for this

Wwindwp you can use batch method. check d1 api docs

S

Samantas5855•10/14/23, 11:01 AM

batch method on the http rest api?

S

Samantas5855•10/14/23, 11:38 AM

@Matt I found your repository https://github.com/elithrar/http-api-d1-example#endpoints, does the /query/batch/ endpoint still exist?

SSamantas5855 @Matt I found your repository https://github.com/elithrar/http-api-d1-example#en...

M

Matt Silverlock•10/14/23, 12:31 PM

Yes: https://github.com/elithrar/http-api-d1-example/blob/d5c1eaaac12ef1d3e1d68cc01e02bd0a583aa5fd/src/index.ts#L137

GitHub

http-api-d1-example/src/index.ts at d5c1eaaac12ef1d3e1d68cc01e02bd0...

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

elithrar/http-api-d1-example

WWill Hackett Is there an update on the current hard limits for D1 while in beta? Would our En...

M

Matt Silverlock•10/14/23, 12:32 PM

Which specific limits? Per database (2GB) will increase for everyone during the beta, but can’t be increased by exception at this point in time.

MMatt Silverlock Yes: https://github.com/elithrar/http-api-d1-example/blob/d5c1eaaac12ef1d3e1d68c...

S

Samantas5855•10/14/23, 12:34 PM

oh thanks, is this endpoint documented?

S

Samantas5855•10/14/23, 12:36 PM

also would it be possible to do

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

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

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

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

instead of doing

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

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

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

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

SSamantas5855 oh thanks, is this endpoint documented?

M

Matt Silverlock•10/14/23, 12:38 PM

It’s in the README: https://github.com/elithrar/http-api-d1-example/tree/main#endpoints (note that it is not an official Cloudflare project: it’s just an 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...

MMatt Silverlock It’s in the README: https://github.com/elithrar/http-api-d1-example/tree/main#en...

S

Samantas5855•10/14/23, 12:38 PM

I mean is it documented on official cloudflare docs?

S

Samantas5855•10/14/23, 12:38 PM

if not, why?

SSamantas5855 I mean is it documented on official cloudflare docs?

M

Matt Silverlock•10/14/23, 12:41 PM

All API methods are here: https://developers.cloudflare.com/d1/platform/client-api/#batch-statements

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

SSamantas5855 I mean is it documented on official cloudflare docs?

M

Matt Silverlock•10/14/23, 12:42 PM

No methods are undocumented. If the docs aren’t helpful, open a PR on the docs repository so we can improve them

C

Cyb3r-Jak3•10/14/23, 12:43 PM

Seems like there is confusion on Worker API and REST API methods/endpoints

C

Cyb3r-Jak3•10/14/23, 12:47 PM

@Samantas5855 The

/query/batch

/query/batch

endpoint exists for the worker because it calls the

.batch

.batch

method through the Workers API. All of the endpoints in the worker are just ways to access the Workers API bindings

CCyb3r-Jak3 @Samantas5855 The `/query/batch` endpoint exists for the worker because it calls...

S

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

So if a method on the worker api existed named db.foobar() then the endpoint /query/foobar would also exist?

SSamantas5855 also would it be possible to do ```sql INSERT INTO table (channel, title, start_...

S

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

also I was able to do that

SSamantas5855 So if a method on the worker api existed named db.foobar() then the endpoint /qu...

C

Cyb3r-Jak3•10/14/23, 12:51 PM

If the worker was written that way then yes. The endpoints in the worker can be set to whatever as it just translations from a REST to the Workers binding

S

Samantas5855•10/14/23, 12:51 PM

by calling the query api like that

  --data '{
    "params": [
        "a",
        "b",
        "c",
        "d",
        "e",
        "a2",
        "b2",
        "c2",
        "d2",
        "e2"
    ],
    "sql": "INSERT INTO myTable (f1, f2, f3, f4, f5) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"
}'

  --data '{
    "params": [
        "a",
        "b",
        "c",
        "d",
        "e",
        "a2",
        "b2",
        "c2",
        "d2",
        "e2"
    ],
    "sql": "INSERT INTO myTable (f1, f2, f3, f4, f5) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"
}'

  --data '{
    "params": [
        "a",
        "b",
        "c",
        "d",
        "e",
        "a2",
        "b2",
        "c2",
        "d2",
        "e2"
    ],
    "sql": "INSERT INTO myTable (f1, f2, f3, f4, f5) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"
}'

  --data '{
    "params": [
        "a",
        "b",
        "c",
        "d",
        "e",
        "a2",
        "b2",
        "c2",
        "d2",
        "e2"
    ],
    "sql": "INSERT INTO myTable (f1, f2, f3, f4, f5) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?);"
}'

MMatt Silverlock Which specific limits? Per database (2GB) will increase for everyone during the ...

P

Paul•10/14/23, 1:03 PM

Can you share with us what the new limit will be when it’s increased?

S

Samantas5855•10/14/23, 2:36 PM

https://developers.cloudflare.com/d1/platform/limits/

Maximum arguments per SQL function    32

Maximum arguments per SQL function    32

Why only 32? Is this limit going to get bigger?

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public beta. Join the #d1-beta channel in the Cloudflare Developer Discord or subscribe to D1’s public …

SSamantas5855 https://developers.cloudflare.com/d1/platform/limits/ ``` Maximum arguments per ...

I

Isaac McFadyen•10/14/23, 2:58 PM

SQLite actually recommends a maximum of 8 for high security deployments (i.e Cloudflare D1) so I expect 32 is the maximum they will go, since that's fairly generous already.

SSamantas5855 https://developers.cloudflare.com/d1/platform/limits/ ``` Maximum arguments per ...

M

Michał•10/14/23, 3:01 PM

In what case do you need more than that?

MMatt Silverlock Yes: https://github.com/elithrar/http-api-d1-example/blob/d5c1eaaac12ef1d3e1d68c...

S

Samantas5855•10/14/23, 3:05 PM

I tried making a request to /query/batch and I get page not found

U

Up•10/14/23, 3:46 PM

anyone know what I'm doing wrong when trying to dump my DB?

const dump = await c.env.DB.dump();
    return new Response(dump, {
        status: 200,
        headers: {
            'Content-Type': 'application/octet-stream',
            'Content-Disposition': 'attachment; filename="sqlite.db"',
        }
    });

const dump = await c.env.DB.dump();
    return new Response(dump, {
        status: 200,
        headers: {
            'Content-Type': 'application/octet-stream',
            'Content-Disposition': 'attachment; filename="sqlite.db"',
        }
    });

const dump = await c.env.DB.dump();
    return new Response(dump, {
        status: 200,
        headers: {
            'Content-Type': 'application/octet-stream',
            'Content-Disposition': 'attachment; filename="sqlite.db"',
        }
    });

const dump = await c.env.DB.dump();
    return new Response(dump, {
        status: 200,
        headers: {
            'Content-Type': 'application/octet-stream',
            'Content-Disposition': 'attachment; filename="sqlite.db"',
        }
    });

running locally, getting a

D1_DUMP_ERROR: Status + 404

D1_DUMP_ERROR: Status + 404

when I call that endpoint

U

Up•10/14/23, 4:24 PM

CCyb3r-Jak3 @Samantas5855 The `/query/batch` endpoint exists for the worker because it calls...

S

Samantas5855•10/14/23, 5:27 PM

regarding that, it doesn't seem to exist anymore

S

Samantas5855•10/14/23, 5:28 PM

unless I'm doing something wrong?

MMatt Silverlock Note that if you're using DBeaver: I don't think it supports D1. Local SQLite !=...

P

Paul•10/14/23, 7:52 PM

are there any GUI clients we can use to view the local .wrangler/ D1?

SSamantas5855 I tried making a request to /query/batch and I get page not found

M

Matt Silverlock•10/14/23, 8:09 PM

I think you’re mixing up a standalone example with D1’s official API. I strongly recommend slowing down a tad and reading the docs:

SSamantas5855 I tried making a request to /query/batch and I get page not found

M

Matt Silverlock•10/14/23, 8:09 PM

The example you are referring to has nothing to do with D1’s official REST API.

M

Matt Silverlock•10/14/23, 8:11 PM

I invite folks to open GitHub issues so we can understand where/why they are running into limits -> allows us to increase them where it makes sense to. It’s not “fixed” at 32, but if you’re generating 1000+ then something is likely wrong with the query. This is why we ask folks to open issues (or use the form linked in the docs)

MMatt Silverlock The example you are referring to has nothing to do with D1’s official REST API.

S

Samantas5855•10/14/23, 10:52 PM

is there any example of performning batch commands with the official d1 rest api?

S

Samantas5855•10/14/23, 10:53 PM

it's the last piece that's missing to complete the implementation

C

Cyb3r-Jak3•10/14/23, 11:17 PM

You won't get the same transaction benefits as using a worker but something like

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');`

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');`

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');`

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');`

as a statement should work. Do also keep in mind that the API has a rate limit of 1200 requests per 5 minutes which is another reason deploying your own worker as an REST endpoint is recommended

deleted your message, it seems to contain secrets

Similar Threads

Similar Threads

Similar Threads