@rozenmd I would like to use the url in the where clause. how do we reference the url?

Ddigeratidave @rozenmd I would like to use the url in the where clause. how do we reference th...

request.urlrequest.url has the URL. I'd suggest reading the MDN docs on how to parse URLs (https://developer.mozilla.org/en-US/docs/Web/API/URL) and/or moving over to #workers-help for non-D1 specific questions

URL - Web APIs | MDN

The URL interface is used to parse, construct, normalize, and encode URLs. It works by providing properties which allow you to easily read and modify the components of a URL.

Matt Silverlock•9/11/23, 10:20 PM

https://x.com/andrii_sherman/status/1701243280321552399?s=46

Andrew Sherman (@andrii_sherman)

Just sitting here, browsing @Cloudflare D1 from @DrizzleORM Studio

Twitter

•

9/11/23, 2:36 PM

justaname•9/11/23, 11:35 PM

Was wondering if connecting to the database via a url is possible?

Jjustaname Was wondering if connecting to the database via a url is possible?

Cyb3r-Jak3•9/11/23, 11:37 PM

You mean as like a connection string or via HTTP?

justaname•9/11/23, 11:38 PM

Yes

Jjustaname Yes

Cyb3r-Jak3•9/11/23, 11:40 PM

Connection string: No
HTTP API: Not officially, you can reverse engineer wrangler but not to be stable and can break

justaname•9/11/23, 11:42 PM

There is a plan for a connection string in the future though correct?

Jjustaname There is a plan for a connection string in the future though correct?

Max (@rozenmd)•9/12/23, 8:33 AM

I don't work on the core, so I don't have the full picture, but I don't see how connection string could work without a custom driver, by default sqlite looks for files on the filesystem to connect to

MMax (@rozenmd)I don't work on the core, so I don't have the full picture, but I don't see how ...

GuillaumeLakano•9/12/23, 3:41 PM

Matt confirm that later this will be possible to have millions of databases, so I suppose you'll support a kind of connection string or similar to permit to reach a specific database not mentioned in the wrangler.toml config

For our use case, our goal to have million of D1 databases is to permit us to create 1 DB per client, to isolate and increase the security (it's what we do currently with Durable Objects). But we can't put million of ID in the wrangler.toml so there will be surely a way to point to the DB of our choice directly in the code. ( BTW it's the DB name is also limited to 32 alphanumeric characters currently in the wrangler.toml )

GGuillaumeLakano Matt confirm that later this will be possible to have millions of databases, so ...

Max (@rozenmd)•9/12/23, 3:47 PM

That particular case should be possible, I assumed the previous commenter was talking about the sqlite library's connection string, not from inside Workers

Unsmart•9/12/23, 3:48 PM

Theres been some talks around dynamic bindings for all R2/D1/Queues before but not something that blocks GA (i.e. might be a while till it happens)

digeratidaveOP•9/12/23, 6:08 PM

Am I understanding correctly that SQLite does not support remote access other than through Workers, Pages, and Wrangler?
I was looking for a tool like TOAD or SQL Developer to remotely access the database to run queries but it sounds like SQLite does not support that. Did I understand correctly?

Ddigeratidave Am I understanding correctly that SQLite does not support remote access other th...

Cyb3r-Jak3•9/12/23, 6:35 PM

There’s an unofficial api that you can use to make queries but nothing like a native connection string

Ddigeratidave Am I understanding correctly that SQLite does not support remote access other th...

Matt Silverlock•9/12/23, 6:51 PM

SQLite itself is file-based. Anyone building on it needs to expose some sort of network API - such as HTTP

dave•9/12/23, 11:46 PM

How can I combine these two in one query?

const d1_lookup_code = await c.env.DB.prepare(`SELECT id, program, details FROM my_codes WHERE code = ?`).bind(code).first();
await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ?`).bind(code).run();

const d1_lookup_code = await c.env.DB.prepare(`SELECT id, program, details FROM my_codes WHERE code = ?`).bind(code).first();
await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ?`).bind(code).run();

Unsmart•9/12/23, 11:51 PM

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).run()

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).run()

Unsmart•9/12/23, 11:51 PM

dave•9/12/23, 11:52 PM

oh nice

dave•9/12/23, 11:52 PM

where is RETURNING documented?

kian•9/12/23, 11:53 PM

It's just apart of SQLite (and Postgres) https://www.sqlite.org/lang_returning.html

dave•9/12/23, 11:53 PM

it's really hard to search for the word

RETURNING

RETURNING

dave•9/12/23, 11:54 PM

ahhhhh

dave•9/12/23, 11:54 PM

it's somewhat new, guess that's why chatgpt failed me

dave•9/12/23, 11:54 PM

thanks!

dave•9/12/23, 11:55 PM

can I filter what gets returned?

dave•9/12/23, 11:55 PM

gonna try it, one sec

dave•9/12/23, 11:56 PM

Error: D1_ERROR: Error: SQL execute error: Execute returned results - did you mean to call query?

Error: D1_ERROR: Error: SQL execute error: Execute returned results - did you mean to call query?

dave•9/12/23, 11:57 PM

ah .run is wrong

dave•9/12/23, 11:59 PM

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).first()

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).first()

dave•9/12/23, 11:59 PM

worked perfectly! Thank you!

GuillaumeLakano•9/13/23, 1:29 PM

Hi ! How is it stored the foreign keys in the storage please? Is it just the storage cost of a pointer to the associated table or is the content value of the foreign key is stored on each rows ?
To add context, Imagine a table « urls » where the primary key is an text URL, and another table of millions of users that include the foreign key to this « urls » table

Original message was deleted

Matt Silverlock•9/13/23, 7:30 PM

Necessarily, yes - same for nearly any relational SQL database.

GGuillaumeLakano Hi ! How is it stored the foreign keys in the storage please? Is it just the sto...

Matt Silverlock•9/13/23, 7:30 PM

Can you expand - what problem are you trying to solve?

Matt Silverlock•9/13/23, 9:01 PM

Time Travel is now (fully) in the Cloudflare dashboard: https://x.com/elithrar/status/1702064456484762002

Matt Silverlock 🐀 (@elithrar)

Don't mind me, just using Time Travel to immediately roll back my D1 database via the @cloudflare dashboard (now live!)

Twitter

•

9/13/23, 8:59 PM

Epailes•9/13/23, 9:49 PM

For anyone interested in using prisma with d1: https://github.com/prisma/prisma/issues/13310#issuecomment-1718355367

GitHub

Support for Cloudflare's new SQLite database D1 · Issue #13310 · pr...

Cloudflare have just announced their first relational DB D1 which is based on SQLite. https://blog.cloudflare.com/introducing-d1/ Would be awesome if Prisma could support it one day..

SSanju I'm on an enterprise plan. I'm working on a new product that uses D1. Are there ...

Cyb3r-Jak3•9/14/23, 2:43 AM

Maybe try reaching out to your CSM to see, but given it is a alpha product not sure

itstmillertime•9/14/23, 2:52 AM

It would be nice for miniflare to support

bun:sqlite

bun:sqlite

instead of

better-sqlite3

better-sqlite3

.
When I use bun as a package manager, I find it does not install the bindings correctly for better-sqlite3, and D1 queries throw an error as a result.

Cyb3r-Jak3•9/14/23, 2:53 AM

You can make a feature requets on the workers-sdk repo

MMatt Silverlock Can you expand - what problem are you trying to solve? 🙂

GuillaumeLakano•9/14/23, 4:53 AM

I known I can uses a foreign key based on the rowid of the other table (urls) rather than uses a text URL as a primary/foreign key. But what is the D1 storage impact if I decide to uses a long string as a key ? If the foreign key is a long text (ex: 200 characters), and there is ten of millions of rows that includes this long text foreign keys. Do it's increase the D1 storage a lot, or internally do you simply store each foreign key as a pointer (few bytes) to the matched table/primary key row?

Iitstmillertime It would be nice for miniflare to support `bun:sqlite` instead of `better-sqlite...

DaniFoldi•9/14/23, 10:08 AM

Eventually the better-sqlite3 dep will go away, since workerd already includes sqlite

DaniFoldi•9/14/23, 10:10 AM

In fact, the next release should already contain this: https://github.com/cloudflare/miniflare/commit/5846058d85d94a06988a19a3c62e2e6f2dff7cfc

GGuillaumeLakano I known I can uses a foreign key based on the rowid of the other table (urls) ra...

Matt Silverlock•9/14/23, 12:00 PM

It's a pointer

futurepaul•9/14/23, 2:22 PM

is d1 down right now? getting a "failed to reach database. please try again later" status

dave•9/14/23, 4:30 PM

This is my flow:

1. Request A inserts data into a table.
2. Request A redirects the client to make another requests.
3. Request B reads that data back that was inserted in request A.

Can I get away with using a

waitUntil

waitUntil

for the data insertion in request A and immediately redirect to request B, or do I have to wait until the insert is complete before redirecting to request B?