25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
use std::time::Duration;
use dbx_core::db::postgres;
use dbx_core::models::connection::DatabaseType;
use dbx_core::table_structure_sql::{
build_table_structure_change_sql, EditableStructureIndex, TableStructureSqlOptions,
};
fn live_postgres_url() -> String {
let host = std::env::var("DBX_LIVE_POSTGRES_HOST").unwrap_or_else(|_| "127.0.0.1".to_string());
let port = std::env::var("DBX_LIVE_POSTGRES_PORT").ok().and_then(|value| value.parse().ok()).unwrap_or(5432);
let user = std::env::var("DBX_LIVE_POSTGRES_USER").unwrap_or_else(|_| "postgres".to_string());
let password = std::env::var("DBX_LIVE_POSTGRES_PASSWORD").unwrap_or_default();
let database = std::env::var("DBX_LIVE_POSTGRES_DATABASE").unwrap_or_else(|_| "postgres".to_string());
format!("postgresql://{user}:{password}@{host}:{port}/{database}")
}
fn concurrent_index(columns: Vec<&str>) -> EditableStructureIndex {
EditableStructureIndex {
id: "idx_users_email".to_string(),
name: "idx_users_email".to_string(),
columns: columns.into_iter().map(str::to_string).collect(),
is_unique: false,
is_primary: false,
filter: String::new(),
index_type: String::new(),
included_columns: Vec::new(),
column_opclasses: Vec::new(),
comment: String::new(),
concurrently: true,
original: None,
oracle_original_missing: false,
marked_for_drop: false,
}
}
/// `CREATE INDEX CONCURRENTLY` generated by the table structure editor must
/// succeed on a real PostgreSQL server and leave a valid index
/// (`pg_index.indisvalid = true`).
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_concurrent_index_builds_valid_index() {
let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
let suffix = uuid::Uuid::new_v4().simple().to_string();
let schema = format!("dbx_cic_{}", &suffix[..8]);
let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
let _ = postgres::execute_batch(&pool, &cleanup).await;
let setup = vec![
format!("CREATE SCHEMA \"{schema}\""),
format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text, name text, status text)"),
];
postgres::execute_batch(&pool, &setup).await.expect("create live test schema");
let result = build_table_structure_change_sql(TableStructureSqlOptions {
database_type: Some(DatabaseType::Postgres),
driver_profile: None,
schema: Some(schema.clone()),
table_name: "users".to_string(),
columns: Vec::new(),
indexes: vec![concurrent_index(vec!["email"])],
foreign_keys: Vec::new(),
triggers: Vec::new(),
table_comment: None,
original_table_comment: None,
mysql_engine: None,
mysql_auto_increment_value: None,
transwarp_create: None,
partitioned: false,
foreign_table: false,
is_gaussdb_m_mode: false,
table_collation: None,
primary_key_name: None,
primary_key_change: None,
oracle_foreign_key_changes: None,
oracle_constraint_changes: None,
});
assert!(result.warnings.is_empty(), "{:?}", result.warnings);
assert_eq!(
result.statements,
vec![format!("CREATE INDEX CONCURRENTLY \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\");")]
);
postgres::execute_batch(&pool, &result.statements).await.expect("execute concurrent index build");
let client = pool.get().await.expect("checkout connection");
let row = client
.query_one(
"SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass($1)",
&[&format!("{schema}.idx_users_email")],
)
.await
.expect("read pg_index");
let valid: bool = row.get(0);
assert!(valid, "concurrently-built index must be valid");
postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}
/// PostgreSQL forbids `CREATE INDEX CONCURRENTLY` inside an explicit
/// transaction block; the editor relies on per-statement autocommit, so the
/// generated statement must only ever run outside `BEGIN`.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_concurrent_index_rejects_transaction_block() {
let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
let suffix = uuid::Uuid::new_v4().simple().to_string();
let schema = format!("dbx_cic_{}", &suffix[..8]);
let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
let _ = postgres::execute_batch(&pool, &cleanup).await;
let setup = vec![
format!("CREATE SCHEMA \"{schema}\""),
format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text)"),
];
postgres::execute_batch(&pool, &setup).await.expect("create live test schema");
let client = pool.get().await.expect("checkout connection");
client.batch_execute("BEGIN").await.expect("begin transaction");
let err = client
.batch_execute(&format!("CREATE INDEX CONCURRENTLY \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\")"))
.await
.expect_err("CREATE INDEX CONCURRENTLY must fail inside a transaction block");
let _ = client.batch_execute("ROLLBACK").await;
let message = err.as_db_error().map(|db_error| db_error.message().to_string()).unwrap_or_else(|| err.to_string());
assert!(
message.contains("cannot run inside a transaction block"),
"expected transaction-block error, got: {message}"
);
postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}
/// A cancelled `CREATE INDEX CONCURRENTLY` leaves a same-name INVALID index
/// behind (`pg_index.indisvalid = false`); `list_invalid_indexes` must report
/// it so the editor can block a doomed retry and point the user at the fix.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_invalid_index_leftover_is_detected() {
let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
let suffix = uuid::Uuid::new_v4().simple().to_string();
let schema = format!("dbx_cic_{}", &suffix[..8]);
let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
let _ = postgres::execute_batch(&pool, &cleanup).await;
let setup = vec![
format!("CREATE SCHEMA \"{schema}\""),
format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text)"),
// Simulate the leftover of a cancelled concurrent build. PostgreSQL
// does not accept a schema-qualified index name in CREATE INDEX; the
// index inherits the table's schema, so only the table is qualified.
format!("CREATE INDEX \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\")"),
];
postgres::execute_batch(&pool, &setup).await.expect("create live test schema");
let invalid = postgres::list_invalid_indexes(&pool, &schema, "users").await.expect("list invalid indexes");
assert!(invalid.is_empty(), "fresh index must be valid, got: {invalid:?}");
// Flip indisvalid to false the same way an interrupted concurrent build does.
let client = pool.get().await.expect("checkout connection");
client
.batch_execute(&format!(
"UPDATE pg_index SET indisvalid = false WHERE indexrelid = to_regclass('{schema}.idx_users_email')"
))
.await
.expect("mark index invalid");
let invalid = postgres::list_invalid_indexes(&pool, &schema, "users").await.expect("list invalid indexes");
assert_eq!(invalid, vec!["idx_users_email".to_string()], "invalid leftover must be reported");
postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}
/// The structure editor must never emit `CREATE INDEX CONCURRENTLY` for a
/// partitioned parent table — the builder rejects the request up front
/// (fail closed) and no SQL reaches the server at all, instead of downgrading
/// to a blocking `CREATE INDEX`.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_partitioned_parent_concurrent_request_rejected() {
let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
let suffix = uuid::Uuid::new_v4().simple().to_string();
let schema = format!("dbx_cic_{}", &suffix[..8]);
let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
let _ = postgres::execute_batch(&pool, &cleanup).await;
let setup = vec![
format!("CREATE SCHEMA \"{schema}\""),
format!(
"CREATE TABLE \"{schema}\".events (id integer, created_at date) PARTITION BY RANGE (created_at)"
),
format!("CREATE TABLE \"{schema}\".events_2026 PARTITION OF \"{schema}\".events FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')"),
];
postgres::execute_batch(&pool, &setup).await.expect("create live test schema");
let mut idx = concurrent_index(vec!["id"]);
idx.name = "idx_events_id".to_string();
idx.id = "idx_events_id".to_string();
let result = build_table_structure_change_sql(TableStructureSqlOptions {
database_type: Some(DatabaseType::Postgres),
driver_profile: None,
schema: Some(schema.clone()),
table_name: "events".to_string(),
columns: Vec::new(),
indexes: vec![idx],
foreign_keys: Vec::new(),
triggers: Vec::new(),
table_comment: None,
original_table_comment: None,
mysql_engine: None,
mysql_auto_increment_value: None,
transwarp_create: None,
partitioned: true,
foreign_table: false,
is_gaussdb_m_mode: false,
table_collation: None,
primary_key_name: None,
primary_key_change: None,
oracle_foreign_key_changes: None,
oracle_constraint_changes: None,
});
assert_eq!(
result.warnings,
vec![
"CREATE INDEX CONCURRENTLY is not supported on PostgreSQL partitioned parent tables. Create indexes concurrently on individual partitions and attach them separately."
]
);
assert!(
result.statements.is_empty(),
"no SQL may be generated for an unsupported concurrent request, got: {:?}",
result.statements
);
// Nothing was executed: the partitioned parent still has no such index.
let client = pool.get().await.expect("checkout connection");
let row = client
.query_one(
"SELECT count(*) FROM pg_catalog.pg_class WHERE relname = $1 AND relnamespace = to_regnamespace($2)",
&[&"idx_events_id", &schema],
)
.await
.expect("count index relations");
let count: i64 = row.get(0);
assert_eq!(count, 0, "rejected request must not create any index on the server");
postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}