# SQLx Helper Module
`sqlxhelper` is the SQLx-oriented PostgreSQL integration layer in Neocrates. It provides a connection pool wrapper, transaction management, migration support, and SQL logging macros.
See also: [root README](../../README.md)
---
## Enable the Feature
```toml
neocrates = { version = "0.1", default-features = false, features = ["sqlx"] }
```
---
## Public API
### Connection Pool
| `SqlxPool::new(url, max_connections)` | Create a pool manually (auto-creates the database if missing) |
| `SqlxPool::from_env()` | Create from `DATABASE_URL` / `DATABASE_POOL_SIZE` env vars |
| `SqlxPool::pool()` | Return a reference to the underlying `&PgPool` |
| `SqlxPool::begin()` | Begin a transaction manually, returns `SqlxTx` |
| `SqlxPool::with_transaction(f)` | Execute a closure inside an auto-commit/rollback transaction |
| `SqlxPool::run_migrations(migrator)` | Run SQLx migrations |
| `SqlxPool::size()` | Total connection count |
| `SqlxPool::idle()` | Idle connection count |
| `SqlxPool::health_check()` | Connectivity check (`SELECT 1`) |
### Type Aliases
| `SqlxTx<'a>` | Alias for `sqlx::Transaction<'a, sqlx::Postgres>` |
| `SqlxResult<T>` | Alias for `Result<T, SqlxError>` |
### Error Type
`SqlxError` — variants: `Database`, `Migration`, `UrlParseError`, `Config`, `Other`
### SQL Logging Macros (all require `.await`)
| `sqlx_fetch_all!(exec, query)` | Fetch all matching rows as `Vec<T>` |
| `sqlx_fetch_one!(exec, query)` | Fetch exactly one row (error if zero or more than one) |
| `sqlx_fetch_optional!(exec, query)` | Fetch zero or one row as `Option<T>` |
| `sqlx_execute!(exec, query)` | Execute INSERT / UPDATE / DELETE |
| `sqlx_fetch_scalar!(exec, query)` | Fetch a single scalar value |
| `sqlx_with_tx!(pool, \|tx\| { ... })` | Auto-managed transaction block (recommended) |
---
## Quick Start
```rust
use neocrates::sqlxhelper::pool::SqlxPool;
#[tokio::main]
async fn main() -> anyhow::Result<()> {
let pool = SqlxPool::from_env().await?;
pool.health_check().await?;
Ok(())
}
```
Environment variables:
```bash
export DATABASE_URL="postgres://postgres:postgres@localhost/myapp"
export DATABASE_POOL_SIZE=10 # optional, default 10
```
---
## Step-by-Step Tutorial
### 1. Create the Pool
**From environment variables (recommended):**
```rust
let pool = SqlxPool::from_env().await?;
```
**With explicit parameters:**
```rust
let pool = SqlxPool::new(
"postgres://postgres:password@localhost/myapp",
10,
).await?;
```
On startup the pool automatically:
1. Checks whether the target database exists; creates it if not
2. Establishes the connection pool
3. Runs `SET TIME ZONE 'UTC'` on every new connection
---
### 2. Execute Queries
`SqlxPool` implements `Deref<Target = PgPool>`, so it can be passed anywhere `&PgPool` is expected.
**Using the raw sqlx API:**
```rust
let row: (i64,) = sqlx::query_as("SELECT 1")
.fetch_one(pool.pool())
.await?;
```
**Using the logging macros (recommended — automatically records call location and SQL):**
```rust
use neocrates::sqlxhelper::logging::set_sql_logging;
// Optional: enable manually. Enabled by default in debug builds.
set_sql_logging(true);
#[derive(sqlx::FromRow)]
struct User { id: i64, name: String }
// Fetch all rows
let users: Vec<User> = neocrates::sqlx_fetch_all!(
pool.pool(),
sqlx::query_as::<_, User>("SELECT id, name FROM users")
).await?;
// Fetch exactly one row
let user: User = neocrates::sqlx_fetch_one!(
pool.pool(),
sqlx::query_as::<_, User>("SELECT id, name FROM users WHERE id = $1").bind(1i64)
).await?;
// Fetch an optional row
let maybe: Option<User> = neocrates::sqlx_fetch_optional!(
pool.pool(),
sqlx::query_as::<_, User>("SELECT id, name FROM users WHERE id = $1").bind(99i64)
).await?;
// Scalar query
let count: i64 = neocrates::sqlx_fetch_scalar!(
pool.pool(),
sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM users")
).await?;
// Write operation
let result = neocrates::sqlx_execute!(
pool.pool(),
sqlx::query("DELETE FROM users WHERE id = $1").bind(1i64)
).await?;
println!("rows affected: {}", result.rows_affected());
```
---
### 3. Transactions — `sqlx_with_tx!` (recommended)
The `sqlx_with_tx!` macro handles commit and rollback automatically:
```rust
let user_id: i64 = neocrates::sqlx_with_tx!(pool, |tx| {
// Reborrow the transaction as &mut PgConnection with &mut *tx
neocrates::sqlx_execute!(
&mut *tx,
sqlx::query("INSERT INTO users (name) VALUES ($1)").bind("alice")
).await?;
let id = neocrates::sqlx_fetch_scalar!(
&mut *tx,
sqlx::query_scalar::<_, i64>("SELECT lastval()")
).await?;
Ok(id)
}).await?;
```
- Closure returns `Ok(...)` → transaction is **committed**
- Closure returns `Err(...)` → transaction is **rolled back** (a rollback failure is logged with `warn!` and does not replace the original error)
---
### 4. Transactions — Manual Control (advanced)
Use `begin()` when you need finer-grained control (e.g. conditional commit):
```rust
use neocrates::sqlxhelper::pool::SqlxTx;
let mut tx: SqlxTx = pool.begin().await?;
let result = async {
neocrates::sqlx_execute!(
&mut *tx,
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
.bind(100i64).bind(1i64)
).await?;
neocrates::sqlx_execute!(
&mut *tx,
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
.bind(100i64).bind(2i64)
).await?;
Ok::<_, neocrates::sqlxhelper::pool::SqlxError>(())
}.await;
match result {
Ok(()) => tx.commit().await?,
Err(e) => {
tx.rollback().await.ok();
return Err(e.into());
}
}
```
> Dropping a `SqlxTx` triggers an automatic rollback, but **calling `rollback()` explicitly is safer and more predictable**.
---
### 5. Transactions Across Service Layer
**Core pattern**: service methods accept `&mut sqlx::PgConnection`, making them work both with and without a transaction — the caller decides.
`SqlxPool::acquire()` returns a `PoolConnection` and `SqlxTx` both deref to `PgConnection`, so the same function works in either context.
```rust
use neocrates::sqlxhelper::pool::{SqlxPool, SqlxResult};
// Service method: accepts &mut PgConnection
async fn create_user(
conn: &mut sqlx::PgConnection,
name: &str,
) -> SqlxResult<i64> {
neocrates::sqlx_fetch_scalar!(
&mut *conn,
sqlx::query_scalar::<_, i64>(
"INSERT INTO users (name) VALUES ($1) RETURNING id"
).bind(name)
).await
}
async fn create_profile(
conn: &mut sqlx::PgConnection,
user_id: i64,
bio: &str,
) -> SqlxResult<()> {
neocrates::sqlx_execute!(
&mut *conn,
sqlx::query("INSERT INTO profiles (user_id, bio) VALUES ($1, $2)")
.bind(user_id).bind(bio)
).await?;
Ok(())
}
// Scenario A: standalone call (no transaction)
async fn register_simple(pool: &SqlxPool, name: &str) -> SqlxResult<i64> {
let mut conn = pool.acquire().await?;
create_user(&mut conn, name).await
}
// Scenario B: multiple service calls inside one transaction
async fn register_with_profile(
pool: &SqlxPool,
name: &str,
bio: &str,
) -> SqlxResult<i64> {
neocrates::sqlx_with_tx!(pool, |tx| {
let user_id = create_user(&mut *tx, name).await?;
create_profile(&mut *tx, user_id, bio).await?;
Ok(user_id)
}).await
}
```
---
### 6. Migrations
```rust
use sqlx::migrate::Migrator;
use std::path::Path;
let migrator = Migrator::new(Path::new("./migrations")).await?;
pool.run_migrations(&migrator).await?;
```
Place migration files in `./migrations/`, named like `0001_create_users.sql`, `0002_add_profiles.sql`, etc.
---
### 7. SQL Logging
```rust
use neocrates::sqlxhelper::logging::{set_sql_logging, is_sql_logging_enabled};
// Enable at app startup (disable in production if desired)
set_sql_logging(true);
if is_sql_logging_enabled() {
println!("SQL logging is on");
}
```
- **Default**: enabled in debug builds (`cfg!(debug_assertions)`), disabled in release
- **Log format**: includes the call site (file, line, column) and the full SQL string
- **Log target**: `sql` — can be filtered independently via tracing subscriber
Example output:
```
INFO sql: [src/services/user.rs:42:5]
---
## Gotchas
| Macros return Futures | All `sqlx_*!` macros return a Future — always append `.await` |
| `&mut *tx` reborrow | Inside a transaction block use `&mut *tx`, not `&mut tx` |
| Prefer `sqlx_with_tx!` | Use `begin()` only when you need savepoints or conditional commit logic |
| Rollback semantics | In `sqlx_with_tx!`, a rollback failure is `warn!`-logged and never replaces the original error |
| Timezone | Every connection automatically runs `SET TIME ZONE 'UTC'` on connect |
---
## Roadmap
1. Builder-style pool configuration (instead of only constructor args / env vars)
2. Per-query timing and Prometheus metrics integration
3. Savepoint / nested transaction support
4. Support for non-PostgreSQL backends