On top of that the docs say it's designed for per-user/tenant use cases, while they elsewhere say those use cases aren't ready yet.
Also, afaik they still have a 10,000 database limit on paid plans. Maybe you can get that lifted if you have a personal relationship with them, but even very low scale apps would quickly hit that.
IMO D1 (hosted SQLite in general) just seems like an interesting distraction. The per-tenant use-case has some pros; security being the biggest one. But I've basically never worked on an app where that boundary was so strict, across 100% of query use-cases, that I'd be willing to make such a critical infrastructure decision to support it. Even something as simple as a business analytics dashboard starts to break down that boundary, and would require replication of the data to another database (which is something many apps do anyway, but you can get a lot of mileage out of just directing BI queries at your normal database).
The second-best thing I can say about it is that it might have an interesting cost profile, but it seems like the situations where that would surface as an advantage is quite small. Mostly just low-scale, low-cost applications. Yeah, the total iterative cost of some low-utilization tenant might literally be $0; but assuming you have some money coming in, every other database can support this for an iterative cost of like $0.10, and you don't have to deal with all the downsides of D1 and SQLite.