Hi, I'm planning to use cloudflare workers for my next project. Currently researching `d1`. 1. Do I

Rrobert.zaremba Hi, I'm planning to use cloudflare workers for my next project. Currently resear...

V

Victor•1/22/24, 3:31 PM

That is the limit in beta, but it will go up. The base included in workers paid is 5gb, then charged every gig after. Up in this thread d1-database Matt mentioned about a (currently TBA, but planned for an eventual) >100GB limit

Rrobert.zaremba Hi, I'm planning to use cloudflare workers for my next project. Currently resear...

M

Max (@rozenmd)•1/22/24, 3:44 PM

For larger databases, Hyperdrive is probably going to be a better fit: https://developers.cloudflare.com/hyperdrive/

J

josh•1/22/24, 8:21 PM

Can someone explain the reasoning behind why a

wrangler.toml

wrangler.toml

file is required for local development of a D1 database? I'm trying to run some migrations, but it's not working unless I create the file. I don't quite get why it's needed when the only thing required for local development is the binding.

[[d1_databases]]
binding = "DB"
database_id = "<can_be_any_string>"
database_name = "<can_be_any_string>"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB"
database_id = "<can_be_any_string>"
database_name = "<can_be_any_string>"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB"
database_id = "<can_be_any_string>"
database_name = "<can_be_any_string>"
preview_database_id = "DB"

[[d1_databases]]
binding = "DB"
database_id = "<can_be_any_string>"
database_name = "<can_be_any_string>"
preview_database_id = "DB"

The above is exactly what I have in the file now. I'm trying to run the following:

npx wrangler d1 migrations apply DB --local

npx wrangler d1 migrations apply DB --local

npx wrangler d1 migrations apply DB --local

npx wrangler d1 migrations apply DB --local

K

kian•1/22/24, 9:49 PM

When you say dev environment, do you mean

wrangler dev

wrangler dev

wrangler dev

wrangler dev?

C

Cyb3r-Jak3•1/22/24, 9:55 PM

Does

wrangler tail

wrangler tail

wrangler tail

wrangler tail show anything?

P

Ping for toast•1/23/24, 1:51 PM

I am curious about the implementation details of D1. I am seriously considering a model where

Each user gets their own SQLite database
Each user, if they so wish, can arbitrarily query the database
Giving each user their own sqlite database has many advantages
Trivial to monitor storage usage of users
Trivial to delete all user's data
Users can hopefully have data autonomy in the sense they can just download all their data
But I am curious about the security concerns of this. I haven't found anything saying to what degree sqlite is protected against sql injection escaping the database. Obviously, the D1 team must be very familiar with this problem so I just thought I would ask, I understand if you aren't at liberty to discuss the implementation details of D1

R

robert.zarembaOP•1/23/24, 1:59 PM

Is there any sqlite extension enabled by D1 to do geospatial queries? I need to be able to search events "near my location".

D

Denver James Duran•1/23/24, 3:15 PM

Quick question guys, does cloudflare D1 doesn't support math functions? like floor()
https://www.sqlite.org/lang_mathfunc.html#floor

PPing for toast I am curious about the implementation details of D1. I am seriously considering ...

M

Max (@rozenmd)•1/23/24, 4:02 PM

D1 doesn't know ahead of time which of your users should be able to query which databases - that's up to your application code to decide

MMax (@rozenmd)D1 doesn't know ahead of time which of your users should be able to query which ...

P

Ping for toast•1/23/24, 4:02 PM

I'm more asking if D1's backend has protections (and if so, what) against SQLite vulnerabilities allowing the SQL environment to be escaped, or if it's simply assumed SQLite is secure

PPing for toast I'm more asking if D1's backend has protections (and if so, what) against SQLite...

M

Max (@rozenmd)•1/23/24, 4:04 PM

we definitely haven't assumed sqlite is secure

: https://github.com/cloudflare/workerd/blob/main/src/workerd/util/sqlite.c%2B%2B#L713

MMax (@rozenmd)we definitely haven't assumed sqlite is secure 😅 : https://github.com/cloudflar...

P

Ping for toast•1/23/24, 4:05 PM

Oh, this is incredible, the same thing you are running in production I assume?

M

Max (@rozenmd)•1/23/24, 4:05 PM

workerd can lag by a day or so, but yes

MMax (@rozenmd)workerd can lag by a day or so, but yes

P

Ping for toast•1/23/24, 4:05 PM

Thank you for open sourcing this, it's unbelievably helpful

PPing for toast Thank you for open sourcing this, it's unbelievably helpful

K

kenton•1/23/24, 8:09 PM

FWIW, SQLite is designed to be secure, as it is used extensively in many places where such security is needed. For instance, Google Chrome has long given arbitrary web sites the ability to store data in sqlite databases via the WebSQL API (although this API has been deprecated due to inability to achieve standardization). The library is extensively fuzzed. There have been some security vulnerabilities over the years, but far fewer than, say, V8. You should of course carefully read https://www.sqlite.org/security.html .

MMax (@rozenmd)workerd can lag by a day or so, but yes

K

kenton•1/23/24, 8:10 PM

Changes land in workerd first; it's production that lags behind.

Kkenton FWIW, SQLite is designed to be secure, as it is used extensively in many places ...

P

Ping for toast•1/23/24, 8:11 PM

did google employ any sort of isolation techniques non the less that you are aware of?

PPing for toast did google employ any sort of isolation techniques non the less that you are awa...

K

kenton•1/23/24, 8:14 PM

I'm not super familiar with the details but I would assume they ran sqlite inside the renderer process, which is heavily sandboxed

K

kenton•1/23/24, 8:14 PM

that is a second layer of protection, intended as defense in depth against vulnerabilities

P

Ping for toast•1/23/24, 8:16 PM

because the worst fear is that, should there be some undiscovered issue, and a user gets arbitrary command access, they could read other people's databases which is terrifying. So somehow I need to figure out how to give each database it's own sandbox, and that's frankly where I run out of knowledge. It would be cool to learn, but if I mess up here that's a HUGE issue. Maybe this isn't even worth attempting. It would be incredibly cool, but maybe it isn't worth attempting

P

Ping for toast•1/23/24, 8:18 PM

I guess the context is that I'm making a personal analytics product. Kinda sorta grafana esq or if you've seen datasette like that. So it feels natural to give each person their own database and visual tools to play with it, but also ways to directly query it, but if you have a vulnerability well thats a lot of sensitive data

PPing for toast because the worst fear is that, should there be some undiscovered issue, and a u...

K

kenton•1/23/24, 8:19 PM

building sandboxes is a bit of a niche skill. You could use something off-the-shelf like firecracker or gvisor. For this purpose, you might also be OK using basic containers (if configured correctly), or writing something custom based on Linux namespaces and seccomp (not actually that hard once you figure it out).

P

Ping for toast•1/23/24, 8:21 PM

I'll look at the things you listed. Containers for each user feels very expensive, but the other ones are potentially excellent

K

kenton•1/23/24, 8:22 PM

Honestly though, the rate of critical vulnerabilities in SQLite is like once every few years. If you're on the ball about patching you might not need per-user layer 2 sandboxing.

P

Ping for toast•1/23/24, 8:22 PM

I'm also considering just using D1 and giving each user their own database to scale until I can hopefully afford to pay someone to do it for me

P

Ping for toast•1/23/24, 8:22 PM

I feel like I recall reading somewhere this is a supported use case

PPing for toast I feel like I recall reading somewhere this is a supported use case

M

Max (@rozenmd)•1/23/24, 8:24 PM

it is, just the UX needs a bit of work

MMax (@rozenmd)it is, just the UX needs a bit of work

P

Ping for toast•1/23/24, 8:26 PM

yeah I was thinking that the prospect of having thousands of these is scary

PPing for toast yeah I was thinking that the prospect of having thousands of these is scary

M

Max (@rozenmd)•1/23/24, 8:26 PM

the eventual idea is to have a DO-style binding where you don't need to call the API to patch your worker's bindings for new dbs

P

Ping for toast•1/23/24, 8:27 PM

from a visual standpoint, ideally I imagine having like "groups" and then a group contains databases

P

Ping for toast•1/23/24, 8:27 PM

and the group is presented more like a conventional sql interface (tight rows)

P

Ping for toast•1/23/24, 8:28 PM

oh... like KV is!

P

Ping for toast•1/23/24, 8:31 PM

and then like this

env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?")

might become

env.USERS("123").prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS("123").prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS("123").prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS("123").prepare("SELECT * FROM Customers WHERE CompanyName = ?")

or

env.USERS["123"].prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS["123"].prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS["123"].prepare("SELECT * FROM Customers WHERE CompanyName = ?")

env.USERS["123"].prepare("SELECT * FROM Customers WHERE CompanyName = ?")

(because while I'm not familiar with sandboxing, I am very familiar with abusing proxy objects to do terrible things in typescript)

PPing for toast oh... like KV is!

M

Max (@rozenmd)•1/23/24, 8:32 PM

oh yeah the D1 UI dates back to when we thought 10 dbs per account was a lot, we'll be refreshing it

P

Ping for toast•1/23/24, 8:34 PM

I dunno I just think that one more level of nesting is appropriate here. Like on my personal account I have tons of projects, and I wouldn't want one project crowding the interface that I use for other projects with hundreds of databases. Maybe the suggestion here is make a... is it a zone? organization? account?

Rrobert.zaremba Is there any sqlite extension enabled by D1 to do geospatial queries? I need to ...

R

robert.zarembaOP•1/23/24, 8:34 PM

Up

Rrobert.zaremba Up 👆

C

Cyb3r-Jak3•1/23/24, 8:54 PM

What extensions are you looking for? The list of allowed is at https://github.com/cloudflare/workerd/blob/132c62a3916c2ad5413b2a3efd9a9b7a0242532c/src/workerd/util/sqlite.c%2B%2B#L126

GitHub

workerd/src/workerd/util/sqlite.c++ at 132c62a3916c2ad5413b2a3efd9a...

The JavaScript / Wasm runtime that powers Cloudflare Workers - cloudflare/workerd

P

Ping for toast•1/23/24, 8:57 PM

probably SpatiaLite but if D1 supports R*Tree you'd get by fine

W

Will•1/24/24, 4:46 AM

does D1 support locking?

Original message was deleted

U

Unsmart•1/24/24, 4:55 AM

SQLite is fine for a lot of users though you may need read replicas and sharding as with literally any database at some scale.

D1 doesn’t have the built in methods for this yet but will come before production ready.

If you want something production ready right now durable objects is the best option to stay on cloudflare.

W

WcaleNieWolny•1/24/24, 9:20 AM

I rely heavily on the d1 http api in my postgres app. I am trying to use d1 as an edge cache to postgres so that cloudflare workers can connect directly to d1 instead of postgres reducing latency. In order to do that I added an insert trigger in postgres to call the

d1 http api

d1 http api

d1 http api

d1 http api. Here is the issue that I am facing
D1 api sometimes fails randomly with no clear reason. I have implemented a queue system to collect all of the failed calls and here are some error msgs I get

```
{"content":"{\"success\":false,\"errors\":[{\"code\":10000,\"message\":\"Authentication error\"}]}\n","error_msg":null,"status_code":500}
```
{"content":"{\"success\":false,\"errors\":[{\"code\":10000,\"message\":\"Authentication error\"}]}\n","error_msg":null,"status_code":500}
```
{"content":"{\"success\":false,\"errors\":[{\"code\":10000,\"message\":\"Authentication error\"}]}\n","error_msg":null,"status_code":500}
```
{"content":"{\"success\":false,\"errors\":[{\"code\":10000,\"message\":\"Authentication error\"}]}\n","error_msg":null,"status_code":500}
(Strange, as I do not change authentication and only some calls fail with this error

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (503 Service Unavailable) [truncate html, see screenshot]

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (503 Service Unavailable) [truncate html, see screenshot]

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (503 Service Unavailable) [truncate html, see screenshot]

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (503 Service Unavailable) [truncate html, see screenshot]

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7502,\n      \"message\": \"Internal error: db error: FATAL: server conn crashed?\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7502,\n      \"message\": \"Internal error: db error: FATAL: server conn crashed?\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7502,\n      \"message\": \"Internal error: db error: FATAL: server conn crashed?\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7502,\n      \"message\": \"Internal error: db error: FATAL: server conn crashed?\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": [],\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Cannot resolve D1 due to transient issue on remote node.\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": [],\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Cannot resolve D1 due to transient issue on remote node.\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": [],\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Cannot resolve D1 due to transient issue on remote node.\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": [],\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Cannot resolve D1 due to transient issue on remote node.\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Internal error: Flipper failed: Flipper API returned: error sending request for url (https://flipper.sd.cfplat.com/features/edgeworker?acct=8640544): error trying to connect: dns error: failed to lookup address information: Name or service not known\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Internal error: Flipper failed: Flipper API returned: error sending request for url (https://flipper.sd.cfplat.com/features/edgeworker?acct=8640544): error trying to connect: dns error: failed to lookup address information: Name or service not known\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Internal error: Flipper failed: Flipper API returned: error sending request for url (https://flipper.sd.cfplat.com/features/edgeworker?acct=8640544): error trying to connect: dns error: failed to lookup address information: Name or service not known\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7500,\n      \"message\": \"Internal error: Flipper failed: Flipper API returned: error sending request for url (https://flipper.sd.cfplat.com/features/edgeworker?acct=8640544): error trying to connect: dns error: failed to lookup address information: Name or service not known\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

W

WcaleNieWolny•1/24/24, 9:21 AM

`{"content":null,"error_msg":"Timeout was reached","status_code":null}` - this is my app timing out the HTTP request. I am not sure how long the timeout is, but it's likely around 5+ seconds.
- ```
{"content":"upstream connect error or disconnect/reset before headers. retried and the latest reset reason: connection termination","error_msg":null,"status_code":503}
```
  {"content":"upstream connect error or disconnect/reset before headers. retried and the latest reset reason: connection termination","error_msg":null,"status_code":503}
```
{"content":"upstream connect error or disconnect/reset before headers. retried and the latest reset reason: connection termination","error_msg":null,"status_code":503}
```
  {"content":"upstream connect error or disconnect/reset before headers. retried and the latest reset reason: connection termination","error_msg":null,"status_code":503}
- ```
{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (409 Conflict) error code: 1018\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}
```
  {"content":"{\n \"result\": null,\n \"success\": false,\n \"errors\": [\n {\n \"code\": 7501,\n \"message\": \"Internal error: D1 API returned: (409 Conflict) error code: 1018\"\n }\n ],\n \"messages\": []\n}","error_msg":null,"status_code":500}
```
{"content":"{\n  \"result\": null,\n  \"success\": false,\n  \"errors\": [\n    {\n      \"code\": 7501,\n      \"message\": \"Internal error: D1 API returned: (409 Conflict) error code: 1018\"\n    }\n  ],\n  \"messages\": []\n}","error_msg":null,"status_code":500}
```
  {"content":"{\n \"result\": null,\n \"success\": false,\n \"errors\": [\n {\n \"code\": 7501,\n \"message\": \"Internal error: D1 API returned: (409 Conflict) error code: 1018\"\n }\n ],\n \"messages\": []\n}","error_msg":null,"status_code":500}
- ```
{"content":"<html>\r\n<head><title>502 Bad Gateway</title></head>\r\n<body>\r\n<center><h1>502 Bad Gateway</h1></center>\r\n<hr><center>cloudflare</center>\r\n</body>\r\n</html>\r\n","error_msg":null,"status_code":502}
```
  {"content":"<html>\r\n<head><title>502 Bad Gateway</title></head>\r\n<body>\r\n<center><h1>502 Bad Gateway</h1></center>\r\n<hr><center>cloudflare</center>\r\n</body>\r\n</html>\r\n","error_msg":null,"status_code":502}
```
{"content":"<html>\r\n<head><title>502 Bad Gateway</title></head>\r\n<body>\r\n<center><h1>502 Bad Gateway</h1></center>\r\n<hr><center>cloudflare</center>\r\n</body>\r\n</html>\r\n","error_msg":null,"status_code":502}
```
  {"content":"<html>\r\n<head><title>502 Bad Gateway</title></head>\r\n<body>\r\n<center><h1>502 Bad Gateway</h1></center>\r\n<hr><center>cloudflare</center>\r\n</body>\r\n</html>\r\n","error_msg":null,"status_code":502}
Given that there were A LOT of requests into D1 (around 150K since 18th january) and that I only ever got random 200 errors like that I do not want to say that d1 is bad. However aside from retyring the failed requests is there is anything else I could try? I can easily retry d1 failed requests but I really do not want to resort to that.

WWcaleNieWolny I rely heavily on the [d1 http api](https://developers.cloudflare.com/api/operat...

K

kenton•1/24/24, 3:19 PM

The HTTP API at api.cloudflare.com is intended to be used for configuration and debugging, not live serving. It won't have anywhere near the availability and performance of the D1 API called from a Worker. If you want HTTP access to your D1 database that can be used as part of a production stack, I would recommend writing a Worker that implements an HTTP API backed by a D1 binding, rather than using api.cloudflare.com.

Kkenton The HTTP API at api.cloudflare.com is intended to be used for configuration and ...

M

Max (@rozenmd)•1/24/24, 3:25 PM

for example: https://github.com/elithrar/http-api-d1-example

Hi, I'm planning to use cloudflare workers for my next project. Currently researching `d1`. 1. Do I

Similar Threads

Similar Threads

Similar Threads