--- title: "Data and records" description: "Read and write records that are private to their owner, stored in the owner's region, with labelled and encrypted fields." section: "interfaces" status: "Building for 0.3 · ships as a stable API" spec: "db" canonical: "https://mainsdk.dev/docs/interfaces/db" updated: "2026-10-28" --- # Data and records > Read and write records that are private to their owner, stored in the owner's region, with labelled and encrypted fields. Status: Building for 0.3 · ships as a stable API · Canonical: https://mainsdk.dev/docs/interfaces/db · Updated: 2026-10-28 `db` reads and writes your app's records, scoped to their owner and stored in the owner's region, with labelled fields for personal data and encrypted sensitive ones. ## At a glance - Status: Building for 0.3 · ships as a stable API - Phase: 1 (Core, public beta) - Milestone: 0.3 Every interface - Plan: free in your app (Apache-2.0) - Spec: https://github.com/hubxero/mainsdk/blob/develop/docs/specs/db.md - Category: Data Calls: - `db..for(user).list()` - `db..for(user).get()` - `db..for(user).create()` - `db..for(user).update()` - `db..for(user).delete()` - `db..for(user).restore()` - `db..for(user).trashed().list()` - `db..public()` - `db.transaction()` - `model(schema, options)` - `etag(version)` - `ifMatch(request)` - `preconditionFailed(error)` - `new DbCollection(sql, tenancy, name, fields)` - `new FileCollection(name, file)` - `new LocalCollection(name)` - `openCollection(backend, name, fields)` - `new RuntimeDb(opts)` - `new Outbox(opts)` - `new VersionConflict(currentVersion, fromIfMatch?)` - `spanMs(v)` - `models(defs)` - `fingerprint(sql)` - `queryScope(kind, fn, opts?)` - `trackQueries(fn)` - `parseDbSettings(cfg, opts?)` In main.yaml: ```yaml data: { store: postgres, residency: { default: eu, by-market: { in: in, us: us } } } db: driver: postgres # owner default; the db spec's example uses neon regions: { us: secret:DB_US, eu: secret:DB_EU, in: secret:DB_IN } fields: user.email: { label: personal } patient.notes: { label: phi, encrypt: true } ``` Guarantees, whatever the driver: - No query without an owner scope - Each record stored in its owner's region - Personal fields carry a label; card numbers are never stored - Repeated idempotency keys never write twice ## Drivers Pick one in main.yaml with `db.driver`. | Driver | Id in main.yaml | Default | Note | | --- | --- | --- | --- | | Postgres | `postgres` | yes | | | Neon | `neon` | | | | Supabase | `supabase` | | | | SQLite (local only) | `sqlite` | | | ## Codes | Code | Where | Meaning | | --- | --- | --- | | `MAIN-DATA-0013` | check | A query joins tables across regions | | `MAIN-DATA-0020` | check | A field labelled `pan`: card numbers are never stored | | `MAIN-DATA-0021` | check | A field that holds personal data has no label | | `MAIN-DATA-0030` | check | SQLite local driver in production | | `MAIN-DATA-0040` | check | A statement takes a lock that blocks reads or writes on an existing table while it runs | | `MAIN-DATA-0041` | check | A rename or drop of a column or table that the base ref's code still reads, so the running release breaks during the deploy | | `MAIN-DATA-0042` | check | A new migration for a tool that can't take a lock timeout from the runner doesn't set `lock_timeout` itself | | `MAIN-DATA-0043` | check | An `UPDATE` or `DELETE` with no `WHERE`, or a backfill of a whole table, in a migration; run it as a batched job | | `MAIN-DATA-0044` | check | A migration statement timed out waiting for a lock after every retry; nothing after it was applied | | `MAIN-DATA-0045` | check | Another instance holds the migration lock; this run waited and found every migration already applied | | `MAIN-DATA-0051` | check | A serverless target connects to Postgres without a pooled endpoint, so each instance opens its own connections | | `MAIN-DATA-0055` | check | A `db` call is awaited inside a loop over the results of another query | | `MAIN-DATA-0056` | check | A `db.timeout`, `db.pool` or `db.slow` value is invalid: a request timeout over 30s, a pool `max` over 100, or `slow` not under the request timeout | | `MAIN-DATA-0061` | check | `jobs.run`, `events.publish`, `webhooks.emit`, `email.send` or `notify` called inside a `db.transaction` callback on the module instead of on `tx` | | `MAIN-DATA-0062` | check | A unique index on a model with trash doesn't exclude trashed rows, so a deleted record blocks a new one | | `MAIN-DATA-0065` | check | `trash` on a model with a labelled field is longer than that label's retention, or `trash` isn't a valid duration | | `MAIN-DATA-0070` | check | SQL is built from strings (a template literal or concatenation) | | `MAIN-DATA-0071` | check | A connection string uses a shared admin account such as `root`, `admin` or `postgres` | | `MAIN-DATA-0180` | check | A query filters, sorts or joins on the raw value of a vaulted field, which the database holds only as a token. Fix: use the driver's blind index, or look the person up by an id | | `MAIN-DATA-0181` | check | The vault driver's declared countries (`vault.countries`) miss a country in `markets`. Fix: pick a driver that supports it or drop the market | | `MAIN-DATA-0182` | check | A vaulted field has a label a vault cannot hold, such as `pan`. Fix: use a canonical label; never store card numbers | | `MAIN-DATA-0007` | runtime | Record belongs to another user (403) | | `MAIN-DATA-0008` | runtime | The record isn't there, with tenancy off (404) | | `MAIN-DATA-0009` | runtime | A create repeats an id or a declared unique value (409) | | `MAIN-DATA-0031` | runtime | The local SQLite driver can't start: this Node has no `node:sqlite` (500) | | `MAIN-DATA-0032` | runtime | `db.driver` is not built, or a Postgres driver has no client (400) | | `MAIN-DATA-0050` | runtime | A query ran past its statement timeout and was cancelled (503, not retried) | | `MAIN-DATA-0052` | runtime | No connection was free within `db.pool.acquire` (503 with `Retry-After: 1`) | | `MAIN-DATA-0053` | runtime | A query took longer than `db.slow` (warn log, once a minute per query shape) | | `MAIN-DATA-0054` | runtime | One query shape ran more than `db.repeat` times in one request or job (warn log) | | `MAIN-DATA-0060` | runtime | `ifVersion` doesn't match the record's version (409; 412 from `If-Match`) | | `MAIN-DATA-0063` | runtime | `restore` of a record not in trash, or past its trash period (404) | | `MAIN-DATA-0066` | runtime | A write to a versioned record came with no `If-Match` (428) | | `MAIN-DATA-0064` | runtime | | ## Features | Feature | Phase | Milestone | Spec ticket | | --- | --- | --- | --- | | db@1 contract and field labels | 1 | 0.3 | `db#contract` | | Owner scope enforced by types | 1 | 0.3 | `db#query-scope` | | Region stores for US, EU and India | 1 | 0.3 | `db#regions` | | Cross-region join check | 1 | 0.3 | `db#join-check` | | Field labels enforced | 1 | 0.3 | `db#labels` | | Field encryption through keys | 1 | 0.3 | `db#field-encryption` | | Idempotent writes | 1 | 0.3 | `db#idempotent-writes` | | Postgres, Neon and local SQLite drivers with Prisma and Drizzle | 1 | 0.3 | `db#drivers` | | Residency vaults and tokenisation | 3 | 1.2 | `residency-vaults` | | Pages from list calls with keyset cursors | 1 | 0.3 | `pagination#db-list` | | Delete | 1 | 0.3 | `db-writes#trash` | | Row versions with ETag and If-Match | 1 | 0.3 | `db-writes#versions` | | Query timeouts | 1 | 0.3 | `db-queries` | | Slow-query and repeated-query detection | 1 | 0.3 | `db-queries` | | Safe migrations and mainsdk db migrate | 1 | 0.4 | `db-migrations` | ## MainSDK Cloud Everything on this page is free in your app. ## Quick start Declare each model once at module level with `models`, then query through `.for(user)`. With no `db:` block in `main.yaml`, records go to a local SQLite file, which is fine for development. TypeScript: ```ts import { models, s } from "mainsdk"; export const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); ``` Rust: ```rust use mainsdk::prelude::*; let app = App::test(); Context::new().tenant("tnt_a").user("u1").scope(async { let orders = app.db().model("orders").for_user("u1"); let doc = orders.create(serde_json::json!({ "total": 5 })).await?; assert_eq!(orders.get(&doc.id).await?.map(|d| d.owner), Some("u1".into())); Ok::<_, MainError>(()) }).await?; ``` ```ts title="app/api/orders/route.ts" import { auth, models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export async function GET(req: Request) { const session = await auth.require(req); const page = await orders.for(session.user).list({ limit: 20 }); return Response.json(page); } ``` A query without `.for(user)` or `.public()` does not compile. A user who asks for a record they don't own gets a 403 with code `MAIN-DATA-0007` and a fix. Every record has an `id`, an `owner` and a `createdAt` that MainSDK keeps for you. ## Calls at a glance | Call | What it does | | --- | --- | | `db.orders.for(user).list({ where, order, limit, cursor })` | A page of the user's records | | `db.orders.for(user).get(id)` | One record, or a refusal | | `db.orders.for(user).create(input, { idempotencyKey })` | Add a record | | `db.orders.for(user).update(id, patch, { idempotencyKey, ifVersion })` | Change a record | | `db.orders.for(user).delete(id, { ifVersion })` | Delete, or move to trash | | `db.orders.for(user).restore(id)` | Bring a trashed record back | | `db.orders.for(user).trashed().list()` | The user's trashed records | | `db.products.public().list()` | Read a model declared `public: true` | | `db.transaction(fn)` | Run several calls as one unit | | `model(schema, options)` | A model with `labels`, `encrypt`, `trash`, `versions`, `unique` or `public` | | `etag(version)` and `ifMatch(request)` | Send a record's version as an ETag, and read `If-Match` into `ifVersion` | | `preconditionFailed(error)` | Turn a version conflict into the 412 an `If-Match` request expects | ## Configuration ```yaml title="main.yaml" db: driver: postgres # sqlite (local only) | postgres | neon | supabase regions: # one store per region, each a secret reference us: secret:DB_US eu: secret:DB_EU in: secret:DB_IN fields: patient.name: { label: personal } patient.notes: { label: phi, encrypt: true } # envelope-encrypted through keys models: invoice: { trash: 7d, versions: true } timeout: { request: 5s } slow: 200ms ``` | Key | Type | What it does | | --- | --- | --- | | `db.driver` | string | `sqlite` (the default, local only), `postgres`, `neon` or `supabase`. Any other name fails with `MAIN-DATA-0032` | | `db.path` | string | The SQLite file; the default is `.mainsdk/db.sqlite` | | `db.url` | `secret:` ref | One store for every region; the default is `secret:DATABASE_URL` | | `db.regions` | map of `secret:` refs | A store per region (`us`, `eu`, `in`), instead of `db.url`. A region with no store fails and never falls back to another | | `db.residency` | region | Region for an owner whose region is not known yet; the default is `us` | | `db.fields...label` | label | One of `personal`, `sensitive`, `child`, `phi` or `pan` | | `db.fields...encrypt` | boolean | Encrypts the field with the owner's region key through [`keys`](https://mainsdk.dev/docs/interfaces/keys); needs a label other than `pan` | | `db.models..trash` | duration | Keep deleted records this long so `restore` can bring them back | | `db.models..versions` | boolean | Count versions, so an update or delete must say which version it saw | | `db.models..unique` | list | Unique indexes, each a field or a list of fields | | `db.timeout.request`, `db.timeout.job` | duration | The statement timeout in a request or a job | | `db.pool.max`, `db.pool.acquire`, `db.pool.idle` | number, duration | Connection pool per store | | `db.slow`, `db.repeat` | duration, number | Log a query slower than `slow`, or one fingerprint run more than `repeat` times in one request | You can also set `labels`, `encrypt`, `public`, `trash`, `versions` and `unique` on a model in code with `model(schema, options)`; `main.yaml` adds to the code, it never removes from it. > **Warning:** `pan` is in the label set so that card numbers can be named and refused. A field labelled `pan` throws `MAIN-DATA-0020` when its model is declared and fails `mainsdk check`. Take payments through [`pay`](https://mainsdk.dev/docs/interfaces/pay) instead. `postgres`, `neon` and `supabase` need a client: MainSDK names none, so you pass the `postgres` option to `createApp` as `(url) => ({ pool })` with your own `pg` or Neon pool. Without it the driver fails with `MAIN-DATA-0032`. ### Regions by market Region follows the owner. Pass a user as `{ id, market }` or `{ id, region }` and the record goes to that region's store. A market maps to a region by a fixed rule: `in` to `in`, European markets to `eu`, everything else to `us`. A plain user id uses `db.residency`. See [data and regions](https://mainsdk.dev/docs/concepts/data-and-regions). A `data:` block in `main.yaml` with `residency.by-market` is planned; today the rule above is built in. ## What you get `db` is how your app reads and writes its records. Every query is scoped to an owner, so one user can never read another's orders by accident. Each record is stored in its owner's region (US, EU or India), and fields that hold personal data carry a label, which lets MainSDK enforce residency, retention and erasure for you. Card numbers can't be stored at all, and sensitive fields are encrypted with keys held in your KMS. Reach for it for any data that belongs to a person or an organisation. You describe a record with a schema from [`schema`](https://mainsdk.dev/docs/interfaces/schema), and MainSDK creates the table. ## Examples ### List a user's orders, with a filter and a cursor `where` narrows the list inside the owner's scope. A cursor works only for the query that made it, so pass the same `where`, `order` and `limit` with it. ```ts title="app/api/orders/route.ts" import { auth, models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export async function GET(req: Request) { const session = await auth.require(req); const url = new URL(req.url); const page = await orders.for(session.user).list({ where: { status: "shipped" }, // also { total: { gte: 3 } } or { status: { in: ["a", "b"] } } order: { by: "total", dir: "desc" }, limit: 20, cursor: url.searchParams.get("cursor") ?? undefined, }); return Response.json(page); // { items, next? } } ``` ### Create an order exactly once Pass an idempotency key. A repeated key returns the first result and writes nothing a second time, so a retried form post or a flaky network never creates two orders. ```ts title="app/api/checkout/route.ts" import { auth, models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export async function POST(req: Request) { const session = await auth.require(req); const cart = (await req.json()) as { item: string; total: number }; const idempotencyKey = req.headers.get("idempotency-key") ?? crypto.randomUUID(); const order = await orders.for(session.user).create( { item: cart.item, total: cart.total, status: "pending" }, { idempotencyKey }, ); return Response.json(order, { status: 201 }); } ``` `update` takes the same option: ```ts title="app/api/orders/[id]/cancel/route.ts" import { auth, models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export async function POST(req: Request, { params }: { params: Promise<{ id: string }> }) { const { id } = await params; const session = await auth.require(req); const key = req.headers.get("idempotency-key") ?? undefined; return Response.json( await orders.for(session.user).update(id, { status: "cancelled" }, { idempotencyKey: key }), ); } ``` ### Records in the owner's region You don't pick a store in code. Describe where the owner is, and the record goes to the store for that region; an India user's order lands in `in` and an EU user's order in `eu`. ```ts title="lib/create-order.ts" import { models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export function createOrder(user: { id: string; market: string }, item: string, total: number) { return orders.for({ id: user.id, market: user.market }).create({ item, total }); } ``` A statement that joins tables across regions fails `mainsdk check` with `MAIN-DATA-0013`, and a transaction can't span two stores. Analytics may combine regions only as totals. Moving a user to another region as an audited job is planned. ### Delete, restore and version a record Set `trash` and a deleted record waits that long before it is gone. Set `versions` and every update says which version it saw. ```ts title="lib/invoices.ts" import { model, models, s } from "mainsdk"; export const { invoices } = models({ invoices: model(s.object({ total: s.int() }), { trash: "7d", versions: true }), }); export async function adjust(user: string, id: string) { const mine = invoices.for(user); const inv = await mine.create({ total: 5 }); // inv.version is 1 await mine.update(inv.id, { total: 6 }, { ifVersion: inv.version }); // a stale version throws MAIN-DATA-0060 (409) await mine.delete(inv.id, { ifVersion: "any" }); // "any" is last write wins return mine.restore(inv.id); // while the trash period lasts } ``` `trashed().list()` lists the caller's deleted records, and `upsert(rows, { key })` inserts rows or replaces the owner's row with the same key in one transaction. To run several calls as one unit, wrap them in `db.transaction(async () => { ... })`; it commits when the function returns and rolls back when it throws. The full contract is spec `db-writes`. ### Labelled and encrypted fields Label every field that holds personal data. Add `encrypt: true` and the field is stored as ciphertext with a wrapped data key; the owner reads plain text. Encrypted fields need the [`keys`](https://mainsdk.dev/docs/interfaces/keys) interface to be set up. ```yaml title="main.yaml" db: fields: patient.name: { label: personal } patient.notes: { label: phi, encrypt: true } ``` ```ts title="app/api/patients/[id]/notes/route.ts" import { auth, models, s } from "mainsdk"; const { patient } = models({ patient: s.object({ name: s.string(), notes: s.string() }) }); export async function POST(req: Request, { params }: { params: Promise<{ id: string }> }) { const { id } = await params; const session = await auth.require(req); const { notes } = (await req.json()) as { notes: string }; await patient.for(session.user).update(id, { notes }); // stored encrypted const record = await patient.for(session.user).get(id); return Response.json({ notes: record.notes }); // plain text for the owner } ``` ### Public records A model declared with `public: true` can be read without an owner. Writes still go through an owner. ```ts title="lib/products.ts" import { model, models, s } from "mainsdk"; export const { products } = models({ products: model(s.object({ name: s.string(), price: s.int() }), { public: true }), }); export async function catalogue() { return products.public().list(); // { items, next? } } ``` ## Queries that stay fast Every statement is timed. A request's statements get a timeout (5 seconds by default, 30 at most) set on the server with `SET LOCAL statement_timeout`, so a runaway query is cancelled instead of holding a connection; a job gets 60 seconds. A client that disconnects cancels its running query. At most `db.pool.max` transactions run on a store at once, and one that waits longer than `db.pool.acquire` fails with `MAIN-DATA-0052` and `Retry-After: 1`; while anyone waits, the db shows as degraded in [`health`](https://mainsdk.dev/docs/interfaces/status-page). ```yaml title="main.yaml" db: timeout: { request: 5s, job: 60s } pool: { max: 10, acquire: 2s } slow: 200ms # log a query slower than this, once a minute per query shape repeat: 10 # log one query shape run more than this in one request: the N+1 pattern ``` A slow query is logged as `MAIN-DATA-0053` with its text without values, a fingerprint and the calling line. A handler that loads a list and then one record per item is logged as `MAIN-DATA-0054` with a fix that suggests `where: { id: { in: ids } }`. `mainsdk info --json` shows the limits and the ten slowest fingerprints. ```ts title="app/api/orders/route.test.ts" import { expectQueries } from "@mainsdk/testing"; declare const handler: (req: Request) => Promise; const request = new Request("http://localhost/api/orders"); // Fails with MAIN-DATA-0054 when one query shape runs more than ten times in the handler. await expectQueries(() => handler(request), { repeat: 10 }); ``` `mainsdk check` reports a loop over a query's results that awaits another query (`MAIN-DATA-0055`), invalid limits (`MAIN-DATA-0056`) and, on a serverless target, a Postgres URL that isn't a pooled endpoint (`MAIN-DATA-0051`). ## Migrations that don't block MainSDK doesn't replace Prisma, Drizzle or your SQL files. `mainsdk check` reads the migrations that are new since the base branch (`db.migrations.base`, `origin/develop` by default) and reports statements that would block your running app: `CREATE INDEX` without `CONCURRENTLY`, an added `NOT NULL` column without a default, a type change, a foreign key without `NOT VALID` (`MAIN-DATA-0040`), a rename or a drop of something the base branch still reads (`MAIN-DATA-0041`), a missing `SET lock_timeout` for tools that can't take one (`MAIN-DATA-0042`), and a whole-table `UPDATE` or `DELETE` (`MAIN-DATA-0043`). Each finding names the safe form. ```bash mainsdk db migrate --plan # show what would run, per region store mainsdk db migrate # run it under a lock, with a lock timeout and retries ``` `mainsdk db migrate` runs your tool once per region store under an advisory lock, with a lock timeout and retries from `db.migrations`. A statement that can't get its lock after the last retry stops the run with `MAIN-DATA-0044`; a run that waited for another instance and found nothing left to do says so with `MAIN-DATA-0045`. It prints no connection string or password. ```yaml title="main.yaml" db: migrations: tool: prisma # prisma | drizzle | sql lock-timeout: 5s retries: 5 allow: # an exception needs a reason and an expiry - { file: prisma/migrations/20261027_seed/migration.sql, rule: MAIN-DATA-0043, reason: "12 rows", expires: 2026-12-31 } ``` ## Pages Every list call returns a page: `{ items, next }`. There is no `next` on the last page. Pass `next` back as `cursor` to get the next page. Cursors are keyset based, so a page costs the same however deep it is, and they are bound to the query, the tenant and the user. `limit` is at most 100. To walk every item, use `paginate`: ```ts title="lib/export-orders.ts" import { models, paginate, s } from "mainsdk"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }), }); // declared once in lib/orders.ts, shown inline so the example stands alone export async function allOrders(user: string) { return paginate((o) => orders.for(user).list(o), { limit: 50 }).all(); // or: for await (const order of paginate(...)) } ``` A bad, foreign or expired cursor fails with `MAIN-PAGE-0001`; a `limit` below 1 fails with `MAIN-PAGE-0002`. ## Errors and checks | Code | When | Fix | | --- | --- | --- | | `MAIN-DATA-0007` | A record belongs to another owner (403) | Query with `orders.for(user)` using the user who owns it | | `MAIN-DATA-0008` | The record isn't there (404) | Check the id and the model | | `MAIN-DATA-0013` | A transaction spans two stores, or `mainsdk check` finds a join across regions | Query each region and combine only totals | | `MAIN-DATA-0020` | A field is labelled `pan` | Never store card numbers; use `pay` | | `MAIN-DATA-0021` | `mainsdk check` finds a personal-looking field with no label | Add a label under `db.fields` | | `MAIN-DATA-0030` | SQLite in production | Set `postgres`, `neon` or `supabase` in `main.env.prod.yaml` | | `MAIN-DATA-0032` | The driver is unknown or needs a client you haven't passed | Use a built driver and pass the `postgres` option | | `MAIN-DATA-0050`, `-0052` | A query ran past its timeout, or no connection was free (503) | Lower the query's cost, or raise `db.timeout` or `db.pool.max` | | `MAIN-DATA-0060` | `ifVersion` doesn't match (409) | Read the record again and retry | | `MAIN-DATA-0063` | `restore` of a record not in trash, or past its trash period (404) | Restore within the trash period | | `MAIN-DATA-0009` | A create repeats an id or a unique value (409) | Leave `id` out, or change the unique value | | `MAIN-DATA-0031` | This Node has no `node:sqlite` (500) | Use Node 22.18 or later, or the postgres driver | | `MAIN-DATA-0040` to `-0045` | A migration would block, break the running release, lack a lock timeout, rewrite a whole table, or could not get its lock | Use the safe form in the finding | | `MAIN-DATA-0051`, `-0055`, `-0056` | `mainsdk check` finds a direct Postgres URL on a serverless target, a query in a loop, or an unsafe limit | Use the pooled endpoint, load with one `in` query, fix the setting | | `MAIN-DATA-0053`, `-0054` | A slow query, or a repeated one (logs, not errors) | Add an index, or batch the lookups | | `MAIN-DATA-0061`, `-0062`, `-0065` | `mainsdk check` finds a side effect inside `db.transaction`, a unique index that trash can block, or `trash` past a label's retention | Use `tx.jobs.run`, add `WHERE deleted_at IS NULL`, shorten `trash` | | `MAIN-DATA-0066` | A write to a versioned record came with no `If-Match` (428) | Send the ETag you read, or `If-Match: *` | | `MAIN-DATA-0070`, `-0071` | `mainsdk check` finds SQL built from strings, or a connection string with a shared admin account such as `root` or `postgres` | Use bound parameters or the `sql` tag; create a role with only the rights the app needs | | `MAIN-DATA-0180`, `-0181`, `-0182` | `mainsdk check` finds a query on the raw value of a vaulted field, a vault driver that does not cover a market, or a vaulted field with a label a vault cannot hold | Use the blind index or an id; pick a driver that covers the market; label the field `personal`, `sensitive`, `child` or `phi` | | `MAIN-PAGE-0001`, `-0002` | A bad cursor, or a `limit` below 1 | Start again without a cursor | ```json title="403 response" { "type": "https://mainsdk.dev/errors/MAIN-DATA-0007", "title": "This record belongs to another owner", "status": 403, "code": "MAIN-DATA-0007", "fix": "Read and write records through db..for(user) with the user who owns them", "retryable": false } ``` Error shapes are in [errors](https://mainsdk.dev/docs/reference/errors). ## Swapping drivers Change one line. Your model calls, labels, regions and idempotency stay the same. ```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. Each driver declares the regions it supports and whether it offers row-level security in its capability profile. SQLite is for local development only; it throws `MAIN-DATA-0030` when the app runs with `env: prod`, and `mainsdk check` fails it too. `db.sql()` returns the migration statements for every declared model, and `db.migrate()` creates the tables now (SQLite creates them on first use). ## Testing `createTestApp` gives you an in-memory SQLite store. ```ts title="lib/orders.test.ts" import assert from "node:assert/strict"; import { describe, it } from "node:test"; import { models, s } from "mainsdk"; import { createTestApp } from "@mainsdk/testing"; const { orders } = models({ orders: s.object({ item: s.string(), total: s.int() }) }); describe("orders", () => { it("db.AC2 refuses another user's order", async () => { createTestApp({ main: "./main.yaml" }); const order = await orders.for("u1").create({ item: "tea", total: 4900 }); await assert.rejects(() => orders.for("u2").get(order.id), { code: "MAIN-DATA-0007" }); }); it("db.AC6 writes once for a repeated idempotency key", async () => { createTestApp({ main: "./main.yaml" }); const a = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" }); const b = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" }); assert.equal(b.id, a.id); }); }); ``` ## Security and privacy - No query runs without an owner scope, and the type system enforces it. - Each record lives in its owner's region. Cross-region joins fail the check. - Personal fields carry a canonical label, which drives retention and erasure in [`privacy`](https://mainsdk.dev/docs/interfaces/privacy). Card numbers are never stored. - Encrypted fields use envelope encryption with the owner's region key. With per-user keys, erasing a person also makes their backed-up rows unreadable. - Countries beyond US, EU and India get residency vaults and tokenisation in phase 3 (planned). ## FAQ **Do I have to rewrite my schema?** No. `db` adopts an existing Postgres schema, and your Prisma or Drizzle migrations keep working. See [bring an existing app](https://mainsdk.dev/docs/getting-started/existing-app). **Which region does a record go to before I know the user's market?** The one in `db.residency`, which defaults to `us`. Moving the record later as an audited job is planned. **Can I still run my own SQL?** `db.raw()` is planned and is not built yet. Until then, use your own client for statements the typed calls can't express; `mainsdk check` refuses cross-region joins in any SQL string it finds. **How do backups work?** Through the [`backup`](https://mainsdk.dev/docs/interfaces/backup) interface and restore drills, covered on its own page. ## Reference These are the lower-level pieces behind `db`. Most apps never call them, but they're exported and they explain what MainSDK writes to your database. | Call | What it does | | --- | --- | | `new RuntimeDb(opts)` | The db an app uses: it behaves as `Db`, and `db.transaction` hands its callback a `Tx` whose `jobs`, `events` and `webhooks` write outbox rows. `attach` connects the outbox once the app has built it | | `new Outbox(opts)` | The `mainsdk_outbox` table and its relay (see below). `start()` begins the sweep, `close()` stops it and `settled()` waits for relays that are running, for tests and shutdown | | `openCollection(backend, name, fields)` | Open the collection `name` on a backend: `{ kind: "local" }`, `{ kind: "file", dir }` or `{ kind: "db", sql, tenancy }`. The same name on the same db gives the same table | | `new LocalCollection(name)` | A collection kept in process memory. It keeps no file | | `new FileCollection(name, file)` | A collection saved as one JSON file, rewritten whole after each change through a temporary file and a rename, so a crash never leaves half a file. `reload()` re-reads the file | | `new DbCollection(sql, tenancy, name, fields)` | A collection stored in the table `mainsdk_` of the app's database, in the current tenant's region store | | `new VersionConflict(currentVersion, fromIfMatch?)` | The `MAIN-DATA-0060` error for an `ifVersion` that no longer matches. It carries `currentVersion` and never a field value, and its status is 409, or 412 when the version came from `If-Match` | | `spanMs(v)` | Read a span such as `30d`, `12h` or `7y` (a year is 365 days), or a number of milliseconds, as milliseconds. It returns `undefined` for anything else | | `parseDbSettings(cfg, opts?)` | Read the `db` section of `main.yaml` into settings and a list of issues. A bad value falls back to its default, so a typo never removes a limit. `serverless: true` lowers the default pool to one connection per instance | | `fingerprint(sql)` | The normalised text of a statement, with literals replaced by `?`, and a 16-character hash of it. The result is cached per distinct text | | `queryScope(kind, fn, opts?)` | Run `fn` as work of one kind (such as a request or a job), so its queries get that kind's timeout, stop when `opts.signal` aborts and are counted per fingerprint | | `trackQueries(fn)` | Run `fn` and return `{ result, trace }`, where `trace` lists every fingerprint with its count, the slow queries and the total | ### The outbox `tx.jobs.run`, `tx.events.publish` and `tx.webhooks.send` (and `tx.webhooks.emit`) called inside `db.transaction` write a row to the `mainsdk_outbox` table in the same commit as your data. A rollback leaves nothing behind, and a commit never loses the work. After the commit, the instance relays its rows within `db.outbox.relay` (1 second by default). A sweep every `db.outbox.sweep` (1 minute by default) relays rows that no relay took, so a crash between commit and relay delays the work and never loses it. The row id is the idempotency key of the relayed call, so a relay that runs twice has one effect. Relayed rows are kept for `db.outbox.keep`, 7 days by default. A row that fails its sixth try is marked dead, logged as `MAIN-DATA-0064` and put in the job dead-letter queue. ```ts title="lib/ship-order.ts" import { db, models, s } from "mainsdk"; const { orders } = models({ orders: s.object({ status: s.string() }) }); export async function ship(user: string, orderId: string) { await db.transaction(async (tx) => { await orders.for(user).update(orderId, { status: "shipped" }); await tx.events.publish("order.shipped", { orderId }); // relayed after the commit, once }); } ``` ## Next - [Encryption keys](https://mainsdk.dev/docs/interfaces/keys): how encrypted fields are protected. - [Privacy and data rights](https://mainsdk.dev/docs/interfaces/privacy): retention, export and erasure per label. - [Permissions](https://mainsdk.dev/docs/interfaces/access): share records beyond their owner.