I found the slowest query/time consuming is this one - ``` Delete from Accounts where account_id =

I found the slowest query/time consuming is this one -

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Because

Accounts

Accounts

Accounts

Accounts is the main table with foreign key in all other tables, so when a customer delete an account it freeze everything.
As the query also delete from 20 other tables as well with foreign key relationship.

Any suggestion?

Farhian•8/8/24, 12:29 PM

Hi guys, I am trying to restore (using time travel) one of our databases and I keep getting this error: {"D1_RESET_DO":true} [code: 7500]. i have tried using both the CLI and the web portal - incl. using different timestamps. Any thoughts? Thank you!

VVikash I found the slowest query/time consuming is this one - ``` Delete from Account...

Steved•8/8/24, 3:48 PM

Any suggestions?

It may reduce the duration of that query if you can do some pre-emptive cleanup before the final deletion of the Account. My guess is there's one or two tables that have most of the data in.

Steved•8/8/24, 3:50 PM

Though it may be trickier if you don't have account_id in some of the child tables, e.g. Accounts -> Users -> Conversations -> Messages with each cascading the deletion.

VikashOP•8/8/24, 3:54 PM

I think other people also having similar issue with SQL Lite and I need to try with indexing as well. I am not really sure of foreign key are auto-indexed in SQL Lite or not. So, I'd need to try EXPLAIN QUERY PLANEXPLAIN QUERY PLAN on all tables

https://stackoverflow.com/questions/24259265/sqlite-bad-performance-with-on-delete-cascade

Stack Overflow

SQLite: Bad performance with ON DELETE CASCADE

I am using a SQLite Database in my application for storing measurement data.

It is organized in groups in projects. The tables are interconnected with foreign keys, that use ON DELETE CASCADE to h...

Steved•8/8/24, 4:00 PM

Oh my goodness. Yes, if there are missing indexes on FKs that would explain it - I had taken that for granted given the other DBs I use. Another quirk of SQLite to be mindful of.
The max query duration is 30s which is an incredibly long time, which suggests missing indexes, or eye watering amounts of data.
On the positive side, adding the indexes should be straightforward and may even improve your app's performance in other instances.

VikashOP•8/8/24, 4:04 PM

yes, I was never thought of that coming from SQL being used on all other applications. So, I will build indexes tomorrow and hopefully it will resolve everything.

Steved•8/8/24, 4:46 PM

For what it is worth, I just checked one of my SQLite tables with a FK, and it doesn't claim to have an index with PRAGMA index_list('TableName');PRAGMA index_list('TableName'); and when I did a simple query on the table with EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc'EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc' it reported use of SCAN (full table scan). So I think that confirms to me that the FK indexes are not created automatically. Thank you for highlighting this, as I'll need to revisit the schema's for my SQLite apps.
(Positives) The indexes will probably also reduce your CF costs too.

Mike•8/8/24, 5:29 PM

If anyone is looking for a GUI for D1, we just added support for it to our VSCode extension: https://dbcode.io/docs/supported-databases/d1/d1

Docs - Cloudflare D1

Database Management in Visual Studio Code. Supports PostgreSQL, MySQL, MariaDB, SQL Server, and more. Connect to your database and manage your data with ease.

Peter JY•8/9/24, 9:38 AM

Parden me. Is it possible to insert into a table from a xml file, and select * from the table where a function returns true to a xml file?

Ssteida I want to ask whether D1 SQLite will be updated. I need jsonb. Thank you

Vy•8/9/24, 6:16 PM

no known timeline at the moment. But we're discussing an update in Q4, plus how we can stay more regular with sqlite updates.

VVy no known timeline at the moment. But we're discussing an update in Q4, plus how ...

iwgu•8/9/24, 7:29 PM

Hey, are you considering increasing 10gb limit in foreseen future?

Iiwgu Hey, are you considering increasing 10gb limit in foreseen future?

Isaac McFadyen•8/9/24, 7:35 PM

See this message for some past details from an employee - in short, maybe a bit but not really, D1 is more suited towards many small DBs (for example, one per customer) than one larger one. https://canary.discord.com/channels/595317990191398933/992060581832032316/1157239450741526560

IIsaac McFadyen See this message for some past details from an employee - in short, maybe a bit ...

iwgu•8/9/24, 7:42 PM

Hey, thank you, it is clear now. In that case I guess going horizontal and requesting increase on "maximum storage per account" when limit is reached is the way.

VikashOP•8/10/24, 1:57 PM

Yes, why not 20? For example a simple CRM may have (Accounts, Users, Apikeys, Contacts, Deals, Pipelines, Lists, SavedFilters, Tags etc) and each has account_id.

iska•8/10/24, 6:57 PM

hi how are d1 migrations handled in prod ? read only , no write ? for your customers, do you have a way to know time estimation it will take to let them know the duration of the maintenance ? any rollback in case something gone wrong ? what about data backup of d1 does it happen automatically ?

iska•8/10/24, 6:59 PM

also whats the storage limit for D1 ? curious if you have people running multiple TB at ease with it

Iiska also whats the storage limit for D1 ? curious if you have people running multipl...

Isaac McFadyen•8/10/24, 7:01 PM

10GB per database currently. The goal with D1 is to create many smaller DBs (i.e. one DB per customer, one DB per user, etc) rather than a single monolithic DB.

Isaac McFadyen•8/10/24, 7:01 PM

See here for more details: https://canary.discord.com/channels/595317990191398933/992060581832032316/1157239450741526560

iska•8/10/24, 7:07 PM

alright that changes thing up a bit but 1DB per customer say you have 50k users because you are a free app but only 10k are paying

you wouldnt want to create a DB for non paying users, a waste
2.having a db per customer makes aggregation queries such as grouped statistics for example a bit complicated
say you run multiples applications, you would hit rather quickly the limit of i think i saw 50k db

iska•8/10/24, 7:29 PM

i guess i could shard something like this:

App1:

Free Users:
- app1_free_shard_1 (1GB D1 DB)
- app1_free_shard_2 (1GB D1 DB)
  ...
Paid Users:
- app1_paid_shard_1 (5GB D1 DB)
- app1_paid_shard_2 (5GB D1 DB)
  ...
Enterprise:
- app1_tenant_bigcorp (10GB D1 DB)
- app1_tenant_shared_1 (10GB D1 DB for multiple smaller tenants)
  ...
Analytics:
- app1_analytics (10GB D1 DB)

but that includes a whole lot of infra dev and complex logic

iska•8/10/24, 7:30 PM

monolithic db are good because they are easy to start with , then you start sharding later in the road but starting to shard right off the boat given D1 limit seems a no brainer

wonder if someone already created some library to achieve something similar

Ccopper btw, how many secs did the entire delete in those 20 table at the end? btw2, D1 ...

VikashOP•8/11/24, 2:13 PM

It’s resolved after creating index on all foreign keys

iska•8/13/24, 9:40 AM

surely, i wish there was a library that offered the orchestration of sharding D1 dbs, with features like auto resharding, migration sharding, visual interface , would be so valuable to workers D1 eco system

hendrixString•8/13/24, 11:49 AM

Hi there, a quick question. Why doesn't the REST-API of D1 enables a batch of parameterized statements (it only allows static statements joined by a semicolon, which is SQL Injection vulnerable). Any plans as to when this will be supported ? The way it is supported with Workers

sandulat•8/13/24, 1:31 PM

Hey! I'm running a simple SQL query with D1 (paid plan) and Drizzle every minute in a Worker's CRON job:

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

The query has been running thousands of times without any issues, but today I encountered this random error:

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

The

authRequests

authRequests

table is empty 99% of the time, or occasionally contains just a few rows (<20). Could this be an issue on Cloudflare's side?

Here's the

createdAt

createdAt

column definition:

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

It has no indexes, but I find it hard to believe that's the problem.

Abensur•8/13/24, 9:51 PM

Anyone tried the request form to increase "Maximum characters (bytes) in a LIKE or GLOB pattern"?

Abensur•8/13/24, 9:52 PM

Maybe I should use eq instead of like

Abensur•8/13/24, 9:53 PM

yeah, nvm, eq worked just fine, wrong use case for like

0xwei_hup•8/14/24, 5:25 AM

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

hey guys, would like to ask what's the difference for local and preview actually? I only know that local is stored at your local

.wrangler/state/v3/d1/miniflare-d1databaseobject/...

.wrangler/state/v3/d1/miniflare-d1databaseobject/...

directory but what's preview?

iska•8/14/24, 6:33 AM

Is D1 going to support auto scaling some day so user doesn’t have to deal manually with sharding ? Like how long can we estimate this feature to be ready approximately less than a year from now or no eta at all ?

00xwei_hup ```--preview boolean optional Execute commands/files against a preview D1 databa...

Hello, I’m Allie!•8/14/24, 6:01 PM

Preview runs against a real D1 DB

jacob•8/14/24, 6:04 PM

Are D1 errors just presented as a regular "Error"? How should I handle a scenario where I want to check specifically if the error in my catch block is due to a unique constraint failing

jacob•8/14/24, 6:06 PM

I'm effectively trying to do something like this ..

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

arch•8/14/24, 8:32 PM

How do people prefer to manage their Workers + D1 schemas? So far I've been relying on just SQL files I manually load in. Prefer not using an ORM in most cases :p

jdruwe•8/14/24, 9:58 PM

Guys, I have a question regarding pushing a lot of data into D1. Basically I want to insert around 300K records (based on data from a CSV file I am passed). I build a UI using Hono inside a worker but I am running into the following error: "Exceeded CPU Time Limits", so to me it seems like a worker is not the best solution. What other option can you suggest to me? Could I just insert the data using the REST api? I just noticed there is also an option for: https://developers.cloudflare.com/workers/testing/local-development/#develop-using-remote-resources-and-bindings. It also results the same error, isn't that weird as I am executing the logic locally? For now I think something like: $ npx wrangler d1 execute example-db --remote --file=users_export.sql is better

Ashish Rawat•8/15/24, 7:15 AM

D1 is not usable without sharding. Just for mini projects it's good. I want to know is there any lib to shard d1 with workers.

AAshish Rawat D1 is not usable without sharding. Just for mini projects it's good. I want to k...

iska•8/15/24, 9:24 AM

im using drizzle orm to avoid vendor lock in and will move to aws rds whenever i hit 10 gb limit, dealing with sharding is a hassle , will wait for an auto scale solution from cloudflare

iska•8/15/24, 9:24 AM

i m building a product , dont want to deal with infra too much

rossanmol•8/15/24, 5:26 PM

Getting this error not authorized: SQLITE_AUTH in Workers when using D1? Anyone knows what it could be?

Rrossanmol Getting this error not authorized: SQLITE_AUTH in Workers when using D1? Anyone ...

Isaac McFadyen•8/15/24, 5:27 PM

Are you trying to access internal SQLite tables or use SQLite pragmas?

Isaac McFadyen•8/15/24, 5:27 PM

Some are blocked on D1 for security reasons.

Isaac McFadyen•8/15/24, 5:28 PM

If you are using a database ORM or explorer then it's possible it's making those queries on your behalf to try and inspect the schema of the database (and getting blocked).

jdruwe•8/15/24, 9:49 PM

When importing data into a D1 db using the wrangler command it mentions: "This process may take some time, during which your D1 database will be unavailable to serve queries.". I noticed this also in a test as drizzle showed "7500: Currently processing a long-running import." on a select query. Is there any way to get around this? Lets assume that the DB is already being used in production, I would like for the app to continue working. Any suggestions?

I found the slowest query/time consuming is this one - ``` Delete from Accounts where account_id =

Similar Threads

Similar Threads

Similar Threads