Postgres

Postgres is the default store behind db and one of the drivers behind search. Your app reads and writes records through db.<model>.for(user), and MainSDK routes each record to the Postgres database for its owner's region: US, EU or India. Any Postgres works, whether you run it yourself or use a managed service. With the pgvector extension enabled, the same database also serves vector search, so you don't need a separate search engine for retrieval. Pick Postgres when you want the reference stack, an existing schema, or full control over where your data lives.

db:
  driver: postgres

search:
  driver: pgvector

Before you start

You need a Postgres database for each region your users are in.

1. Create a database per region. Run one in the US, one in the EU and one in India, on your own servers or a managed service. Each record is written to the store for its owner's region, so the databases must actually sit in those regions.
2. Create an app role. Give the app its own database role with the rights your schema needs, rather than the superuser.
3. Copy a connection string for each. A Postgres connection string carries the host, database, role and password, so treat it as a secret. Require TLS on connections that leave your network.
4. For vector search: enable the pgvector extension in each database that holds searchable records (CREATE EXTENSION vector;). Managed services usually list it among their supported extensions; on your own servers you install the package first.

Store each connection string in your secrets manager, never in code or a committed env file.

Configure

data: says where records live; db: says how to reach each region's store and how fields are labelled.

data:
  store: postgres
  residency: { default: eu, by-market: { in: in, us: us } }
db:
  driver: postgres
  regions:
    us: secret:DB_US        # suggested name; connection string for the US store
    eu: secret:DB_EU        # suggested name; connection string for the EU store
    in: secret:DB_IN        # suggested name; connection string for the India store
  fields:
    user.email:    { label: personal }
    patient.notes: { label: phi, encrypt: true }

A literal connection string in main.yaml fails mainsdk check with MAIN-CONFIG-0004. You keep your existing schema and your Prisma or Drizzle migrations; MainSDK adopts them without a rewrite.

For vector search on the same database:

search:
  driver: pgvector

The search interface has no spec yet, so which database and table pgvector reads, and how it gets its connection, are not decided yet.

Use it

Read a user's records. A query without .for(user) or .public() does not compile.

import { auth, db } from "mainsdk";

export async function GET(req: Request) {
  const session = await auth.require(req);
  const orders = await db.orders.for(session.user).list({ limit: 20 });
  return Response.json(orders);
}

Search with pgvector. Results are scoped to the person searching.

import { auth, search } from "mainsdk";

export async function GET(req: Request) {
  const session = await auth.require(req);
  const q = new URL(req.url).searchParams.get("q") ?? "";
  const hits = await search.query({ q, for: session.user, limit: 10 });
  return Response.json(hits);
}

Embeddings for vector search come from ai.embed(). How you pass a vector to search.query() is not decided yet.

Local development and testing

• No database: mainsdk dev can run db on the SQLite local driver, which mainsdk check refuses in production.
• A local Postgres: to test your real schema and migrations, run Postgres on your machine or in a container and point each region secret at it in development.
• Tests: createTestApp() from @mainsdk/testing runs db on its in-memory fake, and chaos.on("db", { outage: true }) simulates the database going down.

import { it } from "node:test";
import assert from "node:assert/strict";
import { createTestApp } from "@mainsdk/testing";
import { db } from "mainsdk";

it("db.AC2 refuses another user's order", async () => {
  createTestApp({ main: "./main.yaml" });
  const order = await db.orders.for({ id: "u1" }).create({ total: 4900 });
  await assert.rejects(db.orders.for({ id: "u2" }).get(order.id), { code: "MAIN-DATA-0007" });
});

What MainSDK adds on top

• No query without an owner scope; another user's record returns 403 with MAIN-DATA-0007.
• Each record stored in its owner's region; a join across regions fails the check with MAIN-DATA-0013.
• Personal fields carry a label, encrypt: true fields are envelope-encrypted through keys, and card numbers are never stored.
• A repeated idempotency key on create or update never writes twice.
• Search results scoped to the person searching.

See Data and records and Search and vector search for the full contracts.

Limits and notes

• Postgres is the owner's default db driver; the db spec's own example uses Neon and will be updated.
• The postgres driver comes with the db spec (phase 1). pgvector is a planned search driver for phase 2, and search has no spec yet.
• Each driver declares the regions it supports and whether it offers row-level security in its capability profile.
• SQL built from strings fails the check. db.raw() returns the driver's client for Postgres features db does not wrap; every use is logged and listed by mainsdk check.
• Backups are your provider's job. Declare your targets with backup; for self-hosted Postgres, see pgBackRest.

Swap to or from it

db:
  driver: neon   # was postgres

FAQ

Do I have to rewrite my schema to use MainSDK? No. db adopts an existing Postgres schema and your Prisma or Drizzle migrations; mainsdk adopt adds labels and owner scope.

Do I need three databases? Records live in their owner's region, and db keeps one store per region, so you configure a connection for each region under db.regions.

Do I need a separate vector database? No. Enable pgvector in Postgres and set search.driver: pgvector.

Next

• Data and records and Search and vector search
• Neon, Supabase and SQLite
• pgBackRest and AWS Backup
• Swap a driver

Open questions