Database

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:

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

Two layers provide the implementation:

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:

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:

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):

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 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 and Migrations.