Another thing I'm wondering now is the foolowing: I'm implementing an api with a traditional paginat

UUnsmart It is in the doc on the pricing page: > 1. Rows read measure how many rows a que...

M

I mean explicitly stating that a count query will also perform these reads (so count resulting in value 1000, will mean 1000 rows read)

M

Marcel OverdijkOP•5/8/24, 10:21 PM

Above does not seem effective but will cost 10 reads less... I'm not concerned about those 10 more reads btw.
But maybe executing 1 query instead of 2 might even perform better....

M

Marcel OverdijkOP•5/8/24, 10:23 PM

The tables I'm quering this way will not contain more than 1000 rows on average.

MMarcel Overdijk Another thing I'm wondering now is the foolowing: I'm implementing an api with a...

I

Isaac McFadyen•5/8/24, 10:29 PM

I'd strongly recommend against the "query all the rows" approach.

I

Isaac McFadyen•5/8/24, 10:30 PM

Workers have memory limits so if you filter in the Worker you'll still run into issues where if the rows are very large and a script is being executed by 2 people then you might hit those limits

I

Isaac McFadyen•5/8/24, 10:30 PM

Plus you'll be billed more for processing time in the Worker itself (which is likely more than the extra 10 reads from D1)

M

Marcel OverdijkOP•5/8/24, 10:49 PM

Yes I’m also against it.

M

Marcel OverdijkOP•5/8/24, 10:51 PM

But I might instead cache the result of the count query, as it is quite static data.

M

Marcel OverdijkOP•5/8/24, 10:56 PM

Yes that could be an option.
But I also have other filter arguments besides offset limit…
Like country, category etc. In the api 0, 1 or more of the filters can be applied.

M

Marcel OverdijkOP•5/8/24, 11:02 PM

No 0, 1 or more column based filters + limit / offset.
Where normally the limit is 10 so 10 rows are returned. But also the total count based on the filters is needed. (To show navigation buttons)

M

Marcel OverdijkOP•5/8/24, 11:03 PM

So result is 10 rows (based on limit param) but count could be 300, 400 or like 1000

W

wiln•5/8/24, 11:26 PM

Hello, I am new to D1, I want to ask if it is possible to read and write to the database using the python code on my computers? It makes me feel like you can only do that using javascript and workers

I

Isaac McFadyen•5/9/24, 3:30 AM

TIL, that's cool

U

Unsmart•5/9/24, 3:48 AM

neat, never made a table without an index personally so would always work well

M

matt•5/9/24, 7:43 AM

im a little confused by some of the language in the d1 material I've been reading -- are read replicas available now?

Mmatt im a little confused by some of the language in the d1 material I've been readin...

H

Hello, I’m Allie!•5/9/24, 7:44 AM

They are not

M

matt•5/9/24, 7:46 AM

ah ok thanks for the update. the blog post from april makes it sounds like they are already running. did CF provide any timelines for when they might be available? really like the look of the session API and can't wait to try it out

Mmatt ah ok thanks for the update. the blog post from april makes it sounds like they ...

H

Hello, I’m Allie!•5/9/24, 7:46 AM

:soontm:

H

Hello, I’m Allie!•5/9/24, 7:46 AM

Best answer we can give you atm

M

matt•5/9/24, 7:46 AM

no ones perfect

Ggeelen if you're doing `select count(1) from my_table` then as long as `my_table` has a...

M

Marcel OverdijkOP•5/9/24, 8:26 AM

Thanks for joining the discussion @geelen , however the behavior you mention is not what I see:

I have a table with a primary key on the

id

id

field and that table also has various other index like on

name

name

.
But this is the results I get:

With select *

{
  "result": [
    {
      "results": [
        {
          "count(*)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2931,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(*)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2931,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(*)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2931,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(*)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2931,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

With select 1:

{
  "result": [
    {
      "results": [
        {
          "count(1)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2308,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(1)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2308,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(1)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2308,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(1)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.2308,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

M

Marcel OverdijkOP•5/9/24, 8:31 AM

With select id (on primary key):

{
  "result": [
    {
      "results": [
        {
          "count(id)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4641,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(id)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4641,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(id)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4641,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(id)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4641,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

On other indexed field:

{
  "result": [
    {
      "results": [
        {
          "count(name)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4976,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(name)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4976,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(name)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4976,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(name)": 900
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.4976,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 900,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

But as you can see the

rows_read

rows_read

is always similar to the count result.

M

Marcel OverdijkOP•5/9/24, 8:35 AM

M

Marcel OverdijkOP•5/9/24, 8:35 AM

als when showing the table details it indicates the

id

id

column is a PK

M

Marcel OverdijkOP•5/9/24, 8:40 AM

Also

PRAGMA index_list('driver');

PRAGMA index_list('driver');

PRAGMA index_list('driver');

PRAGMA index_list('driver'); gives me all the indexes (inclduing the unique PK index):

M

Marcel OverdijkOP•5/9/24, 8:43 AM

I also tried:

select count(country_of_birth_country_id) from driver where country_of_birth_country_id = 'united-kingdom'

select count(country_of_birth_country_id) from driver where country_of_birth_country_id = 'united-kingdom'

select count(country_of_birth_country_id) from driver where country_of_birth_country_id = 'united-kingdom'

select count(country_of_birth_country_id) from driver where country_of_birth_country_id = 'united-kingdom'
(

country_of_birth_country_id

country_of_birth_country_id

country_of_birth_country_id

country_of_birth_country_id is an indexed column and I use in the

count

count

and in the

where

where

) but it gives:

{
  "result": [
    {
      "results": [
        {
          "count(country_of_birth_country_id)": 174
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.7666,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 175,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(country_of_birth_country_id)": 174
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.7666,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 175,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(country_of_birth_country_id)": 174
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.7666,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 175,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

{
  "result": [
    {
      "results": [
        {
          "count(country_of_birth_country_id)": 174
        }
      ],
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.7666,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 12607488,
        "rows_read": 175,
        "rows_written": 0
      }
    }
  ],
  "success": true,
  "errors": [],
  "messages": []
}

Result from the count was

174

174 but still

175

175 rows read.

M

Marcel OverdijkOP•5/9/24, 8:45 AM

I assume 174 + 1 as it accesses the index? But anyway, I cannot seem to get count queries to have only 1 read...

R

roftt•5/9/24, 2:26 PM

Trying to bind 3000 databases to my worker in wrangler.toml but getting "504 Gateway Time-out" error. It works for ~1800 but for 3000 it's hitting the time limit? What can I do?

K

kian•5/9/24, 2:26 PM

Bind less databases is the only real answer

K

kian•5/9/24, 2:26 PM

It'll take longer with each binding until it times out

W

Walshy•5/9/24, 2:27 PM

maybe wfp would be better suited here?

W

Walshy•5/9/24, 2:27 PM

If you're making a db per customer you can make a worker per customer and have the binding per one

N

Natalie•5/9/24, 7:39 PM

Curious as to whether the Rest API is suitable for use in workers?

K

kian•5/9/24, 7:40 PM

I wouldn’t recommend it for most use-cases, it’ll be slower than bindings and has rate limits on its usage.

K

kian•5/9/24, 7:41 PM

It’s fine for an admin API, or one-off queries.

C

Chaika•5/9/24, 7:42 PM

?d1-remote-connections

F

Flare•5/9/24, 7:42 PM

Accessing D1 from outside workers:

D1 is based on SQLite, which does not natively support remote connections and isn't MySQL/PostgreSQL wire compatible, nor does D1 itself offer that functionality. As such, your options are:

1 -> The Cloudflare API Query Endpoint (https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database), with the upside of needing less setup, all requests have to go back to Cloudflare's core, so request latency is far from optimal from some locations. You would also be subject to the API Rate limit of 1200 requests per 5 minutes.

2 -> Create a worker to proxy requests to your D1 Database. Either tightly bound to your application/schema or loose and just accepting any query. This would allow your worker to execute potentially closer to your D1 Database and avoid the api rate limit.

C

Chaika•5/9/24, 7:43 PM

well that's about outside workers but basically covers the same, bindings better, faster, and no rate limiting

N

Natalie•5/9/24, 7:48 PM

Thanks!

E

Envy•5/9/24, 9:03 PM

I'm trying to Time Travel restore a database to a restore point 1-2 days ago and getting an error.

"There was a problem restoring your database, please try again later."

EDIT it did restore successfully but gave the error at the end instead of a success message

I

IEatBeans•5/9/24, 9:41 PM

Sentry just reported this error: “Error: D1_ERROR: Network connection lost.”. What would cause this? Can’t reproduce and not sure what caused it.

K

krofdrakula•5/10/24, 6:39 AM

i'm trying to read data from D1 queries over the HTTP from outside a worker, and am wondering if D1 also supports

FORMAT Tabseparated

FORMAT Tabseparated

FORMAT Tabseparated

FORMAT Tabseparated or similar like Analytics Engine responses do - is there a way to stream results instead of having to parse the entire JSON into memory and then iterate over the results?

Another thing I'm wondering now is the foolowing: I'm implementing an api with a traditional paginat

Similar Threads

Similar Threads

Similar Threads