Database CLI Client

A Database CLI Client (Command Line Interface Client) is a software tool that allows users to interact with a database system directly from the command line or a terminal. Instead of using a graphical user interface (GUI), users type commands to perform various database operations.

Purpose and Features:
Database CLI clients are essential tools for developers, database administrators (DBAs), and anyone needing to programmatically interact with a database. Their primary purposes and common features include:

1. Executing SQL Queries: The most fundamental feature is the ability to run SQL `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP`, and other Data Definition Language (DDL) and Data Manipulation Language (DML) statements.
2. Database Management: Users can create, modify, and delete databases, tables, views, indexes, and other database objects.
3. Data Inspection: Allows for quick viewing and verification of data within tables, often with options for formatting output (e.g., tabular, CSV, JSON).
4. Transaction Management: Support for beginning, committing, or rolling back transactions.
5. Schema Introspection: Tools often provide commands to inspect the database schema, list tables, describe table columns, show indexes, etc.
6. Scripting and Automation: CLI clients are highly scriptable, making them ideal for automation tasks, batch processing, and integrating database operations into larger workflows or CI/CD pipelines.
7. Remote Access: They are easily usable over SSH connections, allowing secure remote administration of databases without needing a graphical desktop environment.
8. Resource Efficiency: Compared to GUI tools, CLI clients typically consume fewer system resources.

How it Works:
At its core, a Database CLI Client connects to a database server using a specific database driver or connector library. Once connected, it sends the user's commands (typically SQL queries) to the database server. The server processes these queries and sends back the results, which the CLI client then formats and displays to the user's terminal.

Examples:
Well-known database CLI clients include:
* `psql` for PostgreSQL
* `mysql` for MySQL/MariaDB
* `sqlite3` for SQLite
* `sqlcmd` for Microsoft SQL Server

Building a CLI Client:
When building a custom CLI client in a language like Rust, you would typically use:
* A CLI Argument Parsing Library: Like `clap` or `structopt`, to define and parse command-line arguments (e.g., database URL, SQL query).
* A Database Driver/ORM: Like `sqlx` or `diesel`, to establish connections, execute queries, and handle results for the specific database system (e.g., PostgreSQL, MySQL, SQLite).
* Asynchronous Runtime (Optional but Recommended for I/O): Like `tokio`, for efficient handling of network operations when communicating with the database.

Example Code

```rust
// Cargo.toml
// [dependencies]
// tokio = { version = "1", features = ["full"] }
// sqlx = { version = "0.7", features = ["runtime-tokio", "sqlite"] }
// clap = { version = "4", features = ["derive"] }
// serde_json = "1.0" // For generic value handling in printing

use clap::Parser;
use sqlx::sqlite::{SqlitePool, SqliteRow};
use sqlx::{Column, Row, TypeInfo};
use std::collections::HashMap;

/// A simple Rust database CLI client for SQLite
#[derive(Parser, Debug)]
#[command(author, version, about, long_about = None)]
struct Args {
    /// Database URL (e.g., sqlite::memory: or sqlite://my_database.db)
    #[arg(short, long, default_value = "sqlite::memory:")]
    database_url: String,

    /// SQL query to execute
    #[arg(short, long)]
    query: String,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let args = Args::parse();

    // Establish a database connection pool
    let pool = SqlitePool::connect(&args.database_url).await?;
    println!("Connected to database: {}", args.database_url);

    // Determine if the query is a SELECT statement to handle results differently
    let query_str = args.query.trim().to_lowercase();
    if query_str.starts_with("select") {
        execute_select_query(&pool, &args.query).await?;
    } else {
        execute_non_select_query(&pool, &args.query).await?;
    }

    Ok(())
}

/// Executes a SELECT query and prints results in a formatted table.
async fn execute_select_query(pool: &SqlitePool, query: &str) -> Result<(), Box<dyn std::error::Error>> {
    let rows = sqlx::query(query).fetch_all(pool).await?;

    if rows.is_empty() {
        println!("No results found.");
        return Ok(());
    }

    // Get column names from the first row for header and width calculation
    let columns: Vec<&Column> = rows[0].columns().collect();
    let column_names: Vec<String> = columns.iter().map(|c| c.name().to_string()).collect();

    // Calculate maximum width for each column based on header and data
    let mut column_widths: HashMap<String, usize> = HashMap::new();
    for name in &column_names {
        column_widths.insert(name.clone(), name.len()); // Initialize with header length
    }

    for row in &rows {
        for col in &columns {
            let col_name = col.name();
            // Attempt to get value as a JSON Value to stringify it generically
            let value_str = match row.try_get::<serde_json::Value, &str>(col_name) {
                Ok(v) => v.to_string(),
                Err(_) => "NULL".to_string(), // Fallback for NULL or unreadable types
            };
            let current_width = column_widths.entry(col_name.to_string()).or_insert(0);
            *current_width = (*current_width).max(value_str.len());
        }
    }

    // Print table header
    for name in &column_names {
        print!("{:<width$} | ", name, width = column_widths[name]);
    }
    println!();

    // Print separator line
    for name in &column_names {
        for _ in 0..column_widths[name] {
            print!("-");
        }
        print!("-+");
    }
    println!();

    // Print data rows
    for row in rows {
        for col in &columns {
            let col_name = col.name();
            let value_str = match row.try_get::<serde_json::Value, &str>(col_name) {
                Ok(v) => v.to_string(),
                Err(_) => "NULL".to_string(),
            };
            print!("{:<width$} | ", value_str, width = column_widths[col_name]);
        }
        println!();
    }

    Ok(())
}

/// Executes non-SELECT queries (e.g., INSERT, UPDATE, DELETE, CREATE) and reports status.
async fn execute_non_select_query(pool: &SqlitePool, query: &str) -> Result<(), Box<dyn std::error::Error>> {
    let result = sqlx::query(query).execute(pool).await?;
    println!("Query executed successfully.");
    println!("Rows affected: {}", result.rows_affected());
    Ok(())
}

/*
Example Usage:

1.  Create a test database and table (using a file-based SQLite database for persistence):
    cargo run -- -d sqlite://test.db -q "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)"

2.  Insert data:
    cargo run -- -d sqlite://test.db -q "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"
    cargo run -- -d sqlite://test.db -q "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')"

3.  Select all data:
    cargo run -- -d sqlite://test.db -q "SELECT * FROM users"

4.  Select specific columns:
    cargo run -- -d sqlite://test.db -q "SELECT name FROM users WHERE id = 1"

5.  Update data:
    cargo run -- -d sqlite://test.db -q "UPDATE users SET email = 'alice.smith@example.com' WHERE name = 'Alice'"

6.  Select again to see changes:
    cargo run -- -d sqlite://test.db -q "SELECT * FROM users"

7.  Using in-memory database (data won't persist):
    cargo run -- -q "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)"
    cargo run -- -q "INSERT INTO products (name) VALUES ('Laptop')"
    cargo run -- -q "SELECT * FROM products"
*/
```

Example Code

Related Topics