systemprompt_cli/commands/cloud/db/
mod.rs

1//! `cloud db` subcommands operating on a remote tenant database.
2//!
3//! Resolves a profile's database URL from its secrets, then routes most
4//! commands through the shared infrastructure [`db`] handlers; backup and
5//! restore are handled locally via `pg_dump`/`pg_restore`.
6//!
7//! Copyright (c) systemprompt.io — Business Source License 1.1.
8//! See <https://systemprompt.io> for licensing details.
9
10mod backup;
11mod restore;
12
13use anyhow::{Context, Result, anyhow, bail};
14use clap::{Subcommand, ValueEnum};
15use std::path::PathBuf;
16use std::process::Command;
17use systemprompt_cloud::{ProfilePath, ProjectContext};
18use systemprompt_runtime::DatabaseContext;
19
20use crate::commands::infrastructure::db;
21use crate::context::CommandContext;
22
23#[derive(Debug, Subcommand)]
24pub enum CloudDbCommands {
25    #[command(about = "Run migrations on cloud database")]
26    Migrate {
27        #[arg(long, help = "Profile name")]
28        profile: String,
29    },
30
31    #[command(about = "Execute SQL query (read-only) on cloud database")]
32    Query {
33        #[arg(long, help = "Profile name")]
34        profile: String,
35        sql: String,
36        #[arg(long)]
37        limit: Option<u32>,
38        #[arg(long)]
39        offset: Option<u32>,
40    },
41
42    #[command(about = "Execute write operation on cloud database")]
43    Execute {
44        #[arg(long, help = "Profile name")]
45        profile: String,
46        sql: String,
47    },
48
49    #[command(about = "Diff cloud schema against extension declarations")]
50    Doctor {
51        #[arg(long, help = "Profile name")]
52        profile: String,
53    },
54
55    #[command(about = "Show cloud database connection status")]
56    Status {
57        #[arg(long, help = "Profile name")]
58        profile: String,
59    },
60
61    #[command(about = "Show cloud database info")]
62    Info {
63        #[arg(long, help = "Profile name")]
64        profile: String,
65    },
66
67    #[command(about = "List all tables in cloud database")]
68    Tables {
69        #[arg(long, help = "Profile name")]
70        profile: String,
71        #[arg(long, help = "Filter tables by pattern")]
72        filter: Option<String>,
73    },
74
75    #[command(about = "Describe table schema in cloud database")]
76    Describe {
77        #[arg(long, help = "Profile name")]
78        profile: String,
79        table_name: String,
80    },
81
82    #[command(about = "Get row count for a table in cloud database")]
83    Count {
84        #[arg(long, help = "Profile name")]
85        profile: String,
86        table_name: String,
87    },
88
89    #[command(about = "List all indexes in cloud database")]
90    Indexes {
91        #[arg(long, help = "Profile name")]
92        profile: String,
93        #[arg(long, help = "Filter by table name")]
94        table: Option<String>,
95    },
96
97    #[command(about = "Show cloud database and table sizes")]
98    Size {
99        #[arg(long, help = "Profile name")]
100        profile: String,
101    },
102
103    #[command(about = "Backup cloud database using pg_dump")]
104    Backup {
105        #[arg(long, help = "Profile name")]
106        profile: String,
107
108        #[arg(
109            long,
110            default_value = "custom",
111            help = "Backup format: custom, sql, directory"
112        )]
113        format: BackupFormat,
114
115        #[arg(
116            long,
117            help = "Output file path (default: backups/<profile>-<timestamp>.<ext>)"
118        )]
119        output: Option<String>,
120    },
121
122    #[command(about = "Restore cloud database from a backup file")]
123    Restore {
124        #[arg(long, help = "Profile name")]
125        profile: String,
126
127        #[arg(help = "Path to backup file")]
128        file: String,
129
130        #[arg(short = 'y', long, help = "Skip confirmation prompt")]
131        yes: bool,
132    },
133}
134
135#[derive(Debug, Clone, Copy, ValueEnum)]
136pub enum BackupFormat {
137    #[value(help = "pg_dump custom format (-Fc), supports parallel restore")]
138    Custom,
139    #[value(help = "Plain SQL text format (-Fp), human-readable")]
140    Sql,
141    #[value(help = "Directory format (-Fd), supports parallel dump and restore")]
142    Directory,
143}
144
145impl CloudDbCommands {
146    fn profile_name(&self) -> &str {
147        match self {
148            Self::Migrate { profile }
149            | Self::Query { profile, .. }
150            | Self::Execute { profile, .. }
151            | Self::Doctor { profile }
152            | Self::Status { profile }
153            | Self::Info { profile }
154            | Self::Tables { profile, .. }
155            | Self::Describe { profile, .. }
156            | Self::Count { profile, .. }
157            | Self::Indexes { profile, .. }
158            | Self::Size { profile }
159            | Self::Backup { profile, .. }
160            | Self::Restore { profile, .. } => profile,
161        }
162    }
163
164    fn into_db_command(self) -> Option<db::DbCommands> {
165        match self {
166            Self::Migrate { .. } => Some(db::DbCommands::Migrate {
167                allow_checksum_drift: false,
168            }),
169            Self::Query {
170                sql, limit, offset, ..
171            } => Some(db::DbCommands::Query { sql, limit, offset }),
172            Self::Execute { sql, .. } => Some(db::DbCommands::Execute { sql }),
173            Self::Doctor { .. } => Some(db::DbCommands::Doctor),
174            Self::Status { .. } => Some(db::DbCommands::Status),
175            Self::Info { .. } => Some(db::DbCommands::Info),
176            Self::Tables { filter, .. } => Some(db::DbCommands::Tables { filter }),
177            Self::Describe { table_name, .. } => Some(db::DbCommands::Describe { table_name }),
178            Self::Count { table_name, .. } => Some(db::DbCommands::Count { table_name }),
179            Self::Indexes { table, .. } => Some(db::DbCommands::Indexes { table }),
180            Self::Size { .. } => Some(db::DbCommands::Size),
181            Self::Backup { .. } | Self::Restore { .. } => None,
182        }
183    }
184}
185
186pub async fn execute(cmd: CloudDbCommands, ctx: &CommandContext) -> Result<()> {
187    let profile_name = cmd.profile_name().to_owned();
188    let db_url = load_cloud_database_url(&profile_name)?;
189    execute_inner(cmd, &profile_name, &db_url, ctx).await
190}
191
192pub async fn execute_with_database_url(
193    cmd: CloudDbCommands,
194    database_url: &str,
195    ctx: &CommandContext,
196) -> Result<()> {
197    let profile_name = cmd.profile_name().to_owned();
198    execute_inner(cmd, &profile_name, database_url, ctx).await
199}
200
201async fn execute_inner(
202    cmd: CloudDbCommands,
203    profile_name: &str,
204    db_url: &str,
205    ctx: &CommandContext,
206) -> Result<()> {
207    match &cmd {
208        CloudDbCommands::Backup { format, output, .. } => {
209            return backup::execute(profile_name, db_url, *format, output.as_deref());
210        },
211        CloudDbCommands::Restore { file, yes, .. } => {
212            return restore::execute(profile_name, db_url, file, *yes, ctx.prompter(), &ctx.cli);
213        },
214        _ => {},
215    }
216
217    let db_ctx = DatabaseContext::from_url(db_url).await?;
218    let db_cmd = cmd
219        .into_db_command()
220        .ok_or_else(|| anyhow!("Unexpected command variant"))?;
221
222    let db_scoped =
223        CommandContext::with_database(ctx.cli.clone(), ctx.env.clone(), db_ctx, db_url.to_owned());
224    db::execute(db_cmd, &db_scoped).await
225}
226
227fn load_cloud_database_url(profile_name: &str) -> Result<String> {
228    let ctx = ProjectContext::discover();
229    let profile_dir = ctx.profile_dir(profile_name);
230
231    if !profile_dir.exists() {
232        return Err(anyhow!("Profile '{}' not found", profile_name));
233    }
234
235    let secrets_path = ProfilePath::Secrets.resolve(&profile_dir);
236    let secrets = systemprompt_config::load_secrets_from_path(&secrets_path)
237        .with_context(|| format!("Failed to load secrets for profile '{}'", profile_name))?;
238
239    Ok(secrets.effective_database_url(true).to_owned())
240}
241
242fn ensure_pg_tool(tool: &str) -> Result<()> {
243    match Command::new(tool).arg("--version").output() {
244        Ok(output) if output.status.success() => Ok(()),
245        _ => bail!(
246            "'{}' not found. Install PostgreSQL client tools:\n  apt install postgresql-client",
247            tool
248        ),
249    }
250}
251
252fn find_pg_dump() -> Result<PathBuf> {
253    for version in [17, 16, 15, 14] {
254        let path = PathBuf::from(format!("/usr/lib/postgresql/{}/bin/pg_dump", version));
255        if path.exists() {
256            return Ok(path);
257        }
258    }
259    ensure_pg_tool("pg_dump")?;
260    Ok(PathBuf::from("pg_dump"))
261}
262
263fn find_pg_restore() -> Result<PathBuf> {
264    for version in [17, 16, 15, 14] {
265        let path = PathBuf::from(format!("/usr/lib/postgresql/{}/bin/pg_restore", version));
266        if path.exists() {
267            return Ok(path);
268        }
269    }
270    ensure_pg_tool("pg_restore")?;
271    Ok(PathBuf::from("pg_restore"))
272}
273
274fn adjust_ssl_mode(database_url: &str) -> String {
275    database_url.replace("sslmode=require", "sslmode=prefer")
276}
systemprompt_cli/commands/cloud/db/mod.rs

systemprompt_cli/commands/cloud/db/
mod.rs
