I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at once to a D1 databa

I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at once to a D1 database. I'm getting failures due to maxing out the number of parameters to my sql statement.

I understand

batch

batch

batch

batch is the solution to this, but can I send 10k sql inserts in one batch? Is there a limit to batch?

RRexNormous I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at...

Hard@Work•7/26/24, 2:10 PM

The limit to a batch is just how many you can cram into memory. I would say batch is a decent bit more efficient than a single large insert

Chaika•7/26/24, 2:10 PM

yea it was said before someone got 100k queries in a batch without issues: https://discord.com/channels/595317990191398933/992060581832032316/1180147342373044274

I use batch with upserts up to ~1-2k rows without issue

CChaika yea it was said before someone got 100k queries in a batch without issues: https...

RexNormousOP•7/26/24, 2:15 PM

Excellent, thank you both. Great to hear that batch isn't hard-limited!

CChaika https://developers.cloudflare.com/workers/platform/limits/ free gets 1 mb max si...

Chinoman10•7/26/24, 10:34 PM

Yea this 1MB max. size limitation hit me when I built a Functions backend with some stuff from the Typescript package... as soon as I imported it it exploded

. So I had to scrap that whole beautiful development I made, and now it's exclusive to our own company (where we are gladly paying for Workers Paid), since none of our clients want to pay every month for things that get called once per week or something similar.

debug•7/26/24, 11:05 PM

Hi guys, does anyone tried using the D1 rest api ?

Ddebug Hi guys, does anyone tried using the D1 rest api ?

Chinoman10•7/27/24, 11:46 AM

Yes, we use it, why do you ask?

CChinoman10 Yes, we use it, why do you ask?

debug•7/27/24, 11:47 AM

All good, i had lots of troubles to set it up, mostly because of cors and preflight requests

Fishy!•7/28/24, 2:16 AM

Hey, is there a link to the specification for how dynamic bindings and migrations will fit together?

surgery18•7/28/24, 6:03 PM

Does D1 support triggers? Whenever I try to run the migration with --remote to create a trigger, I get "X [ERROR] incomplete input: SQLITE_ERROR [code: 7500]" Example code of what I am trying to run is like this

-- Create a simple table
DROP TABLE IF EXISTS test_table;

CREATE TABLE IF NOT EXISTS test_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Drop any existing trigger
DROP TRIGGER IF EXISTS test_trigger;

-- Create a simple trigger
CREATE TRIGGER test_trigger
AFTER UPDATE ON test_table
FOR EACH ROW
BEGIN
  UPDATE test_table
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = OLD.id;
END;

-- Create a simple table
DROP TABLE IF EXISTS test_table;

CREATE TABLE IF NOT EXISTS test_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Drop any existing trigger
DROP TRIGGER IF EXISTS test_trigger;

-- Create a simple trigger
CREATE TRIGGER test_trigger
AFTER UPDATE ON test_table
FOR EACH ROW
BEGIN
  UPDATE test_table
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = OLD.id;
END;

surgery18•7/28/24, 6:04 PM

But if I run it with --local it is fine.

Ssurgery18 Does D1 support triggers? Whenever I try to run the migration with --remote to ...

boop•7/28/24, 10:18 PM

It does support triggers d1-database

surgery18•7/28/24, 11:09 PM

Ok, this is strange. I cannot do the triggers during migrations, but I can run a query manually via the console on the web to create the triggers. That might be something to look at if that is a bug. I would think it should be able to create triggers during migrations.

Darkblacks•7/29/24, 5:43 AM

Hey guys. Is there a column limit on the D1 database? I heard it was 100. If so is there a way to increase that?

몰

몰라세스•7/29/24, 5:45 AM

If Sqlite's column limit is 100 col, no ways of increase.

몰

몰라세스•7/29/24, 5:46 AM

But, it has using relation or DB normalization.

Darkblacks•7/29/24, 5:48 AM

I see that the the SQLite has a 2000 column limit but not sure if D1 is restricting it

Darkblacks•7/29/24, 5:49 AM

Also is there a limit on storage per database? I know it says 0.76/GB after 10gb but is there a limit

Darkblacks•7/29/24, 5:49 AM

I am trying to migrate from MS SQL so looking at options

Darkblacks•7/29/24, 6:00 AM

I just tried creating a table with over 100 columns and it gives me an error saying too many columns. Why is there a limit on this when native SQLite supports way more?

Chaika•7/29/24, 6:12 AM

D1 follows sqlites high security recommendations (although they've relaxed some), SQLite recommends 100 max https://www.sqlite.org/security.html

Darkblacks•7/29/24, 6:16 AM

Thanks for that. Would you happen to know if there is a restriction on storage for D1. I am in the worker paid plain and it says 5GB free then 0.75/GB after that. Is there a limit on how much I can store since the docs have conflicting answers

Chaika•7/29/24, 6:20 AM

Limits page has those answers: https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

Chaika•7/29/24, 6:21 AM

On paid, 10 GB max per db and 250 GB per account (account max can be raised, per DB is more of a hard limit they haven't been raising for each customer)

Darkblacks•7/29/24, 6:42 AM

Cool. Thanks for the help

Vinay•7/29/24, 4:45 PM

anyone has issues with escalating D1 costs? My cost went up 10x without any uptick in traffic.
I dont see 2 months of data analysis, I cant find which queries or objects are offending.

How do I solve this issue

kian•7/29/24, 4:47 PM

Try the wrangler d1 insightswrangler d1 insights command: https://developers.cloudflare.com/d1/observability/metrics-analytics/#query-insights

Cloudflare Docs

Metrics and analytics · Cloudflare D1 docs

D1 exposes database analytics that allow you to inspect query volume, query latency, and storage size across all and/or each database in your account.

Vinay•7/29/24, 8:18 PM

wrangler d1 insights says "To find top 10 queries by execution count:"
In what time period? It is not mentioned in the docs either

VVinay wrangler d1 insights says "To find top 10 queries by execution count:" In what t...

Isaac McFadyen•7/29/24, 8:20 PM

Try specifying the --timePeriod--timePeriod flag like in the last example on that page.

Isaac McFadyen•7/29/24, 8:20 PM

Like --timePeriod=7d--timePeriod=7d etc.

Vinay•7/29/24, 8:21 PM

thank you

Original message was deleted

Cyb3r-Jak3•7/30/24, 12:11 AM

I doubt it because the insights come from the GraphQL API

Ccopper When will D1 get replication?

Hard@Work•7/30/24, 8:58 AM

:soontm:

Ppeshakoo is the best way to delete thousands of random rows via batches, or is there a wa...

Matt Silverlock•7/30/24, 11:30 AM

What are you trying to do? Why random? Why batches?

Super Morris•7/30/24, 2:27 PM

Is there a way to get billing alarm on D1 row reads? (there only is reads for DO)
We had a loop in a D1 SQL request, tha cost us a few hundert bucks

Fabian•7/30/24, 8:17 PM

wasn‘t this already changed a few months ago as well? Like it was different before and said it made not much sense so it was changed to metrics

FFabian wasn‘t this already changed a few months ago as well? Like it was different befo...

Chaika•7/30/24, 8:22 PM

I believe the full story is that about 5 months ago they changed it to return nothing because the docs said it wouldn't but a big library or two used it, and it broke them. So it was reverted. I guess it never made it out again under a compat flag, and instead now they're just documenting it correctly

Zack•7/30/24, 10:00 PM

Question about D1, is there currently any global read replica infrastructure in place? I have read conflicting information online, some saying that it does this, and some saying that it is coming soon. Just wanted to understand if it does actually do this yet or not, or if it's currently just a single-homed read/write instance

ZZack Question about D1, is there currently any global read replica infrastructure in ...

Cyb3r-Jak3•7/30/24, 10:14 PM

Still coming :soontm:

CCyb3r-Jak3 Still coming :soontm:

Zack•7/30/24, 10:15 PM

Alright, look forward to it arriving

Zack•7/30/24, 10:15 PM

Sadly kind of useless to me currently until that arrives

Old Man Maker•7/31/24, 5:11 AM

Hi all - is INSERT OR IGNORE INTO supported on D1?

Super Morris•7/31/24, 7:01 AM

@cloudflare please add billing alarms for d1 with highest priority! that can ruin people

Hhannes just to be clear: there are already read replicas, 4 (I think) per D1 instance i...

Chaika•7/31/24, 12:45 PM

There's no read replicas at the moment. Your D1 Database is a singular Durable Object/single thread running in a single location.
Now DO Storage is usually replicated to a few nearby locations part of the region, but that's just for recovery/backup, it's not running in more then one location

KWAC•7/31/24, 3:28 PM

I'm using nuxt + drizzle orm + cloudflare D1.

I can insert, delete, update data from drizzle studio (binded with D1), but I defined api and make api calls from by webapp page to perform those actions, it returns status 200 (OK) but nothing changes in database

I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at once to a D1 databa

Similar Threads