# Database & Prisma

A schema change is never just a schema change. Add one field to a model and three other things are now stale: the client types your services compile against, the runtime validation that guards your contract boundary, and the migration that has to reach the database before any of it works. Do those in separate tools and each one is a separate keep-in-sync step — and when an AI agent is writing the backend, each separate step is another turn where the pieces can drift apart. NetScript's answer is to make the `.prisma` file the single source: **one edit, one `netscript db generate`, and the Deno-runtime typed client and the matching zod schemas regenerate together**, with the migration created and applied by the same `netscript db` workflow.

NetScript's persistence layer is **Prisma 7 over a driver adapter** (Postgres is the recommended engine, with MSSQL and MySQL adapters shipped alongside), generated for the Deno runtime and provisioned for you by **Aspire**. Every plugin contributes its own `.prisma` models, which are aggregated into a single generated client over one primary datasource.

![Per-plugin .prisma schema files (root ExampleRecord, workers, sagas, triggers, auth) under database/postgres/schema/plugins are aggregated by netscript db generate into a single Deno-runtime Prisma client and matching zod schemas; the client talks to one datasource through a selected driver adapter — postgres, mssql, or mysql.](https://rickylabs.github.io/netscript/netscript/assets/diagrams/database-schema-aggregation.svg)

*One generated client, many contributors. Each plugin's .prisma file is aggregated into the same datasource; an adapter (postgres / mssql / mysql) selects the driver underneath.*

> Aspire is step 2 — before any db command
>
> Every
>
> netscript db
>
> command talks to Postgres
>
> through Aspire
>
> . In the default layout Postgres is a container that
>
> Aspire
>
> provisions, so
>
> cd aspire && aspire start
>
> must be up
>
> before
>
> you run
>
> netscript db init
>
> . Run a
>
> db
>
> command against a stopped AppHost and it fails with
>
> aspire start failed: … Project file does not exist
>
> . That is the dependency, not a bug — bring orchestration up first, then run the db workflow from the workspace root. See
>
> Orchestration with Aspire
>
> for why.

> Use this when
>
> Reach for the database layer when you need
>
> durable, relational state
>
> — records that survive restarts, are queried with a typed client, and are shared across your service and its plugins. The engine is polyglot: pick it at scaffold time with
>
> --db
>
> — Postgres (the recommended engine; or
>
> mysql
>
> /
>
> mssql
>
> /
>
> sqlite
>
> ). For
>
> ephemeral or high-throughput
>
> state (counters, locks, work queues, scheduled triggers) reach for
>
> KV, queues & cron
>
> instead; the scaffold uses both — Postgres for records, Redis/KV for execution state. Postgres can
>
> also
>
> back the work queue itself — see
>
> the Postgres queue backend
>
> below.

## Learn → / Do →

- **Learn** — the [Team Workspace tutorial, step 03](https://rickylabs.github.io/netscript/tutorials/workspace/03-workspace-data/) wires workspace data through the database from scratch.
- **Do** — the [Use a second database](https://rickylabs.github.io/netscript/data-persistence/how-to/use-a-second-database/) recipe adds a second adapter-backed datasource (MSSQL or MySQL) beside the primary Postgres.

## How persistence is wired

The scaffold engine is chosen with the `--db` flag — `netscript init my-app --db postgres|mysql|mssql|sqlite`. Postgres is the recommended engine (and what every tutorial uses); `mysql`, `mssql`, and `sqlite` are first-class alternatives. `postgres` / `mysql` / `mssql` provision an Aspire container; `sqlite` is file-backed with **no** Aspire container resource. The `--db postgres` scaffold lays down a `database/postgres/` workspace (a different engine lays down `database/<engine>/`). A few facts are worth internalizing before you run anything, because they differ from a typical single-file Prisma setup.

```text
my-app/
├── appsettings.json              # NetScript.Databases.postgres — Engine/Mode/DatabaseName (the real config)
├── netscript.config.ts           # databases.config is intentionally EMPTY here
└── database/postgres/
    ├── schema/
    │   ├── schema.prisma          # root: generator client (runtime="deno"), generator zod, datasource db
    │   └── plugins/               # per-plugin .prisma models aggregated here
    │       ├── workers/workers.prisma
    │       ├── sagas/sagas.prisma
    │       ├── triggers/triggers.prisma
    │       └── auth/auth.prisma   # appears once the auth plugin is added (better-auth backend)
    ├── prisma.config.ts           # schema dir + migrations path + datasource url
    ├── scripts/                   # migrate.ts, seed.ts, generate-zod.ts, fix-zod-imports.ts, …
    └── schema/.generated/         # appears after `db generate`: Prisma client + zod schemas
```

- **The datasource is driven by `appsettings.json`, not `netscript.config.ts`.** In the scaffold, `netscript.config.ts` has `databases: { config: [] }` (empty). The connection details live in `appsettings.json` under `NetScript.Databases.postgres` — `Engine: "Postgres"`, `Mode: "Container"`, a generated `DatabaseName`, and a persistent `DataPath` of `.data/postgres`, with `PrimaryDatabase: "postgres"`. Aspire reads that block to provision the container.
- **One datasource, many contributors.** There is a single primary Postgres datasource. Each first-party plugin ships its own `database/<plugin>.prisma`, and those models are aggregated under `database/postgres/schema/plugins/<plugin>/`. After adding `workers`, `sagas`, and `triggers`, their tables (for example `model JobDefinition` from workers) join the root `ExampleRecord` model in the same database — one `db init` / `db generate` cycle covers the app and its plugins together.
- **The client is generated for Deno.** `schema.prisma` sets `generator client` with `runtime = "deno"` and `output = "./.generated"`, plus a `generator zod` (prisma-zod-generator) emitting matching zod schemas to `./.generated/zod`. You get a type-safe client *and* runtime validation schemas from one generate.

## Headline API: a model and a query

Define models in Prisma schema files, then query them through the generated Deno client. The root `schema.prisma` ships a single sample model, `ExampleRecord`; plugin schema files add their own. The tabs below show a model alongside the typed query you'd write against the generated client.

```prisma
// database/postgres/schema/schema.prisma
generator client {
  provider = "prisma-client"
  output   = "./.generated"
  runtime  = "deno"
}

generator zod {
  provider = "prisma-zod-generator"
  output   = "./.generated/zod"
}

datasource db {
  provider = "postgresql"
  // The datasource URL is resolved by prisma.config.ts (POSTGRES_URI / DATABASE_URL),
  // not declared here.
}

model ExampleRecord {
  id        String   @id @default(uuid())
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
```

```ts
// Import the Deno-runtime client generated by `netscript db generate`.
import { PrismaClient } from '../database/postgres/schema/.generated/client.server.ts';

const prisma = new PrismaClient();

// Insert a record, then read it back — fully typed off the model above.
const created = await prisma.exampleRecord.create({
  data: { name: 'first' },
});

const recent = await prisma.exampleRecord.findMany({
  orderBy: { createdAt: 'desc' },
  take: 10,
});

console.log(created.id, recent.length);
```

> Generate after every schema change
>
> The generated client under
>
> database/postgres/schema/.generated/
>
> is a build artifact. Edit a
>
> .prisma
>
> file and your code is still typed against the
>
> old
>
> shape until you re-run
>
> netscript db generate
>
> . Generate after every schema change, and re-run
>
> netscript db migrate
>
> to create and apply the matching migration.

## The database workflow

With `aspire start` up (Postgres reachable), run the public `netscript db` commands from the workspace root in this order. The first three are the create-and-fill cycle; `status` confirms it landed and `migrate` evolves the schema later.

```bash
# Create the first migration from the current Prisma schema and apply it.
# --name labels the migration directory (here: a migration called "init").
netscript db init --name init
```

```bash
# Generate the Deno-runtime Prisma client (and the zod schemas) into
# database/postgres/schema/.generated so your code can import typed models.
netscript db generate
```

```bash
# Run the workspace seed scripts (database/postgres/scripts/seed.ts)
# to populate baseline rows.
netscript db seed
```

```bash
# Show migration / tooling status: which migrations are applied and
# whether the database is in sync with the schema.
netscript db status
```

```bash
# Later, when the schema evolves: create + apply the next migration.
# Edit a .prisma file, then:
netscript db migrate --name add_orders
netscript db generate    # re-generate the client against the new shape
```

## Plugins contribute models

Adding a first-party plugin installs its Prisma models to the *same* datasource — there is no second database. The aggregated tables land under `database/postgres/schema/plugins/<plugin>/` and migrate in the same `db init`/`migrate` cycle as your app.

**Plugin-contributed Prisma models (same primary datasource)**

| Name | Type | Description |
| --- | --- | --- |
| `workers` | `@netscript/plugin-workers` | Job and schedule definitions (for example JobDefinition) backing the durable background-job runtime. |
| `sagas` | `@netscript/plugin-sagas` | Durable saga state — saga_runtime_state / saga_runtime_transition / saga_runtime_correlation — when the saga store backend is prisma (vs kv). See Durable sagas. |
| `triggers` | `@netscript/plugin-triggers` | Trigger definitions and delivery bookkeeping for the inbound webhook/ingest runtime. |
| `auth` | `@netscript/plugin-auth` | auth.prisma: auth_users / auth_sessions / auth_accounts / auth_verifications — used by the better-auth backend. (kv-oauth stores sessions in KV; WorkOS is stateless.) |

> auth.prisma is backend-specific
>
> The
>
> auth plugin
>
> contributes
>
> auth.prisma
>
> with
>
> auth_users
>
> ,
>
> auth_sessions
>
> ,
>
> auth_accounts
>
> , and
>
> auth_verifications
>
> . Those tables are the persistence shape for the
>
> better-auth
>
> backend. The default
>
> kv-oauth
>
> backend keeps sessions in
>
> KV
>
> and
>
> WorkOS
>
> is stateless (sealed cookie), so they do not depend on these tables. Run the auth migration only when you select the better-auth backend via
>
> NETSCRIPT_AUTH_BACKEND
>
> .

> Postgres can also back the work queue
>
> Persistence is not the only place Postgres shows up. The queue runtime now ships a
>
> PostgreSQL backend
>
> alongside Deno KV, Redis, and RabbitMQ — a durable, row-claimed (
>
> FOR UPDATE SKIP LOCKED
>
> ) queue table in the same Postgres you already provision. It is selectable only via an
>
> explicit
>
> provider: 'postgres'
>
> (auto-discovery never picks it). See
>
> KV, queues & cron
>
> for
>
> connection.postgres.{url, tableName}
>
> and the full backend matrix.

## Adapter selection — postgres, mssql, mysql

Prisma 7 talks to the database through a **driver adapter**. NetScript wraps each driver in a small `DatabaseAdapter` that gives every backend the same lifecycle surface (`getDriverAdapter` → `setClient` → `connect`/`disconnect`/`healthCheck`/`getStatus`). The recommended scaffold is Postgres, but MSSQL and MySQL ship as sub-exports so you can target — or add — a second engine without changing how your code consumes the client.

The shared options shape comes from `DatabaseConnectionOptions` (`@netscript/database/ports`): every adapter accepts either a `connectionString` or the structured `host` / `port` / `database` / `username` / `password` / `ssl` / `poolSize` / `timeout` parts, plus a few engine-specific extras.

**Driver adapters (each implements DatabaseAdapter)**

| Name | Type | Description |
| --- | --- | --- |
| `adapters/postgres` | `createPostgresAdapter(opts)` | PostgreSQL via @prisma/adapter-pg. Re-exported from the @netscript/database/adapters barrel. PostgresConnectionOptions adds schema and applicationName. Builds a connection string under the hood. |
| `adapters/mssql` | `createMssqlAdapter(opts)` | SQL Server via @prisma/adapter-mssql. Import from @netscript/database/adapters/mssql (sub-export — NOT in the barrel). MssqlConnectionOptions adds instanceName, trustServerCertificate, encrypt, integratedSecurity, applicationName, connectTimeout, requestTimeout. Accepts ADO.NET-style connection strings. |
| `adapters/mysql` | `createMysqlAdapter(opts)` | MySQL 8.x / MariaDB via @netscript/prisma-adapter-mysql (a native-Deno driver). Import from @netscript/database/adapters/mysql (sub-export). MysqlConnectionOptions adds charset, timezone, connectionLimit, multipleStatements. |

**DatabaseAdapter surface (every adapter)**

| Name | Type | Description |
| --- | --- | --- |
| `provider` | `'postgres' \| 'mssql' \| 'mysql'` | Readonly provider identity used in status reporting. |
| `getDriverAdapter()` | `→ driver adapter` | Returns the Prisma driver adapter to pass as `new PrismaClient({ adapter })`. Lazily constructs it from the options. |
| `setClient(client)` | `(client) => void` | Hand the constructed PrismaClient back to the adapter so connect / health / raw calls have a client to use. |
| `getClient()` | `→ PrismaClient` | Return the configured Prisma client. Throws if setClient() has not been called — see the callout below. |
| `connect() / disconnect()` | `→ Promise` | Explicit $connect / $disconnect. Prisma auto-connects on first query, so these are optional. |
| `healthCheck()` | `→ Promise` | Lightweight `SELECT 1` probe; true when the connection answers. |
| `getStatus()` | `→ Promise` | Snapshot: { connected, provider, database, host, lastConnected, error }. |
| `executeRaw() / executeRawUnsafe()` | `(query, ...params) => Promise` | Raw query escape hatches routed through the configured client. |

> getClient() needs setClient() first
>
> The adapter does not own the
>
> PrismaClient
>
> — you construct it. The flow is always: build the adapter, call
>
> getDriverAdapter()
>
> , pass that to
>
> new PrismaClient({ adapter })
>
> , then call
>
> adapter.setClient(client)
>
> . Only then does
>
> adapter.getClient()
>
> (and
>
> connect
>
> /
>
> healthCheck
>
> /
>
> executeRaw
>
> ) work — call
>
> getClient()
>
> before
>
> setClient()
>
> and it throws
>
> "… client not initialized. Call setClient() …"
>
> .

## Tracing — Prisma OpenTelemetry spans

Prisma query spans are wired through a lightweight tracing helper exported from `@netscript/database/tracing`. It is a drop-in for `@prisma/instrumentation` that avoids the CJS-heavy `@opentelemetry/instrumentation` dependency (which breaks Deno bundle/compile). Call `enablePrismaTracing()` **once, before you construct any Prisma client**, and engine spans dispatch into your OpenTelemetry tracer with the standard W3C `traceparent`.

**@netscript/database/tracing — PrismaTracingConfig + hooks**

| Name | Type | Description |
| --- | --- | --- |
| `enablePrismaTracing(config?)` | `(PrismaTracingConfig) => void` | Register the tracing helper. Call once, before creating any PrismaClient. |
| `config.tracerProvider` | `PrismaTracingProvider` | Tracer provider to use. Defaults to the globally registered OpenTelemetry provider. |
| `config.ignoreSpanTypes` | `(string \| RegExp)[]` | Span-name patterns to drop (string match or regex). Defaults to []. |
| `disablePrismaTracing()` | `() => void` | Clear the global tracing helper. |
| `isPrismaTracingEnabled()` | `() => boolean` | True when a tracing helper is currently registered. |
| `enableInstrumentation()` | `() => boolean (@netscript/database)` | Convenience toggle from the package root: enables Prisma OTEL instrumentation only when OTEL_DENO=true; returns whether it was enabled. |

```ts
// services/orders/src/tracing.ts
import { enablePrismaTracing } from '@netscript/database/tracing';

// Call ONCE, before constructing any PrismaClient. Engine spans then dispatch
// into the globally registered OpenTelemetry tracer with a W3C traceparent.
enablePrismaTracing({
  // tracerProvider defaults to the global provider; override only if you manage
  // your own. ignoreSpanTypes drops noisy internal spans by name or regex.
  ignoreSpanTypes: ['prisma:client:serialize', /detect_platform/],
});

// ... now construct the client (see the adapter example below).
import { PrismaClient } from '../database/postgres/schema/.generated/client.server.ts';
const prisma = new PrismaClient();
```

```ts
// Alternatively, gate instrumentation on the OTEL_DENO flag from the package root.
import { enableInstrumentation } from '@netscript/database';

// Returns true only when OTEL_DENO=true and wiring succeeded.
const tracing = enableInstrumentation();
console.log('prisma tracing on:', tracing);
```

## Runnable example: wiring a second database (MySQL)

The adapters let you stand up a **second** datasource beside the primary Postgres — for example a reporting MySQL instance — without leaving the typed-client model. The pattern is the same for every engine: create the adapter, pass `getDriverAdapter()` into a `PrismaClient`, then `setClient()` so the adapter can manage lifecycle and health. Swap the import to `adapters/mssql` and `createMssqlAdapter` for SQL Server — the surface is identical. The MySQL adapter is a sub-export (not in the `adapters` barrel), so import it from its own path.

```ts
// services/reporting/src/db.ts
// MySQL ships as a sub-export — import from /adapters/mysql, not the barrel.
import { createMysqlAdapter } from '@netscript/database/adapters/mysql';
import { PrismaClient } from '../database/mysql/schema/.generated/client.server.ts';

// 1) Build the adapter from structured parts (or pass { connectionString }).
const adapter = createMysqlAdapter({
  host: Deno.env.get('MYSQL_HOST') ?? 'localhost',
  port: 3306,
  database: 'reporting',
  username: Deno.env.get('MYSQL_USER') ?? 'root',
  password: Deno.env.get('MYSQL_PASSWORD'),
  ssl: false,
});

// 2) Pass the driver adapter into Prisma, then hand the client back.
export const reporting = new PrismaClient({ adapter: adapter.getDriverAdapter() });
adapter.setClient(reporting);

// 3) Now adapter lifecycle + health work off the same client.
await adapter.connect();
const ok = await adapter.healthCheck(); // SELECT 1
console.log('mysql healthy:', ok, await adapter.getStatus());
```

```ts
// services/reporting/src/db.ts — SQL Server variant (same shape)
import { createMssqlAdapter } from '@netscript/database/adapters/mssql';
import { PrismaClient } from '../database/mssql/schema/.generated/client.server.ts';

const adapter = createMssqlAdapter({
  host: Deno.env.get('MSSQL_SERVER') ?? 'localhost',
  port: 1433,
  database: 'reporting',
  username: 'sa',
  password: Deno.env.get('MSSQL_PASSWORD'),
  // local-dev TLS knobs; tighten for production
  encrypt: true,
  trustServerCertificate: true,
});

export const reporting = new PrismaClient({ adapter: adapter.getDriverAdapter() });
adapter.setClient(reporting);
await adapter.connect();
```

```ts
// Prefer env-driven config? Each engine ships a getter that reads structured
// vars (MYSQL_HOST/…) and falls back to a connection-string env var.
import { createMysqlAdapter } from '@netscript/database/adapters/mysql';
import { getMysqlConfig } from '@netscript/database/adapters/mysql';

// getMysqlConfig() → MysqlAdapterConfig from MYSQL_* or MYSQLDB_URI / DATABASE_URL.
const cfg = getMysqlConfig();
const adapter = createMysqlAdapter({
  host: cfg.hostname,
  port: cfg.port,
  database: cfg.db,
  username: cfg.username,
  password: cfg.password,
});
// (getMssqlConfig / getPostgres equivalents exist per adapter.)
```

> A second datasource is a second schema dir
>
> A second engine is a separate Prisma schema workspace (for example
>
> database/mysql/schema/
>
> ) with its own
>
> generate
>
> output and its own migrations — it does
>
> not
>
> merge into the Postgres aggregation above. Generate and migrate each datasource independently. The full step-by-step lives in the
>
> Use a second database
>
> recipe.

## Endpoints & ports

The database itself isn't an HTTP service you call — it's a Postgres container Aspire provisions and the generated Prisma client connects to. The relevant surfaces are the Aspire resources and the connection-string env vars the workspace resolves.

**Database surface (provisioned by Aspire)**

| Name | Type | Description |
| --- | --- | --- |
| `postgres` | `Aspire resource` | The Postgres container Aspire provisions from appsettings.json NetScript.Databases.postgres. Watch it go green in the dashboard. |
| `redis` | `Aspire resource` | Redis cache — the default `--cache-backend`; Redis-compatible — backing KV/queues. A separate concern from Postgres; see KV, queues & cron. (`garnet` and `deno-kv` are alternative backends.) |
| `https://localhost:18888` | `dashboard` | Aspire dashboard (token printed by `aspire start`) — confirm the postgres resource is healthy. |
| `POSTGRES_URI / DATABASE_URL` | `env` | Connection string resolved by prisma.config.ts and normalized to a URL. Set these yourself under --no-aspire. |

> Production pitfalls
>
> - **Forgetting Aspire.** The most common failure: running `netscript db init` with no `aspire start` up. Start orchestration first, from the `aspire/` folder.
> - **Wrong directory.** Run `aspire start` from `aspire/`, but run the `netscript db` commands from the workspace **root** (or pass `--project-root`).
> - **Stale client after a schema change.** Editing a `.prisma` file without re-running `netscript db generate` leaves your code typed against the old model.
> - **Calling getClient() before setClient().** A second-database adapter throws until you hand it the constructed `PrismaClient` via `setClient()`.
> - **Tracing wired too late.** `enablePrismaTracing()` must run *before* the first `PrismaClient` is constructed, or early query spans are lost.
> - **Docker not running.** Aspire provisions Postgres and Redis as containers; if Docker/Podman is down the `postgres` resource never goes green.

> MSSQL & MySQL are sub-exports
>
> Postgres is the recommended engine and lives in the
>
> @netscript/database/adapters
>
> barrel. The
>
> SQL Server
>
> and
>
> MySQL
>
> adapters are imported from their own sub-paths —
>
> @netscript/database/adapters/mssql
>
> and
>
> @netscript/database/adapters/mysql
>
> — so a Postgres-only app never pulls in the heavier
>
> mssql
>
> / native-Deno MySQL drivers. The MySQL driver itself lives in the standalone
>
> `@netscript/prisma-adapter-mysql`
>
> package.

## How it compares

Backend platforms answer the persistence question differently, and each answer is a real trade-off. **Convex** replaces the relational engine with its own reactive datastore and a TypeScript-declared schema; **Supabase** standardizes on hosted Postgres with a SQL schema. NetScript keeps the engine decision with you and makes the Prisma schema the contract everything else is generated from.

|  | NetScript | Convex | Supabase |
| --- | --- | --- | --- |
| Choice of relational engine (Postgres / MySQL / MSSQL / SQLite) | Yes — `--db` at scaffold time | No — Convex is its own datastore | No — Postgres |
| Schema authored in Prisma files | Yes — aggregated per plugin | No — declared in TypeScript | No — SQL migrations |
| Database runs in your own infrastructure by default | Yes — Aspire-provisioned container, or a server you point at | No — Convex Cloud by default (self-hosting available) | No — hosted Postgres by default (self-hosting available) |

If your product wants a managed reactive datastore, those platforms are built for it. If your product needs to sit on a specific relational engine — an existing MSSQL estate, a Postgres you already operate, a file-backed SQLite for the desktop case — the driver adapters above are the mechanism that keeps your query code identical across all of them.

## Where to go next

This hub is intentionally thin — the full generated API lives in the reference. Pick the lane that matches what you're doing.

[Learn — Workspace data (Track B 03)  Guided tutorial: wire workspace data through Prisma, end to end, in the Team Workspace track.](https://rickylabs.github.io/netscript/netscript/tutorials/workspace/03-workspace-data/)

[Do — Use a second database  Task recipe: add an MSSQL or MySQL datasource beside Postgres with a driver adapter.](https://rickylabs.github.io/netscript/netscript/data-persistence/how-to/use-a-second-database/)

[Do — Database & migration  Task recipe: bring up Postgres with Aspire, then run db init → generate → seed → status → migrate.](https://rickylabs.github.io/netscript/netscript/data-persistence/how-to/database-migration/)

[Look up — @netscript/database reference  The full generated API: ports, the postgres/mssql/mysql adapters, extensions, and tracing.](https://rickylabs.github.io/netscript/netscript/reference/database/)

[Understand — Orchestration with Aspire  Why the AppHost (aspire/apphost.mts) provisions Postgres and Redis, and how the resource graph fits together.](https://rickylabs.github.io/netscript/netscript/explanation/aspire/)

[MySQL — prisma-adapter-mysql  The native-Deno MySQL Prisma driver adapter surface, generated.](https://rickylabs.github.io/netscript/netscript/reference/prisma-adapter-mysql/)

[Durable streams](https://rickylabs.github.io/netscript/netscript/durable-workflows/streams/) [KV, queues & cron](https://rickylabs.github.io/netscript/netscript/data-persistence/kv-queues-cron/)