Databases

Databases back stores. Define one in your project, map it to a path alias in tsconfig.json, and import it wherever you need it.

Available databases

MemoryDB

For development, tests, or ephemeral use, Primate ships a built-in in-memory database:

import memorydb from "primate/memorydb";

const db = memorydb();

MemoryDB requires no configuration and holds no state between instances. It is particularly useful in test suites where you want a clean database per test.

Setting up a database

Create a file for your database configuration and map it to a path alias in tsconfig.json:

// config/db.ts
import postgresql from "@primate/postgresql";

export default postgresql({
  host: "localhost",
  port: 5432,
  database: "myapp",
  username: "myapp",
  password: "myapp",
});

// tsconfig.json
{
  "compilerOptions": {
    "paths": {
      "@/config/db": ["./config/db"]
    }
  }
}

The @/config/db alias is conventional but not required. Primate resolves the aliases configured in compilerOptions.paths; it does not hardcode an alias prefix. @db, @/db, or any other TypeScript path alias works equally well.

Node does not apply tsconfig.json path aliases when you run a module directly outside Primate. For direct Node execution, use a relative import or define a package import whose key starts with # in package.json:

// package.json
{
  "imports": {
    "#db": "./config/db.ts"
  }
}

For direct Node execution, import the database as #db in that module.

Using a database in a store

Import your database and pass it explicitly to the store:

import db from "@/config/db";
import store from "primate/store";
import p from "pema";

export default store({
  table: "post",
  db,
  schema: {
    id: store.key.primary(p.u32),
    title: p.string,
  },
});

See the stores page for the full store API. See Pema and Stores for which schemas can represent persistent fields.

Connection pools

PostgreSQL, MySQL, and MongoDB accept the same optional pool controls:

const db = postgresql({
  database: "myapp",
  pool: {
    max: 10,
    connectTimeout: 5_000,
    idleTimeout: 60_000,
  },
});

Timeouts are in milliseconds. connectTimeout limits establishing a new network connection; it does not limit how long a query can wait for pool capacity. Omitted values preserve each driver's defaults. See the driver page for exact defaults and idleTimeout: 0 behavior.

Use fork() when work needs capacity and lifecycle independent from the store pool, such as a long transaction:

const dedicated = db.fork({ pool: { max: 2 } });

try {
  // dedicated.client, dedicated.sql(), and store operations use another pool
} finally {
  await dedicated.close();
}

The fork privately inherits the endpoint, credentials, and omitted pool controls. Closing it does not close the parent, and closing the parent does not close the fork. Call close() on every fork you create.

Raw SQL

For queries that don't fit the store API — complex joins, aggregates, database-specific functions, or raw DDL — all four SQL drivers (SQLite, MySQL, PostgreSQL, and Oracle) expose db.sql for immediate execution:

import db from "@/config/db";
import p from "pema";

const results = await db.sql({
  query: "SELECT name FROM users WHERE age > :age",
  params: { age: 18 },
  output: p.array({ name: p.string }),
});

Named placeholders such as :age map to params keys and are always passed through the driver's parameter-binding mechanism rather than interpolated into the query. TypeScript enforces that every placeholder has a matching parameter and that no unrelated parameters are supplied. An output schema validates the database result and supplies its inferred return type. The output may use any suitable validation schema, including Pema collections.

Both params and output are optional. A write-only query needs no output and returns void:

await db.sql({
  query: "INSERT INTO users (name, age) VALUES (:name, :age)",
  params: { name: "John", age: 30 },
});

A query with no parameters omits params:

const results = await db.sql({
  query: "SELECT name FROM users",
  output: p.array({ name: p.string }),
});

The string shorthand executes static SQL and deliberately discards its result:

await db.sql("CREATE INDEX idx_users_age ON users (age)");

Each db.sql call is a standalone operation. Writes commit before the returned promise resolves; db.sql does not join a store transaction. If you manage a driver transaction through db.client, use that client consistently for every statement in the transaction rather than mixing in db.sql calls.

Placeholder translation is handled automatically per driver — SQLite uses $name, while MySQL, PostgreSQL, and Oracle use positional parameters. You always write :name and the driver does the rest. Parameter values are raw driver values; use explicit SQL conversion functions or db.client for driver-specific representations and behavior.

Typical project layout

config/
  db.ts               # database configuration
stores/
  Post.ts             # store using @/config/db