THUNDER Database & Drizzle Guide
Complete guide to managing PostgreSQL databases, Drizzle ORM, schema migrations, Aiven SSL connection handling, and custom CLI automation scripts.
PostgreSQL Datastore
Supports Aiven PostgreSQL, Neon serverless, Supabase, or local PostgreSQL containers seamlessly.
Drizzle ORM
100% TypeScript type safety with zero runtime code generation overhead.
Aiven SSL Fix Built-in
Automatic SSL certificate resolution (rejectUnauthorized: false) for Cloudflare Workers.
Complete CLI Suite
Dedicated status checking, seeding, migration rollback, and database reset commands.
Database CLI Management Commands
Run these commands from the root directory to manage migrations, seed data, or run diagnostics.
Migration & System Health Status
Displays current database migration state, counts applied vs pending migrations, verifies active database connections, and checks SSL handshake stability.
$ pnpm db:status [DB Status] Checking database migration status... [DB Status] Connected to PostgreSQL: postgresql://*****@aiven-db.com:25432/defaultdb [DB Status] Current migration version: 20260729_init [DB Status] Applied migrations: 3 / 3 [DB Status] Pending migrations: 0 [DB Status] System Health: OK
Apply Pending Migrations
Executes all pending Drizzle SQL migration files sequentially inside a database transaction.
$ pnpm db:migrate [DB Migrate] Applying pending migrations... [DB Migrate] Executing migration: 0001_add_rbac_tables.sql [DB Migrate] Migrations completed successfully in 142ms.
Seed Default Roles & Users
Seeds default system roles (Admin, Manager, User), granular permissions, and creates default administrator credentials.
$ pnpm db:seed [DB Seed] Seeding database... [DB Seed] Created roles: admin, manager, user [DB Seed] Assigned 24 RBAC permissions [DB Seed] Default admin created: admin@thunderstack.dev
Roll Back Migrations
Safely rolls back the last applied migration batch, updating migration metadata tables accurately.
$ pnpm db:rollback [DB Rollback] Reverting last migration batch... [DB Rollback] Rolled back: 0001_add_rbac_tables.sql [DB Rollback] Database state restored to version 0000_init.sql
Wipe & Re-initialize Database
Drops all existing database schema tables, re-applies all Drizzle migrations from scratch, and runs the seed script.
$ pnpm db:reset [DB Reset] WARNING: Dropping all database tables... [DB Reset] Schema dropped successfully. [DB Reset] Running pnpm db:migrate... [DB Reset] Running pnpm db:seed... [DB Reset] Database reset complete!
Interactive Command Manual
Launches the interactive CLI manual explaining all available database options, parameters, and flags.
$ pnpm db:help =================================================== ⚡ THUNDER STACK DATABASE UTILITIES MANUAL ⚡ =================================================== Available commands: pnpm db:status - Check migration status & SSL pnpm db:migrate - Apply pending migrations pnpm db:seed - Seed default RBAC roles & admin pnpm db:rollback - Revert last migration batch pnpm db:reset - Clean rebuild database tables pnpm db:generate - Generate Drizzle SQL migration pnpm db:studio - Open visual Drizzle Studio GUI
Database Schema & Models
Core entities defined in server/db/src/schema.ts.
| Table Name | Description | Columns |
|---|---|---|
| users | Core user entity storing profiles, email verification status, and RBAC role assignment. | id, name, email, emailVerified, image, roleId, createdAt, updatedAt |
| roles | RBAC Role definitions (e.g., Admin, Manager, User) supporting system-level locking. | id, name, description, isSystem, createdAt, updatedAt |
| permissions | Granular permission definitions (e.g., users:read, users:write, settings:manage). | id, key, name, description, category |
| role_permissions | Junction table mapping roles to multiple permissions. | roleId, permissionId |
| sessions | Active authentication sessions managed by Better Auth with IP and user-agent tracking. | id, userId, token, expiresAt, ipAddress, userAgent |
| accounts | OAuth provider accounts (Google, GitHub) connected to user profiles. | id, userId, accountId, providerId, accessToken, refreshToken |
| verifications | Email verification tokens and 6-digit OTP codes for passwordless or email auth. | id, identifier, value, expiresAt |
Aiven PostgreSQL SSL Connection Setup
Connecting Cloudflare Workers & Node.js to cloud PostgreSQL databases with SSL verification.
When connecting to cloud PostgreSQL providers such as Aiven PostgreSQL, SSL mode is mandatory. In serverless environments like Cloudflare Workers (wrangler), default SSL CA certificate bundles can cause handshake errors. THUNDER Stack automatically configures the connection driver:
import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";
const pool = new pg.Pool({
connectionString: process.env.DATABASE_URL,
ssl: process.env.DATABASE_URL?.includes("sslmode=require")
? { rejectUnauthorized: false }
: undefined,
});
export const db = drizzle(pool);Production Scenarios & Conflict Resolution
Common challenges faced during production database operations and how to resolve them safely.
1Adding a "NOT NULL" Column to a Table with Existing Data
The Issue: If you add a new column defined as notNull() to an active table that already has rows of data, PostgreSQL will reject the migration with a constraint violation because existing rows cannot accept null values.
How to tackle it:
- Define a default value: Always supply a default fallback directly in your Drizzle schema, e.g.
.default("default_val")or.default(0). - Three-Step Migration (No default):
- Generate a migration adding the column as nullable first.
- Run an SQL update script to populate values for existing rows.
- Generate a final migration altering the column to enforce
NOT NULL.
2Data Loss Risk During Migration Rollbacks
The Issue: Running pnpm db:rollback drops the tables or columns created by the target migration. If this command is executed in staging or production, any data stored in those columns will be permanently deleted.
How to tackle it:
- Execute Backup: Always perform a snapshot backup using database utilities (e.g.,
pg_dump) prior to performing rollbacks. - Avoid Rollbacks in Production: Prefer "roll-forward" actions. If you need to revert a change in production, generate a brand new migration file that safely deletes the column or drops the table, allowing for data migration steps before the drop.
3Database Table Locks & Connection Timeouts
The Issue: Performing schema alterations (like adding foreign keys or indexes) on large tables locks the table. Active API threads attempting to query this table will hang and timeout, exhausting the database connection pool.
How to tackle it:
- Create Indexes Concurrently: For high-traffic tables, edit the generated SQL file manually to apply indexes concurrently:
CREATE INDEX CONCURRENTLY. - Schedule Off-Peak Hours: Apply intensive migrations when database workload is lowest.
4Out-of-Sync Migration Metadata Table
The Issue: If a local migration SQL file is modified after it has already been applied, the database's internal metadata tracking table (drizzle.__drizzle_migrations) will mismatch and block future deployments.
How to tackle it:
- Diagnose Status: Run
pnpm db:statusto isolate exactly which migration checksum is failing. - Manually Adjust Checksum: If the database and schema are physically identical, update the failing checksum row in the
drizzle.__drizzle_migrationstable, or delete the record of the target migration and run `db:migrate` again.
Ready to deploy your frontend & backend?
Check out our complete deployment guide for Vercel, Cloudflare Pages, and Cloudflare Workers.