Postgres on Angular with PGlite

PGlite + Angular Signals offers an intriguing persistence and state-management solution, especially for PWAs requiring offline capabilities. When Postgres is already in the backend stack, this combo provides unified queries and seamless sync with remote Postgres.

Getting PGlite to work with Angular took some time, so I thought of documenting a minimal demo.

1. Create a demo app and install @electric-sql/pglite

npm install -g @angular/cli@latest
NG_PROJECT_NAME=${NG_PROJECT_NAME:-"ng-pglite-demo"}
ng new --ai-config=none --inline-style --inline-template --routing \
  --ssr=false --style=scss --zoneless $NG_PROJECT_NAME
cd $NG_PROJECT_NAME
npm install @electric-sql/pglite

2. Update angular.json (with jq like below) or set the values manually

cp angular.json angular.json.bak

jq --arg project_name "$NG_PROJECT_NAME" \
  '(.projects[$project_name].architect.build.configurations.development.externalDependencies += ["util"]) | 
   (.projects[$project_name].architect.build.configurations.production.externalDependencies += ["util"]) | 
   (.projects[$project_name].architect.build.options.allowedCommonJsDependencies += ["@electric-sql/pglite"]) | 
   (.projects[$project_name].architect.build.options.assets += [{"glob": "**/*", "input": "node_modules/@electric-sql/pglite/dist", "output": "/"}]) | 
   (.projects[$project_name].architect.serve.options.prebundle) = false' \
  angular.json > tmp.json && mv tmp.json angular.json

3. Create an Angular Service, say PGliteService in src/app/pglite.service.ts

import { Injectable, signal, DestroyRef, inject } from '@angular/core';
import { PGlite } from '@electric-sql/pglite';

@Injectable({ providedIn: 'root' })
export class PGliteService {
  private readonly destroyRef = inject(DestroyRef);
  private db: PGlite | null = null;
  private initPromise: Promise<void> | null = null;
  readonly version = signal<string | null>(null);

  async init(dbFile?: string, initdbSQL?: string): Promise<void> {
    if (this.initPromise) return this.initPromise;
    if (this.db) return;

    return this.initPromise = (async () => {
      try {
        dbFile?.trim() ? this.db = new PGlite(dbFile) : this.db = new PGlite();
        const result = await this.db.query<{ version: string }>(
          'SELECT version()',
        )
        this.version.set(result.rows[0].version);
        if (initdbSQL?.trim()) {
          await this.db.exec(initdbSQL);
        }
        this.destroyRef.onDestroy(() => this.cleanup());
      } catch (error) {
        console.error('Database initialization failed:', error);
        this.cleanup();
        throw error;
      }
    })();
  }

  async query<T = any>(sql: string, params?: any[]): Promise<{ rows: T[] }> {
    return this.DB().query<T>(sql, params);
  }

  async exec(sql: string): Promise<void> {
    await this.DB().exec(sql);
  }

  private cleanup(): void {
    if (this.db) {
      this.db.close();
      this.db = null;
      this.version.set(null);
      this.initPromise = null;
    }
  }

  // expose the pglite db instance (prefer not. query, exec mostly suffice)
  DB(): PGlite {
    if (!this.version() || !this.db) {
      throw new Error('Database not initialized. Call init() first.');
    }
    return this.db;
  }
}

4. Initialize PGliteService in src/app/app.config.ts; this could slow down app startup. Can also be lazily initialized by running inject(PGliteService).init(/* optional args */) in components.

import { ApplicationConfig, inject, provideAppInitializer, provideBrowserGlobalErrorListeners, provideZonelessChangeDetection } from '@angular/core';
import { provideRouter } from '@angular/router';
import { routes } from './app.routes';
import { PGliteService } from './pglite.service';

export const appConfig: ApplicationConfig = {
  providers: [
    provideBrowserGlobalErrorListeners(),
    provideZonelessChangeDetection(),
    provideRouter(routes),
    provideAppInitializer(() => inject(PGliteService).init(/* IndexedDB path. null/empty for in-memory db */ 'idb://my-pgdata', /* optional initdb sql */)),
  ]
};

5. Use PGlite in components eg src/app/app.ts

import { Component, inject, signal } from '@angular/core';
import { CommonModule } from '@angular/common';
import { PGliteService } from './pglite.service';

interface Item {
  id: number;
  name: string;
}

@Component({
  selector: 'app-root',
  imports: [CommonModule],
  template: `
    <input [value]="newName()" (input)="newName.set($any($event.target).value)" placeholder="enter name"> &nbsp;
    <button (click)="insert()">insert item</button>

    <table border="1">
      <thead>
        <tr><th>ID</th><th>Name</th><th>Actions</th></tr>
      </thead>
      <tbody>
        @for (item of items(); track item.id) {
          <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.name }}</td>
            <td>
              <button (click)="update(item.id)">update</button> &nbsp;
              <button (click)="delete(item.id)">delete</button>
            </td>
          </tr>
        }
      </tbody>
    </table>

    <hr>
    {{ version() }}
  `,
  styles: ``,
})
export class App {
  private pglite = inject(PGliteService);
  version = this.pglite.version;
  items = signal<Item[]>([]);
  newName = signal('');

  async ngOnInit() {
    try {
      await this.pglite.exec(`
        CREATE TABLE IF NOT EXISTS items (
          id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
          name TEXT NOT NULL
        )`);
      await this.selectAll();
    } catch (err) {
      console.error(err);
    }
  }

  async insert() {
    const name = this.newName().trim();
    if (!name) return;
    await this.pglite.query(
      `INSERT INTO items (name) VALUES ($1)`,
      [name]
    );

    this.newName.set('');
    await this.selectAll();
  }

  async update(id: number) {
    await this.pglite.query(`UPDATE items SET name = name || '_updated' WHERE id = $1`, [id]);
    await this.selectAll();
  }

  async delete(id: number) {
    await this.pglite.query(`DELETE FROM items WHERE id = $1`, [id]);
    await this.selectAll();
  }

  async selectAll() {
    const result = await this.pglite.query<Item>(`SELECT * FROM items ORDER BY id`);
    this.items.set(result.rows);
  }
}

4. Verify

ng serve

Visit http://localhost:4200 and try some INSERT, UPDATE and DELETE operations. Then hard-refresh (Cmd/Ctrl + R) the browser and notice data being persisted.