Cloudflare Developers•3y ago

Like this: https://gist.github.com/jonasb/42c4acab8226b0afd0590124be756878

Gist

Using LIMIT on CTE

Using LIMIT on CTE. GitHub Gist: instantly share code, notes, and snippets.

Jjonasb Like this: https://gist.github.com/jonasb/42c4acab8226b0afd0590124be756878

Lane C•10/11/23, 9:53 PM

Thanks will try that. Although I can imagine there could be something off with my query or indexes, it doesn't explain why the query executes in less than 200ms locally and 7 seconds in production against the same-sized dataset...

HHello, I’m Allie!Though you can also add multiple value fields to a single insert

Samantas5855•10/11/23, 10:08 PM

How can we do this by the way?

Samantas5855•10/11/23, 10:10 PM

For example if we want to perform 10 identical inserts how can this be done without repeating the query with one https request?

ilsubyeega•10/12/23, 3:03 AM

do reading from subset of table not counts as rows_read?

For example, if the users table has an index on a timestamp column created_atcreated_at, the query SELECT * FROM users WHERE created_at > ?1SELECT * FROM users WHERE created_at > ?1 would only need to read a subset of the table.

For https://developers.cloudflare.com/d1/platform/pricing/, I still cannot understand how rows read works at this moment. Rows reading measurement only shows example of full table scan, but not others such like partial read (filterable, join etc).

Also, how will db.dump() from client api being calculated as billing, is it just full scan of rows_read?

ilsubyeega•10/12/23, 3:05 AM

^ also https://developers.cloudflare.com/workers/wrangler/commands/#backup-download

divby0•10/12/23, 5:26 AM

I read that d1 is strongly consistent apart form read replicas. Are there read replicas yet, do I maybe (without knowing) have turned them on? Or can I rely that after a write is done, all reads will include that new data?

Dahoom•10/12/23, 6:03 AM

you could probably Jerryrig an inefficient replication system with the cache api

DDahoom you could probably Jerryrig an inefficient replication system with the cache api

divby0•10/12/23, 6:07 AM

Yeah but I just have an edge case where it would be comfortable to rely on the strong consistency without checking stuff. But I guess I'll just do the check in case I want to have read replication later on and forget about that part

Bangonkali•10/12/23, 7:56 AM

Hi guys just a quick question. has anyone ever succeeded in running a local instance

wrangler pages dev

wrangler pages dev

bound to a local instance of D1D1 started up with something like pnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sqlpnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sql ? Just want to make sure this is a supported use case for D1D1 in the Beta stage @ the moment. Note: I already have a working D1D1 setup with pages on Cloudflare. I'm interesting in running locally. So my question essentially is just if whether this is already supported or not.

BBangonkali Hi guys just a quick question. has anyone ever succeeded in running a local inst...

forslunds•10/12/23, 8:28 AM

i have no problems running D1 locally? just have to remember as you are trying that its a seperate db from remote so you need to migrate/seed the local also.

Varna•10/12/23, 9:22 AM

I dumped a 300mb dump.sql file and fed it to

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql

but it stayed the same for past 1h:

Mapping SQL input into an array of statements

Fforslunds i have no problems running D1 locally? just have to remember as you are trying t...

Bangonkali•10/12/23, 9:35 AM

Hi @forslunds , were you able to connect your local cloudflare pages dev instance to your local D1? I'm having issues with binding since my Env.DB is null after migrations. D1 stand-alone is working well in my case.

VVarna I dumped a 300mb dump.sql file and fed it to `pnpx wrangler d1 execute db_name -...

itsmatteomanf•10/12/23, 10:06 AM

That tells me you have very long single queries... which are probably gonna fail.

itsmatteomanf•10/12/23, 10:07 AM

Can you try and reduce the size of each query or do it in parts?

Iitsmatteomanf Can you try and reduce the size of each query or do it in parts?

Varna•10/12/23, 10:24 AM

not sure. I'm trying to figure out if it is possible to migrate MySQL -> D1. I could import only schema, and then setup a background Worker to populate the data during night or smth. But I couldn't manage to run a Worker that could connect to a MySQL db, so I would probably need to put the dump.sql on a R2 first.

Varna•10/12/23, 10:29 AM

well, people will need a way to migrate into D1 after release anyways, so I guess this is the chance to find or make an example for such case

VVarna not sure. I'm trying to figure out if it is possible to migrate MySQL -> D1. I c...

itsmatteomanf•10/12/23, 10:29 AM

You can just reduce the size of the .sql dump file by itself...
Just do one with the one schema, one with the data (or multiples depending on the size of the file itself).

itsmatteomanf•10/12/23, 10:31 AM

Just note that each statement can't be more than 100kB, but you can have as many statements as you want (there is a size limit to the total D1 Query the API accepts, I've hit it, but I haven't found it explicitly listed so can't say exactly)... cc @rozenmd @Matt

itsmatteomanf•10/12/23, 10:31 AM

It will return a

Request entity is too large [code: 7011]

Request entity is too large [code: 7011]

error from Wrangler, though.

Iitsmatteomanf Just note that each statement can't be more than 100kB, but you can have as many...

Varna•10/12/23, 10:32 AM

ohh, that's very good to know, cause currently my line limit is set to 1000KB (default mysqldump setting), ty

VVarna ohh, that's very good to know, cause currently my line limit is set to 1000KB (d...

itsmatteomanf•10/12/23, 10:33 AM

Yeah, that's a hard limit

Ddivby0 I read that d1 is strongly consistent apart form read replicas. Are there read r...

Max (@rozenmd)•10/12/23, 10:47 AM

no replication yet

MMax (@rozenmd)no replication yet

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

awesome thanks

Iitsmatteomanf Just note that each statement can't be more than 100kB, but you can have as many...

Max (@rozenmd)•10/12/23, 10:49 AM

we're probably limited by the api gateway

MMax (@rozenmd)we're probably limited by the api gateway

itsmatteomanf•10/12/23, 11:08 AM

But is there really a 100% overhead considering I’ve seen many other endpoints being limited at 100MB? I had 50MB of data…

itsmatteomanf•10/12/23, 11:09 AM

(The full statement, not the CSV I started from)

roftt•10/12/23, 11:12 AM

If I'm not logged in in wrangler, can I run something like this?: CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrangler d1 execute test-database --command="SELECT * FROM Customers"

Somehow I did yesterday but it's not working now. Or am I completely missing something?

Rroftt If I'm not logged in in wrangler, can I run something like this?: CLOUDFLARE_ACC...

Max (@rozenmd)•10/12/23, 11:15 AM

you should be, what does "not working" mean, error message?

Max (@rozenmd)•10/12/23, 11:15 AM

you can debug that your tokens are right via

CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrangler whoami

CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrangler whoami

Iitsmatteomanf But is there really a 100% overhead considering I’ve seen many other endpoints b...

Max (@rozenmd)•10/12/23, 11:16 AM

i dont understand the question tbh

MMax (@rozenmd)i dont understand the question tbh

itsmatteomanf•10/12/23, 11:19 AM

Like, I had a ~50MB .sql file, which I executed and failed. If the limit is 100MB on the gateway (assuming it as the one used by other endpoints, like KV…) it assumes there is a ton of overhead on the actually query, which seems strange

Max (@rozenmd)•10/12/23, 11:28 AM

like if you have a 49mb file, does it work?

Max (@rozenmd)•10/12/23, 11:30 AM

i'm just saying i doubt we changed the default api gateway limits for d1 since the alpha would crash if you looked at it wrong

MMax (@rozenmd)you should be, what does "not working" mean, error message?

roftt•10/12/23, 12:05 PM

'CLOUDFLARE_ACCOUNT_ID' is not recognized as an internal or external command, operable program or batch file.

Rroftt 'CLOUDFLARE_ACCOUNT_ID' is not recognized as an internal or external command, op...

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

that'll be it then -

CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrangler d1 execute test-database --command="SELECT * FROM Customers"

CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrangler d1 execute test-database --command="SELECT * FROM Customers"

needs to be run from a linux-like environment

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

otherwise you need to add

CLOUDFLARE_ACCOUNT_ID

CLOUDFLARE_ACCOUNT_ID

and

CLOUDFLARE_API_TOKEN

CLOUDFLARE_API_TOKEN

to your windows system env vars

MMax (@rozenmd)that'll be it then - `CLOUDFLARE_ACCOUNT_ID=1234 CLOUDFLARE_API_TOKEN=4321 wrang...

roftt•10/12/23, 12:08 PM

Oh, thanks! Forgot that I ran it from my ec2 linux instance

Mming Hi, I would like to build a worker application; and I want the browser to receiv...

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

Not yet - best you can do is use a cron trigger (https://developers.cloudflare.com/workers/configuration/cron-triggers) and check manually every 60 seconds

Mming thanks. Is there a plan to support this? Something like change data capture for ...

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

it's in the backlog, yep

Jsx•10/12/23, 3:40 PM

Just a curios question.
Will D1 make out of workers environment and become like a standalone DB as a service?

Original message was deleted

Max (@rozenmd)•10/12/23, 7:07 PM

i've actually been slowly iterating on that UI so i can write my articles in there

LLane C Is D1 super slow with multiple joins? I have a simple blog schema. I have less t...

Max (@rozenmd)•10/12/23, 7:17 PM

actually noticing this might be an issue with LIMIT/OFFSET, will raise it internally

AliAlhusainy•10/12/23, 7:41 PM

Samantas5855•10/12/23, 8:38 PM

Is it possible to perform many insertions without repeating the query on the https api? Something similar to this syntax:

kian•10/13/23, 1:03 AM

There's nothing special about how D1 handles it, so you can do whatever SQLite supports. For example, you can repeat the values to insert several rows.

INSERT INTO test (id) VALUES (1), (2), (3), (4), (5)

INSERT INTO test (id) VALUES (1), (2), (3), (4), (5)

kian•10/13/23, 1:05 AM

If you wanted to use parameters, then you'd do this.

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

{
  "params": [
    1,
    2,
    3,
    4,
    5
  ],
  "sql": "INSERT INTO test (id) VALUES (?), (?), (?), (?), (?)"
}

kian•10/13/23, 1:05 AM

You can take a look at https://www.sqlite.org/lang_insert.html for the syntax of

INSERT

INSERT

Santa DCON 🎄•10/13/23, 2:47 AM

Yay a bug! Not sure if posted, but if the primary key is set to null, the dashboard's update function fails to open:

Like this: https://gist.github.com/jonasb/42c4acab8226b0afd0590124be756878

Similar Threads