Database

Overview

FinOpenPOS uses PGLite — full PostgreSQL running as WASM inside the Node.js process. No external database server required. Data is stored on the filesystem at apps/web/data/pglite.

The ORM is Drizzle, providing type-safe SQL queries and automatic schema management via drizzle-kit push.

Schema

See Fiscal Database Schema for the fiscal-specific tables (fiscalSettings, invoices, invoiceItems, invoiceEvents, cities).

Conventions

PGLite (Default)

PGLite runs full PostgreSQL via WASM directly in the Node.js process.

Pros:

• Zero configuration — no PostgreSQL server needed
• No external dependencies — works immediately after bun install
• Ideal for development and small deployments

Limitations:

• Single-process (no external concurrent connections)
• Lower performance than native PostgreSQL under heavy load
• No replication

The PGLite singleton lives at src/lib/db/index.ts using a globalThis trick to survive HMR in development.

Seeding

On the first bun run dev, the database is seeded with:

• ~5570 IBGE cities (fetched from the IBGE API)
• Default payment methods
• Demo data: 20 customers, 32 products, 40 orders, 25 transactions

Migrating to PostgreSQL

When the project needs a real database, migration is straightforward because Drizzle ORM abstracts the data access layer — the schema is identical.

Automatic Migration

cd apps/web && bun run prepare-prod

Then set DATABASE_URL in your apps/web/.env file and push the schema:

cd apps/web && bun run db:push
cd apps/web && bun run dev

Manual Migration

1. Install the PostgreSQL driver

bun add pg
bun remove @electric-sql/pglite

2. Update apps/web/src/lib/db/index.ts

import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

export const db = drizzle(process.env.DATABASE_URL!, { schema });

3. Update apps/web/drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/lib/db/schema.ts",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

4. Add the env variable

DATABASE_URL=postgresql://user:password@host:5432/finopenpos

5. Push schema and run

cd apps/web && bun run db:push
bun run dev

6. Clean up

• Delete scripts/ensure-db.ts (only exists for PGLite recovery)
• Remove db:ensure from dev and build scripts in package.json
• Remove serverExternalPackages from next.config.mjs
• In Docker, replace the PGLite volume with a PostgreSQL connection via DATABASE_URL