---
id: 28
type: docs
title: "Heroku Postgres + Drizzle Operations"
slug: "heroku-postgres-drizzle-operations"
canonical_url: "https://docs.phatysd.me/content/heroku-postgres-drizzle-operations"
status: published
revision: 2
published_at: "2026-09-19T16:34:11.292Z"
updated_at: "2026-09-19T16:34:11.292Z"
hierarchy_path: "Web Blog > Operations > Database"
topics:
  - "PostgreSQL"
  - "Drizzle ORM"
  - "Heroku"
  - "Database operations"
tags:
  - "postgres"
  - "drizzle"
  - "heroku"
  - "migrations"
  - "backup"
related:
  - slug: "deleting-seed-data-safely-production"
    title: "Deleting Seed Data Safely in Production"
    type: blog
    canonical_url: "https://docs.phatysd.me/content/deleting-seed-data-safely-production"
  - slug: "deployment-runbook-heroku-cloudflare"
    title: "Deployment Runbook: Heroku + Cloudflare"
    type: docs
    canonical_url: "https://docs.phatysd.me/content/deployment-runbook-heroku-cloudflare"
---

# Heroku Postgres + Drizzle Operations

This runbook is for safe schema and content-data operations on the application's Heroku Postgres database. It distinguishes migrations, seed data, content publishing, backups, and destructive restores.

## Connection model

The app reads `DATABASE_URL` and creates a Drizzle client over `postgres-js`. In production, `src/lib/db.ts` enables PostgreSQL SSL unless the connection string explicitly disables it. `prepare: false` is used for compatibility with the hosted connection path.

Never print `DATABASE_URL`. If a connection must be passed to a command, keep it in the environment of that command and redact the output.

## Schema and migrations

The schema is defined in `src/db/schema.ts`. The primary content tables are:

- `content_items`: current content state, status, metadata, and revision number.
- `content_revisions`: immutable published snapshots linked to `content_items` with cascade delete.

The project workflow is:

```bash
npm run db:generate
npm run db:migrate
```

Generate SQL from the reviewed schema change, inspect the generated migration, then apply it to the intended `DATABASE_URL`. Do not use `drizzle-kit push` as a substitute for a reviewed production migration unless the project explicitly adopts that workflow.

Drizzle's migrator records applied migrations and applies pending files. Keep migration files in version control. Make migrations idempotent where practical and avoid combining irreversible data deletion with a schema migration.

## Content data versus seed data

Migration data establishes required structure or invariant reference data. Seed data creates repeatable demo content. The project's `npm run db:seed` script is not a safe production cleanup command.

Before deleting demo data:

1. Confirm the exact target rows by slug, ID, or an explicit seed marker.
2. Take a backup if the data matters.
3. Delete in a transaction when multiple tables are involved.
4. Rely on the foreign-key cascade from revisions only after confirming the schema.
5. Verify published, draft, and revision counts after the operation.

Example inspection query:

```sql
SELECT id, type, status, slug, revision_number
FROM content_items
ORDER BY id;

SELECT content_item_id, count(*)
FROM content_revisions
GROUP BY content_item_id
ORDER BY content_item_id;
```

Do not run `TRUNCATE` or restore over the primary database as a shortcut for removing seed rows. Those operations have a much larger blast radius.

## Backups and restore warnings

For a small Heroku Postgres database, the official PGBackups workflow is:

```bash
heroku pg:backups:capture -a <APP>
heroku pg:backups -a <APP>
heroku pg:backups:download -a <APP>
```

A restore is destructive: the target data is removed before the backup is restored. Treat the restore target as an explicit, reviewed decision. Store downloaded backups securely and delete temporary files when no longer needed.

Credentials can be rotated through the Heroku Postgres workflow. After rotation, restart/release the app and verify the application connection without exposing the new URL.

## Production checklist

- Confirm the target app and database attachment.
- Confirm the migration or SQL is reviewed.
- Confirm backups and restore consequences.
- Check connection SSL.
- Run the smallest safe transaction.
- Verify counts and public routes.
- Check logs for connection errors without logging the URL.

## References

- [Drizzle migrations](https://orm.drizzle.team/docs/migrations)
- [Drizzle PostgreSQL](https://orm.drizzle.team/docs/get-started-postgresql)
- [Heroku connecting to Postgres](https://devcenter.heroku.com/articles/connecting-heroku-postgres)
- [Heroku PGBackups](https://devcenter.heroku.com/articles/heroku-postgres-backups)
- [Heroku logical backups](https://devcenter.heroku.com/articles/heroku-postgres-logical-backups)
- Repository sources: `src/db/schema.ts` and `src/lib/db.ts`.
