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

systemprompt_cli/commands/cloud/db/
mod.rs
