feat(cli): add init dry-run and print-sql

Nik Samokhvalov · Nik Samokhvalov · commit 18e166ba94ac · 2025-12-13T10:06:14.000-08:00
- Add --print-sql and --dry-run options

- Redact passwords by default (override with --show-secrets)
diff --git a/cli/README.md b/cli/README.md
@@ -67,6 +67,20 @@ Optional permissions (RDS/self-managed extras from the root `README.md`) are ena
 npx postgresai init postgresql://admin@host:5432/dbname --skip-optional-permissions
 ```
 
+### Print SQL / dry run
+
+To see what SQL would be executed (passwords redacted by default):
+
+```bash
+npx postgresai init postgresql://admin@host:5432/dbname --print-sql
+```
+
+To print SQL and exit without applying anything:
+
+```bash
+npx postgresai init postgresql://admin@host:5432/dbname --dry-run
+```
+
 ## Quick start
 
 ### Authentication
diff --git a/cli/bin/postgres-ai.ts b/cli/bin/postgres-ai.ts
@@ -129,6 +129,9 @@ program
   .option("--monitoring-user <name>", "Monitoring role name to create/update", "postgres_ai_mon")
   .option("--password <password>", "Monitoring role password (overrides PGAI_MON_PASSWORD)")
   .option("--skip-optional-permissions", "Skip optional permissions (RDS/self-managed extras)", false)
+  .option("--print-sql", "Print SQL steps before applying (passwords redacted by default)", false)
+  .option("--show-secrets", "When printing SQL, do not redact secrets (DANGEROUS)", false)
+  .option("--dry-run", "Print SQL steps and exit without applying changes", false)
   .action(async (conn: string | undefined, opts: {
     dbUrl?: string;
     host?: string;
@@ -139,6 +142,9 @@ program
     monitoringUser: string;
     password?: string;
     skipOptionalPermissions?: boolean;
+    printSql?: boolean;
+    showSecrets?: boolean;
+    dryRun?: boolean;
   }) => {
     let adminConn;
     try {
@@ -165,6 +171,8 @@ program
     console.log(`Monitoring user: ${opts.monitoringUser}`);
     console.log(`Optional permissions: ${includeOptionalPermissions ? "enabled" : "skipped"}`);
 
+    const shouldPrintSql = !!opts.printSql || !!opts.dryRun;
+
     // Use native pg client instead of requiring psql to be installed
     const { Client } = require("pg");
     const client = new Client(adminConn.clientConfig);
@@ -210,6 +218,30 @@ program
         roleExists,
       });
 
+      if (shouldPrintSql) {
+        const redact = !opts.showSecrets;
+        const redactPasswords = (sql: string): string => {
+          if (!redact) return sql;
+          // Replace PASSWORD '<literal>' (handles doubled quotes inside).
+          return sql.replace(/password\s+'(?:''|[^'])*'/gi, "password '<redacted>'");
+        };
+
+        console.log("\n--- SQL plan ---");
+        for (const step of plan.steps) {
+          console.log(`\n-- ${step.name}${step.optional ? " (optional)" : ""}`);
+          console.log(redactPasswords(step.sql));
+        }
+        console.log("\n--- end SQL plan ---\n");
+        if (redact) {
+          console.log("Note: passwords are redacted in the printed SQL (use --show-secrets to print them).");
+        }
+      }
+
+      if (opts.dryRun) {
+        console.log("✓ dry-run completed (no changes were applied)");
+        return;
+      }
+
       const { applied, skippedOptional } = await applyInitPlan({ client, plan });
 
       console.log("✓ init completed");
diff --git a/cli/test/init.test.cjs b/cli/test/init.test.cjs
@@ -87,4 +87,18 @@ test("resolveAdminConnection rejects invalid psql-like port", () => {
   );
 });
 
+test("print-sql redaction regex matches password literal with embedded quotes", async () => {
+  const plan = await init.buildInitPlan({
+    database: "mydb",
+    monitoringUser: "postgres_ai_mon",
+    monitoringPassword: "pa'ss",
+    includeOptionalPermissions: false,
+    roleExists: false,
+  });
+  const step = plan.steps.find((s) => s.name === "create monitoring user");
+  assert.ok(step);
+  const redacted = step.sql.replace(/password\\s+'(?:''|[^'])*'/gi, "password '<redacted>'");
+  assert.match(redacted, /password '<redacted>'/i);
+});
+
 
