Drizzle ORM

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

export const roleEnum = pgEnum('role', [
  'admin', 'user', 'moderator'
]);

export const statusEnum = pgEnum('status', [
  'active', 'inactive', 'suspended'
]);

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  role: roleEnum('role').default('user').notNull(),
  status: statusEnum('status').default('active').notNull(),
});

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  slug: text('slug').unique().notNull(),
  authorId: integer('author_id').notNull(),
  category: text('category'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ([
  index('author_idx').on(table.authorId),
  uniqueIndex('slug_idx').on(table.slug),
  index('category_author_idx')
    .on(table.category, table.authorId),
]));

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

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id')
    .references(() => users.id, {
      onDelete: 'cascade',
    })
    .notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

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

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

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

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

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

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

export const profiles = pgTable('profiles', {
  id: serial('id').primaryKey(),
  bio: text('bio'),
  avatarUrl: text('avatar_url'),
  userId: integer('user_id').unique().notNull(),
});

export const usersRelations = relations(users, ({ one }) => ({
  profile: one(profiles),
}));

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

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

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').unique().notNull(),
});

export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id').notNull()
    .references(() => posts.id),
  tagId: integer('tag_id').notNull()
    .references(() => tags.id),
}, (t) => ([
  primaryKey({ columns: [t.postId, t.tagId] }),
]));

export const postsRelations = relations(posts, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));

import { asc, desc } from 'drizzle-orm';

// Order by single column
await db.select().from(users)
  .orderBy(asc(users.name));

// Order by multiple columns
await db.select().from(users)
  .orderBy(desc(users.createdAt), asc(users.name));

// Pagination with limit and offset
const page = 2;
const pageSize = 20;
await db.select().from(users)
  .orderBy(desc(users.createdAt))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

import { eq, lt, and } from 'drizzle-orm';

// Delete by ID
await db.delete(users).where(eq(users.id, 1));

// Delete with returning
const [deleted] = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();

// Delete with complex condition
await db.delete(users).where(
  and(
    eq(users.isActive, false),
    lt(users.createdAt, new Date('2024-01-01'))
  )
);

import { eq } from 'drizzle-orm';

const result = await db.select({
  postId: posts.id,
  postTitle: posts.title,
  authorName: users.name,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

import { eq, gt, count } from 'drizzle-orm';

// Group by with count
const postsByAuthor = await db.select({
  authorId: posts.authorId,
  postCount: count(),
}).from(posts)
  .groupBy(posts.authorId);

// Having — filter groups
const prolificAuthors = await db.select({
  authorId: posts.authorId,
  postCount: count(),
}).from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(), 5));

await db.transaction(async (tx) => {
  await tx.insert(users).values({
    name: 'Alice',
    email: 'alice@example.com',
  });

  // Nested transaction — creates a SAVEPOINT
  try {
    await tx.transaction(async (tx2) => {
      await tx2.insert(posts).values({
        title: 'First Post',
        authorId: 1,
      });
      // This will rollback only the nested tx
      throw new Error('Oops');
    });
  } catch {
    // Nested tx rolled back, outer tx continues
    console.log('Nested transaction failed');
  }

  // This still commits — Alice is created
  await tx.insert(profiles).values({
    userId: 1,
    bio: 'No posts yet',
  });
});

# Generate migration from schema diff
npx drizzle-kit generate

# Apply pending migrations
npx drizzle-kit migrate

# Push schema directly (no migration files)
npx drizzle-kit push

# Open Drizzle Studio (DB GUI)
npx drizzle-kit studio

# 1. Edit your schema file (e.g., src/db/schema.ts)
#    Add/modify tables, columns, indexes

# 2. Generate a migration
npx drizzle-kit generate
# Creates: drizzle/0001_add_user_table.sql

# 3. Review the generated SQL
cat drizzle/0001_add_user_table.sql

# 4. Apply the migration
npx drizzle-kit migrate

# 5. Check current schema status
npx drizzle-kit check

# 6. Drop a migration (if needed before applying)
npx drizzle-kit drop

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';

const db = drizzle(process.env.DATABASE_URL!);

// Run migrations on app startup
await migrate(db, {
  migrationsFolder: './drizzle',
});

console.log('Migrations complete');

# Fast iteration — diff schema and push to the DB directly
npx drizzle-kit push           # no SQL files, no history; great for local/dev

# Versioned migrations — generate SQL, commit it, then apply
npx drizzle-kit generate       # writes drizzle/<timestamp>_<name>.sql
npx drizzle-kit migrate        # applies pending migration files

# Other useful kit commands
npx drizzle-kit studio         # browse + edit data at localhost:4983
npx drizzle-kit check          # verify migration files are consistent
npx drizzle-kit up             # rewrite drizzle metadata after manual edits

# DETAILED_TAB:
# ===== drizzle-kit push =====
# What it does: diffs your schema.ts against the live DB and runs the
# resulting ALTERs immediately. No SQL files written. No history.
#
# Use when:
#   - Local dev where the schema is still in flux
#   - A throwaway preview branch
#   - You don't need an audit trail of how the schema evolved
#
# Don't use in production — there's no replayable record of changes.
npx drizzle-kit push
npx drizzle-kit push --verbose            # show every SQL statement
npx drizzle-kit push --strict             # confirm potentially destructive ops

# ===== drizzle-kit generate + migrate =====
# What it does:
#   generate → diffs schema.ts and writes a numbered .sql file
#              into your migrations folder
#   migrate  → applies all unapplied files in order, tracked in a
#              __drizzle_migrations table
#
# This is the production workflow. Commit the SQL files to git so
# every deploy sees the same migration history.

# drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
})

# Workflow
npx drizzle-kit generate --name=add_posts_table   # writes 0001_add_posts_table.sql
git add drizzle/
git commit -m 'migration: add posts table'
npx drizzle-kit migrate                            # apply locally / in CI

# Programmatic migrate (e.g. in a CI step or app startup)
import { migrate } from 'drizzle-orm/node-postgres/migrator'
await migrate(db, { migrationsFolder: './drizzle' })

# ===== Hybrid pattern that works for most teams =====
#   - Local dev: npx drizzle-kit push for fast iteration
#   - Before PR: npx drizzle-kit generate to capture the final shape
#   - CI / prod: npx drizzle-kit migrate from the generated SQL files