Everyone1 loves SQLite, because it’s simple, fast, and in-process, meaning you don’t need a long-lived service running or, *shudders*, Docker Desktop. It’s common for ORMs to work out-of-the-box with SQLite, so you can start fiddling around before getting a proper database set up. It’s also somewhat common/encouraged to use it in tests, where a “real” database isn’t available.
However, this inevitably runs into problems. Production apps generally use Postgres, and subtle differences in the dialect mean that you have to use Postgres in development too, and try to use it in tests as well. Back in the day, this way lay pain, heartache, special poorly-maintained “testing” databases, a workflow for running tests that required two terminal tabs, or indeed the dreaded Docker Desktop.
Thankfully, these days are now behind us because we now have PGLite. PGLite! It’s like SQLite but Postgres! (but only in javascript/WASM, not as a standalone binary like SQLite).
What does that mean exactly? Postgres normally needs a database service permanently running. In development, you will also need two services, either both on your laptop or (more common) the Postgres instance running on a remote server. PGLite is an in-process version of Postgres, meaning you don’t need this second service, you just import ‘pglite’ in code, and you will have a fully-fledged Postgres instance writing to a local file, rather than a separate docker container
Why is this incredibly useful? Primarily for testing! It means you can easily set up tests that read and write to a dummy database, and the point is that it’s exactly the same as the production behaviour. PGLite is literally a standalone build of the same codebase behind proper-Postgres. Example in a Typescript app with Drizzle ORM:
What else is it (potentially) useful for:
As a go-to quick-setup database when starting a new project, similar to SQLite. It saves having to do any migration to Postgres later.
As an in-browser database, for doing non-trivial data operations on the client. This is actually the use case it was developed for, to plug into the ElectricSQL local-first2 framework.
Not crazy, but haven’t tried it: As an actual production database for small apps. This would save a lot of round trips and the cost of hosting a permanent database.