Support pglite as database

[nileshtrivedi]

I see support for sqlite is currently being implemented.

Before too many assumptions about sqlite get baked in, I'd like to request support for pglite too. It's an embeddable postgres (via WASM) that runs in Node, Bun, and Deno. For persistence, it can use Node's Filesystem APIs (which also work in Bun and Deno). Many ORMs, like Prisma and Drizzle have adapters for it.

Here is why I'd prefer using pglite over sqlite:

• Postgres simply has many more features like:
  • More expressive data types and therefore better data integrity
  • Row-level security (RLS) and Transaction-scoped settings
  • Full text search (FTS)
  • Foreign data wrappers (FDW)
  • LISTEN / NOTIFY
• There is a much bigger ecosystem of postgres extensions (and pglite already supports many of them) such as pgvector or postgis
• Many existing open-source apps require postgres as database

I think pglite makes a better choice than sqlite for storage of structured data. Having support for it would give Rivet a strong advantage over Cloudflare's DurableObjects.

[linear]

RVT-5262