---
title: Database
description: The Database Effect service - the only way platform code talks to PostgreSQL, with scoping helpers, transactions, and a test layer.
---

`@africaos/database` is the single gateway to PostgreSQL. Applications never build a Postgres client themselves - they depend on `Database` and the runtime decides which implementation they get. This page documents the service, its helpers, and the conventions for writing queries.

### The service

The `Database` service is defined in `platform/database/src/service.ts`. Its value *is* the SQL client (`SqlClient.SqlClient`), so consuming code writes queries with the familiar tagged-template style:

```ts
const rows = yield* Effect.flatMap(Database, (sql) => sql`SELECT * FROM products`);
```

Two layers provide the implementation:

| Layer | Backing | When used |
| --- | --- | --- |
| `Database.Live` | A real Postgres client from the validated `DATABASE_URL`, sharing the process-wide pool. | Production and development. |
| `Database.Test` | An in-memory PGlite database, loaded lazily. | Tests - no Postgres server needed, and the WASM engine never ships in production bundles. |

### The shared pool

Both Better Auth and `@effect/sql-pg` talk to the same Postgres database. `platform/database/src/pool.ts` provides a single process-wide `pg.Pool` pointed at the validated `DATABASE_URL`, so connection limits stay predictable and connections are not duplicated across the auth provider and the Effect client.

The pool is created lazily on first access (via a `Proxy`), so environments without `DATABASE_URL` never crash at import time. Missing `DATABASE_URL` raises a clear error at first use instead.

### Writing queries

The `sql` client is used with template tags. Parameters are always bound, never interpolated:

```ts
const rows = yield* sql<{ id: string; name: string }>`
  SELECT id, name FROM retail.products
  WHERE organization_id = ${organizationId}
  ORDER BY name
`;
```

The generic type parameter declares the row shape; Effect returns an array of it. Use `.unsafe()` only for closed unions chosen by code, never user input - and always bind values as parameters.

### Tenant scoping - the orgWhere helpers

Africa OS is multi-tenant: every query that touches tenant data must be constrained to the current organization. The scoping helpers in `platform/database/src/scoping.ts` make that structural instead of remembered:

```ts
import { orgWhere, orgScope } from "@africaos/database";

// Constrains to organization_id = <orgId>
const rows = yield* sql`
  SELECT * FROM retail.products
  WHERE ${yield* orgWhere(orgId)}
`;

// Same, but names the table - for joins where organization_id is ambiguous
const rows = yield* sql`
  SELECT * FROM retail.products p
  JOIN retail.product_categories c ON c.id = p.category_id
  WHERE ${yield* orgScope("p", orgId)}
`;
```

`orgWhere(orgId)` expands to `organization_id = <orgId>`; `orgScope("p", orgId)` to `p.organization_id = <orgId>`. Both are Effect programs that require `Database`, so they work naturally inside a server function's `Effect.gen`.

The `orgId` must come from the request runtime (`CurrentOrganization`), never from client input.

### Transactions

Queries that must succeed or fail together are wrapped in `withTransaction` (`platform/database/src/transactions.ts`):

```ts
import { withTransaction } from "@africaos/database";

const organization = yield* withTransaction(
  Effect.gen(function* () {
    // insert organization
    // insert owner membership
    // create owner role
  })
);
```

- `withTransaction(effect)` commits on success and rolls back on failure.
- `withTransactionRollback(effect)` always rolls back - it exists for tests, so a program can exercise the database and undo everything it did.

Nested calls compose through savepoints, so a helper inside a helper stays safe.

### Query hygiene rules

- **Bind values, never interpolate them.** `sql${param}` is the only safe way to pass data.
- **Use `orgWhere`/`orgScope` on every tenant-scoped query.** Forgetting the scope is a data leak across organizations.
- **Read row shapes with a generic.** `sql<Row>()` gives you typed rows without hand-written parsers.
- **Keep transactions minimal.** Wrap only the work that must be atomic; avoid long transactions around unrelated queries.
- **Errors are `SqlError`.** Queries fail on the Effect error channel as `SqlError`; surface them at the boundary where they are actionable.

### The database in tests

`Database.Test` boots an in-memory PGlite. Tests provide it through the platform layers, and migrations are applied against it the same way as against Postgres. Tests that need isolation pass a fresh memo map to the request runtime, so each test gets its own database. See [Testing](/tooling/testing) for the patterns.

### Where the schema lives

The `Database` service only talks to Postgres; the schema itself lives in SQL migrations under `infra/database/migrations`. See [Data model](/database/data-model) and [Migrations](/database/migrations).