# Use a second database

**Goal:** add a *second* database — a second Postgres, or a MySQL/SQL Server instance — to a NetScript workspace that already has its primary datasource, so each datasource gets its own Prisma schema, migrations, and generated client.

This recipe assumes the primary datasource is Postgres (the recommended default — every tutorial scaffolds with `--db postgres`), but the primary engine is itself chosen at scaffold time: pass `--db mysql`, `--db mssql`, or `--db sqlite` to `netscript init` for a MySQL, SQL Server, or file-backed SQLite primary instead. Everything below applies regardless of which engine your primary uses; the examples simply show the common Postgres-primary case.

NetScript's default scaffold gives you **one primary datasource** that every plugin aggregates its `.prisma` models into (see [Database & migration](https://rickylabs.github.io/netscript/data-persistence/how-to/database-migration/)). A second database is the opposite shape: a **separate** Prisma schema workspace with its own `generate` output and its own migration history. It never merges into the primary aggregation.

The second datasource is polyglot the same way the primary is: `netscript db add <engine>` accepts the same four engines as `netscript init` — `postgres`, `mysql`, `mssql`, and `sqlite`. The container-mode engines (`postgres`/`mysql`/`mssql`) are provisioned as an Aspire container resource (`addPostgres` / `addMySql` / `addSqlServer`); `sqlite` is **file-backed and has no Aspire container resource**, so the Aspire/Docker prerequisites below apply only to the container engines.

There are two ways to add one, and they answer different needs:

- **Scaffold it (`netscript db add`)** — when you want NetScript to own the second datasource: its own schema dir, migrations, seed scripts, and an Aspire-provisioned container. This is the recommended path and the bulk of this recipe.
- **Wire an adapter by hand** — when the second database is *external* (a managed MySQL, a reporting warehouse) that you only read/write from application code, with no NetScript-managed migrations. Covered in [Connect an external database by hand](#connect-an-external-database-by-hand).

## Before you start

**Prerequisites**

| Name | Type | Description |
| --- | --- | --- |
| `A scaffolded workspace` | `with a primary db` | An existing NetScript project whose primary datasource is already wired (Postgres, the recommended engine, or whichever engine you passed to netscript init via --db) — ideally migrated once via the Database & migration recipe so you know the single-datasource loop. |
| `netscript CLI` | `on PATH` | deno install --global --allow-all --name netscript jsr:@netscript/cli. netscript db add --help should print. |
| `Aspire CLI + Docker` | `for container mode` | The scaffolded second database is provisioned as a container by Aspire (addMySql / addPostgres / addSqlServer). Docker or Podman must be running. Skip only for an external/hand-wired database. |
| `Deno` | `2.x` | Prisma client generation runs under the Deno runtime (the generated schema sets runtime="deno"). |

> One datasource, or several — pick deliberately
>
> A second database is the right tool when you need an
>
> isolated
>
> data domain: a different engine (MySQL/SQL Server beside Postgres), a separate migration lifecycle, or a datasource you can scale and back up independently. If you only want a new
>
> table
>
> , add a model to the primary schema instead — adding a whole datasource means a second migration history, a second generated client, and a second connection to manage. See
>
> [Database & Prisma](https://rickylabs.github.io/netscript/netscript/data-persistence/database/) for why the default is a single aggregated datasource.

## Step 1 — Scaffold the second database

From the workspace root, run `netscript db add <engine>`. The engine is one of `postgres`, `mysql`, `mssql`, or `sqlite`. The `--name` flag sets the **config key** the datasource is registered under (it defaults to the engine name):

```bash
# Add a MySQL datasource. Registered under NetScript.Databases.mysql,
# scaffolded into database/mysql/, provisioned by Aspire's addMySql (image 8.4).
netscript db add mysql
```

```bash
# A second Postgres for an isolated domain. --name gives it a distinct config
# key (and workspace), so it does not collide with the primary 'postgres'.
netscript db add postgres --name analytics
```

```bash
# SQL Server via Aspire's addSqlServer. Same shape — its own schema + migrations.
netscript db add mssql --name reporting
```

What `netscript db add` does, in one pass:

- **Scaffolds a workspace** at `database/<engine>/` (for `mysql`, `database/mysql/`) — its own `schema/schema.prisma`, `prisma.config.ts`, `scripts/`, and (after generate) `schema/.generated/`.
- **Registers the datasource** in `appsettings.json` under `NetScript.Databases.<configKey>` with the engine, mode, and a generated `DatabaseName` — the same appsettings-driven model the primary Postgres uses.
- **Adds the workspace** as a member of the project so tooling discovers it.
- **Regenerates the Aspire config and AppHost helper files** so the new container (for example the MySQL resource via `addMySql`) joins the resource graph the next time you run `aspire start`.

> The new datasource starts empty
>
> db add
>
> scaffolds the workspace and registers the datasource, but it does
>
> not
>
> run a migration or generate a client — the new
>
> database/mysql/schema/schema.prisma
>
> ships a starter schema and no
>
> .generated/
>
> directory yet. You run the migration loop yourself in Step 3, targeting the new datasource with
>
> --db
>
> .

## Step 2 — Bring the new container up with Aspire

Because `db add` regenerated the Aspire config, the new database becomes a container in the resource graph. Start (or restart) the AppHost so it provisions:

```bash
# database/migration recipe covers this in full — restart so Aspire picks up
# the regenerated config and provisions the new container (e.g. the mysql resource).
cd aspire
aspire start
```

Open the Aspire dashboard at [https://localhost:18888](https://localhost:18888) (the access token is printed by `aspire start`) and confirm the new resource — `mysql`, `analytics`, or whatever your config key is — goes green alongside the existing `postgres` and `redis`.

> If you skipped db add and only edited appsettings
>
> The container only appears because
>
> db add
>
> regenerated the Aspire helpers
>
> . If you register a datasource by hand-editing
>
> appsettings.json
>
> without re-running the generator, the AppHost will not know about it and the resource never shows up. Let the CLI own the appsettings + Aspire wiring — that is the whole point of
>
> db add
>
> .

## Step 3 — Migrate and generate the second datasource

The `netscript db` operations are **multi-database aware**: every one takes a `--db <target>` flag, where the target is a **config key**, a database name, or `all`. With the second database registered under `NetScript.Databases.mysql`, point each command at it with `--db mysql`. Run these from the workspace root, with `aspire start` up in another terminal:

```bash
# Create + apply the first migration for the SECOND datasource only.
# --db selects the config key; --name labels the migration directory.
netscript db init --db mysql --name init
```

```bash
# Generate the Deno-runtime Prisma client + zod schemas into
# database/mysql/schema/.generated for the mysql datasource.
netscript db generate --db mysql
```

```bash
# Run that datasource's seed scripts (database/mysql/scripts/seed.ts).
netscript db seed --db mysql
```

```bash
# Confirm the mysql datasource is migrated and in sync — the authoritative check.
netscript db status --db mysql
```

```bash
# Operate on EVERY registered datasource at once (primary + second).
netscript db migrate --db all --name add_reports
netscript db generate --db all
```

Each datasource keeps its **own** migration history under `database/<engine>/migrations/` and its **own** generated client. Editing the second schema and re-running `netscript db migrate --db mysql` never touches the primary Postgres, and vice versa.

> Omitting --db targets the primary
>
> Run a
>
> db
>
> command with no
>
> --db
>
> and it resolves to the
>
> primary
>
> datasource (the default Postgres). Always pass
>
> --db <configKey>
>
> when you mean the second one, or
>
> --db all
>
> to fan out across every registered datasource. The resolver matches on config key first, then database name.

## Step 4 — Query the second client from application code

After `db generate --db mysql`, the second datasource has its own typed client at `database/mysql/schema/.generated/client.server.ts`. Import it exactly like the primary — just from the new path. The two clients are independent `PrismaClient` instances, so a service can read from both:

```ts
// services/reporting/src/db.ts
// Each datasource generates its OWN client. Import the second one from its path.
import { PrismaClient as ReportingPrisma } from '../database/mysql/schema/.generated/client.server.ts';

export const reporting = new ReportingPrisma();

// Fully typed off the SECOND schema's models — separate from the primary client.
const rows = await reporting.report.findMany({ take: 20 });
console.log(rows.length);
```

```ts
// services/reporting/src/sync.ts
// A service can hold both clients side by side — one per datasource.
import { PrismaClient as AppPrisma } from '../database/postgres/schema/.generated/client.server.ts';
import { PrismaClient as ReportingPrisma } from '../database/mysql/schema/.generated/client.server.ts';

const app = new AppPrisma();
const reporting = new ReportingPrisma();

// Copy a record from the primary Postgres into the MySQL reporting datasource.
const order = await app.exampleRecord.findFirstOrThrow();
await reporting.report.create({ data: { sourceId: order.id, name: order.name } });
```

## Connect an external database by hand

If the second database is **external** — a managed MySQL you do not want NetScript to migrate or provision — skip `db add` and wire a driver adapter in application code. NetScript wraps each Prisma 7 driver in a small `DatabaseAdapter` with a uniform lifecycle (`getDriverAdapter` → `setClient` → `connect`/`healthCheck`/`getStatus`). The MySQL and SQL Server adapters are **sub-exports** (not in the `@netscript/database/adapters` barrel), so a Postgres-only app never pulls in their drivers:

**Second-database adapter factories (each implements DatabaseAdapter)**

| Name | Type | Description |
| --- | --- | --- |
| `createMysqlAdapter(opts)` | `@netscript/database/adapters/mysql` | MySQL 8.x / MariaDB via the native-Deno @netscript/prisma-adapter-mysql driver. MysqlConnectionOptions adds charset, timezone, connectionLimit, multipleStatements over the shared parts. |
| `createMssqlAdapter(opts)` | `@netscript/database/adapters/mssql` | SQL Server via @prisma/adapter-mssql. MssqlConnectionOptions adds instanceName, encrypt, trustServerCertificate, integratedSecurity, connectTimeout, requestTimeout. |
| `createPostgresAdapter(opts)` | `@netscript/database/adapters` | PostgreSQL via @prisma/adapter-pg. The only adapter in the barrel; PostgresConnectionOptions adds schema and applicationName. |

The shared options come from `DatabaseConnectionOptions` (`@netscript/database/ports`): pass a `connectionString`, or the structured `host` / `port` / `database` / `username` / `password` / `ssl` / `poolSize` / `timeout` parts.

```ts
// services/reporting/src/external-db.ts
// 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 to the adapter.
export const reporting = new PrismaClient({ adapter: adapter.getDriverAdapter() });
adapter.setClient(reporting);

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

```ts
// Prefer reading config from the environment? getMysqlConfig() reads structured
// MYSQL_HOST / MYSQL_PORT / MYSQL_DATABASE / MYSQL_USER / MYSQL_PASSWORD vars and
// falls back to a connection-string env var (MYSQLDB_URI, then DATABASE_URL).
import { createMysqlAdapter, getMysqlConfig } from '@netscript/database/adapters/mysql';

const cfg = getMysqlConfig(); // → MysqlAdapterConfig, or throws if nothing is set
const adapter = createMysqlAdapter({
  host: cfg.hostname,
  port: cfg.port,
  database: cfg.db,
  username: cfg.username,
  password: cfg.password,
});
// (getMssqlConfig has the same shape for SQL Server, reading MSSQL_* / MSSQLDB_URI.)
```

```ts
// services/reporting/src/external-db.ts — SQL Server (same lifecycle)
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'),
  encrypt: true,              // local-dev TLS knobs; tighten for production
  trustServerCertificate: true,
});

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

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

## In-production pitfalls

> Watch for these
>
> - **Forgetting `--db`.** A bare `netscript db migrate` targets the **primary** datasource. Always pass `--db <configKey>` (e.g. `--db mysql`) for the second one, or `--db all` to fan out — otherwise you migrate the wrong database.
> - **Aspire not restarted after `db add`.** The new container only joins the resource graph after the regenerated Aspire config is loaded. Restart `aspire start` before the new datasource is reachable.
> - **Stale second client.** Each datasource has its *own* `.generated/`. Editing `database/mysql/schema/schema.prisma` without `netscript db generate --db mysql` leaves your code typed against the old shape — same trap as the primary, once per datasource.
> - **Importing the wrong client.** The primary is `database/postgres/schema/.generated/client.server.ts`; the second is `database/mysql/schema/.generated/client.server.ts`. They are distinct `PrismaClient`s — crossing the imports queries the wrong database.
> - **Calling `getClient()` before `setClient()`** on a hand-wired adapter throws — see the callout above.
> - **Docker down.** A scaffolded (container-mode) second database is provisioned by Aspire; if Docker/Podman is not running, its resource never goes green. An external/hand-wired database does not need this.
> - **Alpha surface.** NetScript packages share the aligned `0.0.1-beta.11` version, and public subpaths can still be renamed. Pin versions and re-check the import paths after upgrades.

## See also

[Database & Prisma — capability hub](https://rickylabs.github.io/netscript/netscript/data-persistence/database/) ·

[Database & migration — the primary-datasource loop](https://rickylabs.github.io/netscript/netscript/data-persistence/how-to/database-migration/) ·

[@netscript/database reference](https://rickylabs.github.io/netscript/netscript/reference/database/) ·

[@netscript/prisma-adapter-mysql reference](https://rickylabs.github.io/netscript/netscript/reference/prisma-adapter-mysql/) ·

[Orchestration with Aspire](https://rickylabs.github.io/netscript/netscript/explanation/aspire/) ·

[KV, queues & cron — the Postgres queue backend](https://rickylabs.github.io/netscript/netscript/data-persistence/kv-queues-cron/)

[Expose OpenAPI & Scalar](https://rickylabs.github.io/netscript/netscript/services-sdk/how-to/expose-openapi-scalar/) [Choose a queue provider](https://rickylabs.github.io/netscript/netscript/data-persistence/how-to/choose-a-queue-provider/)