aussiegingersnap/db-postgres

PostgreSQL Database Skill

Comprehensive patterns for PostgreSQL database management in Node.js/TypeScript projects using Drizzle ORM, including a versioned migration system and local Docker development.

When to Use This Skill

• Setting up PostgreSQL in a new project
• Writing database migrations
• Adding tables or columns to existing schemas
• Configuring local PostgreSQL with Docker
• Troubleshooting database issues

Core Concepts

PostgreSQL vs SQLite

PostgreSQL is appropriate when:

• Multiple servers need database access
• Remote database inspection is required
• High write concurrency is expected
• Team needs direct database access for debugging
• Complex queries, full-text search, or JSON operations

Naming Conventions

Singular table names are enforced:

• user not users
• session not sessions
• account not accounts

This convention improves readability in code where you reference user.id rather than users.id.

Database Setup Pattern

Package Installation

npm install drizzle-orm postgres
npm install -D drizzle-kit @types/node

Directory Structure

src/lib/db/
├── index.ts      # Connection, migrations, types
├── schema.ts     # Drizzle schema definitions
├── migrate.ts    # Migration runner
└── queries.ts    # Typed query functions (optional)

drizzle/
├── migrations/   # Generated SQL migrations
└── meta/         # Migration metadata

Environment Configuration

# .env.local (development)
DATABASE_URL=postgres://postgres:postgres@localhost:5432/myapp

# Production
DATABASE_URL=postgres://user:password@host:5432/myapp?sslmode=require

Connection Setup

Create src/lib/db/index.ts:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error('DATABASE_URL environment variable is required');
}

// Connection for queries
const queryClient = postgres(connectionString);

// Connection for migrations (with max 1 connection)
const migrationClient = postgres(connectionString, { max: 1 });

export const db = drizzle(queryClient, { schema });
export const migrationDb = drizzle(migrationClient);

export * from './schema';

Drizzle Configuration

Create drizzle.config.ts at project root:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/lib/db/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Schema Patterns

Basic Schema Structure

Create src/lib/db/schema.ts:

import { pgTable, text, timestamp, integer, boolean, uuid, varchar, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Example: Define tables here as needed
// Use singular table names: user, session, account

// Type exports - infer from schema
// export type User = typeof user.$inferSelect;
// export type NewUser = typeof user.$inferInsert;

Schema Conventions

Primary Keys - Use TEXT UUIDs or SERIAL integers:

// UUID primary key (recommended for user-facing entities)
export const user = pgTable('user', {
  id: uuid('id').primaryKey().defaultRandom(),
  // ...
});

// Serial primary key (for internal/junction tables)
export const auditLog = pgTable('audit_log', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  // ...
});

Timestamps - Always include created/updated:

export const user = pgTable('user', {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

Foreign Keys - Use cascading deletes for dependent data:

export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: uuid('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),
  // ...
});

Indexes - Create for frequently queried columns:

export const user = pgTable('user', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('user_email_idx').on(table.email),
}));

Enums - Use PostgreSQL enums for fixed values:

import { pgEnum } from 'drizzle-orm/pg-core';

export const userRoleEnum = pgEnum('user_role', ['admin', 'member', 'guest']);

export const user = pgTable('user', {
  id: uuid('id').primaryKey().defaultRandom(),
  role: userRoleEnum('role').notNull().default('member'),
});

Relations

Define relations for type-safe joins:

export const userRelations = relations(user, ({ many }) => ({
  sessions: many(session),
  accounts: many(account),
}));

export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, {
    fields: [session.userId],
    references: [user.id],
  }),
}));

Migration System

Generating Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Generate with custom name
npx drizzle-kit generate --name add_user_table

Running Migrations

Create src/lib/db/migrate.ts:

import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { migrationDb } from './index';

async function runMigrations() {
  console.log('Running migrations...');
  
  await migrate(migrationDb, {
    migrationsFolder: './drizzle/migrations',
  });
  
  console.log('Migrations complete');
  process.exit(0);
}

runMigrations().catch((err) => {
  console.error('Migration failed:', err);
  process.exit(1);
});

Add to package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "tsx src/lib/db/migrate.ts",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Migration Workflow

1. Modify schema in src/lib/db/schema.ts
2. Generate migration: npm run db:generate
3. Review the generated SQL in drizzle/migrations/
4. Apply migration: npm run db:migrate

Key Rules

1. Never modify existing migrations - They may have already run in production
2. Always review generated SQL - Drizzle generates migrations automatically
3. Use db:push for prototyping - Syncs schema without migrations (dev only)
4. Commit migrations - They're part of your codebase

Query Patterns

Basic CRUD

import { db, user } from '@/lib/db';
import { eq, and, or, desc, asc } from 'drizzle-orm';

// Insert
const newUser = await db.insert(user).values({
  email: '[email protected]',
  name: 'John Doe',
}).returning();

// Select one
const foundUser = await db.query.user.findFirst({
  where: eq(user.email, '[email protected]'),
});

// Select many with conditions
const users = await db.query.user.findMany({
  where: and(
    eq(user.role, 'member'),
    eq(user.active, true)
  ),
  orderBy: desc(user.createdAt),
  limit: 10,
});

// Update
await db.update(user)
  .set({ name: 'Jane Doe', updatedAt: new Date() })
  .where(eq(user.id, userId));

// Delete
await db.delete(user).where(eq(user.id, userId));

With Relations

// Fetch user with sessions
const userWithSessions = await db.query.user.findFirst({
  where: eq(user.id, userId),
  with: {
    sessions: true,
  },
});

// Nested relations
const userFull = await db.query.user.findFirst({
  where: eq(user.id, userId),
  with: {
    sessions: true,
    accounts: {
      columns: {
        provider: true,
        providerAccountId: true,
      },
    },
  },
});

Transactions

await db.transaction(async (tx) => {
  const [newUser] = await tx.insert(user).values({
    email: '[email protected]',
  }).returning();
  
  await tx.insert(session).values({
    id: generateSessionId(),
    userId: newUser.id,
    expiresAt: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
  });
});

Local Development with Docker

See docker-local skill for complete Docker Compose setup.

Quick start:

# Start PostgreSQL
docker compose up -d postgres

# Run migrations
npm run db:migrate

# Open Drizzle Studio
npm run db:studio

Troubleshooting

"Connection refused"

Ensure PostgreSQL is running:

docker compose ps
docker compose logs postgres

"Relation does not exist"

Migrations haven't run:

npm run db:migrate

Type errors with schema

Regenerate types:

npx drizzle-kit generate

Connection pool exhausted

For serverless environments, use connection pooling:

const queryClient = postgres(connectionString, {
  max: 10, // Adjust based on your needs
  idle_timeout: 20,
  connect_timeout: 10,
});

References

• references/drizzle-boilerplate.md - Complete setup code
• references/migrations.md - Advanced migration patterns