---
title: "Database"
description: "Every app has its own Postgres schema, queried with Drizzle ORM v1."
canonical_url: "https://docs.lightdrop.cloud/guides/database"
---
# Database

> Every app has its own Postgres schema, queried with Drizzle ORM v1.

## Schema

Declare tables with `pgTable` and no schema. The app's database role uses its own schema as
`search_path`, so `todos` resolves to `app_todos.todos`.

```ts [server/db/schema.ts]
import { boolean, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'

export const todos = pgTable('todos', {
  id: serial().primaryKey(),
  title: text().notNull(),
  done: boolean().notNull().default(false),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
})
```

## Migrations

```bash
pnpm db:generate
```

Drizzle Kit writes a folder per migration to `drizzle/`. Commit it. Migrations run with the app's
own role on every deploy, and on `nuxt dev` whenever `drizzle/` changes. Apps without Nuxt or Nitro
get the same from [`wervt dev`](https://docs.lightdrop.cloud/api/platform-contract#wervt-dev).

## Queries

```ts
import { useDb } from '@wervt/nuxt/server'
import { desc, eq } from 'drizzle-orm'
import { todos } from '../db/schema'

const open = await useDb()
  .select()
  .from(todos)
  .where(eq(todos.done, false))
  .orderBy(desc(todos.createdAt))
```

`useDb()` returns a Drizzle client for `DATABASE_URL`, which wervt sets for each app. Don't create
your own clients.

## Running SQL

```bash
wervt db sql todos "select count(*) from todos"
wervt db sql todos --file report.sql --json
wervt db sql todos --preview pr-12 "select * from todos limit 5"
```

One statement, as the app's own role, so it reaches the app's schema and nothing else. It runs
read-only in a transaction that is rolled back, with a 10 second timeout and at most 500 rows
returned. The dashboard's Database page runs the same queries.

To change data, pass `--write`. The statement is committed:

```bash
wervt db sql todos --write "update todos set done = true where id = 3"
```

Only admins and the app's owner and members can run SQL. Each statement shows up in
`wervt audit` with its command, row count and whether it could write, never with its text.

## Seeding

Example and fixture data lives in a [task](https://docs.lightdrop.cloud/guides/tasks) named `db:seed`, not in migrations:

```ts [server/tasks/db/seed.ts]
import { defineTask, seedTable } from '@wervt/nuxt/server'
import { todos } from '../../db/schema'

export default defineTask({
  meta: { description: 'Fill an empty database with example todos' },
  async run() {
    const inserted = await seedTable(todos, [
      { title: 'Try the app', done: true },
      { title: 'Deploy to wervt' },
    ])
    return { result: { todos: inserted } }
  },
})
```

`seedTable(table, rows)` only inserts while the table is empty and returns how many rows it
inserted, so the task can run any number of times. For anything more involved, write the checks
yourself with `useDb()`; keep the task idempotent either way.

**Locally**, `nuxt dev` runs `db:seed` once, right after it created and migrated a new database:
on the first start, or after `wervt delete <app> --drop-database` against the local stack. While
the dev server is running, run it again with:

```bash
pnpm exec nuxt task run db:seed
```

To seed by hand only, turn the automatic run off:

```ts [nuxt.config.ts]
export default defineNuxtConfig({
  modules: ['@wervt/nuxt'],
  wervt: { dev: { seed: false } },
})
```

**In production**, nothing seeds by itself: `db:seed` never runs on deploy, and `@wervt/nuxt`
removes it from `nitro.scheduledTasks` with a warning. It is an ordinary task, so it shows up on
the app's **Tasks** page and runs on request, for example to fill a new app with starter data:

```bash
wervt tasks my-app --run db:seed
```

## Extensions

Apps share a few Postgres extensions. They are installed once for all apps, so their types and
functions work in every app's schema without a `CREATE EXTENSION` of its own (which an app's role
couldn't run anyway):

<table>
<thead>
  <tr>
    <th>
      Extension
    </th>
    
    <th>
      For
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        vector
      </code>
    </td>
    
    <td>
      Embeddings and nearest-neighbour search (<a href="https://github.com/pgvector/pgvector" rel="nofollow">
        pgvector
      </a>
      
      )
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        pg_trgm
      </code>
    </td>
    
    <td>
      Fuzzy text search: <code>
        similarity()
      </code>
      
      , <code>
        %
      </code>
      
      , trigram indexes
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        unaccent
      </code>
    </td>
    
    <td>
      Search that ignores accents
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        citext
      </code>
    </td>
    
    <td>
      Case-insensitive text columns
    </td>
  </tr>
</tbody>
</table>

```ts [server/db/schema.ts]
import { index, pgTable, serial, text, vector } from 'drizzle-orm/pg-core'

export const notes = pgTable(
  'notes',
  {
    id: serial().primaryKey(),
    body: text().notNull(),
    embedding: vector({ dimensions: 1024 }),
  },
  (table) => [index().using('hnsw', table.embedding.op('vector_cosine_ops'))],
)
```

```ts
import { cosineDistance } from 'drizzle-orm'

const distance = cosineDistance(notes.embedding, queryEmbedding)
const nearest = await useDb().select({ id: notes.id, distance }).from(notes).orderBy(distance).limit(5)
```

- Don't write `CREATE EXTENSION` into migrations, and don't qualify the types: they resolve
through the role's `search_path` (`app_<name>, extensions`).
- An app deployed before extensions existed gets them with its next deploy.
- Leave `vector` columns out of [shapes](https://docs.lightdrop.cloud/guides/live-data) (`columns`): clients have no use for
embeddings, and they are large.
- Self-hosted: `vector` needs a Postgres image that has pgvector (`pgvector/pgvector`, as in the
stack's compose files); the others are part of Postgres.

## Isolation

<table>
<thead>
  <tr>
    <th>
      Each app's role…
    </th>
    
    <th>
      
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      owns its schema <code>
        app_<name>
      </code>
    </td>
    
    <td>
      create tables, migrate
    </td>
  </tr>
  
  <tr>
    <td>
      can connect to the <code>
        wervt
      </code>
      
       database only
    </td>
    
    <td>
      not <code>
        postgres
      </code>
      
      , not <code>
        template1
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      has no access to <code>
        public
      </code>
      
       or other apps' schemas
    </td>
    
    <td>
      <code>
        permission denied
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      can use the shared <code>
        extensions
      </code>
      
       schema
    </td>
    
    <td>
      not create or change anything in it
    </td>
  </tr>
  
  <tr>
    <td>
      can't create schemas
    </td>
    
    <td>
      except while migrating
    </td>
  </tr>
  
  <tr>
    <td>
      has at most 10 connections
    </td>
    
    <td>
      
    </td>
  </tr>
  
  <tr>
    <td>
      cancels statements after 30 s
    </td>
    
    <td>
      <code>
        statement_timeout
      </code>
      
      , not while migrating
    </td>
  </tr>
  
  <tr>
    <td>
      ends sessions idle in a transaction after 60 s
    </td>
    
    <td>
      <code>
        idle_in_transaction_session_timeout
      </code>
    </td>
  </tr>
</tbody>
</table>

Role passwords are derived from a server secret (HMAC of the app name), so they are never stored.

Apps of accounts that aren't admins have a size limit (1 GB unless an admin changes it): deploys
warn from 80 % and are refused past it, while the running release keeps working. See
[Limits](https://docs.lightdrop.cloud/guides/private-apps#limits).

## Connection pooling

In production, apps reach Postgres through a connection pooler in transaction mode: a connection
belongs to the app only for the duration of one transaction (or one statement outside of one).
Prepared statements and transactions work as usual. Session state doesn't carry over between
statements: use `set local` inside a transaction instead of `set`, transaction-level advisory
locks (`pg_advisory_xact_lock`) instead of session-level ones, and no `listen`.

## Browsing the data

The dashboard's **Database** page lists the app's tables with their rows and structure, and runs
SQL. It connects as the app's own role, so it sees exactly what the app sees, and every query runs
read-only: one statement at a time, in a transaction that is rolled back, stopped after 10 seconds.
It uses two of the role's 10 connections while open.


## Sitemap

See the full [sitemap](https://docs.lightdrop.cloud/sitemap.md) for all pages.
