Am I understanding correctly that SQLite does not support remote access other than through Workers,

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 RETURNINGRETURNING

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:sqlitebun:sqlite instead of better-sqlite3better-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?

Ddave This is my flow: 1. Request A inserts data into a table. 2. Request A redirects...

Matt Silverlock•9/14/23, 5:19 PM

When the

await

await

returns the data is inserted. D1 is strongly consistent - read-your-own-writes - from within the same Worker invocation.

MMatt Silverlock When the `await` returns the data is inserted. D1 is strongly consistent - read-...

dave•9/14/23, 5:20 PM

the thing is Request B could be coming in from a completely different colo

dave•9/14/23, 5:20 PM

hmm, I guess I should do await then

Ddave hmm, I guess I should do await then 🙂

Matt Silverlock•9/14/23, 5:22 PM

You should always do

await

await

ReeachyZ_•9/14/23, 5:22 PM

whats the maven repository for use the cloudfalre api?

RReeachyZ_whats the maven repository for use the cloudfalre api?

James•9/14/23, 5:38 PM

maven repo for what exactly? I'm not sure this is D1 related

Can you expand?

RReeachyZ_whats the maven repository for use the cloudfalre api?

James•9/14/23, 5:40 PM

Ah I see you have a thread open at https://canary.discord.com/channels/595317990191398933/1151930094504390736/1151930094504390736. This definitely isn't D1 related, so please keep the discussion to that thread. Someone will reply to you if and when they can assist

JJames maven repo for what exactly? I'm not sure this is D1 related 🤔 Can you expand?

ReeachyZ_•9/14/23, 5:40 PM

I think I must have gone to the wrong channel. But basically I want to check whenever there is a DDoS attack (with the cloudflare api) block all traffic from the country of origin until the attack is stopped. Instead of doing this manually in the dashboard, I wanted to do it automatically.

JJames Ah I see you have a thread open at https://canary.discord.com/channels/595317990...

ReeachyZ_•9/14/23, 5:40 PM

Ok, thank you

dave•9/14/23, 6:24 PM

Is there an easy command I can do to dump everything from my local D1 database?

dave•9/14/23, 6:24 PM

like all tables, rows, etc

SSanju Has anyone successfully raised their D1 database limit here? If yes, what's the ...

Matt Silverlock•9/14/23, 7:37 PM

Number of databases? What are you looking for? We aren't raising storage limits one-off, but we can raise the # of databases.

Matt Silverlock•9/14/23, 7:59 PM

We've published some useful database-level commands in the docs: https://developers.cloudflare.com/d1/learning/database-commands/

Debug D1 · Cloudflare D1 docs

D1 supports a number of database-level commands that allow you to list tables, indexes, and inspect the schema for a given table or index.

Ccopper Was out of the loop, was there work on D1's write performance in the meantime? A...

Dahoom•9/15/23, 7:09 AM

compared to the older versions of d1?

Am I understanding correctly that SQLite does not support remote access other than through Workers,

Similar Threads

Similar Threads

Similar Threads