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

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

mattOP•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 Overdijk•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 Overdijk•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 Overdijk•5/9/24, 8:35 AM

M

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

als when showing the table details it indicates the

id

id

column is a PK

M

Marcel Overdijk•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 Overdijk•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 Overdijk•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?

C

cosbgn•5/10/24, 8:29 AM

Does anyone knows really what's the difference between bindings and the http api?

Can the http api handle production traffic just fine? I know bindings are recommended but what's the main difference, are queries just a bit slower? Like 100ms or seconds?

C

cosbgn•5/10/24, 8:31 AM

Oh nevermind 1200 req per 5min limit, so it's impossible in production. Ok got it.

Ccosbgn Oh nevermind 1200 req per 5min limit, so it's impossible in production. Ok got i...

D

DaniFoldi•5/10/24, 9:01 AM

Yup, first there's the rate limit, and Cloudflare REST API goes to a few colos, so the request will definitely be noticeably slower - the exact duration depends on your and your DB's location

N

Nehal Ali•5/10/24, 2:42 PM

Hello , i am trying to perform an alpha database migration for one of my alpha databases , i followed this guide: https://developers.cloudflare.com/d1/platform/alpha-migration/#1-verify-that-a-database-is-alpha
but i keep getting error

  Internal error: failed to run backup: Failed to query D1: D1 API returned: (500 Internal Server
  Error) {"error":"Error 9000: something went wrong","success":false} [code: 7500]

  Internal error: failed to run backup: Failed to query D1: D1 API returned: (500 Internal Server
  Error) {"error":"Error 9000: something went wrong","success":false} [code: 7500]

  Internal error: failed to run backup: Failed to query D1: D1 API returned: (500 Internal Server
  Error) {"error":"Error 9000: something went wrong","success":false} [code: 7500]

  Internal error: failed to run backup: Failed to query D1: D1 API returned: (500 Internal Server
  Error) {"error":"Error 9000: something went wrong","success":false} [code: 7500]

when i run wrangler d1 backup command , any idea how to resolve this ?

Cloudflare Docs

Alpha database migration guide · Cloudflare D1 docs

D1 alpha databases will stop accepting live SQL queries on July 1, 2024.

D

DavidHidvegi•5/10/24, 8:47 PM

Hi, do you know if in the D1 Limits Docs (https://developers.cloudflare.com/d1/platform/limits/) the following part means that it would be possible to increase the size of one database up to 10TB (from 10GB) ?

Increase your limits

Request adjustments to limits that conflict with your project goals by contacting Cloudflare. To request an increase to a limit, complete the Limit Increase Request Form
and we will contact you with next steps.

DDavidHidvegi Hi, do you know if in the D1 Limits Docs (https://developers.cloudflare.com/d1/p...

C

Chaika•5/10/24, 8:54 PM

that's not how they want you to scale, they want you to make more d1 dbs and spread out on them, not a single. the 10gb is a hard technical limit
d1-database

C

Chaika•5/10/24, 8:55 PM

and they've said before they don't currently increase that limit at all
d1-database
I kind of doubt we'll ever see singular D1 DBs bigger then 100 gb, let alone 10 tb lol

CChaika and they've said before they don't currently increase that limit at all https://...

D

DavidHidvegi•5/10/24, 8:57 PM

Ah thanks, that was my assumption

M

Murder Chicken•5/10/24, 9:40 PM

Is it possible yet to leverage a sqlite client with a remote D1 database yet? I know there's a JDBC connector being tested but anything that allows us to leverage a stand-alone GUI app to interact with a remote DB?

MMurder Chicken Is it possible yet to leverage a sqlite client with a remote D1 database yet? I...

H

Hello, I’m Allie!•5/10/24, 9:42 PM

Not that I know of(might be some 3rd-party package for it, idk). The issue is is that there isn't really a standard SQLite Protocol as far as I'm aware, so most clients just open a file. This is made even more difficult because D1 itself doesn't store the database as a file, but in a different way that makes it easier for them to handle on the backend

HHello, I’m Allie!Not that I know of(might be some 3rd-party package for it, idk). The issue is is...

M

Murder Chicken•5/10/24, 9:43 PM

Bummer. I was hoping something might have popped up in the community in the last few months. Thanks for the info!

MMurder Chicken Is it possible yet to leverage a sqlite client with a remote D1 database yet? I...

J

josh•5/11/24, 12:22 AM

Any D1 clients JDBC, ODBC, or otherwise would be built against our HTTP API. It should be pretty trivial to make use of the API yourself, but currently you should expect it to be slightly less reliable (auth for the HTTP API goes through our control plane whereas bindings from workers do not).

Jjosh Any D1 clients JDBC, ODBC, or otherwise would be built against our HTTP API. It ...

M

Murder Chicken•5/11/24, 12:55 PM

Thank you for that input Josh. I saw some VSCode extension (https://marketplace.visualstudio.com/items?itemName=yawarjamal.cf-d1) that purports to do just that but I'm hesitant to use it.

Cloudflare D1 - Visual Studio Marketplace

Extension for Visual Studio Code - Explore and query Cloudflare D1 databases

A

AngusMa•5/11/24, 3:37 PM

I’m sure I bound the database in

wrangler.toml

wrangler.toml

correctly, and a table named

record

record

is in the database.
Why can’t it found the table?

A

AngusMa•5/11/24, 3:43 PM

Yeah I forgot it.
It had been a long time since I edited the code last time.

C

Codename_A•5/11/24, 6:27 PM

npx wrangler d1 execute hadith-db --file=db.sql

it complains about my db size. The db size is about 58 mb and there are some long entries. I really want to use d1 as I am using cf for other stuff. Is there a way to do this?

wrangler-2024-05-11_18-16-55_223.log2.33KB

MMurder Chicken Bummer. I was hoping something might have popped up in the community in the las...

I

IEatBeans•5/11/24, 9:21 PM

I use a project called d1 manager on GitHub. It’s very basic but is good enough (better than the one on the cf dashboard)

MMurder Chicken Thank you for that input Josh. I saw some VSCode extension (https://marketplace...

I

IEatBeans•5/11/24, 9:21 PM

That looks cool, did you ever try it?

A

Alec | Kriebel LLC.•5/12/24, 5:32 PM

On May 5th, some of my D1 queries spuriously started failing with:

D1_ERROR: Internal error while starting up D1 storage caused object to be reset.`

D1_ERROR: Internal error while starting up D1 storage caused object to be reset.`

D1_ERROR: Internal error while starting up D1 storage caused object to be reset.`

D1_ERROR: Internal error while starting up D1 storage caused object to be reset.`

I don't see any other mentions of this and there were no incidents declared during this time. Anyone know what happened here?

IIEatBeans That looks cool, did you ever try it?

M

Murder Chicken•5/12/24, 11:08 PM

I did not. It didn't have any kind of feedback that I could see and so I didn't dive into it.

M

Martial•5/13/24, 12:47 AM

I am not sure to understand the purpose of the D1 database, the 10GB limit seems rather low for a serverless database. How I am supposed to go beyond that limit?

MMartial I am not sure to understand the purpose of the D1 database, the 10GB limit seems...

I

IEatBeans•5/13/24, 3:10 AM

You are supposed to use many databases. I think you can have like thousands bound to a worker

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

Similar Threads

Similar Threads

Similar Threads