Nuxt+Prisma+Docker for Automatic Database Synchronization (PostgreSQL)

This article introduces a solution for synchronizing databases in Docker. The basic development environment for this article is Nuxt4 + Prisma + Docker. Synchronization occurs after the container starts and the application process is launched. The core logic involves database synchronization scripts and scripts that run during application startup. Nuxt provides a `Server Start` type plugin development hook, making this implementation straightforward. Please refer to other development environments for their respective implementations.

Database migrations are **not executed during the Docker image build phase**; synchronization occurs **after the container starts and the application process is launched**. Nitro startup plugins invoke a custom migration runner, which reads `prisma/migrations/*/migration.sql` from the image and writes to the Prisma-compatible `_prisma_migrations` table.

The key points for reproduction are three-fold:

1.  **Include** `prisma/migrations` (and the runtime Prisma Client artifact `prisma/generated`) in the **image**.
2.  **Implement** `server/lib/db-migrations.ts` (complete code below).
3.  **Call** `ensureDatabaseMigrations()` at the **earliest stage of application startup** (plugin example below).

The `prisma/migrations` directory content is automatically generated by commands like `prisma migrate dev`. Each project's schema differs, so the migration SQL itself is not elaborated upon here.


## Overall Flow

```mermaid
sequenceDiagram
  participant Docker as Docker Build
  participant CMD as node server/index.mjs
  participant Plugin as Nitro Startup Plugin
  participant Mig as db-migrations.ts
  participant PG as PostgreSQL

  Docker->>Docker: pnpm build (includes prisma generate)
  Docker->>Docker: COPY .output + prisma/migrations + prisma/generated
  Note over Docker: No database connection or migration during build

  CMD->>Plugin: Process startup
  Plugin->>Mig: ensureDatabaseMigrations()
  Mig->>PG: CREATE DATABASE if not exists
  Mig->>PG: advisory lock + read/write _prisma_migrations
  Mig->>PG: execute unapplied migration.sql
  Plugin->>CMD: Continue with data seeding and other initialization after migration
```

Compared to "executing `npx prisma migrate deploy` in `entrypoint.sh`," the current **production Dockerfile's CMD directly starts Node**, without relying on Prisma CLI installed within the image. Migration logic is entirely handled within the application using the `pg` driver.

The repository still retains `docker/entrypoint.sh` (Prisma official CLI method), but it is **not hooked into the current Dockerfile's ENTRYPOINT/CMD**, serving only as an optional reference, see the end of the article.

---

## Environment Variables

| Variable | Required | Description |
|----------|----------|-------------|
| `DATABASE_URL` | Yes | Target database connection string, must include database name; `?schema=public` specifies the PostgreSQL schema |
| `DATABASE_AUTO_MIGRATE` | No | Setting to `false` skips automatic migration |
| `DATABASE_BOOTSTRAP_URL` | No | Management connection for `CREATE DATABASE`, defaults to changing the database name in `DATABASE_URL` to `postgres` |
| `DATABASE_SCHEMA` | No | Schema name when not provided in the URL, defaults to `public` |

Compose Example (only showing database-related parts):

```yaml
environment:
  DATABASE_URL: "postgresql://postgres:password@host:5432/dbname?schema=public"
  # DATABASE_AUTO_MIGRATE: "false"   # Can be disabled when migration is performed by external operations
  # DATABASE_BOOTSTRAP_URL: "postgresql://postgres:password@host:5432/postgres"
```

---

## Docker Image: What to Do During Build

The build phase only involves application packaging and copying migration files; **no database connection is made**.

```dockerfile
FROM node:22.21.1-alpine3.22 AS builder
WORKDIR /app
RUN corepack enable
COPY package.json pnpm-lock.yaml ./
RUN pnpm install --frozen-lockfile
COPY . .
RUN pnpm build

FROM node:22.21.1-alpine3.22 AS runner
WORKDIR /app

COPY --from=builder /app/.output ./
COPY --from=builder /app/prisma/generated ./prisma/generated
COPY --from=builder /app/prisma/migrations ./prisma/migrations

ENV DATABASE_URL="postgresql://user:pass@host:5432/dbname?schema=public"
EXPOSE 9090
CMD ["node", "/app/server/index.mjs"]
```

Notes:

- Before `pnpm build`, `prisma generate` must have been executed on the development machine/CI so that `prisma/generated` exists and is copied into the image.
- The runtime working directory is `/app`. The migration runner reads SQL from `process.cwd()/prisma/migrations`, consistent with the COPY path above.
- The final image **does not require** the `prisma` CLI package to be installed.

---

## Startup Plugin: Run Migrations Before Business Initialization

Migrations must be completed before any Prisma queries. This is achieved by `await`ing in a Nitro plugin during startup:

```typescript
import { ensureDatabaseMigrations } from "~~/server/lib/db-migrations";

async function runStartupInitialization() {
  await ensureDatabaseMigrations();
}

export default defineNitroPlugin(async () => {
  await runStartupInitialization();
});
```

Subsequent `prisma.systemConfig.create` calls within the same plugin file belong to **data seeding**, not schema synchronization. When reproducing, only retain the `ensureDatabaseMigrations()` call; seed logic can be deleted or modified based on project needs.

---

## Core: Custom Migration Runner (Complete Code)

Path convention: `server/lib/db-migrations.ts`.

Behavior summary:

1. Reads `prisma/migrations/<name>/migration.sql` sorted by directory name.
2. If the target database does not exist (PostgreSQL `3D000`), it executes `CREATE DATABASE` using the bootstrap connection.
3. Creates/uses the schema and acquires `pg_advisory_lock` to prevent concurrent migrations across multiple instances.
4. Maintains the `_prisma_migrations` table (with fields consistent with Prisma), where the checksum is the SHA-256 hash of the SQL.
5. **Baseline**: If business tables already exist but there's no migration history, and `validateExistingSchemaIsLatest` passes, it only writes migration records without re-executing SQL (see adaptation notes in the next section).
6. For other unapplied migrations, it executes SQL within a transaction and records it.

Dependencies: `pg` (already in `dependencies` in `package.json`).

```typescript
import { createHash, randomUUID } from "node:crypto";
import { access, readdir, readFile } from "node:fs/promises";
import path from "node:path";
import { Client } from "pg";

const MIGRATIONS_DIR = path.resolve(process.cwd(), "prisma", "migrations");
const MIGRATIONS_TABLE = "_prisma_migrations";
const LOCK_KEY_1 = 20260328;
const LOCK_KEY_2 = 1;

type MigrationFile = {
  name: string;
  sql: string;
  checksum: string;
};

type DatabaseTarget = {
  databaseUrl: string;
  adminUrl: string;
  databaseName: string;
  schemaName: string;
};

// List of tables, examples only
const EXPECTED_TABLES = [
  "users",
  "settings",
];

let migrationTask: Promise<void> | null = null;

function getChecksum(sql: string) {
  return createHash("sha256").update(sql).digest("hex");
}

function quoteIdentifier(value: string) {
  return `"${value.replaceAll(`"`, `""`)}"`;
}

function getDatabaseTarget(): DatabaseTarget {
  const databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) {
    throw new Error("DATABASE_URL is required before applying migrations.");
  }

  const parsed = new URL(databaseUrl);
  const databaseName = decodeURIComponent(parsed.pathname.replace(/^\//, ""));
  if (!databaseName) {
    throw new Error("DATABASE_URL must include a database name.");
  }

  const schemaName =
    parsed.searchParams.get("schema") ||
    process.env.DATABASE_SCHEMA ||
    "public";

  const adminUrl =
    process.env.DATABASE_BOOTSTRAP_URL ||
    (() => {
      const bootstrap = new URL(databaseUrl);
      bootstrap.pathname = "/postgres";
      return bootstrap.toString();
    })();

  return {
    databaseUrl,
    adminUrl,
    databaseName,
    schemaName,
  };
}

async function readMigrationFiles(): Promise<MigrationFile[]> {
  try {
    await access(MIGRATIONS_DIR);
  } catch {
    return [];
  }

  const entries = await readdir(MIGRATIONS_DIR, { withFileTypes: true });
  const migrations = await Promise.all(
    entries
      .filter((entry) => entry.isDirectory())
      .sort((a, b) => a.name.localeCompare(b.name))
      .map(async (entry) => {
        const sql = await readFile(
          path.join(MIGRATIONS_DIR, entry.name, "migration.sql"),
          "utf8"
        );

        return {
          name: entry.name,
          sql,
          checksum: getChecksum(sql),
        };
      })
  );

  return migrations;
}

async function ensureMigrationsTable(client: Client) {
  await client.query(`
    CREATE TABLE IF NOT EXISTS "${MIGRATIONS_TABLE}" (
      "id" TEXT PRIMARY KEY,
      "checksum" TEXT NOT NULL,
      "finished_at" TIMESTAMPTZ,
      "migration_name" TEXT NOT NULL UNIQUE,
      "logs" TEXT,
      "rolled_back_at" TIMESTAMPTZ,
      "started_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      "applied_steps_count" INTEGER NOT NULL DEFAULT 0
    );
  `);
}

async function ensureSchema(client: Client, schemaName: string) {
  await client.query(`CREATE SCHEMA IF NOT EXISTS ${quoteIdentifier(schemaName)}`);
  await client.query(`SET search_path TO ${quoteIdentifier(schemaName)}`);
}

async function loadAppliedMigrations(client: Client) {
  const result = await client.query<{
    migration_name: string;
    checksum: string;
  }>(
    `SELECT "migration_name", "checksum"
     FROM "${MIGRATIONS_TABLE}"
     WHERE "rolled_back_at" IS NULL`
  );

  return new Map(
    result.rows.map((row) => [row.migration_name, row.checksum] as const)
  );
}

async function hasUserTables(client: Client) {
  const result = await client.query<{ exists: boolean }>(
    `
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = current_schema()
          AND table_name = ANY($1::text[])
      ) AS "exists"
    `,
    [EXPECTED_TABLES]
  );

  return Boolean(result.rows[0]?.exists);
}

async function validateExistingSchemaIsLatest(client: Client) {
  const tableCountResult = await client.query<{ count: string }>(
    `
      SELECT COUNT(*)::text AS "count"
      FROM information_schema.tables
      WHERE table_schema = current_schema()
        AND table_name = ANY($1::text[])
    `,
    [EXPECTED_TABLES]
  );

  const existingTableCount = Number(tableCountResult.rows[0]?.count ?? 0);
  if (existingTableCount !== EXPECTED_TABLES.length) {
    return false;
  }

  const metaColumnResult = await client.query<{ exists: boolean }>(
    `
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name = 'user_chat_messages'
          AND column_name = 'meta'
      ) AS "exists"
    `
  );

  const removedColumnsResult = await client.query<{
    fund_account_type_exists: boolean;
    flow_pay_type_exists: boolean;
    fixed_flow_pay_type_exists: boolean;
  }>(`
    SELECT
      EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name = 'user_fund_accounts'
          AND column_name = 'accountType'
      ) AS "fund_account_type_exists",
      EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name = 'user_flows'
          AND column_name = 'payType'
      ) AS "flow_pay_type_exists",
      EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name = 'user_fixed_flows'
          AND column_name = 'payType'
      ) AS "fixed_flow_pay_type_exists"
  `);

  const removed = removedColumnsResult.rows[0];

  return (
    Boolean(metaColumnResult.rows[0]?.exists) &&
    !removed?.fund_account_type_exists &&
    !removed?.flow_pay_type_exists &&
    !removed?.fixed_flow_pay_type_exists
  );
}

async function ensureDatabaseExists(target: DatabaseTarget) {
  const probeClient = new Client({
    connectionString: target.databaseUrl,
  });

  try {
    await probeClient.connect();
    await probeClient.end();
    return;
  } catch (error: any) {
    try {
      await probeClient.end();
    } catch {
      // Ignore close errors after failed connect.
    }

    if (error?.code !== "3D000") {
      throw error;
    }
  }

  const adminClient = new Client({
    connectionString: target.adminUrl,
  });

  await adminClient.connect();
  try {
    const existsResult = await adminClient.query<{ exists: boolean }>(
      "SELECT EXISTS (SELECT 1 FROM pg_database WHERE datname = $1) AS exists",
      [target.databaseName]
    );

    if (!existsResult.rows[0]?.exists) {
      await adminClient.query(
        `CREATE DATABASE ${quoteIdentifier(target.databaseName)}`
      );
      console.log(`[db:migrate] created database ${target.databaseName}`);
    }
  } finally {
    await adminClient.end();
  }
}

async function baselineMigrations(
  client: Client,
  migrations: MigrationFile[],
  applied: Map<string, string>
) {
  if (applied.size > 0) {
    return;
  }

  const hasTables = await hasUserTables(client);
  if (!hasTables) {
    return;
  }

  const isLatestSchema = await validateExistingSchemaIsLatest(client);
  if (!isLatestSchema) {
    throw new Error(
      "Existing database schema detected without _prisma_migrations history, and the schema does not match the current expected version. Refusing automatic baseline to avoid duplicate execution or destructive drift."
    );
  }

  await client.query("BEGIN");
  try {
    for (const migration of migrations) {
      await client.query(
        `INSERT INTO "${MIGRATIONS_TABLE}" ("id", "checksum", "finished_at", "migration_name", "started_at", "applied_steps_count") VALUES ($1, $2, NOW(), $3, NOW(), 1)`,
        [randomUUID(), migration.checksum, migration.name]
      );
      applied.set(migration.name, migration.checksum);
    }
    await client.query("COMMIT");
    console.log("[db:migrate] baselined existing schema");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  }
}

async function applyPendingMigrations() {
  const target = getDatabaseTarget();

  const migrations = await readMigrationFiles();
  if (migrations.length < 1) {
    return;
  }

  await ensureDatabaseExists(target);

  const client = new Client({
    connectionString: target.databaseUrl,
  });

  await client.connect();

  try {
    await ensureSchema(client, target.schemaName);
    await client.query("SELECT pg_advisory_lock($1, $2)", [
      LOCK_KEY_1,
      LOCK_KEY_2,
    ]);
    await ensureMigrationsTable(client);

    const applied = await loadAppliedMigrations(client);
    await baselineMigrations(client, migrations, applied);

    for (const migration of migrations) {
      const appliedChecksum = applied.get(migration.name);

      if (appliedChecksum) {
        if (appliedChecksum !== migration.checksum) {
          throw new Error(
            `Migration checksum mismatch: ${migration.name}. Existing databases cannot safely apply a modified migration file.`
          );
        }
        continue;
      }

      await client.query("BEGIN");
      try {
        await client.query(migration.sql);
        await client.query(
          `INSERT INTO "${MIGRATIONS_TABLE}" ("id", "checksum", "finished_at", "migration_name", "started_at", "applied_steps_count") VALUES ($1, $2, NOW(), $3, NOW(), 1)`,
          [randomUUID(), migration.checksum, migration.name]
        );
        await client.query("COMMIT");
        console.log(`[db:migrate] applied ${migration.name}`);
      } catch (error) {
        await client.query("ROLLBACK");
        throw error;
      }
    }
  } finally {
    try {
      await client.query("SELECT pg_advisory_unlock($1, $2)", [
        LOCK_KEY_1,
        LOCK_KEY_2,
      ]);
    } catch {
      // Ignore unlock failures during shutdown/error handling.
    }
    await client.end();
  }
}

export async function ensureDatabaseMigrations() {
  if (process.env.DATABASE_AUTO_MIGRATE === "false") {
    return;
  }

  migrationTask ??= applyPendingMigrations();
  return migrationTask;
}
```

## Optional Solution: Call Prisma CLI in Entrypoint

Utilizing Prisma CLI's `npx prisma migrate deploy` capability for database synchronization. However, this approach requires installing Prisma CLI within the container, significantly increasing image size, and is therefore not recommended.


## Operations and Troubleshooting

| Phenomenon | Possible Cause |
|------------|----------------|
| Startup error `DATABASE_URL is required` | Environment variable not injected |
| `Migration checksum mismatch` | Applied migration file has been modified; a new migration should be created instead of altering historical SQL |
| Baseline execution rejected | Old database schema does not conform to `validateExistingSchemaIsLatest`; manual alignment or `prisma migrate resolve` type processing is required |
| Migrations not found in container | `prisma/migrations` not copied or `cwd` is not `/app` |
| Multiple replicas starting simultaneously | Advisory lock serializes migrations; it is still recommended to start a single instance first during release or set `DATABASE_AUTO_MIGRATE=false` for a Job to execute |

Log prefix: `[db:migrate]`, e.g., `created database`, `baselined existing schema`, `applied <migration_name>`.