PGlite allows you to run a full Postgres database locally (especially in a browser) using WASM, with support for reactivity and live sync.

• This guide focuses on using PGlite with Drizzle ORM and Next.js.

• Official documentation:

  • PGlite Official Docs. PGlite's Drizzle integration guide. You can also explore other supported ORMs. The project is available on Github.
  • Drizzle Official Docs. Drizzle's PGlite integration guide.

• If you cannot use drizzle-kit to generate IF NOT EXISTS with CREATE TABLE, this is expected since they removed it in v3.0.0. Here's a workaround to add it back (ref):

  • We want to use the SQL commands generated by Drizzle without rewriting them.

  • drizzle.config.ts

  • Script scripts/export-db-migrations.ts generates an export.json file where we replace CREATE TABLE statements with CREATE TABLE IF NOT EXISTS.

  • db.ts

  • In your app page.ts

  • Modify the command in package.json. You need to install yarn add -D tsx.

    {
    	"scripts": {
    		"db:generate": "drizzle-kit generate && tsx scripts/export-db-migrations.ts"
    	}
    }
    

    Now, you just need to run yarn run db:generate.

• Drizzle Studio is not compatible with IndexedDB as a database source. This limitation is not documented in the current versions (drizzle-orm@0.38.4, drizzle-kit@0.30.2)

• Use Multi-tab worker to ensure only one PGlite instance runs across multiple browser tabs.

  • db/pglite.worker.ts

  • db/db.ts

  • Use .create() instead of the constructor to ensure proper typing for any extensions added to PGliteWorker

    // instead of
    const pg = new PGliteWorker()
    
    // use
    const pg = await PGliteWorker.create()
    

• Using PGlite React Hooks ← read the official docs first.

  • If you use Multi-tab worker, the extension live must be placed in the PGliteWorker() definition rather than inside the PGlite() definition.
  • When using Drizzle ORM to wrap the PGlite instance (e.g., const db = drizzle({ client: pg })), you need to pass the PGlite instance to <PGliteProvider using db={pg}, not the Drizzle-wrapped db.
  • Complete Setup Example