---
title: "Database"
description: "Every app has its own Postgres schema, queried with Drizzle ORM v1."
canonical_url: "https://wervt.app/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.

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

## 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't create schemas
    </td>
    
    <td>
      except while migrating
    </td>
  </tr>
  
  <tr>
    <td>
      has at most 10 connections
    </td>
    
    <td>
      
    </td>
  </tr>
</tbody>
</table>

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

## 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://wervt.app/sitemap.md) for all pages.