You talk below about cases where people create 1db per user.. But to be more efficient, if I want to

You talk below about cases where people create 1db per user.. But to be more efficient, if I want to create 1 db per region (enam, wnam...) and redirect each user to the closest/fastest db, is there a way to programatically do that? with the

CF-IPCountry

CF-IPCountry

header ? but should I build a table with states on the east/west cost ... and redirect based on it? or is there a way to get a ping to the D1 center location?

rodrigoOP•11/30/23, 9:59 AM

And regarding Asia-Pacific region .. that is a region too big (Australia, China , India ...). Is there (or will there) a way to allocate D1 dbs to smaller areas within the region?

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

Rob M.•11/30/23, 10:18 AM

I'm not sure about cloudflare, but you can use any IP database for that like this one:
https://www.maxmind.com/en/geoip-databases

MaxMind GeoIP® Databases | MaxMind

IP geolocation and network data from the creator of GeoIP. Download and host
the data locally to eliminate network latency and per-query charges.

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

mrwind•11/30/23, 11:03 AM

request on worker will include request.cf.continent and request.cf.country . maybe you can use it and map to d1 region.

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

Max (@rozenmd)•11/30/23, 12:08 PM

the Workers platform is built in such a way that you should never need to make decisions like that - we know where your requests are coming from and should route them to the nearest replica for you, without your input (except for cases where you've opted into keeping data in certain regions)

Max (@rozenmd)•11/30/23, 12:09 PM

though I understand it's annoying to have slower requests atm while replication hasn't been shipped yet

MMax (@rozenmd)the Workers platform is built in such a way that you should never need to make d...

rodrigoOP•11/30/23, 2:10 PM

I was talking more about the case where you want to have several D1 dbs so that you have several writers. In this case, there is a way to redirect users to its closer db writer ? (example I want Australian users to use a master D1 db in Australia instead of having ig generating anywhere in the Asia-Pacific region)

Rrodrigo I was talking more about the case where you want to have several D1 dbs so that ...

Max (@rozenmd)•11/30/23, 2:20 PM

then yeah,

request.cf

request.cf

has the data you need: https://developers.cloudflare.com/workers/runtime-apis/request/#incomingrequestcfproperties

Max (@rozenmd)•11/30/23, 2:53 PM

...yet

AAkthar What is the limit on number of databases we can create? i can see it's 50k in do...

Matt Silverlock•11/30/23, 6:54 PM

Support for more than 100,000+ databases for Workers for Platforms users — there’s no limit, but if we said “unlimited” you might not believe us — on top of the 50,000 databases per account that D1 already enables.

From https://blog.cloudflare.com/d1-open-beta-is-here/

The Cloudflare Blog

D1: open beta is here

D1 is now in open beta, and the theme is “scale”: with higher per-database storage limits and the ability to create more databases, we’re unlocking the ability for developers to build production-scale applications on D1

GuillaumeLakano•11/30/23, 8:47 PM

Hi! When D1 return an error because of a constraint, we doesn't receive the 'meta' object with the rows_read / rows_written, could you confirm we're not billed in this kind of errors please?

GGuillaumeLakano Hi! When D1 return an error because of a constraint, we doesn't receive the 'met...

Matt Silverlock•11/30/23, 11:44 PM

It can depend - this isn’t final yet and there may be cases where we should count rows read. Note that no one is actively billed just yet.

di_uthred•12/1/23, 4:09 AM

Hi. I want to upgrade the schema. I understand that ALTER is not supported? How do I go about executing a SQL query to move the data into the new altered table? Do I need to use wrangler to do that, or is there a way to do it from the UI?

kian•12/1/23, 4:18 AM

SQLite supports renaming a table, adding a new column and renaming an existing column - so that subset of

ALTER TABLE

ALTER TABLE

is supported.

Vikash•12/1/23, 5:34 AM

It seems the JSON parse function doesn't work in query - I have

value

value

column to store some JSON data, but when I query with

json(value)

json(value)

it return the string instead the parsed JSON.

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

Result

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

I was expecting the JSON to be parsed using

json(value)

json(value)

function given in docs - https://developers.cloudflare.com/d1/learning/querying-json/#supported-functions

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

kian•12/1/23, 6:12 AM

The json(X) function verifies that its argument X is a valid JSON string and returns a minified version of that JSON string

kian•12/1/23, 6:12 AM

It doesn't turn it into a JS object, you need to do that yourself.

kian•12/1/23, 6:12 AM

It's a SQLite built-in function rather than something D1 specific.

Kkian It doesn't turn it into a JS object, you need to do that yourself.

Vikash•12/1/23, 6:58 AM

you mean the returned value can't be used in query to print in output?

ken•12/1/23, 12:55 PM

Hey

Does anyone know the limit of the number of statements for Batch Statements (

db.batch()

db.batch()

) on D1?

https://developers.cloudflare.com/d1/platform/client-api/#dbbatch|

I can see that the Maximum SQL statement length is

100,000 bytes (100 KB)

100,000 bytes (100 KB)

and it should apply. But is there any other limits as well?

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

Kken Hey 👋 Does anyone know the limit of the number of statements for Batch Statemen...

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

should be the same as a regular db.run/all, except the limits apply individually to the queries

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

all .batch does is save you the round trips of sending your sql+params for several queries

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

Hello, I’m Allie!•12/1/23, 1:10 PM

Isn’t that it though? If limits are applied to each query individually, how many queries can you make?

HHello, I’m Allie!Isn’t that it though? If limits are applied to each query individually, how many...

Max (@rozenmd)•12/1/23, 1:15 PM

I'm not sure we've tested it tbh, the answer is probably "however many queries it takes to crash the DO"

Max (@rozenmd)•12/1/23, 1:15 PM

each query is run sequentially though so it's probably a lot

MMax (@rozenmd)all .batch does is save you the round trips of sending your sql+params for sever...

ken•12/1/23, 1:44 PM

that makes sense, my query is throttling anyway so should be fine ,but wanted to see the upper limit. Thank you!

Original message was deleted

Max (@rozenmd)•12/1/23, 2:01 PM

that's an alpha db btw

Max (@rozenmd)•12/1/23, 2:30 PM

thats awesome

Village (Josef)•12/2/23, 4:49 PM

What's the fastest way to wipe a database and remigrate it all

Village (Josef)•12/2/23, 4:49 PM

I'd like to avoid having to manually delete 17 tables

senojsitruc•12/2/23, 4:53 PM

I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d42) yesterday between 8 - 9pm (ET). I created a support ticket (#3047705). The dashboard times out trying to list the database and says, "Failed to reach database. Please try again later." I had been very successfully testing it with a limited load and then a couple days ago I started trying additional load. And then it died. That could be coincidental, of course. I restored the data to a new database instance and got back up-and-running. If I've done something incorrect with D1, I'd like to know more details so that I can prevent this from happening again. Thanks! (D1 is great, btw.)

Village (Josef)•12/2/23, 5:21 PM

Just wipe and go

Village (Josef)•12/2/23, 5:21 PM

I ended up finding the --file for wrangler and just wrote a bunch of drop table statements

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

Fun Miles•12/2/23, 6:15 PM

Is one batch query a single API call? I am hitting a maximum API call rate limit for a single worker in a CRON worker. Would batching save me?
Is it possible to batch

SELECT

SELECT

statements?

Ssenojsitruc I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d4...

Matt Silverlock•12/2/23, 6:25 PM

Well take a look. There’s nothing you should be able to do that results in your database being “stuck” - we consider those cases bugs

Fun Miles•12/2/23, 6:42 PM

I can confirm I am able to have an array of 'SELECT' in there. But you cannot do

DB.prepare(statment).bind(arguments...).first()

DB.prepare(statment).bind(arguments...).first()

. So the result will be an array.

Fun Miles•12/2/23, 6:44 PM

But how about the number of API calls it is counted as from the point of view of not getting an error of Too many API calls from a single worker ?

Fun Miles•12/2/23, 6:45 PM

I know if will count as as many row retrieval as I have SELECT statement (1 result per SELECT), but that's not an issue.

Original message was deleted

Fun Miles•12/2/23, 6:46 PM

You were able to do 100k batched queries, so it sounds like such a batch counts as one API call and not 100k, otherwise you'd have hit the limit

Fun Miles•12/2/23, 7:03 PM

My re-factored query worker is now rewritten and it is not hitting the API limit anymore. But some of the queries exceed CPU time. This is a problem I've hit before. The docs say a query worker or a CRON with more than 1h between execution should get 15 minutes of CPU, but clearly it is not the case. I am being killed after 32,000ms. Is this a bug in the new billing?

Fun Miles•12/2/23, 7:08 PM

@Matt Any idea? I should be able to work around this for a while by reducing the queue batching size but that means I can hardly trust that it won't happen again.

Fun Miles•12/2/23, 9:23 PM

It doesn't look like D1 side limit to me. The message from tail said: Queue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PMQueue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PM

FFun Miles It doesn't look like D1 side limit to me. The message from tail said: `Queue sch...

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

This is related to #queues-beta - not D1.

MMatt Silverlock This is related to #queues-beta - not D1.

Fun Miles•12/2/23, 10:43 PM

Or CRON workers without Queues, but nobody has answered my questions there. Thought you might have better knowledge.

FFun Miles Or CRON workers without Queues, but nobody has answered my questions there. Thou...

Matt Silverlock•12/2/23, 10:49 PM

The documented limits are 15 minutes of wall clock time (total duration), not true CPU time, if you use Unbound, for Cron triggers & Queue consumers. Recommend keeping the discussion in the relevant channels.

Fun Miles•12/2/23, 10:50 PM

It does say of CPU time per invocation. Not wall clock time.

Fun Miles•12/2/23, 10:52 PM

What channel do I discuss Cron trigger issue?

FFun Miles What channel do I discuss Cron trigger issue?

Matt Silverlock•12/2/23, 11:16 PM

If this is specific to a Cron trigger, then #workers-help - best to keep your question in one place. None of this is related to D1.

Hearse•12/3/23, 5:05 AM

I have a couple questions:

can i query d1 from outside of pages/workers, for example (discord bot)
can i tell wrangler d1 for my local database to copy all entries from my production database

const sqlQuery = ctx.env.DB.prepare( ` SELECT key, json(value) as value, created_at, updated_at, expire_at FROM Example Where account_id = ? LIMIT ?, ?` ).bind(authUser.account_id, offset, limit); const { results } = await sqlQuery.all();

[ { "key": "rMDrWHc", "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}", "created_at": "2023-12-01 05:27:45", "updated_at": null, "expire_at": null } ]

You talk below about cases where people create 1db per user.. But to be more efficient, if I want to

Similar Threads