Function backup

db/cli.ts:318–378

* Exports database data to a backup file.

(options?: {
  env?: string;
  schema?: string;
  args?: string[];
})

 * Exports database data to a backup file.
 */
async function backup(options?: {
  env?: string;
  schema?: string;
  args?: string[];
}) {
  const schema = options?.schema ?? "public";
  const db = getDatabase(options);

  const tables = await db
    .from("information_schema.tables")
    .where({
      table_schema: options?.schema ?? "public",
      table_type: "BASE TABLE",
    })
    .andWhereNot({ table_name: "migration" })
    .andWhereNot({ table_name: "migration_lock" })
    .select("table_name")
    .then((rows) => rows.map((row) => `"${row.table_name}"`).join(", "))
    .finally(() => db.destroy());

  const cmd = execa(
    "pg_dump",
    [
      "--verbose",
      "--data-only",
      `--schema=${schema}`,
      "--no-owner",
      "--no-privileges",
      // '--column-inserts',
      "--disable-triggers",
      "--exclude-table=migration",
      "--exclude-table=migration_lock",
      "--exclude-table=migration_id_seq",
      "--exclude-table=migration_lock_index_seq",
      ...(options?.args ?? []),
    ],
    { stdio: ["pipe", "pipe", "inherit"] },
  ).on("exit", (code) => {
    if (code && code !== 0) process.exit(code);
  });

  const timestamp = new Date().toISOString().replace(/(-|:|\.\d{3})/g, "");
  const suffix = options?.env ? `_${options.env}` : "";
  const file = resolve(backupsDir, `${timestamp}${suffix}.sql`);
  const out = createWriteStream(file, { encoding: "utf8" });
  const rl = createInterface({ input: cmd.stdout!, terminal: false });

  rl.on("line", (line) => {
    // Some (system) triggers cannot be disabled in a cloud environment
    // "DISABLE TRIGGER ALL" => "DISABLE TRIGGER USER"
    if (line.endsWith(" TRIGGER ALL;")) {
      out.write(`${line.substring(0, line.length - 5)} USER;${EOL}`, "utf8");
    }
    // Add a command that truncates all the database tables
    else if (line.startsWith("SET row_security")) {
      out.write(`${line}${EOL}${EOL}TRUNCATE TABLE ${tables} CASCADE;${EOL}`);
    } else {
      out.write(`${line}${EOL}`, "utf8");