is there anyway to perform spatial queries in D1? Considering a switch from Postgres for a new proje

is there anyway to perform spatial queries in D1? Considering a switch from Postgres for a new project but worried I won't be able to perform a common query:

Find a row that is closest to the user's position

Find a row that is closest to the user's position

Find a row that is closest to the user's position

Find a row that is closest to the user's position

Tony Leung•4/2/24, 2:36 PM

Oh i get it, it's like aws outpost

Matt Silverlock•4/2/24, 2:43 PM

With the initial support for Python + Workers (https://blog.cloudflare.com/python-workers), we've put together a brief example on how to query D1 from Python: https://developers.cloudflare.com/d1/examples/query-d1-from-python-workers/

Cloudflare Docs

Query D1 from Python Workers · Cloudflare D1 docs

The Cloudflare Workers platform supports multiple languages, including TypeScript, JavaScript, Rust and Python.

TTitan is there anyway to perform spatial queries in D1? Considering a switch from Post...

Matt Silverlock•4/2/24, 2:44 PM

Not yet, but looking into supporting: https://vxtwitter.com/elithrar/status/1774819539261255950?s=20

vxTwitter / fixvxTwitter

Matt Silverlock 🐀 (@elithrar)

@ipsumhh @Cloudflare D1 already supports full-text search: you can use the SQLite FTS5 extension out-of-the-box: https://www.sqlite.org/fts5.html

which also has a trigram tokenizer.

e don't yet support R*Tree (https://www.sqlite.org/rtree.html) but are looking into it!

Rrenzor supabase has been GA for half a decade and still doesn't have transactions 😆

Epailes•4/2/24, 2:52 PM

I mean they've got RPC which works fine for transactions.

MMax (@rozenmd)It's from our open beta blog: > Support for more than 100,000+ databases for Wor...

Epailes•4/2/24, 3:04 PM

I'll admit at being disappointed seeing that requirement if you need to go above 50k instances, depending on the use case 50k instances is quite easy to reach (eg something with a freemium model), I wouldn't mind paying for the involved read/writes/storage, but enterprise contracts are pricey and would put me off switching to D1 even when dynamic bindings are available due to that, it's just too much unknown price wise to commit to that kind of vendor lock in.

Epailes•4/2/24, 3:06 PM

But congrats on reaching GA! I know it's been a lot of work getting here and I'm excited to see how D1 continues to grow over the next year :MeowHeartCloudflare:

EEpailes I'll admit at being disappointed seeing that requirement if you need to go above...

Tony Leung•4/2/24, 3:11 PM

Honestly how can you reach 50k db? I'm just curious

rob•4/2/24, 3:12 PM

if 1 user getting their own db , 50k users

talkingarmor•4/2/24, 3:13 PM

Hi, I am looking to migrate from Neon to D1 so that I can bind Pages functions to them directly. My database stores a lot of texts (which is auto compressed by Postgres). Does D1 supports auto compression?

Matt Silverlock•4/2/24, 3:15 PM

FYI: Prisma ORM support for D1 in preview: https://vxtwitter.com/prisma/status/1775170363791425667?s=46

vxTwitter / fixvxTwitter

Prisma (@prisma)

Prisma ORM v5.12.0 just dropped!

Here's what's new:

Cloudflare D1 Support (Preview)

createMany()createMany() for SQLite

https://github.com/prisma/prisma/releases/tag/5.12.0

Rrob if 1 user getting their own db , 50k users

Tony Leung•4/2/24, 3:15 PM

I will not sharding like that tho..., i will shard it by date of birth, date of registration or anything like that (i used to do that in dynamo db to reduce costs), but in another way i usually split the app into many small business logic and create a database for each of them.

Tony Leung•4/2/24, 3:16 PM

Btw while designing the system i will try to store data as much as possible in s3 (sacrifice a bit of convenient) to make the db light and fast

Tony Leung•4/2/24, 3:17 PM

Only metadata are stored and query in db

Tony Leung•4/2/24, 3:17 PM

Might not be the brightest idea but it saves me a lot of money on aws

TTony Leung I will not sharding like that tho..., i will shard it by date of birth, date of ...

Epailes•4/2/24, 3:20 PM

Yeah pretty much what rob said, eg my service has a free tier, so those users are low revenue, but also low read/write/storage, so a traditional monolithic db is generally fine, just need to upgrade the size when necessary. D1 would be significantly better though for auto-scaling and data separation concerns (once dynamic binding and tooling is in place to manage the schema of that many instances).

rob•4/2/24, 3:21 PM

Workers for platforms ups the limits , maybe reach out to someone and see what the requirments are to get that enabled

Epailes•4/2/24, 3:21 PM

There's workarounds I could do to use D1 today, like sharing dbs between users as you said, but I'm using postgres right now and rely on RLS to keep data separate, I'd rather have a guarantee of data separation rather than refactor everything to make sure data is separate (alongside the risk of developer error in the future potentially leaking data), but that's my personal preference.

Epailes•4/2/24, 3:23 PM

It's the kind of thing where once I'd be fine signing up for an ent contract I'll consider it, but by that point it becomes even more work to migrate over to D1 that I'll likely have higher priorities unless I was facing technical issues with my current setup.

forslunds•4/2/24, 4:24 PM

Or skip freemium, i only have 5usd users, Meaning 250k mrr.. im sure i can affiord enterprise once the limit is reached.

.. waiting on dynamic binding

bun•4/2/24, 7:46 PM

Not d1 related much but how do you guys deal with dates those that use drizzle? Simple createdAt column with CURRENT_TIMESTAMP as default doesn’t work for me

bun•4/2/24, 7:47 PM

It gives “Invalid date” upon data retrieval

Bbun It gives “Invalid date” upon data retrieval

mw10013•4/2/24, 8:22 PM

https://orm.drizzle.team/learn/guides/timestamp-default-value

Drizzle ORM - SQL Timestamp as a default value

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Mmw10013 https://orm.drizzle.team/learn/guides/timestamp-default-value

bun•4/2/24, 9:57 PM

ty worked

Village (Josef)•4/3/24, 2:01 AM

Are you able to use the limit request form to get increased per database now? I’m happy with no replicas as a tradeoff, just want to be future proof with more storage.

VVillage (Josef)Are you able to use the limit request form to get increased per database now? I’...

Matt Silverlock•4/3/24, 2:19 AM

All existing databases (if you have a paid Workers plan) can grow to 10GB each - https://developers.cloudflare.com/d1/platform/changelog/#d1-is-generally-available

Cloudflare Docs

Changelog · Cloudflare D1 docs

D1 is now generally available and production ready. Read the blog post for more details on new features in GA and to learn more about the upcoming D1 …

MMatt Silverlock All existing databases (if you have a paid Workers plan) can grow to 10GB each -...

Village (Josef)•4/3/24, 2:20 AM

Are you able to request a limit beyond that or is it still a hard-coded value like in the beta?

VVillage (Josef)Are you able to request a limit beyond that or is it still a hard-coded value li...

Matt Silverlock•4/3/24, 2:22 AM

We don’t currently increase that limit - it’s not that it’s hard coded, it’s that we have a very (very) high bar for performance & cold starts.

We’re continuing to work on raising it over time - and as we do it’ll just work. But also unlikely a single D1 DB will be 100GB (which is a large transactional DB by any standard)

Matt Silverlock•4/3/24, 2:23 AM

Keep in mind that a 10GB database storing user records @ 1KB per row = 10M users.

Village (Josef)•4/3/24, 2:24 AM

That's fair yeah, we're considering a serverless migration at work and our current DB is 108GB (we can't split by user with our use case) so size is the issue

Tony Leung•4/3/24, 4:36 AM

does SQLite View improve query speed? I'm not planning to join view with anything else

Original message was deleted

Ray Hao•4/3/24, 10:51 AM

Same here. I tried specify a region and it works.

Original message was deleted

Matt Silverlock•4/3/24, 11:18 AM

@geelen

Original message was deleted

Matt Silverlock•4/3/24, 11:21 AM

Are you still seeing this? I was able to create a new DB just now.

Original message was deleted

Matt Silverlock•4/3/24, 12:28 PM

OK, looking into it. My new DB was automatic (based on your previous comment).

Tony Leung•4/3/24, 1:37 PM

Hi guys, is there anyway to turn off foreign key check while migrating the database? Some table altering tasks needs table to be dropped, which will cascade referenced tables.

Tony Leung•4/3/24, 1:39 PM

A sample scenario was mentioned in this issue https://github.com/cloudflare/workers-sdk/issues/5438

GitHub

🚀 Feature Request: Allow turning off foreign key check while migrat...

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column t...

TTony Leung Hi guys, is there anyway to turn off foreign key check while migrating the datab...

Vy•4/3/24, 1:44 PM

checkout https://developers.cloudflare.com/d1/build-with-d1/foreign-keys/#defer-foreign-key-constraints

Cloudflare Docs

Define foreign keys · Cloudflare D1 docs

D1 supports defining and enforcing foreign key constraints across tables in a database.

VVy checkout https://developers.cloudflare.com/d1/build-with-d1/foreign-keys/#defer-...

Tony Leung•4/3/24, 1:46 PM

This works with table creating or importing but not for table dropping. Matt also suggested me to bracketing the migration by that pragma but sadly it didn't work

Village (Josef)•4/3/24, 2:23 PM

Got another question, there's still no way to dynamically bind to databases correct? I.e. bind to all 50k DBs from a Worker instead of the ~5k that'll fit in wrangler.toml? I believe you can use the API, but that kind of defeats the purpose.

kian•4/3/24, 2:25 PM

Correct

Original message was deleted

Tony Leung•4/3/24, 2:26 PM

Yeah I think it's the only way to add foreign key or change column type, but whenever I drop the table, all records in tables which reference to the table dropped before are gone too because of cascading on delete.

Tony Leung•4/3/24, 2:30 PM

My current approach is store the child table data to a temp table then insert it again after migrating the parent table. But when my child table also has some more children, the task turns out to be complex and wasteful

Kkian Correct

Village (Josef)•4/3/24, 3:10 PM

Thank you!

is there anyway to perform spatial queries in D1? Considering a switch from Postgres for a new proje

Similar Threads

Similar Threads

Similar Threads