> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ooneex.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Connect to PostgreSQL, SQLite, or Redis behind a small set of adapters with TypeORM repositories and a Bun Redis client.

The `@ooneex/database` component is the connection layer for relational and key-value stores. PostgreSQL and SQLite adapters extend `TypeormDatabase` and hand you a typed TypeORM `Repository` per entity; `RedisDatabase` wraps Bun's native Redis client. Every adapter exposes the same lifecycle — `open`, `close`, `drop` — and registers with the container through `@decorator.database()`.

## Why this component

* **One lifecycle, several engines.** `open`, `close`, and `drop` mean the same thing whether you target Postgres, SQLite, or Redis.
* **Typed repositories.** `open(Entity)` returns a TypeORM `Repository<Entity>` with full query-builder and entity-manager access.
* **Connection reuse.** Adapters cache their `DataSource` and only initialize it once, so repeated `open` calls share a single pool.
* **Container-managed.** Register an adapter with `@decorator.database()` and resolve it from the container for dependency injection.
* **Config from the environment.** Connection details come from `AppEnv` (`DATABASE_URL`, `DATABASE_REDIS_URL`, `SQLITE_DATABASE_PATH`) or from explicit options.

## How it works

You pick (or scaffold) an adapter and register it. Relational adapters extend the abstract `TypeormDatabase` and implement a single `getSource()` that builds a TypeORM `DataSource`; the base class handles initialization, repositories, transactions, and teardown. The Redis adapter constructs a Bun `RedisClient` from a connection URL.

| Method                            | Engine            | Purpose                                                                  |
| --------------------------------- | ----------------- | ------------------------------------------------------------------------ |
| `open<Entity>(entity, database?)` | Postgres / SQLite | Initialize the `DataSource` if needed and return a `Repository<Entity>`. |
| `open()`                          | Redis             | Connect (if not already) and return the Bun `RedisClient`.               |
| `close()`                         | All               | Destroy the `DataSource` / close the Redis connection.                   |
| `drop()`                          | All               | Drop the database schema (`FLUSHDB` for Redis). Destructive.             |
| `getEntityManager(database?)`     | Postgres / SQLite | Return the TypeORM `EntityManager` for transactions.                     |
| `getSource(database?)`            | Postgres / SQLite | Build (and cache) the underlying `DataSource`.                           |
| `getClient()`                     | Redis             | Return the underlying Bun `RedisClient`.                                 |

The built-in adapters differ in the engine they target, not in how you call them:

| Adapter                 | Engine                                | Best for                                         | Returns from `open`  |
| ----------------------- | ------------------------------------- | ------------------------------------------------ | -------------------- |
| `TypeormPgDatabase`     | PostgreSQL (TypeORM)                  | Production relational data, connection pooling   | `Repository<Entity>` |
| `TypeormSqliteDatabase` | SQLite via `better-sqlite3` (TypeORM) | Local development, embedded and analytics stores | `Repository<Entity>` |
| `RedisDatabase`         | Redis (Bun client)                    | Caching, sessions, ephemeral key-value data      | `RedisClient`        |

For vector search and embeddings, a separate `VectorDatabase` adapter ships in `@ooneex/rag` and is scaffolded with `vector-database:create` (see the [CLI command](#cli-command) below).

`synchronize` defaults to `false` on every relational adapter — schema changes go through migrations, not auto-sync. Adapters cache their `DataSource`, so call `close()` when you are done to release the pool.

## Environment variables

| Variable               | Adapter                 | Required                      | Purpose                                                                                                                               |
| ---------------------- | ----------------------- | ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `DATABASE_URL`         | `TypeormPgDatabase`     | Yes (unless passed in config) | Postgres connection string, e.g. `postgres://user:pass@localhost:5432/app`. Missing throws `DatabaseException` (`CONNECTION_FAILED`). |
| `SQLITE_DATABASE_PATH` | `TypeormSqliteDatabase` | Yes (unless passed in config) | Path to the SQLite database file, e.g. `var/db`. Missing throws `DatabaseException` (`CONNECTION_FAILED`).                            |
| `DATABASE_REDIS_URL`   | `RedisDatabase`         | Yes (unless passed in config) | Redis connection string, e.g. `redis://localhost:6379`. Missing throws `DatabaseException` (`CONNECTION_FAILED`).                     |

These map to the `database.url`, `database.sqlite.path`, and `database.redis.url` keys in the app's env configuration.

```bash theme={null}
DATABASE_URL=postgres://user:pass@localhost:5432/app
SQLITE_DATABASE_PATH=var/db
DATABASE_REDIS_URL=redis://localhost:6379
```

## Usage

A relational adapter implements `getSource()` and inherits the lifecycle. Resolve it from the container, then `open()` per entity to get a repository:

```typescript theme={null}
import { container } from "@ooneex/container";
import { UserEntity } from "@/entities/UserEntity";
import { AppDatabase } from "@/databases/AppDatabase";

const database = container.get(AppDatabase);

// Initialize the DataSource (once) and get a typed repository
const userRepository = await database.open(UserEntity);

const users = await userRepository.find({ take: 100, order: { createdAt: "DESC" } });
const user = await userRepository.findOneBy({ id: "123" });

await database.close();
```

Use the entity manager for transactions:

```typescript theme={null}
const userRepository = await database.open(UserEntity);
const manager = userRepository.manager;

await manager.transaction(async (tx) => {
  const user = await tx.findOneBy(UserEntity, { id: "123" });
  const order = tx.create(OrderEntity, { userId: user.id, total: 99.99 });
  await tx.save(order);
});
```

`RedisDatabase` hands you Bun's native client:

```typescript theme={null}
import { container } from "@ooneex/container";
import { CacheRedis } from "@/databases/CacheRedis";

const redis = container.get(CacheRedis);

const client = await redis.open();
await client.set("session:123", JSON.stringify(session));
const value = await client.get("session:123");

await redis.close();
```

## Decorator and usage

### `@decorator.database()`

Registers a database adapter with the container. It accepts an optional scope (defaults to singleton). Use it on an adapter that extends `TypeormDatabase` (Postgres/SQLite) or `RedisDatabase`.

```typescript theme={null}
import { DataSource } from "typeorm";
import { TypeormDatabase, DatabaseException, decorator } from "@ooneex/database";
import { inject } from "@ooneex/container";
import { AppEnv } from "@ooneex/app-env";
import { SharedModule } from "@module/shared/SharedModule";

@decorator.database()
export class AppDatabase extends TypeormDatabase {
  public constructor(@inject(AppEnv) private readonly env: AppEnv) {
    super();
  }

  public getSource(_database?: string): DataSource {
    if (this.source) {
      return this.source;
    }

    const url = this.env.DATABASE_URL ?? "";

    if (!url) {
      throw new DatabaseException(
        "Database URL is required. Please provide a URL either through the constructor options or set the DATABASE_URL environment variable.",
        "CONNECTION_FAILED",
      );
    }

    this.source = new DataSource({
      synchronize: false,
      entities: SharedModule.entities,
      extra: { max: 10 },
      url,
      type: "postgres",
    });

    return this.source;
  }
}
```

Resolve it from the container and inject it where needed:

```typescript theme={null}
import { inject } from "@ooneex/container";

export class UserService {
  constructor(@inject(AppDatabase) private readonly database: AppDatabase) {}
}
```

## Exceptions

The component throws `DatabaseException` when an adapter is misconfigured or an operation fails. It carries a machine-readable `key`, a human-readable `message`, a `data` object, and a `500` HTTP status.

| Key                 | When                                                                                                                                       |
| ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `CONNECTION_FAILED` | A required connection setting is missing — no `DATABASE_URL`, `SQLITE_DATABASE_PATH`, or `DATABASE_REDIS_URL` (and none passed in config). |
| `OPERATION_FAILED`  | A Redis `open`, `close`, or `drop` call failed at runtime. The `data` carries the connection URL and underlying error.                     |

```typescript theme={null}
import { DatabaseException } from "@ooneex/database";

try {
  const userRepository = await database.open(UserEntity);
  await userRepository.find();
} catch (error) {
  if (error instanceof DatabaseException) {
    logger.error(`Database error [${error.key}]: ${error.message}`, error.data);
  } else {
    throw error;
  }
}
```

## Best practices

* **Keep `synchronize` off.** Evolve the schema through migrations, not auto-sync, so changes are reviewable and reversible.
* **Reuse the cached source.** Resolve adapters from the container as singletons and let them cache the `DataSource` — don't build a new connection per request.
* **Close what you open.** Call `close()` on shutdown (or when a one-off adapter is done) to release the pool and Redis socket.
* **Guard `drop()`.** It destroys data (`FLUSHDB` on Redis); never call it against a shared or production database.
* **Configure from the environment.** Read connection details from `AppEnv` so the same code runs across dev, test, and production.
* **Use the entity manager for transactions.** Wrap multi-entity writes in `manager.transaction()` so they commit or roll back together.
* **Throw `DatabaseException` with a stable `key`.** In custom adapters, keep keys constant (`CONNECTION_FAILED`, `OPERATION_FAILED`) and put variable detail in `data`.

## CLI command

Scaffold a database adapter and its test file with the generator. It prompts for the engine (`postgres`, `sqlite`, or `redis`), writes the class under `modules/<module>/src/databases/<Name>Database.ts`, generates a matching spec, and installs `@ooneex/database` if it is missing.

```bash theme={null}
# Interactive: prompts for the name and engine
ooneex database:create

# Provide the name
ooneex database:create --name=App

# Target a module and overwrite
ooneex database:create --name=App --module=auth --override
```

| Option       | Description                                                           | Default             |
| ------------ | --------------------------------------------------------------------- | ------------------- |
| `--name`     | Database class name. The `Database` suffix is appended automatically. | Prompted if omitted |
| `--module`   | Target module the class is generated into.                            | `shared`            |
| `--override` | Overwrite an existing class without prompting.                        | `false`             |

The generated SQLite stub starts ready for you to register entities and adjust the `DataSource`:

```typescript theme={null}
import { DataSource } from "typeorm";
import { TypeormDatabase, DatabaseException, decorator } from "@ooneex/database";

@decorator.database()
export class AppDatabase extends TypeormDatabase {
  public getSource(database?: string): DataSource {
    database = database || "var/db";

    this.source = new DataSource({
      synchronize: false,
      entities: [
        // TODO: Load your entities here
      ],
      enableWAL: true,
      busyErrorRetry: 2000,
      busyTimeout: 30_000,
      database,
      type: "sqlite",
    });

    return this.source;
  }
}
```

For vector search, `ooneex vector-database:create` scaffolds a `VectorDatabase` adapter (from `@ooneex/rag`) with an embedding model and Arrow schema. See [database:create](/cli/commands/database-create) and [vector-database:create](/cli/commands/vector-database-create) for the full command references.

## Use with Claude and Codex

The generator ships matching `database:create` and `database:migrate` skills. They run the scaffold and then guide your AI agent through completing the adapter — registering entities, configuring the `DataSource`, and applying migrations. Initialize the skills once for your agent:

<Tabs>
  <Tab title="Claude">
    ```bash theme={null}
    ooneex claude:init
    ```

    Then ask Claude in natural language — it maps the request to the generator, runs it, and fills in the implementation:

    ```text Prompt icon="terminal" wrap theme={null}
    Create a PostgreSQL database connection for the app.
    ```
  </Tab>

  <Tab title="Codex">
    ```bash theme={null}
    ooneex codex:init
    ```

    Then ask Codex in natural language — it maps the request to the generator, runs it, and fills in the implementation:

    ```text Prompt icon="terminal" wrap theme={null}
    Create a PostgreSQL database connection for the app.
    ```
  </Tab>
</Tabs>

For example, the prompt above maps to `database:create --name=App` with the `postgres` engine, then registers your entities on the `DataSource`.