Personally my favorite part of using D1 is that the database is managed the same way as everything else, and you just access it through a Workers binding rather than needing any authentication or connection strings or anything. I'm excited to see how the new session API and read replicas work too, since they might be able to reduce DB read latency to being within the same datacenter in many instances. But I only know about as much about the D1 session API and read replicas as anyone else that read the blog post about it.

Disclaimer: I work for Cloudflare, but not on Workers (my team just is a heavy user of Workers). I'm just speaking as a Workers user/enthusiast here.

when D1 moved from alpha to beta they removed the backup feature. Granted it was beta but their support and tooling is quite fragile. Even now they have declared general availbility for D1, but if you try to take backup using their proposed wrangler commands it does not work(there are bugs in handling of dates). You end up wastting lot of time due to this.

D1 is sqlite and supabase is postgresql so they are not exactly comparable but pros/cons of sqlite vs postgres apply here except that sqlite pros of db in process would not apply here since now both the db's have to be connected via wire.