It depends on your total database size limit. At 100MB, it’s about 25,000, but then you wouldn’t hav

It depends on your total database size limit. At 100MB, it’s about 25,000, but then you wouldn’t have any room left for data nor indexes. In practice, we’re likely to document this as 1,000 (per DB) as a soft limit.

MMatt Silverlock It depends on your total database size limit. At 100MB, it’s about 25,000, but t...

peachneo•5/7/23, 9:21 AM

thanks

Ffate i tried around here and there, but i couldn't find options to set foreign keys i...

Hello, I’m Allie!•5/7/23, 7:07 PM

You should just be able to add foreign keys to your create table statement

TigersWay•5/8/23, 3:41 AM

Hi. Wondering how far/deep we should not be concerned about the (bad) performance of our SQL queries.... I have multiple - relatively simple - queries working with the primary-key-automatic-index which should be done in a few ms, taking now ~1s, while the network latency is not so great but clearly expected.

TigersWay•5/8/23, 3:42 AM

@geelen LOL the usual "soon"

TigersWay•5/8/23, 3:43 AM

Well it's still usable because clearly not in production.... I'll wait. Thanks

lukeed•5/8/23, 5:23 AM

Any pricing updates?

lukeed•5/8/23, 5:24 AM

James•5/8/23, 6:38 AM

Nothing official yet. Functionally, there’s probably not going to be much difference other than your Cloud vendor of choice, which is going to come down to availability and pricing.

D1 is still very early and in alpha right now, but it’ll probably make the most sense to use alongside the rest of the Cloudflare stack.

Murder Chicken•5/8/23, 11:41 AM

Is there a best practice way to accomplish a query of this nature (with many more records) through D1's API?

INSERT INTO example_table (activity_id, user_id, status) 
VALUES 
  (2496, 1, 'AVAILALBE'),
  (2496, 1, 'NOT_AVAILABLE'),
  (2496, 2, 'AVAILABLE') 
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO example_table (activity_id, user_id, status) 
VALUES 
  (2496, 1, 'AVAILALBE'),
  (2496, 1, 'NOT_AVAILABLE'),
  (2496, 2, 'AVAILABLE') 
ON CONFLICT DO UPDATE SET status = excluded.status;

Murder Chicken•5/8/23, 11:41 AM

Is it leveraging

batch

batch

with an array of objects or something of that nature?

Murder Chicken•5/8/23, 11:42 AM

Though I could not find a way to pass an array of data to batch... just an array of

stmt.bind

stmt.bind

ashutosh•5/8/23, 1:01 PM

Can we access the D1 database outside Cloudflare workers environment?

Farhian•5/8/23, 3:27 PM

Hi guys, does D1 support the 'WITH RECURSIVE' syntax? Thank you!

Llukeed Any pricing updates?

Epailes•5/8/23, 3:29 PM

I think developer week is next week (or very soon) based on some comments staff have made, looks like it'll be shared then

Farhian•5/8/23, 3:31 PM

@Sourabh.Sashank you need to be careful about SQL injection in those queries

Farhian•5/8/23, 3:32 PM

You might want to consider preparing the queries and bind the params

Farhian•5/8/23, 3:33 PM

Will do, thank you

Farhian•5/8/23, 3:33 PM

it seems the recursive was added in mysql 3.8 and D1 is using 3.39.4 - so it should work

Farhian•5/8/23, 3:36 PM

Thanks @skye_31

Ssourabh.sashank Yeah. This is just the sample inner code to find out issue. We are doing vari...

Farhian•5/8/23, 3:37 PM

awesome

Murder Chicken•5/8/23, 4:38 PM

Attempting to create a trigger in my schema.sql file results in a

ERROR 9009: SQL prepare error: incomplete input [code: 7500]

ERROR 9009: SQL prepare error: incomplete input [code: 7500]

which I've seen reported elsewhere in here. Anyone experience this and get past it? Even the simplest trigger fails when trying to use the

--file

--file

operator from wrangler to seed the live DB.

Murder Chicken•5/8/23, 4:39 PM

Removal of the trigger from the schema file and it works as expected but I was under the impression that sqlite triggers were supported (given, alpha).

MMurder Chicken Removal of the trigger from the schema file and it works as expected but I was u...

Matt SilverlockOP•5/8/23, 6:01 PM

Can you share your syntax?

MMatt Silverlock Can you share your syntax?

Matt SilverlockOP•5/8/23, 6:01 PM

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

Matt SilverlockOP•5/8/23, 6:01 PM

File = https://github.com/cloudflare/wildebeest/blob/main/migrations/0000_initial.sql

Matt SilverlockOP•5/8/23, 6:02 PM

This all works, and has for a while, so I'm curious what you're doing differently here, or if your schema is too large or has some newline issue

Murder Chicken•5/8/23, 6:02 PM

This is the current bit that's breaking it...

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

Murder Chicken•5/8/23, 6:03 PM

Everything else in the file seems to be fine. Removal of this piece allows it to run.

Murder Chicken•5/8/23, 6:04 PM

Wrangler 2.19.0

Murder Chicken•5/8/23, 6:06 PM

Of further note, this works locally.

➜ npx wrangler@d1 d1 execute test-db --file 0000_initial.sql 🌀 Mapping SQL input into an array of statements 🌀 Parsing 25 statements 🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities' WHEN OLD.status <> NEW.status BEGIN INSERT INTO changelog (c_id, for_date, status_was, status_is) VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status); END;

It depends on your total database size limit. At 100MB, it’s about 25,000, but then you wouldn’t hav

Similar Threads

Similar Threads

Similar Threads