https://github.com/nora-soderlund/cloudflare-d1-backups

GitHub

GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a ...

A package to dump a copy of your experimental D1 database through a worker. - GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a copy of your experimental D1 database through a worker.

DDahoom https://github.com/nora-soderlund/cloudflare-d1-backups

mukhtharcm•10/2/23, 4:17 AM

Ty!

Mmukhtharcm Ty!

DahoomOP•10/2/23, 4:18 AM

dont thank me, thank nora. Shes the one who made it

Original message was deleted

mukhtharcm•10/2/23, 6:15 AM

Thank you @nora for this!
I've been searching forever for something like this!

AAlexander I'm guessing you have a production database of some kind and are you planning to...

Laurent Pellegrino•10/2/23, 12:54 PM

Thanks for your message.

Yep, it's somewhat like a read-only replica. But the challenge is that about 90% of the data is refreshed daily, so diff computing or event-based reactions won't be of much help. I think @rozenmd's generic response might be due to Cloudflare not wanting backups to be abused. If I'm not mistaken, backup imports don't count as writes. So, in theory, someone could set up a local database, fill it up, back it up, and then import to Cloudflare D1 at no cost. Those backups weren't just for disaster recovery; they fit my scenario perfectly. But with only Timetravel remaining, the $1/M write cost is steep for those of us needing to import large databases daily or even weekly only.

Max (@rozenmd)•10/2/23, 12:59 PM

backup imports don't count as writes

D1's beta backend doesn't have backup imports - you can write to the DB, and you're billed as a write.

Metrics do not appear for D1's older alpha dbs because the metrics feature was built only for the beta backend.

JJason Hostetter Great to hear, looking forward to it. Was worried that functionality would be lo...

Matt Silverlock•10/2/23, 1:40 PM

FYI: you will be able to create up to 50,000 D1 databases per account as of this week (and if you genuinely get close, we can increase further: this is a soft limit).

Steved•10/2/23, 1:55 PM

Great to hear. Will there be a way for a pages app, for example, to access the multiple databases without adding new bindings, and having to redeploy? Classic case of multi-tenancy. In other systems, you'd just load the DB connection string for the tenant then access the DB.

MMatt Silverlock FYI: you will be able to create up to 50,000 D1 databases per account as of this...

Altrius•10/2/23, 3:29 PM

heck, thats a lot of databases haha, if anyone manages to actually reach that ill be impressed (unless they do one db per user? which could make sense for some use cases I guess haha)

Epailes•10/2/23, 5:45 PM

"By default, D1 will automatically create your database in a location close to where you issued the request to create a database"
I hadn't even considered that instances will be close to users by default because of this, D1 will honestly be game changing for multi-tenant applications!

Epailes•10/2/23, 5:46 PM

I'm thinking ahead to performing analytics on all the instances, right now I have a postgres instance setup using RLS to separate data between tenants. Currently it's relatively trivial for me to see how users interact with the application just by writing a sql query and exporting the report.

Would the best tool to get a similar experience be using analytics-engine? Although I don't always know the data I'm after ahead of time (and pricing hasn't been announced for this yet).

Would it be feasible to create a report generating worker which loops through each of the D1 instances running a similar query and spits it out into a report. Or is there a better way?

EEpailes I'm thinking ahead to performing analytics on all the instances, right now I hav...

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

What kind of data are you after? I would use Analytics Engine for "product events" (like what I think you are describing) and D1's own GraphQL API for "show me top-level usage by database: rows read, written, errors, etc"

kian•10/2/23, 6:47 PM

Usually within a week or two.

kian•10/2/23, 6:47 PM

The Cloudflare Workers runtime needs to make a new release using the latest workerd release/commit.

MMatt Silverlock What kind of data are you after? I would use Analytics Engine for "product event...

Epailes•10/2/23, 6:48 PM

Yeah this is more around product events, eg what is the most popular type of product category, what theme is the most used, how many products are hosted by the platform in total across all tenants?

MMatt Silverlock FYI: you will be able to create up to 50,000 D1 databases per account as of this...

Sameer Ali•10/2/23, 7:01 PM

THAT'S GREAT. is that going to be available for durable object only or we'll be able to use them in worker with dynamic binding?

Unsmart•10/2/23, 7:06 PM

Thats not dynamic bindings yet afaik you would have to pre bind each database or use the API to update the worker binding to add the new one. Probably best and easiest used with WfP

Unsmart•10/2/23, 7:07 PM

And not really sure how durable object come into play in your question

UUnsmart And not really sure how durable object come into play in your question

Max (@rozenmd)•10/3/23, 5:02 AM

like how DOs don't have to bind to individual objects

Unsmart•10/3/23, 5:04 AM

Yeah thats the nice part with DOs for sure but maybe im just reading into the wording too much but it seems like it was a bit of a different question

MMatt Silverlock FYI: you will be able to create up to 50,000 D1 databases per account as of this...

GuillaumeLakano•10/3/23, 9:34 AM

Do you known if this soft limit of 50,000 databases will be increased again later for the paid plans please?
I known it's possible to have kind of unlimited databases in WfP, but this require a business account of $200 / month if I understood correctly.
Our goal is to isolate 1 DB per user for security reason (like a sandbox)

GGuillaumeLakano Do you known if this soft limit of 50,000 databases will be increased again late...

itsmatteomanf•10/3/23, 9:36 AM

Matt will reply specifically for this case, but in general for Cloudflare soft limits can be increased for any account, but may require specific subscriptions to do so. I presume, but have no confirmation, that you can increase it just by asking Matt (while in beta, maybe even after) or opening a support ticket.

GGuillaumeLakano Do you known if this soft limit of 50,000 databases will be increased again late...

Epailes•10/3/23, 9:47 AM

Fyi ent contracts are closer to $2000/month last I checked

EEpailes Fyi ent contracts are closer to $2000/month last I checked

GuillaumeLakano•10/3/23, 9:48 AM

There is a Business plan at $200, but I don't known if WfP is only for Enterprises account or Business, but in all case even Business for us it's out of our budget

GuillaumeLakano•10/3/23, 9:50 AM

Generally, the security features of Cloudflare are free, as the goal is to have a better/safer internet. Our goal is only to add a extra security layer with isolate each DB per user, so in case of a successful attack, it's mitigated to only the user database

divby0•10/3/23, 10:00 AM

I upgraded my wrangler v2 to v3 and in that process I start getting

Expression tree is too large (maximum depth 20)

Expression tree is too large (maximum depth 20)

for some queries. Those are not particularly complex, it's just like get x -> join this field -> join this field, so I'd guess this would be the depth 2 or 3?

It worked in v2 and it works on prod, so how comes it doesn't work when I locally upgrade to wrangler v3?

Here's the specific query:

  const teamArticles = await db.query.articles.findMany({
    where: ownerOrInTeam(articles.userId, authUser), // this is one equality check
    with: {
      pineconeReferences: true,
      outlineDrafts: { // if I do outlineDrafts: true, I am not over 20 anymore
        with: { // this seems to be the edge at which it goes over 20 seemingly
          draftPieces: true
        }
      }
    }
  });

  const teamArticles = await db.query.articles.findMany({
    where: ownerOrInTeam(articles.userId, authUser), // this is one equality check
    with: {
      pineconeReferences: true,
      outlineDrafts: { // if I do outlineDrafts: true, I am not over 20 anymore
        with: { // this seems to be the edge at which it goes over 20 seemingly
          draftPieces: true
        }
      }
    }
  });

This is drizzle syntax by the way if that helps.

divby0•10/3/23, 10:03 AM

Just to make it clear, this happens on several requests, not just this particular query.

kian•10/3/23, 10:24 AM

D1 has

SQLITE_LIMIT_EXPR_DEPTH

SQLITE_LIMIT_EXPR_DEPTH

set to

on the new backend.

kian•10/3/23, 10:24 AM

Your database is likely still on the

alpha

alpha

backend - try running

wrangler d1 info <dbname>

wrangler d1 info <dbname>

kian•10/3/23, 10:25 AM

In any case, if you can get ahold of the actual SQL query that Drizzle generates, you can prefix it with

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

which should tell you the actual expression depth of your query.

divby0•10/3/23, 10:26 AM

Yes, in fact the database was created in alpha backend. 1. How long can I keep this for this existing project, is there any sunset planned? Can I still create new databases with this backend?

Kkian Your database is likely still on the `alpha` backend - try running `wrangler d1 ...

divby0•10/3/23, 10:26 AM

btw for anyone reading this after the fact, the command is

wrangler d1 info <dbname>

wrangler d1 info <dbname>

without the database

kian•10/3/23, 10:27 AM

Not sure on those, cc @Matt

Kkian In any case, if you can get ahold of the actual SQL query that Drizzle generates...

divby0•10/3/23, 10:27 AM

I can try, let me see if I can get it

kian•10/3/23, 10:34 AM

You wouldn't be the first person to run into it, but if you have several real-world examples of queries that hit the limit then it should be reasonable to raise the limit.

divby0•10/3/23, 10:36 AM

Do you speak for cloudflare or do you mean raise it locally for my wrangler v3 setup? It can't be that cloudflare would globally raise the limit because of me, would they?

kian•10/3/23, 10:37 AM

d1-database

kian•10/3/23, 10:37 AM

Just echoing Matt

divby0•10/3/23, 10:39 AM

That makes sense. I am having trouble finding out how exactly to get the executed query to then run it with the explain prefix, but I'll paste it here as soon as I have

kian•10/3/23, 10:39 AM

Looks like you can end it in

toSQL();

toSQL();

https://orm.drizzle.team/docs/goodies#printing-sql-query

divby0•10/3/23, 10:48 AM

Unfortuantely not with the relational query I am using (db.query.[....]db.query.[....]) only with the ones that start differently

GGuillaumeLakano Do you known if this soft limit of 50,000 databases will be increased again late...

Matt Silverlock•10/3/23, 10:49 AM

How many do you need, and is that “now” or “planning for two years from now” - ? We want to allow folks to get increases as needed, but I want to understand if this is an actual blocker for you or not. Keep in mind you can only have ~5k databases per Worker script right now.

GGuillaumeLakano There is a Business plan at $200, but I don't known if WfP is only for Enterpris...

Matt Silverlock•10/3/23, 10:50 AM

If $200/mo is out of your budget - you still have to pay for D1 reads, writes & storage. $200 gets you a LOT of that, but it’s not free!

Ddivby0 Unfortuantely not with the relational query I am using (`db.query.[....]`) only ...

Matt Silverlock•10/3/23, 10:51 AM

Open an issue on GitHub with your query and error. I will not keep up with it here.

MMatt Silverlock Open an issue on GitHub with your query and error. I will not keep up with it he...

divby0•10/3/23, 10:53 AM

Will do

Is there a way to remove my blocker by using wrangler v3 with an alpha / legacy D1 binding? Cause I can't continue development or I'd have to go back to wrangler v2 if it's not possible

MMatt Silverlock How many do you need, and is that “now” or “planning for two years from now” - ?...

GuillaumeLakano•10/3/23, 11:09 AM

We're planning our project release for around Q1 of 2024 too, so we'll wait just after the release of D1 in GA. Before, we've used the DO to have a kind of document DB per user. Was useful but all the eviction problem (sharing memory per Classe) make us move to D1 when you've confirmed to me few weeks/months ago that you'll permit kind of unlimited databases in the future. So, now we're full D1 SQL.

Since, I've read in the blog article « D1: beta open » that finally this kind of unlimited databases will be reserved to your WfP clients (enterprise plan of $2,000 / month), so finally may be we need to have one full classic DB for all the users, or to fallback to our old architecture on DO.

Since yesterday ,you've increased from 25 to 50,000 databases per account, it's a big step, and it's only a soft limit, but we need to understood if we take a risk to add this security layer ( 1 user = 1 D1 database, kind of sandbox ), because if we reach for example 50,000 users, we'll be totally trap in a very bad situation.

GGuillaumeLakano We're planning our project release for around Q1 of 2024 too, so we'll wait just...

Matt Silverlock•10/3/23, 11:17 AM

We will continue to increase it for paid Workers plans (before/at GA). Don’t worry.

MMatt Silverlock We will continue to increase it for paid Workers plans (before/at GA). Don’t wor...

GuillaumeLakano•10/3/23, 11:19 AM

Good to heard, thank you for the answer ! I suppose you can't now spoil a little bit on the future soft limite?

const teamArticles = await db.query.articles.findMany({ where: ownerOrInTeam(articles.userId, authUser), // this is one equality check with: { pineconeReferences: true, outlineDrafts: { // if I do outlineDrafts: true, I am not over 20 anymore with: { // this seems to be the edge at which it goes over 20 seemingly draftPieces: true } } } });

https://github.com/nora-soderlund/cloudflare-d1-backups

Similar Threads