Drizzle ORM

// src/db/schema/product.ts
import { pgTable, serial, text, integer, timestamp } from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

export const product = pgTable('product', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  userId: integer('user_id').references(() => user.id),
  createdAt: timestamp('created_at').defaultNow(),
})

export const productRelations = relations(product, ({ one }) => ({
  creator: one(user, {
    fields: [product.userId],
    references: [user.id],
  }),
}))

export const user = pgTable('user', {
  // Required column with unique constraint
  email: text('email').notNull().unique(),
  
  // Optional column with default
  role: text('role').default('user'),
  
  // Not null with default
  status: text('status').default('active').notNull(),
  
  // Primary key
  id: serial('id').primaryKey(),
  
  // Foreign key
  companyId: integer('company_id').references(() => company.id),
})

import { db } from '@/db'
import { product } from '@/db/schema'
import { eq } from 'drizzle-orm'

export const getProduct = createServerFn({ method: 'GET' }).handler(
  async ({ data }: { data: { id: number } }) => {
    return db.query.product.findFirst({
      where: eq(product.id, data.id),
    })
  }
)

const products = await db.query.product.findMany({
  where: eq(product.userId, userId),
  orderBy: (p) => p.createdAt,
  limit: 10,
})

const product = await db.query.product.findFirst({
  where: eq(product.id, id),
  with: {
    creator: true, // Include related user
  },
})

// Access: product.creator.name

const count = await db
  .select({ count: sql<number>`count(*)` })
  .from(product)
  .where(eq(product.userId, userId))

const result = await db
  .insert(product)
  .values({
    name: 'New Product',
    userId: user.id,
  })
  .returning()

console.log(result[0].id) // Get inserted record

await db.insert(product).values([
  { name: 'Product 1', userId: 123 },
  { name: 'Product 2', userId: 123 },
  { name: 'Product 3', userId: 123 },
])

const updated = await db
  .update(product)
  .set({ name: 'Updated Name' })
  .where(eq(product.id, productId))
  .returning()

await db.delete(product).where(eq(product.id, productId))

await db
  .insert(product)
  .values({ id: 1, name: 'Product', userId: 123 })
  .onConflictDoUpdate({
    target: product.id,
    set: { name: 'Updated Product' },
  })

import { eq, ne, lt, lte, gt, gte, like, inArray, between, isNull } from 'drizzle-orm'

await db.query.product.findMany({
  where: (p) => ({
    // Equality
    userId: eq(p.userId, 123),
    
    // Not equal
    status: ne(p.status, 'deleted'),
    
    // Comparisons
    price: gt(p.price, 100),
    created: gte(p.createdAt, startDate),
    
    // String matching
    name: like(p.name, '%Pattern%'),
    
    // In list
    status: inArray(p.status, ['active', 'pending']),
    
    // Between
    views: between(p.views, 100, 1000),
    
    // Null checks
    deletedAt: isNull(p.deletedAt),
  }),
})

await db.transaction(async (tx) => {
  // All queries in transaction
  await tx.insert(order).values(orderData)
  await tx.update(inventory).set({ quantity: qty - 1 })
  // If any fails, entire transaction rolls back
})

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import { product } from '@/db/schema'

// Type for selecting (with all fields)
type Product = InferSelectModel<typeof product>

// Type for inserting (required fields only)
type ProductInsert = InferInsertModel<typeof product>

// Use in functions
function renderProduct(p: Product) {
  return <div>{p.name}</div>
}

export const userRelations = relations(user, ({ many }) => ({
  products: many(product),
}))

// Query with relation
const userWithProducts = await db.query.user.findFirst({
  where: eq(user.id, 123),
  with: {
    products: true,
  },
})

// Access: userWithProducts.products

export const productRelations = relations(product, ({ one }) => ({
  creator: one(user, {
    fields: [product.userId],
    references: [user.id],
  }),
}))

// Query
const productWithCreator = await db.query.product.findFirst({
  where: eq(product.id, 1),
  with: {
    creator: true,
  },
})

export const userTags = pgTable('user_tags', {
  userId: integer('user_id').references(() => user.id),
  tagId: integer('tag_id').references(() => tag.id),
})

export const userRelations = relations(user, ({ many }) => ({
  tags: many(userTags),
}))

export const userTagsRelations = relations(userTags, ({ one }) => ({
  user: one(user),
  tag: one(tag),
}))

import { index } from 'drizzle-orm/pg-core'

export const product = pgTable(
  'product',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id'),
    status: text('status'),
  },
  (table) => ({
    userIdx: index('user_idx').on(table.userId),
    statusIdx: index('status_idx').on(table.status),
  })
)

import { unique } from 'drizzle-orm/pg-core'

export const profile = pgTable(
  'profile',
  {
    id: serial('id').primaryKey(),
    handle: text('handle').notNull(),
  },
  (table) => ({
    handleUnique: unique().on(table.handle),
  })
)

pnpm db:generate

# Push directly (development)
pnpm db:push

# Or migrate (production)
pnpm db:migrate

pnpm db:studio