{"id":28,"type":"docs","title":"Heroku Postgres + Drizzle Operations","slug":"heroku-postgres-drizzle-operations","canonicalUrl":"https://docs.phatysd.me/content/heroku-postgres-drizzle-operations","status":"published","revision":2,"publishedAt":"2026-09-19T16:34:11.292Z","updatedAt":"2026-09-19T16:34:11.292Z","hierarchyPath":"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","canonicalUrl":"https://docs.phatysd.me/content/deleting-seed-data-safely-production"},{"slug":"deployment-runbook-heroku-cloudflare","title":"Deployment Runbook: Heroku + Cloudflare","type":"docs","canonicalUrl":"https://docs.phatysd.me/content/deployment-runbook-heroku-cloudflare"}],"body":"# Heroku Postgres + Drizzle Operations\n\nThis 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.\n\n## Connection model\n\nThe 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.\n\nNever print `DATABASE_URL`. If a connection must be passed to a command, keep it in the environment of that command and redact the output.\n\n## Schema and migrations\n\nThe schema is defined in `src/db/schema.ts`. The primary content tables are:\n\n- `content_items`: current content state, status, metadata, and revision number.\n- `content_revisions`: immutable published snapshots linked to `content_items` with cascade delete.\n\nThe project workflow is:\n\n```bash\nnpm run db:generate\nnpm run db:migrate\n```\n\nGenerate 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.\n\nDrizzle'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.\n\n## Content data versus seed data\n\nMigration 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.\n\nBefore deleting demo data:\n\n1. Confirm the exact target rows by slug, ID, or an explicit seed marker.\n2. Take a backup if the data matters.\n3. Delete in a transaction when multiple tables are involved.\n4. Rely on the foreign-key cascade from revisions only after confirming the schema.\n5. Verify published, draft, and revision counts after the operation.\n\nExample inspection query:\n\n```sql\nSELECT id, type, status, slug, revision_number\nFROM content_items\nORDER BY id;\n\nSELECT content_item_id, count(*)\nFROM content_revisions\nGROUP BY content_item_id\nORDER BY content_item_id;\n```\n\nDo not run `TRUNCATE` or restore over the primary database as a shortcut for removing seed rows. Those operations have a much larger blast radius.\n\n## Backups and restore warnings\n\nFor a small Heroku Postgres database, the official PGBackups workflow is:\n\n```bash\nheroku pg:backups:capture -a <APP>\nheroku pg:backups -a <APP>\nheroku pg:backups:download -a <APP>\n```\n\nA 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.\n\nCredentials can be rotated through the Heroku Postgres workflow. After rotation, restart/release the app and verify the application connection without exposing the new URL.\n\n## Production checklist\n\n- Confirm the target app and database attachment.\n- Confirm the migration or SQL is reviewed.\n- Confirm backups and restore consequences.\n- Check connection SSL.\n- Run the smallest safe transaction.\n- Verify counts and public routes.\n- Check logs for connection errors without logging the URL.\n\n## References\n\n- [Drizzle migrations](https://orm.drizzle.team/docs/migrations)\n- [Drizzle PostgreSQL](https://orm.drizzle.team/docs/get-started-postgresql)\n- [Heroku connecting to Postgres](https://devcenter.heroku.com/articles/connecting-heroku-postgres)\n- [Heroku PGBackups](https://devcenter.heroku.com/articles/heroku-postgres-backups)\n- [Heroku logical backups](https://devcenter.heroku.com/articles/heroku-postgres-logical-backups)\n- Repository sources: `src/db/schema.ts` and `src/lib/db.ts`.\n"}