npm package: @matteuccimarco/slim-pg
Description: PostgreSQL client with SLIM serialization - store JSON as compact SLIM format
Author: Marco Matteucci
Latest version: 0.1.0

# SLIM-PG

PostgreSQL client with SLIM serialization - store JSON data in compact format, reduce storage and improve performance.

## Why SLIM-PG?

- **40-50% smaller** than JSON for structured data
- **Human-readable** stored format (unlike binary formats)
- **Lossless** round-trip conversion
- **Drop-in replacement** for pg client
- **Automatic encoding/decoding** of specified columns

## Installation

```bash
npm install @matteuccimarco/slim-pg
```

## Quick Start

```typescript
import { createSlimPg } from '@matteuccimarco/slim-pg';

// Create client
const db = createSlimPg('postgres://user:pass@localhost:5432/mydb');

// Insert with automatic SLIM encoding
await db.insert('users', {
  name: 'Alice',
  data: { preferences: { theme: 'dark', notifications: true } }
});
// The 'data' column is stored as SLIM: preferences(theme:dark,notifications:+)

// Query with automatic SLIM decoding
const users = await db.find('users', 'name = $1', ['Alice']);
// users[0].data is automatically decoded to { preferences: { theme: 'dark', notifications: true } }

// Close connection
await db.close();
```

## API

### Creating a Client

```typescript
import { createSlimPg, SlimPg } from '@matteuccimarco/slim-pg';

// Using connection string
const db = createSlimPg('postgres://...');

// Using options
const db = new SlimPg({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'user',
  password: 'pass',
  slimColumns: ['data', 'metadata', 'config'], // Columns to encode/decode
  storeAsText: true, // Store as TEXT (default) or keep JSONB
});
```

### Insert Operations

```typescript
// Single insert
await db.insert('table', {
  name: 'Test',
  data: { key: 'value' }  // Auto-encoded to SLIM
});

// Batch insert
await db.insertMany('table', [
  { name: 'A', data: { x: 1 } },
  { name: 'B', data: { x: 2 } },
]);
```

### Query Operations

```typescript
// Find all
const rows = await db.find('users');

// Find with condition
const rows = await db.find('users', 'active = $1', [true]);

// Find one
const user = await db.findOne('users', 'id = $1', [123]);

// Raw query
const result = await db.query('SELECT * FROM users WHERE age > $1', [18]);
```

### Update Operations

```typescript
await db.update(
  'users',
  { data: { updated: true } },  // Auto-encoded
  'id = $1',
  [123]
);
```

### Delete Operations

```typescript
const deletedCount = await db.delete('users', 'id = $1', [123]);
```

### Statistics

```typescript
const stats = db.getStats();
console.log(stats);
// {
//   queriesExecuted: 10,
//   rowsEncoded: 5,
//   rowsDecoded: 8,
//   bytesBeforeEncoding: 1250,
//   bytesAfterEncoding: 780,
//   compressionRatio: 0.376  // 37.6% savings
// }

db.resetStats();
```

## Column Configuration

By default, these columns are automatically encoded/decoded:
- `data`
- `metadata`
- `payload`
- `content`
- `json_data`
- `slim_data`

Customize with:

```typescript
const db = createSlimPg({
  connectionString: 'postgres://...',
  slimColumns: ['my_json_column', 'config', 'settings']
});
```

Or per-query:

```typescript
await db.query(
  'SELECT * FROM table',
  [],
  { slimColumns: ['custom_column'] }
);
```

## Schema Recommendations

For best results, store SLIM data as TEXT:

```sql
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  data TEXT,  -- SLIM-encoded data
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index for full-text search on SLIM data (optional)
CREATE INDEX idx_documents_data ON documents USING gin(to_tsvector('english', data));
```

## Compression Examples

| Data Type | JSON Size | SLIM Size | Savings |
|-----------|-----------|-----------|---------|
| User profile | 245 bytes | 152 bytes | 38% |
| Config object | 512 bytes | 298 bytes | 42% |
| API response | 1.2 KB | 720 bytes | 40% |
| Nested structure | 2.8 KB | 1.6 KB | 43% |

## Part of SLIM Ecosystem

- **slim-protocol-core** - Core encoder/decoder
- **@matteuccimarco/slim-db** - Embedded document database
- **@matteuccimarco/slim-iot-logger** - IoT sensor logging
- **@matteuccimarco/slim-pg** - PostgreSQL integration (this package)

## License

MIT