database-sql

Database & SQL

When to use this skill

• Designing a database schema
• Writing or optimizing SQL queries
• Creating database migrations
• Setting up an ORM (Prisma, Drizzle, TypeORM, SQLAlchemy)
• Debugging query performance
• Adding indexes

Schema design principles

Naming conventions

• Tables: plural, snake_case — users, order_items
• Columns: snake_case — created_at, first_name
• Primary keys: id (auto-increment or UUID)
• Foreign keys: <singular_table>_id — user_id, order_id
• Indexes: idx_<table>_<columns> — idx_users_email
• Booleans: is_ or has_ prefix — is_active, has_verified

Common patterns

-- Standard table template
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Junction table for many-to-many
CREATE TABLE user_roles (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, role_id)
);

-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

Data types guide

Use case	PostgreSQL	MySQL
Primary key	UUID or BIGSERIAL	BIGINT AUTO_INCREMENT
Short text	VARCHAR(n)	VARCHAR(n)
Long text	TEXT	TEXT
Currency	NUMERIC(12,2)	DECIMAL(12,2)
Timestamps	TIMESTAMPTZ	DATETIME
JSON	JSONB	JSON
Booleans	BOOLEAN	TINYINT(1)
Enums	VARCHAR + CHECK	ENUM(...)

Migrations

Prisma

// schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Order {
  id        String      @id @default(uuid())
  userId    String      @map("user_id")
  user      User        @relation(fields: [userId], references: [id])
  status    OrderStatus @default(PENDING)
  total     Decimal     @db.Decimal(12, 2)
  createdAt DateTime    @default(now()) @map("created_at")

  @@index([userId])
  @@map("orders")
}

enum OrderStatus {
  PENDING
  CONFIRMED
  SHIPPED
  DELIVERED
  CANCELLED
}