# @200systems/mf-db-core

**Database abstraction core for the BitBrick microframework**

This package provides the backbone for integrating with relational databases (PostgreSQL or MySQL), offering a unified layer for connection handling, querying, transactions, and migrations.

---

## 🔧 Key Features

- Unified interface for database clients
- Adapter support for PostgreSQL and MySQL (via external factories)
- Full support for transactions
- Strongly-typed migration system
- Configuration validation and health checks
- Granular error handling by operation type

---

## 📦 Installation

```bash
npm install @200systems/mf-db-core
```

> You will also need to install a specific adapter package such as `@200systems/db-postgres` or `@200systems/db-mysql`.

---

## 🚀 Quick Start

```ts
import { DatabaseAdapter } from '@200systems/mf-db-core';

const adapter = new DatabaseAdapter({
  type: 'postgresql',
  host: 'localhost',
  user: 'admin',
  password: 'secret',
  database: 'mydb',
  port: 5432
});

await adapter.initialize();

const users = await adapter.query('SELECT * FROM users WHERE active = $1', [true]);

await adapter.close();
```

---

## 🔄 Transactions

```ts
await adapter.transaction(async (trx) => {
  await trx.query('INSERT INTO logs (event, ts) VALUES ($1, now())', ['create_user']);

  const result = await trx.query<{ id: number }>(
    'INSERT INTO users (email, active) VALUES ($1, $2) RETURNING id',
    ['user@example.com', true]
  );

  const userId = result.rows[0].id;

  await trx.query('INSERT INTO profiles (user_id) VALUES ($1)', [userId]);
});
```

- The transaction will automatically **commit** if successful
- Any thrown error will **rollback** the transaction
- Nested transactions are not natively supported

---

## 🧪 Health Check

```ts
const status = await adapter.healthCheck();

if (status.status !== 'ok') {
  throw new Error(`Database unavailable: ${status.reason}`);
}
```

---

## 🔍 Pool Info (if supported by the adapter)

```ts
const pool = adapter.getClient().getConnectionInfo?.();
if (pool) {
  console.log(`Pool: total=${pool.total}, idle=${pool.idle}, waiting=${pool.waiting}`);
}
```

---

## ⏱ Readiness Check

```ts
if (!adapter.isReady()) {
  console.warn('Adapter is not ready yet.');
}
```

---

## 📦 Architecture Overview

### 🔌 DatabaseAdapter

This class encapsulates adapter selection (PostgreSQL or MySQL), dynamically initializes the correct client, and exposes high-level methods:

- `.initialize()` / `.close()`
- `.query(sql, params)`
- `.transaction(cb)`
- `.healthCheck()`
- `.getClient()` (direct access to `DatabaseClient`)

> The dynamic loading logic (e.g., `PostgresFactory.getInstance`) must be implemented in the actual adapter packages.

### 🧠 `DatabaseClient` Interface

Contract that all database adapters must implement:

```ts
interface DatabaseClient {
  initialize(): Promise<void>;
  close(): Promise<void>;
  query<T>(sql: string, params?: any[]): Promise<QueryResult<T>>;
  transaction<T>(cb: (trx: DatabaseTransaction) => Promise<T>): Promise<T>;
  healthCheck(): Promise<HealthCheckResult>;
  isReady(): boolean;
}
```

### 🔄 `DatabaseTransaction`

Within a transaction, the callback receives a `DatabaseTransaction` instance, allowing you to perform isolated queries.

---

## 📁 Project Structure

```
mf-db-core/
├── src/
│   ├── adapters/              # Generic adapter layer
│   ├── base/                  # Reusable base logic (e.g., transactions)
│   ├── custom-types/          # Utility and internal types
│   ├── error-handling/        # Operation-specific error classes
│   ├── interfaces/            # Public contract definitions
│   └── index.ts               # Entry point
```

---

## 🧬 Migrations

The core defines types for managing and recording schema migrations. You can implement your own `MigrationManager`:

```ts
const manager: MigrationManager = getCustomMigrationManager();

const pending = await manager.getPendingMigrations();

for (const migration of pending) {
  try {
    await manager.applyMigration(migration);
    console.log(`Migration ${migration.version} applied`);
  } catch (err) {
    console.error(`Failed to apply migration ${migration.version}:`, err);
    break;
  }
}
```

Each migration should follow the `Migration` type contract with `up`, `down`, and `version` fields.

---

## 💣 Error Handling

All operations raise specific error classes for easier diagnosis:

- `ConnectionError`
- `QueryError`
- `MigrationError`
- `TransactionError`
- `DatabaseError` (generic base class)

---

## 🧩 Creating a Custom Adapter

Example: PostgreSQL Adapter Factory

```ts
export class PostgresFactory {
  static getInstance(config: DatabaseConfig, logger: Logger): DatabaseClient {
    return new PostgresClient(config, logger);
  }
}
```

Inside `DatabaseAdapter`, you would dynamically import and use this factory depending on the type.

---

## 📝 License

MIT — © 200Systems