sal/postgresclient/README.md

# SAL PostgreSQL Client

The SAL PostgreSQL Client (`sal-postgresclient`) is an independent package that provides a simple and efficient way to interact with PostgreSQL databases in Rust. It offers connection management, query execution, a builder pattern for flexible configuration, and PostgreSQL installer functionality using nerdctl.

## Features

- **Connection Management**: Automatic connection handling and reconnection
- **Query Execution**: Simple API for executing queries and fetching results
- **Builder Pattern**: Flexible configuration with authentication support
- **Environment Variable Support**: Easy configuration through environment variables
- **Thread Safety**: Safe to use in multi-threaded applications
- **PostgreSQL Installer**: Install and configure PostgreSQL using nerdctl containers
- **Rhai Integration**: Scripting support for PostgreSQL operations

## Usage

### Basic Usage

```rust
use sal_postgresclient::{execute, query, query_one};

// Execute a query
let create_table_query = "CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT)";
execute(create_table_query, &[]).expect("Failed to create table");

// Insert data
let insert_query = "INSERT INTO users (name) VALUES ($1) RETURNING id";
let rows = query(insert_query, &[&"John Doe"]).expect("Failed to insert data");
let id: i32 = rows[0].get(0);

// Query data
let select_query = "SELECT id, name FROM users WHERE id = $1";
let row = query_one(select_query, &[&id]).expect("Failed to query data");
let name: String = row.get(1);
println!("User: {} (ID: {})", name, id);
```

### Connection Management

The module manages connections automatically, but you can also reset the connection if needed:

```rust
use sal_postgresclient::reset;

// Reset the PostgreSQL client connection
reset().expect("Failed to reset connection");
```

### Builder Pattern

The module provides a builder pattern for flexible configuration:

```rust
use sal_postgresclient::{PostgresConfigBuilder, with_config};

// Create a configuration builder
let config = PostgresConfigBuilder::new()
    .host("db.example.com")
    .port(5432)
    .user("postgres")
    .password("secret")
    .database("mydb")
    .application_name("my-app")
    .connect_timeout(30)
    .ssl_mode("require");

// Connect with the configuration
let client = with_config(config).expect("Failed to connect");
```

### PostgreSQL Installer

The package includes a PostgreSQL installer that can set up PostgreSQL using nerdctl containers:

```rust
use sal_postgresclient::{PostgresInstallerConfig, install_postgres};

// Create installer configuration
let config = PostgresInstallerConfig::new()
    .container_name("my-postgres")
    .version("15")
    .port(5433)
    .username("myuser")
    .password("mypassword")
    .data_dir("/path/to/data")
    .persistent(true);

// Install PostgreSQL
let container = install_postgres(config).expect("Failed to install PostgreSQL");
```

### Rhai Integration

The package provides Rhai scripting support for PostgreSQL operations:

```rust
use sal_postgresclient::rhai::register_postgresclient_module;
use rhai::Engine;

let mut engine = Engine::new();
register_postgresclient_module(&mut engine).expect("Failed to register PostgreSQL module");

// Now you can use PostgreSQL functions in Rhai scripts
let script = r#"
    // Connect to PostgreSQL
    let connected = pg_connect();

    // Execute a query
    let rows_affected = pg_execute("CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT)");

    // Query data
    let results = pg_query("SELECT * FROM test");
"#;

engine.eval::<()>(script).expect("Failed to execute script");
```

## Configuration

### Environment Variables

The module uses the following environment variables for configuration:

- `POSTGRES_HOST`: PostgreSQL server host (default: localhost)
- `POSTGRES_PORT`: PostgreSQL server port (default: 5432)
- `POSTGRES_USER`: PostgreSQL username (default: postgres)
- `POSTGRES_PASSWORD`: PostgreSQL password
- `POSTGRES_DB`: PostgreSQL database name (default: postgres)

### Connection String

The connection string is built from the configuration options:

```
host=localhost port=5432 user=postgres dbname=postgres
```

With authentication:

```
host=localhost port=5432 user=postgres password=secret dbname=postgres
```

With additional options:

```
host=localhost port=5432 user=postgres dbname=postgres application_name=my-app connect_timeout=30 sslmode=require
```

## API Reference

### Connection Functions

- `get_postgres_client() -> Result<Arc<PostgresClientWrapper>, PostgresError>`: Get the PostgreSQL client instance
- `reset() -> Result<(), PostgresError>`: Reset the PostgreSQL client connection

### Query Functions

- `execute(query: &str, params: &[&(dyn postgres::types::ToSql + Sync)]) -> Result<u64, PostgresError>`: Execute a query and return the number of affected rows
- `query(query: &str, params: &[&(dyn postgres::types::ToSql + Sync)]) -> Result<Vec<Row>, PostgresError>`: Execute a query and return the results as a vector of rows
- `query_one(query: &str, params: &[&(dyn postgres::types::ToSql + Sync)]) -> Result<Row, PostgresError>`: Execute a query and return a single row
- `query_opt(query: &str, params: &[&(dyn postgres::types::ToSql + Sync)]) -> Result<Option<Row>, PostgresError>`: Execute a query and return an optional row

### Configuration Functions

- `PostgresConfigBuilder::new() -> PostgresConfigBuilder`: Create a new PostgreSQL configuration builder
- `with_config(config: PostgresConfigBuilder) -> Result<Client, PostgresError>`: Create a new PostgreSQL client with custom configuration

## Error Handling

The module uses the `postgres::Error` type for error handling:

```rust
use sal_postgresclient::{query, query_one};

// Handle errors
match query("SELECT * FROM users", &[]) {
    Ok(rows) => {
        println!("Found {} users", rows.len());
    },
    Err(e) => {
        eprintln!("Error querying users: {}", e);
    }
}

// Using query_one with no results
match query_one("SELECT * FROM users WHERE id = $1", &[&999]) {
    Ok(_) => {
        println!("User found");
    },
    Err(e) => {
        eprintln!("User not found: {}", e);
    }
}
```

## Thread Safety

The PostgreSQL client module is designed to be thread-safe. It uses `Arc` and `Mutex` to ensure safe concurrent access to the client instance.

## Examples

### Basic CRUD Operations

```rust
use sal_postgresclient::{execute, query, query_one};

// Create
let create_query = "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id";
let rows = query(create_query, &[&"Alice", &"alice@example.com"]).expect("Failed to create user");
let id: i32 = rows[0].get(0);

// Read
let read_query = "SELECT id, name, email FROM users WHERE id = $1";
let row = query_one(read_query, &[&id]).expect("Failed to read user");
let name: String = row.get(1);
let email: String = row.get(2);

// Update
let update_query = "UPDATE users SET email = $1 WHERE id = $2";
let affected = execute(update_query, &[&"new.alice@example.com", &id]).expect("Failed to update user");

// Delete
let delete_query = "DELETE FROM users WHERE id = $1";
let affected = execute(delete_query, &[&id]).expect("Failed to delete user");
```

### Transactions

Transactions are not directly supported by the module, but you can use the PostgreSQL client to implement them:

```rust
use sal_postgresclient::{execute, query};

// Start a transaction
execute("BEGIN", &[]).expect("Failed to start transaction");

// Perform operations
let insert_query = "INSERT INTO accounts (user_id, balance) VALUES ($1, $2)";
execute(insert_query, &[&1, &1000.0]).expect("Failed to insert account");

let update_query = "UPDATE users SET has_account = TRUE WHERE id = $1";
execute(update_query, &[&1]).expect("Failed to update user");

// Commit the transaction
execute("COMMIT", &[]).expect("Failed to commit transaction");

// Or rollback in case of an error
// execute("ROLLBACK", &[]).expect("Failed to rollback transaction");
```

## Testing

The module includes comprehensive tests for both unit and integration testing:

```rust
// Unit tests
#[test]
fn test_postgres_config_builder() {
    let config = PostgresConfigBuilder::new()
        .host("test-host")
        .port(5433)
        .user("test-user");
    
    let conn_string = config.build_connection_string();
    assert!(conn_string.contains("host=test-host"));
    assert!(conn_string.contains("port=5433"));
    assert!(conn_string.contains("user=test-user"));
}

// Integration tests
#[test]
fn test_basic_postgres_operations() {
    // Skip if PostgreSQL is not available
    if !is_postgres_available() {
        return;
    }
    
    // Create a test table
    let create_table_query = "CREATE TEMPORARY TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)";
    execute(create_table_query, &[]).expect("Failed to create table");
    
    // Insert data
    let insert_query = "INSERT INTO test_table (name) VALUES ($1) RETURNING id";
    let rows = query(insert_query, &[&"test"]).expect("Failed to insert data");
    let id: i32 = rows[0].get(0);
    
    // Query data
    let select_query = "SELECT name FROM test_table WHERE id = $1";
    let row = query_one(select_query, &[&id]).expect("Failed to query data");
    let name: String = row.get(0);
    assert_eq!(name, "test");
}
```