SAL PostgreSQL Client (sal-postgresclient)

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.

Installation

Add this to your Cargo.toml:

[dependencies]
sal-postgresclient = "0.1.0"

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

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:

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:

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:

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:

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:

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

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:

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:

// 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");
}