Cloudflare Developers•2y ago

Does D1 supports sqlite extensions?

Ccosbgn Does D1 supports sqlite extensions?

Hard@Work•8/6/24, 8:43 AM

IIRC there are some pre-installed, but you can't configure/add/remove them yourself

cosbgnOP•8/6/24, 8:43 AM

Can I use something like sqlite-vec ?

Ccosbgn Can I use something like [sqlite-vec](https://github.com/asg017/sqlite-vec) ?

Hard@Work•8/6/24, 8:44 AM

Looking at it, I don't think so?

Hard@Work•8/6/24, 8:45 AM

Don't remember where(if there is a place) you can see what extensions are installed

cosbgnOP•8/6/24, 8:46 AM

I tried googling but I can't find it

Ccosbgn I tried googling but I can't find it

Hard@Work•8/6/24, 8:49 AM

I think this is it: https://github.com/cloudflare/workerd/blob/920ff29acca7c91793ada337464aa50f0c70a71f/build/BUILD.sqlite3

GitHub

workerd/build/BUILD.sqlite3 at 920ff29acca7c91793ada337464aa50f0c70...

The JavaScript / Wasm runtime that powers Cloudflare Workers - cloudflare/workerd

cosbgnOP•8/6/24, 8:58 AM

Thanks!

Ccopper Pls give a bit more info about the load in reads and writes/sec, thanks

Vikash•8/6/24, 10:42 AM

This is last 24h metrics -

.gwapes•8/6/24, 9:59 PM

Hi, I just wanted to ask and clarify whether D1 is globally disturbed. As in, the data is everywhere and with a worker the data will come from a system closest to where the request came from. And what thjs means with that. Sorry if these are simple questions

Cloudflare Docs

Data location · Cloudflare D1 docs

Learn how the location of data stored in D1 is determined, including where the leader is placed and how you optimize that location based on your …

..gwapes Hi, I just wanted to ask and clarify whether D1 is globally disturbed. As in, th...

Isaac McFadyen•8/6/24, 10:05 PM

D1 is not currently globally distributed. D1 is in one location (the location closest to where you created it, from the available regions that host D1) but there's no read replication currently.

.gwapes•8/6/24, 10:05 PM

Oh right, I thought it was. My bad. Thanks for the info appreciate it

Ccopper Very nice, I just wonder now how we can get per-seconds-numbers from that chart ...

Vikash•8/7/24, 2:46 AM

No, where to find write limit?

ad-z•8/7/24, 3:13 AM

Hi everyone, after some research, I have summarized the process as follows. I'm unsure if it's accurate, so I'm open to discussion.

[user]
│
request
│
▼
+--------------------------------------------+
| V8 Engine |
| +--------------------+ +-------------+ |
| | Workers(isloate) | | DB Bindings | |
| +--------------------+ +-------------+ |
+-------------------------------------------+
│
RESTful API request to a specific location
│
---------------------------
│
▼
+-----------------------------------------------------+
| V8 Engine |
| +------------------------+ +--------------------+ |
| | D1 Durable Object | | wrapped SQLite | |
| | workers(isloate) | | with VFS etc | |
| +------------------------+ +--------------------+ |
+-----------------------------------------------------+
│
▼
[private storage]

jdruwe•8/7/24, 9:06 AM

Quick question, I am building a small system sending out coupon codes to customers. I want these coupon code to be stored somewhere and I was thinking about using D1. Now I wonder, is there a way for me to lock a coupon code while being READ by user A preventing user B from receiving the same coupon code? I know batching is an option but I want to be sure this will work as I know there are some plan to introduce(?) read replica's.

Shannor•8/7/24, 1:45 PM

Question with using D1 in Intellij Datagrip products. I'm using the library recommended for the driver but I noticed this error posted here https://github.com/isaac-mcfadyen/d1-jdbc-driver/issues/6

Wanted to know if anyone has looked into it? I'm assuming it's low priority. I'm doing the work around but what just curious. Thanks in advance!

GitHub

Updating records inline is not working · Issue #6 · isaac-mcfadyen/...

First, thanks a lot for this project, whatever is available is already amazing to have and is an extreme productivity booster when working with D1

. Maybe enable GitHub sponsorships? For the actu...

SShannor Question with using D1 in Intellij Datagrip products. I'm using the library reco...

Isaac McFadyen•8/7/24, 3:12 PM

Hi! That's actually my lib, and somewhat unmaintained at the moment

Isaac McFadyen•8/7/24, 3:12 PM

There's a v2 branch/PR that I've done a bit of work on but haven't yet gotten the chance to merge, and there are still a few outstanding issues in there.

Shannor•8/7/24, 6:46 PM

Oh nice! Any way I could help out on it? I use it at work so it's wonderful for me! lol I don't have much experience making Intellij drivers though

iwgu•8/8/24, 10:56 AM

Hey, is there any roadmap for increasing 10gb limit?

Ccopper I mean what you could do is to just send the load yourself, e.g. with ab (Apach...

Vikash•8/8/24, 11:37 AM

I found the slowest query/time consuming is this one -

Delete from Accounts where account_id = ?

Delete from Accounts where account_id = ?

Because AccountsAccounts is the main table with foreign key in all other tables, so when a customer delete an account it freeze everything.
As the query also delete from 20 other tables as well with foreign key relationship.

Any suggestion?

Farhian•8/8/24, 12:29 PM

Hi guys, I am trying to restore (using time travel) one of our databases and I keep getting this error: {"D1_RESET_DO":true} [code: 7500]. i have tried using both the CLI and the web portal - incl. using different timestamps. Any thoughts? Thank you!

VVikash I found the slowest query/time consuming is this one - ``` Delete from Account...

Steved•8/8/24, 3:48 PM

Any suggestions?

It may reduce the duration of that query if you can do some pre-emptive cleanup before the final deletion of the Account. My guess is there's one or two tables that have most of the data in.

Steved•8/8/24, 3:50 PM

Though it may be trickier if you don't have account_id in some of the child tables, e.g. Accounts -> Users -> Conversations -> Messages with each cascading the deletion.

Vikash•8/8/24, 3:54 PM

I think other people also having similar issue with SQL Lite and I need to try with indexing as well. I am not really sure of foreign key are auto-indexed in SQL Lite or not. So, I'd need to try EXPLAIN QUERY PLANEXPLAIN QUERY PLAN on all tables

https://stackoverflow.com/questions/24259265/sqlite-bad-performance-with-on-delete-cascade

Stack Overflow

SQLite: Bad performance with ON DELETE CASCADE

I am using a SQLite Database in my application for storing measurement data.

It is organized in groups in projects. The tables are interconnected with foreign keys, that use ON DELETE CASCADE to h...

Steved•8/8/24, 4:00 PM

Oh my goodness. Yes, if there are missing indexes on FKs that would explain it - I had taken that for granted given the other DBs I use. Another quirk of SQLite to be mindful of.
The max query duration is 30s which is an incredibly long time, which suggests missing indexes, or eye watering amounts of data.
On the positive side, adding the indexes should be straightforward and may even improve your app's performance in other instances.

Vikash•8/8/24, 4:04 PM

yes, I was never thought of that coming from SQL being used on all other applications. So, I will build indexes tomorrow and hopefully it will resolve everything.

Steved•8/8/24, 4:46 PM

For what it is worth, I just checked one of my SQLite tables with a FK, and it doesn't claim to have an index with PRAGMA index_list('TableName');PRAGMA index_list('TableName'); and when I did a simple query on the table with EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc'EXPLAIN QUERY PLAN select * from TableName where UserId = 'abc' it reported use of SCAN (full table scan). So I think that confirms to me that the FK indexes are not created automatically. Thank you for highlighting this, as I'll need to revisit the schema's for my SQLite apps.
(Positives) The indexes will probably also reduce your CF costs too.

Mike•8/8/24, 5:29 PM

If anyone is looking for a GUI for D1, we just added support for it to our VSCode extension: https://dbcode.io/docs/supported-databases/d1/d1

Docs - Cloudflare D1

Database Management in Visual Studio Code. Supports PostgreSQL, MySQL, MariaDB, SQL Server, and more. Connect to your database and manage your data with ease.

Peter JY•8/9/24, 9:38 AM

Parden me. Is it possible to insert into a table from a xml file, and select * from the table where a function returns true to a xml file?

Ssteida I want to ask whether D1 SQLite will be updated. I need jsonb. Thank you

Vy•8/9/24, 6:16 PM

no known timeline at the moment. But we're discussing an update in Q4, plus how we can stay more regular with sqlite updates.

VVy no known timeline at the moment. But we're discussing an update in Q4, plus how ...

iwgu•8/9/24, 7:29 PM

Hey, are you considering increasing 10gb limit in foreseen future?

Iiwgu Hey, are you considering increasing 10gb limit in foreseen future?

Isaac McFadyen•8/9/24, 7:35 PM

See this message for some past details from an employee - in short, maybe a bit but not really, D1 is more suited towards many small DBs (for example, one per customer) than one larger one. https://canary.discord.com/channels/595317990191398933/992060581832032316/1157239450741526560

IIsaac McFadyen See this message for some past details from an employee - in short, maybe a bit ...

iwgu•8/9/24, 7:42 PM

Hey, thank you, it is clear now. In that case I guess going horizontal and requesting increase on "maximum storage per account" when limit is reached is the way.

Vikash•8/10/24, 1:57 PM

Yes, why not 20? For example a simple CRM may have (Accounts, Users, Apikeys, Contacts, Deals, Pipelines, Lists, SavedFilters, Tags etc) and each has account_id.

iska•8/10/24, 6:57 PM

hi how are d1 migrations handled in prod ? read only , no write ? for your customers, do you have a way to know time estimation it will take to let them know the duration of the maintenance ? any rollback in case something gone wrong ? what about data backup of d1 does it happen automatically ?

iska•8/10/24, 6:59 PM

also whats the storage limit for D1 ? curious if you have people running multiple TB at ease with it

Iiska also whats the storage limit for D1 ? curious if you have people running multipl...

Isaac McFadyen•8/10/24, 7:01 PM

10GB per database currently. The goal with D1 is to create many smaller DBs (i.e. one DB per customer, one DB per user, etc) rather than a single monolithic DB.

Isaac McFadyen•8/10/24, 7:01 PM

See here for more details: https://canary.discord.com/channels/595317990191398933/992060581832032316/1157239450741526560

iska•8/10/24, 7:07 PM

alright that changes thing up a bit but 1DB per customer say you have 50k users because you are a free app but only 10k are paying

you wouldnt want to create a DB for non paying users, a waste
2.having a db per customer makes aggregation queries such as grouped statistics for example a bit complicated
say you run multiples applications, you would hit rather quickly the limit of i think i saw 50k db

iska•8/10/24, 7:29 PM

i guess i could shard something like this:

App1:

Free Users:
- app1_free_shard_1 (1GB D1 DB)
- app1_free_shard_2 (1GB D1 DB)
  ...
Paid Users:
- app1_paid_shard_1 (5GB D1 DB)
- app1_paid_shard_2 (5GB D1 DB)
  ...
Enterprise:
- app1_tenant_bigcorp (10GB D1 DB)
- app1_tenant_shared_1 (10GB D1 DB for multiple smaller tenants)
  ...
Analytics:
- app1_analytics (10GB D1 DB)

but that includes a whole lot of infra dev and complex logic

iska•8/10/24, 7:30 PM

monolithic db are good because they are easy to start with , then you start sharding later in the road but starting to shard right off the boat given D1 limit seems a no brainer

wonder if someone already created some library to achieve something similar

Ccopper btw, how many secs did the entire delete in those 20 table at the end? btw2, D1 ...

Vikash•8/11/24, 2:13 PM

It’s resolved after creating index on all foreign keys

Does D1 supports sqlite extensions?

Similar Threads

Similar Threads

Similar Threads