---
title: "Postgres"
description: "Use Postgres for MainSDK data and vector search - a connection string per region, main.yaml, field labels, pgvector, local testing and swapping."
section: "integrations"
canonical: "https://mainsdk.dev/docs/integrations/postgres"
updated: "2026-10-28"
---

# Postgres

> Use Postgres for MainSDK data and vector search - a connection string per region, main.yaml, field labels, pgvector, local testing and swapping.

Canonical: https://mainsdk.dev/docs/integrations/postgres · Updated: 2026-10-28

Postgres is the default store behind [`db`](https://mainsdk.dev/docs/interfaces/db) and one of the drivers behind [`search`](https://mainsdk.dev/docs/interfaces/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.

## Interfaces it serves

### Data and records (`db`)

- Status: Building for 0.3 · ships as a stable API
- Driver: `postgres` (Postgres, default). owner default; spec to be updated
- Docs: https://mainsdk.dev/docs/interfaces/db.md

```yaml
db:
  driver: postgres
```

Swap to another provider: `neon`, `supabase`, `sqlite`.

### Search (`search`)

- Status: Building for 0.4 · ships as a beta API
- Driver: `pgvector` (pgvector, planned driver). later, with vector search
- Docs: https://mainsdk.dev/docs/interfaces/search.md

```yaml
search:
  driver: pgvector
```

Swap to another provider: `db`, `typesense`, `meilisearch`, `local`, `algolia`.

## 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.

```yaml title="main.yaml"
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:

```yaml title="main.yaml"
search:
  driver: pgvector
```

The `search` interface has no spec yet, so which database and table `pgvector` reads, and how it gets its connection, are [TBD].

## Use it

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

```ts title="app/api/orders/route.ts"
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.

```ts title="app/api/search/route.ts"
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()`](https://mainsdk.dev/docs/interfaces/ai). How you pass a vector to `search.query()` is [TBD].

## Local development and testing

- **No database:** `mainsdk dev` can run `db` on the [SQLite](https://mainsdk.dev/docs/integrations/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.

```ts title="app/api/orders/orders.test.ts"
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`](https://mainsdk.dev/docs/interfaces/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](https://mainsdk.dev/docs/interfaces/db) and [Search and vector search](https://mainsdk.dev/docs/interfaces/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`](https://mainsdk.dev/docs/interfaces/backup); for self-hosted Postgres, see [pgBackRest](https://mainsdk.dev/docs/integrations/pgbackrest).

## Swap to or from it

```yaml title="main.yaml"
db:
  driver: neon   # was postgres
```

> **Warning:** A database is stateful. Changing the driver points MainSDK at a different store; your rows don't move by themselves. MainSDK runs the move as a guided migration job with a rollback, not a silent switch. Moving search off pgvector means re-indexing in the new engine.

## 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](https://mainsdk.dev/docs/interfaces/db) and [Search and vector search](https://mainsdk.dev/docs/interfaces/search)
- [Neon](https://mainsdk.dev/docs/integrations/neon), [Supabase](https://mainsdk.dev/docs/integrations/supabase) and [SQLite](https://mainsdk.dev/docs/integrations/sqlite)
- [pgBackRest](https://mainsdk.dev/docs/integrations/pgbackrest) and [AWS Backup](https://mainsdk.dev/docs/integrations/aws-backup)
- [Swap a driver](https://mainsdk.dev/docs/guides/swap-a-driver)