we have folks building products that have 1 db per customer, where each db has the same schema, just

we have folks building products that have 1 db per customer, where each db has the same schema, just stores different data
though migrations + dynamically binding to new dbs are a pain point

MMax (@rozenmd)we have folks building products that have 1 db per customer, where each db has t...

Hello, I’m Allie!•11/29/23, 9:33 AM

Where's my SQLite DOs?

janat08•11/29/23, 9:34 AM

I'm not interested in 1db per customer thing, as I'd like them to be able to interact with each other

janat08•11/29/23, 9:34 AM

is there some kind of workaround, or limitations improvement to make d1 fit for general usage?

Jjanat08 is there some kind of workaround, or limitations improvement to make d1 fit for ...

Max (@rozenmd)OP•11/29/23, 9:40 AM

It doesn't sound like D1 fits your use case if you're worried about petabyte scale. Hyperdrive is probably a better fit: https://developers.cloudflare.com/hyperdrive/

janat08•11/29/23, 10:38 AM

I have no reason to worry about petabyte scyle, but surely anyone is hitting the 2gb limit

kian•11/29/23, 11:04 AM

This is a beta-only limitation. The D1 team will increase the maximum storage per-database, storage per-account and number of databases for Paid plans during the course of D1’s public beta.

kian•11/29/23, 11:05 AM

They're beta limitations - there is no forecasted hard limit, but there will naturally be one even in GA.

janat08•11/29/23, 12:13 PM

612345 wtyu wq\vcb

mushang98•11/29/23, 12:30 PM

I'm sorry for asking. I'm a newbie around cloudflare workers and pages. I've been trying to use next.js with D1 with no luck. Is there any template or example code and configs for this ?

kian•11/29/23, 12:31 PM

https://github.com/Rei-x/next-on-cloudflare-example & https://github.com/james-elicx/cloudy are both NextJS and use D1

Mmushang98 I'm sorry for asking. I'm a newbie around cloudflare workers and pages. I've bee...

Matt Silverlock•11/29/23, 12:31 PM

And https://developers.cloudflare.com/pages/framework-guides/deploy-a-nextjs-site/ shows how to use KV bindings (which is the same pattern D1 uses)

Deploy a Next.js site · Cloudflare Pages docs

Next.js is an open-source React framework for creating websites and applications. In this guide, you will create a new Next.js application and deploy …

Kkian <https://github.com/Rei-x/next-on-cloudflare-example> & <https://github.com/jame...

mushang98•11/29/23, 12:34 PM

Thank you, I'll look into it

MMatt Silverlock And https://developers.cloudflare.com/pages/framework-guides/deploy-a-nextjs-sit...

mushang98•11/29/23, 12:34 PM

I've tried this, but got error with the "env" variable. Any suggestion ?

kian•11/29/23, 12:36 PM

I'd recommend asking in #next-on-pages if you continue to have issues with

env

env

or bindings in general - once you have

env

env

working then D1 will just work as you'd expect normally.

Kkian I'd recommend asking in #next-on-pages if you continue to have issues with `env`...

mushang98•11/29/23, 12:38 PM

Okay, but I'll try something with the example first. thanks for the help

talfiz•11/29/23, 4:11 PM

hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every time I insert a binary into this table the whole database collapse I cant interact with the database anymore someone know why?

Ttalfiz hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every tim...

Matt Silverlock•11/29/23, 4:14 PM

What errors are you seeing?

talfiz•11/29/23, 4:17 PM

after successfuly inserting into the table (I can see the values in the UI) if I try to edit / insert from the ui or with code i get the error FOREIGN KEY constraint failed (Code: 7500) , and If I try to delete the whole table from the UI Its telling me to try again later (its the same for two days)

talfiz•11/29/23, 4:19 PM

I believe its a bug

Max (@rozenmd)OP•11/29/23, 4:21 PM

the dashboard doesn't support editing binary

talfiz•11/29/23, 4:21 PM

I dont try to edit the binary I try to edit a completly diffrent table

talfiz•11/29/23, 4:22 PM

Its the same message for all the tables

talfiz•11/29/23, 4:22 PM

Even tables with no binary

Mmushang98 I've tried this, but got error with the "env" variable. Any suggestion ?

Rob M.•11/29/23, 4:41 PM

Can you send a snippet with your "env" usage?

Rob M.•11/29/23, 4:44 PM

By the way, you should use 'context.env.*' instead of 'env.*' in Page workers. Maybe that's the issue.

talfiz•11/29/23, 5:56 PM

Hi guys I fixed the bug what I tried to do is to delete a user when a teacher is still refrenceing him to do that I should add ON DELETE CASCADE to the table thanks for however tried to help

MMax (@rozenmd)So far the plan is just for a single writer

rodrigo•11/30/23, 9:57 AM

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-IPCountryCF-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?

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

rodrigo•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)OP•11/30/23, 2:20 PM

then yeah, request.cfrequest.cf has the data you need: https://developers.cloudflare.com/workers/runtime-apis/request/#incomingrequestcfproperties

Max (@rozenmd)OP•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 TABLEALTER 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)OP•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)OP•12/1/23, 1:02 PM

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

we have folks building products that have 1 db per customer, where each db has the same schema, just

Similar Threads