how easy would it be to access a D1 database from outside of cloudflare pages & workers? i need to

how easy would it be to access a D1 database from outside of cloudflare pages & workers?

i need to build something that i cannot run on workers or pages, but all the data i would need would be on cf d1. other than using the api is there any other way?

Aaddison how easy would it be to access a D1 database from outside of cloudflare pages & ...

Cyb3r-Jak3•11/5/23, 3:27 AM

There isn’t.

Aaddison how easy would it be to access a D1 database from outside of cloudflare pages & ...

kev-ac•11/5/23, 12:56 PM

Depends on what you need to do with the data you could create a worker that interacts with the DB for you. But keep the overall overhead of this in mind.

Aaddison how easy would it be to access a D1 database from outside of cloudflare pages & ...

Max (@rozenmd)•11/5/23, 3:48 PM

https://github.com/elithrar/http-api-d1-example

GitHub

GitHub - elithrar/http-api-d1-example: An example HTTP API for Clou...

An example HTTP API for Cloudflare's D1 database: https://developers.cloudflare.com/d1/

GitHub - elithrar/http-api-d1-example: An example HTTP API for Cloudflare's D1 database: ht...

roftt•11/5/23, 4:37 PM

Is it possible for "json_extract" to search for all values, regardless of letter casing?

다

다미•11/5/23, 5:29 PM

Does d1 support ST_Distance_Sphere?

_tweak^prone•11/5/23, 5:50 PM

Does

wrangler dev

wrangler dev

connect to my external Cloudflare D1 if i specify it in the wrangler.toml by merely doing:

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "(my db name)"
database_id = "(my db id)"

[[d1_databases]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "(my db name)"
database_id = "(my db id)"

I would like to work on my "live" D1 database, on local dev.

Dahoom•11/5/23, 5:51 PM

you could try the remote dev feature

Dahoom•11/5/23, 5:51 PM

i never tried it myself but it MIGHT possibly have that as a feature

Dahoom•11/5/23, 5:52 PM

otherwise I would recommend turso for connecting to a live db while in local dev

DDahoom otherwise I would recommend turso for connecting to a live db while in local dev

_tweak^prone•11/5/23, 5:57 PM

turso ?

Dahoom•11/5/23, 5:57 PM

https://turso.tech

Dahoom•11/5/23, 5:58 PM

difference here is you can directly connect to a db instead of needing a worker binding to access it

_tweak^prone•11/5/23, 5:58 PM

I need anything that has super fast excellent conenction to Cloudflare Workers, and which I can connect to rather seamles with Laravel. So Turso would be find if it suits that.

_tweak^prone•11/5/23, 5:58 PM

currently i connect to D1 via PDO in Laravel directly though

Dahoom•11/5/23, 5:59 PM

Im sure it wont be an issue with turso

Dahoom•11/5/23, 6:00 PM

it uses sqlite and you can connect to it with libsql

_tweak^prone•11/5/23, 6:00 PM

Im perplexed though, that I can connect via Laravel^PHP to D1 "live", but it seems its a problem with wrangler ? Or atleast, i have to make hoops to make it work ?

Dahoom•11/5/23, 6:01 PM

you cant easily connect to the live D1 db in a local context

_tweak^prone•11/5/23, 6:01 PM

well, i off to turso then, to test that

Dahoom•11/5/23, 6:01 PM

good luck

_tweak^prone•11/5/23, 6:18 PM

Hmm - It doesnt seem like Laravel has any integration into turso.

Dahoom•11/5/23, 6:22 PM

look for php integrations maybe? If not you should easily be able to use it with a libsql adapter

DDahoom look for php integrations maybe? If not you should easily be able to use it with...

_tweak^prone•11/5/23, 6:23 PM

A php integration would however bypass the entire framework.

do you know how to use libsql regular adapter with the token ? is it prefixed the url ?

Dahoom•11/5/23, 6:25 PM

no idea honestly, I just use turso with a libsql node package so most of that is done for me

_tweak^prone•11/5/23, 6:25 PM

yearh, they seem to have support for TS/JS, Python, Go etc .

Im going back to D1

__tweak^prone Does `wrangler dev` connect to my external Cloudflare D1 if i specify it in the ...

Matt Silverlock•11/5/23, 6:25 PM

Pass

--remote

--remote

__tweak^prone Does `wrangler dev` connect to my external Cloudflare D1 if i specify it in the ...

Matt Silverlock•11/5/23, 6:25 PM

https://developers.cloudflare.com/d1/learning/local-development/ (rather)

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

MMatt Silverlock Pass `--remote`

_tweak^prone•11/5/23, 6:26 PM

just in the nick of time

_tweak^prone•11/5/23, 6:26 PM

Oh yearh - this was what i was looking for. Thanks !

GuillaumeLakano•11/5/23, 7:07 PM

Hi! I have 2 pricing questions about D1 please
1) If a table is empty, is the SELECT request is billed ?
2) If a table contains for example 100,000 rows, is a DROP TABLE is billed like one write request, or as a 100,000 writes requests ?

kian•11/5/23, 7:09 PM

Whilst I’m not at my computer to test, the meta object returned should have rows_read and rows_written that tell you

Kkian Whilst I’m not at my computer to test, the meta object returned should have rows...

GuillaumeLakano•11/5/23, 7:10 PM

Oh good point, I'll try, thank you!

GuillaumeLakano•11/5/23, 8:08 PM

From my tests, I'm surprised because the rows_read / rows_written are not what I thought:

1) An INSERT is indeed 1 write, but also a read cost
2) More surprising, an « INSERT ... RETURNING * » is 2 writes + 2 reads
3) A DROP TABLE is 3 read and 0 write
4) A CREATE TABLE is 1 read + 2 writes
5) A SELECT from an empty table is 1 read

Do you known if it's the correct expected billing please ?

Frank Walsh•11/5/23, 10:06 PM

Has anyone successfully applied migration in cloudflare CI/CD or GitHub?

In cloudlflare it seems like the command executes in my build.sh file but nothing happens.

In GitHub I get a generic bash exits with error code 1

Frank Walsh•11/5/23, 10:11 PM

The interesting thing about the GitHub error is initially I got an error stating that CLOUDFLARE_API_TOKEN had to be defined as an environment variable. After defining that is when I get the generic error

Original message was deleted

GuillaumeLakano•11/5/23, 11:23 PM

This could explains the read cost, but not the multiples writes from a CREATE TABLE or a INSERT/RETURNING

GGuillaumeLakano From my tests, I'm surprised because the rows_read / rows_written are not what I...

Unsmart•11/6/23, 12:16 AM

The metadata there is correct but also an INSERT will not always be only 1 write... it depends on the number of indexes because it has to write to each index. Not sure why insert returning would add another write there though

kw•11/6/23, 12:53 AM

how can we use transaction in D1 to avoid race condition? my use case is I'll read some data and write based on the data I read. how to avoid dirty read in this case? db.batch() seems not useful here

kw•11/6/23, 1:06 AM

something like SELECT FOR UPDATESELECT FOR UPDATE

GGuillaumeLakano From my tests, I'm surprised because the rows_read / rows_written are not what I...

Matt Silverlock•11/6/23, 1:12 AM

These all seem reasonable but it will also depend on your index configuration. Also keep in mind the first 25 billion reads are included.

MMatt Silverlock These all seem reasonable but it will also depend on your index configuration. A...

GuillaumeLakano•11/6/23, 7:51 AM

I totally agree that the pricing includes a generous number of free reads. There is also free writes operations, it's really appreciated.

As the write cost is 1,000 time bigger than read cost, we trying to estimate our bill after the free part. It's why I've done theses checks of rows_reads/rows_written on some SQL operations and I'm surprised on some costs.

For example we'll remove all our RETURNING keywords from our SQL statements because we had stupidly thought that this will cause only an additional read, and not an additional write.

Pascale•11/6/23, 11:20 AM

I'm struggling to use any of the two D1 exporters due to the error: "D1 Error: Isolate Has exceeded Memory Size". Anyone have a pointer to get a whole backup from large databases? Tuning the respective body size limits etc. didnt help

kian•11/6/23, 11:24 AM

Which exporter are you using, https://github.com/nora-soderlund/cloudflare-d1-backups?

Pascale•11/6/23, 11:24 AM

yeah, I tried this one and the fork

Pascale•11/6/23, 11:26 AM

the reasoning behind that is that the app is currently structured in a way to only interact with one database, but I hit the 2GB limit pretty fast, so now I try to pool the data over multiple databases to work around that.

the backup should help me migrate my data

__tweak^prone Does `wrangler dev` connect to my external Cloudflare D1 if i specify it in the ...

JustinNoel•11/6/23, 12:10 PM

PSA:

You can access your local D1sqlite data base by going into the follow in your source directory:

.wrangler -> state -> v3 -> d1 -> miniflare-D1DatabaseObject -> xxxxxxxxxx.sqlite.wrangler -> state -> v3 -> d1 -> miniflare-D1DatabaseObject -> xxxxxxxxxx.sqlite

Just use any SQLite GUI tool and and open that file.

Note: If you use any database change commands to alter the structure of the database, you will not see those changes in your current GUI. You have to close the GUI connection and open the file again.

Arda Acar•11/6/23, 12:43 PM

How can I export d1 database as .sql with wrangler?

AArda Acar How can I export d1 database as .sql with wrangler?

Max (@rozenmd)•11/6/23, 12:57 PM

with wrangler, you can't - alphaalpha databases only let you download .sqlite backups, and betabeta databases don't let you download backups yet

MMax (@rozenmd)with wrangler, you can't - `alpha` databases only let you download .sqlite backu...

Arda Acar•11/6/23, 1:45 PM

Thanks for the answer. Will it be possible to back up the .sql database when it comes out of beta?

AArda Acar Thanks for the answer. Will it be possible to back up the .sql database when it ...

Max (@rozenmd)•11/6/23, 1:49 PM

Probably not as a means of backup, since we have point-in-time recovery now: https://developers.cloudflare.com/d1/learning/time-travel/

but we're aware folks want to be able to export their databases at any time, and it's in our backlog to make that possible

how easy would it be to access a D1 database from outside of cloudflare pages & workers? i need to

Similar Threads

Similar Threads

Similar Threads