sailfork / t3code-github / .repos/ effect/ packages/ sql/ pg/ src/ PgMigrator.ts

/**
 * Runs database migrations for PostgreSQL projects that use Effect SQL.
 *
 * This module reuses the shared SQL migrator and connects it to PostgreSQL. It
 * exposes the common migration helpers and adds `run` and `layer` functions
 * that apply pending migration files with the current SQL client. When schema
 * dumps are requested, it uses `pg_dump` and the usual process and filesystem
 * services.
 *
 * @stability unstable
 * @since 4.0.0
 */
import * as Duration from "effect/Duration"
import * as Effect from "effect/Effect"
import * as FileSystem from "effect/FileSystem"
import * as Layer from "effect/Layer"
import * as Path from "effect/Path"
import * as ChildProcess from "effect/process/ChildProcess"
import * as ChildProcessSpawner from "effect/process/ChildProcessSpawner"
import * as Migrator from "effect/sql/Migrator"
import type { SqlClient } from "effect/sql/SqlClient"
import type { SqlError } from "effect/sql/SqlError"
import { Buffer } from "node:buffer"
import * as NodeFs from "node:fs/promises"
import * as Os from "node:os"
import * as NodePath from "node:path"
import * as Tls from "node:tls"
import { resolveConfig, type ResolvedConfig } from "./internal/config.ts"
import * as Password from "./internal/password.ts"
import { PgClient, type PgClientConfig } from "./PgClient.ts"

/**
 * @since 4.0.0
 */
export * from "effect/sql/Migrator"
export {
  /**
   * @stability unstable
   * @category loaders
   * @since 4.0.0
   */
  fromFileSystem
} from "effect/sql/MigratorFileSystem"

/**
 * Runs PostgreSQL SQL migrations using the configured clients. Schema dumps use `pg_dump` and require child process, filesystem, and path services.
 *
 * **Details**
 *
 * `pg_dump` connects with the same resolved settings as `PgClient`: URL and
 * explicit fields, Unix socket path, user, database, `application_name`,
 * `options`, and connect timeout. The password is passed through `PGPASSWORD`
 * and the inherited environment is not used.
 *
 * TLS maps to libpq as follows: no TLS uses `sslmode=disable`,
 * `rejectUnauthorized: false` uses `require`, and verified TLS uses
 * `verify-full` with the client's trusted CAs (`ssl.ca`, or Node's default CAs)
 * written to a temporary `sslrootcert` file. A PEM `ssl.cert` / `ssl.key` pair
 * is passed as `sslcert` / `sslkey`.
 *
 * URL `sslmode=prefer` / `allow` also uses `verify-full`, because the client
 * verifies the certificate whenever the server accepts TLS and libpq cannot
 * verify while keeping a plaintext fallback. Dumps from servers that refuse TLS
 * therefore fail, even though the client falls back to plaintext.
 *
 * Node's default CAs require `tls.getCACertificates` (Node.js 22.15 / 23.10 or
 * later). On older runtimes, verified TLS dumps require an explicit `ssl.ca`.
 *
 * The dump fails, and the migrator logs the error, for custom `stream`
 * connections, TLS over Unix sockets, socket paths not named
 * `.s.PGSQL.<port>`, a `servername` different from the host, and other `ssl`
 * options.
 *
 * @stability unstable
 * @category running
 * @since 4.0.0
 */
export const run: <R2 = never>(
  options: Migrator.MigratorOptions<R2>
) => Effect.Effect<
  ReadonlyArray<readonly [id: number, name: string]>,
  Migrator.MigrationError | SqlError,
  | SqlClient
  | PgClient
  | ChildProcessSpawner.ChildProcessSpawner
  | FileSystem.FileSystem
  | Path.Path
  | R2
> = Migrator.make({
  dumpSchema(path, table) {
    const pgDump = Effect.fnUntraced(function*(connection: DumpConnection, args: Array<string>) {
      const spawner = yield* ChildProcessSpawner.ChildProcessSpawner
      const dump = yield* ChildProcess.make("pg_dump", [
        ...args,
        "--no-owner",
        "--no-privileges",
        "--no-password",
        `--dbname=${connection.conninfo}`
      ], {
        env: { PATH: process.env.PATH, PGPASSWORD: connection.password },
        extendEnv: false
      }).pipe(spawner.string)

      return dump.replace(/^--.*$/gm, "")
        .replace(/^SET .*$/gm, "")
        .replace(/^SELECT pg_catalog\..*$/gm, "")
        .replace(/\n{2,}/gm, "\n\n")
        .trim()
    })

    const pgDumpAll = Effect.gen(function*() {
      const sql = yield* PgClient
      const connection = yield* dumpConnection(sql.config)
      const [schema, migrations] = yield* Effect.all([
        pgDump(connection, ["--schema-only"]),
        pgDump(connection, ["--column-inserts", "--data-only", `--table=${table}`])
      ], { concurrency: 2 })
      return schema + "\n\n" + migrations
    }).pipe(Effect.scoped)

    const pgDumpFile = Effect.fnUntraced(
      function*(path: string) {
        const fs = yield* FileSystem.FileSystem
        const path_ = yield* Path.Path
        const dump = yield* pgDumpAll
        yield* fs.makeDirectory(path_.dirname(path), { recursive: true })
        yield* fs.writeFileString(path, dump)
      },
      Effect.mapError((error) => new Migrator.MigrationError({ kind: "Failed", message: error.message, cause: error }))
    )

    return pgDumpFile(path)
  }
})

interface DumpConnection {
  readonly conninfo: string
  readonly password: string | undefined
}

const unsupported = (reason: string) =>
  Effect.fail(
    new Migrator.MigrationError({
      kind: "Failed",
      message: `pg_dump cannot connect the same way as PgClient: ${reason}`
    })
  )

// Builds the libpq connection for pg_dump from the client's resolved config.
// The password is passed through PGPASSWORD, never in argv.
const dumpConnection = Effect.fnUntraced(function*(options: PgClientConfig) {
  const config = yield* resolveConfig(options)
  if (config.stream !== undefined) {
    return yield* unsupported("custom `stream` connections have no libpq equivalent")
  }
  let host = config.host
  let port = config.port
  if (config.path !== undefined) {
    // libpq takes the socket directory and derives the file name from the port
    const socket = /^(.*)\/\.s\.PGSQL\.(\d+)$/.exec(config.path)
    if (socket === null) {
      return yield* unsupported(`the socket path "${config.path}" is not named .s.PGSQL.<port>`)
    }
    host = socket[1] || "/"
    port = Number(socket[2])
  }
  const startup = config.startupParameters
  const connectTimeout = Duration.isFinite(config.connectTimeout)
    ? String(Math.max(1, Math.ceil(Duration.toSeconds(config.connectTimeout))))
    : undefined
  return {
    conninfo: conninfo({
      host,
      port: String(port),
      user: config.username,
      dbname: startup.database,
      application_name: startup.application_name,
      options: startup.options,
      connect_timeout: connectTimeout,
      ...yield* tlsParameters(config)
    }),
    password: yield* Password.resolve(config.password)
  } satisfies DumpConnection
})

const conninfo = (parameters: Record<string, string | undefined>): string =>
  Object.entries(parameters)
    .flatMap(([key, value]) => value === undefined ? [] : [`${key}='${value.replace(/[\\']/g, "\\

Loading Sailfork

amp;")}'`]) .join(" ") const supportedTlsOptions = new Set(["ca", "cert", "key", "rejectUnauthorized", "servername"]) // Maps the client's TLS behaviour to libpq. Certificates are written to a // scoped temporary directory because libpq only reads them from files. Unused // file parameters point at missing files inside that directory so libpq does // not fall back to ~/.postgresql. const tlsParameters = Effect.fnUntraced(function*(config: ResolvedConfig) { if (config.ssl === false) return { sslmode: "disable" } if (config.path !== undefined) { // The server refuses TLS on Unix sockets, so optional TLS is plaintext if (config.sslOptional) return { sslmode: "disable" } return yield* unsupported("libpq does not use TLS over Unix sockets") } const ssl: Tls.ConnectionOptions = typeof config.ssl === "object" ? config.ssl : {} const options = Object.keys(ssl).filter((key) => (ssl as Record<string, unknown>)[key] !== undefined && !supportedTlsOptions.has(key) ) if (options.length > 0) { return yield* unsupported(`the ssl options ${options.join(", ")} have no libpq equivalent`) } if (ssl.servername !== undefined && ssl.servername !== config.host) { return yield* unsupported("libpq verifies the host, not a separate ssl servername") } if ((ssl.cert === undefined) !== (ssl.key === undefined)) { return yield* unsupported("ssl cert and key must be set together") } if (ssl.key !== undefined && typeof ssl.key !== "string" && !(ssl.key instanceof Uint8Array)) { return yield* unsupported("ssl key must be a single PEM string or Buffer") } // Optional TLS still verifies, and libpq cannot verify with a plaintext fallback const verify = ssl.rejectUnauthorized !== false if (verify && ssl.ca === undefined && typeof Tls.getCACertificates !== "function") { return yield* unsupported( "this Node.js version cannot report its default CAs (tls.getCACertificates needs Node.js 22.15 / 23.10), set ssl.ca" ) } const directory = yield* Effect.acquireRelease( Effect.tryPromise({ try: () => NodeFs.mkdtemp(NodePath.join(Os.tmpdir(), "effect-sql-pg-dump-")), catch: (cause) => new Migrator.MigrationError({ kind: "Failed", message: "Could not create pg_dump TLS directory", cause }) }), (directory) => Effect.promise(() => NodeFs.rm(directory, { recursive: true, force: true })) ) // Without a PEM the parameter points at a missing file, so libpq does not // fall back to ~/.postgresql const file = (name: string, pem: Pem | undefined) => { const path = NodePath.join(directory, pem === undefined ? "none" : name) if (pem === undefined) return Effect.succeed(path) return Effect.tryPromise({ try: () => NodeFs.writeFile(path, toPem(pem), { mode: 0o600 }), catch: (cause) => new Migrator.MigrationError({ kind: "Failed", message: `Could not write ${name}`, cause }) }).pipe(Effect.as(path)) } return { sslmode: verify ? "verify-full" : "require", // Node replaces its default trust store when `ca` is set. The default // store includes NODE_EXTRA_CA_CERTS and --use-system-ca. sslrootcert: yield* file("root.crt", verify ? ssl.ca ?? Tls.getCACertificates("default") : undefined), sslcert: yield* file("client.crt", ssl.cert), sslkey: yield* file("client.key", ssl.key) } }) type Pem = string | Uint8Array | ReadonlyArray<string | Uint8Array> const toPem = (value: Pem): string => (Array.isArray(value) ? value : [value]).map((pem) => Buffer.from(pem).toString().trim()).join("\n") + "\n" /** * Creates a layer that runs PostgreSQL migrations during layer construction, including `pg_dump`-based schema dump support when requested. * * @stability unstable * @category layers * @since 4.0.0 */ export const layer = <R>( options: Migrator.MigratorOptions<R> ): Layer.Layer< never, Migrator.MigrationError | SqlError, | SqlClient | PgClient | ChildProcessSpawner.ChildProcessSpawner | FileSystem.FileSystem | Path.Path | R > => Layer.effectDiscard(run(options))