Database boot and migrations

Since we are working with a local database, we need to initialize the database on each client before we can use it.

Furthermore, we also need a strategy to handle schema migrations. Since the database is local, we are required to keep the full history of schema changes and apply them to the database on each client.

Every client could run a different version of the database, therefore is necessary to include a way to upgrade from any previous version to the current one.

We achieve this using drizzle-kit and vite.

Migrations service

We include database booting (create initial tables) and migrations (upgrade database schema) in a single service:

import { Effect } from "effect";
import { Pglite } from "./pglite";

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

Pglite allows executing raw SQL queries with exec. This allows to run the drizzle-kit migrations defined as .sql files.

Since we are using vite we can import sql files as strings and execute them using exec.

We first need to inform vite to include .sql files as assets using assetsInclude:

export default defineConfig({
  assetsInclude: ["./src/drizzle/*.sql"],
  // ...
});

Then we can use import with the ?raw suffix to import .sql files as strings:

// 👇 Import drizzle migrations (check the name of the generated files)
import v0000 from "../drizzle/0000_thin_wilson_fisk.sql?raw";

Executing migrations

With each change in the database drizzle-kit generates a new migration file.

We execute each migration files in order when performing a migration. We first define an execute helper function that runs exec:

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

Inside Migrations we then export an array of migrations. Each entry in the array applies the migration at the same index:

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

    // 👇 Execute migrations in order (append new migrations to the array when needed)
    return [execute(db.client)(v0000)] as const;
  }),
}) {}

When the app loads, we check the current version of the database and run the migrations until the latest version, applying each migration in the array in order.

We are going to see how to do that later when implementing loaders.