I used PGLite for a project (monkeys.zip) and it was absolutely fantastic! Loved how speedily I could have a "real" postgres instance without spinning up docker. Worked really really well with pnpm workspaces and a monorepo setup (EG pnpm run will run the DB package, as well as frontend and backend).
The only downside (and this applies for SQLite as well) is that it runs too well that you can get some bad habits - or at least follow patterns that don't support horizontal scaling which you would want to do in production. A number of problems across different projects have bit me because I relied too long on SQLite (or PGLite) when moving from local dev to setting up cloud infra. This includes things like connection pooling, read replicas, consistency issues with sharding. Maybe all those people who productionize *Lite have a point!
Also checkout https://www.doltgres.com/
I used PGLite for a project (monkeys.zip) and it was absolutely fantastic! Loved how speedily I could have a "real" postgres instance without spinning up docker. Worked really really well with pnpm workspaces and a monorepo setup (EG pnpm run will run the DB package, as well as frontend and backend).
The only downside (and this applies for SQLite as well) is that it runs too well that you can get some bad habits - or at least follow patterns that don't support horizontal scaling which you would want to do in production. A number of problems across different projects have bit me because I relied too long on SQLite (or PGLite) when moving from local dev to setting up cloud infra. This includes things like connection pooling, read replicas, consistency issues with sharding. Maybe all those people who productionize *Lite have a point!
Pgembed is pglite for native code.