Local-first client database migrations

Local database migrations is a required feature for all local-first apps. This example shows a strategy on how with Pglite using Drizzle and Effect.

Sandro Maglione

Contact me

655 words

・

Local-first apps are becoming easier and easier to implement. One key feature that is required in all local app is client database migrations.

This article shows how to implement local database migrations with Pglite, Drizzle, and Effect.

Drizzle configuration

Drizzle is used as ORM:

drizzle-orm to build and execute queries
drizzle-kit to generate migrations (.sql)

pnpm add drizzle-orm

pnpm add -D drizzle-kit

We first add drizzle.config.ts in the root of the project. This configuration defines:

Reference to the database definition file (schema)
Output directory where to generate the migration files (out)

Make sure to specify postgresql as dialect, since Pglite uses postgres as database

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db.ts",
  out: "./src/drizzle",
  dialect: "postgresql",
});

In the file referenced by schema define the database tables using drizzle-orm.

Again, make sure to use the postgres dialect of drizzle (pg-core)

This configuration requires a systemTable to keep track of the current version of the local database. This information is used to execute migration only when the local database version is not inline with the latest migration:

src/db.ts

import { integer, pgTable, varchar } from "drizzle-orm/pg-core";

export const activityTable = pgTable("activity", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar().notNull(),
});

// 👇 Required table to execute migrations
export const systemTable = pgTable("system", {
  version: integer().notNull().default(0),
});

Migrations are generated by executing drizzle-kit. Let's add the generate command to scripts:

package.json

{
  // ...
  "scripts": {
    "typecheck": "tsc --noEmit",
    "dev": "vite --port=3001",
    "build": "vite build",
    "serve": "vite preview",
    "start": "vite",
    "generate": "pnpm drizzle-kit generate"
  },
  // ...
}

Running pnpm run generate will create the migrations based on db.ts inside src/drizzle (as specified inside drizzle.config.ts).

Vite configuration

We need a couple of adjustments to the vite configuration to make Pglite and .sql files import working.

First, inside vite.config.ts we need to include .sql files (assetsInclude). We also need to exclude Pglite from optimizeDeps:

vite.config.ts

import { TanStackRouterVite } from "@tanstack/router-plugin/vite";
import react from "@vitejs/plugin-react";
import { defineConfig } from "vite";

export default defineConfig({
  assetsInclude: ["./src/drizzle/*.sql"],
  plugins: [TanStackRouterVite({}), react()],
  optimizeDeps: { exclude: ["@electric-sql/pglite"] },
});

Second, since we need to import raw .sql files, we need to include types from vite/client to avoid TypeScript missing reference issues:

tsconfig.json

{
  "compilerOptions": {
    "strict": true,
    "esModuleInterop": true,
    "jsx": "react-jsx",
    "target": "ESNext",
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "types": ["vite/client"]
  }
}

Effect configuration

The implementation uses effect to create, organize, and execute services. We also install Pglite core (@electric-sql/pglite) and the Pglite plugin for React (@electric-sql/pglite-react):

pnpm add effect @electric-sql/pglite @electric-sql/pglite-react

The first service allows executing type-safe queries with Pglite. The database is stored inside IndexedDB. We also include drizzle-orm to build queries:

pglite.ts

import * as _PGlite from "@electric-sql/pglite";
import { live } from "@electric-sql/pglite/live";
import { drizzle } from "drizzle-orm/pglite";
import { Config, Data, Effect } from "effect";

class PgliteError extends Data.TaggedError("PgliteError")<{
  cause: unknown;
}> {}

export class Pglite extends Effect.Service<Pglite>()("Pglite", {
  effect: Effect.gen(function* () {
    // IndexedDB source using `effect` configuration
    const indexDb = yield* Config.string("INDEX_DB");

    const client = yield* Effect.tryPromise({
      try: () => _PGlite.PGlite.create(`idb://${indexDb}`),
      catch: (error) => new PgliteError({ cause: error }),
    });

    // Include `drizzle-orm` query builder
    const orm = drizzle({ client });

    // 👇 Export type-safe query execution
    const query = <R>(execute: (_: typeof orm) => Promise<R>) =>
      Effect.tryPromise({
        try: () => execute(orm),
        catch: (error) => new PgliteError({ cause: error }),
      });

    return { client, orm, query };
  }),
}) {}

Migrations require executing .sql files generated by drizzle-kit. We create a Migrations service that exports an array of migrations. Each index in the array represents a migration's version.

Make sure to import each migration as raw sql files (.sql?raw).

Include the migrations in order in the exported array.

migrations.ts

import v0000 from "../../drizzle/0000_material_george_stacy.sql?raw";
import v0001 from "../../drizzle/0001_absurd_mandrill.sql?raw";

import type { PGlite } from "@electric-sql/pglite";
import { Data, Effect } from "effect";
import { Pglite } from "./pglite";

class MigrationsError extends Data.TaggedError("MigrationsError")<{
  cause: unknown;
}> {}

const execute = (client: PGlite) => (sql: string) =>
  Effect.tryPromise({
    try: () => client.exec(sql),
    catch: (error) => new MigrationsError({ cause: error }),
  });

export class Migrations extends Effect.Service<Migrations>()("Migrations", {
  dependencies: [Pglite.Default],
  effect: Effect.gen(function* () {
    const db = yield* Pglite;
    const migrate = execute(db.client);

    // 👇 Ordered array of migrations, each index a new version of the database
    return [migrate(v0000), migrate(v0001)] as const;
  }),
}) {}

That's all we need. The last step is creating a Runtime that includes all the services and the configuration. The runtime will be used to execute the final effect:

runtime-client.ts

import { ConfigProvider, Layer, ManagedRuntime } from "effect";
import { Migrations } from "./services/migrations";
import { Pglite } from "./services/pglite";

// Provide `Config` values (IndexedDB)
const CustomConfigProvider = Layer.setConfigProvider(
  ConfigProvider.fromMap(new Map([["INDEX_DB", "v1"]]))
);

const MainLayer = Layer.mergeAll(Migrations.Default, Pglite.Default).pipe(
  Layer.provide(CustomConfigProvider)
);

export const RuntimeClient = ManagedRuntime.make(MainLayer);

Execute migration

The migration must be executed before everything else in the app. This makes sure that the database is loaded and up-to-date before the app is displayed.

The best place to execute migrations is inside a client loader.

Make sure the loader executes on the client. The migrations act on a local-database using IndexedDB, therefore a server loader won't work!

The example below shows how to implement this using TanStack Router. You can really use any framework, or also plain React.

The migrations are executed inside the root loader (__root.tsx). This makes sure that the migrations are applied in all pages:

routes/__root.tsx

export const Route = createRootRoute({
  component: RootComponent,
  loader: () =>
    RuntimeClient.runPromise(
      Effect.gen(function* () {
        // Execute migration steps
      }).pipe(
        Effect.tapErrorCause(Effect.logError) // 👈 Log any error
      )
    ),
  pendingComponent: () => <div>Loading...</div>,
  errorComponent: (error) => <pre>{JSON.stringify(error, null, 2)}</pre>,
});

function RootComponent() {
  return (
    <>
      <Outlet />
      <TanStackRouterDevtools position="bottom-right" />
    </>
  );
}

The migration includes the following steps:

Load the current local database version from the systemTable (or 0 if the client doesn't include any database yet)
Execute all the migrations in the migration array (in order), starting from the current system version up to the latest version. This step includes the initial database creation if no database exists yet
Update the current local database version to the latest migration (migrations.length), or insert the initial database version if no row is yet inserted

routes/__root.tsx

export const Route = createRootRoute({
  component: RootComponent,
  loader: () =>
    RuntimeClient.runPromise(
      Effect.gen(function* () {
        const migrations = yield* Migrations;
        const { query } = yield* Pglite;

        const latestVersion = migrations.length;

        // 1️⃣ Load the current local database version
        const { version } = yield* query((_) =>
          _.select().from(systemTable).limit(1)
        ).pipe(
          Effect.flatMap(Array.head),
          Effect.catchTags({
            // ...or `0` if the client doesn't include any database yet
            PgliteError: () => Effect.succeed({ version: 0 }),
          })
        );

        // 2️⃣ Execute all the migrations in the migration array in order
        yield* Effect.all(migrations.slice(version));

        if (version === 0) {
          // 3️⃣ Insert the initial database version
          yield* query((_) => _.insert(systemTable).values({ version: 0 }));
        }

        // Update the current local database version to the latest migration
        yield* query((_) => _.update(systemTable).set({ version: latestVersion }));

        yield* Effect.log(
          version === latestVersion
            ? "Database up to date"
            : `Migrations done (from ${version} to ${latestVersion})`
        );

        return latestVersion;
      }).pipe(Effect.tapErrorCause(Effect.logError))
    ),
  pendingComponent: () => <div>Loading...</div>,
  errorComponent: (error) => <pre>{JSON.stringify(error, null, 2)}</pre>,
});

The final implementation looks as follows:

routes/__root.tsx

import { Outlet, createRootRoute } from "@tanstack/react-router";
import { TanStackRouterDevtools } from "@tanstack/router-devtools";
import { Array, Effect } from "effect";
import { systemTable } from "../db";
import { RuntimeClient } from "../lib/runtime-client";
import { Migrations } from "../lib/services/migrations";
import { Pglite } from "../lib/services/pglite";

export const Route = createRootRoute({
  component: RootComponent,
  loader: () =>
    RuntimeClient.runPromise(
      Effect.gen(function* () {
        const migrations = yield* Migrations;
        const { query } = yield* Pglite;

        const latestVersion = migrations.length;

        const { version } = yield* query((_) =>
          _.select().from(systemTable).limit(1)
        ).pipe(
          Effect.flatMap(Array.head),
          Effect.catchTags({
            PgliteError: () => Effect.succeed({ version: 0 }),
          })
        );

        yield* Effect.all(migrations.slice(version));

        if (version === 0) {
          yield* query((_) => _.insert(systemTable).values({ version: 0 }));
        }

        yield* query((_) => _.update(systemTable).set({ version: latestVersion }));

        yield* Effect.log(
          version === latestVersion
            ? "Database up to date"
            : `Migrations done (from ${version} to ${latestVersion})`
        );

        return latestVersion;
      }).pipe(Effect.tapErrorCause(Effect.logError))
    ),
  pendingComponent: () => <div>Loading...</div>,
  errorComponent: (error) => <pre>{JSON.stringify(error, null, 2)}</pre>,
});

function RootComponent() {
  return (
    <>
      <Outlet />
      <TanStackRouterDevtools position="bottom-right" />
    </>
  );
}

Now every time you run the app the migrations will be executed before all pages. The console logs the executed migration, or "Database up to date" if all no migration is needed.

In case of errors, the implemention will also log any error in the console (Effect.logError).

You can check out a full local-first app using this migrations' setup at Local-only calories tracker app

This implementation is missing the following features:

Handle failed operations: reverse all previous migrations if the process fails at any step
Handle data migrations: the current implementation only applies database schema migrations, but it doesn't consider data migrations