gh-neondatabase-labs-ai-rules-neon-plugin/skills/neon-drizzle/guides/schema-only.md

Schema Creation and Modification

Complete Walkthrough: This is a self-contained, step-by-step guide with its own numbered phases (Phase 1-6). Follow each phase in order for schema design, modification, and migration workflows.

Guide for creating or modifying database schemas with Drizzle.

Table of Contents

• Workflow Checklist
• Phase 1: Schema Design Patterns
• Phase 2: Common Schema Patterns
• Phase 3: Schema Modifications
• Phase 4: Indexes and Constraints
• Phase 5: Generate and Apply Changes
• Phase 6: Advanced Patterns
• Common Issues
• Next Steps

---

Workflow Checklist

When following this guide, I will track these high-level tasks:

• Design schema using appropriate patterns (tables, relationships, types)
• Apply common schema patterns (auth, soft deletes, enums, JSON)
• Implement schema modifications (add/rename/drop columns, change types)
• Add indexes and constraints for performance and data integrity
• Generate and apply migrations
• Verify changes and test with queries

---

Phase 1: Schema Design Patterns

1.1. Basic Table Structure

import { pgTable, serial, text, varchar, timestamp, boolean } from 'drizzle-orm/pg-core';

export const tableName = pgTable('table_name', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

Key conventions:

• Use serial for auto-incrementing IDs
• Use varchar for short strings (with length limit)
• Use text for long strings
• Use timestamp for dates/times
• Always add createdAt for audit trails

1.2. Relationships

One-to-Many:

import { pgTable, serial, text, timestamp, index } from 'drizzle-orm/pg-core';

export const authors = pgTable('authors', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: serial('author_id')
    .notNull()
    .references(() => authors.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
}, (table) => ({
  authorIdIdx: index('posts_author_id_idx').on(table.authorId),
}));

Important: Always add index on foreign keys for query performance.

Many-to-Many:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
});

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const postsTags = pgTable('posts_tags', {
  postId: serial('post_id')
    .notNull()
    .references(() => posts.id),
  tagId: serial('tag_id')
    .notNull()
    .references(() => tags.id),
}, (table) => ({
  pk: index('posts_tags_pk').on(table.postId, table.tagId),
}));

1.3. Type-Safe Relations

Enable relational queries:

import { relations } from 'drizzle-orm';

export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(authors, {
    fields: [posts.authorId],
    references: [authors.id],
  }),
}));

Benefits:

• Type-safe joins
• Automatic loading of related data
• No manual JOIN queries needed

Phase 2: Common Schema Patterns

2.1. User Authentication

import { pgTable, serial, varchar, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }),
  name: varchar('name', { length: 255 }).notNull(),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  lastLoginAt: timestamp('last_login_at'),
});

2.2. Soft Deletes

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  deletedAt: timestamp('deleted_at'),
  createdAt: timestamp('created_at').defaultNow(),
});

Query with soft deletes:

const activePosts = await db
  .select()
  .from(posts)
  .where(isNull(posts.deletedAt));

2.3. Enums

import { pgEnum, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const statusEnum = pgEnum('status', ['draft', 'published', 'archived']);

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  status: statusEnum('status').default('draft'),
});

2.4. JSON Fields

import { pgTable, serial, jsonb } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  metadata: jsonb('metadata').$type<{
    color?: string;
    size?: string;
    tags?: string[];
  }>(),
});

Phase 3: Schema Modifications

3.1. Adding Columns

Step 1: Update schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  phoneNumber: varchar('phone_number', { length: 20 }), // NEW
});

Step 2: Generate migration:

[package-manager] drizzle-kit generate

Step 3: Apply migration:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

3.2. Renaming Columns

Important: Drizzle sees renames as drop + add. Manual migration required.

Step 1: Update schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: varchar('full_name', { length: 255 }), // was 'name'
});

Step 2: Generate migration (will create drop + add):

[package-manager] drizzle-kit generate

Step 3: Edit migration file manually:

-- Change from:
-- ALTER TABLE users DROP COLUMN name;
-- ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- To:
ALTER TABLE users RENAME COLUMN name TO full_name;

Step 4: Apply migration:

[package-manager] drizzle-kit migrate

3.3. Dropping Columns

Step 1: Remove from schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  // removed: phoneNumber
});

Step 2: Generate and apply:

[package-manager] drizzle-kit generate
[package-manager] drizzle-kit migrate

Warning: This permanently deletes data. Back up first!

3.4. Changing Column Types

Step 1: Update schema:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  views: bigint('views', { mode: 'number' }), // was: integer
});

Step 2: Generate migration:

[package-manager] drizzle-kit generate

Step 3: Review generated SQL - may need data migration if incompatible types.

Phase 4: Indexes and Constraints

4.1. Add Indexes

Single column:

import { pgTable, serial, text, index } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: serial('author_id').notNull(),
}, (table) => ({
  titleIdx: index('posts_title_idx').on(table.title),
  authorIdIdx: index('posts_author_id_idx').on(table.authorId),
}));

Composite index:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: serial('author_id').notNull(),
  status: text('status').notNull(),
}, (table) => ({
  authorStatusIdx: index('posts_author_status_idx').on(table.authorId, table.status),
}));

4.2. Unique Constraints

Single column:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
});

Multiple columns:

import { pgTable, serial, text, unique } from 'drizzle-orm/pg-core';

export const postsTags = pgTable('posts_tags', {
  postId: serial('post_id').notNull(),
  tagId: serial('tag_id').notNull(),
}, (table) => ({
  unq: unique('posts_tags_unique').on(table.postId, table.tagId),
}));

4.3. Check Constraints

import { pgTable, serial, integer, check } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price').notNull(),
  discountedPrice: integer('discounted_price'),
}, (table) => ({
  priceCheck: check('price_check', 'price >= 0'),
  discountCheck: check('discount_check', 'discounted_price < price'),
}));

Phase 5: Generate and Apply Changes

5.1. Generate Migration

After any schema changes:

[package-manager] drizzle-kit generate

Review generated SQL in src/db/migrations/.

5.2. Apply Migration

With proper environment loading:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

Or use the migration script:

[package-manager] tsx scripts/run-migration.ts

5.3. Verify Changes

Check in database:

psql $DATABASE_URL -c "\d table_name"

Test with queries:

import { db } from './src/db';
import { tableName } from './src/db/schema';

const result = await db.select().from(tableName);
console.log('Schema works:', result);

Phase 6: Advanced Patterns

For complex schemas, see:

• templates/schema-example.ts - Multi-table examples with relations
• references/migrations.md - Advanced migration patterns

Common Issues

• Migration conflicts: See guides/troubleshooting.md
• Relationship errors: Ensure foreign keys reference correct columns
• Type mismatches: Match TypeScript types with SQL types carefully

Next Steps

After schema creation:

1. Run migrations (see above)
2. Create queries (see references/query-patterns.md)
3. Add validation (use Zod or similar)
4. Test thoroughly before production