# 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`:
```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
```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");
}
```